Task
Rippling's growth team launched a campaign in Rippling Spend on 2024-02-15 and wants a quick before-vs-after performance read. Write a SQL query to compare daily campaign performance for the periods before and after launch.
Requirements
- Use only the
campaign_events table.
- Return one row per period:
Before Launch and After Launch.
- For each period, calculate:
total_impressionstotal_clickstotal_signupsctr = total_clicks / total_impressionssignup_rate = total_signups / total_clicks
- Include only rows where
event_date is between 2024-02-08 and 2024-02-21 inclusive.
- Treat dates before
2024-02-15 as Before Launch, and dates on or after 2024-02-15 as After Launch.
- Order the output by
period so After Launch appears first.
Table Definition
| column | type | description |
|---|
| event_id | INT | Primary key for each daily campaign record |
| event_date | DATE | Date of campaign activity |
| campaign_name | VARCHAR(100) | Campaign name in Rippling Spend |
| impressions | INT | Number of impressions recorded that day |
| clicks | INT | Number of clicks recorded that day |
| signups | INT | Number of signups attributed that day |
| channel | VARCHAR(50) | Marketing channel |
Sample Data
| event_id | event_date | campaign_name | impressions | clicks | signups | channel |
|---|
| 1 | 2024-02-12 | Spend Launch | 1000 | 45 | 6 | Paid Search |
| 2 | 2024-02-08 | Spend Launch | 800 | 32 | 4 | Paid Social |
| 3 | 2024-02-16 | Spend Launch | 1500 | 90 | 15 | Paid Search |
| 4 | 2024-02-14 | Spend Launch | 950 | 40 | 5 | Email |
| 5 | 2024-02-18 | Spend Launch | 1700 | 102 | 18 | Paid Social |
| 6 | 2024-02-21 | Spend Launch | 1600 | 88 | 14 | Email |
Expected Output
| period | total_impressions | total_clicks | total_signups | ctr | signup_rate |
|---|
| After Launch | 7400 | 430 | 70 | 0.0581 | 0.1628 |
| Before Launch | 4400 | 177 | 21 | 0.0402 | 0.1186 |
