Module 8: Patterns analysts use every week · Lesson 8.2 · about 35 min
Cohort retention
Group customers by the month of their first order, then count how many come back one, two and three months later. Build the classic retention table on Northbay, one CTE at a time.
Module 8 · about 35 minutes · read first: 5.4, 7.1. You’ll be able to build a cohort retention table in SQL: one row per starting month, one column per month since the start, and read what it says.
In everyday terms: a school reunion list. You follow “the class of 2024” and count how many still turn up each year, instead of counting everyone in the hall.
1. Why this matters
Total customers can grow while the business quietly leaks: lots of new customers who never come back look fine in a total. A cohort analysis follows each group of customers from the month they started, so you can see whether people stay. Subscription, retail and app companies all use it, and “write a retention query” is a favourite take-home task in analyst interviews.
2. The idea in three steps
A cohort is a group of customers who share a starting point. Retention is the share of a cohort that is still active some time later. Every cohort query follows the same three steps:
- Assign a cohort to each customer: the month of their first completed order (or their sign-up month).
- Measure activity: for every order, how many months after the cohort month it happened (the month number, 0 for the first month).
- Count and divide: active customers per cohort and month number, divided by the cohort’s size at month 0.
Keep each step in its own CTE (Lesson 5.4). It is far easier to check.
3. Step 1: each customer’s cohort
Guests have no customer_id and cancelled orders don’t count, so filter both out first.
-- try
WITH firsts AS (
SELECT customer_id, MIN(order_date) AS first_date
FROM orders
WHERE status = 'completed' AND customer_id IS NOT NULL
GROUP BY customer_id
)
SELECT strftime('%Y-%m', first_date) AS cohort, COUNT(*) AS customers
FROM firsts
GROUP BY cohort
ORDER BY cohort
The January 2024 cohort has 106 customers, February 63, March 78. Then the cohorts shrink fast: by 2025 only a handful of customers are new each month. Why? Northbay’s loyalty members have been joining since 2021, but the orders table starts in January 2024. Anyone who was already shopping shows up with a “first” order early in 2024. This is left censoring: the data cuts off the start of people’s history. In real work, either use the true sign-up date or say clearly that the first cohorts include older customers.
4. Step 2: the month number
Subtracting two dates gives days, not months. To count calendar months, turn each date into a month index (year × 12 + month) and subtract.
-- try
WITH firsts AS (
SELECT customer_id, MIN(order_date) AS first_date
FROM orders
WHERE status = 'completed' AND customer_id IS NOT NULL
GROUP BY customer_id
)
SELECT o.customer_id, f.first_date, o.order_date,
(CAST(strftime('%Y', o.order_date) AS INTEGER) - CAST(strftime('%Y', f.first_date) AS INTEGER)) * 12
+ (CAST(strftime('%m', o.order_date) AS INTEGER) - CAST(strftime('%m', f.first_date) AS INTEGER)) AS month_number
FROM orders o
JOIN firsts f ON f.customer_id = o.customer_id
WHERE o.status = 'completed' AND o.customer_id = 42
ORDER BY o.order_date
Customer 42’s first two orders were both in September 2024, so both are month 0. The later ones are months 10, 12 and 15. (SQL Server: DATEDIFF(month, first_date, order_date) counts month boundaries the same way. PostgreSQL: (extract(year from age(...)) * 12 + ...) or the same year × 12 + month trick.)
5. Step 3: count, divide and pivot
Now count distinct customers per cohort and month number. A customer who ordered three times in month 2 is still one retained customer. Then turn month numbers into columns with COUNT(DISTINCT CASE ...) (this is pivoting, Lesson 8.6).
-- try
WITH firsts AS (
SELECT customer_id, MIN(order_date) AS first_date
FROM orders
WHERE status = 'completed' AND customer_id IS NOT NULL
GROUP BY customer_id
),
activity AS (
SELECT DISTINCT o.customer_id,
strftime('%Y-%m', f.first_date) AS cohort,
(CAST(strftime('%Y', o.order_date) AS INTEGER) - CAST(strftime('%Y', f.first_date) AS INTEGER)) * 12
+ (CAST(strftime('%m', o.order_date) AS INTEGER) - CAST(strftime('%m', f.first_date) AS INTEGER)) AS month_number
FROM orders o
JOIN firsts f ON f.customer_id = o.customer_id
WHERE o.status = 'completed'
)
SELECT cohort,
COUNT(DISTINCT CASE WHEN month_number = 0 THEN customer_id END) AS size,
ROUND(100.0 * COUNT(DISTINCT CASE WHEN month_number = 1 THEN customer_id END)
/ COUNT(DISTINCT CASE WHEN month_number = 0 THEN customer_id END), 1) AS m1_pct,
ROUND(100.0 * COUNT(DISTINCT CASE WHEN month_number = 2 THEN customer_id END)
/ COUNT(DISTINCT CASE WHEN month_number = 0 THEN customer_id END), 1) AS m2_pct,
ROUND(100.0 * COUNT(DISTINCT CASE WHEN month_number = 3 THEN customer_id END)
/ COUNT(DISTINCT CASE WHEN month_number = 0 THEN customer_id END), 1) AS m3_pct,
ROUND(100.0 * COUNT(DISTINCT CASE WHEN month_number = 6 THEN customer_id END)
/ COUNT(DISTINCT CASE WHEN month_number = 0 THEN customer_id END), 1) AS m6_pct,
ROUND(100.0 * COUNT(DISTINCT CASE WHEN month_number = 12 THEN customer_id END)
/ COUNT(DISTINCT CASE WHEN month_number = 0 THEN customer_id END), 1) AS m12_pct
FROM activity
WHERE cohort <= '2024-06'
GROUP BY cohort
ORDER BY cohort
This is the classic retention table (also called a cohort triangle, because later cohorts have fewer months to fill in). Read it across a row to follow one cohort over time, and down a column to compare cohorts at the same age.
Maths and principle
Principle: a cohort table is a two-way grouping. Each customer gets a fixed label (the cohort) and each order gets a relative time (the month number); then you count distinct customers in every (cohort, month number) cell.
Equation: retention(c, k) = |{ customers in cohort c active in month k }| ÷ |{ customers in cohort c }|. In relational algebra: γcohort, k; COUNT(DISTINCT customer_id)(orders ⋈ firsts). The cohort map is a function: each customer has exactly one first month, which is why MIN() is used, never a join that could give two.
Analogy: a school year group. You follow “the class of 2024” from Grade 9 to Grade 12 and count how many are still enrolled each year, instead of counting the whole school.
Try it: a cohort of 80 has 12 active customers in month 3. What is the month-3 retention? (12 ÷ 80 = 15%.)
- Cohort
- A group of customers who started in the same period, here the month of their first completed order.
- Month number
- How many calendar months after the cohort month an order happened. The first month is 0.
- Retention
- Customers in the cohort active in month k, divided by the cohort size at month 0.
- Left censoring
- The data starts after some customers had already started, so the first cohort looks too big.
- Cumulative retention
- Counts a customer as kept if they came back at any time up to month k. Always higher.
- Physical meaning
- Each customer gets one fixed label (their cohort) and each order gets a relative time (its month number). The query then counts distinct customers in every (cohort, month number) cell and divides by the cell in column 0.
“Divide by last month’s active customers.” That gives a month-to-month rate that can go up and down for odd reasons. Retention always divides by the size of the cohort at month 0, so every number in a row shares the same base and the row can only be read one way.
Watch it happen, one step at a time:
| customer | order_month |
|---|---|
| A | 2025-01 |
| A | 2025-03 |
| B | 2025-01 |
| C | 2025-02 |
| C | 2025-03 |
| customer | cohort |
|---|---|
| A | 2025-01 |
| B | 2025-01 |
| C | 2025-02 |
| customer | cohort | month_no |
|---|---|---|
| A | 2025-01 | 0 |
| A | 2025-01 | 2 |
| B | 2025-01 | 0 |
| C | 2025-02 | 0 |
| C | 2025-02 | 1 |
| cohort | m0 | m1 | m2 |
|---|---|---|---|
| 2025-01 | 2 | 0 | 1 |
| 2025-02 | 1 | 1 | – |
| cohort | size | m1_pct | m2_pct |
|---|---|---|---|
| 2025-01 | 2 | 0 | 50 |
| 2025-02 | 1 | 100 | – |
Any live box with a SELECT has a Show how it runs button that animates the real query on Northbay, row by row.
6. Worked example
Question: do Northbay customers come back?
- The April 2024 cohort has 59 customers.
- In month 1 (May), 11 of them ordered again: 11 ÷ 59 = 18.6%.
- In month 2, 13 ordered: 22.0%. In month 12 (April 2025), 13 again: 22.0%.
- Down the month-1 column, retention rises from 10.4% (January) to 20.4% (June). Part of that is the left-censoring effect: the January cohort is padded with older customers whose “first” order was simply their January shop.
The insight: “About one in five new customers shops again in any given month, and that holds steady a year later.” Steady curves like this are a good sign for a grocery chain; a curve that drops towards zero would mean customers try Northbay once and leave.
There is another common definition: cumulative (or “rolling”) retention, which counts a customer as retained if they came back at any point up to month k. It is always higher. Say which one you used.
7. Practice
-- exercise: For each cohort from January to June 2024, show the cohort size and how many customers placed another completed order within 90 days after their first order date.
-- hint: Keep the firsts CTE. Then, per cohort, SUM(EXISTS (SELECT 1 FROM orders o WHERE ... o.order_date > f.first_date AND o.order_date <= date(f.first_date, '+90 days'))).
WITH firsts AS (
SELECT customer_id, MIN(order_date) AS first_date
FROM orders
WHERE status = 'completed' AND customer_id IS NOT NULL
GROUP BY customer_id
)
SELECT strftime('%Y-%m', first_date) AS cohort, COUNT(*) AS size
FROM firsts
WHERE first_date < '2024-07-01'
GROUP BY cohort
-- answer:
WITH firsts AS (
SELECT customer_id, MIN(order_date) AS first_date
FROM orders
WHERE status = 'completed' AND customer_id IS NOT NULL
GROUP BY customer_id
)
SELECT strftime('%Y-%m', f.first_date) AS cohort,
COUNT(*) AS size,
SUM(EXISTS (SELECT 1 FROM orders o
WHERE o.customer_id = f.customer_id
AND o.status = 'completed'
AND o.order_date > f.first_date
AND o.order_date <= date(f.first_date, '+90 days'))) AS back_in_90
FROM firsts f
WHERE f.first_date < '2024-07-01'
GROUP BY cohort
In January 2024, 40 of 106 (37.7%) came back within 90 days.
-- exercise: Use sign-up cohorts instead: for each year customers joined (joined_on), show the number of members and how many placed a completed order in 2025.
-- hint: Group customers by strftime('%Y', joined_on). Count members with COUNT(*) and active ones with SUM(EXISTS (...)) on orders from 2025-01-01.
SELECT strftime('%Y', joined_on) AS joined_year, COUNT(*) AS members
FROM customers
GROUP BY joined_year
-- answer:
SELECT strftime('%Y', c.joined_on) AS joined_year,
COUNT(*) AS members,
SUM(EXISTS (SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id
AND o.status = 'completed'
AND o.order_date >= '2025-01-01')) AS active_2025
FROM customers c
GROUP BY joined_year
Try it on your own data: put your own orders export into [[sql-playground]] and swap in your table and column names; [[plot-maker]] can draw the retention curves.
- A cohort is a group that shares a starting month; MIN(order_date) per customer assigns it.
- Month number = (year × 12 + month) of the order minus the same for the cohort month.
- Count DISTINCT customers per (cohort, month number), then divide by the month-0 size.
- Read a row across to follow one cohort; read a column down to compare cohorts at the same age.
- The first cohort is often padded by older customers (left censoring): say so.
New words: cohort, retention, month number, retention table, left censoring, cumulative retention.
Check yourself
Sources
- SQLite documentation: Aggregate Functions (COUNT DISTINCT) https://www.sqlite.org/lang_aggfunc.html
- SQLite documentation: The WITH Clause https://www.sqlite.org/lang_with.html
- Microsoft Learn: DATEDIFF (Transact-SQL) https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql
- Croll, A. and Yoskovitz, B. (2013), Lean Analytics, O'Reilly, chapter 7 (cohort analysis)
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. Northbay Retail is a made-up company; its data is generated in your browser. Queries run on your device and nothing you type is sent anywhere.