Interview Guides

Top Funnel Clicks and CPC | Dataford Interview Questions - Dataford - Ace your Interview

Top Funnel Clicks and CPC

Easy

SQL & Data Manipulation

Asked at 1 company1

Also asked at

Problem

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_id
- campaign_name
- top_funnel_clicks
- total_spend
- cpc
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

Schema

meta_campaigns

Column	Type	Description
campaign_idPK	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_idPK	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_idPK	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

Example 1

Input

{"meta_campaigns":[[103,"Reels Prospecting US","TRAFFIC","ACTIVE"],[101,"Advantage+ Shopping Spring","SALES","ACTIVE"],[105,"Brand Awareness EMEA","AWARENESS","ACTIVE"],[104,"Lead Gen SMB","LEADS","PAUSED"],[102,"Click to Message LATAM","ENGAGEMENT","ACTIVE"],[106,"App Install Android","APP_PROMOTION",null],[107,"Creators Partnership Test","ENGAGEMENT","DRAFT"],[108,"WhatsApp Retargeting","SALES","ACTIVE"],["1","Holiday Prospecting US","OUTCOME_LEADS","LEARNING_LIMITED"],["2","Travel Intent EU",

Output

[["101","Advantage+ Shopping Spring","3","200.00","66.67"],["103","Reels Prospecting US","2","60.00","30.00"],["3","Auto Leads Midwest","0","-35.00","null"],["4","App Install Android","0","71.00","null"],["12","Catalog Sales UK","0","119.00","null"],["17","Creators Partnership Test","0","69.00","null"],["18","Creators Partnership Test","0","-15.00","null"],["102","Click to Message LATAM","0","90.00","null"],["105","Brand Awareness EMEA","0","30.00","null"],["106","App Install Android","0","-10.0

Problem

Task

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_id
- campaign_name
- top_funnel_clicks
- total_spend
- cpc
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

Schema

meta_campaigns

Column	Type	Description
campaign_idPK	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_idPK	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_idPK	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

Example 1

Input

{"meta_campaigns":[[103,"Reels Prospecting US","TRAFFIC","ACTIVE"],[101,"Advantage+ Shopping Spring","SALES","ACTIVE"],[105,"Brand Awareness EMEA","AWARENESS","ACTIVE"],[104,"Lead Gen SMB","LEADS","PAUSED"],[102,"Click to Message LATAM","ENGAGEMENT","ACTIVE"],[106,"App Install Android","APP_PROMOTION",null],[107,"Creators Partnership Test","ENGAGEMENT","DRAFT"],[108,"WhatsApp Retargeting","SALES","ACTIVE"],["1","Holiday Prospecting US","OUTCOME_LEADS","LEARNING_LIMITED"],["2","Travel Intent EU",

Output

[["101","Advantage+ Shopping Spring","3","200.00","66.67"],["103","Reels Prospecting US","2","60.00","30.00"],["3","Auto Leads Midwest","0","-35.00","null"],["4","App Install Android","0","71.00","null"],["12","Catalog Sales UK","0","119.00","null"],["17","Creators Partnership Test","0","69.00","null"],["18","Creators Partnership Test","0","-15.00","null"],["102","Click to Message LATAM","0","90.00","null"],["105","Brand Awareness EMEA","0","30.00","null"],["106","App Install Android","0","-10.0

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Median CTR by Meta CampaignHard

Monthly Campaign Performance SummaryEasy

Compare Campaign Performance Across MonthsMedium

Next question

Top Funnel Clicks and CPC

Easy

SQL & Data Manipulation

Asked at 1 company1

Also asked at

Problem

Task

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_id
- campaign_name
- top_funnel_clicks
- total_spend
- cpc
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

Schema

meta_campaigns

Column	Type	Description
campaign_idPK	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_idPK	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_idPK	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

Example 1

Input

{"meta_campaigns":[[103,"Reels Prospecting US","TRAFFIC","ACTIVE"],[101,"Advantage+ Shopping Spring","SALES","ACTIVE"],[105,"Brand Awareness EMEA","AWARENESS","ACTIVE"],[104,"Lead Gen SMB","LEADS","PAUSED"],[102,"Click to Message LATAM","ENGAGEMENT","ACTIVE"],[106,"App Install Android","APP_PROMOTION",null],[107,"Creators Partnership Test","ENGAGEMENT","DRAFT"],[108,"WhatsApp Retargeting","SALES","ACTIVE"],["1","Holiday Prospecting US","OUTCOME_LEADS","LEARNING_LIMITED"],["2","Travel Intent EU",

Output

[["101","Advantage+ Shopping Spring","3","200.00","66.67"],["103","Reels Prospecting US","2","60.00","30.00"],["3","Auto Leads Midwest","0","-35.00","null"],["4","App Install Android","0","71.00","null"],["12","Catalog Sales UK","0","119.00","null"],["17","Creators Partnership Test","0","69.00","null"],["18","Creators Partnership Test","0","-15.00","null"],["102","Click to Message LATAM","0","90.00","null"],["105","Brand Awareness EMEA","0","30.00","null"],["106","App Install Android","0","-10.0

Problem

Task

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_id
- campaign_name
- top_funnel_clicks
- total_spend
- cpc
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

Schema

meta_campaigns

Column	Type	Description
campaign_idPK	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_idPK	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_idPK	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

Example 1

Input

{"meta_campaigns":[[103,"Reels Prospecting US","TRAFFIC","ACTIVE"],[101,"Advantage+ Shopping Spring","SALES","ACTIVE"],[105,"Brand Awareness EMEA","AWARENESS","ACTIVE"],[104,"Lead Gen SMB","LEADS","PAUSED"],[102,"Click to Message LATAM","ENGAGEMENT","ACTIVE"],[106,"App Install Android","APP_PROMOTION",null],[107,"Creators Partnership Test","ENGAGEMENT","DRAFT"],[108,"WhatsApp Retargeting","SALES","ACTIVE"],["1","Holiday Prospecting US","OUTCOME_LEADS","LEARNING_LIMITED"],["2","Travel Intent EU",

Output

[["101","Advantage+ Shopping Spring","3","200.00","66.67"],["103","Reels Prospecting US","2","60.00","30.00"],["3","Auto Leads Midwest","0","-35.00","null"],["4","App Install Android","0","71.00","null"],["12","Catalog Sales UK","0","119.00","null"],["17","Creators Partnership Test","0","69.00","null"],["18","Creators Partnership Test","0","-15.00","null"],["102","Click to Message LATAM","0","90.00","null"],["105","Brand Awareness EMEA","0","30.00","null"],["106","App Install Android","0","-10.0

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Median CTR by Meta CampaignHard

Monthly Campaign Performance SummaryEasy

Compare Campaign Performance Across MonthsMedium

Next question