How to Perform Funnel Analysis Using SQL: A Complete Guide for Data Engineers

Funnel analysis using SQL measures user progression through sequential steps toward conversion by deduplicating events, identifying downstream conversions via self-joins, and aggregating step-level metrics with standard SQL constructs.

Funnel analysis is essential for understanding where users drop off in multi-step workflows. The Data Engineer Handbook repository (DataExpert-io/data-engineer-handbook) provides a production-ready SQL pattern in funnel_analysis.sql that runs on any modern analytics warehouse without modification.

Understanding Funnel Analysis with SQL

A funnel represents a linear sequence of user actions leading to a goal. Common examples include:

• Landing page → Product page → Cart → Checkout → Purchase
• App install → Registration → Feature activation → Subscription

The SQL approach in the handbook treats each funnel step as a user-day-event triplet, enabling precise conversion tracking without window functions or proprietary syntax.

The 4-Stage SQL Funnel Pattern

The implementation in intermediate-bootcamp/materials/4-applying-analytical-patterns/lecture-lab/funnel_analysis.sql follows a modular pipeline:

Stage 1: Deduplicate Raw Events

Duplicate log entries from retries, retries, or bot traffic must be removed before analysis. The handbook uses a GROUP BY on the natural key of the event stream:

SELECT url, host, user_id, event_time
FROM events
GROUP BY url, host, user_id, event_time

This preserves one record per unique user action, eliminating noise that would inflate visit counts.

Stage 2: Clean and Enrich

Derived columns improve downstream readability. The pattern adds event_date and enforces data quality:

SELECT *, DATE(event_time) AS event_date
FROM deduped_events
WHERE user_id IS NOT NULL
ORDER BY user_id, event_time

The ORDER BY ensures deterministic behavior when users have multiple events at identical timestamps.

Stage 3: Identify Conversions with Self-Joins

The core logic detects whether a user completed a target action after their initial funnel entry. This uses a self-join on user_id and event_date with a temporal inequality:

SELECT ce1.user_id,
       ce1.event_time,
       ce1.url,
       COUNT(DISTINCT CASE WHEN ce2.url = '/api/v1/user' THEN ce2.url END) AS converted
FROM clean_events ce1
JOIN clean_events ce2
  ON ce2.user_id = ce1.user_id
 AND ce2.event_date = ce1.event_date
 AND ce2.event_time > ce1.event_time
GROUP BY ce1.user_id, ce1.event_time, ce1.url

Key implementation details:

• ce2.event_time > ce1.event_time ensures causal ordering—only future events count as conversions
• COUNT(DISTINCT …) caps the conversion flag at 1 per user-event, preventing double-counting
• The CASE expression filters for the specific conversion URL ('/api/v1/user' in the example)

Stage 4: Aggregate Funnel Metrics

Final aggregation computes actionable metrics per funnel step:

SELECT url,
       COUNT(*) AS visits,
       CAST(SUM(converted) AS REAL) / COUNT(*) AS conversion_rate
FROM converted
GROUP BY url
HAVING CAST(SUM(converted) AS REAL) / COUNT(*) > 0
   AND COUNT(*) > 100;

The HAVING clause filters out:

• Steps with zero conversion (noise from mis-tagged events)
• Steps with negligible volume (< 100 visits)

Complete Working Example

This runnable query adapts the handbook's pattern for a generic web analytics scenario:

WITH deduped_events AS (
    SELECT url, host, user_id, event_time
    FROM events
    GROUP BY url, host, user_id, event_time
),
clean_events AS (
    SELECT 
        url,
        host,
        user_id,
        event_time,
        DATE(event_time) AS event_date
    FROM deduped_events
    WHERE user_id IS NOT NULL
),
converted AS (
    SELECT 
        ce1.url AS funnel_step,
        ce1.user_id,
        ce1.event_date,
        MAX(CASE WHEN ce2.url = '/api/v1/user' THEN 1 ELSE 0 END) AS converted
    FROM clean_events ce1
    LEFT JOIN clean_events ce2
      ON ce2.user_id = ce1.user_id
     AND ce2.event_date = ce1.event_date
     AND ce2.event_time > ce1.event_time
     AND ce2.url = '/api/v1/user'
    GROUP BY ce1.url, ce1.user_id, ce1.event_date
)
SELECT 
    funnel_step,
    COUNT(*) AS total_visits,
    SUM(converted) AS conversions,
    ROUND(SUM(converted) * 100.0 / COUNT(*), 2) AS conversion_pct
FROM converted
GROUP BY funnel_step
HAVING COUNT(*) > 100
ORDER BY conversion_pct DESC;

Extending to Multi-Step Funnels

For tracking progression across multiple sequential steps, pivot events into flags and compute progressive rates:

WITH base AS (
    -- deduplication and cleaning as above
    SELECT 
        user_id,
        event_date,
        event_time,
        url
    FROM clean_events
),
step_flags AS (
    SELECT 
        user_id,
        event_date,
        MAX(CASE WHEN url = '/landing' THEN 1 END) AS step_landing,
        MAX(CASE WHEN url = '/product' THEN 1 END) AS step_product,
        MAX(CASE WHEN url = '/cart' THEN 1 END) AS step_cart,
        MAX(CASE WHEN url = '/checkout' THEN 1 END) AS step_checkout,
        MAX(CASE WHEN url = '/purchase' THEN 1 END) AS step_purchase
    FROM base
    GROUP BY user_id, event_date
)
SELECT
    SUM(step_landing) AS landing_visits,
    ROUND(SUM(step_product) * 100.0 / NULLIF(SUM(step_landing), 0), 2) 
        AS landing_to_product_pct,
    ROUND(SUM(step_cart) * 100.0 / NULLIF(SUM(step_product), 0), 2) 
        AS product_to_cart_pct,
    ROUND(SUM(step_checkout) * 100.0 / NULLIF(SUM(step_cart), 0), 2) 
        AS cart_to_checkout_pct,
    ROUND(SUM(step_purchase) * 100.0 / NULLIF(SUM(step_checkout), 0), 2) 
        AS checkout_to_purchase_pct
FROM step_flags;

NULLIF prevents division-by-zero when a step has no traffic.

Performance Considerations for Funnel SQL

Factor	Optimization	Rationale
Date filtering	Add WHERE event_date >= CURRENT_DATE - INTERVAL '90 days'	Reduces join cardinality on large tables
Partitioning	Ensure event_date is a partition key	Enables partition pruning in BigQuery/Snowflake
Indexing	Index on (user_id, event_date) or use clustering	Accelerates the self-join predicate
Materialization	Persist clean_events as a table or incremental model	Avoids recomputing deduplication on every query

The handbook's pattern avoids window functions (LEAD/LAG) intentionally—self-joins often perform better on distributed warehouses when properly filtered by date.

Summary

• Deduplicate events with GROUP BY on the natural key before any analysis
• Self-join on user_id + event_date with event_time > inequality to detect conversions
• Use COUNT(DISTINCT CASE …) or MAX(CASE …) to create binary conversion flags per user-day
• Filter aggressively with HAVING to remove statistical noise from low-volume steps
• Extend to multi-step funnels by pivoting events into step flags and computing progressive ratios

The pattern in funnel_analysis.sql is warehouse-agnostic—deploy it on Snowflake, BigQuery, Redshift, or Postgres without modification.

Frequently Asked Questions

What is funnel analysis in SQL?

Funnel analysis in SQL measures how many users complete each step in a defined sequence of actions, typically culminating in a conversion event. Unlike BI tools that hide the logic, SQL funnels give data engineers full transparency into attribution rules, time windows, and deduplication logic. The Data Engineer Handbook implements this with standard joins and aggregations that run on any analytics database.

Why use self-joins instead of window functions for funnel analysis?

Self-joins often outperform window functions on distributed warehouses when queries are filtered to specific date ranges. A self-join on user_id and event_date restricts the working set dramatically, whereas LEAD() or LAG() over unbounded partitions can trigger full table scans. For daily funnel metrics with 90-day lookback windows, the handbook's join-based approach typically executes faster and uses less memory.

How do I handle users who convert across multiple days?

The handbook pattern uses event_date equality in the join condition, so conversions are attributed only to events on the same calendar day. To support multi-day conversion windows, replace ce2.event_date = ce1.event_date with date arithmetic like ce2.event_date BETWEEN ce1.event_date AND ce1.event_date + INTERVAL '7 days'. Be aware this increases join cardinality—add careful limits to prevent explosive growth.

Can this SQL funnel pattern track multiple conversion goals?

Yes. Replace the single CASE WHEN url = '/api/v1/user' with multiple flag columns, one per goal. The aggregation stage then computes separate conversion rates for each goal. Alternatively, add a conversion_goal column and GROUP BY both funnel_step and conversion_goal to compare goal performance side-by-side in one query.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}