All lessons

Module 8: Analyses employers ask for · Lesson 8.5 · about 20 min

Cohort retention tables

Group customers by the month they first bought, count how many come back in each later month, and turn it into the triangle-shaped retention table that subscription and retail teams read every week.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 8 · about 20 minutes · uses Lessons 4.3, 6.3 and 7.5. You’ll be able to build a cohort retention table and read it.

A cohort is everyone who started in the same month. Following each cohort month by month shows whether customers stay, and whether newer customers stay better than older ones.

In everyday terms: school classes by starting year: you do not compare this year’s Grade 9s with the Grade 12s; you follow each class as it moves up.

1. Each customer’s cohort and age

Pythonorders.csv
  • cohort: the month of the customer’s first order, copied to every row with transform (Lesson 4.3).
  • age: how many months after the first order this row happened. Month 0 is the first month.

2. The retention triangle

Pythonorders.csv

Read a row to follow one cohort over time; read a column to compare cohorts at the same age. div(counts[0], axis=0) divides each row by its own month-0 size.

Try it: read the triangle
back in month 1 / 3 / 6 (weighted by cohort size)
cohorts shown
Each row is a cohort: the customers whose first order fell in that month. Each column is how many months later, and the colour is the share of the cohort that bought again that month (darker = more). Month 0 is always 100 %. The bottom rows are short because those customers have not had time to come back, and they are tiny, so their colours jump around: hide them with the slider and the averages settle. Real counts from orders.csv.

3. Reading it honestly

  • The first cohort is mixed. The data starts in January 2025, so that cohort also holds every older customer who happened to buy that month. It is the biggest cohort (216 people against 153 the month after), which hints at it. Leave it out or label it.
  • A blank cell can mean zero. If nobody from a cohort came back in some month, that cell is NaN; inside the triangle, fillna(0) it.
  • Small cohorts are noisy. Late cohorts have few new customers, so one person changes the % a lot. Show the cohort size next to the table.
  • Recent columns are partial if the last month is not over (Lesson 7.2).
Cohort
The month of a customer's first order: groupby(...).transform('min').
Age
Months between the cohort month and the order month.
Retention
Active customers at that age ÷ cohort size.
The triangle
Young cohorts have fewer months, so the table is a triangle.
Physical meaning
Age 0 is always 100 %: everyone bought in their first month. Reading down a column compares cohorts at the same age, the fair comparison; reading along a row follows one cohort over time.

“The bottom-right corner is low, so retention is falling.” The newest cohorts have few months and small sizes; their last cells are noisy. Compare cohorts at the same age, and check the size column.

Watch it happen, one step at a time:

Orders. C1 buys in Jan, Feb, Mar; C2 in Jan, Mar; C3 in Jan; C4 in Feb, Mar.
Cohorts. Jan: C1, C2, C3 (size 3). Feb: C4 (size 1).
Ages. C2's March order is age 2; C4's March order is age 1.
Count active per age. Jan cohort: age 0 → 3, age 1 → 1, age 2 → 2.
Divide by size.
cohortsizeage 0age 1age 2
Jan3100 %33 %67 %
Feb1100 %100 %

4. Your turn

Your turnorders.csv
How it works

Principle: comparing all customers this month with all customers last month mixes old and new people. A cohort follows one fixed group over time, so changes in the table are changes in behaviour, not in who is counted.

retentionc,a=active customers of cohort c in month c+acustomers of cohort c

Shapes to look for: a fast drop after month 0 then a flat line means a loyal core; a line that keeps falling means people drift away; columns that improve for newer cohorts mean the product or onboarding got better.

5. Practice

  1. Cohorts by quarter instead of month.
  2. Revenue retention instead of customer retention.
  3. Why do subscription businesses read the diagonal of this table?
Answers
  1. Use to_period('Q') and the age in quarters.
  2. Sum revenue per cohort and age instead of nunique of customers, then divide by month 0 revenue.
  3. Each diagonal is one calendar month, so a dip along a diagonal shows something that happened to everyone that month (an outage, a price rise).

Try it on your own data: CSV profiler checks the date and customer columns before you build cohorts.

New words: cohort, retention, cohort age, triangle, left censoring.

  • Cohort = first order month, via groupby + transform(‘min’).
  • Age = months since the cohort month.
  • Retention = active at that age ÷ cohort size.
  • Compare cohorts down a column, and mind small sizes.

Check yourself

1What is a customer's cohort here?
2Why is the retention table a triangle?
3Why can the first cohort look different in a data extract?

Sources

  • Fader, P. S. and Hardie, B. G. S. (2010), Customer-Base Valuation in a Contractual Setting, Marketing Science 29(1)
  • McKinney, W. (2022), Python for Data Analysis, 3rd edition, O'Reilly (open access) https://wesmckinney.com/book/

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. All datasets are made up. Python runs in your browser (the first run downloads it once, about 20 MB); nothing you type is sent anywhere.