Segmenting customers sounds like a big machine-learning project. It doesn't have to be. One of the most useful methods is also one of the oldest, and you can build it in an afternoon with the order data you already have.
It's called RFM, and it asks three questions about every customer.
- Recency: how long since they last bought?
- Frequency: how often have they bought?
- Monetary value: how much have they spent in total?
Someone who bought last week, buys regularly and spends a lot is a very different person to someone who bought once two years ago, even if they both appear as "customers" in your list.
Building it
You need one table of completed orders with a customer id, a date and a value. From it, compute the three numbers per customer. Here's a version in BigQuery-style SQL:
WITH base AS (
SELECT
customer_id,
DATE_DIFF(CURRENT_DATE(), MAX(order_date), DAY) AS recency_days,
COUNT(DISTINCT order_id) AS frequency,
SUM(order_value) AS monetary
FROM orders
WHERE order_status = 'completed'
GROUP BY customer_id
)
SELECT
*,
NTILE(5) OVER (ORDER BY recency_days DESC) AS r_score,
NTILE(5) OVER (ORDER BY frequency) AS f_score,
NTILE(5) OVER (ORDER BY monetary) AS m_score
FROM base
NTILE(5) cuts customers into five equal groups, so each customer gets a score from 1 to 5 on each measure, with 5 as best. Recency is ordered the other way round because fewer days since the last order is better.
One wrinkle. In most shops, a large share of customers have bought exactly once, so frequency has a lot of ties, and NTILE splits them arbitrarily. For frequency I often skip the quintiles and use hand-picked bands instead: one order, two, three to four, five or more. It's a little less tidy and a lot more useful.
Turning scores into groups
A hundred and twenty-five combinations is too many to act on, so collapse them into a handful of named groups. You can adjust the thresholds to suit the business, but a typical set looks like this:
| Group | Rough rule | What they are |
|---|---|---|
| Best customers | High on all three | Recent, frequent, high spend |
| Loyal | High frequency, any recency | Come back often |
| New | Recent, one order | Haven't had the chance to repeat yet |
| Slipping | Used to be frequent, recency falling | Starting to drift away |
| Lapsed | Low recency, once decent value | Gone quiet |
| One-and-done | Low everything | Bought once, long ago |
What to do with them
This is the part that matters, and it's where many segmentation projects stall. A segment is only useful if it changes what you do.
Best customers. Don't discount them. They'd have bought anyway. Early access, a thank-you, or a request for a review fits better.
New customers. The second purchase is the big hurdle. A well-timed message a couple of weeks after the first order, based on what they bought, often does more than any other campaign.
Slipping customers. This is your highest-value group to win back, because they know you. A message that reminds them what they liked, without begging, is a good start.
Lapsed and one-and-done. Try once, cheaply, and then leave them alone. Constant emails to people who stopped caring only train them to ignore you and push up your costs.
Always test. Keep a small control group in each segment that receives nothing, so you can tell whether your effort made any difference.
When to go beyond RFM
RFM is a good first step, not the last. If you have richer data you might cluster customers on behaviour: product categories bought, channel, time of day, discount sensitivity. Algorithms like k-means can find groups you didn't think of. For marketplaces, build buyer and seller cohorts separately, because they behave nothing like each other.
But start simple. A team that acts on six clear groups beats a team with a sophisticated model that nobody uses.
One last thing: be careful with the data. Customers shared their details to buy something. Use them in ways they'd reasonably expect, keep what you hold to what you need, and respect opt-outs.