Funnels

Why it matters

A funnel is a sequence of steps users are meant to pass through: visit the site, sign up, add to cart, purchase. At each step some people drop out. Funnel analysis counts how many make it through each step and where the biggest drop-off is, which tells product and marketing teams where to focus. A 2-point improvement at the weakest step is often worth more than doubling traffic.

Start from an event log

Funnels are usually built from an event table: one row per thing a user did.

Notice two realistic problems: user 2 visited twice, and user 6 purchased without any recorded visit or signup (maybe they came from an app, or tracking failed). A good funnel query handles both.

Step 1: one row per user with step flags

Pivot the events into one row per user, with a 1/0 flag per step. MAX(CASE ...) is the standard trick: the flag is 1 if any of the user's rows match.

SELECT user_id,
       MAX(CASE WHEN event_name = 'visit'    THEN 1 ELSE 0 END) AS did_visit,
       MAX(CASE WHEN event_name = 'signup'   THEN 1 ELSE 0 END) AS did_signup,
       MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS did_purchase
FROM events
GROUP BY user_id;

Duplicates disappear, because MAX of several 1s is still 1. This is why funnels count distinct users, never event rows.

Step 2: enforce the step order

In a strict funnel, a user only counts at a step if they passed every earlier step. Multiplying the flags does that neatly: did_visit * did_signup is 1 only when both are 1.

WITH user_steps AS ( ...step 1... ),
funnel AS (
  SELECT SUM(did_visit)                             AS visitors,
         SUM(did_visit * did_signup)                AS signups,
         SUM(did_visit * did_signup * did_purchase) AS purchasers
  FROM user_steps
)
SELECT visitors, signups, purchasers,
       ROUND(signups * 100.0 / visitors, 1)    AS signup_rate_pct,
       ROUND(purchasers * 100.0 / signups, 1)  AS purchase_rate_pct,
       ROUND(purchasers * 100.0 / visitors, 1) AS overall_conversion_pct
FROM funnel;

User 6, who purchased with no visit, is excluded from every step. Whether that is right is a business decision: a "loose" funnel would count each step independently. Say which one you used.

Step conversion vs overall conversion

Report both:

• Step conversion: signups / visitors, purchasers / signups. Shows where people drop.
• Overall conversion: purchasers / visitors. Shows the end-to-end result.

With 9 visitors, 5 signups and 3 purchasers, step conversion is 55.6% then 60.0%, and overall conversion is 33.3%. The visit-to-signup step loses the most people, so that is where to look first.

Adding time: did it happen in order, within a window?

Flags ignore timing. A user who purchased on Monday and signed up on Friday would pass the strict funnel above. When order and timing matter, compare timestamps per user:

WITH firsts AS (
  SELECT user_id,
         MIN(CASE WHEN event_name = 'visit'  THEN event_time END) AS first_visit,
         MIN(CASE WHEN event_name = 'signup' THEN event_time END) AS first_signup
  FROM events
  GROUP BY user_id
)
SELECT COUNT(first_visit) AS visitors,
       SUM(CASE WHEN first_signup >= first_visit
                 AND julianday(first_signup) - julianday(first_visit) <= 7
                THEN 1 ELSE 0 END) AS signed_up_within_7_days
FROM firsts;

MIN(CASE ... THEN event_time END) returns the first time each step happened (the CASE gives NULL for other events, and MIN ignores NULL). COUNT(first_visit) counts only users who have a visit. Text timestamps in YYYY-MM-DD HH:MM form compare correctly as strings, which is why ISO dates are so useful.

Segmenting the funnel

Once the per-user CTE exists, add a dimension to compare groups: join to users and GROUP BY device, country or traffic source. A funnel that is fine on desktop but collapses on mobile points straight to a bug or a design problem.

Common mistakes

• Counting events, not users. Repeat visits inflate the top of the funnel and make conversion look worse.
• Not enforcing step order when the stakeholder expects a strict funnel, or enforcing it when they expect independent counts.
• Dividing by the wrong base. Be explicit whether a rate is "of previous step" or "of visitors".
• Integer division in rates: use 100.0.
• Mixing time periods: visitors from June and purchases from July in the same funnel. Fix a cohort window first.

Quick recap

• Turn the event log into one row per user with MAX(CASE ...) flags.
• Multiply flags to require earlier steps; sum them to count users per step.
• Report step conversion and overall conversion.
• Use first-event timestamps when order or a time limit matters.

Try it

Open the Do tab and compute a strict visit -> signup -> purchase funnel from an event log.