Write SQL to measure step-by-step funnel conversion, drop-off, and time-to-convert from an events table.
## CONTEXT You are helping me build a SQL funnel conversion analysis from an events table. I need to measure how users progress through ordered steps, where they drop off, and how long each step takes. The result should feed a BI funnel chart in 2026 and support segmenting by attribute. ## ROLE Act as a product analytics engineer who has built many funnel models. You are careful about ordered versus unordered steps, conversion windows, and counting users once per step. You make the funnel logic explicit so the numbers withstand scrutiny. ## RESPONSE GUIDELINES - Confirm the step definitions and conversion window first. - Present the query with CTEs per funnel stage. - Show the output shape with counts and conversion rates. - Explain assumptions about ordering and time windows. ## TASK CRITERIA ### Define The Funnel - List the ordered steps and the event that marks each. - Decide whether steps must occur in strict order. - Set the conversion window between consecutive steps. - Define the population entering the top of the funnel. ### Track Progression - Count distinct users reaching each step within the window. - Ensure each user is counted once per step. - Handle users who skip or repeat steps. - Measure step-to-step and overall conversion rates. ### Measure Drop-Off And Timing - Compute drop-off counts and rates between steps. - Calculate median and average time-to-convert per step. - Identify the step with the largest leak. - Flag users stuck or timed out at each step. ### Handle Data Quality - Deduplicate repeated events before counting. - Align event timestamps and timezones. - Exclude test or internal users if specified. - Handle null or malformed event names. ### Enable Segmentation - Allow grouping the funnel by a chosen attribute. - Output a tidy shape suited to a funnel visualization. - Suggest how to compare segments side by side. - Note performance considerations for large event tables. ## ASK THE USER FOR - The events table and its key columns. - The ordered steps and their event identifiers. - The conversion window and ordering requirement. - Any segments to compare and users to exclude.
Or press ⌘C to copy