How to automate your weekly marketing reports

The weekly marketing report is the most automated-sounding, least automated artifact in performance marketing. Every team has tooling around it — connectors, dashboards, the platforms' own exports — and yet on Monday morning, somewhere between four ad platforms and a Google Sheet with monthly sections, a person is still doing the assembly by hand. This is the complete guide to actually automating it: the definitions that must be locked first, the pulls with their platform-specific traps, the write mechanics that protect your Sheet, the schedule semantics, and the rollout pattern that survives contact with a real team.

It's written for the person who owns the report — the performance lead, the agency analyst, the marketing-ops manager who knows that "just use a connector" is advice from someone who has never maintained a client-facing Sheet.

Part 1 — Why weekly reports resist automation

The report isn't a dataset; it's a structured artifact. Monthly sections stacked down a tab. A column order stakeholders recognize. CAC computed against purchases for this client and against first purchases for that one. Formulas someone tuned over months. Generic tools fail here for a specific reason: they understand data, not structure. A connector can refresh a raw tab; it cannot find the June section, refuse to duplicate week 24, or extend a formula column. The automation that works has to read the report the way its owner does — which is the standard everything below is built to.

The cost of staying manual is well-characterized: 45–90 minutes per report per week for the assembly alone, multiplied by clients or markets, plus the error tax — wrong-week pastes, definition drift between analysts, formulas flattened by hurried pastes — that lands client-visibly. (The full cost model.)

Part 2 — Lock the definitions before touching tools

Every failed reporting automation we've audited failed here, not at the technology. Automation enforces definitions ruthlessly; if they're vague, it enforces vagueness ruthlessly. Write a definitions block into the workbook and treat it as a contract:

• The period — last full ISO week, Monday–Sunday, in a named timezone (the app's, usually). Not "the last 7 days," which drifts and overlaps.
• The customer — the exact event that counts: purchase, a first purchase, purchase. Spelled character-for-character, with the dedup rule (unique users, not event rows) explicit.
• The scope — campaign prefixes in and out, geos, media sources, re-engagement excluded or not.
• The formulas — CAC = spend ÷ new customers (the definition above); CPI = spend ÷ MMP installs; ROAS basis and revenue source named.
• The targets — per-channel CAC/ROAS thresholds that drive the report's flags.

One page. Signed off by whoever answers for the numbers. This single artifact ends most of the arguments automation would otherwise inherit.

Part 3 — The pulls, platform by platform

Each source has mechanics that decide whether the numbers are right:

Meta — act_<id>/insights with explicit fields and the attribution setting pinned per request (e.g. 7-day click / 1-day view). Meta credits conversions to the impression date, so last week rises for ~7 days; re-pull a trailing window every run or the report freezes a low number. (Meta specifics.)

Google — GAQL per 10-digit customer ID under the MCC; cost_micros ÷ 1,000,000; conversions are modeled, credited to the click date, and restate — same trailing-window discipline. (Google specifics.)

TikTok & Snapchat — reporting APIs at campaign/ad-group and campaign/ad-squad grain; their self-reported conversions run on their own windows (and SKAN on iOS), so they enter the report as labeled claims, never as the denominator.

The MMP (AppsFlyer) — the outcome series: Master API for aggregated spend/installs, Raw Data Pull API for the event-level rows your KPI events come from, deduplicated per user, SKAN kept in its own columns. This is what CAC and ROAS compute on.

The cross-cutting rules: one period resolved per source in its timezone and landed in the report's; one FX source; platform claims and MMP outcomes in separate, labeled columns, reconciled with a variance view. (Reconciliation mechanics.)

Part 4 — The write: where automations earn trust or lose it

The write into a structured Sheet is the part most tooling gets wrong, and the part with the strictest requirements:

1. Schema re-validation — every run re-reads tabs, headers, sections and the append anchor and matches them against the stored mapping. Anything moved → halt with a diff. Never guess.
2. The anchor — the next period belongs in the exact row under the current monthly section, not the first empty row in a column.
3. The duplicate guard — week 24 already present → the run refuses, even if triggered twice.
4. Append-only — existing cells are never replaced; history is immutable.
5. Formula extension — calculated columns extend over the new row; values never paste over formulas.
6. The preview — a diff of the target row and values before anything changes, with the audit entry after.

(The write mechanics in depth.) These six behaviors are the difference between automation you babysit and automation you schedule.

Part 5 — The output: a report designed to be read

Automation should ship the report your stakeholders already trust — same structure, same formatting — plus a summary built for the 15-second Monday skim: headline numbers with WoW deltas, exceptions flagged against the targets in the definitions block, one link to the Sheet. (Report design in full.)

The flags do the triage; the human writes the three sentences of judgment on top. That division — mechanics automated, narrative human — is the stable end state.

Part 6 — The schedule, with grown-up semantics

Weekly on Monday before the team logs on, in the report's timezone. Every run: re-validate → pull (with trailing windows) → reconcile → duplicate-check → previewed append → summary. Failure policy: no partial writes — a run completes or leaves the report untouched and alerts loudly with the reason. Add the daily pacing pulse and the month-end close (timed after restatements settle) as their own schedules on the same pipeline. (Schedule semantics in depth.)

Part 7 — The rollout that actually works

1. Map one report — let the automation show you the structure it detected; correct it once.
2. Run one cycle in parallel — automated beside manual; resolve every discrepancy to a definition and update the block.
3. Test the guards deliberately — rename a column in a copy (must halt); fire the schedule twice (must refuse the duplicate).
4. Hand over the schedule — manual process retired, preview policy as strict as you like.
5. Then scale — the second report is faster, the tenth is routine.

Resist the urge to automate the messiest report first. Prove the loop on the boring one; take the flagship with the lessons learned.

When a weekly shouldn't be automated

While the structure or definitions still change monthly; for one-off analyses; for a brand-new account whose first weeks are still negotiating what the report is. Automation amplifies whatever it runs — stabilize, then schedule.

How Opera runs all of this

This pillar is, section for section, what Opera's reporting automation does natively: schema detection and drift halts, both AppsFlyer APIs at the right grain, pinned platform pulls with trailing windows, your definitions stored once, previewed append-only writes, reconciliation built in, and schedules with no-partial-writes. The setup is a mapping confirmation, not a project — which is why the rollout above takes a week, not a quarter.

"Every Monday at 7am, update the weekly report with last week's spend, results and CAC by channel — reconciled against AppsFlyer — and post the summary to #growth with anything off-target flagged."