Google Ads to BigQuery in 2026: Build a Data Warehouse Instead of Rebuilding Reports

The Google Ads interface is very good at showing what is happening right now and noticeably worse at showing what happened eight months ago, how it compares to your cost of goods, and what your conversion rate did during last year’s promotion. Once you are asking that kind of question more than twice a week, manual work begins: CSV exports, spreadsheet joins, reports that are stale before they are sent. Moving Google Ads to BigQuery removes that entire class of work — data arrives on its own, history stays as long as you want it, and any slice is one query away.

What follows: what the setup actually gives you, how the Data Transfer Service compares to the API and to paid connectors, how the table schema is laid out, where double counting hides in it, the six queries that justify the project, and an honest look at cost.

The honest boundary: when the interface is enough

Not everyone needs a warehouse. If your whole analysis fits inside the interface, adding infrastructure buys complexity with no return.

Job	Interface is enough	Warehouse needed
Daily spend and CPA monitoring	Yes	—
Custom formulas over native metrics	Yes, via custom columns	—
Long history, year-over-year season comparisons	Limited	Yes
Joining CRM, COGS, refunds, LTV	No	Yes
Dozens of accounts in one report	Painful	Yes
Alerts on your own conditions	Partly	Yes
Keeping data if account access is lost	No	Yes

If your needs sit in the first two rows, start with custom columns and the report editor — free, and they cover more than people assume. BigQuery starts where the data you need does not exist in the interface at all.

Three ways to get the data in

Method	Strengths	Trade-offs	Best for
Data Transfer Service (native)	Set up in an hour, no code, first-party support, works on a whole manager account	Fixed schema, once-daily refresh, limited report coverage	Most teams — start here
Google Ads API + your own loader	Any report and field, your own cadence and schema	Build and maintenance cost, API quotas, error handling	Teams the native set does not cover
ELT connectors	Many sources at once, normalisation out of the box, frequent syncs	Subscription cost, vendor dependency, their own field limits	Agencies with a zoo of sources

A sane path: the native transfer as the backbone, a small API loader for the two or three reports it misses, and a connector only if Google Ads is one of five or more platforms you need in the same place.

How the Data Transfer Service works

What you need to start

• A Google Cloud project with billing enabled — the transfer will not be created without it, even if your volumes fit the free tier.
• Permission to create transfers in the project and write to the destination dataset.
• An identity with read access to the ad account.
• The account ID. You can point it at a single account or at a manager account, in which case the transfer pulls every linked sub-account into one dataset with a customer ID column.

Schedule and refresh depth

• Transfers run on a schedule; the practical standard is once a day in the morning, after yesterday’s numbers have settled.
• The refresh window defaults to about 7 days and can be extended to roughly 30. The window matters because each run rewrites not just yesterday but the previous N days — conversions arrive with a lag and last week’s numbers are still moving.
• Historical backfill runs separately and in chunks. Long ranges take hours, sometimes days. Plan for that before promising a three-year report by tomorrow morning.
• A classic gotcha: the backfill end date is exclusive — add one day.

What lands in the dataset

The transfer creates date-partitioned tables prefixed ads_, broadly in three groups:

• Entity tables — ads_Customer, ads_Campaign, ads_AdGroup, ads_Ad: names, statuses, settings, budgets, captured as a daily snapshot.
• Stats tables — ads_CampaignBasicStats, ads_AdGroupBasicStats, ads_AdBasicStats, ads_KeywordStats and similar: impressions, clicks, cost, conversions, value.
• Segment tables — device, geo, audience and search term breakdowns, with the exact set depending on the transfer version and the campaign types in the account.

In practice: open the dataset and read the real table list and schemas before you build anything. The set varies between accounts, and copying someone else’s SQL verbatim — including from this article — is how you end up debugging a report that never matched reality.

Limitations worth knowing up front

• You cannot choose the schema. You get what arrives, including fields you will never use.
• Once a day. There is no intraday monitoring here — that stays the job of Google Ads scripts and in-platform rules.
• Not every interface report has a transfer equivalent.
• Data arrives in the account’s currency and time zone — the first thing that breaks when you merge multiple accounts.

The data model: where double counting hides

The most common beginner error in these tables is summing everything and reporting more spend than actually happened. Four rules remove most of it:

1. Do not mix levels. Campaign stats and keyword stats are different grains. Summing spend from ads_KeywordStats will not reconcile to campaign totals — some traffic is not attached to keywords at all.
2. Segmented tables duplicate money on joins. A device- or network-segmented table sums correctly on its own, but joining two segmented tables multiplies rows. Aggregate first, join second, always on key plus date.
3. Conversions and value depend on attribution and keep moving. Yesterday’s number changes for another week. That is the point of the refresh window — and if you copy data downstream, copy it with the same rewrite window.
4. Money arrives in micros. Several cost fields come multiplied by a million. Divide by 1,000,000 and normalise currency if you run more than one account.

Six queries that justify the project

1. Daily summary with margin-based ROAS

Join spend to margin data from your ERP and you get what the interface will never show: profitability by campaign in real money rather than revenue.

SELECT date, campaign_name,
       SUM(cost) / 1e6                      AS spend,
       SUM(conv_value * margin_rate)        AS gross_profit,
       SAFE_DIVIDE(SUM(conv_value * margin_rate), SUM(cost) / 1e6) AS margin_roas
FROM   ads_daily_joined
WHERE  date BETWEEN start_date AND end_date
GROUP BY date, campaign_name

How to derive the margin rate and a break-even CPA is covered in the unit economics and LTV guide.

2. Search terms quietly eating budget

Find terms whose spend over the last 30 days grew against the previous 30 with no conversions to show for it. That is a ready-made negative keyword candidate list — the same thing you otherwise assemble by eye.

3. Lost impression share as a trend

The interface shows the current number; the warehouse shows the weekly trend and lines it up against budget changes, so you can see exactly when a campaign became budget-constrained instead of guessing “recently”. How to read those metrics: impression share and auction insights.

4. Cohorts by first-click date

Join clicks to CRM records on the click identifier and measure what each weekly cohort produced at 30, 60 and 90 days. It is the only honest way to compare channels with different sales cycles — and it only works if the identifier survives the journey, which is covered in GCLID, GBRAID and WBRAID tracking.

5. Alerts on conditions the interface does not have

SELECT campaign_id, campaign_name, spend_today, median_28d
FROM   spend_stats
WHERE  spend_today > median_28d * 2
   AND conversions_today = 0

Schedule it and pipe the output into your messenger. This does not replace automated rules — it complements them with conditions the interface cannot express.

6. The baseline for seasonality percentages

Comparing conversion rate during past promotions against a calm baseline produces exactly the number you need for seasonality adjustments. Calculated once, reused every season.

What changes once Google Ads to BigQuery is running

Technically the outcome is boring: some tables appear. Practically four things change, and they are the reason the project is worth doing.

• “Preparing the report” disappears. A question from a client or a director stops being half a day of work — the slice is a query away or already sitting in a dashboard.
• The account gains memory. “What did we do last November” turns from recollection into a number, and stops depending on whoever ran the account six months ago.
• Metrics become shared. Margin ROAS computed in the warehouse reads the same for the buyer, the analyst and finance, which ends the argument about whose report is right.
• Anomalies surface earlier. A daily query across all accounts catches on day one what people notice on day three: an account at zero spend, a campaign with doubled CPC, a product category that dropped out of the feed.

What it does not do is improve campaigns or make decisions. The warehouse answers “what happened”; “what to do about it” stays with you and with in-platform tools such as Google Ads experiments.

Incremental rebuilds: stop recomputing everything daily

The first version of a warehouse usually rebuilds in full — drop the table, recreate it. That is fine on a month of data and slow and expensive on three years. Moving to incremental looks like this:

1. Partition the aggregate by date, the same date the source tables use.
2. Each run rewrites only the hot tail — the last N days, where N matches the transfer’s refresh window. Older partitions are untouched.
3. Replace partitions atomically: compute the new set, then swap, so dashboards never read a half-written table.
4. Snapshot entity tables separately. Otherwise a campaign rename rewrites your history retroactively.
5. Log every run — date, row count, runtime. Without that log you will not notice the transfer silently failing on a Thursday.

One habit that saves hours: keep one SQL file per aggregate in version control rather than a scatter of queries typed into dashboard editors. Logic that lives inside a dashboard is logic nobody can review or reuse.

What it costs

The Google Ads transfer itself is not billed separately — you pay for storage and for queries. Orders of magnitude (indicative; check current Google Cloud pricing for exact figures):

• Storage — cents per gigabyte per month, and partitions untouched for 90 days move to long-term storage at roughly half the price. A year of Google Ads data for one mid-sized account is usually a few gigabytes.
• Queries — billed on bytes scanned, by the terabyte, with a monthly free allowance that a disciplined small team stays inside.
• The usual source of a surprise bill — SELECT * across an unfiltered range inside a dashboard that fifteen people refresh every five minutes.

Three hygiene rules keep the bill sane: always filter on the partition column, never select *, and point dashboards at pre-aggregated tables rather than raw ones.

Looker Studio on top

• Connect to an aggregated table, not to raw data. The difference in speed and in cost is an order of magnitude.
• Rebuild the aggregate once a day after the transfer, not on every report open.
• Do not put twenty widgets from different sources on one page — each one fires its own query.
• What actually belongs in a buyer’s report is covered in media buyer dashboards and reporting.

Seven implementation mistakes

1. Starting with the perfect data model. Three months of design and nobody uses it. Start with the one report that hurts today.
2. Leaving the refresh window at default. Seven days does not cover a long conversion lag, and your warehouse quietly under-reports conversions.
3. Forgetting the micros division. A day-one classic.
4. Mixing currencies and time zones. Normalise at the warehouse layer, not in the dashboard.
5. Duplicating business logic. The same metric computed three different ways in three dashboards destroys trust faster than having no dashboards at all.
6. Not snapshotting entity tables. Campaign names change; without snapshots your history gets rewritten retroactively.
7. Ignoring access control. Who can read a dataset holding every client’s numbers is the same class of question as ad account permissions — see Google Ads account access security.

A two-week rollout plan

Days	What you do
1–2	Cloud project, billing, dataset, permissions. Create the transfer on the manager account with a daily schedule.
3–4	Kick off a 13–24 month backfill. While it runs, write down the three reports the business actually needs.
5–7	Reconcile totals against the interface for 7 and 30 days. Anything above a 1–2% gap gets investigated before you build further.
8–10	Build the first aggregate: day × account × campaign plus your business metrics.
11–12	Connect Looker Studio to the aggregate and ship one working report.
13–14	Schedule the daily rebuild and one alert. Document where every number comes from.

The reconciliation step is not optional. A warehouse whose numbers do not match the interface is worse than none, because people make decisions on it. Same principle as an account audit: trust the data first, draw conclusions second.

If your first-party data is not flowing into the ad platform yet, the warehouse solves only half the problem — you will be able to measure but not to optimise on it. The other half is in Data Manager and first-party data. And the scenario agencies build warehouses for in the first place — consolidated reporting across dozens of accounts — is covered in the guide to the Google Ads manager account.

If you would rather work through this on your own accounts, PPC Rebels covers the setup as part of its Google Ads services and training, and the account-side patterns are in the agency account guide.

A warehouse does not make advertising better by itself. It does something else: it ends the argument about numbers. Once everyone reads the same source with reproducible logic, the conversation moves from “my report says something different” to actual decisions — and that alone repays two weeks of setup.

FAQ: Google Ads and BigQuery

Do I need a paid Google Cloud account?

You need a project with billing enabled. Small volumes often fit inside the monthly free allowances for storage and queries, but a payment method has to be attached regardless.

Can I export every account under a manager account at once?

Yes. Point the transfer at the manager account ID and every linked sub-account lands in one dataset with a customer ID column. This is the standard agency setup.

How fresh is the data?

The transfer runs on a schedule, in practice once a day. Intraday analytics is not what this mechanism is for.

What is the refresh window and why extend it?

It is the number of past days rewritten on every run — about 7 by default, extendable to roughly 30. Extend it if your conversions take longer than a week to arrive, otherwise the warehouse permanently under-counts them.

How far back can I load history?

Backfill runs separately and in practice reaches back several years, but it processes in chunks and takes time — hours to days. Budget for it in the plan.

Why do BigQuery numbers not match the interface?

Three usual suspects: time zone, money in micros, and a different attribution model or window. The fourth is comparing recent days that are still being written.

Can I just use the Looker Studio connector without BigQuery?

For small volumes, yes. It breaks down on long history, many accounts, and joins with external data, where reports slow to a crawl and hit source-side limits.

What will this cost per month?

For one team with disciplined queries, usually a modest amount comparable to a single SaaS subscription. It becomes expensive when dashboards scan raw tables in full on every refresh.

Do I need an analyst, or can a buyer handle it?

Setting up the transfer and writing the first queries is a confident-SQL-user task. Designing aggregates, incremental rebuilds and cost control is a separate skill — but you do not need it to start.

What about data from other platforms?

Load it into the same dataset and normalise it to a common shape: date, account, campaign, spend, conversions, value. The value of a warehouse is the common denominator, not the fact that Google Ads data sits in the cloud.

Does the warehouse survive losing account access?

Yes — the history stays in your own project. That is an underrated reason to set the export up before you need it rather than after.

Where should I start with limited resources?

Native transfer on one account, a 13-month backfill, and the single report you currently assemble by hand. Everything else can be added as real questions appear.