Customers who bought more than once - reporting?

[rwoody]

I have a request to be able to create reports from time to time that list customer's that have made more than one purchase from the store. I created the first one manually by importing the orders table and then doing a sort on the Customer IDs, but you can see this was monumental (the site had over 2000 customer's from just this last year).

When in the customer view, one can see in the right hand column information about each customer to include number of orders made in the past. I've reviewed the code on the customer.php page, but I'm just not clear on how that information is being pulled. I've looked at the customer detail table and of course that information is not contained there.

Can someone assist me with ideas on how to develop a report that will pull the information displayed on the customer detail to include number of purchases made by each customer?

(ver 1.3.8a - PHP/MySQL 5x)

Any ideas on this would be really appreciated.

Thanks

Ruth

[gharls]

Does your reference to 'purchases by customer' mean number of orders, or numbers of items purchased?

[rwoody]

Thank you so much for looking at my dilemma. Number of orders is the short answer. This might be something that others might want and if we can figure out a solution, I would be more than happy to share the final result.

When doing marketing for one's store, you like to reward repeat customer's. I've looked at the rewards program contribution - and it is a possibility for the future, (but if I remember correctly there maybe some issues with that one, I need to go back and look at my notes), but it does not solve "history".

In other words, this site has been in existence for several years but is now under new ownership. I am assisting the new owner with many, many changes and with marketing. He wants to establish a list of repeat customer's over the last three years. Now I'm aware that when that list is created, that I will need to verify the validity of the data (sometimes there are multiple orders due to something being out of stock as an example). But the customer does not want to pay me every time he wants to pull this report and currently, to be honest it's a pain in the keester.

I'd like to be able to easily create a report where one could select the order date range or orders greater than a specific order ID (since they are chronological) and be able to pull a list that would provide the customer's name, email, Order ID and Order Date. I can do that by going directly to the orders table and doing an export of data, but then I have this huge list that I then sort by Customer ID. Now I have to manually pick through the list and note the duplicate customer ID's and pull those out to create the final list of customer's who've had more than one order. When you add to that data integrity (which will still need to be done manually) the time involved is not cost effective.

Ruth

[gharls]

Hello--

Don't thank me just yet ... I'm just browsing the forum looking for my own answers, while at the same time hoping to offer some 'lessons learned' to folks working out their ZC reporting concerns.

My apologies for being so dense ... are you looking for (1), the SQL to produce your report, (2), an existing mod that meets your spec, or (3), how to build such a report?

Maybe I'm missing something, but your spec seems fairly straightforward....

[rwoody]

Hey listen, even a friendly voice out there, even if it is to commiserate with me is great...LOL - There is nothing out there that does this, so yes, I'm trying to figure out a better sql call that I could use to base a new report on. This is going to need to be a "from scratch" piece.

I've taken a look at customer.php - and for the life of me (now I'm probably just blind here) I cannot figure out how they are pulling the data in the customers page in the admin. If you open your admin and just click on "Customers", you will note on the right hand side as you click on each customer, there is a ton of data there. What would be nirvana would be if a report could be built that would take that information and pull it. Each customer has the information in regards which contains the number of orders that customer has made. Now when I look at the calls in the code, I'm over looking what table they are using to grab that info. customers_info does not contain that information and I don't see a join to another table such as orders...

I should state that I'm not the world's best coder, but typically seem to get things done.. but I need a better mouse trap than what I'm using now.. shall we say.

Ruth