The Google Ads UI is fine until you need it to answer real questions. "Which creative assets drive conversions by ZIP code?" The UI says no. "Compare CPL across campaign managers and geocoded regions?" No again. "Pull raw impression data for 6 months and machine-learn the winner?" Absolutely not.
Google Ads was built to show clicks and spend. It wasn't built for the questions that matter when your ad budget is $2M a year and you're trying to survive a quarterly loss. That's where the pipeline lives. Google Ads API → BigQuery raw tables → Python transform layer → Looker Studio dashboards + custom charts. Aidxn shipped this at Rebuild Relief (insurance claims, hail and storm). This is what it looks like under the hood.
Why Pull Google Ads Into BigQuery At All?
Because the Google Ads UI optimises for clicks, not answers. The UI is a funnel: campaigns → ad groups → ads → keywords. Every metric is pre-aggregated. You see performance rolled up to the level you're looking at, and if that isn't the level where the real answer lives, you're stuck.
BigQuery has the opposite constraint: it holds every row and lets you ask questions the original schema never imagined. Pair impression-level data (when, where, what keyword, what device, what placement) with conversion-level data (which lead, their ZIP, repair cost, claim status) and suddenly you're answering questions like: "Storm damage claims in postcode 4000–4003 convert 3× better from mobile display ads than desktop search. Should we shift budget?" Google Ads can't see the connection. BigQuery finds it in 2 seconds.
The second reason is history. Google Ads UI shows the last 90 days by default. If you want to compare Q4 2024 to Q3 2024 to Q2 2024 to identify seasonal shifts in CPL, you're clicking through four screens. In BigQuery, it's a WHERE clause. Append every day's data to the raw table. After 6 months, you have 180 days of denormalized facts. No archiving. No export. No guessing.
The Schema: Four Tables, One Truth
campaigns: campaign_id, campaign_name, status, budget_daily, start_date, end_date. Every time a campaign name changes or budget updates, the row gets replaced (not appended). This is slowly-changing dimension (SCD) Type 1: keep the current state, discard history. ad_groups: campaign_id, ad_group_id, ad_group_name, status, max_cpc, max_cpm. Same SCD Type 1 pattern. ads: ad_group_id, ad_id, ad_headline, ad_description, display_url, status, ad_type (search / display / shopping). The creative layer. Rows are immutable — once an ad is paused, it doesn't update, a new row appears. keywords: ad_group_id, keyword_id, keyword_text, match_type (broad / phrase / exact), status, max_cpc, quality_score. Immutable rows again.
Then add the metrics table: daily_metrics: date, campaign_id, ad_group_id, ad_id, keyword_id, impressions, clicks, spend, conversions, conversion_value, conversion_lag (days between click and conversion). This is the firehose. One row per campaign/ad_group/ad/keyword per day, regardless of whether metrics were zero. It balloons. For 6 months across 50 campaigns, expect 2–5M rows. That's fine. BigQuery can scan 5M rows and aggregate in milliseconds.
The Daily Extract: Google Ads API → Cloud Run
Google Ads API has rate limits (~10k queries/day in standard mode) and requires OAuth. The pattern: store credentials in Secret Manager, spin up a Cloud Run job nightly, pull yesterday's metrics from each entity type, append to BigQuery, done.
import google.ads.google_ads.client
from google.cloud import bigquery
from datetime import datetime, timedelta
client = google_ads.google_ads.client.GoogleAdsClient.load_from_storage()
ga_service = client.get_service("GoogleAdsService")
bq = bigquery.Client()
yesterday = (datetime.now() - timedelta(days=1)).strftime("%Y-%m-%d")
query = f"""
SELECT
campaign.id,
campaign.name,
campaign.status,
ad_group.id,
ad.id,
ad.headline_part1,
ad.description1,
metrics.impressions,
metrics.clicks,
metrics.cost_micros / 1_000_000 as spend,
metrics.conversions,
metrics.conversion_value_micros / 1_000_000 as conversion_value
FROM ad_group_ad
WHERE segments.date = '{yesterday}'
AND campaign.status != 'REMOVED'
LIMIT 10000
"""
results = ga_service.search(customer_id="1234567890", query=query)
rows = []
for row in results:
rows.append({
"date": yesterday,
"campaign_id": row.campaign.id,
"ad_group_id": row.ad_group.id,
"ad_id": row.ad.id,
"impressions": row.metrics.impressions,
"clicks": row.metrics.clicks,
"spend": row.metrics.cost_micros / 1_000_000,
"conversions": row.metrics.conversions,
"conversion_value": row.metrics.conversion_value_micros / 1_000_000,
})
errors = bq.insert_rows_json("rebuild-relief-ads.google_ads.daily_metrics", rows)
if errors:
print(f"Insert failed: {errors}")
This Cloud Run task runs daily at 2am. It pulls from Google Ads API, dedups on date + campaign_id + ad_group_id + ad_id (in case of retries), and appends to BigQuery. Cost: ~$1–3/day in Cloud Run + BigQuery slots. Google Ads API quota: negligible — 10k queries/day is plenty for 50 campaigns.
The Transform Layer: Materialized Views
Raw tables are immutable. They're your audit trail. The transform layer sits on top — SQL views that calculate what you actually want to see. Examples:
cpl_by_campaign_weekly: Weekly cost-per-lead rolled up by campaign, joining daily_metrics to your CRM's conversions table (which has ZIP code, claim status, repair cost). roas_by_creative: ROAS (return on ad spend = conversion value / spend) grouped by ad headline + description, so you spot which creative copy drives the best return. impression_share_by_device: Impression share (your ads / all ads in the auction) by device type (mobile / desktop / tablet) so you see if you're losing ground on mobile across specific ad groups.
These are materialized views, refreshed nightly after the daily extract completes. They're pre-aggregated. A dashboard query that would scan 5M rows in the raw table scans 500 rows in the materialized view. Looker Studio queries complete in milliseconds instead of 10 seconds.
Dashboard Ideas: What To Build First
CPL Trends: Line chart of cost-per-lead by week across the last 6 months, broken out by campaign. Color the line red if CPL is trending up (bad) and green if it's flat or down (good). Drill down into ad groups to find which cohorts are dragging overall CPL. Creative Performance Matrix: X-axis = impressions, Y-axis = ROAS. Each dot = one ad (headline + description). Spot the high-impression, low-ROAS creatives — those are budget drains. The high-ROAS, high-impression creatives are the workhorses; scale them. Geocoded Conversion Heatmap: Postcode-level heatmap overlay on a map of your service area. Each postcode colored by average lead cost and conversion rate. Identify "cheap lead" postcodes where CAC is abnormally low — they might be high-intent areas or strong brand presence. Keyword Health Scorecard: Table of top 50 keywords by spend, with quality score, conversion rate, and CPA. Highlight any keyword with quality score < 6 (Google's penalty zone) and low conversion rate — pause it or build a better landing page. Device and Placement Breakdown: Pie chart of spend by device type (mobile / desktop / tablet) and another by placement (search / display / YouTube). Most teams don't realize they're burning 40% of budget on underperforming mobile display. This makes it obvious.
Six FAQs
What's the latency? When do I see yesterday's data?
Google Ads API updates around 3am UTC. If you run the Cloud Run job at 2:30am, you'll miss yesterday's data. Run it at 4:30am. Data lands in BigQuery by 5am. Dashboards refresh at 6am. So at 6am, your team sees data that's 24–30 hours old. That's acceptable for daily budgeting and weekly strategic reviews. If you need real-time, you're doing it wrong — Google Ads is a slow channel. Decisions happen on a weekly cadence, not hourly.
Do I need dbt, or can I use SQL views?
SQL views are fine for starting. Materialized views are better — they're pre-baked, cheaper, faster. dbt adds version control and documentation but introduces dependency hell if you have 20+ views. If your transform layer is under 5 views, use SQL. At 10+, dbt is worth it. Velocity uses dbt for large pipelines; start simple.
How do I handle currency conversion if I'm running ads in multiple regions?
Store spend in the source currency. Join a static currency_rates table (updated weekly) that maps USD, GBP, AUD, etc. to a reporting currency (usually your home currency). Multiply spend × rate when aggregating for reports. Don't convert in the Python extract — keep raw data raw.
What happens if the API call fails mid-month?
Idempotency. Use MERGE statements instead of INSERT. The MERGE checks if {date, campaign_id, ad_group_id, ad_id} already exists. If it does, UPDATE the metrics. If not, INSERT. A retry on day 5 won't double-count day 1's data. Plan for this from day one.
Can I join Google Ads data to my CRM data for real cohort retention by ad source?
Yes, if your CRM stores utm_source and utm_medium (or gclid). Join on gclid if you have it — it's Google's click ID, immutable, uniquely identifies every ad click. If not, fall back to utm parameters and accept some slop due to renaming and typos. The join quality depends on your CRM hygiene. Rebuild Relief stores gclid on every lead, so the join is pixel-perfect.
What's the annual cost to run this pipeline?
Google Ads API: free (included with your ads account). Cloud Run: ~$40/month for daily extract + transforms. BigQuery: ~$100–200/month depending on query volume. Looker Studio: free (Google tool) or $400+/month if you're a large team using the pro version. Total: ~$150–250/month for the whole stack, which is 0.01% of your $2M annual ad budget. A no-brainer.
The Bottom Line
Google Ads UI is a view of your data, not the whole story. The moment you need cross-channel insights, historical comparison, or creative-level performance with geographic breakdowns, you've outgrown it. BigQuery is where the truth lives. Build the pipeline once (a week of engineering), maintain it for years (30 minutes a month). Your team stops asking "what happened?" and starts asking "what should we do?" That's when insights turn into action. Check Velocity pricing to explore how cohort analysis and retention metrics pair with your Google Ads pipeline for customer lifetime value insights, or read more on cohort retention curves for the full retention story.