Ads Reporting Automation
Daily, hands-off reporting for 40+ Meta ad accounts across several Business Portfolios, synced two-way with the team’s Google Sheets and a web dashboard.
01Problem
The ads team copied spend, purchases and revenue from dozens of ad accounts into monthly sheets by hand. It was slow, numbers were often missing or wrong for recent days, and every new month meant rebuilding files and formulas.
The goal: numbers that match Ads Manager without anyone touching them, while keeping the team’s existing sheets and formulas.
02Architecture
Each ad account is read with the System User token of the portfolio that owns it, so no account depends on a single token.
03My contribution
- Built the Apps Script that pulls each account daily and writes to the right row and month file, without overwriting formulas.
- Re-syncs the last three days on every run so late-attributed conversions are corrected automatically.
- Auto-creates next month’s file from the current one: dates rebuilt for the new month, inputs cleared, formulas untouched.
- Two-way sync: edits in the sheet reach the dashboard, and changes from the dashboard are written back.
- Safe rollup editing: when an account is added, its reference is inserted into the team’s rollup formula only if the formula pattern is recognised, with a character-level check and a backup for undo.
- Added TikTok Ads validation against the API and a daily "valid up to" status.
04Technical challenges
Spend not matching Ads Manager
Account-level spend included campaigns the team does not count, while conversions were double counted at campaign level.
Spend summed at campaign level for active/paused campaigns only, conversions read once at account level with the same attribution window as Ads Manager.
Accounts in different portfolios
Some accounts returned permission errors (#200) because they were read with the wrong token.
Mapped every account to its owning portfolio’s System User token and made the live fetch use the same mapping as the scheduled sync.
API rate limits
Pulling every account at once hit Meta’s request limit (code 17).
Queued requests with back-off and retries; temporary errors are retried instead of being shown as failures.
Editing formulas owned by another team
The rollup sheet’s formulas belong to the ads team and must not be rewritten.
Insert-only change that mirrors the last account’s pattern, verified so the old formula is unchanged apart from the insertion, with automatic backup.
05Results
- Daily reports for 40+ accounts with no manual copy-paste.
- Morning validation: sheet = dashboard = Meta API for previous days, with zero difference.
- Month rollover happens on its own; the team keeps using the same sheets.
06Evidence
- Blurred dashboard screenshot (above).
- Pipeline and token strategy described on this page.
- Script structure and safeguards can be shown in an interview.
Internal company system. Source code is private; screenshots are blurred to hide business figures and personal data. Happy to walk through the code and architecture in an interview.