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 |
