TECH **Funnel Analysis with Multi-Step SQL: Zero-Fluff Study Guide**

Funnel Analysis with Multi-Step SQL: Zero-Fluff Study Guide

(For Data Analysts, SQL Engineers, and Certification Prep)

1. What This Is & Why It Matters

Funnel analysis tracks how users progress through a sequence of steps—like signing up, adding items to a cart, and checking out. It answers: - Where are users dropping off? - Which step has the highest abandonment rate? - How does conversion differ by user segment (e.g., mobile vs. desktop)?

Why it matters in production:
- E-commerce: If 80% of users add items to cart but only 20% checkout, you’re losing revenue. Funnel analysis pinpoints the leak.
- SaaS: If users sign up but never complete onboarding, you’re wasting acquisition spend.
- Marketing: If a campaign drives traffic but no conversions, you’re burning budget on the wrong audience.

Real-world scenario:
You’re a data analyst at an e-commerce startup. The CEO says, "Our checkout conversion is terrible—fix it." You inherit a user_events table with millions of rows. Without funnel analysis, you’re guessing. With it, you can: 1. Define the steps (e.g., view_product → add_to_cart → initiate_checkout → complete_purchase).
2. Measure drop-off at each step.
3. Compare funnels by device, traffic source, or user cohort.

If you ignore funnel analysis:
- You’ll optimize the wrong steps (e.g., improving product pages when the real issue is a broken checkout button).
- You’ll miss hidden bottlenecks (e.g., a 30-second payment processing delay causing 40% abandonment).
- You’ll waste engineering resources on features users don’t use.

2. Core Concepts & Components

1. Funnel Step

• Definition: A discrete action in a user journey (e.g., view_product, add_to_cart).
• Production insight: Steps must be mutually exclusive and sequential. If a user can skip steps (e.g., checkout without adding to cart), your funnel breaks.

2. Conversion Rate

• Definition: % of users who complete a step out of those who started it.
  Conversion Rate = (Users at Step N) / (Users at Step N-1)
• Production insight: A 5% drop in checkout conversion can mean millions in lost revenue for high-traffic sites.

3. Drop-off Rate

• Definition: % of users who don’t proceed to the next step.
  Drop-off Rate = 1 - Conversion Rate
• Production insight: High drop-off at early steps (e.g., product page) suggests UX issues; late steps (e.g., payment) suggest trust or friction problems.

4. Cohort

• Definition: A group of users who share a characteristic (e.g., "signed up in January" or "iOS users").
• Production insight: Comparing funnels by cohort reveals if changes (e.g., a new checkout flow) actually improved conversion.

5. Time-to-Conversion

• Definition: How long it takes users to move between steps.
• Production insight: If users take 5 minutes to go from add_to_cart to checkout, you might need a "save cart" feature.

6. Window Function

• Definition: SQL functions (ROW_NUMBER(), LAG(), LEAD()) that operate on a "window" of rows (e.g., "for each user, show their first 3 events").
• Production insight: Without window functions, funnel analysis becomes 10x slower (you’d need self-joins or subqueries).

7. Event Table

• Definition: A table logging user actions (e.g., user_id, event_name, timestamp).
• Production insight: If your event table lacks user_id or timestamp, funnel analysis is impossible.

8. Sessionization

• Definition: Grouping events into "sessions" (e.g., all events within 30 minutes of each other).
• Production insight: Without sessionization, you’ll count the same user multiple times if they return later.

3. Step-by-Step Hands-On: Build a 3-Step Funnel

Prerequisites

• A SQL environment (e.g., BigQuery, Snowflake, PostgreSQL, or a local DB with sample data).
• A table user_events with columns: sql user_id (INT), event_name (STRING), event_time (TIMESTAMP)
• Sample data (run this to create a test table): sql CREATE TABLE user_events AS WITH sample_data AS (
SELECT 1 AS user_id, 'view_product' AS event_name, TIMESTAMP '2023-01-01 10:00:00' AS event_time UNION ALL
SELECT 1, 'add_to_cart', '2023-01-01 10:05:00' UNION ALL
SELECT 1, 'initiate_checkout', '2023-01-01 10:10:00' UNION ALL
SELECT 1, 'complete_purchase', '2023-01-01 10:15:00' UNION ALL
SELECT 2, 'view_product', '2023-01-01 11:00:00' UNION ALL
SELECT 2, 'add_to_cart', '2023-01-01 11:05:00' UNION ALL
SELECT 2, 'initiate_checkout', '2023-01-01 11:10:00' UNION ALL
SELECT 3, 'view_product', '2023-01-01 12:00:00' UNION ALL
SELECT 3, 'add_to_cart', '2023-01-01 12:05:00' UNION ALL
SELECT 4, 'view_product', '2023-01-01 13:00:00' ) SELECT * FROM sample_data;

Step 1: Define the Funnel Steps

Your funnel: 1. view_product 2. add_to_cart 3. initiate_checkout

Step 2: Count Users at Each Step

WITH step_counts AS (
  SELECT
    event_name,
    COUNT(DISTINCT user_id) AS users
  FROM user_events
  WHERE event_name IN ('view_product', 'add_to_cart', 'initiate_checkout')
  GROUP BY event_name
)
SELECT * FROM step_counts ORDER BY users DESC;

Expected output:

event_name       | users
-----------------+------
view_product     | 4
add_to_cart      | 3
initiate_checkout| 2

Step 3: Calculate Conversion Rates

WITH step_counts AS (
  SELECT
    event_name,
    COUNT(DISTINCT user_id) AS users
  FROM user_events
  WHERE event_name IN ('view_product', 'add_to_cart', 'initiate_checkout')
  GROUP BY event_name
),
funnel AS (
  SELECT
    'view_product → add_to_cart' AS step,
    (SELECT users FROM step_counts WHERE event_name = 'add_to_cart') * 100.0 /
    (SELECT users FROM step_counts WHERE event_name = 'view_product') AS conversion_rate
  UNION ALL
  SELECT
    'add_to_cart → initiate_checkout' AS step,
    (SELECT users FROM step_counts WHERE event_name = 'initiate_checkout') * 100.0 /
    (SELECT users FROM step_counts WHERE event_name = 'add_to_cart') AS conversion_rate
)
SELECT * FROM funnel;

Expected output:

step                          | conversion_rate
------------------------------+-----------------
view_product → add_to_cart    | 75.0
add_to_cart → initiate_checkout| 66.66666666666666

Step 4: Add Time-to-Conversion (Advanced)

WITH user_journeys AS (
  SELECT
    user_id,
    event_name,
    event_time,
    LEAD(event_name) OVER (PARTITION BY user_id ORDER BY event_time) AS next_event,
    LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS next_event_time
  FROM user_events
  WHERE event_name IN ('view_product', 'add_to_cart', 'initiate_checkout')
),
time_to_convert AS (
  SELECT
    user_id,
    event_name,
    next_event,
    TIMESTAMPDIFF(MINUTE, event_time, next_event_time) AS minutes_to_next_step
  FROM user_journeys
  WHERE next_event IS NOT NULL
)
SELECT
  event_name || ' → ' || next_event AS step,
  AVG(minutes_to_next_step) AS avg_minutes_to_convert
FROM time_to_convert
GROUP BY step;

Expected output:

step                          | avg_minutes_to_convert
------------------------------+-----------------------
view_product → add_to_cart    | 5.0
add_to_cart → initiate_checkout| 5.0

Step 5: Compare Funnels by Cohort (e.g., Device Type)

(Assume user_events has a device_type column.)

WITH funnel_by_device AS (
  SELECT
    device_type,
    COUNT(DISTINCT CASE WHEN event_name = 'view_product' THEN user_id END) AS view_product_users,
    COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_id END) AS add_to_cart_users,
    COUNT(DISTINCT CASE WHEN event_name = 'initiate_checkout' THEN user_id END) AS initiate_checkout_users
  FROM user_events
  WHERE event_name IN ('view_product', 'add_to_cart', 'initiate_checkout')
  GROUP BY device_type
)
SELECT
  device_type,
  view_product_users,
  add_to_cart_users,
  initiate_checkout_users,
  ROUND(add_to_cart_users * 100.0 / view_product_users, 2) AS view_to_cart_rate,
  ROUND(initiate_checkout_users * 100.0 / add_to_cart_users, 2) AS cart_to_checkout_rate
FROM funnel_by_device;

Expected output (if device_type exists):

device_type | view_product_users | add_to_cart_users | initiate_checkout_users | view_to_cart_rate | cart_to_checkout_rate
------------+--------------------+-------------------+------------------------+-------------------+----------------------
mobile      | 1000               | 600               | 300                    | 60.0              | 50.0
desktop     | 2000               | 1800              | 1500                   | 90.0              | 83.33

4. ? Production-Ready Best Practices

Performance

• Index user_id and event_time: Funnel queries scan millions of rows. Indexes speed up DISTINCT and GROUP BY.
• Use COUNT(DISTINCT) sparingly: On large tables, it’s slow. If possible, pre-aggregate user counts.
• Limit time ranges: Filter by date (e.g., WHERE event_time > CURRENT_DATE - INTERVAL '30 days') to avoid full-table scans.

Accuracy

• Sessionize events: If a user returns after 24 hours, should that count as a new session? Define a timeout (e.g., 30 minutes).
• Deduplicate events: Some users trigger the same event multiple times (e.g., refreshing a page). Use ROW_NUMBER() to keep only the first occurrence.
• Handle partial funnels: If a user skips a step (e.g., goes straight to checkout), decide whether to exclude them or count them as "dropped off."

Maintainability

• Store funnel definitions in a table: Instead of hardcoding steps in SQL, create a funnel_steps table: sql CREATE TABLE funnel_steps (
step_order INT,
step_name STRING ); INSERT INTO funnel_steps VALUES (1, 'view_product'), (2, 'add_to_cart'), (3, 'initiate_checkout');
• Automate with dbt or Airflow: Schedule funnel queries to run daily and output to a dashboard (e.g., Metabase, Tableau).

Visualization

• Use a funnel chart: Tools like Tableau or Looker have built-in funnel visualizations.
• Highlight drop-off steps: Color-code steps with >20% drop-off in red.
• Add annotations: Explain why a step might have high drop-off (e.g., "Checkout button broken on mobile").

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus

Typical Question Patterns

1. Given a table of events, calculate conversion rates between steps.
2. Trick: Watch for COUNT(DISTINCT) vs. COUNT(*). The former is usually correct.
3. Identify the step with the highest drop-off.
4. Trick: The answer is often the step with the largest absolute drop in users, not the highest percentage.
5. Compare funnels by cohort (e.g., mobile vs. desktop).
6. Trick: The question might ask for relative improvement, not absolute numbers.
7. Calculate time-to-conversion between steps.
8. Trick: Use TIMESTAMPDIFF() or DATEDIFF() (syntax varies by SQL dialect).

Key Trap Distinctions

• COUNT(DISTINCT) vs. COUNT(*):
• COUNT(DISTINCT user_id) = unique users.
• COUNT(*) = total events (including duplicates).
• Sessionization:
• Without it, a user who returns after 24 hours is counted as a new user.
• Funnel order:
• Steps must be sequential. If a user can skip steps, the funnel is invalid.

Example Scenario Question

"You have a table user_events with columns user_id, event_name, and event_time. Write a query to calculate the conversion rate from view_product to add_to_cart."

Answer:

SELECT
  COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_id END) * 100.0 /
  COUNT(DISTINCT CASE WHEN event_name = 'view_product' THEN user_id END) AS conversion_rate
FROM user_events
WHERE event_name IN ('view_product', 'add_to_cart');

Why it works:
- COUNT(DISTINCT) ensures each user is counted once.
- The WHERE clause filters to only the relevant steps.
- * 100.0 converts the ratio to a percentage.

7. ? Hands-On Challenge

Challenge:
Using the user_events table from earlier, write a query to: 1. Count the number of users who completed all 3 steps (view_product → add_to_cart → initiate_checkout).
2. Calculate the overall conversion rate (users who completed all steps / users who started the funnel).

Solution:

WITH funnel_users AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_name = 'view_product' THEN 1 ELSE 0 END) AS has_viewed,
    MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS has_added,
    MAX(CASE WHEN event_name = 'initiate_checkout' THEN 1 ELSE 0 END) AS has_initiated
  FROM user_events
  WHERE event_name IN ('view_product', 'add_to_cart', 'initiate_checkout')
  GROUP BY user_id
)
SELECT
  COUNT(*) AS users_completed_all_steps,
  COUNT(*) * 100.0 / SUM(has_viewed) AS overall_conversion_rate
FROM funnel_users
WHERE has_viewed = 1 AND has_added = 1 AND has_initiated = 1;

Expected output:

users_completed_all_steps | overall_conversion_rate
--------------------------+-------------------------
2                         | 50.0

Why it works:
- The funnel_users CTE flags whether each user completed each step.
- The outer query counts users who completed all steps and divides by the total users who started the funnel.

8. ? Rapid-Reference Crib Sheet

9. ? Where to Go Next

1. Google’s Funnel Analysis Guide – How to set up funnels in Google Analytics.
2. Mode Analytics SQL Tutorial – Interactive SQL exercises, including funnel analysis.
3. dbt Funnel Analysis Package – Pre-built dbt macros for funnels.
4. SQL for Data Analysis (O’Reilly) – Chapter