xmetricRecipes

Recipe Tableau and SQL

How do I build a monthly cohort retention table?

Give every customer a cohort, the month of their first purchase, with { FIXED [customer_id] : MIN([order_date]) }. Then count distinct customers active at each month after that and divide by the cohort's size. At this bookshop the biggest cohort, a promo month of 78 new customers, kept only 10.3% of them three months later, against 37.7% for every other month.

Updated

The data

Lantern Books loyalty-card purchases, 2025

Synthetic databookshop-purchases.csv

An indie bookshop's loyalty card, January to December 2025. In March a 40%-off spring sale signed up more than twice the usual number of new members.

customer_idorder_dateamount
LB-00122025-01-0119.98
LB-00162025-01-0114.99
LB-00202025-01-0125
LB-00322025-01-0145

Step 1

Give every customer a cohort

Each bar counts the customers whose first purchase fell in that month. March towers over the rest: that's the spring sale. In Tableau, save the formula as a field called Cohort month and drag it to Columns, with COUNTD([customer_id]) on Rows.

Tableau

DATETRUNC('month', { FIXED [customer_id] : MIN([order_date]) })

SQL

WITH f AS (
  SELECT
    customer_id,
    date_trunc('month', MIN(order_date)) AS cohort_month
  FROM input
  GROUP BY customer_id
)
SELECT cohort_month, COUNT(*) AS customers
FROM f
GROUP BY cohort_month
ORDER BY cohort_month

Rows

Cohort monthCustomers
Jan 202534
Feb 202530
Mar 202578
Apr 202532
May 202529
Jun 202527
Jul 202531
Aug 202533
Sep 202536

Common mistake

MIN of the date without FIXED

Without FIXED, MIN([order_date]) is computed inside each cell of the view. In a view by month it returns each month's first purchase, so every customer gets a new cohort every month they shop, and the cohorts add up to far more people than exist.

WrongStarts with 2025-01-01 00:00:00, 2025-02-01 00:00:00, 2025-03-01 00:00:00…
Point at the line to read it.
050100Cohort months in this order →

Tableau

DATETRUNC('month', MIN([order_date]))

SQL

SELECT cohort_month, COUNT(*) AS customers
FROM (
  SELECT
    customer_id,
    date_trunc('month', MIN(order_date)) AS cohort_month
  FROM input
  GROUP BY customer_id, date_trunc('month', order_date)
)
GROUP BY cohort_month
ORDER BY cohort_month
RightStarts with 2025-01-01 00:00:00, 2025-02-01 00:00:00, 2025-03-01 00:00:00…
Point at the line to read it.
050100Cohort months in this order →

Tableau

DATETRUNC('month', { FIXED [customer_id] : MIN([order_date]) })

SQL

SELECT cohort_month, COUNT(*) AS customers
FROM (
  SELECT
    customer_id,
    date_trunc('month', MIN(order_date)) AS cohort_month
  FROM input
  GROUP BY customer_id
)
GROUP BY cohort_month
ORDER BY cohort_month

Step 2

Count who came back, month by month

Month 0 is the first purchase, month 1 the next calendar month, and so on. A customer who buys three books in one month counts once: that's why it's COUNTD of customers, never a count of rows. The March row starts big and fades fastest.

Tableau

DATEDIFF('month', [Cohort month], DATETRUNC('month', [order_date]))
// with COUNTD([customer_id])

SQL

WITH m AS (
  SELECT
    DISTINCT customer_id,
    date_trunc('month', order_date) AS month
  FROM input
), f AS (
  SELECT customer_id, MIN(month) AS cohort_month
  FROM m
  GROUP BY customer_id
)
SELECT
  f.cohort_month,
  date_diff('month', f.cohort_month, m.month) AS month_number,
  COUNT(*) AS active_customers
FROM m JOIN f USING (customer_id)
GROUP BY ALL
ORDER BY cohort_month, month_number

Rows

MonthMonth numberActive customers
Jan 2025034
Jan 2025116
Jan 2025215
Jan 2025313
Jan 2025412
Jan 202555
Jan 2025613
Jan 2025717
Jan 2025811
Jan 202597
Jan 2025109
Jan 20251116
Feb 2025030
Feb 2025115
Feb 2025210
Feb 202539
Feb 202548
Feb 202559
Feb 202569
Feb 2025710
Feb 202585
Feb 202599
Feb 2025109
Mar 2025078
Mar 202519
Mar 202529
Mar 202538
Mar 202545
Mar 202555
Mar 202561
Mar 202576
Mar 2025810
Mar 202594
Apr 2025032
Apr 2025120
Apr 2025216
Apr 2025310
Apr 2025411
Apr 2025516
Apr 2025614
Apr 2025710
Apr 202589
May 2025029
May 2025118
May 2025212
May 2025315
May 2025417
May 202559
May 2025612
May 2025710

First 50 of 72 rows.

Step 3

Divide by the cohort's size

The second FIXED gives each cohort its starting size, so every cell is a share of the people who started. The grid runs only to December, the last month in the file: the empty corner is the future, not a zero. In SQL the generate_series scaffold keeps any observed month with no returns as a real 0.

Tableau

COUNTD([customer_id]) / MIN({ FIXED [Cohort month] : COUNTD([customer_id]) })

SQL

WITH m AS (
  SELECT
    DISTINCT customer_id,
    date_trunc('month', order_date) AS month
  FROM input
), f AS (
  SELECT customer_id, MIN(month) AS cohort_month
  FROM m
  GROUP BY customer_id
), s AS (
  SELECT cohort_month, COUNT(*) AS customers
  FROM f
  GROUP BY cohort_month
), a AS (
  SELECT
    f.cohort_month,
    date_diff('month', f.cohort_month, m.month) AS month_number,
    COUNT(*) AS active
  FROM m JOIN f USING (customer_id)
  GROUP BY ALL
), g AS (
  SELECT s.cohort_month, s.customers, n AS month_number
  FROM s, generate_series(0, date_diff('month', s.cohort_month, (
    SELECT MAX(month)
    FROM m
  ))) AS t(n)
)
SELECT
  g.cohort_month,
  g.month_number,
  g.customers,
  COALESCE(a.active, 0) AS active,
  COALESCE(a.active, 0) / g.customers AS retention
FROM g LEFT JOIN a USING (cohort_month, month_number)
ORDER BY cohort_month, month_number

Rows

MonthMonth numberCustomersActiveRetention
Jan 202503434100%
Jan 20251341647.1%
Jan 20252341544.1%
Jan 20253341338.2%
Jan 20254341235.3%
Jan 2025534514.7%
Jan 20256341338.2%
Jan 20257341750%
Jan 20258341132.4%
Jan 2025934720.6%
Jan 20251034926.5%
Jan 202511341647.1%
Feb 202503030100%
Feb 20251301550%
Feb 20252301033.3%
Feb 2025330930%
Feb 2025430826.7%
Feb 2025530930%
Feb 2025630930%
Feb 20257301033.3%
Feb 2025830516.7%
Feb 2025930930%
Feb 20251030930%
Mar 202507878100%
Mar 2025178911.5%
Mar 2025278911.5%
Mar 2025378810.3%
Mar 202547856.4%
Mar 202557856.4%
Mar 202567811.3%
Mar 202577867.7%
Mar 20258781012.8%
Mar 202597845.1%
Apr 202503232100%
Apr 20251322062.5%
Apr 20252321650%
Apr 20253321031.3%
Apr 20254321134.4%
Apr 20255321650%
Apr 20256321443.8%
Apr 20257321031.3%
Apr 2025832928.1%
May 202502929100%
May 20251291862.1%
May 20252291241.4%
May 20253291551.7%
May 20254291758.6%
May 2025529931%
May 20256291241.4%
May 20257291034.5%

First 50 of 72 rows.

Did the spring sale bring in loyal customers?

Point at a cell to read it.
Jan 2025Feb 2025Mar 2025Apr 2025May 2025Jun 2025Jul 2025Aug 2025Sep 202501234567891011Month number →100%47%44%38%35%15%38%50%32%21%26%47%100%50%33%30%27%30%30%33%17%30%30%100%12%12%10%6%6%1%8%13%5%100%63%50%31%34%50%44%31%28%100%62%41%52%59%31%41%34%100%74%56%48%41%30%30%100%58%39%42%42%52%100%61%42%27%33%100%44%28%36%
0%25%50%75%100%

No. March brought in 78 new members, more than twice February's 30. Three months on, March had 8 still buying and February had 9: the biggest cohort of the year kept fewer regulars than one of the smallest. A discount that big mostly attracts people who came for the discount.

Step 4

Compare cohorts at the same age

One column of the heatmap, as bars. Only cohorts old enough to have reached that age appear; slide the age up and the newest months drop out. Whatever the age, March sits at the bottom.

Tableau

// filter [Month number] = {knob.age}
COUNTD([customer_id]) / MIN({ FIXED [Cohort month] : COUNTD([customer_id]) })

SQL

WITH m AS (
  SELECT
    DISTINCT customer_id,
    date_trunc('month', order_date) AS month
  FROM input
), f AS (
  SELECT customer_id, MIN(month) AS cohort_month
  FROM m
  GROUP BY customer_id
), s AS (
  SELECT cohort_month, COUNT(*) AS customers
  FROM f
  GROUP BY cohort_month
), a AS (
  SELECT f.cohort_month, COUNT(*) AS active
  FROM m JOIN f USING (customer_id)
  WHERE date_diff('month', f.cohort_month, m.month) = 3
  GROUP BY f.cohort_month
)
SELECT
  s.cohort_month,
  s.customers,
  COALESCE(a.active, 0) AS active,
  COALESCE(a.active, 0) / s.customers AS retention
FROM s LEFT JOIN a USING (cohort_month)
WHERE date_diff('month', s.cohort_month, (
  SELECT MAX(month)
  FROM m
)) >= 3
ORDER BY s.cohort_month

Rows

MonthCustomersActiveRetention
Jan 2025341338.2%
Feb 202530930%
Mar 202578810.3%
Apr 2025321031.3%
May 2025291551.7%
Jun 2025271348.1%
Jul 2025311341.9%
Aug 202533927.3%
Sep 2025361336.1%
19
Point at the line to read it.
12025-05-01 00:00:0051.7%22025-06-01 00:00:0048.1%32025-07-01 00:00:0041.9%42025-01-01 00:00:0038.2%52025-09-01 00:00:0036.1%62025-04-01 00:00:0031.3%72025-02-01 00:00:0030%82025-08-01 00:00:0027.3%92025-03-01 00:00:0010.3%

At month 3, the March cohort retains 10.3%; every other cohort together retains 37.7%. Move the slider: March stays at the bottom at every age. That's why a cohort table beats one blended retention rate. Mix a big promo month into the average and retention looks like it fell, when the regular customers behave exactly as before.

Common mistake

Counting purchases instead of customers

Loyal customers buy more than once a month. Count rows and month 0 alone comes out above 100%, because the numerator counts books while the denominator counts people.

WrongStarts with 2025-01-01 00:00:00, 2025-02-01 00:00:00, 2025-03-01 00:00:00…
Point at the line to read it.
00.511.5Cohort months in this order →

Tableau

COUNT([customer_id]) / MIN({ FIXED [Cohort month] : COUNTD([customer_id]) })

SQL

WITH f AS (
  SELECT
    customer_id,
    date_trunc('month', MIN(order_date)) AS cohort_month
  FROM input
  GROUP BY customer_id
), s AS (
  SELECT cohort_month, COUNT(*) AS customers
  FROM f
  GROUP BY cohort_month
)
SELECT
  f.cohort_month,
  COUNT(*) / MIN(s.customers) AS month0_retention
FROM input i JOIN f USING (customer_id) JOIN s USING (cohort_month)
WHERE date_trunc('month', i.order_date) = f.cohort_month
GROUP BY f.cohort_month
ORDER BY f.cohort_month
RightStarts with 2025-01-01 00:00:00, 2025-02-01 00:00:00, 2025-03-01 00:00:00…
Point at the line to read it.
00.511.5Cohort months in this order →

Tableau

COUNTD([customer_id]) / MIN({ FIXED [Cohort month] : COUNTD([customer_id]) })

SQL

WITH f AS (
  SELECT
    customer_id,
    date_trunc('month', MIN(order_date)) AS cohort_month
  FROM input
  GROUP BY customer_id
), s AS (
  SELECT cohort_month, COUNT(*) AS customers
  FROM f
  GROUP BY cohort_month
)
SELECT
  f.cohort_month,
  COUNT(DISTINCT i.customer_id)
    / MIN(s.customers) AS month0_retention
FROM input i JOIN f USING (customer_id) JOIN s USING (cohort_month)
WHERE date_trunc('month', i.order_date) = f.cohort_month
GROUP BY f.cohort_month
ORDER BY f.cohort_month

Why is the bottom-right corner empty?

Because those months haven't happened. The September cohort has three months of history, the January cohort eleven. An empty cell is the future; a zero is an observed month where nobody came back.

Common mistake

Reading the future as zero

The July cohort has only five months of history. Fill its month 6 with a zero and the average retention curve dives at the end, as if loyalty collapsed: month 9 reads 6.1% instead of 14.1%. Average each age only over the cohorts that have reached it.

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

SQL

WITH m AS (
  SELECT
    DISTINCT customer_id,
    date_trunc('month', order_date) AS month
  FROM input
), f AS (
  SELECT customer_id, MIN(month) AS cohort_month
  FROM m
  GROUP BY customer_id
), s AS (
  SELECT cohort_month, COUNT(*) AS customers
  FROM f
  GROUP BY cohort_month
), a AS (
  SELECT
    f.cohort_month,
    date_diff('month', f.cohort_month, m.month) AS month_number,
    COUNT(*) AS active
  FROM m JOIN f USING (customer_id)
  GROUP BY ALL
), g AS (
  SELECT s.cohort_month, s.customers, n AS month_number
  FROM s, generate_series(0, 9) AS t(n)
)
SELECT
  month_number,
  SUM(COALESCE(a.active, 0)) / SUM(g.customers) AS retention
FROM g LEFT JOIN a USING (cohort_month, month_number)
GROUP BY month_number
ORDER BY month_number
Right
Point at the line to read it.
00.250.50.751Rows in this order →

SQL

WITH m AS (
  SELECT
    DISTINCT customer_id,
    date_trunc('month', order_date) AS month
  FROM input
), f AS (
  SELECT customer_id, MIN(month) AS cohort_month
  FROM m
  GROUP BY customer_id
), s AS (
  SELECT cohort_month, COUNT(*) AS customers
  FROM f
  GROUP BY cohort_month
), a AS (
  SELECT
    f.cohort_month,
    date_diff('month', f.cohort_month, m.month) AS month_number,
    COUNT(*) AS active
  FROM m JOIN f USING (customer_id)
  GROUP BY ALL
), g AS (
  SELECT s.cohort_month, s.customers, n AS month_number
  FROM s, generate_series(0, date_diff('month', s.cohort_month, (
    SELECT MAX(month)
    FROM m
  ))) AS t(n)
  WHERE n <= 9
)
SELECT
  month_number,
  SUM(COALESCE(a.active, 0)) / SUM(g.customers) AS retention
FROM g LEFT JOIN a USING (cohort_month, month_number)
GROUP BY month_number
ORDER BY month_number

Run it on your own data

Any export with a text customer id and a purchase or activity date works, up to 100,000 rows. Use one row per qualifying purchase (drop refunds and cancellations first). The panel below runs the whole file, with an observation end month you choose, and gives you the cohort table as a CSV. It runs in your browser; nothing is uploaded.

How can I run monthly cohort analysis on my export?

Choose your CSV, drop it here or paste rows with headers. Match its columns to the analysis, and xmetric calculates the complete export in this browser. This panel accepts up to 100,000 rows; nothing is uploaded.

Definition. Use the first observed activity month per text customer ID as the cohort. Count each customer once per calendar month. Retention is 100 *active customers/cohort customers. Fill zero-activity ages only through the selected observation end; omit later ages. Filter qualifying events before import. This is observed activity retention, not subscription MRR retention. Worked result record · definition updated .

No own data loaded. Choose your export or try the labeled sample.

Questions

Why does cohort analysis need a FIXED LOD in Tableau?

A customer's first purchase date has to be the same on every one of their rows, whatever the view shows. MIN([order_date]) alone is computed per cell, so in a view by month it returns that month's first date and every purchase looks like a first one. FIXED computes it once per customer, before dimension filters.

Does a date filter change the cohorts?

A regular dimension filter doesn't, because FIXED runs before it. A context filter or a data source filter does: if the export starts in March, a February customer who came back in March becomes a March customer. Keep the full history in the data, or say the cohort means first seen in this file.

Why is an empty cell different from a zero?

A zero means the month was observed and nobody from the cohort came back. An empty cell means the month hasn't happened yet for that cohort. Treat the two the same and the newest cohorts drag every average down.

Calendar months or 30-day windows?

This table uses calendar months: a purchase on January 31 and another on February 1 count as month 0 and month 1. That is the usual choice for monthly reporting. Use day windows (DATEDIFF('day', ...) / 30) when the exact gap matters.

Is this customer retention or revenue retention?

Customer retention: it counts people, not money. For revenue retention, sum each cohort's spend by month and divide by its month-0 spend.

Sources