How to Implement Funnel Analysis in SQL: A Production-Ready Pattern

Funnel analysis in SQL tracks user progression through sequential steps by deduplicating events, cleaning data, using self-joins to detect downstream conversions, and aggregating step-level metrics, as implemented in the DataExpert-io/data-engineer-handbook repository.

Funnel analysis measures how users move through defined stages toward a conversion goal, and implementing this logic in SQL creates a portable solution that runs on any modern data warehouse. The DataExpert-io/data-engineer-handbook repository provides a complete reference implementation in intermediate-bootcamp/materials/4-applying-analytical-patterns/lecture-lab/funnel_analysis.sql that demonstrates how to calculate conversion rates using standard SQL constructs. This pattern executes identically on Snowflake, BigQuery, Redshift, and PostgreSQL without proprietary extensions.

The 4-Stage SQL Funnel Pattern

The reference implementation follows a modular architecture that separates data quality concerns from analytical logic. Each stage builds upon the previous using common table expressions (CTEs), making the pipeline readable and maintainable.

Stage 1: Deduplicate Raw Events

Duplicate log entries can inflate visit counts and skew conversion rates. The first stage eliminates noise by grouping on the unique combination of url, host, user_id, and event_time to collapse identical events into single records.

WITH deduped_events AS (
    SELECT url, host, user_id, event_time
    FROM events
    GROUP BY url, host, user_id, event_time
)

Stage 2: Clean and Enrich Event Data

The pipeline derives an event_date column using DATE(event_time) and filters out rows where user_id IS NULL to ensure only authenticated users are tracked. Ordering by user_id and event_time prepares the dataset for sequential analysis.

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

Stage 3: Identify Conversions with Self-Joins

For each event (ce1), the query joins to later events (ce2) from the same user on the same day where ce2.event_time > ce1.event_time. When ce2.url matches the target conversion endpoint ('/api/v1/user'), the user is flagged as converted using COUNT(DISTINCT CASE WHEN ...) to ensure each user-day contributes at most one conversion flag.

converted AS (
    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
)

Stage 4: Aggregate Funnel Metrics

The final aggregation groups by the initial url (representing the funnel step) and computes total visits with COUNT(1) and conversion rates with CAST(SUM(converted) AS REAL) / COUNT(*). A HAVING clause filters out steps with negligible traffic (< 100 visits) or zero conversion rates to ensure statistical significance.

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;

Complete SQL Funnel Analysis Implementation

Here is the complete, runnable pattern from the Data Engineer Handbook:

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 *, DATE(event_time) AS event_date
    FROM deduped_events
    WHERE user_id IS NOT NULL
    ORDER BY user_id, event_time
),
converted AS (
    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
)
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;

Extending to Multi-Step Funnels

To track progression through multiple sequential steps (e.g., step1 → step2 → purchase), replace the single conversion flag with a series of step indicators and compute progressive conversion ratios:

WITH base AS (
    -- Include deduplication and cleaning CTEs from above
    SELECT *, DATE(event_time) AS event_date
    FROM deduped_events
    WHERE user_id IS NOT NULL
),
step_flags AS (
    SELECT user_id,
           event_date,
           MAX(CASE WHEN url = '/step1' THEN 1 END) AS step1,
           MAX(CASE WHEN url = '/step2' THEN 1 END) AS step2,
           MAX(CASE WHEN url = '/purchase' THEN 1 END) AS purchase
    FROM base
    GROUP BY user_id, event_date
)
SELECT
    SUM(step1) AS step1_visits,
    SUM(step2) / NULLIF(SUM(step1),0) AS step1_to_step2_rate,
    SUM(purchase) / NULLIF(SUM(step2),0) AS step2_to_purchase_rate
FROM step_flags;

Why This Pattern Works Across Data Warehouses

Because the solution relies solely on standard SQL—CTEs, self-joins, aggregate functions, and date casting—it executes identically on Snowflake, BigQuery, Redshift, and PostgreSQL. The self-join approach avoids window functions that may have vendor-specific syntax differences, making the pattern truly portable for analytics teams working in multi-platform environments.

Summary

• Deduplicate events using GROUP BY on url, host, user_id, and event_time to eliminate log noise before analysis
• Clean data by deriving event_date columns and filtering null user_id values to ensure accurate user tracking
• Detect conversions via self-joins on user_id and event_date with time-sequencing logic (event_time > current_time)
• Calculate metrics using SUM(converted) / COUNT(*) and apply HAVING clauses to filter out statistically insignificant steps
• Reference the source in intermediate-bootcamp/materials/4-applying-analytical-patterns/lecture-lab/funnel_analysis.sql for the complete production implementation

Frequently Asked Questions

What is funnel analysis in SQL?

Funnel analysis in SQL measures user progression through defined steps toward a conversion goal by tracking how many users move from one stage to the next within a specified timeframe. It typically involves joining event data to itself to determine whether users who triggered an initial event later completed a target action, enabling calculation of drop-off rates between steps.

How do you handle duplicate events in SQL funnel analysis?

Remove duplicates by grouping on the unique combination of dimensions that define a single user action, such as url, host, user_id, and event_time. This GROUP BY operation collapses repeated log entries from the same user interaction into single records, preventing inflated visit counts and skewed conversion rates in your final metrics.

Can this SQL funnel pattern run on BigQuery and Snowflake?

Yes, this pattern uses standard ANSI SQL constructs—including CTEs, self-joins, and basic aggregate functions—that execute identically across BigQuery, Snowflake, Redshift, PostgreSQL, and other modern data warehouses without modification. The deliberate avoidance of proprietary window functions or syntax ensures cross-platform compatibility.

How do you calculate conversion rates between funnel steps?

Calculate step-to-step conversion rates by dividing the number of users who reached the target step by the number who entered the previous step, using SUM(conversion_flag) / COUNT(*) for binary flags or SUM(next_step) / NULLIF(SUM(current_step),0) for multi-step sequences. The NULLIF function prevents division-by-zero errors when preceding steps have no recorded visits.

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