Events Before Conversion — Solution & ExplanationProblems / Events Before Conversion / EditorialApproach

Conversion moment. MIN(purchase_time) per purchaser — one timestamp per converter.

Count prior events with a conditional LEFT JOIN. From first_purchase, LEFT JOIN events on same user *and* event_time < first_purchase_time, then COUNT(e.event_id) per user. Two details carry the problem: the join must be LEFT (a converter with no prior events still needs a row) and the count must be of a *right-side column* (so the unmatched row counts as 0, not 1).

Channel rollup. Join to users for the channel and average, rounding to 2 decimals. Non-purchasers never entered the pipeline — no explicit filter needed.

Key InsightCOUNT(*) vs COUNT(column) is the difference between counting rows and counting matches. After a LEFT JOIN, the zero-match entity still produces one row (all NULLs on the right), so COUNT(*) reports 1 where the truth is 0 — a bug that only shows up for exactly the edge users the report is most curious about. Pairing every LEFT JOIN aggregate with a NULL-skipping count is the reflex this problem drills.Hints

Anchor everything to MIN(purchase_time) per user — repeat purchasers count events before their FIRST buy only.

The time cut is strict: event_time < first_purchase_time. The data plants an event exactly at the purchase timestamp to catch <=.

Use a LEFT JOIN from converters to their prior events and COUNT(e.event_id), not COUNT(*): the converter with zero prior events must produce a 0 row, and COUNT(*) would count their NULL row as 1.

Averaging per channel happens over converters, so join the per-user counts to users for the channel label only after the counting is done.

Solve Events Before Conversion yourself →