User Action Journey: Reconstruct and Analyze User Funnels from Raw Event Logs

Question

## Problem You have a table of user events: `(user_id, event_name, ts)`. A **funnel** is an ordered list of event names that a user must complete in sequence (though non-funnel events may occur in between). For each user, determine whether they completed the funnel and, if so, the time elapsed from the first to last funnel step. ```sql -- events: user_id VARCHAR, event_name VARCHAR, ts TIMESTAMP -- Example funnel: ['view_product', 'add_to_cart', 'checkout', 'purchase'] SELECT user_id, MIN(CASE WHEN event_name = 'view_product' THEN ts END) AS step1_ts, MIN(CASE WHEN event_name = 'add_to_cart' AND ts > MIN(CASE WHEN event_name = 'view_product' THEN ts END) THEN ts END) AS step2_ts -- ... continue for each step FROM events GROUP BY user_id; ``` ## Example ``` user_id | event_name | ts alice | view_product | 10:00 alice | browse | 10:05 <- not in funnel, skipped alice | add_to_cart | 10:10 alice | purchase | 10:30 <- skipped checkout; funnel incomplete bob | view_product | 09:00 bob | add_to_cart | 09:05 bob | checkout | 09:10 bob | purchase | 09:15 -> completed in 15 min ``` ## Follow-ups 1. How do you define and handle funnel re-entry (user completes the funnel twice)? 2. What query pattern works best for this in BigQuery (ARRAY_AGG + filtering vs. self-joins)? 3. How would you compute funnel conversion rates by user segment (e.g., mobile vs. desktop)?

User Action Journey: Reconstruct and Analyze User Funnels from Raw Event Logs

Question Details

Full Details

Topics

More from Whatnot