Marketing Campaign ROI Dashboard
A fully automated Google Sheets performance hub for a 6-channel, 12-month ad budget: live pivot dashboards, QUERY-driven channel league tables, and a budget reallocation model.
View code & data on GitHub ↗The Problem
A small marketing team was reporting channel performance by manually copying numbers into a monthly slide. No single source of truth, no week-over-week trend visibility, and no way to answer 'where should the next dollar go?' without an analyst rebuilding everything.
The goal was a self-maintaining Sheets workbook: raw campaign exports pasted into one tab, with every downstream table, KPI card, and chart recalculating automatically. No formulas to drag, no ranges to update.
Approach
Normalized raw-data layer
Designed a strict append-only RawData tab (date, channel, campaign, spend, impressions, clicks, leads, customers, revenue) with data validation rules so exports from each ad platform land in a consistent shape.
Metric layer with ARRAYFORMULA
Single-cell array formulas compute CTR, CPC, CPL, CAC, and ROAS for every row. New pasted rows inherit all metrics instantly with zero formula dragging.
QUERY-powered channel league table
A QUERY aggregation ranks channels by ROAS and CAC with month filtering via a dropdown, replacing what was previously an hour of manual pivot rebuilding each month.
Executive dashboard tab
KPI scorecards (spend, revenue, blended CAC, ROAS), SPARKLINE micro-trends per channel, and conditional-format heatmaps that flag any channel-week where CAC exceeds target by 20% or more.
Budget reallocation model
A marginal-ROAS table models shifting 10% budget increments from the weakest to strongest channels, projecting revenue impact before any real money moves.
The Work
Representative excerpts from the analysis: the queries and transformations doing the heavy lifting. The full runnable project lives in the GitHub repo.
=QUERY(RawData!A2:J,
"SELECT B,
SUM(D),
SUM(G),
SUM(D)/SUM(G),
SUM(J)/SUM(D)
WHERE A IS NOT NULL " &
IF(DashSelMonth<>"All",
" AND MONTH(A)+1 = " & DashSelMonth, "") & "
GROUP BY B
ORDER BY SUM(J)/SUM(D) DESC
LABEL B 'Channel',
SUM(D) 'Spend',
SUM(G) 'Leads',
SUM(D)/SUM(G) 'CPL',
SUM(J)/SUM(D) 'ROAS'", 0)// CAC, fills for every data row automatically
=ARRAYFORMULA(
IF(LEN(A2:A)=0, "",
IF(H2:H=0, "no conv.",
ROUND(D2:D / H2:H, 2))))
// ROAS with divide-by-zero guard
=ARRAYFORMULA(
IF(LEN(A2:A)=0, "",
IFERROR(ROUND(J2:J / D2:D, 2), 0)))
// Week-over-week spend trend per channel (KPI card)
=SPARKLINE(
QUERY(RawData!A2:J,
"SELECT SUM(D) WHERE B='" & $B4 & "'
GROUP BY WEEK(A) ORDER BY WEEK(A)", 0),
{"charttype","column"; "color","#16a34a"})What the Data Shows
Return on ad spend by channel
ROAS (revenue / spend)
Branded search leads but is flagged for incrementality. Email produces the best clean return on the smallest budget slice. Display drops from 3.5x to 1.0x once view-through attribution is excluded.
Key Findings
Email delivered an 8.3x ROAS on just 6% of total spend, making it the most under-funded channel in the mix by a wide margin.
Paid social CAC more than doubled across the year ($34 to $76) while search CAC stayed flat, a decay that monthly slide reporting had completely hidden.
Display showed a 3.5x platform-reported ROAS but only 1.0x on click-attributed revenue. Most of its reported value was attribution inflation, not incremental revenue.
Branded search posted a 9.3x ROAS but near-zero incrementality risk: brand traffic that would likely convert organically anyway.
The reallocation model projected that moving 15% of budget from paid social and display into email and non-brand search lifts total revenue about 18% at identical spend.
Recommendations
Shift 15% of budget per the marginal-ROAS model
The modeled reallocation from decaying channels into email and non-brand search projects +18% revenue at flat spend, measurable within one quarter.
Cap branded search spend
Run a 2-week geo holdout to measure branded search incrementality before continuing to credit it with a 9.2x ROAS.
Weekly CAC alerts, not monthly slides
The conditional-format heatmap catches CAC drift in week 2, not month 3. The paid social decay would have cost about $14k less if caught at the first threshold breach.
Adopt view-through-excluded reporting
Report display on click-attributed ROAS (1.2x) alongside platform-reported (4.1x) so budget decisions use the conservative number.