Task
Meta's marketing analytics team wants a campaign-level view of top-of-funnel performance across recent ad delivery. Write a SQL query to return top-of-funnel clicks and cost per click (CPC) by campaign for the last 30 days.
Requirements
- Use only events from the last 30 days relative to
DATE '2024-06-30'.
- Count only clicks where
funnel_stage = 'top_of_funnel'.
- Sum spend from delivery records in the same 30-day window.
- Return one row per campaign with:
campaign_idcampaign_nametop_funnel_clickstotal_spendcpc
- Include campaigns with spend but no top-of-funnel clicks; in that case, return
0 clicks and NULL CPC.
- Order results by
top_funnel_clicks descending, then campaign_id ascending.
Table Definitions
meta_campaigns
| column | type | description |
|---|
| campaign_id | INT | Unique campaign identifier |
| campaign_name | VARCHAR(100) | Campaign name in Meta Ads Manager |
| objective | VARCHAR(50) | Campaign objective |
| status | VARCHAR(20) | Current campaign status |
meta_ad_delivery
| column | type | description |
|---|
| delivery_id | INT | Unique delivery record |
| campaign_id | INT | Campaign tied to spend |
| delivery_date | DATE | Date of ad delivery |
| spend_usd | DECIMAL(10,2) | Spend recorded for that date |
| impressions | INT | Delivered impressions |
meta_click_events
| column | type | description |
|---|
| click_id | INT | Unique click event |
| campaign_id | INT | Campaign tied to the click |
| click_date | DATE | Date of click |
| funnel_stage | VARCHAR(30) | Funnel stage classification |
| click_source | VARCHAR(30) | Source surface for the click |
Sample Data
meta_campaigns
| campaign_id | campaign_name | objective | status |
|---|
| 103 | Reels Prospecting US | TRAFFIC | ACTIVE |
| 101 | Advantage+ Shopping Spring | SALES | ACTIVE |
| 104 | Lead Gen SMB | LEADS | PAUSED |
| 102 | Click to Message LATAM | ENGAGEMENT | ACTIVE |
meta_ad_delivery
| delivery_id | campaign_id | delivery_date | spend_usd | impressions |
|---|
| 2003 | 102 | 2024-06-29 | 90.00 | 9000 |
| 2001 | 101 | 2024-06-28 | 120.00 | 10000 |
| 2008 | 104 | 2024-05-31 | 80.00 | 7000 |
| 2006 | 103 | 2024-06-05 | 0.00 | 0 |
meta_click_events
| click_id | campaign_id | click_date | funnel_stage | click_source |
|---|
| 3002 | 101 | 2024-06-29 | top_of_funnel | Facebook Feed |
| 3005 | 102 | 2024-06-28 | mid_funnel | Messenger Inbox |
| 3008 | 103 | 2024-05-30 | top_of_funnel | Instagram Reels |
| 3010 | 104 | 2024-06-10 | top_of_funnel | Facebook Feed |
Expected Output
| campaign_id | campaign_name | top_funnel_clicks | total_spend | cpc |
|---|
| 101 | Advantage+ Shopping Spring | 3 | 200.00 | 66.67 |
| 103 | Reels Prospecting US | 2 | 60.00 | 30.00 |
| 104 | Lead Gen SMB | 1 | 80.00 | 80.00 |
| 102 | Click to Message LATAM | 0 | 100.00 | NULL |
