Home
>
Magento,
MySQL > How to export customers from orders between certain dates in Magento
How to export customers from orders between certain dates in Magento
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:
1 | SET @etID := ( SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'order_address' ); |
2 | SET @atFn := ( SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'firstname' AND entity_type_id = @etID); |
3 | SET @atLn := ( SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'lastname' AND entity_type_id = @etID); |
4 | SET @atEmail := ( SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'customer_email' ); |
6 | SET @startDate := '2010-08-06 00:00:00' ; |
7 | SET @endDate := '2010-08-13 23:59:59' ; |
9 | SELECT o.increment_id, e.value as email, ln.value as lastname, fn.value as firstname |
11 | INNER JOIN sales_order_varchar e ON e.entity_id = o.entity_id AND attribute_id = @atEmail |
12 | INNER JOIN sales_order_entity lne ON lne.parent_id = o.entity_id AND lne.entity_type_id = @etID |
13 | INNER JOIN sales_order_entity_varchar ln ON ln.entity_id = lne.entity_id AND ln.attribute_id = @atLn |
14 | INNER JOIN sales_order_entity_varchar fn ON fn.entity_id = lne.entity_id AND fn.attribute_id = @atFn |
15 | WHERE o.created_at BETWEEN @startDate AND @endDate |
16 | GROUP BY o.increment_id |
17 | 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 1.4.0.1