xmetricRecipes

Recipe Tableau and SQL

Which customers are my best, and which am I about to lose?

Give each customer three numbers: days since their last order, number of orders, and total spend. Score each 1 to 5, then name segments from the scores. At this yarn shop, 30 of the 76 biggest spenders haven't ordered in over 180 days: a ranking by spend alone would call them your best customers.

Updated

The data

Purl & Pine yarn shop, online orders January 2025 to September 2026

Synthetic dataorders.csv

An online yarn shop with 380 customers. A group of big-spending knitting-club regulars bought heavily through 2025, then went quiet.

order_idorder_datecustomer_idcustomerrevenue
PP-300012025-01-01C0096Moira Berg97.52
PP-300022025-01-01C0099Yara Tanaka93.85
PP-300032025-01-01C0323Mira Young64.26
PP-300042025-01-02C0044Ava Patel47.7

Step 1

Three numbers per customer

One dot per customer. Recency is counted from the last order in the file, not from today. The cluster at the top right is the surprise: customers who spent the most and then stopped coming back.

Tableau

Recency days: DATEDIFF('day', { FIXED [customer_id] : MAX([order_date]) }, { FIXED : MAX([order_date]) }); Frequency: { FIXED [customer_id] : COUNTD([order_id]) }; Monetary: { FIXED [customer_id] : SUM([revenue]) }

SQL

WITH c AS (
  SELECT
    customer_id,
    ANY_VALUE(customer) AS customer,
    date_diff('day', MAX(order_date), (
      SELECT MAX(order_date)
      FROM input
    )) AS recency_days,
    COUNT(DISTINCT order_id) AS frequency,
    SUM(revenue) AS monetary
  FROM input
  GROUP BY customer_id
)
SELECT customer, recency_days, frequency, monetary
FROM c
ORDER BY monetary DESC

Rows

CustomerRecency daysFrequencyRevenue
Sana Duarte2215$1,625
Vera Lind3414$1,591
Tess Duarte2314$1,586
Olive Ito2014$1,550
Grace Walsh1415$1,493
Iris Lind813$1,375
Ruth Lind2313$1,347
Lucia Lind28011$1,321
Olive Moreau2212$1,317
Vera Olsen2012$1,302
Sofia Patel312$1,287
Tilde Fischer2212$1,252
Aiko Zhou1612$1,235
Lucia Novak512$1,209
Vera Patel011$1,193
Ingrid Garcia011$1,186
Tess Zhou30510$1,185
Olive Rossi27810$1,156
Ava Okafor31710$1,142
Iris Ito26911$1,140
Moira Berg25210$1,120
Aiko Novak510$1,108
Kit Murphy2719$1,106
Wren Duarte3109$1,006
Ava Quist169$1,003
Lucia Quist210$1,001
Rhea Berg2849$994
Aiko Duarte3309$972
Noor Rossi349$962
Pia Olsen99$955
Uma Garcia239$953
Yara Tanaka3738$947
Nell Larsen2878$917
Tess Larsen3118$912
Elena Ito39$894
Grace Rossi78$891
Elena Murphy2678$872
Wren Novak398$870
Noor Ueda2968$866
Leila Murphy28$854
Moira Jensen3658$846
Ingrid Murphy3417$835
Quinn Rossi48$816
Clara Olsen48$799
Uma Ueda288$798
Joan Evans248$776
Noor Moreau208$769
Sky Okafor3096$758
Sofia Fischer2966$732
Kit Berg2336$700

First 50 of 380 rows.

Step 2

Score each number 1 to 5

Recency and spend use quintile cut-offs: the 20th, 40th, 60th and 80th percentile across customers, so each score holds about a fifth of them. Recency is reversed: fewer days scores higher. Frequency uses fixed tiers instead, because almost half the customers ordered exactly once and quintiles can't split a tie.

Tableau

R score: IF [Recency days] <= { FIXED : PERCENTILE({ INCLUDE [customer_id] : MAX([Recency days]) }, 0.2) } THEN 5
ELSEIF ... 0.4 ... THEN 4
ELSEIF ... 0.6 ... THEN 3
ELSEIF ... 0.8 ... THEN 2
ELSE 1
END; F score: IF [Frequency] >= 6 THEN 5
ELSEIF [Frequency] >= 4 THEN 4
ELSEIF [Frequency] = 3 THEN 3
ELSEIF [Frequency] = 2 THEN 2
ELSE 1
END; M score: as R, on { INCLUDE [customer_id] : SUM([revenue]) }, with >= and 5 at the top

SQL

WITH c AS (
  SELECT
    customer_id,
    ANY_VALUE(customer) AS customer,
    date_diff('day', MAX(order_date), (
      SELECT MAX(order_date)
      FROM input
    )) AS recency_days,
    COUNT(DISTINCT order_id) AS frequency,
    SUM(revenue) AS monetary
  FROM input
  GROUP BY customer_id
), q AS (
  SELECT
    quantile_cont(recency_days, [0.2, 0.4, 0.6, 0.8]) AS r,
    quantile_cont(monetary, [0.2, 0.4, 0.6, 0.8]) AS m
  FROM c
), s AS (
  SELECT
    c.*,
    CASE
      WHEN recency_days <= r [1] THEN 5
      WHEN recency_days <= r [2] THEN 4
      WHEN recency_days <= r [3] THEN 3
      WHEN recency_days <= r [4] THEN 2
      ELSE 1
      END AS r_score,
    CASE
      WHEN frequency >= 6 THEN 5
      WHEN frequency >= 4 THEN 4
      WHEN frequency = 3 THEN 3
      WHEN frequency = 2 THEN 2
      ELSE 1
      END AS f_score,
    CASE
      WHEN monetary >= m [4] THEN 5
      WHEN monetary >= m [3] THEN 4
      WHEN monetary >= m [2] THEN 3
      WHEN monetary >= m [1] THEN 2
      ELSE 1
      END AS m_score
  FROM c CROSS JOIN q
)
SELECT
  'Recency' AS score_of,
  r_score AS score,
  COUNT(*) : : INTEGER AS customers
FROM s
GROUP BY 2
UNION ALL 
SELECT 'Frequency', f_score, COUNT(*) : : INTEGER
FROM s
GROUP BY 2
UNION ALL 
SELECT 'Monetary', m_score, COUNT(*) : : INTEGER
FROM s
GROUP BY 2
ORDER BY 1, 2

Rows

Score ofScoreCustomers
Frequency1181
Frequency253
Frequency341
Frequency413
Frequency592
Monetary176
Monetary276
Monetary376
Monetary476
Monetary576
Recency175
Recency277
Recency375
Recency476
Recency577

Common mistake

Quintiles on frequency

NTILE(5) forces five equal groups, so customers with the same single order land in scores 1, 2 and 3 by chance, and a six-order customer can be a 4 or a 5. Use fixed tiers for counts with heavy ties.

Wrong
Point at the line to read it.
050100150Rows in this order →

SQL

WITH c AS (
  SELECT customer_id, COUNT(DISTINCT order_id) AS frequency
  FROM input
  GROUP BY customer_id
), n AS (
  SELECT frequency, NTILE(5) OVER (ORDER BY frequency) AS f_score
  FROM c
)
SELECT
  f_score,
  MIN(frequency) AS fewest_orders,
  MAX(frequency) AS most_orders,
  COUNT(*) : : INTEGER AS customers
FROM n
GROUP BY f_score
ORDER BY f_score
Right
Point at the line to read it.
050100150Rows in this order →

SQL

WITH c AS (
  SELECT customer_id, COUNT(DISTINCT order_id) AS frequency
  FROM input
  GROUP BY customer_id
), n AS (
  SELECT
    frequency,
    CASE
      WHEN frequency >= 6 THEN 5
      WHEN frequency >= 4 THEN 4
      WHEN frequency = 3 THEN 3
      WHEN frequency = 2 THEN 2
      ELSE 1
      END AS f_score
  FROM c
)
SELECT
  f_score,
  MIN(frequency) AS fewest_orders,
  MAX(frequency) AS most_orders,
  COUNT(*) : : INTEGER AS customers
FROM n
GROUP BY f_score
ORDER BY f_score

Step 3

Name segments from the scores

Rules are checked in order and each customer gets the first one they match. At risk means: used to buy often, hasn't lately. Here it is the second-biggest segment by revenue.

Tableau

IF [R score] >= 4 AND [F score] >= 4 AND [M score] >= 4 THEN 'Champions'
ELSEIF [R score] >= 3 AND [F score] >= 4 THEN 'Loyal'
ELSEIF [R score] >= 4 AND [F score] = 1 THEN 'New'
ELSEIF [R score] <= 2 AND [F score] >= 3 THEN 'At risk'
ELSEIF [R score] <= 2 THEN 'Hibernating'
ELSE 'Needs attention'
END

SQL

WITH c AS (
  SELECT
    customer_id,
    ANY_VALUE(customer) AS customer,
    date_diff('day', MAX(order_date), (
      SELECT MAX(order_date)
      FROM input
    )) AS recency_days,
    COUNT(DISTINCT order_id) AS frequency,
    SUM(revenue) AS monetary
  FROM input
  GROUP BY customer_id
), q AS (
  SELECT
    quantile_cont(recency_days, [0.2, 0.4, 0.6, 0.8]) AS r,
    quantile_cont(monetary, [0.2, 0.4, 0.6, 0.8]) AS m
  FROM c
), s AS (
  SELECT
    c.*,
    CASE
      WHEN recency_days <= r [1] THEN 5
      WHEN recency_days <= r [2] THEN 4
      WHEN recency_days <= r [3] THEN 3
      WHEN recency_days <= r [4] THEN 2
      ELSE 1
      END AS r_score,
    CASE
      WHEN frequency >= 6 THEN 5
      WHEN frequency >= 4 THEN 4
      WHEN frequency = 3 THEN 3
      WHEN frequency = 2 THEN 2
      ELSE 1
      END AS f_score,
    CASE
      WHEN monetary >= m [4] THEN 5
      WHEN monetary >= m [3] THEN 4
      WHEN monetary >= m [2] THEN 3
      WHEN monetary >= m [1] THEN 2
      ELSE 1
      END AS m_score
  FROM c CROSS JOIN q
), g AS (
  SELECT
    *,
    CASE
      WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
      WHEN r_score >= 3 AND f_score >= 4 THEN 'Loyal'
      WHEN r_score >= 4 AND f_score = 1 THEN 'New'
      WHEN r_score <= 2 AND f_score >= 3 THEN 'At risk'
      WHEN r_score <= 2 THEN 'Hibernating'
      ELSE 'Needs attention'
      END AS segment
  FROM s
)
SELECT
  segment,
  COUNT(*) : : INTEGER AS customers,
  SUM(monetary) AS revenue,
  SUM(monetary)
    / (
    SELECT SUM(monetary)
    FROM g
  ) AS share_of_revenue
FROM g
GROUP BY segment
ORDER BY revenue DESC

Rows

SegmentCustomersMonetaryShare of revenue
Champions6346,20549.3%
At risk3826,85728.7%
Needs attention1049,76610.4%
Hibernating1144,9975.3%
Loyal123,824.1%
New492,0662.2%

Are my biggest spenders my best customers?

Not necessarily. Champions carry 49.3% of revenue here, but At risk is next with 28.7%: customers who used to buy often and have stopped. Rank by spend alone and they sit at the top of your list, looking safe. Recency is the number that tells you they're leaving.

Step 4

Check your top spenders for silence

Take the top fifth by spend, the list a spend-only ranking would hand you as "best customers", and split it by days since the last order. Move the slider: the quiet share stays large well past half a year.

Tableau

IF [Recency days] > 180 THEN 'Quiet'
ELSE 'Active'
END
// Filter: [M score] = 5

SQL

WITH c AS (
  SELECT
    customer_id,
    ANY_VALUE(customer) AS customer,
    date_diff('day', MAX(order_date), (
      SELECT MAX(order_date)
      FROM input
    )) AS recency_days,
    COUNT(DISTINCT order_id) AS frequency,
    SUM(revenue) AS monetary
  FROM input
  GROUP BY customer_id
), q AS (
  SELECT
    quantile_cont(recency_days, [0.2, 0.4, 0.6, 0.8]) AS r,
    quantile_cont(monetary, [0.2, 0.4, 0.6, 0.8]) AS m
  FROM c
), s AS (
  SELECT
    c.*,
    CASE
      WHEN recency_days <= r [1] THEN 5
      WHEN recency_days <= r [2] THEN 4
      WHEN recency_days <= r [3] THEN 3
      WHEN recency_days <= r [4] THEN 2
      ELSE 1
      END AS r_score,
    CASE
      WHEN frequency >= 6 THEN 5
      WHEN frequency >= 4 THEN 4
      WHEN frequency = 3 THEN 3
      WHEN frequency = 2 THEN 2
      ELSE 1
      END AS f_score,
    CASE
      WHEN monetary >= m [4] THEN 5
      WHEN monetary >= m [3] THEN 4
      WHEN monetary >= m [2] THEN 3
      WHEN monetary >= m [1] THEN 2
      ELSE 1
      END AS m_score
  FROM c CROSS JOIN q
), g AS (
  SELECT
    *,
    CASE
      WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
      WHEN r_score >= 3 AND f_score >= 4 THEN 'Loyal'
      WHEN r_score >= 4 AND f_score = 1 THEN 'New'
      WHEN r_score <= 2 AND f_score >= 3 THEN 'At risk'
      WHEN r_score <= 2 THEN 'Hibernating'
      ELSE 'Needs attention'
      END AS segment
  FROM s
)
SELECT
  CASE
    WHEN recency_days > 180 THEN 'Quiet for over {knob.quiet_days} days'
    ELSE 'Ordered in the last {knob.quiet_days} days'
    END AS status,
  COUNT(*) : : INTEGER AS customers,
  SUM(monetary) AS revenue,
  SUM(monetary) / SUM(SUM(monetary)) OVER () AS share_of_top_spend
FROM g
WHERE m_score = 5
GROUP BY status
ORDER BY status

Rows

StatusCustomersMonetaryShare of top spend
Ordered in the last 180 days46$40,70961.3%
Quiet for over 180 days30$25,70038.7%
60360
Point at the line to read it.
1Ordered in the last 180 days$40,7092Quiet for over 180 days$25,700

How many of the top spenders have gone quiet?

30 of the 76 customers in the top fifth by spend haven't ordered in over 180 days, and they account for 38.7% of that group's spend. That is the win-back list, and it is worth more than chasing new one-order customers.

Why percentiles across customers, not rows?

{ FIXED : PERCENTILE([Recency days], 0.2) } looks right, but it takes the percentile across order rows, so a customer with twelve orders counts twelve times and the cut-offs lean toward frequent buyers. Wrapping the value in { INCLUDE [customer_id] : MAX([Recency days]) } gives one value per customer first. The SQL does the same by computing cut-offs from a one-row-per-customer table; Tableau's PERCENTILE and DuckDB's quantile_cont both interpolate, so the cut-offs match.

Common mistake

Measuring recency from today

current_date (or TODAY() in Tableau) adds the days since the export to everyone. The scores drift every day the file sits there, and a historical snapshot scores everyone as lapsed. Anchor on the file's last order date.

WrongStarts with C0011, C0037, C0169…
Point at a bar to read it.
0102030C0011C0037C0169C0009C0174

Tableau

DATEDIFF('day', { FIXED [customer_id] : MAX([order_date]) }, TODAY())

SQL

SELECT
  customer_id,
  date_diff('day', MAX(order_date), current_date) AS recency_days
FROM input
GROUP BY customer_id
ORDER BY recency_days
LIMIT 5
RightStarts with C0011, C0037, C0169…
Point at a bar to read it.
00.250.50.751C0011C0037C0169C0009C0174

Tableau

DATEDIFF('day', { FIXED [customer_id] : MAX([order_date]) }, { FIXED : MAX([order_date]) })

SQL

SELECT
  customer_id,
  date_diff('day', MAX(order_date), (
    SELECT MAX(order_date)
    FROM input
  )) AS recency_days
FROM input
GROUP BY customer_id
ORDER BY recency_days
LIMIT 5

Run it on your own data

You need a customer id, an order id, an order date and an amount. Drop your CSV and pick the columns; the steps recompute in your browser, and nothing is uploaded.

Drop a CSV anywhere on this page

Up to 10,000 rows. The sample, orders.csv, shows the shape.

Questions

What does RFM stand for?

Recency (how recently a customer bought), frequency (how often) and monetary value (how much they spent in total). Each says something different: spend tells you who mattered, recency tells you who still does.

Why not measure recency from today?

An export from last month, or a historical year, adds the same idle days to everyone and pushes recent buyers down. Measure from the last order date in the file and the scores work for any snapshot.

Should monetary be total spend or average order value?

Standard RFM uses total spend. Because total spend grows with frequency, some teams use average order value instead to keep F and M independent: { FIXED [customer_id] : SUM([revenue]) / COUNTD([order_id]) }.

Are these six segment names a standard?

They are one common scheme, not a rule. Name segments after the action you'll take: early access for Champions, a win-back offer for At risk, an onboarding sequence for New. Keep the rules ordered so each customer gets the first segment they match.

Sources