Every business has a "funnel"—the path users take from first visiting your site to becoming a paying customer. But most users drop off along the way.
Funnel analysis helps you pinpoint exactly where they drop off.
- Do 50% of users leave after the landing page?
- Do 80% abandon their cart?
In this guide, we'll build a conversion funnel from scratch using SQL.
The Data Model
We'll use a simple events_funnel table that tracks user actions:
| user_id | event_name | event_time |
|---|---|---|
| 1 | view_landing | 10:00 |
| 1 | add_to_cart | 10:05 |
| 1 | purchase | 10:10 |
| 2 | view_landing | 11:00 |
Method 1: The Step-by-Step Aggregate
The easiest way to build a funnel is to count unique users who performed each action.
The Problem: This approach doesn't guarantee order. A user who did purchase then view_landing would still count for both steps, even though they didn't follow the funnel path.
Method 2: The Ordered Funnel (Using Left Joins)
To ensure users followed step 1 -> step 2 -> step 3, we join the steps together.
Method 3: The Single-Query Pivot
For advanced users, you can do this in one pass using CASE WHEN aggregation. This is much faster on large datasets.
SELECT
COUNT(DISTINCT user_id) as total_users,
COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_id END) as cart_users,
COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) as purchase_users
FROM events_funnel;
Calculating Drop-off Rates
The most important insight is the conversion rate between steps.
If you have:
- Landing: 1000 users
- Cart: 200 users (20% conversion)
- Purchase: 50 users (25% conversion)
You know your biggest problem is getting people to add items to the cart, not checkout!
Test Your Skills with Real Interview Questions
Ready to apply funnel analysis techniques? Try this Salesforce interview question:
- Lead Conversion Funnel (Salesforce) - Calculate conversion rates at each stage of a B2B sales funnel (Lead -> Opportunity -> Closed Won). This question challenges you to compute both counts and percentage drop-offs between varied stages.
Conclusion
Building funnels in SQL gives you granular control over your data. Unlike pre-built analytics tools, you can define custom steps, handle complex user journeys, and filter by any attribute in your database.
Start with simple aggregations, then graduate to ordered funnels as your needs grow!