You know the feeling. A customer bought from you 45 days ago, and you are waiting for them to come back. Another customer bought the same product 90 days ago and you have already written them off. But what if you could predict exactly when each shopper is due for their next order, and send the right nudge at exactly the right moment?
This is not about buying a loyalty platform or installing a complex CRM. You can build a simple repeat purchase predictor using nothing but your order export, a spreadsheet, and a formula that takes 20 minutes to set up.
What a Repeat Purchase Predictor Actually Does
A repeat purchase predictor looks at two things for each customer: how long it typically takes them to reorder, and how long it has been since their last order. When the gap between those two numbers gets small, the customer is “due” for a repurchase. That is your window to reach out.
The predictor does not guess. It uses each customer’s own buying rhythm, which means it works for products that get repurchased weekly, monthly, or seasonally. A skincare brand might see 30-day cycles. A coffee roaster might see 14-day cycles. A candle business might see 60-day cycles. The predictor adapts to all of them.
What You Need
- Your order history export (CSV from Shopify, WooCommerce, or whatever platform you use). You need at minimum: customer email, order date, and order total.
- A spreadsheet (Google Sheets, Excel, or Apple Numbers all work fine).
- 20 minutes.
Step 1: Export Your Orders
Go to your e-commerce platform and export your orders as a CSV. In Shopify, that is Orders > Export > All Orders > Plain CSV file. In WooCommerce, go to WooCommerce > Orders > Export. You want at least 6 months of data, but 12 months is better. The more data you have, the more accurate the prediction.
Open the CSV in your spreadsheet. You need three columns:
- Customer email (or name, if you do not have email)
- Order date
- Order total
Step 2: Calculate Each Customer’s Average Days Between Orders
Create a new sheet or tab. Sort your data by customer email, then by order date (oldest to newest). For each customer who has placed 2 or more orders, calculate the number of days between each consecutive order.
For example, if Customer A ordered on January 1, February 3, and March 7:
- Days between order 1 and 2: 33
- Days between order 2 and 3: 32
- Average days between orders: 32.5
If a customer has only placed one order, you cannot calculate their personal rhythm yet. You will use your store-wide average for them instead. To get that, average the “days between orders” across all your repeat customers. That becomes your default cycle length.
Step 3: Calculate “Days Since Last Order” for Each Customer
Add a column called “Days Since Last Order.” Use this formula (in Google Sheets):
=TODAY() - MAXIFS(B:B, A:A, D2)
Where column B is your order dates, column A is your customer emails, and D2 is the email of the customer in your current row. This gives you the number of days since that customer’s most recent order.
Step 4: Calculate the “Due Score”
This is where the prediction happens. Create a column called “Due Score” with this formula:
=(Days Since Last Order / Average Days Between Orders) * 100
What this score tells you:
- Below 50: Customer is early in their cycle. Do not contact them yet.
- 50 to 80: Customer is approaching their typical reorder window. A soft reminder is appropriate.
- 80 to 100: Customer is due. This is your prime window for a targeted email or SMS.
- Over 100: Customer is overdue. They may have switched to a competitor or lost interest. Try a stronger incentive.
Step 5: Filter and Act
Sort your spreadsheet by Due Score, highest to lowest. Everyone with a score above 80 is your list for this week. You now have a targeted group of customers who are statistically most likely to be ready to buy again.
Here is how to reach out based on their score:
- Score 80 to 100: Send a simple restock reminder. “Your last order was a while ago. Need a refill?” Include a direct link to the product they bought last.
- Score 100 to 130: Send a small incentive. “We noticed you haven’t been back in a while. Here is 10% off your next order.” This is not a discount you give to everyone. It is a targeted win-back for someone the data says should have returned by now.
- Score over 130: Send a different product recommendation. If they have not returned for their usual product after this long, something changed. Show them something new.
Step 6: Set a Weekly Reminder to Refresh
The predictor only works if you check it regularly. Set a calendar reminder for the same day each week. Open your spreadsheet, re-export your latest orders, paste them in, and check who has crossed the 80 threshold. It takes 10 minutes once you have the system set up.
If you want to get fancy, you can use Google Sheets’ conditional formatting to colour-code the Due Score column. Green for under 50, yellow for 50 to 80, orange for 80 to 100, red for over 100. That way you can see your action list at a glance.
What Makes This Better Than a Generic Reminder
Most stores send the same restock reminder to every customer at the same interval. If you sell skincare, you might send a refill email 30 days after every purchase. But not every customer has the same rhythm. Some use the product faster. Some use it slower. Some bought it as a gift and will never reorder.
The predictor respects each customer’s actual behaviour. A customer who reorders every 14 days gets a reminder at day 12, not day 30. A customer who reorders every 90 days does not get a reminder at day 30 that makes them feel spammed. The timing adapts to the individual.
When to Upgrade From a Spreadsheet
The spreadsheet version works up to about 500 customers. Past that, you will want to automate the data refresh. Tools like Google Sheets can connect directly to your Shopify store via apps like EZ Exporter or Matrixify, which can auto-export orders on a schedule. That turns your weekly manual refresh into a living dashboard.
But the formula does not change. The logic is the same whether you are working with 50 rows in a spreadsheet or 50,000 rows in a database. Days since last order divided by average days between orders. That ratio is your prediction.
Want more like this? Join MOKU Club for free. Weekly resources, early access to new guides, and occasional templates you can actually use. Join below.
