RFM segmentation

RFM comes from direct mail, where catalogue companies had to decide who got the next expensive mailing. Three facts from the order history did most of the work: how recently someone bought, how often, and how much. Arthur Hughes' Strategic Database Marketing (first published 1994) popularised the version most teams still use: score each of the three in quintiles, 1 to 5, and you get 125 cells from 111 to 555.

• Recency: days since the customer's last order, counted back from a fixed analysis date. Fewer days scores higher.
• Frequency: number of orders in the window. More scores higher.
• Monetary: total spend in the window (some teams use average order value). More scores higher.

RFM ranks customers by what they have done. It doesn't forecast what they'll do. Fader, Hardie and Lee's 2005 paper RFM and CLV is the standard bridge between the two: it uses the same three inputs in a probability model to estimate each customer's future value, and shows that customers with quite different RFM histories can be worth the same. If the question is "how much is this customer worth?", that's lifetime value. If the question is "who should get which email?", RFM is usually enough.

Quintile scoring sorts customers on each measure separately and splits them into five equal groups. IBM's SPSS Modeler calls this independent binning: each measure is ranked without regard to the other two. Here bin 5 is the best: most recent, most frequent, highest spend.

A worked example

Ten customers of an online shop, scored on 1 October 2026 over the previous 12 months. With ten customers, each quintile holds two. The figures are invented to show the method.

Recency first. Sorted by days since the last order, A and B (5 and 12 days) are the most recent fifth and score 5; I and J score 1. Frequency and monetary are sorted the other way, so F's nine orders and £2,300 put F in the top fifth on both.

Customer F is why people use RFM. On spend alone F is the best customer in the table. On RFM, F is a 255: valuable, and gone quiet for 200 days. That's a different email from the one A should get.

Note what the scores are: ranks within this group of customers at this date. A 5 for recency means "in the most recent fifth", not "bought within a week". Rescore next month and the cut-offs move.

Codes like 255 are hard to act on, so teams group them into named segments. There is no standard list. Names, the number of segments and the score rules all vary by tool and by author, so treat any table of segments, including this one, as a starting point.

The rules used in the example above, applied in this order:

For comparison, Klaviyo's RFM report scores each measure 1 to 3 rather than 1 to 5, and uses six groups: Champions, Loyal, Recent, Needs attention, At risk and Inactive, each defined by its own list of score combinations. Its At risk is a customer who hasn't bought recently with a low or medium spending score, which isn't quite the rule above. Same names, different customers. If two teams compare segment sizes, compare the rules first.

Most warehouses can do this in one query. Here is a short orders table:

The query below is PostgreSQL (subtracting two dates gives days; in BigQuery use DATE_DIFF). It builds one row per customer, scores each measure with NTILE(5), then names the segment.

WITH customers AS (
  SELECT
    customer_id,
    DATE '2026-10-01' - MAX(order_date) AS days_since_last,
    COUNT(DISTINCT order_id)            AS orders,
    SUM(amount)                         AS spend
  FROM orders
  WHERE status = 'completed'
    AND order_date >= DATE '2025-10-01'
    AND order_date <  DATE '2026-10-01'
  GROUP BY customer_id
),
scored AS (
  SELECT
    *,
    NTILE(5) OVER (ORDER BY days_since_last DESC) AS r,
    NTILE(5) OVER (ORDER BY orders)               AS f,
    NTILE(5) OVER (ORDER BY spend)                AS m
  FROM customers
)
SELECT
  CASE
    WHEN r >= 4 AND f >= 4 THEN 'Champions'
    WHEN r >= 3 AND f >= 3 THEN 'Loyal'
    WHEN r >= 3            THEN 'New or recent'
    WHEN f >= 3            THEN 'At risk'
    WHEN r = 2             THEN 'Needs attention'
    ELSE 'Lost'
  END                      AS segment,
  COUNT(*)                 AS customers,
  SUM(spend)               AS spend
FROM scored
GROUP BY 1
ORDER BY 2 DESC;

Recency sorts by days descending, so the customers who bought longest ago land in bucket 1 and the most recent in bucket 5. Frequency and monetary sort ascending for the same reason. On the ten customers above it returns Champions 3, Loyal 1, New or recent 2, At risk 2, Lost 2.

NTILE splits rows as evenly as it can. BigQuery's documentation says bucket sizes differ by at most one, with the remainder going to the lowest buckets first. That evenness is exactly the problem with frequency, as the next section explains.

The time window. Lifetime data lets a customer who bought weekly in 2019 score 5 on frequency today. A 12-month window fixes that, but it also caps frequency and drops anyone whose last order fell outside it, so they vanish from Lost rather than appearing in it. Pick the window on purpose, fix the analysis date in the query instead of using CURRENT_DATE, and write both in the output.

Returns and refunds. An order returned in full should count towards neither frequency nor spend. Net partial refunds off the amount. Klaviyo's RFM report, for example, only counts orders with a positive value, so fully discounted orders are left out. If refunds sit in a separate table, join them before you aggregate, or a serial returner will look like a Champion.

One-off B2B orders. A single £40,000 wholesale order makes a customer an M of 5 and an F of 1, and drags the monetary cut-offs up for everyone else. Score trade accounts separately, or score at the company level rather than per contact, and look at the top ten spenders by hand before trusting the cut-offs.

Ties in NTILE. In a shop where most customers have bought once, NTILE still makes five equal groups, so identical one-order customers get split across buckets 1, 2 and 3 by whatever order the database happens to return them in. PostgreSQL's documentation only promises that NTILE divides the rows "as equally as possible"; BigQuery's own example shows tied rows landing in different buckets. Run it twice and a customer can change segment without placing an order. Two fixes:

• Use fixed thresholds for frequency: 1, 2, 3 to 4, 5 to 9, 10 or more orders.
• Use CEIL(5 * CUME_DIST() OVER (ORDER BY orders)). Peers (rows with equal values) always get the same CUME_DIST, so ties stay together. The buckets come out uneven, which is the honest result.

Duplicate customers. Guest checkouts and changed email addresses split one person into several low scorers. Deduplicate before scoring.

Comparing months. Because quintiles are relative, the share of customers scored 5 stays at roughly a fifth whatever happens. To track whether more customers are lapsing, fix the cut-offs once and reuse them.

Claude writes RFM SQL quickly. Left to itself, it will also pick the window, ignore refunds, use NTILE on a frequency column that is mostly ones and present the segment counts without saying any of that. The fix is to make each of those choices yours, and to make it show the counts at every step. More on that pattern in text-to-SQL and prompts for data analysis.

With Count's MCP server connected, Claude can run the query against your warehouse or spreadsheet itself and show the SQL behind every number. Paste this and fill in the brackets:

Build RFM segments from [schema.orders] in [warehouse].
Read only. Analysis date: [2026-10-01]. Window: the 12 months before it.

1. Before writing SQL, tell me which columns you'll use for customer,
   order ID, order date, amount and status, and the grain of the
   table (one row per what). Ask me if any is unclear.
2. Exclude cancelled and fully refunded orders, and net partial
   refunds off the amount. Report how many orders and how much
   revenue each exclusion removed.
3. Show how many customers have 1, 2, 3, 4 and 5+ orders. If more
   than 20% share one value, score frequency with
   CEIL(5 * CUME_DIST() OVER (ORDER BY orders)) instead of NTILE,
   and say so.
4. Score recency, frequency and monetary 1 to 5, with 5 the best.
5. Name segments with exactly these rules, in this order:
   [paste your rules]
6. Run it. Show one table: segment, customers, share, total spend,
   median days since last order.
7. List the 10 highest spenders with their orders and scores, and
   flag any single order over [£ amount].
8. Show every query in full, with the row count after each step.

Step 7 catches the B2B order and the refund you forgot. Step 3 catches the ties. If the segment counts change when you run it twice, step 3 wasn't followed.

In Count, every segment count comes with its Reasoning Chain: the query, the filters and the orders behind it. When the CRM team asks why a customer is At risk, you can show them which orders put them there. For using the segments in your CRM, see AI for CRM data.