Google’s BigQuery Data Transfer Service (DTS) provides a free, zero-maintenance path to landing Google Ads data in BigQuery. No API authentication, no rate limit handling, no pipeline to maintain. Configuration takes minutes, and within 24 hours 88+ tables refresh automatically each day. Three data quality issues — inflated impression counts, incomplete Performance Max data, and a schema requiring JOINs — require explicit handling in the dbt layer.
This cluster of notes covers setup, known data gaps, and dbt modeling patterns for DTS tables.
The Notes
Google Ads BigQuery DTS Setup — What DTS gives you, how the schema is organized (attribute vs stats tables, p_ prefix), refresh window configuration, backfill behavior, and why MCC-level setup is the correct default for anyone managing more than one account.
Google Ads ClickType Impression Trap — The single most dangerous DTS gotcha. Stats tables repeat impressions across every click type, inflating counts 3-6x. The fix is a specific SQL filter that only applies to impressions, not clicks — and the asymmetry is the part that catches people who think they’ve already handled it.
Google Ads Performance Max Data Gaps — PMax campaigns require a hidden checkbox to appear in BigQuery at all, and even with that checkbox, metrics are frequently missing. If PMax represents more than 20% of your spend, DTS alone won’t give you complete data. This note covers what’s missing, why, and the three options for filling the gap.
Google Ads DTS dbt Integration — How to configure dbt models on top of DTS tables: insert_overwrite incremental strategy to match DTS’s partition-replacement behavior, the 30-day conversion lookback window, and a full base model that handles the ClickType trap, the micros conversion, and the attribute/stats JOIN in one place.
Where DTS Fits
DTS is the right choice when:
- You need Google Ads data at daily grain (not intraday)
- PMax is a minor fraction of your spend, or you’re willing to accept its data gaps
- You want zero infrastructure to manage
- Cost matters (DTS costs almost nothing; alternatives start at $500+/month for managed connectors)
DTS is insufficient when you need intraday refreshes, complete PMax data, or schema control over which tables and fields get loaded. See Ad Data Extraction Tools for the full comparison between DTS, managed connectors (Fivetran, Airbyte), open-source tools (dlt), and direct API access.
Related Context
- Ad Data Extraction Tools — Where DTS fits in the broader extraction landscape
- dbt Ad Reporting Patterns — How to build cross-platform unified models that normalize Google Ads alongside Meta, LinkedIn, and others
- Ad Pipeline Engineering Challenges — The micros conversion, schema changes (Google’s v14→v16 API migration renamed columns to
metrics_*andsegments_*prefixes), and attribution window normalization