You are given event data for a Revolut-style customer onboarding funnel. Write a SQL query that returns, for each day in January 2024, the number of users who reached each funnel step, the conversion rate from the previous step, and the estimated number of users lost at that step. Only include users whose acquisition source is Paid Search or Referral, and treat the funnel as ordered from signup to kyc_submitted to kyc_approved to card_ordered.
Your result should help identify the main operational bottleneck by showing where the largest drop happens on each day. If a user triggers the same step multiple times on the same day, count that user only once for that step-day combination.
Output details
- Return columns in this order:
event_date, step_name, users_at_step, prev_step_users, conversion_from_prev_pct, users_lost_from_prev.
- Sort by
event_date ascending, then funnel order from signup through card_ordered.
- Include only dates and funnel steps having at least one qualifying event; omit other January dates and zero-count steps.
- Round
conversion_from_prev_pct to two decimal places after expressing it as a percentage.
- Return
NULL for previous-step metrics on signup, and for conversion when the previous count is NULL or zero.
- Return
NULL for users_lost_from_prev only when the previous count is NULL; otherwise subtract the current count from it.
