SQL Funnel Queries: Ordered Steps and Complete Cohorts

Build the first qualifying path through a funnel

A user can activate before signup because of faulty tracking, then activate again correctly. Taking the independent minimum timestamp for each event type rejects that user even when a valid path exists. An ordered funnel should find the first qualifying next step after the selected prior step. This PostgreSQL 16 example also closes the observation window before reporting a daily cohort.

Define identity, ordering, and deadlines

Our fixture has stable user IDs and unique event IDs. All timestamps represent UTC. Event order is the pair (event_ts, event_id); the ID breaks equal-timestamp ties because this teaching contract assumes it is an authoritative sequence. In real data, do not use a random UUID or ingestion ID as proof of event order. Choose an actual sequence or a documented rule for simultaneous events.

The contract for this three-step funnel

Use each user’s earliest signup. Select the first activation strictly after it and before 72 hours from signup. Then select the first purchase strictly after that activation and before 168 hours from signup. Both deadlines are exclusive and measured from signup. Each daily denominator includes only fully mature UTC signup-date cohorts; the October 17 cutoff therefore admits cohorts through October 9. Purchases and activations in this fictional input already represent successful, non-test events.

Repeated events are not extra people

User 1 has two purchases. A raw join returns multiple rows, while the metric needs a yes/no result for that user. COUNT(DISTINCT) can hide the repeated rows but cannot select a valid sequence. The example instead caps each step lookup at one row, preserving one row per signup user throughout.

Select later steps relative to earlier steps

ROW_NUMBER selects the anchor. Each LEFT JOIN LATERAL searches events for that user relative to the selected prior row. LEFT joins retain users who never reach a later step. A null activation prevents the purchase comparison from qualifying. See the PostgreSQL LATERAL reference for this correlated lookup behavior.

Run a complete ordered, mature funnel

WITH params(as_of) AS (VALUES(TIMESTAMP '2026-10-17 00:00')),
events(event_id,user_id,event_name,event_ts) AS (
 VALUES
 (1,1,'signup',TIMESTAMP '2026-10-01 09:00'),
 (2,1,'activated',TIMESTAMP '2026-09-30 09:00'),
 (3,1,'activated',TIMESTAMP '2026-10-02 09:00'),
 (4,1,'purchase',TIMESTAMP '2026-10-03 09:00'),
 (5,1,'purchase',TIMESTAMP '2026-10-04 09:00'),
 (6,2,'signup',TIMESTAMP '2026-10-01 09:00'),
 (7,2,'purchase',TIMESTAMP '2026-10-01 10:00'),
 (8,2,'activated',TIMESTAMP '2026-10-02 09:00'),
 (9,2,'purchase',TIMESTAMP '2026-10-03 09:00'),
 (10,3,'signup',TIMESTAMP '2026-10-01 09:00'),
 (11,3,'activated',TIMESTAMP '2026-10-05 09:00'),
 (12,3,'purchase',TIMESTAMP '2026-10-06 09:00'),
 (13,4,'signup',TIMESTAMP '2026-10-01 09:00'),
 (14,4,'activated',TIMESTAMP '2026-10-02 09:00'),
 (15,4,'purchase',TIMESTAMP '2026-10-08 09:00'),
 (16,5,'signup',TIMESTAMP '2026-10-10 00:00'),
 (17,5,'activated',TIMESTAMP '2026-10-10 01:00'),
 (18,5,'purchase',TIMESTAMP '2026-10-11 00:00'),
 (19,6,'signup',TIMESTAMP '2026-10-01 09:00'),
 (20,6,'activated',TIMESTAMP '2026-10-01 09:00'),
 (21,6,'purchase',TIMESTAMP '2026-10-01 09:00')
), signup_ranked AS (
 SELECT e.*,ROW_NUMBER() OVER (
   PARTITION BY user_id ORDER BY event_ts,event_id
 ) AS rn
 FROM events e CROSS JOIN params p
 WHERE event_name='signup' AND event_ts<p.as_of
), signups AS (
 SELECT s.* FROM signup_ranked s CROSS JOIN params p
 WHERE rn=1 AND s.event_ts::date+INTERVAL '8 days'<=p.as_of
), steps AS (
 SELECT s.user_id,s.event_ts AS signup_ts,
        a.event_id AS activation_id,b.event_id AS purchase_id
 FROM signups s
 LEFT JOIN LATERAL (
   SELECT e.event_id,e.event_ts FROM events e
   WHERE e.user_id=s.user_id AND e.event_name='activated'
     AND (e.event_ts,e.event_id)>(s.event_ts,s.event_id)
     AND e.event_ts<s.event_ts+INTERVAL '72 hours'
   ORDER BY e.event_ts,e.event_id LIMIT 1
 ) a ON true
 LEFT JOIN LATERAL (
   SELECT e.event_id FROM events e
   WHERE e.user_id=s.user_id AND e.event_name='purchase'
     AND (e.event_ts,e.event_id)>(a.event_ts,a.event_id)
     AND e.event_ts<s.event_ts+INTERVAL '168 hours'
   ORDER BY e.event_ts,e.event_id LIMIT 1
 ) b ON true
)
SELECT signup_ts::date AS signup_date,COUNT(*) AS signup_users,
       COUNT(activation_id) AS activated_users,
       COUNT(purchase_id) AS purchased_users,
       ROUND(100.0*COUNT(purchase_id)/COUNT(*),2) AS purchase_rate_pct
FROM steps GROUP BY signup_ts::date ORDER BY signup_date;

signup_date | signup_users | activated_users | purchased_users | purchase_rate_pct
2026-10-01 | 5 | 4 | 3 | 60.00

Users 1 and 2 convert because a later valid event remains eligible after an earlier invalid one. User 3 activates after the 72-hour deadline, so never enters the activation or purchase numerator. User 4 activates but purchases at the excluded 168-hour endpoint. User 5 belongs to an immature cohort. User 6 qualifies because their event IDs establish the required order at one timestamp. The counts must satisfy purchased <= activated <= signup.

A two-step funnel is a different question

If activation is not a requirement, search purchases directly after signup rather than reusing the three-step purchase flag. User 3 would then convert within seven days, even though activation was late. The complete two-step conversion query provides an independent fixture and explicit cohort cutoff. Do not label a three-step numerator as ordinary signup-to-purchase conversion.

Keep numerator and denominator in the same cohort

Purchases occurring today may belong to users who signed up last week. Dividing that activity count by today’s signups does not measure today’s signup cohort. Group the final step table by the signup date for the metric here. An event-date operational report can be useful, but needs a different label and denominator.

A per-step deadline changes the maturity rule

This query uses one seven-day total horizon with a shorter activation deadline. If purchases instead have 14 days after activation, the maximum observation window is longer. Update both step predicates and cohort maturity together. Do not simply change the purchase interval while leaving the seven-day cohort cutoff. A rolling 168-hour rule and a business-calendar seven-day rule also differ across daylight-saving changes.

Check the path, not just the final count

Inspect a user whose earlier activation or purchase is invalid but whose later event is valid. Then inspect a user with no activation, one at the exact deadline, and one with timestamp ties. Removing duplicates cannot repair those semantic errors. If the source can contain duplicate event IDs, resolve them before using an ID as a tie-breaker.

Use a trustworthy cutoff and identity mapping

Map anonymous and authenticated identifiers to the intended entity before constructing the funnel. Freeze or version that mapping for reproducibility. Choose as_of using source completeness, not just the current clock. Late-arriving events can change an earlier path, so document whether recent published cohorts are provisional and when they are restated.

Add dimensions at the correct time

Attach a one-row-per-user acquisition dimension after the step table. For changing country, plan, or channel values, define whether the metric uses the value at signup or at another event. Joining an unconstrained history table can reintroduce multiple rows per user after the careful step construction.

Boundaries are executable requirements

The anchor is earliest signup before the cutoff, not earliest signup inside a reporting slice. A report may filter cohort dates after anchor selection. Empty input returns no cohort row rather than an invented zero-user percentage. Equal-time ordering is explicit. A purchase exactly at its deadline fails. Each of these choices should be confirmed before adapting the query.

Validate the intermediate path

During debugging, replace the final aggregate with a SELECT from steps ordered by user_id. Expected activation/purchase IDs are user 1: 3/4; user 2: 8/9; user 3: NULL/NULL; user 4: 14/NULL; user 6: 20/21. Those five rows explain the final rate. Repeat the fixture after changing any deadline or ordering assumption.

FAQ

Should I use the first purchase ever?

Only if that is the metric. This query uses the first qualifying purchase after the selected activation, so an invalid earlier purchase does not suppress a valid path.

Why exclude an individually mature user?

The output is daily cohorts. Including only mature users from an otherwise immature signup date would change that date’s denominator. Report user-level maturity separately if that is the intended analysis.