Building a conversion funnel in SQL
WHAT IT TESTS: Funnel SQL and drop-off reasoning. OUTLINE: Count distinct users reaching each ordered step, compute step-over-step conversion; the biggest drop-off is the lowest consecutive ratio. RED FLAG: Comparing each step to the total, or counting events.
WHAT IT TESTS: Whether you can build an order-aware funnel and locate the worst leak. ANSWER OUTLINE: Build a per-user table of the first timestamp of each step via conditional aggregation, requiring each step's timestamp to follow the prior. Count distinct users per step. Conversion between steps is later over earlier. The biggest drop-off is the consecutive pair with the lowest step-to-step conversion, not the lowest count. RED FLAG: Dividing each step by the initial cohort, or counting events not users.
Read the original → interview
- #sql
- #funnel-analysis
- #conversion
- #onboarding
- #analytics
Get five bites like this every day.
Tezvyn delivers a daily feed of 60-second tech bites with quizzes to lock in what you learn.