Funnel conversion

The problem

Learnhaven, a fictional online course shop, has a four-step path to a sale: view a course page, add it to the cart, begin checkout, purchase. Marco, the product lead, has budget for one design sprint this quarter and must name the step it will fix by Friday. A dashboard tile says checkout converts at 15%, which sounds alarming, but nobody has checked how it was counted. You have the raw event log for two weeks of October 2026. You will build the funnel in SQL from those events, user by user, and show Marco where people really drop.

The file data/events.csv is synthetic, generated by make_data.py with a fixed seed. It has 1,971 events from 435 made-up users.

What you will build from scratch

• events_per_step: raw event counts next to distinct user counts, to see the trap.
• funnel: users who reached each step in order, with step conversion and overall conversion.
• biggest_drop: the transition that loses the largest share of users.

The idea

A funnel is an ordered list of steps. A user reaches step n only if they did steps 1 to n, in that order. For each step:

users(n)              = distinct users who reached step n in order
step_conversion(n)    = users(n) / users(n - 1)
overall_conversion(n) = users(n) / users(1)

Overall conversion is the product of the step conversions, so the funnel is a metric tree like the one in lesson 01: improve any single step and the end number moves.

Three rules decide whether the funnel is right.

1. Count users, not events. A user who views a course six times is one user at step 1. Events measure activity, not progress. If you divide purchase events by checkout events, every retry of a failed card counts against you.
2. Require the earlier steps. A user who added to cart from a saved link but never viewed a course page did not enter this funnel. Counting them at step 2 makes step 2 look better than it is.
3. Require the order. Use the first time each user did each step and check that it is at or after the first time they did the step before. This simplified rule is good enough for most dashboards; a strict version would also check that every step happened inside one session or time window.

In SQL, the clean way is one row per user with the first time of each step:

MIN(CASE WHEN event_name = 'view_course' THEN event_time END) AS t1

and then a flag per step, for example step 3 is reached when t1 IS NOT NULL AND t2 >= t1 AND t3 >= t2. In SQLite a comparison with NULL is NULL, and SUM skips NULLs, so users missing a step simply do not count. Window functions turn the counts into rates: LAG(users) is the previous step and FIRST_VALUE(users) is step 1.

Walk through

Step 1. Look at the trap first: events and users per step.

SELECT event_name, COUNT(*) AS events, COUNT(DISTINCT user_id) AS users
FROM events
GROUP BY event_name
ORDER BY CASE event_name
  WHEN 'view_course' THEN 1 WHEN 'add_to_cart' THEN 2
  WHEN 'begin_checkout' THEN 3 WHEN 'purchase' THEN 4 END;

The dashboard's 15% is 31 purchase events over 211 checkout events (14.7%). Checkout events are inflated by users who started checkout several times.

Step 2. One row per user with the first time of each step.

SELECT
  user_id,
  MIN(CASE WHEN event_name = 'view_course' THEN event_time END) AS t1,
  MIN(CASE WHEN event_name = 'add_to_cart' THEN event_time END) AS t2,
  MIN(CASE WHEN event_name = 'begin_checkout' THEN event_time END) AS t3,
  MIN(CASE WHEN event_name = 'purchase' THEN event_time END) AS t4
FROM events
GROUP BY user_id
ORDER BY user_id
LIMIT 5;

Step 3. Turn those times into a flag per step and count. Here is the count for step 2 alone, to show the saved cart users dropping out:

WITH firsts AS (
  SELECT user_id,
    MIN(CASE WHEN event_name = 'view_course' THEN event_time END) AS t1,
    MIN(CASE WHEN event_name = 'add_to_cart' THEN event_time END) AS t2
  FROM events
  GROUP BY user_id
)
SELECT
  SUM(t2 IS NOT NULL) AS added_to_cart_any_way,
  SUM(t1 IS NOT NULL AND t2 >= t1) AS added_to_cart_in_funnel
FROM firsts;

That returns 160 and 145. The 15 users who came in through a saved cart link never viewed a course, so they are not in this funnel.

Step 4. Stack the four counts with UNION ALL and add the rates with LAG and FIRST_VALUE. The full query is in solution/solution.sql once you have tried it. The result:

Checkout converts at 40.3% of users, not 15%. The biggest drop is view to cart: 275 of the 420 viewers (65.5%) never add the course to the cart.

What to tell Marco: "7.4% of people who view a course buy it. The leakiest step is the course page: only 34.5% of viewers add to cart, and 275 people leave there. Checkout is not the problem; the 15% on the dashboard counts retries as separate people. Spend the sprint on the course page."

Your turn

Open exercise.sql and write the three queries under their markers. Then run, from this folder:

python3 test_lesson.py

To see the reference solution pass: CHECK_SOLUTION=1 python3 test_lesson.py.

For more practice on the same skills, try the SQL case study.

Check yourself

1. Why is purchase events divided by checkout events a bad checkout conversion rate?
2. Step conversion and overall conversion: which do you use to pick the step to fix?
3. A user added to cart before ever viewing a course. Should they count at step 2?
4. The funnel above has no time limit. What changes if you require purchase within 24 hours of the first view?

In real job postings

In the Real Analyst Jobs live feed on 25 October 2026, 72% of the 2,102 US analyst postings that list tools ask for SQL, and 70% of the 914 India postings that list tools do too. Funnel queries from raw events are a standard SQL interview task; see SQL for analyst jobs and data analyst interview questions.

Next

03: Active users and stickiness