If someone spends every Monday copying numbers out of five dashboards, automate the collection, the math and the first draft, and keep the judgment human. What stops AI from inventing numbers is structural: code calculates every metric, validation checks run before anything ships, and the model only writes about a table it was handed. Here’s how to build that pipeline, with an example weekly workflow in n8n.
What to automate and what to keep human
A weekly report does four jobs: collect data, calculate metrics, describe what changed and decide what to do. Automate the first three, and let AI touch only the third.
| Task | Who does it | Why |
|---|---|---|
| Pulling data from ad platforms, GA4, CRM and store | Automation | Error-prone by hand |
| Calculating spend, CPA, ROAS, MER, pipeline | Code or SQL | Must be exact and repeatable |
| Flagging unusual movements | Rules and statistics | Needs consistent thresholds |
| Writing the first-draft summary | AI | Turns a table into readable sentences |
| Explaining why a number moved | Human (AI may suggest hypotheses) | Needs context the data doesn’t hold |
| Budget, target and channel decisions | Human | Accountability stays with a person |
| Metric definitions | Human, written down once | Everything downstream depends on them |
The principle: the model writes, it doesn’t count. Language models are unreliable at arithmetic over large tables and fill gaps with plausible sentences. If every number traces back to a calculated cell, a made-up figure has nowhere to come from.
Mapping your data sources
Before building anything, map each source: what you pull, the grain, the join key, timezone, currency and account owner.
| Source | Pull | Grain | Watch out for |
|---|---|---|---|
| Google Ads | Spend, clicks, conversions, conversion value | Campaign by day | Late conversions, dated by click |
| Meta Ads | Spend, impressions, purchases, purchase value | Campaign by day | Results change until the attribution window closes |
| LinkedIn Ads | Spend, clicks, leads | Campaign by day | Platform leads differ from CRM leads |
| GA4 | Sessions, key events, revenue | Channel by day | Data takes a day or two to settle |
| CRM (HubSpot, Salesforce) | Leads, qualified leads, pipeline, closed-won | Record by stage-change date | Stage definitions and backdated changes |
| Store (Shopify) | Orders, net sales, refunds, new vs returning customers | Order by day | Refunds land after the week closes |
Then write a metric dictionary: every KPI with its formula, source and owner. “Revenue” should mean one thing, such as store net sales after discounts and refunds.
Also decide which system owns which number. GA4, Meta and your store will disagree, and a report showing three revenue figures creates more meetings than it saves. Why GA4 and ad platforms don’t match covers which number fits which decision.
Building the data pipeline
There are two realistic setups:
- Google Sheets plus a connector or automation tool. Fine for a few sources, a weekly cadence and campaign-level data, and easy for the team to inspect.
- A warehouse such as BigQuery. Better for daily data across many campaigns, long history, or joins between order, CRM and ad data. GA4 exports natively to BigQuery, Google Ads loads through the BigQuery Data Transfer Service, and a connector or n8n covers the rest.
Land raw data, then transform
Keep three layers: raw data exactly as the API returned it, a clean table with one standard schema (date, platform, campaign ID, campaign name, spend, clicks, conversions, revenue), and a report table with the calculated KPIs. Never overwrite raw data; when a number looks wrong, you need to see what the platform sent.
Re-pull a trailing window
Ad platforms restate recent conversions as late ones arrive, so each run should re-pull a trailing window, say 14 days, and upsert on date, platform and campaign ID. Appending instead of upserting creates duplicates, a common cause of inflated totals in automated reports.
Normalize before you calculate
Convert every source to one timezone and one reporting currency, noting the exchange rate used. Calculate metrics in SQL or sheet formulas with safe division, so zero conversions shows a blank, not an error.
For orchestration I usually build in n8n, because code nodes and self-hosting suit data work, but Zapier or Make can run a simpler version. n8n vs Zapier vs Make compares them for marketing teams.
Adding AI-written summaries and anomaly alerts
Summaries
Give the model a compact, pre-calculated table, not raw rows: each KPI with this week, last week, the four-week average, the target and the percentage change. Twenty rows of finished numbers beat twenty thousand rows of campaign data and leave the model nothing to calculate.
Prompt rules that hold up:
- Use only numbers that appear in the table, written exactly as they appear.
- Do not calculate new figures, totals or percentages.
- If something needed for a conclusion isn’t in the data, say so.
- Label any explanation as a possible cause for the owner to check.
- Return JSON with a headline, what improved, what worsened and questions for owners, so the delivery step formats it reliably.
Anomaly alerts
Detection should be rules and statistics, not AI. Compare each metric with a baseline, such as the same weekday over the last four weeks, and flag it when it moves outside a band you set. Add minimum volumes so a campaign with three conversions can’t trigger an alarm.
Rules worth having from day one:
- Spend above the daily budget by a set margin
- Spend with zero conversions for two days on a campaign that normally converts
- GA4 purchases or form submissions near zero while store orders or CRM leads look normal, which usually means tracking broke
- A source that returned no data at all
AI only writes the alert. As an example with round numbers: if Meta normally spends around $2,000 a day and Tuesday hits $3,100, the rule fires, and the model turns it into a two-line Slack message naming the campaign and what to check first.
Delivering reports where the team works
A report nobody opens isn’t automated; it’s abandoned. Deliver it where people already look.
- Weekly summary in Slack (or Teams, or email for leadership) before the weekly meeting, so everyone starts from the same numbers.
- A fixed format: headline, five to eight KPI lines against last week and target, three bullets on what changed, open questions tagged to owners, and a link to the sheet or dashboard.
- A separate alerts channel. Alerts mixed into general chat get muted. Keep them rare, tag the owner, and loosen thresholds that fire too often.
- A drill-down dashboard in Looker Studio or similar for campaign-level detail. The summary answers what happened; the dashboard answers where.
Validation checks and guardrails
These checks run after the data lands and before the summary is written. If any fails, the workflow sends “Report held: [check] failed” to the owner instead of the report. A late report is an annoyance; a wrong one gets acted on.
- Freshness: every source has data for every day in the period
- Completeness: row counts per source are within the range of previous runs
- Uniqueness: no duplicate date, platform and campaign keys
- Totals match the source: weekly spend per platform matches a separate account-level pull within a small tolerance
- Sanity bounds: no negative spend, CTR under 100%, ROAS and conversion rate within plausible ranges
- Cross-source ratios: GA4 purchases vs store orders and platform leads vs CRM leads within their usual bands
- Currency and timezone match the metric dictionary
- Output check: every number in the AI summary appears in the input table; if one doesn’t, regenerate once, then fall back to a numbers-only template
Two more guardrails: send the model aggregated metrics only, never customer names, emails or order-level data, and keep API credentials in the automation tool’s credential store, not in prompts or sheets.
Example weekly report workflow
Here’s the full pipeline in n8n. The same pattern works in Make or a script.
- Schedule Trigger: Monday at 6 a.m. in the team’s timezone.
- Pull: native nodes where they fit (HubSpot, Shopify, Google Analytics) and HTTP Request nodes for the Google Ads and Meta APIs. Pull five weeks so the baseline recalculates each run.
- Normalize: a Code node maps every source to the clean schema.
- Store: upsert into the clean table in Google Sheets or BigQuery.
- Calculate: SQL or a Code node builds the KPI table: this week, last week, four-week average, target.
- Validate: a Code node runs the checklist above; an IF node routes failures to the owner in Slack and stops.
- Summarize: an LLM chain node with the prompt rules and a structured output parser.
- Verify: a Code node extracts every number from the text and compares it with the KPI table.
- Deliver: the Slack node posts to the reporting channel with a link to the sheet.
- Log: append a row with run time, row counts, checks passed and the model used.
Attach an error workflow (Error Trigger node) so a failed API call pings someone instead of failing silently, and run anomaly alerts as a separate daily workflow reusing steps 2 to 6.
The Monday message ends up looking like this (example numbers):
Weekly marketing report, Jul 6-12
Spend $42,000 (+8% vs last week, budget $40,000)
Store net sales $168,000 (+3%), MER 4.0 (4-week avg 4.2)
New customers 1,150 (-2%), blended CAC $36.52
Watch: Meta prospecting CPA up 18% on flat spend
Question for paid social: did new creative launch Wednesday?A first version with three to five sources in Sheets typically takes two to four weeks to build and tune, mostly spent on the metric dictionary and validation rather than the AI step. It’s one of the most common builds in my AI automation work, because it gives Monday back to whoever built the report by hand.
Get it built
If your team still builds the report by hand, or its automated one ships numbers nobody trusts, the AI automation add-on (from $2,500/mo) covers the pipeline, checks and summaries. If the tracking underneath needs fixing first, start with the Growth Audit: $1,500 fixed, credited if we continue. See pricing or get in touch.