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.
users| column | type | description |
|---|---|---|
| user_id | INT | Unique user identifier |
| signup_date | DATE | Date the user signed up |
| acquisition_source | VARCHAR(50) | Marketing source |
funnel_events| column | type | description |
|---|---|---|
| event_id | INT | Unique event identifier |
| user_id | INT | User who triggered the event |
| step_name | VARCHAR(50) | Funnel step name |
| event_time | TIMESTAMP | Time of the event |
step_order| column | type | description |
|---|---|---|
| step_name | VARCHAR(50) | Funnel step name |
| step_rank | INT | Position in the funnel |
users| user_id | signup_date | acquisition_source |
|---|---|---|
| 101 | 2024-01-03 | Paid Search |
| 102 | 2024-01-03 | Referral |
| 103 | 2024-01-03 | Organic |
funnel_events| event_id | user_id | step_name | event_time |
|---|---|---|---|
| 1 | 101 | signup | 2024-01-03 09:00:00 |
| 2 | 101 | kyc_submitted | 2024-01-03 10:00:00 |
| 3 | 102 | signup | 2024-01-03 11:00:00 |
| event_date | step_name | users_at_step | prev_step_users | conversion_from_prev_pct | users_lost_from_prev |
|---|---|---|---|---|---|
| 2024-01-03 | signup | 2 | null | null | null |
| 2024-01-03 | kyc_submitted | 1 | 2 | 50.00 | 1 |
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.
users| column | type | description |
|---|---|---|
| user_id | INT | Unique user identifier |
| signup_date | DATE | Date the user signed up |
| acquisition_source | VARCHAR(50) | Marketing source |
funnel_events| column | type | description |
|---|---|---|
| event_id | INT | Unique event identifier |
| user_id | INT | User who triggered the event |
| step_name | VARCHAR(50) | Funnel step name |
| event_time | TIMESTAMP | Time of the event |
step_order| column | type | description |
|---|---|---|
| step_name | VARCHAR(50) | Funnel step name |
| step_rank | INT | Position in the funnel |
users| user_id | signup_date | acquisition_source |
|---|---|---|
| 101 | 2024-01-03 | Paid Search |
| 102 | 2024-01-03 | Referral |
| 103 | 2024-01-03 | Organic |
funnel_events| event_id | user_id | step_name | event_time |
|---|---|---|---|
| 1 | 101 | signup | 2024-01-03 09:00:00 |
| 2 | 101 | kyc_submitted | 2024-01-03 10:00:00 |
| 3 | 102 | signup | 2024-01-03 11:00:00 |
| event_date | step_name | users_at_step | prev_step_users | conversion_from_prev_pct | users_lost_from_prev |
|---|---|---|---|---|---|
| 2024-01-03 | signup | 2 | null | null | null |
| 2024-01-03 | kyc_submitted | 1 | 2 | 50.00 | 1 |
