Build a cohort retention analysis

You are a product analyst building a cohort retention analysis. A retention triangle answers one question well: are later cohorts behaving better or worse than earlier ones at the same age? It is easy to get wrong in ways that look plausible: counting calendar periods instead of periods since joining, letting the youngest cohorts' incomplete periods look like drops, or mixing a cohort definition with an activity definition that the cohort event itself satisfies.

Build a cohort retention analysis.

Cohort by: signup month

1. Define precisely: the cohort event and date for each user (for example first signup), the period length (month or week, matching the cohort grain unless the activity definition says otherwise), period 0, and the retention measure. Decide whether the cohort event itself counts as period-0 activity and say which.
2. Decide the retention type and state it: classic or bounded (active in exactly period N) by default; mention unbounded or rolling retention (active in N or later) only if the use case calls for it.
3. Write the code. If the data lives in a SQL warehouse, write SQL for the dialect named or implied in the event data (default postgres) using CTEs: cohorts, activity by period, cohort sizes, then the triangle. If it is a file, write pandas. Compute period number as whole periods since the cohort date, not calendar month minus calendar month on raw timestamps without truncation.
4. Output the triangle as cohorts in rows, period numbers in columns, values as percentages of cohort size, with the cohort size as its own column.
5. Mark cells that are incomplete because the period has not fully elapsed, and exclude them from averages.
6. If the event data includes a sample, compute the triangle on the sample to show the shape, labelled as illustrative.

• If the event data lacks a user identifier, a timestamp, or anything that can satisfy the activity definition, say what is missing and stop.
• Never fill missing cohort-period cells with zeros; an unobserved period is not zero retention.
• Users with activity before their cohort date (data errors, imports) are reported as a count, not silently dropped or kept.
• Do not draw conclusions from cohorts smaller than about 30 users without saying the numbers are noisy.
• Keep time zones consistent between the cohort date and activity timestamps; state the assumption.

Definitions

Bullets: cohort, period, period 0, retained, retention type, time zone.

Code

One code block.

Retention triangle

A Markdown table if computed from a sample (labelled illustrative); otherwise the column layout the code produces.

How to read it

Four to six sentences: reading down a column (cohort quality over time), across a row (decay curve), where the curve flattens, and what change would count as meaningful.

Caveats

Bullets specific to this data: incomplete periods, small cohorts, seasonality, definition changes.