Marketing analyst interview: acquisition math
Practise campaign spend, conversion and attribution questions without multiplying rows or losing organic traffic.
Practise each question under time pressure, then explain your assumptions, data grain and edge cases aloud before comparing solutions.
Start interview practice→ 02
→ 03
Your exercises
01Return channel and total_spend, rounded to two decimals, for channels with ad spend. Sort by total_spend descending and channel alphabetically.
Return channel and lead_count for all leads, using 'organic' when campaign_id is NULL. Sort by lead_count descending, then channel alphabetically.
Return channel and converted_leads for leads with a non-NULL converted_at date, using 'organic' for leads without a campaign. Sort by converted_leads descending, then channel alphabetically.
Return campaign name and budget for campaigns that generated no leads, sorted by name alphabetically.
For campaigns with ad delivery, return campaign name and CTR percentage as ctr_pct, calculated as total clicks divided by total impressions times 100, rounded to two decimals. Sort by ctr_pct descending, then name alphabetically.