---
name: cohort-retention-analysis
description: "Use when building or reviewing a cohort retention table — users grouped into acquisition cohorts and tracked by age since signup. Anchors every retained-% denominator to the cohort's original size, keeps each user in exactly one cohort, expects a ragged triangle, and refuses to average retention across cohorts of different maturity. Do NOT use for forecasting future retention, scoring an account's churn risk, or choosing a cancellation save-offer."
metadata:
  decimalai-skill-id: "b76ad4e4-7f09-40d5-bb71-05b5fc5567bf"
  decimalai-version-id: "3fa508d7-c762-44b0-ba36-fc6934d62b09"
  decimalai-version: "1"
  decimalai-body-sha256: "04cb5bb4eda75ad934387fc7a3aa47a6f383b418a582c2e1f36ecd2a0b34103c"
  decimalai-name: "cohort-retention-analysis"
  decimalai-format: "1"
  decimalai-invocation: "model"
  decimalai-category: "data"
  decimalai-source: "https://app.decimal.ai/s/cohort-retention-analysis@1/SKILL.md"
---
# Build the retention triangle correctly, don't just tabulate

Asked to turn signups and their later activity into a retention table, the base model usually
produces a plausible-looking grid — but it slips on the parts that decide whether the numbers mean
anything: which denominator each cell divides by, whether a user can land in two cohorts, and what
to put where no data exists yet. Get those wrong and every downstream read of the table is wrong in
the same direction. The rules below are the discipline the table depends on.

## The four rules

**1. Each user belongs to exactly one cohort, fixed by acquisition.**
A cohort is the set of users who first signed up (or first activated) in the same period — week,
month, whatever your grain. A user is stamped into that cohort once and never moves. Do not cohort
users by their *last* active period, and if someone deleted and re-created an account, decide up
front which signup counts; never let one person contribute to two cohorts' age-0 counts.

**2. The denominator is always the cohort's original size at age 0 — never the prior period.**
For cohort C, `retained[N] = (users from C active in period N) / (size of C at age 0)`.
Dividing instead by the *previous* period's survivors gives **period-over-period (rolling)
retention** — a legitimate but different metric. Classic retention holds the denominator fixed, so
the curve is comparable across ages and reads as "share of the original cohort still here." Pick one
definition and state it; do not mix them in the same table.

**3. The table is a ragged triangle. Unobserved future cells are blank, not zero.**
A cohort that signed up 2 periods ago only has ages 0, 1, 2. Ages 3+ for that cohort haven't
happened — those cells are **unknown/NULL**, never 0%. A 0% would say "everyone churned," which is a
measurement, not a gap. So the newest cohort is a single cell, the oldest is a full row, and the
shape is a staircase. Never pad it into a full rectangle.

**4. Do not average retention across cohorts of different maturity.**
Only the oldest cohorts have reached the high ages. If you average "age-6 retention" down its column
across all cohorts, you're really averaging *only the few cohorts old enough to be in that column* —
and if you let the missing younger-cohort cells count as 0, you drag it toward zero. To produce one
representative number at age N: include **only cohorts that have actually reached age N**, and
weight by cohort size (a simple mean over-weights small cohorts). To compare cohorts, compare them
**at equal ages** — a younger cohort's row is shorter because it's less mature, not because it
retains worse.

## Worked example

Three monthly cohorts, observed at the end of March. "Active" = returned that month.

| Cohort | Signups (age 0) | Active age 1 | Active age 2 |
|--------|-----------------|--------------|--------------|
| Jan    | 100             | 60           | 48           |
| Feb    | 120             | 72           | —            |
| Mar    | 150             | —            | —            |

Retention table (each cell ÷ that cohort's own age-0 size):

| Cohort | age 0 | age 1 | age 2 |
|--------|-------|-------|-------|
| Jan    | 100%  | 60%   | 48%   |
| Feb    | 100%  | 60%   | —     |
| Mar    | 100%  | —     | —     |

- Jan age-2 is `48/100 = 48%`, **not** `48/60`. The denominator is 100 for the whole Jan row.
- Feb and Mar age-2 are blank — those months haven't elapsed. They are not 0%.
- The age-2 column contains only Jan. "Age-2 retention is 48%" describes the Jan cohort, not the
  company. Do not average age-1 down the column by counting Mar's blank as 0.
- A cell can rise (e.g. a curve going 100 → 55 → 62) when users who lapsed return — under the
  fixed-denominator definition that's allowed, not necessarily a bug. Membership each period is
  tested independently against the fixed age-0 denominator.

## Before you present the table, check

- Does every retained-% in a row divide by the **same** number (that cohort's age-0 size)?
- Could any single user appear in two cohorts? (re-signups, last-active cohorting)
- Are all future/unreached cells blank rather than 0?
- Is any cross-cohort average silently mixing mature and immature cohorts, or counting blanks as 0?
- Is the metric labeled — classic (fixed denominator) vs rolling (prior period)?

`scripts/cohort_pivot.sql` is a starting SQL template that assigns each user to one acquisition
cohort, computes age, and divides by the cohort's age-0 size — adapt the activity definition to
your data.