Why this matters
Funnel queries show how users move through key steps (e.g., view product → add to cart → purchase). As a Product Analyst, you’ll use funnels to diagnose drop-offs, estimate impact, and prioritize product fixes.
- Evaluate onboarding: Where do users stop? Which step is the bottleneck?
- Campaign analysis: Did the new ad lead to higher step 1 → step 2 conversion?
- Checkout performance: Is the payment page causing leaks on mobile?
- Product experiments: Measure step-level lift in A/B tests.
Concept explained simply
A funnel is an ordered list of events users should complete. We count how many users reach each step, then compute conversion rates between steps and overall.
Mental model
Simple funnel mental model
Imagine placing marbles (users) through a series of tubes (steps). At each tube, some marbles fall out. Two rules matter:
- Order: A marble must pass through tube 1 before tube 2.
- Timing: You can impose time limits between tubes (e.g., must pass tube 2 within 30 minutes of tube 1).
SQL funnels are just ordered filters on event times, sometimes with extra constraints like same session or same device.
Data assumptions
Assume an events table:
events(
user_id STRING,
event_name STRING,
event_time TIMESTAMP,
session_id STRING,
order_id STRING,
revenue NUMERIC,
device STRING,
country STRING
)
Event names we will use in examples: view_landing, sign_up_start, sign_up_complete, view_product, add_to_cart, purchase.
Notes on SQL dialects
- Use INTERVAL 'X' DAY/HOUR or DATEADD depending on your warehouse.
- Use TIMESTAMPDIFF or event_time - prior_time for time windows.
- COUNT(DISTINCT ...) availability and performance vary; consider approximate distinct functions where needed.
Worked examples
Example 1 — Basic 3-step funnel (users over last 14 days)
Goal: view_landing → sign_up_start → sign_up_complete. Count users at each step and conversion rates.
-- Window: last 14 days
WITH s1 AS (
SELECT user_id, MIN(event_time) AS t1
FROM events
WHERE event_name = 'view_landing'
AND event_time >= CURRENT_DATE - INTERVAL '14' DAY
GROUP BY user_id
),
s2 AS (
SELECT s1.user_id, s1.t1, MIN(e.event_time) AS t2
FROM s1
LEFT JOIN events e
ON e.user_id = s1.user_id
AND e.event_name = 'sign_up_start'
AND e.event_time > s1.t1
GROUP BY s1.user_id, s1.t1
),
s3 AS (
SELECT s2.user_id, s2.t1, s2.t2, MIN(e.event_time) AS t3
FROM s2
LEFT JOIN events e
ON e.user_id = s2.user_id
AND e.event_name = 'sign_up_complete'
AND e.event_time > s2.t2
GROUP BY s2.user_id, s2.t1, s2.t2
),
wide AS (
SELECT s2.user_id,
s2.t1,
s2.t2,
s3.t3
FROM s2
LEFT JOIN s3 USING (user_id, t1, t2)
)
SELECT
COUNT(*) AS step1_users, -- reached landing
COUNT(*) FILTER (WHERE t2 IS NOT NULL) AS step2_users, -- started signup
COUNT(*) FILTER (WHERE t3 IS NOT NULL) AS step3_users, -- completed signup
ROUND(100.0 * COUNT(*) FILTER (WHERE t2 IS NOT NULL) / NULLIF(COUNT(*),0), 2) AS conv_1_to_2_pct,
ROUND(100.0 * COUNT(*) FILTER (WHERE t3 IS NOT NULL) / NULLIF(COUNT(*) FILTER (WHERE t2 IS NOT NULL),0), 2) AS conv_2_to_3_pct,
ROUND(100.0 * COUNT(*) FILTER (WHERE t3 IS NOT NULL) / NULLIF(COUNT(*),0), 2) AS conv_overall_pct
FROM wide;
Why this works: we capture the first time a user hits each step and ensure ordering by joining with time constraints.
Alternative: conditional aggregation by user
WITH base AS (
SELECT user_id, event_name, event_time
FROM events
WHERE event_time >= CURRENT_DATE - INTERVAL '14' DAY
),
firsts AS (
SELECT user_id,
MIN(CASE WHEN event_name = 'view_landing' THEN event_time END) AS t1,
MIN(CASE WHEN event_name = 'sign_up_start' THEN event_time END) AS t2_raw,
MIN(CASE WHEN event_name = 'sign_up_complete' THEN event_time END) AS t3_raw
FROM base
GROUP BY user_id
),
ordered AS (
SELECT user_id,
t1,
CASE WHEN t2_raw > t1 THEN t2_raw END AS t2,
CASE WHEN t3_raw IS NOT NULL AND t2_raw IS NOT NULL AND t3_raw > t2_raw AND t2_raw > t1 THEN t3_raw END AS t3
FROM firsts
)
SELECT ... -- same aggregations as above
FROM ordered;
Example 2 — Time windows and session constraint
Goal: view_product → add_to_cart (within 30 minutes, same session) → purchase (within 7 days of add_to_cart, any session).
WITH vp AS (
SELECT user_id, session_id, MIN(event_time) AS t_vp
FROM events
WHERE event_name = 'view_product'
AND event_time >= CURRENT_DATE - INTERVAL '30' DAY
GROUP BY user_id, session_id
),
atc AS (
SELECT vp.user_id, vp.session_id, vp.t_vp,
MIN(e.event_time) AS t_atc
FROM vp
LEFT JOIN events e
ON e.user_id = vp.user_id
AND e.session_id = vp.session_id -- same session
AND e.event_name = 'add_to_cart'
AND e.event_time > vp.t_vp
AND e.event_time <= vp.t_vp + INTERVAL '30' MINUTE
GROUP BY vp.user_id, vp.session_id, vp.t_vp
),
pur AS (
SELECT atc.user_id, atc.t_atc,
MIN(e.event_time) AS t_pur
FROM atc
LEFT JOIN events e
ON e.user_id = atc.user_id
AND e.event_name = 'purchase'
AND e.event_time > atc.t_atc
AND e.event_time <= atc.t_atc + INTERVAL '7' DAY
GROUP BY atc.user_id, atc.t_atc
)
SELECT
COUNT(DISTINCT vp.user_id) AS step1_users,
COUNT(DISTINCT CASE WHEN atc.t_atc IS NOT NULL THEN atc.user_id END) AS step2_users,
COUNT(DISTINCT CASE WHEN pur.t_pur IS NOT NULL THEN pur.user_id END) AS step3_users
FROM vp
LEFT JOIN atc USING (user_id, session_id, t_vp)
LEFT JOIN pur USING (user_id, t_atc);
Key idea: apply per-step constraints in the join conditions.
Example 3 — Funnel by acquisition cohort
Goal: show overall conversion by the month of first landing page view.
WITH first_touch AS (
SELECT user_id,
DATE_TRUNC('month', MIN(event_time)) AS cohort_month,
MIN(event_time) AS t1
FROM events
WHERE event_name = 'view_landing'
GROUP BY user_id
),
start_signup AS (
SELECT f.user_id, f.cohort_month, f.t1,
MIN(e.event_time) AS t2
FROM first_touch f
LEFT JOIN events e
ON e.user_id = f.user_id
AND e.event_name = 'sign_up_start'
AND e.event_time > f.t1
GROUP BY f.user_id, f.cohort_month, f.t1
),
complete_signup AS (
SELECT s.user_id, s.cohort_month, s.t1, s.t2,
MIN(e.event_time) AS t3
FROM start_signup s
LEFT JOIN events e
ON e.user_id = s.user_id
AND e.event_name = 'sign_up_complete'
AND e.event_time > s.t2
GROUP BY s.user_id, s.cohort_month, s.t1, s.t2
)
SELECT cohort_month,
COUNT(*) AS step1_users,
COUNT(*) FILTER (WHERE t2 IS NOT NULL) AS step2_users,
COUNT(*) FILTER (WHERE t3 IS NOT NULL) AS step3_users,
ROUND(100.0 * COUNT(*) FILTER (WHERE t3 IS NOT NULL) / NULLIF(COUNT(*),0), 2) AS overall_conv_pct
FROM complete_signup
GROUP BY cohort_month
ORDER BY cohort_month;
Use cohorts to spot seasonality or changes after releases.
Exercises (you can do these now)
Everyone can take the exercises and test; only logged-in users get saved progress.
- Exercise 1: Build a 3-step funnel for view_product → add_to_cart → purchase over the last 30 days. Show counts and conversion rates between steps and overall. Mirror result columns: step1_users, step2_users, step3_users, conv_1_to_2_pct, conv_2_to_3_pct, conv_overall_pct.
- Exercise 2: Same funnel but with constraints: add_to_cart must happen within 45 minutes of view_product in the same session; purchase must happen within 5 days of add_to_cart. Output the same metrics.
Checklist before running your query
- Define your time window.
- Choose first occurrence per user for each step.
- Apply ordering (step N+1 time > step N time).
- Apply any time windows and session constraints.
- Aggregate counts and compute conversion rates safely with NULLIF to avoid divide-by-zero.
Common mistakes and self-check
- Counting events, not users. Self-check: Are you using COUNT(DISTINCT user_id)?
- Ignoring order. Self-check: Ensure step2_time > step1_time, not just existing.
- Missing time windows. Self-check: Joins include upper bounds like + INTERVAL 'X' MINUTE/DAY.
- Dropping users unintentionally. Self-check: Use LEFT JOINs so earlier-step users remain even if they did not progress.
- Mixing sessions accidentally. Self-check: When required, join by session_id for same-session constraints.
- Using raw first occurrences without enforcing order. Self-check: Validate that first step2 occurs after chosen step1.
Performance tips
- Limit scanned data by time filters early.
- Pre-filter to users who have step1 to reduce join size.
- Materialize intermediate CTEs if your warehouse supports it and data is large.
Practical projects
- Onboarding funnel dashboard: daily funnel counts and conversion rates with device breakdown.
- Checkout health monitor: weekly alert if step 2 → 3 conversion drops by more than 3 percentage points.
- Cohort funnel report: acquisition-month cohorts with overall and step-level conversion tracked for 3 months.
Mini challenge
Create a 4-step funnel: view_product → add_to_cart → start_checkout → purchase. Require add_to_cart within 60 minutes of view_product (same session), and purchase within 3 days of start_checkout. Output counts and step conversions plus overall conversion.
Hint
Extend Example 2 by adding the start_checkout step between add_to_cart and purchase, carrying forward time and session constraints appropriately.
Learning path
- Before this: Basic SQL filtering, grouping, and window functions.
- Now: Build ordered funnels with constraints and cohorts.
- Next: Segment funnels by device, campaign, and experiment groups; learn retention and pathing analysis.
Who this is for
- Aspiring and practicing Product Analysts
- Growth/Marketing Analysts needing conversion insights
- Data-savvy PMs validating user journeys
Prerequisites
- Comfort with SELECT, WHERE, GROUP BY, JOIN
- Basic understanding of DISTINCT and timestamps
- Familiarity with your event schema (event names and fields)
Next steps
- Take the quick test to confirm understanding.
- Implement the mini challenge in your warehouse.
- Schedule a weekly funnel to monitor conversion trends.