Task
NovaCart ran a marketing campaign and wants to know which customer segment converted best. Write a SQL query to identify the highest-converting segment for a specific campaign.
Requirements
- Consider only customers who were sent campaign
CMP100.
- Define a conversion as a purchase made within 7 days after the campaign send date.
- Return one row per customer segment with:
- total targeted customers
- number of converted customers
- conversion rate as a percentage rounded to 2 decimals
- rank by conversion rate, highest first
- Exclude segments with fewer than 2 targeted customers.
Table Definitions
customers
| column | type | description |
|---|
| customer_id | INT | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer name |
| segment | VARCHAR(50) | Customer segment |
| signup_date | DATE | Customer signup date |
| | |
campaign_sends
| column | type | description |
|---|
| send_id | INT | Unique campaign send record |
| campaign_id | VARCHAR(20) | Campaign identifier |
| customer_id | INT | Targeted customer |
| send_date | DATE | Date the campaign was sent |
| | |
orders
| column | type | description |
|---|
| order_id | INT | Unique order identifier |
| customer_id | INT | Customer who placed the order |
| order_date | DATE | Purchase date |
| order_amount | DECIMAL(10,2) | Order value |
Sample Data
customers
| customer_id | customer_name | segment | signup_date |
|---|
| 1 | Ava Patel | New | 2024-01-10 |
| 2 | Ben Ortiz | Loyal | 2023-11-02 |
| 3 | Chloe Kim | At Risk | 2023-08-15 |
| 4 | Daniel Reed | Loyal | 2023-06-20 |
| 5 | Ella Chen | New | 2024-02-01 |
| 6 | Finn Brooks | NULL | 2024-01-25 |
campaign_sends
| send_id | campaign_id | customer_id | send_date |
|---|
| 101 | CMP100 | 1 | 2024-03-01 |
| 102 | CMP100 | 2 | 2024-03-01 |
| 103 | CMP100 | 3 | 2024-03-01 |
| 104 | CMP100 | 4 | 2024-03-01 |
| 105 | CMP100 | 5 | 2024-03-01 |
| 106 | CMP100 | 6 | 2024-03-01 |
orders
| order_id | customer_id | order_date | order_amount |
|---|
| 1001 | 1 | 2024-03-03 | 45.00 |
| 1002 | 2 | 2024-03-10 | 80.00 |
| 1003 | 3 | 2024-03-05 | 25.00 |
| 1004 | 4 | 2024-03-20 | 60.00 |
| 1005 | 5 | 2024-02-28 | 30.00 |
| 1006 | 6 | 2024-03-04 | 15.00 |
Expected Output
| segment | targeted_customers | converted_customers | conversion_rate_pct | segment_rank |
|---|
| At Risk | 2 | 2 | 100.00 | 1 |
| New | 3 | 2 | 66.67 | 2 |
| Loyal | 3 | 1 | 33.33 | 3 |
