lesson 52 Advanced product analysis · advanced

Ordered conversion funnels

Count users who complete events in the required order.

the idea

A funnel is an ordered journey. Find the first view, then the first signup on or after that view, then the first purchase on or after signup. A purchase that happens before signup does not complete this funnel. Equal timestamps are allowed here.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

social_events6 columns · 22 rows

idinteger · key	event_keytext	member_idinteger	event_typetext	event_attext	ingested_attext
1	a	1	view	2025-01-01 10:00:00	2025-01-01 10:00:01
2	b	1	signup	2025-01-01 10:03:00	2025-01-01 10:03:01
3	c	1	purchase	2025-01-01 10:05:00	2025-01-01 10:05:01
4	a	1	view	2025-01-01 10:00:00	2025-01-01 10:10:00
5	d	2	view	2025-01-02 09:00:00	2025-01-02 09:00:01

showing 5 of 22 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

-- Build one row per member at each stage.
 WITH views AS(
  SELECT member_id,MIN(event_at) AS viewed_at
  FROM social_events
  WHERE event_type='view'
  GROUP BY member_id
), signups AS(
  SELECT v.member_id,v.viewed_at,MIN(e.event_at) AS signed_at
  FROM views v
  LEFT JOIN social_events e
    ON e.member_id=v.member_id
    AND e.event_type='signup'
    AND e.event_at>=v.viewed_at
  GROUP BY v.member_id,v.viewed_at
), funnel AS(
  SELECT
    s.member_id,
    s.viewed_at,
    s.signed_at,
    MIN(e.event_at) AS purchased_at
  FROM signups s
  LEFT JOIN social_events e
    ON e.member_id=s.member_id
    AND e.event_type='purchase'
    AND e.event_at>=s.signed_at
  GROUP BY s.member_id,s.viewed_at,s.signed_at
)
SELECT
  COUNT(*) AS viewed,
  COUNT(signed_at) AS signed_up,
  COUNT(purchased_at) AS purchased
FROM funnel;

Watch for: Counting event types independently ignores sequence and includes invalid journeys.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

Find first views. The denominator is members who entered the funnel.

step-1.sql

SELECT member_id,MIN(event_at) AS viewed_at
FROM social_events
WHERE event_type='view'
GROUP BY member_id;

Reconstruct valid stages. Observe member 3: the purchase before view is ignored, and a later valid purchase is kept.

step-2.sql

WITH views AS(
  SELECT member_id,MIN(event_at) AS viewed_at
  FROM social_events
  WHERE event_type='view'
  GROUP BY member_id
), signups AS(
  SELECT v.member_id,v.viewed_at,MIN(e.event_at) AS signed_at
  FROM views v
  LEFT JOIN social_events e
    ON e.member_id=v.member_id
    AND e.event_type='signup'
    AND e.event_at>=v.viewed_at
  GROUP BY v.member_id,v.viewed_at
), funnel AS(
  SELECT
    s.member_id,
    s.viewed_at,
    s.signed_at,
    MIN(e.event_at) AS purchased_at
  FROM signups s
  LEFT JOIN social_events e
    ON e.member_id=s.member_id
    AND e.event_type='purchase'
    AND e.event_at>=s.signed_at
  GROUP BY s.member_id,s.viewed_at,s.signed_at
)
SELECT *
FROM funnel;

Count people at each stage. LEFT JOIN leaves NULL at stages where a person drops off.

step-3.sql

WITH views AS(
  SELECT member_id,MIN(event_at) AS viewed_at
  FROM social_events
  WHERE event_type='view'
  GROUP BY member_id
), signups AS(
  SELECT v.member_id,v.viewed_at,MIN(e.event_at) AS signed_at
  FROM views v
  LEFT JOIN social_events e
    ON e.member_id=v.member_id
    AND e.event_type='signup'
    AND e.event_at>=v.viewed_at
  GROUP BY v.member_id,v.viewed_at
), funnel AS(
  SELECT
    s.member_id,
    s.viewed_at,
    s.signed_at,
    MIN(e.event_at) AS purchased_at
  FROM signups s
  LEFT JOIN social_events e
    ON e.member_id=s.member_id
    AND e.event_type='purchase'
    AND e.event_at>=s.signed_at
  GROUP BY s.member_id,s.viewed_at,s.signed_at
)
SELECT
  COUNT(*) AS viewed,
  COUNT(signed_at) AS signed_up,
  COUNT(purchased_at) AS purchased
FROM funnel