Lesson 6 of 11 · 24 min
Funnel analysis
By the end of this lesson you can build a conversion funnel from session flags or from raw events, compute step-to-step and overall conversion rates, and avoid the population errors that make funnels lie.
A funnel is an ordered list of steps that a user takes towards a goal. In Wadi Market’s web and app sessions (fact_web_sessions, one row per visit) the steps are:
- Session: the visit started.
- Product page:
reached_product_page. - Cart:
added_to_cart. - Checkout:
started_checkout. - Purchase:
purchased.
The flags are cumulative: a session that purchased also reached the product page, added to cart and started checkout. So every step’s count is less than or equal to the previous one, and counting each flag gives the funnel directly.
Two rates describe a funnel:
- Step conversion = step n ÷ step n − 1 (of those who added to cart, how many started checkout?). It tells you where people drop off.
- Overall conversion = step n ÷ step 1 (of all sessions, how many purchased?). It tells you how much the whole journey yields.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons