Report on return customers

[emtecmedia]

Hi,

I would like to know if there is anything out there which could report on return customers. Ie. 20% of customers have purchased once, 35% of customers have purchased twice, 50% of customers have purchased 2 - 3 times.

If anyone has an idea on an sql query on the orders table which would give me some results that would even do.

Thanks,
Brad.

[DrByte]

This may look odd, but it should work as-is, on MySQL 5.0 or greater:

SET @custcount=0;
SELECT (@custcount:=count(*)) as custcount 
FROM customers;
SELECT numorders, count(numorders) as numcusts, @custcount as totalcustomers, count(numorders)/@custcount as percent
FROM (SELECT count(orders_id) AS numorders 
FROM  orders GROUP BY customers_id) o
GROUP BY (o.numorders) ORDER BY numorders DESC;

If you're using custom table prefixes, you'll need to add your prefix to the two highlighted tablenames.

[emtecmedia]

Hi,

Thanks for that, i just tested it and it works fine but im not sure how accurate it is because when i add up the columns they don't match, ie. the percentages total is 76.8% when it should be 100%.

Brad.

[DrByte]

It doesn't tell you how many customers have never ordered ... I guess that's the remaining amount?

[0be1]

I came across this post by accident looking for something else, but I found what the original question to be very interesting. I have tried this query to see what result I would get and received the following error.

1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT count(orders_id) AS numorders
FROM zen_orders GROUP B

I made sure that I changed the lines to accommodate for the table prefixes. Our hosted server is also running MySQL version 4.0.26. I even tried to run the the SQL executor directly in Zen Cart and still had errors.

Any ideas on what to change?

tia...

0be1

[DrByte]

The query I posted uses a derived table, which is akin to subselects. Some of these features are not available on older versions of MySQL.
I wrote and tested it on MySQL 5.0.
I'm not sure if it works in 4.1. From your post it obviously doesn't work in 4.0.

[0be1]

Thanks Dr. Byte... but can the query be re-written to work in 4.0 or are us dark ages people out of luck

0be1

[DrByte]

Thanks Dr. Byte... but can the query be re-written to work in 4.0

No.
But you can run this query and dump it into Excel and count the duplicate values, and work out the percentages from there:

SELECT count(orders_id) AS numorders 
FROM  orders GROUP BY customers_id;

[ksoup]

Thank you, DrByte!! This gives us a wealth of information! :)

This may look odd, but it should work as-is, on MySQL 5.0 or greater:

SET @custcount=0;
SELECT (@custcount:=count(*)) as custcount 
FROM customers;
SELECT numorders, count(numorders) as numcusts, @custcount as totalcustomers, count(numorders)/@custcount as percent
FROM (SELECT count(orders_id) AS numorders 
FROM  orders GROUP BY customers_id) o
GROUP BY (o.numorders) ORDER BY numorders DESC;

If you're using custom table prefixes, you'll need to add your prefix to the two highlighted tablenames.