Funnel Metrics and Conversion Rates from Raw Data

What is a Funnel Analysis?

A funnel analysis tracks how users progress through a defined sequence of steps toward a desired outcome. It gets its name from the shape of the visualization, which narrows as users drop off at each stage.

Imagine an e-commerce website. Thousands of people might visit the landing page, but only a fraction will add a product to their cart, and a smaller fraction still will complete the purchase. This journey is a funnel.

As a Business Analyst, you'll use funnel analysis to answer critical questions like:

• "Where in our signup process are we losing the most users?"
• "What is the conversion rate from 'free trial' to 'paid subscription'?"
• "Did our recent app update improve the user's path to purchasing?"

To understand the value of this skill in the job market, let's hear briefly about why it's considered a key project for any data analyst's portfolio.

The Anatomy of a Funnel: Key Concepts

Before writing any SQL, it's crucial to define the components of your funnel. A poorly defined funnel leads to misleading metrics.

To learn about these core concepts, we'll turn to a guide on the topic.

Understanding these concepts is the "business analysis" part of the task. Your role isn't just to write the code, but to ask the right questions to ensure the analysis accurately reflects the business process.

A Standard SQL Pattern for Funnels

Now, let's get into the SQL. A common and straightforward way to calculate a funnel is to count the number of unique users who completed each step, regardless of order. This is often a great starting point for analysis.

The typical pattern uses COUNT(DISTINCT ...) with a CASE statement for each step of the funnel.

Let's look at a clear example of this pattern.

The general template looks like this:

SELECT
    -- Step 1: e.g., Visited Website
    COUNT(DISTINCT CASE WHEN event_name = 'visited_website' THEN user_id END) AS step_1_users,

    -- Step 2: e.g., Viewed Product
    COUNT(DISTINCT CASE WHEN event_name = 'viewed_product' THEN user_id END) AS step_2_users,

    -- Step 3: e.g., Added to Cart
    COUNT(DISTINCT CASE WHEN event_name = 'added_to_cart' THEN user_id END) AS step_3_users
FROM
    event_table;

From this output, you can easily calculate conversion rates. For example, the conversion rate from Step 2 to Step 3 would be (step_3_users / step_2_users) * 100. This pattern is simple, effective, and a great tool to have ready for analysis.

Case Study: Solving a Real-World Funnel Problem

The simple pattern is great, but real-world scenarios are often more complex, requiring specific attribution logic. Let's walk through a data science interview problem that requires calculating a notification conversion rate. This will bring together everything we've learned about CTEs, joins, and window functions.

We'll follow an expert as she solves this problem, from understanding the data to writing and refining the final query.

Let's recap the logic from that final, optimized query:

1. Define the pool of potential conversions: JOIN clicks (notification_events) with purchases (purchases), keeping only clicks that occurred before the purchase.
2. Identify the converting click: Use the FIRST_VALUE() window function, partitioned by user and ordered by the click timestamp in descending order. This efficiently finds the notification_id of the last click before the purchase.
3. Count conversions per notification type: Aggregate the results from the previous step to get a COUNT of conversions for each notification type.
4. Count total notifications sent: In a separate CTE, count the total number of notifications sent for each type from the notifications table.
5. Calculate the final rate: Join the conversion counts with the total counts and divide to get the conversion rate.

This case study is an excellent example of the thought process required for a Business Analyst role: define the business logic first, then translate it into an efficient and readable SQL query.

WITH funnel_steps AS (
  SELECT
    -- Count users who performed a search
    COUNT(DISTINCT CASE WHEN event_name = 'search_song' THEN user_id END) AS searchers,
    
    -- Count users who both searched and played a song
    -- We can identify them by checking if a user appears in both groups
    COUNT(DISTINCT
      CASE
        WHEN event_name = 'play_song'
        AND user_id IN (SELECT DISTINCT user_id FROM user_activity WHERE event_name = 'search_song')
      THEN user_id
      END
    ) AS players_who_searched
  FROM
    user_activity
)
SELECT
  searchers,
  players_who_searched,
  -- Calculate conversion rate, handling potential division by zero
  ROUND(100.0 * players_who_searched / NULLIF(searchers, 0), 2) AS conversion_rate_pct
FROM
  funnel_steps;

Conclusion

In this lesson, you've learned how to perform one of the most fundamental types of product and business analysis: funnel analysis. You now have the conceptual framework and the SQL patterns needed to track user journeys and quantify conversion.

Key Takeaways:

• Funnel analysis is a method for measuring how users progress through a series of steps.
• Before writing code, you must clearly define your funnel's steps, time windows, and attribution logic (e.g., first-touch vs. last-touch).
• A simple funnel can be built using the COUNT(DISTINCT CASE WHEN ...) pattern for each step.
• More complex attribution logic, like "last-click," can be implemented with CTEs and aggregations, or more elegantly with window functions like FIRST_VALUE, building directly on your advanced SQL skills.

Preview of the Next Lesson:
We've just analyzed a user's journey through a sequence of events in a relatively short timeframe. In our next lesson, we will zoom out to analyze user behavior over a much longer period. We will "Calculate cohort data using SQL for retention analysis," a technique that helps you understand user loyalty and the long-term value your product delivers.