Funnel analysis measures how many users complete each step in a multi-step process — sign-up, onboarding, purchase, activation. This guide covers building funnel queries in SQL using window functions and self-joins, calculating step conversion rates, measuring time-to-convert, and segmenting funnels by user attributes to identify where and why users drop off.
What Funnel Analysis Measures
A funnel is a defined sequence of steps that users must complete to reach a goal — sign-up to activation, trial to paid conversion, product landing page to purchase. Funnel analysis measures how many users complete each step and where they drop off.
The core outputs: step-level conversion rates (what percentage of users who reached step N completed step N+1) and overall funnel conversion (what percentage of users who entered the funnel completed the final step). Segmenting funnels by acquisition channel, device type, user cohort, or pricing plan reveals which user segments convert better and why.
Defining the Funnel in SQL
Start with an event log: one row per user-event with user_id, event_type, and event_timestamp. Define the funnel steps as a sequence of event_type values.
Example funnel: signup_started -> email_verified -> profile_completed -> first_order_placed
**Step 1 — Flag each user's completion of each step**:
WITH step_flags AS (
SELECT
user_id,
MAX(CASE WHEN event_type = 'signup_started' THEN 1 ELSE 0 END) AS step1,
MAX(CASE WHEN event_type = 'email_verified' THEN 1 ELSE 0 END) AS step2,
MAX(CASE WHEN event_type = 'profile_completed' THEN 1 ELSE 0 END) AS step3,
MAX(CASE WHEN event_type = 'first_order_placed' THEN 1 ELSE 0 END) AS step4
FROM events
WHERE event_date >= '2024-01-01'
GROUP BY user_id
),
**Step 2 — Count users reaching each step**:
step_counts AS (
SELECT
SUM(step1) AS reach_step1,
SUM(CASE WHEN step1 = 1 AND step2 = 1 THEN 1 ELSE 0 END) AS reach_step2,
SUM(CASE WHEN step1 = 1 AND step2 = 1 AND step3 = 1 THEN 1 ELSE 0 END) AS reach_step3,
SUM(CASE WHEN step1 = 1 AND step2 = 1 AND step3 = 1 AND step4 = 1 THEN 1 ELSE 0 END) AS reach_step4
FROM step_flags
)
SELECT
reach_step1,
reach_step2, ROUND(100.0 * reach_step2 / reach_step1, 1) AS step1_to_step2_pct,
reach_step3, ROUND(100.0 * reach_step3 / reach_step2, 1) AS step2_to_step3_pct,
reach_step4, ROUND(100.0 * reach_step4 / reach_step3, 1) AS step3_to_step4_pct,
ROUND(100.0 * reach_step4 / reach_step1, 1) AS overall_conversion_pct
FROM step_counts
Ordered Funnel: Enforcing Step Sequence
The simple approach above flags any completion of each event, regardless of order. A user who places an order before completing their profile still gets flagged for step3 and step4. For funnels where order matters, enforce the sequence using MIN timestamps:
WITH first_events AS (
SELECT
user_id,
MIN(CASE WHEN event_type = 'signup_started' THEN event_timestamp END) AS t1,
MIN(CASE WHEN event_type = 'email_verified' THEN event_timestamp END) AS t2,
MIN(CASE WHEN event_type = 'profile_completed' THEN event_timestamp END) AS t3,
MIN(CASE WHEN event_type = 'first_order_placed' THEN event_timestamp END) AS t4
FROM events
GROUP BY user_id
),
ordered_steps AS (
SELECT
user_id,
t1 IS NOT NULL AS reached_step1,
t1 IS NOT NULL AND t2 > t1 AS reached_step2,
t1 IS NOT NULL AND t2 > t1 AND t3 > t2 AS reached_step3,
t1 IS NOT NULL AND t2 > t1 AND t3 > t2 AND t4 > t3 AS reached_step4
FROM first_events
)
Ordered funnels are more accurate for conversion funnels where step sequence is meaningful. Unordered funnels are appropriate when you just want to know whether a user ever completed each milestone, regardless of order.
Conversion Window
Funnels should measure conversion within a defined time window — did the user complete all steps within 7 days of starting? Without a conversion window, a user who signed up in January and placed their first order in November looks like a converted funnel user, inflating measured conversion rates.
Add a conversion window condition:
t1 IS NOT NULL AND t2 > t1 AND t2 <= t1 + INTERVAL '7 days' AS reached_step2_in_window
Apply the same window condition to each subsequent step relative to step 1 (or relative to the preceding step, depending on the question).
Segmented Funnels
Breaking funnels by segment reveals where different user populations drop off:
WITH segmented AS (
SELECT
fe.user_id,
u.acquisition_channel,
u.device_type,
t1 IS NOT NULL AS reached_step1,
...
FROM first_events fe
JOIN users u ON fe.user_id = u.user_id
)
SELECT
acquisition_channel,
COUNT(CASE WHEN reached_step1 THEN 1 END) AS step1_users,
ROUND(100.0 * COUNT(CASE WHEN reached_step2 THEN 1 END) / NULLIF(COUNT(CASE WHEN reached_step1 THEN 1 END), 0), 1) AS step1_to_step2,
...
FROM segmented
GROUP BY acquisition_channel
ORDER BY step1_users DESC
Common segmentation dimensions:
- Acquisition channel: organic search, paid social, direct — which channel converts better?
- Device type: mobile vs desktop — where do mobile users drop off vs desktop?
- Pricing plan: free vs paid trial — does converting to paid before full onboarding improve or hurt full activation?
- Cohort month: is the funnel improving over time with product iterations?
Time-to-Convert Analysis
Beyond whether users convert, how long it takes matters. Long time-to-convert indicates friction or low urgency; short time-to-convert indicates high intent.
SELECT
user_id,
t1 AS funnel_start,
t4 AS funnel_end,
DATEDIFF('hour', t1, t4) AS hours_to_convert,
DATEDIFF('day', t1, t4) AS days_to_convert
FROM first_events
WHERE t1 IS NOT NULL AND t4 IS NOT NULL AND t4 > t3 AND t3 > t2 AND t2 > t1
Summarise with percentiles:
SELECT
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY hours_to_convert) AS p25,
PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY hours_to_convert) AS median,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY hours_to_convert) AS p75,
PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY hours_to_convert) AS p90
FROM converted_users
Segment time-to-convert by acquisition channel or device — channels that drive fast converters have higher intent than channels that drive slow converters.
Funnel Visualisation
The standard funnel visualisation is a waterfall bar chart: each step is a bar whose height represents the number of users who reached that step. The area lost between bars represents drop-off. Colour-code the bars by step conversion rate.
In Tableau: use a calculated field for step conversion, bar chart with step on rows, user count on columns. Add a reference line at 100% of the first step to show absolute drop-off scale.
In Power BI: funnel visual type natively displays this pattern; add conditional formatting on the conversion rate measure.
For segmented funnels, use small multiples (one funnel per segment) or grouped bars with each segment as a colour series.
Funnel Debug Patterns
When a funnel step has unexpectedly low conversion:
- **Check event firing**: Is the event actually being logged? Verify row counts in the events table for the specific event_type.
- **Check time window**: Is the conversion window too tight? Try removing it temporarily.
- **Check ordering logic**: Are users completing steps in an unexpected order that the ordered funnel rejects?
- **Check user scope**: Is the funnel being filtered to a user population that excludes valid converters?
Funnel analysis surfaces the symptom (drop-off at step X); diagnosing the cause requires session recordings, user interviews, or A/B tests targeting that step.
Our BI strategy practice designs product analytics frameworks including funnel tracking, cohort analysis, and conversion optimisation — contact us to discuss your analytics requirements.
A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.
Book a Call →