Task
You are given assignment data for a product experiment on the TurboTax onboarding flow and a table of downstream user actions. Write a SQL query that compares treatment and control performance for users assigned during January 2024. Return one row per variant with the number of assigned users, the number of converters, conversion rate, average revenue per assigned user, and the lift in conversion rate versus control. Treat a user as converted if they have at least one start_return event within 7 days of assignment. Include assigned users even if they never generated an event.
Schema
experiment_assignments
| column | type | description |
|---|
| assignment_id | INT | Unique assignment record |
| user_id | INT | User assigned to the experiment |
| variant | VARCHAR(20) | control or treatment |
| assigned_at | DATE | Assignment date |
| surface | VARCHAR(50) | Product surface where the experiment ran |
| | |
user_events
| column | type | description |
|---|
| event_id | INT | Unique event record |
| user_id | INT | User who generated the event |
| event_name | VARCHAR(50) | Product event name |
| event_date | DATE | Event date |
| revenue | DECIMAL(10,2) | Revenue tied to the event, if any |
| | |
Sample data
experiment_assignments
| assignment_id | user_id | variant | assigned_at | surface |
|---|
| 1 | 101 | treatment | 2024-01-05 | TurboTax onboarding |
| 2 | 102 | control | 2024-01-03 | TurboTax onboarding |
| 3 | 103 | treatment | 2024-01-10 | TurboTax onboarding |
| | | | |
user_events
| event_id | user_id | event_name | event_date | revenue |
|---|
| 201 | 101 | start_return | 2024-01-06 | 0.00 |
| 202 | 101 | file_return | 2024-01-08 | 89.00 |
| 203 | 102 | view_pricing | 2024-01-04 | 0.00 |
| | | | |
Expected output
| variant | assigned_users | converters | conversion_rate | avg_revenue_per_user | conversion_rate_lift_vs_control |
|---|
| control | 4 | 2 | 0.5000 | 37.25 | 0.0000 |
| treatment | 5 | 3 | 0.6000 | 53.80 | 0.1000 |
