Pitfall
Not observable yet
A result that has not had time to happen is unknown, not zero: young cohorts and the current period look like a collapse.
When a metric counts what happens within a window after an event (retention at 30 days, renewal, refund within 60
days), only report the cohorts whose whole window is before the data's end:
CASE WHEN cohort_end + window <= as_of THEN … END, NULL otherwise. The same goes for an unfinished week or month at
the end of a chart.
WITH cohorts(cohort, users, retained) AS (VALUES
(DATE '2026-04-01', 9955, 2031), (DATE '2026-05-01', 10392, 1985), (DATE '2026-06-01', 4817, 34))
SELECT cohort, users,
CASE WHEN cohort + INTERVAL 1 MONTH + INTERVAL 35 DAY <= DATE '2026-07-01'
THEN ROUND(100.0 * retained / users, 1) END AS retained_pct
FROM cohorts
ORDER BY cohort;
| cohort | users | retained_pct |
|---|---|---|
| 2026-04-01 | 9955 | 20.4 |
| 2026-05-01 | 10392 | NULL |
| 2026-06-01 | 4817 | NULL |