Analytics Patterns · Cohorts · date_trunc · LEFT JOIN
54. Month-1 retention by signup cohort
Concept: Cohorts · date_trunc · LEFT JOINshow
A cohort is everyone who signed up in the same month. Month-1 retention is the share of a cohort that was active in the calendar month after their signup month. Cohort size must come from the signup table, so people who never came back still count in the denominator.
Example (different data):
SELECT to_char(joined_on, 'YYYY-MM') AS cohort, COUNT(*) AS members FROM gym_members GROUP BY 1;
Your task
Product wants to know if onboarding is improving: what share of each signup cohort came back the month after joining?
For users who signed up (users.created_at) in January through March 2024, return cohort_month (YYYY-MM), cohort_size, retained (cohort users with at least one app open in the calendar month after their signup month) and retention_pct (retained as a percentage of cohort_size, rounded to 1 decimal). Oldest cohort first.
Starting Postgres in your browser…
Checking your access…