Conversion Funnel Analysis
The growth lead is preparing a board slide and has two questions: where does the shop lose people on the way to a purchase, and is that getting better or worse over time?
You will answer with ecom_events, the clickstream you toured during exploration, with the funnel view → add_to_cart → checkout → purchase. This page uses the any-touch convention: a customer counts at a step if they ever fired it. The mechanics of funnels (any-touch vs time-ordered, time-to-convert) are drilled separately in Event funnels — four drills; here the focus is turning funnel SQL into a business answer.
1. The Board Slide: Funnel at a Glance #
Task: build the funnel table the slide needs — one row per step, in funnel order, each step expressed as a share of the top of the funnel. Output:
step_order: 1 for view, 2 for add_to_cart, 3 for checkout, 4 for purchasestep: the event typeusers: distinct customers who ever fired that eventpct_of_viewers:ROUND(100.0 * users / viewers-at-step-1, 1), with the denominator guarded byNULLIF(..., 0)
Order by step_order. The top row reads 100.0 by construction. That is the sanity check the growth lead will do first.
2. Monthly Funnel Trend #
Task: the second board question. Is conversion moving? Compress the funnel to its endpoints and trend it by calendar month. Output, one row per month with any events:
month_start:DATE_TRUNC('month', event_timestamp)viewers: distinct customers with aviewevent that monthpurchasers: distinct customers with apurchaseevent that monthview_to_purchase_pct:ROUND(100.0 * purchasers / NULLIF(viewers, 0), 1)
Order by month_start. A customer counts in every month they were active. This is a per-month activity trend, not a cohort that follows one group of users across months.
3. Where Would You Intervene? #
Last question on the slide, and it is not a SQL question: given one engineering sprint, which funnel step do you fix first?
Use the sandbox below to pull the evidence — it lists the customers who reached checkout but never purchased, the highest-intent users the shop lost. Then form your answer before opening the model answer: which step loses the most people, which step loses the most valuable people, and what would you actually build?
Model answer. The funnel loses 2 of 9 viewers before the cart (22%), 1 of 7 before checkout, and 2 of 6 between checkout and purchase (33%). The last drop is the one to fix first: checkout abandoners are the highest-intent users in the funnel, the absolute loss is as large as the top-of-funnel drop, and the fix is cheap and targeted — a checkout-recovery email or a friction audit of the payment step for the two abandoners this query surfaces. The monthly trend adds a nuance: conversions cluster (September converts every viewer, most months convert none), which suggests customers research for weeks before buying — so judge any intervention on a multi-month window, and treat all of these percentages with care: at this sample size, one customer moves a step by double digits.
Next Step #
Finish the track: