Drill 9 of 15 in Analytics Patterns
0

Analytics Patterns · Cohorts · date_trunc · LEFT JOIN

9. 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…