Building Conversion Funnels in SQL

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;

The Hard Part: Defining the Funnel Correctly

Funnel SQL looks simple until the business definition gets real.

Questions you must answer up front:

• Does every step need to happen in order?
• Does it need to happen in the same session?
• Is there a time window between steps?
• Can a user repeat a step many times?
• Do you count first conversion only, or any conversion?

Those questions matter more than the syntax because two valid SQL funnels can measure completely different user behavior.

Ordered Funnels vs Loose Stage Counts

The first method in this article is really a stage participation report, not a strict funnel. It tells you how many distinct users performed each event, but not whether they progressed through the sequence correctly.

That is still useful when the business question is:

• "How many users ever reached each stage?"

But it is not enough when the business question is:

• "How many users moved from landing to cart to purchase in order?"

That distinction should be explicit in analytics work. Otherwise teams compare two charts that appear to show the same funnel but are actually measuring different things.

A Strong Funnel Workflow

Use this process when a funnel is important enough to drive product or marketing decisions:

1. Write the funnel stages in business language.
2. Decide whether the funnel is strict or loose.
3. Define the time window between steps.
4. Validate each step population separately.
5. Validate the step-to-step joins on a small sample of users.
6. Only then calculate conversion and drop-off percentages.

That workflow prevents the most common analytics mistake: getting a clean chart from a broken definition.

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.

Related Articles

• Cohort Analysis with SQL for retention-style analysis when the focus is how groups behave over time after acquisition.
• Time Series Analysis with SQL for turning funnel counts into trend and growth reporting over time.
• Customer Segmentation with RFM Analysis in SQL for what to do after the funnel, when you need to segment the converted or retained customer base.

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!