Funnel Analysis for Data Analysts

What Is Funnel Analysis?

Funnel analysis is a method for measuring how users progress through a defined sequence of steps toward a goal — such as signing up, making a purchase, or completing onboarding. Each step in the sequence is a stage of the funnel. Users who do not complete a stage "drop off," and the analyst's task is to measure drop-off rates at each step, identify where the largest losses occur, and understand why.

The term "funnel" reflects the shape of the data: many users enter at the top (awareness or first touch), and progressively fewer complete each subsequent step, so the count narrows toward the bottom (conversion). Funnel analysis is widely used in e-commerce, SaaS, mobile apps, and marketing because it directly connects user behavior to business outcomes.

Core Funnel Metrics

Defining the Funnel

Before querying data, the analyst must define three things precisely: the ordered sequence of events that constitute the funnel, the conversion window (the maximum time allowed between the first and last step), and the unit of analysis (user, session, or account).

Ordered events example (e-commerce checkout):

The conversion window prevents attributing a purchase made weeks later to a funnel that began with a single page view. Common windows range from 30 minutes (session-based) to 7 or 30 days (considered purchase cycles).

Computing a Funnel in SQL

The standard SQL approach uses a self-join or window functions to check whether each user completed subsequent steps within the window after their first step.

WITH step1 AS (
SELECT user_id, MIN(event_time) AS t1
FROM events WHERE event_name = 'product_viewed'
GROUP BY user_id
),
step2 AS (
SELECT e.user_id, MIN(e.event_time) AS t2
FROM events e JOIN step1 s ON e.user_id = s.user_id
WHERE e.event_name = 'add_to_cart' AND e.event_time BETWEEN s.t1 AND s.t1 + INTERVAL '7 days'
GROUP BY e.user_id
),
step3 AS (
SELECT e.user_id, MIN(e.event_time) AS t3
FROM events e JOIN step2 s ON e.user_id = s.user_id
WHERE e.event_name = 'checkout_started' AND e.event_time BETWEEN s.t2 AND s.t2 + INTERVAL '7 days'
GROUP BY e.user_id
)
SELECT
COUNT(DISTINCT s1.user_id) AS step1_users,
COUNT(DISTINCT s2.user_id) AS step2_users,
COUNT(DISTINCT s3.user_id) AS step3_users,
ROUND(COUNT(DISTINCT s2.user_id)::numeric / COUNT(DISTINCT s1.user_id), 3) AS step1_to_2_rate,
ROUND(COUNT(DISTINCT s3.user_id)::numeric / COUNT(DISTINCT s2.user_id), 3) AS step2_to_3_rate
FROM step1 s1
LEFT JOIN step2 s2 ON s1.user_id = s2.user_id
LEFT JOIN step3 s3 ON s2.user_id = s3.user_id;

Funnel Analysis in Python with pandas

For event-level DataFrames, filter and pivot to compute step counts per user:

import pandas as pd

funnel_steps = ['product_viewed', 'add_to_cart', 'checkout_started', 'order_completed']
window_days = 7

# Get first occurrence of each event per user
first_events = (
df[df['event_name'].isin(funnel_steps)]
.sort_values('event_time')
.groupby(['user_id', 'event_name'])['event_time']
.min()
.reset_index()
.pivot(index='user_id', columns='event_name', values='event_time')
)

# Apply conversion window relative to step 1
base = first_events['product_viewed']
for step in funnel_steps[1:]:
if step in first_events.columns:
first_events[step] = first_events[step].where(
first_events[step] <= base + pd.Timedelta(days=window_days)
)

counts = {step: first_events[step].notna().sum() for step in funnel_steps if step in first_events.columns}
print(counts)

Segmented Funnel Analysis

A single aggregate funnel hides important differences between user segments. Always break down the funnel by dimensions that matter for your business:

Common Pitfalls

Summary

Funnel analysis translates raw event streams into actionable conversion metrics. The key discipline is rigorous funnel definition: specify the ordered steps, the conversion window, and the unit of analysis before touching the data. Once the funnel is built, segmenting by channel, device, cohort, and other dimensions reveals where specific user populations drop off — providing the hypotheses that drive product improvements and A/B tests. Combined with cohort analysis and A/B testing, funnel analysis forms the backbone of data-driven product and growth work.