Build a Conversion Funnel That Respects Event Order

Ben has a page view, an example run, and an enquiry. He still should not count as a completed tutorial funnel. His example run happened before the page view.

A funnel is a sequence with rules, not a shopping list of event names. We will build one where readers can inspect the evidence behind every count.

Define the question before counting

We use the fictional website from the session tutorial. The full setup is included here too. A new session begins after more than 30 minutes of inactivity.

Our funnel starts at the first view in each session. A qualifying run must be strictly later than that view. An enquiry must be strictly later than the qualifying run. Both later steps must fall within 30 minutes of the first view, including the deadline. All three events must share the same user and session.

This is a session conversion rate. A returning person can contribute more than one entry. Events with equal timestamps do not establish a strict sequence here; a production event sequence field could support a different tie policy.

See how presence-only counting goes wrong

This query checks only whether each kind of event exists:

SELECT user_id, session_no FROM session_events
GROUP BY user_id, session_no
HAVING bool_or(event = 'view') AND bool_or(event = 'run')
   AND bool_or(event = 'enquiry')
ORDER BY user_id, session_no;

It returns three sessions: Ada’s, Ben’s, and Dee’s. Ben ran the example at 10:00, viewed at 10:05, and enquired at 10:08. Every event name exists, but the required journey does not.

Cy illustrates another boundary: the enquiry arrives 58 minutes after the run, in a new session. It cannot complete the earlier session’s funnel.

Find the first valid next step

Build the sequence in stages. First find the entry timestamp. Then find the earliest eligible run after it. Finally find the earliest eligible enquiry after that run.

WITH entries AS (
 SELECT user_id, session_no,
   min(happened_at) FILTER (WHERE event = 'view') AS viewed_at
 FROM session_events GROUP BY user_id, session_no
), runs AS (
 SELECT v.*, min(e.happened_at) AS ran_at
 FROM entries v LEFT JOIN session_events e
   ON e.user_id = v.user_id AND e.session_no = v.session_no
  AND e.event = 'run' AND e.happened_at > v.viewed_at
  AND e.happened_at <= v.viewed_at + INTERVAL 30 MINUTE
 WHERE v.viewed_at IS NOT NULL
 GROUP BY v.user_id, v.session_no, v.viewed_at
)
SELECT r.*, min(e.happened_at) AS enquired_at
FROM runs r LEFT JOIN session_events e
 ON e.user_id = r.user_id AND e.session_no = r.session_no
 AND e.event = 'enquiry' AND e.happened_at > r.ran_at
 AND e.happened_at <= r.viewed_at + INTERVAL 30 MINUTE
GROUP BY r.user_id, r.session_no, r.viewed_at, r.ran_at
ORDER BY r.user_id, r.session_no;

Notice where the event and time conditions sit: inside each LEFT JOIN condition. That preserves an entry when no next step exists, leaving its timestamp NULL. Moving those conditions into a later WHERE would remove the dropouts we need to measure.

min chooses one qualifying timestamp per stage, so Ada’s repeated run does not duplicate her session. Choosing the earliest eligible run also leaves the greatest possible time for the enquiry under this policy. A later view does not restart this funnel’s fixed clock.

The setup saves these stages as funnel_steps; every browser example can run independently.

Count the journeys that remain

SELECT count(*) AS entered,
  count(ran_at) AS ran_example,
  count(enquired_at) AS sent_enquiry,
  round(100.0 * count(enquired_at) / nullif(count(*), 0), 2) AS conversion_pct
FROM funnel_steps;

Nine sessions contain an entry view. Three proceed to a valid run. Two finish with an enquiry: Ada’s first session and Dee’s session. The entry-to-enquiry rate is 2 / 9 = 22.22%.

count(*) counts all entries. count(ran_at) and count(enquired_at) ignore NULL timestamps, counting only reached steps. nullif avoids division by zero when a filtered dataset has no entries.

SELECT user_id, session_no FROM funnel_steps WHERE ran_at IS NULL ORDER BY user_id, session_no;

Match the metric to the decision

This result diagnoses recorded behavior, not why someone stopped. Missing events, blocked tracking, and a broken form can resemble abandonment. Check instrumentation before drawing a product conclusion from a changing funnel.

To compare acquisition channels, attach the entry session’s channel under a clear attribution rule, then group this result. To ask whether people return later, move to retention cohorts. Keeping those questions separate makes both metrics easier to explain.