Deviopath › PostgreSQL

Monthly Cohort Retention

Hard · CTEs

Cohort analysis groups users by when they joined and tracks how many come back later. Here a cohort is the calendar month of signed_up_on, and month_number counts calendar months since then: activity in the signup month is month 0, the next calendar month is 1, and so on (a user who signs up on Jan 31 and is active on Feb 1 is active in month 1). Watch out for year boundaries: December to January is 1 month.

For every cohort and month number with at least one active user, return:

- cohort: the signup month as text YYYY-MM

- month_number: an integer

- cohort_size: how many users signed up in that month (active or not)

- active_users: how many different users of the cohort were active in that month

- retention_pct: ROUND(100.0 * active_users / cohort_size, 1)

Sort by cohort, then month_number.

Next: Customer RFM Report