◀ Course contents Part 4 · Module 4-03

Funnels & Cohorts

The two reports every product team asks for

No new syntax to speak of — this module is Part 3's joins and Part 4's windows pointed at two specific questions. How many people got from one step to the next, and are the people who signed up in spring still here in autumn. Both are easy to write and easy to write in a way that quietly misleads.

Ready?

Counts, in the Right Order, as the Right Percentage

A funnel is a handful of counts of the same table under different conditions. Postgres has a clause for exactly that, and it is much clearer than the alternative:

COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'signup')   AS signup,
COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'activate') AS activate

FILTER applies to that aggregate alone, so one pass produces every step. The portable version is SUM(CASE WHEN … THEN 1 ELSE 0 END), which does the same job by counting ones and reads considerably worse.

Count people, not events

COUNT(DISTINCT user_id), never COUNT(*). Someone who created three reports is still one person who reached that step. Counting rows can produce a conversion rate above 100%, which at least announces itself — more often it just inflates a step quietly. In this database nine report_created events belong to eight people.

Keeping the steps in order

Grouping by event_name sorts the steps alphabetically — activate, invite_sent, report_created, signup — which is not the journey. Supply the order yourself as a small inline table:

WITH steps(step_no, event_name) AS (
  VALUES (1, 'signup'), (2, 'activate'), (3, 'report_created'), (4, 'invite_sent')
)

That is a real table for the rest of the query. LEFT JOIN events onto it and a step nobody ever reached still appears, with a count of zero — which is exactly the row you most want to see.

Two percentages, two questions

the funnel in this database

step	users	% of start	step conversion
signup	12	100.0	—
activate	9	75.0	75.0
report_created	8	66.7	88.9
invite_sent	3	25.0	37.5

Share of the top always falls, so it never points at anything. Step conversion is what finds the leak: 88.9% of activated users create a report, and then only 37.5% of those invite anyone.

The first is a window over the whole funnel, MAX(users) OVER (). The second is LAG from module 4-02, guarded with NULLIF so a step with nobody in it cannot abort the query.

Quick check

A funnel step shows 112% conversion from the step before it. What is the most likely cause?

One Number Per Step Is an Average

The funnel above says 75% of signups activate. It is a correct number and it describes nobody.

the same funnel, split by plan

plan	signup	activate	report	invite
free	5	2	1	0
pro	4	4	4	2
team	3	3	3	1

Every paying user activates and creates a report. Everything the funnel loses, it loses among free users — and no free user has ever invited anybody. "75% activate" was the average of 100% and 40%.

This is the whole argument for segmenting. An aggregate funnel tells you that you are losing people; a segmented one tells you which people, which is the part you can act on.

Build the grid before you fill it

Every plan needs every step, whether or not anyone on that plan reached it. CROSS JOIN the plans to the steps first, then LEFT JOIN the events on — so the empty cells exist and read zero. Join events straight in and the free-plan invite cell disappears entirely: 11 rows instead of 12, and the missing one is the most interesting row in the report.

The same reasoning applies to time. A funnel says how many converted; it never says how long they took, and "activated eventually" is a different product from "activated the same day". That measurement is a join and a subtraction — first activation event minus signup date — and it should use an inner join on purpose: a user who never activated has no time-to-activate, and letting them in as a zero would drag the average toward a number nobody experienced.

Quick check

Your segmented funnel returns 11 rows where you expected 12. What is missing, and why does it matter?

Grouping People by When They Arrived

A funnel is about a journey. A cohort is about time: a group defined by when it started, followed forward.

The reason to bother is that a plain "monthly active users" chart mixes two different things — how good the product is at keeping people, and how many new people marketing sent. Both push the line up. A cohort separates them, because the cohort's membership never changes.

Label every user

date_trunc('quarter', signup_date). Fixed forever — a Q1 user is in the Q1 cohort in perpetuity.

Size each cohort

The denominator. Every percentage is a share of this cohort, not of everyone.

Find each active period

Distinct user-and-quarter pairs from the events table.

Offset it

Not the calendar quarter — how many quarters after its own cohort started.

Step 4 is what makes cohorts comparable. Quarter 1 for the Q2 cohort is a different calendar quarter from quarter 1 for the Q3 cohort; what they have in common is being one quarter old.

(EXTRACT(YEAR FROM q)    - EXTRACT(YEAR FROM cohort)) * 4
  + (EXTRACT(QUARTER FROM q) - EXTRACT(QUARTER FROM cohort))

Do not derive the offset from AGE()

Taking the quarter out of an AGE() interval drops the year component. The Q4 2023 cohort's activity in January 2024 then comes back as offset -3 — three quarters before those users existed. The query runs, and the grid quietly gains a column that cannot be real. Compute it from the year and quarter parts, as above.

How wide does a cohort have to be?

This is a judgement, not a syntax question, and it is one an analyst has to make honestly. Monthly cohorts do not work on this data. Twelve users spread across eleven months gives cohorts of one — and a cohort of one has a retention rate of 0% or 100%, forever. The grid would be full of confident numbers meaning nothing.

Quarterly gives cohorts of 4, 3, 3 and 2. Still small, but the percentages can now land somewhere other than the extremes. On a real product the same question is asked with weeks, months or quarters, and the answer depends entirely on volume. Picking a grain that produces degenerate percentages is a genuine reporting failure, not a rounding detail.

Quick check

A weekly retention grid shows several cohorts at exactly 0% or exactly 100%. What should you suspect first?

Two Directions, Two Findings

A retention grid is read both ways, and most people only ever read it one way.

quarterly signup cohorts, share still active

cohort	size	Q+0	Q+1
2023 Q1	4	100.0	—
2023 Q2	3	100.0	66.7
2023 Q3	3	100.0	33.3
2023 Q4	2	100.0	100.0

Offset 0 is always 100% by construction — everyone in a cohort was there when it started. It is the columns after it that carry information.

→

Along a row

How one cohort decays. Always downwards; the shape of the fall is the product's retention.

↓

Down a column

Whether newer cohorts retain better than older ones at the same age. This is how you tell whether a change worked.

The column reading is the one people forget, and it is the one that answers "did the thing we shipped in March help?". Comparing March's cohort at one quarter old against January's at one quarter old is a fair comparison. Comparing either against a calendar month is not — the calendar month contains people of every age.

An empty cell is not zero

The Q1 cohort has no Q+1 figure above, and that is not 0% retention — it is no data. A cell can be missing because nobody came back, or because that quarter has not happened yet, or because the events table simply stops. Only the first is a finding, and a grid that renders all three as 0 will produce a meeting about the wrong thing.

Next: changing the data

Every module so far has read. Module 4-04 writes — INSERT, UPDATE and DELETE, the WHERE clause that is the difference between fixing one row and every row, and the transaction that lets you take it back.

Quick check

A team ships an onboarding change in April. Which comparison shows whether it worked?

0 of 10 completed

Loading the tables…

To do

Every funnel needs several counts of the same table under different conditions. Postgres has a clause for exactly that:

COUNT(*) FILTER (WHERE event_name = 'signup')   AS signups,
COUNT(*) FILTER (WHERE event_name = 'activate') AS activations

FILTER applies to that aggregate alone, so one pass over the table produces every step of the funnel at once. It is far clearer than the portable alternative, SUM(CASE WHEN … THEN 1 ELSE 0 END), which does the same job by counting ones.

Note COUNT(DISTINCT user_id) rather than COUNT(*) for funnel steps. A person who created three reports is still one person who reached that step, and counting rows would report a conversion rate above 100%.

Your task: from events, return one row with the number of distinct users who reached each step: signup, activate, report (event report_created) and invite (event invite_sent).

query.sql

SELECT
  AS signup,
  AS activate,
  AS report,
  AS invite
FROM events;

PostgreSQL

Hint

COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'signup') AS signup, and the same shape for the other three.

Output

To do

One wide row is awkward to chart. A funnel is more useful long — one row per step — but grouping by event_name loses the order, because the steps sort alphabetically rather than by where they sit in the journey.

The fix is to supply the order yourself, as a small table written inline with VALUES:

WITH steps(step_no, event_name) AS (
  VALUES (1, 'signup'), (2, 'activate'), (3, 'report_created'), (4, 'invite_sent')
)

That is a real table for the rest of the query — join events to it, and every step gets its number. It also means a step nobody ever reached still appears, with a count of zero, provided the join is a LEFT one.

Your task: return step_no, event_name and users — the distinct users who reached each step — for the four steps above, ordered by step_no.

query.sql

WITH steps(step_no, event_name) AS (
  VALUES (1, 'signup'), (2, 'activate'), (3, 'report_created'), (4, 'invite_sent')
)
SELECT s.step_no, s.event_name,
       AS users
FROM steps s
LEFT JOIN events e ON
GROUP BY s.step_no, s.event_name
ORDER BY s.step_no;

PostgreSQL

Hint

Join on e.event_name = s.event_name, and count COUNT(DISTINCT e.user_id) AS users.

Output

To do

Raw counts do not show where a funnel leaks. Two different percentages are wanted, and they answer different questions:

↓

Share of the top

Of everyone who started, how many got this far? Always falls. Good for "we lose three quarters by the end".

→

Step conversion

Of those who reached the previous step, how many took this one? This is the one that finds the leak.

The first is a window over the whole funnel; the second is LAG from module 4-02:

ROUND(100.0 * users / MAX(users) OVER (), 1)                        AS pct_of_start,
ROUND(100.0 * users / NULLIF(LAG(users) OVER (ORDER BY step_no), 0), 1) AS step_conv

Your task: extend the funnel with pct_of_start and step_conv, both to one decimal place. Order by step_no.

query.sql

WITH steps(step_no, event_name) AS (
  VALUES (1, 'signup'), (2, 'activate'), (3, 'report_created'), (4, 'invite_sent')
),
counts AS (
  SELECT s.step_no, s.event_name, COUNT(DISTINCT e.user_id) AS users
  FROM steps s
  LEFT JOIN events e ON e.event_name = s.event_name
  GROUP BY s.step_no, s.event_name
)
SELECT step_no, event_name, users,
       AS pct_of_start,
       AS step_conv
FROM counts
ORDER BY step_no;

PostgreSQL

Hint

pct_of_start divides by MAX(users) OVER (); step_conv divides by NULLIF(LAG(users) OVER (ORDER BY step_no), 0). The first step has no previous step, so its step_conv is NULL.

Output

To do

A single funnel is one number per step, and one number per step is an average. Averages hide exactly the thing you are looking for.

Split this funnel by plan and the aggregate falls apart in a useful way: paying users convert perfectly, and everything the funnel loses is lost among free users. "75% activate" describes nobody.

Note the shape — every plan needs every step, whether or not anyone on that plan reached it, so the plans and the steps are combined with CROSS JOIN before events are joined on. That guarantees the full grid, with zeros where they belong. It matters here: no free user has ever sent an invite, and an inner join would silently return 11 rows instead of 12 — dropping the cell that says so.

Your task: return plan, step_no, event_name and users for all four steps, for every plan. Order by plan, then step_no.

query.sql

WITH steps(step_no, event_name) AS (
  VALUES (1, 'signup'), (2, 'activate'), (3, 'report_created'), (4, 'invite_sent')
)
SELECT u.plan, s.step_no, s.event_name,
       AS users
FROM users u
CROSS JOIN steps s
LEFT JOIN events e ON
GROUP BY u.plan, s.step_no, s.event_name
ORDER BY u.plan, s.step_no;

PostgreSQL

Hint

The join needs both conditions: ON e.user_id = u.user_id AND e.event_name = s.event_name. Then COUNT(DISTINCT e.user_id) AS users.

Output

To do

A funnel says how many converted. It never says how long they took, and "activated eventually" and "activated the same day" are different products.

The measurement is a join and a subtraction: the user's signup date against the date of their activation event. Real DATE columns subtract to an integer number of days.

Use an inner join here on purpose. Three users never activated, and their time-to-activate is not a large number — it does not exist. Including them as NULLs would be defensible; including them as zeros would be a lie, and letting them into an AVG would quietly drag it toward nothing.

Your task: return user_id and days from signup to first activation, for the users who activated, ordered by user_id.

query.sql

SELECT u.user_id,
       AS days
FROM users u
JOIN events e ON e.user_id = u.user_id
             AND e.event_name = 'activate'
GROUP BY u.user_id, u.signup_date
ORDER BY u.user_id;

PostgreSQL

Hint

MIN(e.event_at) - u.signup_date AS days — MIN because a user could in principle have more than one activation event, and the first is the one that counts.

Output

To do

A cohort is a group defined by when it started, and followed forward through time. The first step is always the same: give every user a label saying which cohort they belong to.

date_trunc('quarter', u.signup_date)::date AS cohort

The label never changes. That is the point of a cohort — a user who signed up in Q1 stays in the Q1 cohort forever, so the Q1 line on the chart always describes the same people.

Your task: return user_id, name and cohort — the first day of the quarter they signed up in — ordered by cohort, then user_id.

query.sql

SELECT u.user_id, u.name,
       AS cohort
FROM users u
ORDER BY cohort, u.user_id;

PostgreSQL

Hint

date_trunc('quarter', u.signup_date)::date AS cohort. The ::date cast keeps it a DATE rather than a timestamp.

Output

To do

Before any retention percentage means anything, the cohort has to be big enough to survive division. This is a judgement, not a syntax question, and it is one an analyst genuinely has to make.

Monthly cohorts do not work on this data. Twelve users spread over eleven months gives cohorts of one — and a cohort of one has a retention rate of either 0% or 100%, forever. The grid would be full of confident numbers that mean nothing.

Your task: return each cohort and its cohort_size, ordered by cohort.

query.sql

SELECT AS cohort,
       AS cohort_size
FROM users u
GROUP BY
ORDER BY cohort;

PostgreSQL

Hint

date_trunc('quarter', u.signup_date)::date AS cohort and COUNT(*) AS cohort_size, grouped by the same date_trunc expression.

Output

To do

Retention needs each activity placed relative to its own cohort's start, not on the calendar. Quarter 1 for the Q2 cohort is a different calendar quarter from quarter 1 for the Q3 cohort; what makes them comparable is that both mean "one quarter in".

Compute the offset from the year and quarter parts directly:

(EXTRACT(YEAR FROM a.q) - EXTRACT(YEAR FROM c.cohort)) * 4
  + (EXTRACT(QUARTER FROM a.q) - EXTRACT(QUARTER FROM c.cohort))

Do not derive this from AGE(). Taking the quarter parts out of an interval drops the year, and the Q4 cohort's activity in the following January comes back as offset -3 — three quarters before the users existed. It runs, and the grid quietly gains a column that should not be there.

Your task: return cohort, q_off and the number of distinct active users, counting a user as active in any quarter they have an event. Order by cohort, then q_off.

query.sql

WITH c AS (
  SELECT user_id, date_trunc('quarter', signup_date)::date AS cohort
  FROM users
),
a AS (
  SELECT DISTINCT user_id, date_trunc('quarter', event_at)::date AS q
  FROM events
)
SELECT c.cohort,
       AS q_off,
       AS active
FROM c
JOIN a ON a.user_id = c.user_id
GROUP BY c.cohort, 2
ORDER BY c.cohort, 2;

PostgreSQL

Hint

The offset is ((EXTRACT(YEAR FROM a.q) - EXTRACT(YEAR FROM c.cohort)) * 4 + (EXTRACT(QUARTER FROM a.q) - EXTRACT(QUARTER FROM c.cohort)))::int, and active is COUNT(DISTINCT a.user_id).

Output

To do

Counts cannot be compared across cohorts of different sizes — 2 out of 3 is better than 2 out of 4, and the raw number says they are the same. A retention grid is always shown as a percentage of its own cohort.

The denominator is the cohort's size, which means the sizes have to be computed and joined back in. Offset 0 is 100% by construction: everyone in a cohort was, by definition, around when it started.

Your task: return cohort, cohort_size, q_off, active and pct to one decimal place. Order by cohort, then q_off.

query.sql

WITH c AS (
  SELECT user_id, date_trunc('quarter', signup_date)::date AS cohort
  FROM users
),
sizes AS (
  SELECT cohort, COUNT(*) AS cohort_size FROM c GROUP BY cohort
),
a AS (
  SELECT DISTINCT user_id, date_trunc('quarter', event_at)::date AS q
  FROM events
)
SELECT c.cohort, s.cohort_size,
       ((EXTRACT(YEAR FROM a.q) - EXTRACT(YEAR FROM c.cohort)) * 4
        + (EXTRACT(QUARTER FROM a.q) - EXTRACT(QUARTER FROM c.cohort)))::int AS q_off,
       COUNT(DISTINCT a.user_id) AS active,
       AS pct
FROM c
JOIN a ON a.user_id = c.user_id
JOIN sizes s ON s.cohort = c.cohort
GROUP BY c.cohort, s.cohort_size, 3
ORDER BY c.cohort, 3;

PostgreSQL

Hint

ROUND(100.0 * COUNT(DISTINCT a.user_id) / s.cohort_size, 1) AS pct.

Output

To do

A retention grid is read two ways, and both matter.

→

Along a row

How one cohort decays over time. Always downwards, and the shape of the fall is the product's retention.

↓

Down a column

Whether newer cohorts retain better than older ones at the same age. This is how you tell if a change worked.

The column reading is the one people forget, and it is the one that answers "did the thing we shipped in March help?". Comparing March's cohort at one quarter old against January's at one quarter old is a fair comparison; comparing either against a calendar month is not.

Your task: a summary. For each cohort, return its cohort_size and retained — the number of its users still active after their first quarter (any offset above 0), as 0 where none were. Order by cohort.

query.sql

WITH c AS (
  SELECT user_id, date_trunc('quarter', signup_date)::date AS cohort
  FROM users
),
later AS (
  SELECT DISTINCT c.user_id, c.cohort
  FROM c
  JOIN events e ON e.user_id = c.user_id
  WHERE
)
SELECT c.cohort,
       COUNT(DISTINCT c.user_id) AS cohort_size,
       AS retained
FROM c
LEFT JOIN later l ON l.user_id = c.user_id
GROUP BY c.cohort
ORDER BY c.cohort;

PostgreSQL

Hint

In `later`, keep only events from a later quarter than the cohort: WHERE date_trunc('quarter', e.event_at)::date > c.cohort. Then retained is COUNT(DISTINCT l.user_id).

Output

The retention grid

To do

The report a product team looks at every week: how each signup cohort decays, as a share of itself, laid out so that cohorts of different sizes can be compared.

Your task: one row per cohort-and-offset cell that has any activity, with:

cohort — the first day of the quarter the user signed up in
cohort_size — how many users are in that cohort
q_off — quarters since the cohort started, so 0 is its own first quarter
active — distinct users from that cohort with an event in that quarter
pct — active as a percentage of cohort_size, to one decimal place

Order by cohort, then q_off.

Quarterly, not monthly — twelve users across eleven months would give cohorts of one, and every percentage would be 0 or 100. Compute the offset from the year and quarter parts rather than from AGE(), or the Q4 cohort's activity in January comes back three quarters before it signed up.

query.sql

-- Label the users, size the cohorts, find the active quarters, divide.
WITH c AS (

),
sizes AS (

),
a AS (

)
SELECT
FROM c
JOIN a ON a.user_id = c.user_id
JOIN sizes s ON s.cohort = c.cohort
GROUP BY
ORDER BY ;

PostgreSQL

Hint

c is SELECT user_id, date_trunc('quarter', signup_date)::date AS cohort FROM users. sizes is SELECT cohort, COUNT(*) AS cohort_size FROM c GROUP BY cohort. a is SELECT DISTINCT user_id, date_trunc('quarter', event_at)::date AS q FROM events. The offset is ((EXTRACT(YEAR FROM a.q) - EXTRACT(YEAR FROM c.cohort)) * 4 + (EXTRACT(QUARTER FROM a.q) - EXTRACT(QUARTER FROM c.cohort)))::int.

Output

Sign in to open this module

Funnels & Cohorts

Counts, in the Right Order, as the Right Percentage

Count people, not events

Keeping the steps in order

Two percentages, two questions

Quick check

One Number Per Step Is an Average

Build the grid before you fill it

Quick check

Grouping People by When They Arrived

Label every user

Size each cohort

Find each active period

Offset it

Do not derive the offset from AGE()

How wide does a cohort have to be?

Quick check

Two Directions, Two Findings

Along a row

Down a column

An empty cell is not zero

Next: changing the data

Quick check

Counting a subset in place

A funnel with its steps in order

The two percentages a funnel needs

Share of the top

Step conversion

The average that hides the answer

Time to the first event that matters

Labelling each user with a cohort

How wide does a cohort have to be?

How far after signup

The grid as percentages

Reading the grid

Along a row

Down a column

The retention grid

Notification