Looker Studio
BigQuery
SQL
GA4
Google Ads API
Automation
The Challenge
The marketing team was spending 6+ hours every week manually pulling data from GA4, Google Ads, Meta Ads, and their CRM to build Excel reports. Data was always 1–3 days stale by the time it reached decision-makers, and there was no single source of truth.
What I Did
- Connected GA4 BigQuery export to centralise all website behaviour data
- Pulled Google Ads and Meta Ads data into BigQuery via scheduled data transfers
- Wrote SQL queries to create clean, aggregated reporting tables (CPL, ROAS, revenue by channel)
- Built a Looker Studio dashboard with 5 pages: Executive Summary, Paid Media, Organic, Email, and Pipeline
- Set up scheduled email delivery of the dashboard every Monday morning
- Added alert thresholds—if CPL exceeds target by 20%, the dashboard highlights it in red
- Documented the full data model so the internal team could maintain and extend it
Outcome
Weekly reporting time dropped from 6 hours to 30 minutes (just for review, no manual data pulling). The executive team now has real-time visibility into campaign performance. Data-driven budget decisions are made weekly instead of monthly.