SQL funnels and conversion analysis: measuring progress through ordered steps

What is a funnel?

Many products want to understand how users progress through a sequence of actions. For an online store, a purchase funnel might be view product, then add to cart, then start checkout, then purchase. Not every viewer adds to cart, not every cart begins checkout, and not every checkout completes. A funnel answers how many users reached each stage, where they drop out, and what percentage complete the journey. Consider the following event data:

The intended funnel is view_product then add_to_cart then checkout_started then purchase. At first glance it may seem enough to count how many users performed each event, but a funnel has another requirement: the stages must happen in the correct order. That changes the analysis.

Counting each event independently

We could begin with conditional aggregation, counting distinct users per event:

SELECT
    COUNT(DISTINCT CASE WHEN event_name = 'view_product'     THEN user END) AS viewed_users,
    COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart'      THEN user END) AS cart_users,
    COUNT(DISTINCT CASE WHEN event_name = 'checkout_started' THEN user END) AS checkout_users,
    COUNT(DISTINCT CASE WHEN event_name = 'purchase'         THEN user END) AS purchase_users
FROM events;

The numbers look reasonable, but look at the users. Asha completed view, cart, checkout, purchase in order. Ben performed three funnel events, but checkout happened before add-to-cart. Dev has a purchase event but no earlier funnel events. So the independent counts say two users purchased, but only one user actually completed view then cart then checkout then purchase in the required order.

For this lesson, a user reaches View with a view_product event, Cart when add_to_cart occurs after their qualifying view, Checkout when checkout_started occurs after their qualifying cart, and Purchase when purchase occurs after their qualifying checkout. The funnel therefore requires view_at < cart_at < checkout_at < purchase_at, and each stage depends on the previous one.

Each stage after the previous qualifying stage

Step 1: find the first funnel entry. First, find each user’s first product view:

WITH first_view AS (
    SELECT
        user,
        MIN(event_at) AS view_at
    FROM events
    WHERE event_name = 'view_product'
    GROUP BY user
)
SELECT * FROM first_view
ORDER BY user;

Dev does not appear because no qualifying view exists. Four users entered the funnel.

Step 2: find the first cart event after the view

Now find an add_to_cart event that occurs after each user’s view:

first_cart AS (
    SELECT
        e.user,
        MIN(e.event_at) AS cart_at
    FROM events AS e
    JOIN first_view AS v
        ON v.user = e.user
    WHERE e.event_name = 'add_to_cart'
      AND e.event_at > v.view_at
    GROUP BY e.user
)

Three users reached the second stage. Ben still qualifies for Cart: his cart (10:08) occurred after his view (10:00). The fact that he also had a checkout event at 10:03 does not stop the cart from qualifying. In this example we only require the qualifying funnel stages themselves to occur in order.

Step 3: find checkout after cart

Now the checkout must occur after the qualifying cart event (e.event_at > c.cart_at):

Ben no longer qualifies. His checkout was at 10:03, but his qualifying cart was 10:08, so checkout_at < cart_at. The stage happened, but not in the required funnel order.

Step 4: find purchase after checkout

Finally, the purchase must occur after the qualifying checkout (e.event_at > c.checkout_at):

Only Asha completed the complete ordered funnel.

Counting stage events without enforcing the funnel order

Suppose a dashboard reports Viewed 4, Cart 3, Checkout 2, Purchased 2. Those values came from counting users who performed each event independently.

-- Dashboard (independent counts):
-- Viewed     4
-- Cart       3
-- Checkout   2
-- Purchased  2
 
-- A: 4    B: 3    C: 2    D: 1

Returning ordered funnel counts in one result

Once the stage CTEs are defined, the counts can be returned together. Now the numbers represent ordered progression, not independent occurrence:

WITH first_view AS (
    SELECT user, MIN(event_at) AS view_at
    FROM events
    WHERE event_name = 'view_product'
    GROUP BY user
),
first_cart AS (
    SELECT e.user, MIN(e.event_at) AS cart_at
    FROM events AS e
    JOIN first_view AS v ON v.user = e.user
    WHERE e.event_name = 'add_to_cart'
      AND e.event_at > v.view_at
    GROUP BY e.user
),
first_checkout AS (
    SELECT e.user, MIN(e.event_at) AS checkout_at
    FROM events AS e
    JOIN first_cart AS c ON c.user = e.user
    WHERE e.event_name = 'checkout_started'
      AND e.event_at > c.cart_at
    GROUP BY e.user
),
first_purchase AS (
    SELECT e.user, MIN(e.event_at) AS purchase_at
    FROM events AS e
    JOIN first_checkout AS c ON c.user = e.user
    WHERE e.event_name = 'purchase'
      AND e.event_at > c.checkout_at
    GROUP BY e.user
)
SELECT
    (SELECT COUNT(*) FROM first_view)     AS viewed_users,
    (SELECT COUNT(*) FROM first_cart)     AS cart_users,
    (SELECT COUNT(*) FROM first_checkout) AS checkout_users,
    (SELECT COUNT(*) FROM first_purchase) AS purchase_users;

Conversion and drop-off

Raw stage counts are useful, but conversion rates make the funnel easier to interpret. Each stage-to-stage rate uses the previous stage as the denominator: View to Cart is 3 / 4 = 75%, Cart to Checkout is 1 / 3 = 33.3%, Checkout to Purchase is 1 / 1 = 100%. Overall funnel conversion asks what percentage of entrants eventually purchased, purchase / view = 1 / 4 = 25%, which is different from the 100% checkout-to-purchase rate. Both metrics are useful; they answer different questions.

SELECT
    viewed_users,
    cart_users,
    checkout_users,
    purchase_users,
    100.0 * cart_users     / NULLIF(viewed_users, 0)   AS view_to_cart_pct,
    100.0 * checkout_users / NULLIF(cart_users, 0)     AS cart_to_checkout_pct,
    100.0 * purchase_users / NULLIF(checkout_users, 0) AS checkout_to_purchase_pct,
    100.0 * purchase_users / NULLIF(viewed_users, 0)   AS overall_conversion_pct
FROM funnel_counts;

100.0 ensures decimal arithmetic where integer division would otherwise truncate, and NULLIF(..., 0) protects against division by zero when a stage has no users.

Drop-off

Conversion and drop-off describe the same transition from opposite directions. If 100 users reach View and 70 reach Cart, conversion is 70% and drop-off is 30%. In counts, drop-off = previous stage users - next stage users. For our View to Cart step, 4 - 3 = 1 user dropped; for Cart to Checkout, 3 - 1 = 2 users failed to reach Checkout after qualifying for Cart.

Repeated events, grain, and time windows

Repeated events. Real event streams repeat actions (view, view, view, cart, cart, checkout). The funnel should not count that person three times at View, which is why each stage uses MIN(event_at) per user: it finds one qualifying timestamp so the user becomes one funnel participant. Event count is not user count.

User-level vs session-level vs product-level

A user who views on Monday and completes cart, checkout, and purchase on Friday completes a user-level funnel, but not a “purchase journey in one session” funnel. The correct grain might be user or user + session_id (a session funnel might need PARTITION BY user, session_id or equivalent grouping). Product funnels add another subtlety: if Asha views a Mouse but adds and purchases a Keyboard, a user-level funnel reports View to Cart to Purchase even though the stages concern different products. If the question is “how often does a viewed product become a purchased product,” the key must be user + product_id. Without the correct entity key, SQL can connect unrelated events into one artificial funnel.

Completion windows and the time-window trap

Many funnels require completion within a time limit, such as purchase within seven days of the initial view. The purchase stage then needs another condition, AND e.event_at <= v.view_at + INTERVAL '7 days', so the funnel requires correct stage order and completion within the window.

Strict vs loose, and segments

A loose ordered funnel allows unrelated events between required stages; only the required stages must occur in order. A stricter funnel requires the events to occur consecutively with no intervening conflicting activity. The queries here use a loose ordered funnel. Funnels also become more useful compared across segments (mobile vs web, or new vs returning users), but the segmentation attribute should have a clear meaning, for example “channel at first funnel entry” rather than “some channel associated with the user.”

How to spot funnel problems

Think about funnel analysis when the requirement says users who progressed from one action to another, conversion rate, checkout or signup funnel, onboarding or activation steps, drop-off between stages, users who completed all steps, or completion within a time limit.

Watch for these common mistakes:

• Stages are counted independently rather than sequentially.
• Later-stage users did not complete earlier stages.
• Repeated events inflate stage counts.
• User-level events from different sessions are combined accidentally.
• Events for different products are connected into one journey.
• No time window is defined.
• The denominator for conversion is unclear.
• The entry cohort and completion period are mixed together.
• Different teams use different funnel definitions for the same metric name.

Answer six questions before writing funnel SQL

One more prediction

Consider Ben’s events below. The required funnel is View then Add to Cart then Checkout. Does Ben reach the Checkout stage of this ordered funnel?

Required: View -> Add to Cart -> Checkout
 
-- A: yes, because a checkout event exists
-- B: yes, because all three event types exist
-- C: no, checkout happened before the qualifying cart event
-- D: no, because every funnel must end in purchase

Summary

A funnel is not simply a collection of event counts; it represents ordered progression. For View then Cart then Checkout then Purchase, the required timestamps satisfy view_at < cart_at < checkout_at < purchase_at, so each stage is built from the previous qualifying stage, for example WHERE e.event_name = 'add_to_cart' AND e.event_at > v.view_at, then checkout after cart, then purchase after checkout. This prevents a user from being counted at a downstream stage merely because the event occurred somewhere in their history.