RFM Segmentation: Score Every Customer and Act on the Right Ones

Why this matters

Most CRM programmes send the same message to everyone, or segment on demographics that barely predict behaviour. RFM fixes that with three numbers almost every business already has in its order table.

The logic comes from catalogue and direct-mail marketers, who learned long before email that past buying behaviour is the best predictor of the next purchase. A customer who bought last week is far more likely to buy again than one who bought last year. Someone who has bought ten times is more likely than someone who bought once. And the value of a response depends on how much the customer spends.

What makes RFM worth learning in 2026 is not the model itself. It is that RFM gives every team a shared language. “We have 4,200 At Risk customers worth 18% of last year’s revenue” is a sentence finance, CRM and product can all act on.

The framework

The three dimensions

Use a fixed lookback window, usually 12 or 24 months, so old one-off buyers do not dilute the ranking. Use net revenue (after discounts and returns), since COD refusals and returns can make gross spend misleading, especially in India.

From 125 cells to named segments

Three scores of 1 to 5 give 125 cells. Nobody runs 125 campaigns. Map them into about ten named segments driven mostly by R and F, with M used to prioritise inside a segment.

Step-by-step playbook

1. Pull one row per customer. Customer ID, last order date, number of orders and net revenue, all inside the lookback window. Exclude cancelled, refused (COD) and fully returned orders.
2. Choose the scoring method per dimension. Recency and monetary are continuous, so quintiles work. Frequency is lumpy (most customers have one or two orders), so use fixed thresholds such as 1, 2, 3 to 4, 5 to 9, and 10 or more.
3. Score and label. Compute R, F and M scores, then apply the segment rules from the table above. Keep the rules in one shared file so analysts and CRM use the same definitions.
4. Size each segment in customers and revenue. You will usually find a small group of Champions and Loyal customers carrying a large share of revenue. That tells you where a 1 point change matters most.
5. Assign one owner and one action per segment. Each segment gets a goal metric, a journey and a guardrail (for example, maximum discount depth).
6. Automate the refresh. Recompute daily or weekly and sync to your email, push and WhatsApp tools. Trigger journeys on segment changes, especially into At risk and out of New.
7. Track migration monthly. Build a matrix of segment last month versus segment this month. The most important cells are New to Potential loyalist (second order) and Loyal to At risk (early churn signal).
8. Prove incrementality. Keep a random 10% holdout per segment out of promotional sends for a full cycle. Compare revenue per customer, not open rates.

Building it in SQL

-- One row per customer, 24-month lookback, net of refusals and returns
WITH base AS (
  SELECT
    customer_id,
    DATE_DIFF(CURRENT_DATE(), MAX(order_date), DAY) AS recency_days,
    COUNT(DISTINCT order_id)                         AS orders,
    SUM(net_revenue)                                 AS monetary
  FROM orders
  WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 24 MONTH)
    AND status NOT IN ('cancelled', 'rto', 'refunded')
  GROUP BY customer_id
),
scored AS (
  SELECT
    *,
    6 - NTILE(5) OVER (ORDER BY recency_days ASC) AS r_score,  -- most recent = 5
    CASE
      WHEN orders >= 10 THEN 5
      WHEN orders >= 5  THEN 4
      WHEN orders >= 3  THEN 3
      WHEN orders = 2   THEN 2
      ELSE 1
    END AS f_score,                                            -- fixed thresholds
    NTILE(5) OVER (ORDER BY monetary ASC) AS m_score
  FROM base
)
SELECT
  *,
  CASE
    WHEN r_score >= 4 AND f_score >= 4                   THEN 'Champions'
    WHEN r_score = 1 AND f_score = 5 AND m_score >= 4    THEN 'Cant lose them'
    WHEN r_score <= 2 AND f_score >= 3                   THEN 'At risk'
    WHEN r_score >= 3 AND f_score >= 3                   THEN 'Loyal'
    WHEN r_score >= 4 AND f_score BETWEEN 2 AND 3        THEN 'Potential loyalists'
    WHEN r_score = 5 AND f_score = 1                     THEN 'New'
    WHEN r_score = 4 AND f_score = 1                     THEN 'Promising'
    WHEN r_score = 3 AND f_score <= 2                    THEN 'About to sleep'
    WHEN r_score = 2 AND f_score <= 2                    THEN 'Hibernating'
    ELSE 'Lost'
  END AS segment
FROM scored;

The order of the CASE rules matters: specific segments (Can’t lose them) must be tested before the broad ones that would otherwise catch them. The syntax shown is BigQuery. In Postgres, replace the date functions with CURRENT_DATE - MAX(order_date) and CURRENT_DATE - INTERVAL '24 months'.

Building it in a spreadsheet

With one customer per row (recency days in column B, orders in C, revenue in D, 5,000 customers):

• Recency score: =6-MAX(1,ROUNDUP(PERCENTRANK.INC($B$2:$B$5001,B2)*5,0))
• Monetary score: =MAX(1,ROUNDUP(PERCENTRANK.INC($D$2:$D$5001,D2)*5,0))
• Frequency score: =IF(C2>=10,5,IF(C2>=5,4,IF(C2>=3,3,IF(C2=2,2,1))))

Then a lookup table maps each R and F pair to a segment name. A pivot table on segment gives counts and revenue share.

Worked example

Say a D2C coffee brand has 20,000 customers who ordered in the last 24 months and ₹6 crore of net revenue in that window.

After scoring:

The arithmetic for Champions: 1,600 x 12 x ₹900 = ₹1,72,80,000, about ₹1.73 crore, or 29% of revenue from 8% of customers.

Now size the At risk opportunity. These 2,400 customers averaged 5 orders but have gone quiet. Suppose a three-step win-back brings back 15% of them for one order of ₹850, while a 10% holdout shows only 5% would have returned anyway. Incremental return rate = 15% - 5% = 10 points. Incremental orders = 2,400 x 0.90 (the treated share) x 0.10 = 216. Incremental revenue = 216 x ₹850 = ₹1,83,600 from one order each, before counting the repeat orders a recovered regular tends to place.

Compare that with sending the same offer to all 20,000 customers. Champions would redeem it on orders they were already going to place, which is pure margin loss. Segmenting first is what makes the economics work.

B2B SaaS vs D2C: what changes

In SaaS, customers pay on a schedule whether or not they use the product, so purchase recency is useless. Swap in usage.

Segment names change too. “At risk” becomes “high ARR, falling usage”, which goes to customer success with a 60 to 90 day lead on renewal. “Champions” become expansion and case study candidates. “Low ARR, no recent usage” gets automated re-onboarding rather than a human call.

How to measure it

Report migration as a matrix, not a list. A healthy programme moves customers up and to the left over time. If At risk grows faster than Loyal for three months in a row, your retention problem is getting worse, regardless of what the total revenue line says.