Funnel Analysis for Data Analysts

What Is Funnel Analysis?

Funnel analysis tracks how many users progress through a defined sequence of steps — from an initial action (e.g., visiting a landing page) to a desired outcome (e.g., completing a purchase). At each step, some users drop off, creating the characteristic funnel shape. The goal is to identify where drop-off is highest, understand why, and prioritise fixes to maximise conversion.

Funnels are used across the product and marketing lifecycle: acquisition funnels (ad click → signup), onboarding funnels (signup → first key action), purchase funnels (add to cart → payment), and feature adoption funnels (feature discovery → repeated use).

Key Funnel Metrics

Building a Funnel in SQL

-- Example: 4-step e-commerce purchase funnel
-- Steps: view_product → add_to_cart → begin_checkout → purchase

WITH funnel AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_type = 'view_product'   THEN 1 ELSE 0 END) AS step1,
    MAX(CASE WHEN event_type = 'add_to_cart'    THEN 1 ELSE 0 END) AS step2,
    MAX(CASE WHEN event_type = 'begin_checkout' THEN 1 ELSE 0 END) AS step3,
    MAX(CASE WHEN event_type = 'purchase'       THEN 1 ELSE 0 END) AS step4
  FROM events
  WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT
  SUM(step1) AS view_product,
  SUM(step2) AS add_to_cart,
  SUM(step3) AS begin_checkout,
  SUM(step4) AS purchase,
  -- Step conversion rates
  ROUND(100.0 * SUM(step2) / NULLIF(SUM(step1), 0), 1) AS view_to_cart_pct,
  ROUND(100.0 * SUM(step3) / NULLIF(SUM(step2), 0), 1) AS cart_to_checkout_pct,
  ROUND(100.0 * SUM(step4) / NULLIF(SUM(step3), 0), 1) AS checkout_to_purchase_pct,
  -- Overall conversion
  ROUND(100.0 * SUM(step4) / NULLIF(SUM(step1), 0), 1) AS overall_conversion_pct
FROM funnel;

Ordered vs Unordered Funnels

The SQL above uses an unordered funnel — it counts users who completed all steps in any order within the time window. An ordered funnel requires each step to happen after the previous one, which is more restrictive and usually more accurate for sequential flows.

-- Ordered funnel: each step must occur AFTER the previous step
WITH step_times AS (
  SELECT
    user_id,
    MIN(CASE WHEN event_type = 'view_product'   THEN event_time END) AS t1,
    MIN(CASE WHEN event_type = 'add_to_cart'    THEN event_time END) AS t2,
    MIN(CASE WHEN event_type = 'begin_checkout' THEN event_time END) AS t3,
    MIN(CASE WHEN event_type = 'purchase'       THEN event_time END) AS t4
  FROM events
  WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT
  COUNT(*) AS entered_funnel,
  COUNT(CASE WHEN t2 > t1 THEN 1 END) AS added_to_cart,
  COUNT(CASE WHEN t3 > t2 AND t2 > t1 THEN 1 END) AS began_checkout,
  COUNT(CASE WHEN t4 > t3 AND t3 > t2 AND t2 > t1 THEN 1 END) AS purchased
FROM step_times
WHERE t1 IS NOT NULL;

Funnel Segmentation

Overall funnel conversion hides variation. Segmenting by device, channel, user type, or geography reveals which groups have the biggest improvement opportunity.

-- Conversion rate by device type
WITH funnel AS (
  SELECT
    user_id,
    device_type,
    MAX(CASE WHEN event_type = 'view_product' THEN 1 ELSE 0 END) AS step1,
    MAX(CASE WHEN event_type = 'purchase'     THEN 1 ELSE 0 END) AS step4
  FROM events
  JOIN users USING (user_id)
  WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY user_id, device_type
)
SELECT
  device_type,
  COUNT(*) AS users_entered,
  SUM(step4) AS conversions,
  ROUND(100.0 * SUM(step4) / COUNT(*), 1) AS conversion_pct
FROM funnel
WHERE step1 = 1
GROUP BY device_type
ORDER BY conversion_pct DESC;

Python Funnel Analysis

Python offers flexible funnel analysis beyond what SQL alone can provide, especially when combining event data with visualization libraries.

import pandas as pd
import matplotlib.pyplot as plt

# Sample event data
events = pd.DataFrame({
    'user_id': [1,1,1,2,2,3,3,3,3,4,5,5,5,5],
    'event':   ['view','cart','purchase','view','cart','view','cart','checkout','purchase','view','view','cart','checkout','purchase']
})

# Define funnel steps in order
steps = ['view', 'cart', 'checkout', 'purchase']

# Count unique users who reached each step
funnel = []
for step in steps:
    users = events[events['event'] == step]['user_id'].nunique()
    funnel.append({'step': step, 'users': users})

df = pd.DataFrame(funnel)
df['pct_of_top'] = (df['users'] / df['users'].iloc[0] * 100).round(1)
df['step_conv'] = (df['users'] / df['users'].shift(1) * 100).round(1)
print(df)

# Visualize
plt.figure(figsize=(8, 5))
plt.bar(df['step'], df['users'], color='steelblue')
plt.title('Funnel: Users per Step')
plt.ylabel('Unique Users')
plt.show()

Time-to-Convert Analysis

Understanding how long users take to move between funnel steps reveals friction points and helps prioritize UX improvements. A purchase that takes 5 days may indicate price sensitivity; one that takes 5 minutes suggests impulse buying.