SQL funnel conversion when events arrive out of order

Funnel conversion needs a defined sequence, population and time window. Counting users who generated each event type independently does not prove that they completed those events in the required order. Sorting by warehouse arrival time can also distort the journey when events arrive late.

The advanced SQL lab includes both event_at and ingested_at. Event time describes when the activity reportedly occurred; ingestion time describes when the system received it. Neither substitutes for a clear funnel definition.

Inspect the delayed journey

Customer C viewed a page at 11:50 and purchased at 12:00. The purchase arrived at 12:00:01, while the view arrived at 12:10. A report built at 12:05 would not yet know about the view. A report built after 12:10 should place the view before the purchase when reconstructing event-time order.

SELECT event_id, customer_id, event_type, event_at, ingested_at
FROM events
WHERE customer_id = 'C'
ORDER BY event_at, event_id;

The view is E08 and the purchase E07. Event identifiers do not establish chronological order. Their ordering can provide a deterministic display for equal timestamps, but an arbitrary identifier cannot resolve which of two simultaneous actions causally preceded the other.

Start with an explicit two-step funnel

For this exercise, the population is customers who viewed on January 1. Conversion means a purchase strictly after their first view and before January 2. The table contains the complete exercise population after all eight events have arrived.

WITH entrants AS (
    SELECT customer_id, MIN(event_at) AS first_view
    FROM events
    WHERE event_type = 'view'
      AND event_at >= '2026-01-01' AND event_at < '2026-01-02'
    GROUP BY customer_id
), journeys AS (
    SELECT v.customer_id, v.first_view,
           MIN(p.event_at) AS first_qualifying_purchase
    FROM entrants AS v
    LEFT JOIN events AS p
      ON p.customer_id = v.customer_id
     AND p.event_type = 'purchase'
     AND p.event_at > v.first_view
     AND p.event_at < '2026-01-02'
    GROUP BY v.customer_id, v.first_view
)
SELECT COUNT(*) AS entrants,
       COUNT(first_qualifying_purchase) AS converted,
       100.0 * COUNT(first_qualifying_purchase) / NULLIF(COUNT(*), 0)
           AS conversion_pct
FROM journeys;

All three customers enter and convert: 3/3, or 100%. This is a tiny synthetic example, not a typical conversion benchmark. The left join preserves entrants without purchases; placing the purchase conditions in WHERE would accidentally remove those nonconverters.

A cart requirement changes the question

Only A has a cart event. A required view-to-cart-to-purchase funnel therefore has one completion among three entrants. B and C are valid two-step converters, but they do not satisfy this three-step definition.

WITH views AS (
    SELECT customer_id, MIN(event_at) AS viewed_at
    FROM events
    WHERE event_type = 'view'
      AND event_at >= '2026-01-01' AND event_at < '2026-01-02'
    GROUP BY customer_id
), carts AS (
    SELECT v.customer_id, MIN(c.event_at) AS cart_at
    FROM views AS v
    LEFT JOIN events AS c ON c.customer_id = v.customer_id
      AND c.event_type = 'cart' AND c.event_at > v.viewed_at
      AND c.event_at < '2026-01-02'
    GROUP BY v.customer_id
)
SELECT c.customer_id, c.cart_at, MIN(p.event_at) AS purchased_after_cart
FROM carts AS c
LEFT JOIN events AS p ON p.customer_id = c.customer_id
  AND p.event_type = 'purchase' AND p.event_at > c.cart_at
  AND p.event_at < '2026-01-02'
GROUP BY c.customer_id, c.cart_at
ORDER BY c.customer_id;

The result contains A with both timestamps and B/C with missing cart and qualifying purchase values. The query's staged CTEs make the ordering conditions inspectable. See the SQLite CTE reference for the syntax.

Decide whether the funnel is user-based or session-based

These queries permit events anywhere within the defined day. They do not require a shared session, device or campaign. Adding those restrictions changes eligibility and may lower completion. The sessionization lesson provides a separate inactivity rule; use a session identifier in every funnel stage if that is the actual contract.

For historical reporting, record the ingestion cutoff as well as the event window. Recompute recent windows when late data arrives, and label provisional results. A changed conversion rate may reflect newly received evidence rather than new customer behavior.

Exercise: restrict the source to events ingested before 12:05. Identify which journey becomes incomplete and document how a later refresh corrects it. Then add a purchase before a view and verify that it does not qualify.

NeuraPath's Data Analytics with Generative AI course connects SQL analysis with defensible business metrics. A funnel project becomes convincing when it explains event order, eligibility and late-data corrections alongside the conversion percentage.

Continue learning

This article is part of the Advanced SQL and analytical patterns sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Build an RFM segmentation table with SQL.
• Continue with Deduplicate change events using a deterministic tie-breaker.