In this lesson, you will learn how to measure the percentage of users who successfully move from one stage of a business process to the next.
Understanding the Funnel
A funnel represents a sequence of events—such as "View Item," "Add to Cart," and "Checkout." To calculate conversion, we don’t just need to know how many people performed each action; we need to know how many people performed them in order. Since our data is often stored as individual event logs, we use Conditional Aggregation (Lesson 7) to pivot these event stages into columns for each user.
The Conversion SQL Pattern
Imagine an events table with user_id and stage. We first categorize each user’s progress. By using a CASE statement inside a MAX() function, we can determine if a user reached a specific milestone:
SELECT
COUNT(user_id) AS total_visitors,
COUNT(CASE WHEN stage = 'view' THEN user_id END) AS viewed_count,
COUNT(CASE WHEN stage = 'cart' THEN user_id END) AS cart_count,
COUNT(CASE WHEN stage = 'purchase' THEN user_id END) AS purchase_count
FROM events;
Calculating the Drop-off
Once we have these counts, calculating the conversion rate is a matter of simple division. If 100 people viewed an item and 20 added it to their cart, your conversion rate is 20 / 100 = 0.20 or 20%.
To find the drop-off rate, you simply subtract the conversion rate from 1. If 20% converted, 80% dropped off. When writing your query, ensure you cast your integers to FLOAT or DECIMAL types before dividing, otherwise, SQL may perform integer division and return a result of 0.
By observing these ratios across your defined stages, you can identify exactly where your process has the most friction. If your "View to Cart" rate is high but "Cart to Purchase" is low, you know your bottleneck is likely in the payment or checkout flow rather than the product selection page.
Key Takeaway: Funnel conversion is calculated by using conditional aggregation to count unique users at each stage, then dividing sequential stage counts to determine the percentage of retained users.