List of Product Ordered for seasonal promotions

At the osCommerce forum, Heather asked;

What Im basically looking for is a list of customers against what products they ordered, some of my products are seasonal, and I want to be able to email the customers that bought them last year to remind them that they are in stock again.

Ideally:

Product + Customer + email address

My answer was to use phpmyadmin, and run the following SQL query;

[php]SELECT DISTINCT op.products_name, o.customers_name, o.customers_email_address
FROM orders o
LEFT JOIN orders_products op
ON o.orders_id = op.orders_id
ORDER BY op.products_name[/php]

I didn’t test it, but Heather returned to the thread to advise it worked as she wanted it. I’m not all that great at SQL stuff, so was hoping it worked out OK.

Basically, the SQL query is interrogating two osCommerce tables; orders and orders_products. From orders we are getting the “customers name” and the “customers email address”. From orders_products we are getting the “products names” ordered, but because we placed a DISTINCT on that, it only returns one rather than many (as there is no point knowing that Mr XYZ bought 3 of the same item!). Then we link the results from the two tables together, based on the orders_id (which is the same integer in each of the two tables). And finally we present the results ordered by products name.

So, in a real life situation, the output from phpmyadmin would look somewhat similar to this;

Please note I blurred out the email addresses in my graphics program.

What’s next?

I’m thinking that this (obviously with a few amendments) could be made into an automated script, which is run on a daily basis. This script would email each customer to let them know that a year ago (or a month ago or whatever) they ordered XYZ product, and it is now time for them to order again. Completely automated with no additional work needed from the shop owner. Probably make a really good addition to osCommerce, though I guess most shops don’t sell seasonal products?

2 Replies to “List of Product Ordered for seasonal promotions”

  1. I could see a use for this in my shops. We are in the pet apparel / accessory business and there is a seasonality there. Halloween dog costumes, dog Christmas apparel, dog life preservers sales for example are primarily seasonal, so a “reminder” that we are restocked for the new season would be a good marketing tool.

  2. Steve (or anyone else) – if you’d be happy to sponsor the development of this to something that is usable in a live shop, I can find the time to code it. Please email if of interest.

Leave a Reply

Your email address will not be published. Required fields are marked *