Click-to-Purchase Rate by Campaign
For each campaign, count the distinct users who clicked it and how many of them made any purchase after their first click on that campaign. Compute the purchase rate.
Expected output
name, clickers, purchasers, purchase_rate_pct (rounded to 1 decimal). Sorted by purchase_rate_pct descending, then name.
Row order is checked. Column names are not checked; numbers are compared to 2 decimals.
Dataset: Digital ads performance
A retailer's paid campaigns across search, social, display and video in February 2026: daily delivery, individual ad clicks and purchases.
campaigns
One row per campaign.
| campaign_id | INTEGER | primary key |
| name | TEXT | |
| channel | TEXT | search, social, display or video |
| start_date | TEXT | YYYY-MM-DD |
| end_date | TEXT | YYYY-MM-DD inclusive, NULL if still running |
| daily_budget | REAL | USD |
ad_daily
Delivery per campaign per day it ran, as reported by the ad platform. clicks is the platform's count, which may differ from our own click log.
| campaign_id | INTEGER | |
| day | TEXT | YYYY-MM-DD |
| impressions | INTEGER | |
| clicks | INTEGER | |
| spend | REAL | USD |
ad_clicks
Our own click log: one row per ad click that reached the site and was tracked.
| click_id | INTEGER | primary key, increases with clicked_at |
| campaign_id | INTEGER | |
| user_id | INTEGER | |
| clicked_at | TEXT | YYYY-MM-DD HH:MM:SS (UTC) |
conversions
One row per purchase. Some purchases come from users who never clicked an ad.
| conversion_id | INTEGER | primary key |
| user_id | INTEGER | |
| converted_at | TEXT | YYYY-MM-DD HH:MM:SS (UTC) |
| revenue | REAL | USD |
Engine: SQLite. Dates are text (YYYY-MM-DD); use strftime(), date() and julianday() for date math.
Related problems
- Revenue by Product Category (easy)
- Customers Who Never Ordered (easy)
- Average Order Value by Country (easy)
- Repeat Purchase Rate (medium)
- Days from Signup to First Order (medium)