Funnels, Retention & Cohorts

Expert · 24 min read · ▶ live playground · ✦ checkpoint

Funnels, cohorts, and retention are three ways to ask whether users keep moving through a product. The SQL is not magic: first make one row per user, then count the right stage, period, or return window.

The café app already has event-grain rows in cafe_app_events. For analytics, you usually fold those events into user-grain checkpoints before you compute rates.

Funnel Steps Are User Checkpoints

A funnel is an ordered path where users move through steps, such as signup → app open → menu view → cart → purchase. Step-to-step conversion is the percent of users at one step who reached the next step.

Start by finding each user's first timestamp for each step. Then stack the step counts and let a window compare each row to the row above it:

WITH user_steps AS (
  SELECT u.user_id,
         min(e.event_ts) FILTER (WHERE e.event_type = 'signup') AS signup_ts,
         min(e.event_ts) FILTER (WHERE e.event_type = 'app_open') AS app_open_ts,
         min(e.event_ts) FILTER (WHERE e.event_type = 'menu_view') AS menu_view_ts,
         min(e.event_ts) FILTER (WHERE e.event_type = 'add_to_cart') AS add_to_cart_ts,
         min(e.event_ts) FILTER (WHERE e.event_type = 'purchase') AS purchase_ts
  FROM cafe_users u
  LEFT JOIN cafe_app_events e ON e.user_id = u.user_id
  GROUP BY u.user_id
),
funnel AS (
  SELECT 1 AS step_order,
         'signup' AS step_name,
         count(*) FILTER (WHERE us.signup_ts IS NOT NULL) AS users
  FROM user_steps us
  UNION ALL
  SELECT 2 AS step_order,
         'app_open' AS step_name,
         count(*) FILTER (WHERE us.app_open_ts > us.signup_ts) AS users
  FROM user_steps us
  UNION ALL
  SELECT 3 AS step_order,
         'menu_view' AS step_name,
         count(*) FILTER (WHERE us.menu_view_ts > us.app_open_ts) AS users
  FROM user_steps us
  UNION ALL
  SELECT 4 AS step_order,
         'add_to_cart' AS step_name,
         count(*) FILTER (WHERE us.add_to_cart_ts > us.menu_view_ts) AS users
  FROM user_steps us
  UNION ALL
  SELECT 5 AS step_order,
         'purchase' AS step_name,
         count(*) FILTER (WHERE us.purchase_ts > us.add_to_cart_ts) AS users
  FROM user_steps us
),
rates AS (
  SELECT f.step_order,
         f.step_name,
         f.users,
         lag(f.users) OVER (ORDER BY f.step_order) AS previous_step_users,
         first_value(f.users) OVER (ORDER BY f.step_order) AS signup_users
  FROM funnel f
)
SELECT r.step_name,
       r.users,
       r.previous_step_users,
       CASE
         WHEN r.previous_step_users IS NULL THEN NULL
         ELSE round(100.0 * r.users / NULLIF(r.previous_step_users, 0), 1)
       END AS pct_from_previous,
       round(100.0 * r.users / NULLIF(r.signup_users, 0), 1) AS pct_of_signups
FROM rates r
ORDER BY r.step_order;
  step_name  | users | previous_step_users | pct_from_previous | pct_of_signups
-------------+-------+---------------------+-------------------+----------------
 signup      |    12 |                NULL |              NULL |          100.0
 app_open    |    11 |                  12 |              91.7 |           91.7
 menu_view   |    11 |                  11 |             100.0 |           91.7
 add_to_cart |     9 |                  11 |              81.8 |           75.0
 purchase    |     8 |                   9 |              88.9 |           66.7
(5 rows)

Notice the grain. user_steps is one row per user. The final grid is one row per funnel step. The first row has no previous step, so pct_from_previous is NULL, not 100. A missing denominator is still unknown.

Cohorts Start With First Seen

A cohort is a group of users who share a starting period. Here, the starting period is the month of each user's first app event.

Once you have first-seen month, a cohort report is just a grouped metric:

WITH first_seen AS (
  SELECT e.user_id,
         CAST(date_trunc('month', min(e.event_ts)) AS date) AS cohort_month
  FROM cafe_app_events e
  GROUP BY e.user_id
),
first_purchase AS (
  SELECT e.user_id,
         min(e.event_ts) AS first_purchase_ts
  FROM cafe_app_events e
  WHERE e.event_type = 'purchase'
  GROUP BY e.user_id
)
SELECT fs.cohort_month,
       count(*) AS cohort_users,
       count(*) FILTER (WHERE fp.first_purchase_ts IS NOT NULL) AS ever_purchased_users,
       round(
         100.0 * count(*) FILTER (WHERE fp.first_purchase_ts IS NOT NULL) / NULLIF(count(*), 0),
         1
       ) AS ever_purchased_pct
FROM first_seen fs
LEFT JOIN first_purchase fp ON fp.user_id = fs.user_id
GROUP BY fs.cohort_month
ORDER BY fs.cohort_month;
 cohort_month | cohort_users | ever_purchased_users | ever_purchased_pct
--------------+--------------+----------------------+--------------------
 2026-01-01   |            4 |                    3 |               75.0
 2026-02-01   |            4 |                    4 |              100.0
 2026-03-01   |            4 |                    1 |               25.0
(3 rows)

The cohort dimension is cohort_month. The metric is "ever purchased." Do not read this as retention yet. It says whether a user bought at least once by the end of the data, not whether they came back month after month.

Retention Is A Triangle

Retention measures whether a cohort is still active in later periods. A retention triangle puts cohorts down the side and "months since first seen" across the top.

This version measures buyer retention: did a user in the cohort make a purchase in month 0, month 1, or month 2? The window in cohort_users annotates each user with their cohort size, then date math turns purchase months into month offsets.

WITH first_seen AS (
  SELECT e.user_id,
         CAST(date_trunc('month', min(e.event_ts)) AS date) AS cohort_month
  FROM cafe_app_events e
  GROUP BY e.user_id
),
cohort_users AS (
  SELECT fs.user_id,
         fs.cohort_month,
         count(*) OVER (PARTITION BY fs.cohort_month) AS cohort_size
  FROM first_seen fs
),
purchase_months AS (
  SELECT DISTINCT e.user_id,
         CAST(date_trunc('month', e.event_ts) AS date) AS purchase_month
  FROM cafe_app_events e
  WHERE e.event_type = 'purchase'
),
bounds AS (
  SELECT CAST(date_trunc('month', max(e.event_ts)) AS date) AS max_activity_month
  FROM cafe_app_events e
),
retention_rows AS (
  SELECT cu.cohort_month,
         max(cu.cohort_size) AS cohort_users,
         CAST(
           (EXTRACT(YEAR FROM pm.purchase_month) - EXTRACT(YEAR FROM cu.cohort_month)) * 12
           + (EXTRACT(MONTH FROM pm.purchase_month) - EXTRACT(MONTH FROM cu.cohort_month))
           AS int
         ) AS month_number,
         count(DISTINCT cu.user_id) AS purchasing_users
  FROM cohort_users cu
  JOIN purchase_months pm ON pm.user_id = cu.user_id
  WHERE pm.purchase_month >= cu.cohort_month
  GROUP BY cu.cohort_month, month_number
),
cohorts AS (
  SELECT cu.cohort_month,
         max(cu.cohort_size) AS cohort_users
  FROM cohort_users cu
  GROUP BY cu.cohort_month
)
SELECT c.cohort_month,
       c.cohort_users,
       COALESCE(max(rr.purchasing_users) FILTER (WHERE rr.month_number = 0), 0) AS month_0_buyers,
       round(
         100.0 * COALESCE(max(rr.purchasing_users) FILTER (WHERE rr.month_number = 0), 0) / NULLIF(c.cohort_users, 0),
         1
       ) AS month_0_pct,
       CASE
         WHEN c.cohort_month + INTERVAL '1 month' <= b.max_activity_month THEN
          COALESCE(max(rr.purchasing_users) FILTER (WHERE rr.month_number = 1), 0)
       END AS month_1_buyers,
       CASE
         WHEN c.cohort_month + INTERVAL '1 month' <= b.max_activity_month THEN
          round(
            100.0 * COALESCE(max(rr.purchasing_users) FILTER (WHERE rr.month_number = 1), 0) / NULLIF(c.cohort_users, 0),
            1
          )
       END AS month_1_pct,
       CASE
         WHEN c.cohort_month + INTERVAL '2 months' <= b.max_activity_month THEN
          COALESCE(max(rr.purchasing_users) FILTER (WHERE rr.month_number = 2), 0)
       END AS month_2_buyers,
       CASE
         WHEN c.cohort_month + INTERVAL '2 months' <= b.max_activity_month THEN
          round(
            100.0 * COALESCE(max(rr.purchasing_users) FILTER (WHERE rr.month_number = 2), 0) / NULLIF(c.cohort_users, 0),
            1
          )
       END AS month_2_pct
FROM cohorts c
CROSS JOIN bounds b
LEFT JOIN retention_rows rr ON rr.cohort_month = c.cohort_month
GROUP BY c.cohort_month, c.cohort_users, b.max_activity_month
ORDER BY c.cohort_month;
 cohort_month | cohort_users | month_0_buyers | month_0_pct | month_1_buyers | month_1_pct | month_2_buyers | month_2_pct
--------------+--------------+----------------+-------------+----------------+-------------+----------------+-------------
 2026-01-01   |            4 |              3 |        75.0 |              3 |        75.0 |              1 |        25.0
 2026-02-01   |            4 |              2 |        50.0 |              4 |       100.0 |           NULL |        NULL
 2026-03-01   |            4 |              1 |        25.0 |           NULL |        NULL |           NULL |        NULL
(3 rows)

The NULLs in later-month columns are honest. The bounds CTE computes the last observed month, and the CASE expressions leave cells blank when that offset has not happened inside the data yet. A blank future is not the same as zero retained users.

Self-Join Or Window?

A self-join fits questions made of row pairs: "a menu view followed by a later purchase." But it can multiply intermediate rows before you group back down.

WITH menu_purchase_pairs AS (
  SELECT mv.user_id,
         mv.event_id AS menu_event_id,
         p.event_id AS purchase_event_id
  FROM cafe_app_events mv
  JOIN cafe_app_events p ON p.user_id = mv.user_id
   AND p.event_type = 'purchase'
   AND p.event_ts > mv.event_ts
  WHERE mv.event_type = 'menu_view'
),
users_after_grouping AS (
  SELECT mpp.user_id
  FROM menu_purchase_pairs mpp
  GROUP BY mpp.user_id
)
SELECT count(*) AS self_join_pair_rows,
       (SELECT count(*) FROM users_after_grouping) AS users_after_grouping
FROM menu_purchase_pairs;
 self_join_pair_rows | users_after_grouping
---------------------+----------------------
                  31 |                    8
(1 row)

Those 31 pairs are not wrong. They are the shape the self-join asked for. If the final answer is one row per user, a window often reads cleaner: annotate each event row with the user's first menu view and first purchase, then fold to user grain.

WITH annotated_events AS (
  SELECT e.user_id,
         e.event_type,
         e.event_ts,
         min(e.event_ts) FILTER (WHERE e.event_type = 'menu_view') OVER (PARTITION BY e.user_id) AS first_menu_view_ts,
         min(e.event_ts) FILTER (WHERE e.event_type = 'purchase') OVER (PARTITION BY e.user_id) AS first_purchase_ts
  FROM cafe_app_events e
),
user_paths AS (
  SELECT DISTINCT ae.user_id,
         ae.first_menu_view_ts,
         ae.first_purchase_ts
  FROM annotated_events ae
)
SELECT up.user_id,
       up.first_menu_view_ts,
       up.first_purchase_ts,
       up.first_purchase_ts - up.first_menu_view_ts AS time_to_first_purchase
FROM user_paths up
WHERE up.first_purchase_ts > up.first_menu_view_ts
ORDER BY up.user_id
LIMIT 6;
 user_id | first_menu_view_ts  |  first_purchase_ts  | time_to_first_purchase
---------+---------------------+---------------------+------------------------
     101 | 2026-01-03 09:21:00 | 2026-01-03 09:27:00 | 00:06:00
     102 | 2026-01-06 08:11:00 | 2026-01-06 08:18:00 | 00:07:00
     104 | 2026-01-20 08:35:00 | 2026-01-20 08:43:00 | 00:08:00
     105 | 2026-02-02 10:15:00 | 2026-02-02 10:22:00 | 00:07:00
     106 | 2026-02-08 14:22:00 | 2026-02-08 14:35:00 | 00:13:00
     107 | 2026-02-14 09:50:00 | 2026-03-02 16:29:00 | 16 days 06:39:00
(6 rows)

Use the self-join when the row pair is the answer. Use the window when each row needs a margin note and the final report returns to user grain.

sql — playgroundlive
⌘/Ctrl + Enter to run

Next, you keep the same discipline but shift from cohorts to time series. Date spines, running totals, moving averages, and period-over-period comparisons are where the empty days and uneven periods start to matter.

Checkpoint

Answer all three to mark this lesson complete

+50 XP on completion