Case study · 04
Monthly Google Ads Performance Analysis
Automates monthly Google Ads reporting by calculating KPIs in code, grounding AI commentary in verified evidence and archiving a consistent stakeholder report.
System overview — a simplified portfolio diagram showing the governed path and human-control boundary.
At a glance
- Audience
- Paid-media, growth, digital marketing and marketing analytics teams
- Business problem
- Repetitive exports, inconsistent spreadsheet calculations and slow stakeholder reporting
- My role
- Reporting requirements, GAQL query, deterministic calculations, AI evidence controls and reporting architecture
- Validation stage
- Mock data and KPI logic validated offline; live external integrations remain disconnected
Project overview
Project overview
I designed and implemented a repeatable monthly reporting workflow that calculates the exact previous calendar month, retrieves or safely mocks responsive-search-ad data, calculates core KPIs in JavaScript and supplies only aggregated evidence for schema-validated AI commentary.
Business challenge
Business challenge
Repeated exports, spreadsheet calculations and report assembly can delay monthly reporting and produce inconsistent outputs.
How it works
How the workflow works
- 01
Calculate the exact previous calendar month.
- 02
Use sanitised mock data or query SearchStream when live mode is explicitly enabled.
- 03
Normalise rows and calculate weighted KPIs deterministically.
- 04
Prepare minimal aggregated evidence for structured analysis.
- 05
Validate or fall back to a deterministic report, then archive idempotently.
Designed value
Expected operational value
- A consistent monthly reporting process
- Fewer spreadsheet calculation errors
- Clearer results for non-technical stakeholders
- An idempotent historical reporting record
Responsible AI & human control
Responsible AI and human control
- Mock mode on by default
- External writes and notifications off by default
- Zero denominators handled explicitly
- AI cannot invent or calculate metrics
- Unique report key prevents duplicate periods
My contribution
What I built and validated
I configured the GAQL reporting logic, implemented deterministic JavaScript calculations, created the mock-data validation path, structured the AI evidence contract and added schema validation, fallback reporting, idempotent Google Sheets archival and guarded Slack notification.
What I validated
- 21-node workflow imported and reviewed against the reporting design
- Previous-calendar-month date handling represented
- Mock fixtures and core KPI calculations tested offline
- Weighted CTR and zero-denominator handling represented explicitly
- Structured AI validation and deterministic fallback reporting represented
- Unique report key represented to prevent duplicate reporting periods
Implementation evidence
Current validation status
KPI logic tested · offline validatedMock fixtures and KPI calculations were tested offline, including weighted CTR, zero-value handling, schema validation, fallback reporting and duplicate-period prevention. Live Google Ads, OpenAI, Google Sheets and Slack execution remains intentionally disconnected pending private configuration and reconciliation.