Learn Analyzing Funnel Conversion Rates

Conversion rates are a key metric in funnel analysis, helping you understand what percentage of users successfully move from one funnel step to the next. To calculate a conversion rate between two steps, divide the number of users who completed the later step by the number who completed the previous step, then multiply by 100 to get a percentage. For example, if 100 users viewed a product and 60 added it to their cart, the conversion rate from view_product to add_to_cart would be (60 / 100) * 100 = 60%. Interpreting these results allows you to see how efficiently users progress through your funnel and where significant drop-offs may occur.

Note

Conversion rates help marketers identify where users drop off in the funnel. By focusing on steps with low conversion rates, you can optimize those stages to improve overall performance.

The WITH clause in SQL, also called a Common Table Expression (CTE), lets you define temporary named result sets that you can reference within a larger query. Using CTEs helps you organize complex queries by breaking them into simpler, logical parts. This improves readability and allows you to reuse the same intermediate results in different parts of your query.


              12345678910111213141516171819202122232425262728
            
-- Calculate conversion rates between funnel steps
WITH step_counts AS (
  SELECT
    fs.step_order,
    fs.event_type,
    COUNT(DISTINCT e.user_id) AS users_at_step
  FROM funnel_steps fs
  LEFT JOIN events e
    ON fs.event_type = e.event_type
  GROUP BY fs.step_order, fs.event_type
  ORDER BY fs.step_order
),
conversion_rates AS (
  SELECT
    s1.event_type AS from_step,
    s2.event_type AS to_step,
    s1.users_at_step AS from_users,
    s2.users_at_step AS to_users,
    ROUND(
      CASE WHEN s1.users_at_step > 0
        THEN (s2.users_at_step::decimal / s1.users_at_step) * 100
        ELSE 0 END, 2
    ) AS conversion_rate_percent
  FROM step_counts s1
  JOIN step_counts s2
    ON s2.step_order = s1.step_order + 1
)
SELECT * FROM conversion_rates;

Common bottlenecks in funnels are steps where a significant percentage of users fail to proceed to the next action. You can spot these bottlenecks by examining conversion rates between each pair of steps. A sharp drop in the conversion rate indicates a potential problem area. For instance, if many users view products but few add items to their cart, the view_product to add_to_cart step may need attention. Analyzing funnel data this way helps you prioritize improvements and test new ideas to increase overall conversions.


              123456789101112131415161718192021222324252627
            
-- Calculate cumulative conversion rates using window functions
WITH step_counts AS (
  SELECT
    fs.step_order,
    fs.event_type,
    COUNT(DISTINCT e.user_id) AS users_at_step
  FROM funnel_steps fs
  LEFT JOIN events e
    ON fs.event_type = e.event_type
  GROUP BY fs.step_order, fs.event_type
  ORDER BY fs.step_order
),
cumulative_rates AS (
  SELECT
    event_type,
    users_at_step,
    FIRST_VALUE(users_at_step) OVER (ORDER BY step_order) AS first_step_users,
    ROUND(
      CASE WHEN FIRST_VALUE(users_at_step) OVER (ORDER BY step_order) > 0
        THEN (users_at_step::decimal / FIRST_VALUE(users_at_step) OVER (ORDER BY step_order)) * 100
        ELSE 0 END, 2
    ) AS cumulative_conversion_percent
  FROM step_counts
)
SELECT event_type, users_at_step, cumulative_conversion_percent
FROM cumulative_rates
ORDER BY event_type;

Task

Swipe to start coding

Write a SQL query that calculates the conversion rates between each consecutive funnel step using the provided tables. Your query should:

Output one row for each pair of consecutive funnel steps (for example, from signup to view_product).
Include the following columns in the output:
- from_step: the event type of the previous step;
- to_step: the event type of the next step;
- from_users: the number of unique users who completed the previous step;
- to_users: the number of unique users who completed the next step;
- conversion_rate_percent: the percentage of users who moved from the previous step to the next, rounded to two decimal places.
Use only the provided tables: funnel_steps, events, and users.
Do not print or comment the output; your query should only return the requested columns.

Solution

Everything was clear?

Thanks for your feedback!

Section 2. Chapter 2

single

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Swipe to show menu