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

> Master funnel analysis using SQL. This guide shows data engineers how to track user progression, deduplicate events, and aggregate metrics for actionable insights.

- Repository: [DataExpert.io/data-engineer-handbook](https://github.com/DataExpert-io/data-engineer-handbook)
- Tags: how-to-guide
- Published: 2026-08-06

---

**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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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.