Chapter 8 of 15

Spreadsheets

"Give me a lever long enough and a fulcrum on which to place it, and I shall move the world." — Archimedes

The spreadsheet is the most underrated power tool in marketing. Everyone has one open; almost nobody knows how to use it — they'll buy five SaaS dashboards when a Google Sheet and twenty minutes would've done it better, and they'd own it. This chapter is all formulas and code, no "why spreadsheets matter" — every section gives you the actual thing to paste. Steal it, swap your ranges, ship it.

The six formulas that pay rent

You don't need 400 functions. You need about six, used well. These run every week.

QUERY — SQL inside your sheet

The one that makes people gasp. QUERY slices a range like a database — filter, group, sort, sum — in a single cell. Add a control cell and concatenate the filter so a dropdown drives it. If you learn one thing here, learn this:

=QUERY(A1:F, "select B, sum(E) where C = 'Search' and D >= date '2026-06-01' group by B order by sum(E) desc label sum(E) 'Total Spend'", 1)

// dropdown-driven (G1 holds the channel):
=QUERY(Raw!A1:F, "select B, sum(E) where C = '"&G1&"' group by B order by sum(E) desc", 1)

QUERY dates are fussy — they need date 'YYYY-MM-DD' literal syntax, and column letters refer to the RANGE, not the sheet. Off-by-one? Count from the first column of your range, not column A.

ARRAYFORMULA — one formula, the whole column

Stop copy-dragging 5,000 rows and watching the sheet wheeze. Write it once, let it fill the column, and it auto-extends as new rows land. Add a header so the column self-documents:

=ARRAYFORMULA(IF(A2:A="", "", B2:B / C2:C))   // e.g. CPA across the column

// with a self-documenting header:
=ARRAYFORMULA(IF(ROW(A1:A)=1, "ROAS", IF(A1:A="", "", D1:D / E1:E)))

INDEX/MATCH — the old reliable

Works in any direction, in any tool, and you'll inherit sheets that use it. XLOOKUP is the modern shorthand, but this never breaks:

=INDEX(Campaigns!C:C, MATCH(A2, Campaigns!A:A, 0))

// XLOOKUP equivalent (newer Sheets / Excel):
=XLOOKUP(A2, Campaigns!A:A, Campaigns!C:C, "not found")

// Two-way lookup (row AND column match):
=INDEX(Data!B:Z, MATCH(A2, Data!A:A, 0), MATCH(B$1, Data!B$1:Z$1, 0))

SUMIFS — sum with conditions

The workhorse of marketing maths: total spend where campaign = X and month = Y. Once it clicks, half your manual filtering vanishes.

=SUMIFS(Spend, Campaign, "Brand", Month, ">="&DATE(2026,6,1), Month, "<"&DATE(2026,7,1))

// COUNTIFS / AVERAGEIFS work identically:
=AVERAGEIFS(CPA, Campaign, G1, Device, "Mobile")

IMPORTRANGE — link sheets together, live

Pull a range from one sheet into another in real time — perfect for a master dashboard reading from per-client working sheets without copy-paste. Wrap it in QUERY to filter as you import:

=IMPORTRANGE("1aB...sheetId...", "Data!A1:F")

// filter while importing — only this client's converting campaigns:
=QUERY(IMPORTRANGE("1aB...", "Data!A1:F"), "select Col2, Col5 where Col6 > 0", 1)

IMPORTRANGE needs a one-time permission grant — the first time, the cell shows #REF!; click it, hit "Allow access." And inside an IMPORTRANGE, QUERY columns become Col1, Col2… not letters. Trips everyone up once.

Cleaning messy data with formulas, not hands

Real exports are filthy — trailing spaces, mixed casing, names jammed in one cell, currency glued to numbers. Clean with formulas so it's repeatable next month, and chain them for one-shot cleanup of a whole column:

=TRIM(A2)                       // kill sneaky leading/trailing spaces
=PROPER(LOWER(A2))              // normalise casing
=VALUE(SUBSTITUTE(A2, ",", "")) // "1,250" text → 1250 number
=SPLIT(A2, " ")                 // full name / UTM string → columns
=REGEXEXTRACT(A2, "utm_campaign=([^&]+)")   // pull the campaign out of a URL
=REGEXREPLACE(A2, "[^0-9.]", "")            // keep only digits + dot

// chained, whole column at once:
=ARRAYFORMULA(IF(A2:A="", "", TRIM(PROPER(SUBSTITUTE(A2:A, "_", " ")))))

Never hand-edit cells to clean data. Delete a space manually instead of TRIM() and the work is un-repeatable — next month's export, you do it all again. Clean column → values column, always.

Pulling live data in

A sheet you paste into weekly is a chore; a sheet that pulls its own data is an asset. Google's free connectors — "Google Ads" and the GA4 add-on — pull campaign and analytics data into a tab on a schedule (install once, set the refresh, forget it). For anything else, any public CSV or JSON endpoint drops straight in: IMPORTDATA is native; for JSON, paste an IMPORTJSON Apps Script function (AI writes it in seconds) and call it like a formula.

=IMPORTDATA("https://example.com/export.csv")

// custom IMPORTJSON (Apps Script), then in a cell:
=IMPORTJSON("https://api.example.com/stats?key=...", "/rows")

Don't be scared of "API" — it's just a URL that returns data. The Kite analytics API, for instance, is a Bearer-token GET that hands back JSON you can land in a sheet (more next section).

Apps Script — Sheets as an automation platform

Google Apps Script (Extensions → Apps Script)

This is where a spreadsheet stops being a spreadsheet. Apps Script is JavaScript baked into Sheets — it runs on a schedule, sends email, hits APIs, edits the sheet while you sleep. You don't need to be a coder: describe it to AI, paste, run. I write half my automations this way.

Pull a live API into the sheet on a timer

function pullKiteSearch() {
  const KEY = PropertiesService.getScriptProperties().getProperty('KITE_API');
  const url = 'https://analytics.hexgeta.com/api/v1/search'
    + '?site=apoiojuridico-imigracao.com&period=28d&dimension=query&limit=100';
  const res = UrlFetchApp.fetch(url, { headers: { Authorization: 'Bearer ' + KEY } });
  const rows = JSON.parse(res.getContentText()).rows;
  const sheet = SpreadsheetApp.getActive().getSheetByName('GSC');
  sheet.clearContents();
  sheet.appendRow(['query', 'clicks', 'impressions', 'ctr', 'position']);
  rows.forEach(r => sheet.appendRow([r.query, r.clicks, r.impressions, r.ctr, r.position]));
}

Keep API keys out of the code — Project Settings → Script Properties, then read with PropertiesService. Same habit as never committing a secret. Don't paste a Bearer token into a cell or a shared script.

Threshold alert — ping yourself when something breaks

function spendAlert() {
  const spend = SpreadsheetApp.getActive().getSheetByName('Data').getRange('B2').getValue();
  if (spend > 500) {
    MailApp.sendEmail('me@example.com', '🚨 Spend alert', "Today's spend is " + spend);
    // or post to a Slack incoming webhook:
    UrlFetchApp.fetch(SLACK_WEBHOOK, {
      method: 'post', contentType: 'application/json',
      payload: JSON.stringify({ text: '🚨 Spend hit ' + spend })
    });
  }
}

Email a client their report every Monday

function weeklyReport() {
  const pdf = SpreadsheetApp.getActive().getAs('application/pdf');
  MailApp.sendEmail({
    to: 'client@acme.com',
    subject: 'Acme — weekly report',
    body: 'This week\\'s numbers attached. Quick Loom to follow.',
    attachments: [pdf]
  });
}

The code is only half the job — the automation is the TRIGGER. In the script editor, Triggers (clock icon) → add a time-driven trigger (e.g. every Monday 8am). No trigger and your fancy script just sits there doing nothing.

AI inside the sheet

AI turned Sheets into a co-pilot, two ways. Outside the sheet — paste headers + a problem into Claude/ChatGPT: "write me the QUERY that pivots spend by campaign for last month," "why is this ARRAYFORMULA returning #N/A," "categorise these 200 search terms into branded/competitor/generic." Inside the sheet — call a model per-row. The classify-per-row trick is the sleeper: a thousand messy search terms tagged in seconds, work that used to be a whole afternoon. With Apps Script you roll your own AI formula:

/** =GPT("classify as branded/competitor/generic: " & A2) */
function GPT(prompt) {
  const KEY = PropertiesService.getScriptProperties().getProperty('OPENAI_KEY');
  const res = UrlFetchApp.fetch('https://api.openai.com/v1/chat/completions', {
    method: 'post', contentType: 'application/json',
    headers: { Authorization: 'Bearer ' + KEY },
    payload: JSON.stringify({ model: 'gpt-4o-mini', messages: [{ role: 'user', content: prompt }] })
  });
  return JSON.parse(res.getContentText()).choices[0].message.content.trim();
}

AI is brilliant at formulas and pattern-work and confidently wrong at arithmetic. Let it write the SUMIFS — but trust the sheet to do the actual sum. Never ask a chatbot to add up your spend column. (Also: a per-row =GPT() across 5,000 rows = 5,000 API calls. Run it on the cleaned, deduped list.)

Building a dashboard that gets opened

You don't need a BI tool — a clean tab beats an over-engineered one nobody opens. Restraint is the skill:

Raw data on its own hidden tab. Never build on top of the source — pull from it with QUERY.
A summary tab: big-number KPIs up top (spend, conv, CPA, ROAS) via SUMIFS/QUERY, driven by one month-dropdown control cell.
2–3 charts max — a trend line and a top-campaigns bar gets you 90% of the value. SPARKLINE() for inline mini-trends; conditional formatting (green up / red down) so status reads at a glance.

// inline sparkline in a cell, right next to the number:
=SPARKLINE(B2:B30, {"charttype","line";"color","#1a73e8"})

// month-driven KPI that reacts to dropdown in G1:
=SUMIFS(Raw!E:E, Raw!D:D, ">="&G1, Raw!D:D, "<"&EDATE(G1,1))

Get fluent here and you become dangerously self-sufficient — answering your own questions, automating your own grunt work, shipping your own reports. One tool, the work of a team.