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.
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.
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
- 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
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.
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:
| cohort | size | age 0 | age 1 | age 2 |
|---|---|---|---|---|
| Jan | 3 | 100 % | 33 % | 67 % |
| Feb | 1 | 100 % | 100 % |
4. Your turn
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.
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
- Cohorts by quarter instead of month.
- Revenue retention instead of customer retention.
- Why do subscription businesses read the diagonal of this table?
Answers
- Use
to_period('Q')and the age in quarters. - Sum revenue per cohort and age instead of
nuniqueof customers, then divide by month 0 revenue. - 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
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.