Sending a reminder to customer about their stock

I'm trying to setup a reminder mail for customers to reorder, based on what they ordered and the quantity with normal usage.

EG. The customer orders 2 boxes of 12 tablets, recommended use is 1 tablet per week, in that case I would like to send an email to the customer 2*12=24 weeks after the product was purchased.

Since the interval is dependent on the type of product, I added 2 fields to the product type:

Interval: days, weeks, months, years
Amount: integer

That way the shop owner can select when to send the reminder for any variant of a product.

Since the product specifications are user input I don't want to schedule the reminder when the product is purchased, just in case the shop owner goes back and change the values of the reminder, the existing reminders would need to be rescheduled and that is seems to be the wrong way to handle them.

My solution would be to check the orders for the products purchased once a day to see if a reminder needs to be sent.

The things I'm trying to figure out are:
- How can I determine the day on which the email should be send based on the order date and the interval and amount fields from the product linked to the line item.
- How can I prevent the reminders from being send multiple times (is it possible to for example flag the line item)?

Any input would be greatly appreciated, also if you have better ideas to get something like this setup, let me know.

Posted: Oct 19, 2011


Fritz Otiker on July 10, 2014

Did you ever figure out how to set something like this up? Im curious as I will have the exact same needs based on usage formula you set for products that are used on a regular basis. Thanks in advance