Task
Chime’s growth team wants to compare signup-to-funded-account conversion rates across acquisition channels for users acquired in January 2024. Write a SQL query to measure channel performance.
Requirements
- Consider only users whose
signup_date falls between 2024-01-01 and 2024-01-31.
- Attribute each user to their acquisition channel from
acquisition_touches.
- Count a user as converted only if they have a
funded event in conversion_events within 14 days after signup.
- Return, for each channel, the number of signed-up users, number of converted users, and conversion rate as a percentage rounded to 2 decimals.
- Include channels with zero conversions, and sort by conversion rate descending, then by channel name.
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Unique Chime user identifier |
| signup_date | DATE | Date the user signed up |
| app_surface | VARCHAR(50) | Chime surface where signup started |
acquisition_touches
| column | type | description |
|---|
| touch_id | INT | Unique acquisition touch identifier |
| user_id | INT | User tied to the acquisition touch |
| channel | VARCHAR(50) | Acquisition channel |
| campaign_name | VARCHAR(100) | Campaign or partner name |
conversion_events
| column | type | description |
|---|
| event_id | INT | Unique conversion event identifier |
| user_id | INT | User tied to the event |
| event_type | VARCHAR(30) | Event type such as funded or card_activated |
| event_date | DATE | Date the event occurred |
Sample Data
users
| user_id | signup_date | app_surface |
|---|
| 3 | 2024-01-20 | Referral Landing Page |
| 1 | 2024-01-03 | Chime App |
| 9 | 2024-01-10 | Chime App |
| 6 | 2024-01-28 | Chime App |
| 2 | 2024-01-05 | Chime.com |
| 11 | 2024-01-31 | Chime.com |
| 5 | 2024-01-25 | Chime App |
| 8 | 2024-01-08 | Chime.com |
| 4 | 2024-02-01 | Chime App |
| 7 | 2024-01-15 | Referral Landing Page |
| 10 | 2024-01-18 | Chime App |
| 12 | 2024-01-12 | Chime App |
acquisition_touches
| touch_id | user_id | channel | campaign_name |
|---|
| 104 | 4 | Paid Search | Brand SEM |
| 101 | 1 | Paid Search | New Year SEM |
| 108 | 8 | Organic Search | SEO Blog |
| 111 | 11 | Referral | Member Get Member |
| 103 | 3 | Referral | Member Get Member |
| 106 | 6 | Paid Social | TikTok Prospecting |
| 109 | 9 | Paid Search | Brand SEM |
| 102 | 2 | Paid Social | Meta Lookalike |
| 112 | 12 | | Unknown Attribution |
| 107 | 7 | Affiliate | Coupon Partner |
| 105 | 5 | Organic Search | SEO Blog |
| 110 | 10 | Paid Social | Meta Retargeting |
conversion_events
| event_id | user_id | event_type | event_date |
|---|
| 205 | 5 | funded | 2024-02-10 |
| 201 | 1 | funded | 2024-01-10 |
| 210 | 10 | card_activated | 2024-01-25 |
| 208 | 8 | funded | 2024-01-15 |
| 204 | 3 | funded | 2024-02-05 |
| 212 | 12 | funded | 2024-01-20 |
| 206 | 6 | funded | 2024-02-12 |
| 202 | 2 | funded | 2024-01-25 |
| 209 | 9 | funded | 2024-01-12 |
| 203 | 2 | card_activated | 2024-01-09 |
| 207 | 7 | funded | 2024-01-20 |
| 211 | 99 | funded | 2024-01-11 |
Expected Output
| channel | signed_up_users | converted_users | conversion_rate_pct |
|---|
| Organic Search | 2 | 1 | 50.00 |
| Paid Search | 2 | 2 | 100.00 |
| Paid Social | 3 | 0 | 0.00 |
| Referral | 2 | 1 | 50.00 |
| Affiliate | 1 | 1 | 100.00 |
| Unattributed | 1 | 1 | 100.00 |
