Google Ads → BigQuery → Looker Studio — The Marketing Attribution Pipeline · Blog

The Google Ads dashboard shows you spend and conversions. It doesn't show you what customers actually do before converting — which channels warm them up, which touchpoint closes the deal, or how long the journey takes. When you're managing multi-million dollar budgets across brand, performance, and retargeting campaigns, "we got 42 conversions" tells you nothing about efficiency. Attribution models matter. This is the pipeline Aiden built to replace the Ads UI: Google Ads → BigQuery Data Transfer Service (fully automated) → partitioned tables + SQL transforms → Looker Studio dashboards. It handles $8M+ annual spend, runs on a daily cron, and gives you attribution accuracy the native UI can't touch.

Why BigQuery Instead of Looker Data Studio's Native Connector?

Google Ads connects natively to Looker Studio — you can plug in your account and build charts in minutes. But native connectors give you one aggregation model and whatever dimensions Google chose. You can't write custom SQL, you can't build custom attribution, and you're locked into day-level granularity. BigQuery unlocks SQL: you own the transformation layer. You can build time-decay attribution (first touch weighted lower than last), multi-touch models (credit splits across all touchpoints), or product-specific attribution (weight by margin instead of just revenue). For $8M+ spend, the difference between "42 conversions" and "campaign A owns 18 of those per our model" is millions of dollars in budget allocation.

The Architecture: DTS → Partitioned Tables → Transforms → Dashboards

Day 1: Set up Google Ads Data Transfer Service (in Google Cloud console). DTS automates the pull — every day at 02:00 UTC, Google pushes your previous day's campaign, ad group, and keyword-level metrics into a BigQuery dataset. The table lands raw: google_ads_account_*_campaign. Costs around $0 if you're under 10GB/day of data (most accounts are). This is your source of truth.

Day 2: Create a transformation layer in BigQuery. Build a materialized view or scheduled query that normalizes the raw DTS table: convert micros to dollars, add date partitions, compute ROAS, and add cohort information (which week did this user first convert?). Store this in a ads_campaigns_daily table keyed by (date, customer_id, campaign_id). This table is your semantic layer — every downstream dashboard and attribution model queries it.

SELECT
  date,
  customer_id,
  campaign_id,
  campaign_name,
  CAST(cost_micros AS FLOAT64) / 1000000 AS cost_usd,
  conversions,
  CAST(conversion_value_micros AS FLOAT64) / 1000000 AS revenue,
  SAFE_DIVIDE(
    CAST(conversion_value_micros AS FLOAT64) / 1000000,
    CAST(cost_micros AS FLOAT64) / 1000000
  ) AS roas
FROM `project.dataset.google_ads_account_*_campaign`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
PARTITION BY date

Day 3: Build attribution models in SQL. Last-click is the default (credit 100% of the conversion to the last ad they clicked). But time-decay is richer: if a user saw your brand ad on Monday and clicked a performance ad on Friday, weight Friday's spend higher but give some credit to Monday (the user was primed). SQL window functions do this in one query — partition by user, order by timestamp, assign weights based on position, then aggregate.

Day 4: Wire Looker Studio to your ads_campaigns_daily and attribution tables. Build four dashboards: (1) campaign ROI ranked by ROAS, (2) attribution breakdown by channel, (3) daily spend trends with forecast, (4) cohort metrics (users acquired in week X how many converted by week Y?). Each chart sources a simple query, not raw tables. Looker Studio handles refresh hourly but your underlying data refreshes once daily, which is fine — Google Ads finalizes metrics at 02:00 UTC anyway.

Custom Attribution Models in SQL

The unlock: time-decay attribution in pure SQL. Flag each touch in the user journey, assign weights (first touch = 0.2, subsequent touches = 0.4, last touch = 0.4), then SUM the weighted revenue per campaign. If a user saw 3 touchpoints before converting, each campaign gets a slice of that conversion proportional to its position.

WITH journey AS (
  SELECT
    user_id,
    campaign_id,
    timestamp,
    revenue,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY timestamp) AS touch_seq,
    COUNT(*) OVER (PARTITION BY user_id) AS total_touches
  FROM events
)
SELECT
  campaign_id,
  SUM(revenue *
    CASE
      WHEN touch_seq = 1 THEN 0.2
      WHEN touch_seq = total_touches THEN 0.4
      ELSE 0.4 / (total_touches - 2)
    END
  ) AS attributed_revenue
FROM journey
GROUP BY campaign_id

This beats last-click by orders of magnitude: you see which campaigns actually warm people up for conversion, not just which campaign happened to be last. Retargeting appears less magical. Brand shows up as the true lead generator.

Comparison to GA4 Attribution

GA4 has Data-Driven Attribution (Google's ML model) baked in. But it operates on website events, not ad-level spend. You can't answer "did my $500k brand campaign generate ROI?" because GA4 sees conversions, not the budget poured into each channel. BigQuery + Google Ads DTS answers that directly: here's the spend by campaign, here's the attributed revenue per model, here's the gap. GA4 remains your truth for on-site behavior; BigQuery is your truth for ad ROI. Use both — wire them together at the user level if your setup allows (store GA4 user IDs in your ads conversion tracking).

Frequently Asked Questions

Does this replace my Google Ads dashboard?

No. Use Ads' native dashboard for diagnostics (why did CTR drop?). Use BigQuery + Looker for strategy (which channels are profitable?). One is operational, one is strategic.

How much does BigQuery cost for this pipeline?

DTS is free. BigQuery charges per GB queried — about $7.50 per TB. A 90-day attribution model across 100 campaigns scans ~1GB, so roughly $0.01 per day. Negligible for the ROI unlock.

Can I use Data Studio's native Google Ads connector instead?

Yes, but you lose custom attribution. Native connector is "plug and play"; BigQuery is "do the work once, own the model forever". Pick based on complexity tolerance.

What if my company uses Meta, TikTok, and Google Ads?

Replicate the DTS pattern for each platform: Meta's API → BigQuery, TikTok's API → BigQuery. Then join them in a unified all_channels_daily table and build one dashboard across all three. This is the real unlock of the pipeline approach.

How granular should my attribution be?

Start campaign-level. Add ad-group and keyword level only if you're spending over $500K/month in any single campaign — the signal-to-noise ratio improves at that scale. Below that, campaigns are the right grain.

Do I need to know SQL to use this?

Yes — that's the whole point. You own the transformation layer. The effort upfront pays for itself once in monthly budget cycles.

The Bottom Line

Google Ads UI is a campaign manager; BigQuery is a scientist. For businesses under $1M annual ad spend, the UI is fine. Above that, custom attribution models pay for themselves within weeks. The pipeline (DTS → transforms → Looker) takes a day to set up, costs virtually nothing, and gives you accuracy that no native connector can match. See how we approach data-driven marketing or dive deeper into unified marketing analytics across GSC, GA4, and Meta.

Data Engineering