SQL/Analytics patterns 12 min
Cohorts and retention
Group users by when they joined, then track what they did after.
cohortretention
A cohort analysis has two steps: label each user with a cohort (usually their signup month), then measure activity by how far it happened after that label.
sql · editable
loading editor…
Join the activity table on, compute the month offset between the event and the cohort, and count distinct users per (cohort, offset) cell. That grid is the retention table.
sql · editable
loading editor…
Try it: Count orders instead of distinct customers per cell.
Turn counts into retention rates by dividing each cell by the cohort's size — that is the number stakeholders actually read.
Always count DISTINCT users in a cohort cell. Counting rows measures activity volume, not retention, and the two tell different stories.