Best Customers / Lost Customers

[Guest]

Hi,

 

There are a few contributions designed to show customer statistics and order totals. What I am trying to do is find out which customers have placed the most orders and the values but then left and not ordered again. The idea being that I can then try to entice these ex-top customers back as they are valuable.

 

I would settle even for a query that drags them out by date range or something. I am unsure how to do this.

 

Can anyone help?

 

Thanks

[♥ecartz]

Try

SELECT * FROM (SELECT sum(ot.value) as total_purchased, max(o.date_purchased) as last_purchased, o.customers_id FROM `orders_total` ot, orders o WHERE ot.class = 'ot_total' and ot.orders_id = o.orders_id and o.currency = 'USD' GROUP BY o.customers_id) bcq WHERE bcq.last_purchased < DATE_SUB(NOW(), INTERVAL 2 week) ORDER BY bcq.total_purchased DESC, bcq.last_purchased ASC

Change 2 week to whatever interval works for you. The '2 week' value produces good results on my test database at the moment but might not be useful in a production setting. A 2 year or 18 month value might work better for what you are trying to do.

 

You could also try

SELECT bcq.*, c.customers_firstname, c.customers_lastname, c.customers_email_address FROM (SELECT sum(ot.value) as total_purchased, max(o.date_purchased) as last_purchased, o.customers_id FROM `orders_total` ot, orders o WHERE ot.class = 'ot_total' and ot.orders_id = o.orders_id and o.currency = 'USD' GROUP BY o.customers_id) bcq left join customers c on bcq.customers_id = c.customers_id WHERE bcq.last_purchased < DATE_SUB(NOW(), INTERVAL 1 week) and bcq.total_purchased > 100.0 ORDER BY bcq.last_purchased ASC, bcq.total_purchased DESC

Again, change the 1 week and 100.0 to values that work for your situation.

[Guest]

I tweaked the latter of the options you provided and it is perfect.

 

Thanks so much!