Funnel queries

The idea

Picture four doors in a row: visited, signed up, activated, purchased. A funnel asks how many different people got through each door, and it insists they walk through them in order.

So someone reaches “purchased” only if their earliest purchase stamp lands after the signup stamp that carried them through the door before. Each stage's conversion is simply its count divided by the count of the stage above it.

Two shortcuts quietly break this. Counting log rows instead of people turns one person's two purchases into two conversions. Ignoring order lets somebody who paid before they ever signed up be counted as a success.

Eight users, four stages

How it works

Group the log by user. For one user, walk the stages in order, and for each stage take the earliest stamp that lands strictly after the stamp you used for the stage before. The first time no such stamp exists, that user's chain stops: they are counted at every stage up to there and nowhere further.

Taking the earliest valid stamp at each step is safe: a later choice can only push the next stage further out of reach, never bring it closer. So this greedy walk really does find the furthest stage a user can justify.

STAGES = ["visited", "signed_up", "activated", "purchased"]

def furthest_stage(events):
    """events: [(stage, ts), ...] for ONE user.
    Returns the index of the furthest stage reached in order, or -1."""
    prev, reached = float("-inf"), -1
    for i, stage in enumerate(STAGES):
        # earliest stamp for this stage that lands AFTER the previous one
        ts = min((t for s, t in events if s == stage and t > prev), default=None)
        if ts is None:
            break                    # chain broken: this user stops here
        prev, reached = ts, i
    return reached

def funnel(events_by_user):
    counts = [0] * len(STAGES)
    for events in events_by_user.values():   # one loop per USER, not per event
        r = furthest_stage(events)
        for i in range(r + 1):               # reaching stage r means you were
            counts[i] += 1                   # counted at every stage before it
    step_conv = [counts[i] / counts[i - 1] if counts[i - 1] else None
                 for i in range(1, len(STAGES))]
    return counts, step_conv

# counts    -> [8, 6, 3, 2]
# step_conv -> [0.75, 0.50, 0.667]      overall = 2 / 8 = 0.25

One-pass equivalent: sort the user's stamps by time once, then k = 0; for s, t in stamps: if s == STAGES[k]: k += 1. In SQL the same shape is COUNT(DISTINCT user_id) over per-user MIN(ts) FILTER (WHERE stage = …) values compared pairwise, never COUNT(*).

Cost

The trade-off: ordered, distinct-user counting needs per-user state, a pointer and one timestamp. Counting rows needs no state at all, which is exactly why the cheap query is the tempting wrong one.

Watch out for

• COUNT(*) where you meant COUNT(DISTINCT user_id). One person buying twice adds two to the last bucket. The tell is a stage that is larger than the stage above it, or a conversion over 100%.
• Presence instead of order. MAX(CASE WHEN stage='purchased' …) per user asks “did it ever happen”, not “did it happen after”. Someone who paid on an old account and signed up later slips straight into the final bucket.
• Ties and clock skew. Decide whether the next stage must be strictly after (>) or at-or-after (>=), and pick a deterministic tiebreak. Same-millisecond writes, or stamps from devices with unsynced clocks, will otherwise flip users in and out between runs.
• No time window. A visit in January and a purchase in June is not a conversion anyone means. Bound the walk per user (first stamp + 7 days, say) and put the window in the chart label.
• Left-censored users and denominator drift. People who signed up before the window have no signup row inside it, so they vanish from stage 2 while their purchase still lands in stage 4. Anchor the funnel on users whose first stage falls inside the window, and always say whether each percentage is against the previous stage or against the top.

Worked example

Take the eight users above. Six of them have a signup stamp after their visit, three of those go on to activate, and two of those purchase afterwards: 8 → 6 → 3 → 2, i.e. 75%, 50%, 67%, and 25% end to end. The weakest step is signup → activation, so that is where the work goes.

Now the two traps. u5 has a purchase stamped at t=3 and a signup at t=6: a returning customer on a new account. Drop the ordering rule and u5 rejoins the purchase bucket, which reads 3 against an activation bucket of 3: a perfect 100% activate-to-purchase rate produced by a person who never activated. u6 visited twice and purchased twice; count events instead of users and the funnel becomes 9 → 6 → 3 → 4, with the last step above 100%. Both numbers are impossible for real people, and both look plausible enough to end up on a slide.

Check yourself