A customer bought from you in March and has not been back. Is that a customer you have lost, or one whose normal gap between orders is seven months? Everything you might sensibly do next depends on the answer, and staring at the order list will not give you one.
Retention software sells the answer as machine learning. The genuinely useful version is older, smaller, and runs on three numbers you already have. It was published in 2005 and its authors made a point of noting you can fit it in a spreadsheet.
- Repeat purchase prediction for a shop needs three inputs per customer: how many repeat orders, how long between first and last, and how long since the first.
- The BG/NBD model published in Marketing Science in 2005 was designed for exactly this, and its authors state its parameters can be obtained in Microsoft Excel.
- Silence is not churn. In a shop, nobody cancels, so the model asks whether a customer is probably still active rather than whether they left.
- A probability is only useful if a different action follows from a different number. Decide the actions before you fit anything.
- The famous retention statistics circulate without a checkable primary source, so measure your own repeat rate instead of planning against a folk figure.
What follows is the version a person running a shop can act on, with the maths described rather than derived, and with the places it goes wrong stated plainly.
Why is predicting a shop customer harder than predicting a subscriber?
Because your customers never tell you they have gone. A subscription business observes cancellation as an event. A shop observes absence, and absence looks identical whether somebody is finished with you or simply not ready yet.
This is called the non contractual setting, and it changes the question. You are not asking who churned. You are asking, for each customer, what is the probability they are still active given how they have behaved so far, and how many purchases should you expect from them in the next period.
The distinction matters commercially. If you treat a long gap as churn, you spend win back discounts on people who were going to return at full price anyway, the same mistake that makes a discount code in an abandoned cart email so expensive. If you treat every quiet customer as merely patient, you keep paying to email a list that is mostly gone.
The model that fits in a spreadsheet
The standard tool for this is the BG/NBD model, introduced in Counting Your Customers the Easy Way by Fader, Hardie and Lee in Marketing Science in 2005. The paper's own summary is unusually direct about who it is for: it presents the model as a behavioural variation on an earlier framework that is substantially simpler to implement, notes that its parameters can be obtained quite easily in Microsoft Excel, and positions it as an attractive alternative in most applications.
One of the authors published a technical note on implementing it in Excel, with a worked spreadsheet, which is a strong hint about the intended audience. This is not a research artefact that needs a data team. It is a marketing model built to be used by people who have order data and no infrastructure.
The behavioural story it encodes is easy to state. Each customer buys at their own steady rate while they are still active, and after any purchase there is a chance they quietly stop for good. Buying rates differ between customers, and so does the chance of stopping, and the model assumes those two things vary independently.
What data does it actually need?
Three columns per customer and nothing else. No demographics, no browsing history, no email engagement, no product categories.
The open source implementation documentation names them precisely: frequency, the count of periods in which the customer made a purchase; recency, the duration between a customer's first purchase and their latest one; and T, the age of the customer measured from their first purchase to the end of your observation window. From those it produces the expected number of purchases in a future period and the probability that a customer is still active.
| Input | What it means in your order table | Common mistake |
|---|---|---|
| Frequency | Repeat orders, so first order does not count | Using total order count including the first |
| Recency | Time from first order to most recent order | Using time since the last order instead |
| T | Time from first order to today | Measuring from account creation rather than first purchase |
| Time unit | Weeks or months, chosen once and kept | Mixing units between fitting and prediction |
| Window | A period long enough to contain repeat behaviour | Fitting on three months of a business with yearly cycles |
The recency definition trips up nearly everybody, because in ordinary speech recency means "how long since I last saw them". Here it means how far into their relationship with you their last order sat. A customer whose two orders were both in their first week has low recency even if that week was yesterday.
Where this model breaks
In three specific places, and knowing them is more valuable than knowing the formula.
The first is seasonality. The model assumes a steady individual buying rate, and a shop selling garden furniture or ski equipment has nothing of the kind. A customer who buys every April is not drifting away in November, but the arithmetic cannot see the difference.
The second is numerical, and it is real rather than theoretical. A 2025 paper, A Simplified and Numerically Stable Approach to the BG/NBD Churn Prediction model, addresses exactly this: the authors note the model's limitations where behaviour is influenced by seasonal events and possibly high purchase counts, propose defining churn as no purchases within a set number of days, and derive an alternative expression to avoid numerical overflow or underflow. If your own implementation returns impossible probabilities for your best customers, that is the known failure, not your mistake.
There is a fourth caution that is about you rather than the maths. A fitted bg nbd model produces a tidy number for every customer, and tidy numbers are persuasive. Print the uncertainty beside the estimate, or at minimum look at how much the parameters move when you refit on a slightly different window. If a three week shift in your cutoff date changes a customer's probability from 0.7 to 0.3, you have learned something important about how much weight that column can carry.
The third is scale. Fitting a model on 120 customers gives you parameters with enormous uncertainty. Below a few hundred customers with at least some repeat behaviour, use the raw counts and your own judgement, because a confident number produced from thin data is worse than an honest shrug.
Fit on an earlier period and check the prediction against the period that followed. If you cannot hold back a slice of history for that test, you are not measuring the model, you are admiring it.
What do you do differently at each probability?
Decide this first, in plain sentences, before any fitting. A probability that leads to the same action at 0.2 and 0.8 is a number you paid for and did not use, which is also the test to apply to every automation you consider switching on before peak season.
For a customer with a high probability of being active and a short expected gap, the correct action is usually nothing at all. They are coming back. A discount sent now buys a sale you already had, at a lower price, and teaches them to wait for the next one.
For a customer with a high probability of being active but a long expected gap, a reminder timed to their own interval is worth more than any offer. This is where repeat purchase prediction meets ordinary operations: the reorder note goes out at the point their consumable should be running low, not on a calendar month boundary. The segmentation logic behind that timing is the same one we set out in which stage of email marketing actually improves with automation.
The mechanics of a reorder reminder deserve one line of care. Time it from that customer's own median gap rather than from a category average, and if you do not have enough of their order history to compute a median, use the median across customers who bought the same product rather than across your whole base. A reminder that arrives two weeks early reads as pushy, and one that arrives two weeks late arrives after they bought elsewhere.
For a customer whose probability of being active has fallen, you have one honest choice to make. Spend on winning them back, or stop spending on them. Both are legitimate. What is not legitimate is the default, which is to keep mailing them forever because they cost nothing to keep on a list, since that quietly damages your deliverability for everyone else.
Does this replace RFM, or sit beside it?
Beside it, and the division of labour is clean. RFM sorts your customers into groups you can name and act on today. The BG/NBD model answers a question RFM cannot: whether a given customer in a given group is probably still there.
Think of RFM as the map and the model as the weather. The map tells you that a customer is in the "bought twice, not recently" box, which is genuinely useful for deciding what campaign they belong to. It does not tell you whether that box contains a person on a slow cycle or a person who is finished, and those two need opposite treatment.
Most shops should build the RFM grid first and only add the model when the grid stops answering their questions. That moment usually arrives when someone asks how much to spend winning back a segment, because that question needs a probability alive, not a label.
Turning a probability into money
Expected purchases become customer lifetime value the moment you multiply them by margin rather than by revenue. That distinction is where most spreadsheet valuations go wrong and it is worth being pedantic about.
Take the expected number of orders the model gives you for the next twelve months, multiply by average order value, then multiply by your gross margin rate, then subtract what it costs you to serve that customer including the returns they generate and the support they need. What remains is the number you may spend to keep them, and it is usually much smaller than the revenue figure people quote to themselves.
Two refinements matter at small scale. Discount future value if your cycles are long, because money in eighteen months is worth less than money now and a shop with a two year repurchase cycle is quietly assuming otherwise. And cap the horizon at something you can actually see, because a model happily projects five years from eighteen months of order history and the confidence is invented rather than measured.
Used this way, a churn probability stops being a dashboard ornament. It becomes a spending limit per customer, which is the only form in which customer retention analysis changes what a small business does on Monday.
The statistics you should not plan against
Two numbers appear in almost every article on this subject: that acquiring a customer costs five to twenty five times more than keeping one, and that a five percent increase in retention raises profit by twenty five to ninety five percent. Both are repeated constantly and neither travels with a source you can check.
No link is offered here for either, because searching for the primary evidence returns a chain of articles citing each other. That is not a claim they are false. It is a statement that they are unverifiable at the point of use, which makes them useless for planning your particular shop.
Your own version takes ten minutes. Count the customers who bought in a given quarter, then count how many of them bought again within a year. That is your repeat rate, it is specific to your goods and your prices, and it is the only figure worth putting into a decision about how much a customer is worth to you.
What the model cannot see, and you can
It reads timing and nothing else. Every reason a customer stopped buying is invisible to it, which means the interesting work starts where the model finishes.
Take the customers whose probability of being active has collapsed and look at what they bought. If they cluster around one product, you have a product problem rather than a retention problem, and no email sequence fixes it. If they cluster around one month, look at what happened that month: a delivery failure, a price change, a supplier substitution you thought nobody would notice.
Then read the support threads for twenty of them. The model gives you a shortlist worth reading, which is its real contribution at small scale, since reading every thread is impossible and reading a random twenty tells you little. A shortlist of quiet former customers is the highest yield reading a shop owner can do in an hour.
A simpler thing to do first
Before fitting anything, sort your customers into a grid on two axes: how recently they bought, and how often. Four or nine boxes, no model.
That exercise gives most small shops eighty percent of the value in an afternoon, because it exposes the two facts they were missing. The first is how much of revenue sits with a small group of repeat buyers. The second is how many one time buyers never came back, which is usually a much larger number than the owner expected and points at the first order experience rather than at any retention campaign.
Once that grid exists, the model earns its place by answering the one question a grid cannot: for this specific customer in this box, how likely is it that the silence is permanent? Everything else, including which products to put in front of them, is better handled by the methods in recommending from a small catalogue with almost no purchase data.
Putting it in your own stack
All of this runs on your orders table. The three inputs are a single grouped query, the fit is a spreadsheet or a short script, and the output is a column you write back beside each customer. There is no part of it that requires a separate platform, and the cost of adding one is usually larger than the margin it protects at small scale, which is worth weighing against what building the shop itself costs before you sign anything monthly.
Keep the fit itself boring. A spreadsheet model you understand and re run yourself beats a hosted score you cannot inspect, because when the numbers look wrong you need to be able to open the thing and find out why. If you later move it into code, keep the spreadsheet as the reference implementation and check the two agree on the same input, since a silent divergence between them is easy to create and hard to notice. The same instinct applies to the rest of the stack, which is the argument in picking tools by what you can inspect rather than by feature list.
Run it quarterly, not daily. Retention moves slowly, and a number that updates every night invites you to react to noise. Write down the actions attached to each band before you look at the results, keep a holdout group who receive nothing, and compare after two cycles.
The point of the exercise is not to know who will leave. It is to stop treating every quiet customer the same way, which is what a shop does by default, and which is expensive in both directions at once.