I need to export all customer accounts and their sales statistics (more specifically accounts with ZERO ORDERS). When using the Export Profile Feature with Entity Type "Customers" the field mapping feature gives me no option(s) for anything related to sales data. The only way I know how to identify customer accounts with ZERO purchases is to do it manually within "Manage Customers" which is very laborious to say the least.
Add a comment
|
1 Answer
you can achieve this with one sql request :
Customers without orders:
select e.email
from customer_entity e
left join sales_flat_order o on e.entity_id = o.customer_id
where o.entity_id is null
group by email
;
Customers with total orders
select e.email,count(o.entity_id) as total_orders
from customer_entity e
inner join sales_flat_order o on e.entity_id = o.customer_id
group by email
;
-
Thank you Bio! I just had to change sales_flat_order to sales_order because I'm using Magento 1.3.2.4. - Now how do I add a condition that allows me to ONLY show accounts created during the month of October 2014? I see there is a table called (created_at) within the mag_customer_entity I just can't figure out the correct sytax.– user9722Commented Oct 24, 2014 at 18:45
-
Yeah, you can use created_at field in customer_entity : select * from customer_entity where created_at between "2014-10-01 00:00:00" AND "2014-10-30 00:00:00";– BioCommented Oct 24, 2014 at 19:06