Cohort Retention Analysis in SQL: Building the Triangle Chart

What retention actually measures

Acquisition metrics tell you people arrived. Retention tells you whether the product was worth coming back to. It is the single hardest metric to fake and the one most predictive of whether anything else you do matters.

A cohort is a group of users who first appeared in the same period. Retention is the fraction of that cohort still active N periods later. Grouping by cohort is what separates "we grew" from "we churned and replaced."

Building it in three steps

Assume an events table with visitor_id and timestamp.

Step 1 — assign every user to a cohort.

CREATE OR REPLACE VIEW user_cohorts AS
SELECT visitor_id,
       date_trunc('week', min(timestamp))::DATE AS cohort_week
FROM events
GROUP BY visitor_id;

Step 2 — record each user's activity periods.

CREATE OR REPLACE VIEW user_activity AS
SELECT DISTINCT visitor_id,
       date_trunc('week', timestamp)::DATE AS active_week
FROM events;

Step 3 — join and compute the period offset.

CREATE OR REPLACE VIEW cohort_retention AS
SELECT c.cohort_week,
       datediff('week', c.cohort_week, a.active_week) AS week_number,
       count(DISTINCT a.visitor_id) AS active_users
FROM user_cohorts c
JOIN user_activity a USING (visitor_id)
GROUP BY 1, 2;

Then pivot into the familiar triangle:

SELECT cohort_week,
       max(active_users) FILTER (WHERE week_number = 0) AS w0,
       round(100.0 * max(active_users) FILTER (WHERE week_number = 1)
             / nullif(max(active_users) FILTER (WHERE week_number = 0), 0), 1) AS w1_pct,
       round(100.0 * max(active_users) FILTER (WHERE week_number = 2)
             / nullif(max(active_users) FILTER (WHERE week_number = 0), 0), 1) AS w2_pct,
       round(100.0 * max(active_users) FILTER (WHERE week_number = 4)
             / nullif(max(active_users) FILTER (WHERE week_number = 0), 0), 1) AS w4_pct,
       round(100.0 * max(active_users) FILTER (WHERE week_number = 8)
             / nullif(max(active_users) FILTER (WHERE week_number = 0), 0), 1) AS w8_pct
FROM cohort_retention
GROUP BY cohort_week
ORDER BY cohort_week DESC;

Result:

Reading the triangle

Read down a column to compare cohorts at the same age. This is the only fair comparison — it holds age constant. W1 dropping from 44.6% to 39.2% then recovering to 46.8% is a real week-over-week signal about acquisition quality or onboarding.

Read across a row to see one cohort's decay curve.

The shape of the curve is the whole story. Healthy retention drops sharply at first and then flattens. That flat portion is your durable user base. A curve that keeps declining toward zero means you have no retained users at all — you are renting attention, not building an audience.

The staircase of blanks in the bottom-right is expected: recent cohorts have not lived long enough. Never compare a partial cell to a complete one.

N-day vs unbounded retention

Two definitions, and they answer different questions:

• Bounded (classic) retention — active in week N specifically. Stricter, standard for products with a natural usage rhythm.
• Unbounded ("rolling") retention — active in week N or any week after. More forgiving, better for products used irregularly.

-- unbounded: was the user ever seen at or after week N?
SELECT c.cohort_week, n.week_number,
       count(DISTINCT a.visitor_id) AS retained
FROM user_cohorts c
CROSS JOIN generate_series(0, 12) AS n(week_number)
JOIN user_activity a
  ON a.visitor_id = c.visitor_id
 AND datediff('week', c.cohort_week, a.active_week) >= n.week_number
GROUP BY 1, 2;

Pick one, label the chart with which it is, and never switch silently. Unbounded retention always looks better, and comparing an unbounded number to a bounded benchmark is how teams accidentally convince themselves things are fine.

Traps

Incomplete cohorts. The current week's cohort is still filling. Excluding it is not optional — plotted alongside complete cohorts it always looks like a collapse.

Cohorting on the wrong event. For a product, cohort on activation, not first pageview. A cohort of people who bounced off your homepage will show terrible retention and tell you nothing you can act on.

Weekly boundaries and seasonality. A cohort starting on a holiday week behaves differently. Use enough cohorts that one anomaly does not drive the read.

Small cohorts. Under ~100 users, a single-digit change in retained users swings the percentage wildly.

Survivorship in segments. "Users who did feature X retain better" is usually backwards — engaged users try more features. Correlation, not a causal reason to push feature X.

Frequently asked questions

What does a retention cohort actually measure?

The share of a group that first appeared in one period and came back in a later one. Grouping by first-seen date is what makes cohorts comparable, since it holds acquisition timing constant.

What is the difference between N-day and unbounded retention?

N-day counts a return on exactly day N; unbounded counts a return on day N or any day after. N-day is stricter and noisier on small cohorts; unbounded curves are smoother and never increase.

Why does my retention curve flatten out?

That plateau is the healthy signal — it is the fraction of users who became habitual. A curve that keeps declining to zero means no durable core is forming, which is a product problem rather than a measurement one.

How large does a cohort need to be?

Enough that one user is not a visible percentage. Below about a hundred, each person moves the line by more than a point and the triangle mostly displays noise — widen to weekly or monthly cohorts.

Why does the newest cohort look worse?

Because it has not had time to return yet. The most recent row is always incomplete, and reading it as a decline is the most common misinterpretation of a retention triangle.

From cohorts to action

Split cohorts by acquisition channel and the chart starts paying for itself:

SELECT s.channel, n.week_number,
       round(100.0 * count(DISTINCT a.visitor_id)
             / nullif(count(DISTINCT c.visitor_id), 0), 1) AS retention_pct
FROM user_cohorts c
JOIN first_session s USING (visitor_id)
…
GROUP BY 1, 2;

If paid social retains at 8% by week 4 and organic search retains at 31%, your acquisition spend is buying a metric, not a business. That comparison changes budgets in a way that a blended CAC number never will.

Cohort retention runs well on a columnar engine — it is a large scan over few columns with a distinct count, exactly the shape DuckDB is built for. In InsightsTrack you can write these queries directly in the SQL Editor against your own data. See also window functions for sessionization and 12 dashboard SQL queries.