Google Ads BigQuery Data Transfer Service Setup

The Google Ads BigQuery Data Transfer Service (DTS) provides a free, zero-maintenance way to load Google Ads data into BigQuery. Configuration takes minutes in the BigQuery Console, and within 24 hours 88+ tables of campaign data refresh automatically each day. No code, no API authentication, no rate limit handling.

Three issues require explicit handling: impression counts are inflated without a click-type filter, Performance Max data is incomplete by default, and the schema requires JOINs between attribute and stats tables to produce usable reports.

Prerequisites and Setup

DTS requires three things before you can start:

• An active Google Cloud project with billing enabled
• BigQuery Admin role on the project
• Read access to a Google Ads Customer ID or MCC account

Setup is done through the BigQuery console under “Data Transfers.” Select Google Ads as the source, choose Standard or Custom (GAQL) report type, enter the Customer ID, and set the refresh window.

The refresh window (1 to 30 days, default 7) controls how many days of historical data get replaced on each run. Google Ads conversions arrive late — sometimes for days after the click — so setting the refresh window too short means your conversion numbers will be permanently understated for older dates. Seven days is a reasonable default. If your conversion window is longer (common in B2B), push it to 14 or 30 days.

Maximum transfer frequency is once every 24 hours. If your team needs intraday performance data for budget decisions, DTS can’t help — you’d need direct API access or a managed connector.

Backfills go up to 180 days per request, but you can chain multiple requests sequentially if you need further history. Running a transfer twice on the same date overwrites that partition instead of creating duplicates, so backfilling is safe to retry.

Schema Overview: 88 Tables, Two Types

DTS creates 88+ tables organized around a star schema. Understanding the two table types is the key to writing correct queries.

Attribute tables hold dimensions: campaign names, ad group names, IDs, statuses, settings. No metrics. Examples: Campaign, AdGroup, Keyword, Ad.

Stats tables hold facts: impressions, clicks, cost, conversions. IDs only — no names. Examples: CampaignBasicStats, AdGroupStats, KeywordStats.

Every table has two versions. Campaign_CUSTOMERID is a view. p_Campaign_CUSTOMERID is the date-partitioned table underneath. The naming convention is consistent: the p_ prefix means you’re querying the partitioned table directly.

Always use the p_ versions in production. The unprefixed views scan the full table on every query, regardless of your date filters. An innocent-looking SELECT * on the non-partitioned view will scan months of data and burn through your on-demand BigQuery budget. The partitioned tables respect _DATA_DATE filters and enable partition pruning.

-- Always do this
SELECT *
FROM `project.dataset.p_CampaignBasicStats_CUSTOMERID`
WHERE _DATA_DATE >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)

-- Never do this in production
SELECT *
FROM `project.dataset.CampaignBasicStats_CUSTOMERID`

The _DATA_DATE column is the partition key across all DTS tables. Filter on it explicitly and queries stay cheap.

MCC vs Per-Account Setup

If you manage more than one Google Ads account, configure the transfer at the MCC (My Client Center) level — not at individual account level. This is not an edge case recommendation; it’s the correct default for any non-trivial setup.

A single MCC transfer manages all child accounts, up to 2,000 Customer IDs. All data lands in the same tables, enabling cross-account queries without UNION gymnastics. It also avoids the quota issues that individual per-account transfers create as volume grows.

The alternative — a separate transfer for each Customer ID — results in separate datasets, separate monitoring, and separate failure points. For agencies managing multiple brands or any business with more than one ad account, the operational overhead compounds quickly. Google explicitly recommends MCC-level setup, and it’s worth doing upfront before you’ve built reports pointing at individual account datasets.

What DTS Costs

The connector itself is free. No transfer charges for first-party Google connectors. You pay only BigQuery costs:

• Active storage: $0.02/GB/month
• Long-term storage (>90 days unmodified): $0.01/GB/month
• On-demand queries: $6.25/TiB processed (note: the article cites the older $5/TB rate, current rate is $6.25/TiB)

Most moderate Google Ads setups cost $1 to $5 per month total. Even large accounts rarely exceed $20/month. Advertising metadata doesn’t generate the data volumes that event-level analytics do — a campaign might have thousands of rows per day, not millions.

The single most impactful cost control: always query p_ prefixed tables with a _DATA_DATE filter. This limits BigQuery to scanning only the partitions you need instead of the entire table history.

The Three Gotchas You Need to Know

DTS is a genuinely good tool hiding behind three genuinely bad defaults:

1. The ClickType impression trap — Stats tables contain a segments_click_type column, and impressions are repeated across every click type. Without filtering, impression counts can be 3-6x higher than reality. See Google Ads ClickType Impression Trap for the full treatment and the correct SQL fix.

2. Performance Max data gaps — PMax campaigns require checking a separate checkbox during setup, and even then, metrics are frequently missing from stats tables. If PMax is a significant part of your ad spend, DTS alone won’t give you complete data. See Google Ads Performance Max Data Gaps.

3. Stats tables have no names — Attribute tables have names, stats tables have IDs. To build a human-readable report, you must JOIN them using the pattern described in the source article. The JOIN requires filtering the attribute table to _LATEST_DATE to get current campaign names rather than historical values.

With those three issues handled, DTS requires minimal ongoing maintenance and costs almost nothing.