A cohort is everyone who started in the same period: here, customers grouped by the month of their first order. Retention asks how many of each cohort ordered again one month later, two months later, and so on. Comparing cohorts shows whether newer customers stick around better than older ones.
The build, step by step:
- Each customer's cohort:
DATE_TRUNC('month', MIN(order_date)).
- Each order's month offset from the cohort:
DATE_DIFF('month', cohort, order_month), so 0 is the first month.
- Count distinct customers per (cohort, offset); divide by the cohort's size for a rate.
Count distinct customers, not orders: two orders in a month are still one retained customer. Recent cohorts have had less time to come back, so their later offsets are empty, not zero. Reports leave them blank rather than showing a misleading 0%.