Last week a client who runs his store on Magento Commerce asked me how he could export a list of customers who purchased between certain dates in August. Of course, I first went to the Administration area and tried several approaches (order reports, customer reports, data export). Unfortunately nothing worked, so I had to write a script or a plain SQL and run it from the CLI. I preferred a straight query, rather than a script, and I tried not to use sub-selects. Here’s the adapted part, where I’ve added some variables to keep all the attribute id’s:
SET @etID := (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'order_address'); SET @atFn := (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'firstname' AND entity_type_id = @etID); SET @atLn := (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'lastname' AND entity_type_id = @etID); SET @atEmail := (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'customer_email'); SET @startDate := '2010-08-06 00:00:00'; SET @endDate := '2010-08-13 23:59:59'; SELECT o.increment_id, e.value as email, ln.value as lastname, fn.value as firstname FROM sales_order o INNER JOIN sales_order_varchar e ON e.entity_id = o.entity_id AND attribute_id = @atEmail INNER JOIN sales_order_entity lne ON lne.parent_id = o.entity_id AND lne.entity_type_id = @etID INNER JOIN sales_order_entity_varchar ln ON ln.entity_id = lne.entity_id AND ln.attribute_id = @atLn INNER JOIN sales_order_entity_varchar fn ON fn.entity_id = lne.entity_id AND fn.attribute_id = @atFn WHERE o.created_at BETWEEN @startDate AND @endDate GROUP BY o.increment_id ORDER BY o.increment_id DESC;
The only problem is when the Shipping Name is different than the Billing Name – there’s no control over that, as we’re grouping by increment_id. This could easily be fixed in a PHP script, but the approach there should be different (using collections).
Hope this helps anyone, as it did the trick for me.
Solution tested under Magento 220.127.116.11