RFM segmentation for ecommerce: how to build it without a spreadsheet

RFM is direct marketing from the 1970s and it refuses to die, because it is right. Sort customers by how recently they bought, how often, and how much they spent, and you have a map of who to protect, who to reactivate and who to stop paying for.

It is usually presented as a 125-cell grid that nobody maintains. You do not need the grid. You need four groups and a rule for what each one gets.

How to score it

Score each customer 1–5 on each dimension, using quintiles of your own data rather than absolute thresholds. Quintiles matter: a €300 customer is a champion in one shop and unremarkable in another, and an absolute threshold goes stale as the business grows.

1. Recency — days since last order. Sort ascending, split into five equal groups, most recent scores 5.
2. Frequency — number of completed orders, ever or over 24 months. Pick one and write it down.
3. Monetary — total revenue over the same window, excluding shipping and refunded orders.

Two details that change the answer: exclude refunded and returned-unpaid orders from frequency and value, or your best customer may be somebody who sends everything back; and recompute monthly, because every score is relative to a population that moves.

The four groups worth having

Collapse the grid. These four cover nearly every decision you will make:

• Champions — R 4–5, F 4–5. Recent and frequent. Protect them: early access, no discount, and never a win-back email.
• At risk — R 1–2, F 4–5. Used to buy often, has not lately. This is the highest-value group in the whole model, because the revenue is proven and it is leaving.
• New — R 4–5, F 1. Recent, single purchase. The whole job here is the second order.
• Lapsed — R 1–2, F 1–2. One purchase, long ago. Worth one honest sequence, then sunsetting.

Everybody else sits in the middle and gets your ordinary programme. That is fine. The point of RFM is not to classify all of them; it is to find the two groups that deserve different treatment.

What to send each one

• Champions: new arrivals first, restock alerts, an occasional thank-you with no offer attached. Discounting this group is pure margin given away.
• At risk: a message that acknowledges the gap without a coupon. "We have not seen you since March" outperforms 15 % off, and it does not reprice your catalogue.
• New: the post-purchase sequence — how to use it, then the products people who bought this buy next.
• Lapsed: one tiered win-back, then stop marketing to them. Dormant addresses are where spam complaints come from.

Doing it in the database, not in a spreadsheet

The spreadsheet version breaks the moment somebody is on holiday. RFM belongs where the orders are, recomputed on a schedule, exposed as a segment you can send to.

1. One query per dimension, bucketing by ntile(5) over the customer set.
2. Materialise the scores against the customer record so a segment can filter on them without recomputing.
3. Recompute monthly and keep last month's scores — the movement between groups is the actual report.

If your marketing tool cannot compute this from live order data, the fallback is honest: sort by last order date and total revenue, draw the lines yourself, and accept that the answer ages from the day you export it.

What to watch once it is running

The useful number is not how many champions you have. It is the flow between groups, month on month.

• New → repeat conversion rate. The single best measure of whether acquisition is working.
• Champions → at risk. If this rises, something changed in product, delivery or frequency. It is an early warning that arrives months before revenue shows it.
• At risk → champions. Whether your win-back actually wins back, measured against a holdout rather than against hope.