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.
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 |
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 |
| 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 |
| Column | Type | Description |
|---|---|---|
| assignment_idPK | INT | Unique assignment record |
| user_id | INT | User assigned to the experiment |
| variant | VARCHAR(20) | Experiment variant such as control or treatment |
| assigned_at | DATE | Date the user was assigned |
| surface | VARCHAR(50) | Intuit product surface where the experiment ran |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique event record |
| user_id | INT | User who generated the event |
| event_name | VARCHAR(50) | Name of the product event |
| event_date | DATE | Date the event occurred |
| revenue | DECIMAL(10,2) | Revenue associated with the event |
| 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 |
| 204 | 102 | start_return | 2024-01-09 | 0.00 |
| 205 | 103 | start_return | 2024-01-19 | 0.00 |
| 206 | 103 | file_return | 2024-01-20 | 79.00 |
| 207 | 104 | start_return | 2024-01-13 | 0.00 |
| 208 | 104 | file_return | 2024-01-14 | 69.00 |
| 209 | 105 | view_pricing | 2024-01-21 | |
| 210 | 106 | file_return | 2024-01-27 | 80.00 |
| 211 | 107 | start_return | 2024-02-02 | 0.00 |
| 212 | 107 | file_return | 2024-02-03 | 101.00 |
| 213 | 105 | reject_recommendation | 2024-01-13 | 8 |
| 214 | 101 | save_progress | 2024-02-03 | -17 |
| 215 | 102 | start_trial | 2024-02-01 | 17 |
| 216 | 107 | null | 2024-01-12 | null |
| 217 | 107 | reject_recommendation | 2024-01-28 | -5 |
| 218 | 102 | share_referral | 2024-01-06 | 3 |
| 219 | 107 | start_return | 2024-01-30 | 37 |
| 220 | 105 | upload_document | 2024-01-11 | 59 |
| 221 | 108 | accept_recommendation | 2024-01-24 | 73 |
| 222 | 105 | abandon_flow | 2024-01-16 | 62 |
| 223 | 101 | submit_email | 2024-01-23 | -1 |
| 224 | 102 | purchase | 2024-01-15 | 72 |
| 225 | 105 | view_refund_estimate | 2024-02-03 | 78 |
| 226 | 108 | resume_return | 2024-02-06 | 38 |
| 227 | 102 | dismiss_offer | 2024-01-13 | 92 |
| 228 | 104 | null | 2024-01-06 | 62 |
| 229 | 101 | save_progress | 2024-01-16 | -2 |
| 230 | 102 | save_progress | 2024-01-03 | -15 |
| 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 |
| 4 | 104 | control | 2024-01-12 | TurboTax onboarding |
| 5 | 105 | treatment | 2024-01-20 | TurboTax onboarding |
| 6 | 106 | control | 2024-01-25 | TurboTax onboarding |
| 7 | 107 | treatment | 2024-01-28 | TurboTax onboarding |
| 8 | 108 | control | 2024-01-30 | TurboTax onboarding |
| 9 | 109 | treatment | 2024-02-02 | TurboTax onboarding |
| 10 | 110 | control | 2024-01-15 | Credit Karma onboarding |
| 11 | 111 | treatment | 2024-01-18 | |
| 12 | 101 | new_copy | 2024-01-12 | QuickBooks billing |
| 13 | 113 | variant_a | 2024-01-22 | QuickBooks payroll |
| 14 | 110 | variant_b | 2024-01-04 | Intuit identity flow |
| 15 | 105 | self_serve | 2024-01-12 | null |
| 16 | 110 | upsell_test | 2024-01-06 | QuickBooks invoicing |
| 17 | 104 | new_flow | 2024-01-31 | Mailchimp signup |
| 18 | 110 | old_copy | 2024-01-22 | Mailchimp campaign builder |
| 19 | 111 | upsell_test | 2024-01-10 | QuickBooks payroll |
| 20 | 106 | treatment | 2024-01-04 | TurboTax pricing |
| 21 | 102 | test_1 | 2023-12-31 | Mailchimp onboarding |
| 22 | 105 | baseline | 2024-02-03 | TurboTax pricing |
| 23 | 108 | test_2 | 2024-02-03 | Mailchimp campaign builder |
| 24 | 113 | banner_test | 2024-01-21 | QuickBooks invoicing |
| 25 | 110 | self_serve | 2024-01-16 | TurboTax refund tracker |
| 26 | 104 | banner_test | 2024-01-23 | TurboTax onboarding |
| 27 | 111 | checkout_test | 2024-01-02 | Mailchimp signup |
| variant | assigned_users | converters | conversion_rate | avg_revenue_per_user | conversion_rate_lift_vs_control |
|---|---|---|---|---|---|
| control | 4 | 2 | 0.5000 | 43.75 | 0.0000 |
| banner_test | 1 | 0 | 0.0000 | 0.00 | -0.5000 |
| treatment | 4 | 2 | 0.5000 | 55.50 | 0.0000 |
