The Fragmentation Problem
For a nationwide network like Apollo English, managing advertising across multiple platforms (Google Ads, Meta, TikTok) for 78 locations creates massive data fragmentation. Marketers often struggle to definitively answer: "What has actually worked over the last 3 years?"
Building the BigQuery Data Pool
To solve this, I bypassed fragile spreadsheet exports and built a centralized data pool using Google BigQuery. By setting up automated data pipelines using Supermetrics and custom scripts, we ingested daily performance data from every ad platform into a single, structured SQL database.
From Data to Real-time Dashboard
Once the data was secure in BigQuery, I connected it directly to Looker Studio. Because BigQuery handles the heavy lifting, the dashboards load instantly, even when filtering through millions of rows of historical advertising data.
The Business Impact
This integration allowed us to uncover long-term trends, calculate true Customer Acquisition Cost (CAC) by region, and immediately identify underperforming campaigns. The real-time visibility significantly improved ad spend efficiency across the entire 78 center network.