Report on return customers

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.

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 [B]customers[/B];
SELECT numorders, count(numorders) as numcusts, @custcount as totalcustomers, count(numorders)/@custcount as percent
FROM (SELECT count(orders_id) AS numorders
FROM [B]orders[/B] GROUP BY customers_id) o
GROUP BY (o.numorders) ORDER BY numorders DESC;

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.

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

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

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.

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

0be1

0be1:

Thanks Dr. Byte... but can the query be re-written to work in 4.0No.
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;

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

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 [B]customers[/B];
SELECT numorders, count(numorders) as numcusts, @custcount as totalcustomers, count(numorders)/@custcount as percent
FROM (SELECT count(orders_id) AS numorders
FROM [B]orders[/B] GROUP BY customers_id) o
GROUP BY (o.numorders) ORDER BY numorders DESC;