Do You Actually Need BigQuery for Attribution?

Some version of this sentence reaches me on scoping calls most months: "our agency says we need a data warehouse before they can tell us what our ads actually produced." My answer is usually no: for one location and one ad account, a flat conversion log plus a scheduled upload job answers the same question, and it answers it this month rather than next quarter. What follows is the scoring sheet I use, run twice against two invented practices on opposite sides of it.

The verdict: no, unless one of four things is true

BigQuery is not what makes attribution work. What makes attribution work is a click id captured cleanly, an outcome the practice believes, and an upload that lands inside the window. A warehouse is where you keep those three things when they arrive from more than one system, in more than one shape, at more than one time. Without that problem it buys you a second copy of the truth and a permanent job maintaining it. So the default is no until one of four things is true, and note what is not on the list: not a spend level, not a row count, not a number of connected sources.

If the goal is to tie ad spend to real revenue, the warehouse is optional and the click-to-arrival loop itself is not. Here is the gate as I walk it on a call, in a different order than the numbering above.

Question 2 comes last on a call because its yes is the only free one.

What does a warehouse actually add that a scheduled job and a flat log do not?

Reconciliation, re-derivation, and a read surface for somebody who is not you, which are questions 3, 1 and 4 below. What it does not add is bidding performance. Google Ads never sees your warehouse, only the conversions you upload, so bidding improves when what you send gets more accurate, not when it gets better stored.

Question 1: does more than one location or brand feed a single ad account?

It sounds like a count, but the threshold is not a count: it flips to yes only when the location fails to survive onto the conversion row at capture. If the booking widget stamps a location code on every row it writes and the EHR exposes that field in the scheduled export, eight locations stay comfortably on a flat log, especially when each brand runs its own ad account. The join was done upstream, by systems you already own.

Now invert it. Two locations sharing one booking widget that never records which site the appointment was made for is already past the threshold, at two. The dimension was never on the row, so answering "which office earned this conversion" means reopening a system that has been rewriting appointment records ever since.

That is why "how many locations before BigQuery pays for itself" has no numeric answer. A count cannot tell you whether the dimension survived onto the row.

Question 2: do you need answers from past your retention walls?

It gets misread in both directions, so here are the walls with numbers.

Read the first row against the last one. Past 90 days you cannot push a correction into Google Ads however much history you own, so this threshold buys reporting, never bidding. That is why a yes resolves to a free action instead of a build: link the export, take the daily option, leave it alone. What the upload should look like is decided by the Data Manager API upload path, not by your storage layer.

Question 3: does anything have to be joined before the upload leaves?

This is the question that actually builds warehouses, and it flips on two conditions.

The first is systems that disagree about identity. The booking system knows an appointment id, the phone system knows a call id, billing knows an account number, and no two share a key. Somebody has to decide in code that those three rows are one visit, reproducibly, next month too.

The second is a system that mutates the row in place after you read it. Status moves from booked to attended to rescheduled, and the record you uploaded from is no longer the record that exists. A flat log records what you saw. It cannot tell you what changed under it.

If one system hands you a click id and an outcome on the same row you do not have this problem, and most of what an offline conversion tracking build does is keep that true. The join is the expensive part. The upload never was.

Question 4: does a second person need to query this without touching the pipeline?

The threshold is specific, because the vague version approves every project: someone who did not build the pipeline must answer a new question, in under a day, more than twice a quarter.

Under that bar you are a report; over it you are a bottleneck with a laptop and a personal schedule, and a view on a warehouse is how a marketing lead answers her own question at 4pm on a Thursday.

This is the threshold people fail upward on. "It would be nice if the team could look" is not the same sentence as "the team is blocked twice a quarter waiting on me."

Run the sheet: two practices, the same four questions, opposite answers

Practice A is one location, one ad account, one booking widget, one EHR. Questions 1, 3 and 4 are all no: the row cannot be ambiguous, the booking record carries the click id and the outcome together, and nobody but the owner has wanted to query anything. Question 2 is a weak yes, a year-over-year view someday, which buys her the export and nothing else. Verdict: flat log, scheduled upload, linked export costing nothing.

Practice B is eight locations under two brands, three ad accounts, a shared central booking line, and a CRM the front desk edits by hand. Question 1 is yes: the shared line writes bookings with no location code. Question 3 is yes twice over, because the call platform and the CRM disagree about who a person is and the CRM rewrites status after the nightly read. Question 4 is yes, because a marketing lead needs show rate by location on demand. Verdict: build, and build it small.

Same four questions, and Practice B is messier in identity rather than bigger in dollars, which is the only variable this sheet measures.

What does it cost to be wrong in each direction?

Both profiles at month 24 of running the pipeline.

Illustrative table, synthetic figures, proportions typical. The list prices behind the arithmetic are Google's published rates, re-checked on September 6, 2026: the first 10 GiB of storage and the first 1 TiB of query processing each month are free, on-demand querying is $6.25 per TiB after that, and active logical storage is $0.02 per GiB per month ($20.48 per TiB) in us-central1, halving to $0.01 per GiB once a partition has sat 90 days unmodified. The right-hand storage line is therefore 21 GiB active above the free tier plus 65 GiB long-term, and the query line 1.4 billable TiB.

The build costs about $41 a month and somewhere between 10 and 16 hours a month, and only one of those numbers is worth an argument. I tried to make the invoice hurt and could not: a scheduled query touching fourteen tables every five minutes, each billed at BigQuery's 10 MB per-table minimum whether it reads 10 MB or 40 KB, comes to about 1.1 TiB a month, roughly 80 cents after the free terabyte. Every page that tells you BigQuery is basically free is telling the truth and proving nothing. I give the second row in hours because I quote this work at a fixed price, so every hour of infrastructure comes out of my own margin. That is the incentive behind my default answer, and you should weigh it accordingly.

One detail worth carrying into the build: the transfer that bills is your CRM, not your ad platform. Google's own connectors (Google Ads, GA4, Campaign Manager, Search Ads 360, Display and Video 360, Merchant Center) load at no charge. Third-party SaaS and database connectors moved to consumption pricing on September 25, 2025, billed in slot-hours for orchestrating the run and for loading into the destination table.

Being wrong toward the flat log has a price too, and it is always the same price. A log that never stamped location at capture cannot answer which office earned a conversion once the EHR has rewritten the appointment records underneath it, and re-deriving that dimension after the fact costs more than the warehouse would have. Nothing breaks while that is happening, which is what makes it easy to miss: the upload loop keeps running, in the same click-id-only shape as the offline loop I build for practices. The log underneath simply cannot answer a question nobody asked it at design time, and that is why question 1 is question 1.

If you do build it, what is the smallest shape that works?

Two datasets and three objects: two raw append-only landing tables and one view. No materialized mart, deliberately, because that is where the swamp starts.

CREATE SCHEMA IF NOT EXISTS ops_raw    OPTIONS (location = 'us-central1');
CREATE SCHEMA IF NOT EXISTS ops_marts  OPTIONS (location = 'us-central1');
 
CREATE TABLE IF NOT EXISTS ops_raw.bookings (
  booking_id       STRING    NOT NULL,
  location_code    STRING    NOT NULL,
  click_id         STRING,
  click_id_type    STRING,              -- gclid, gbraid or wbraid
  booked_at        TIMESTAMP NOT NULL,
  appointment_at   TIMESTAMP,
  outcome          STRING,              -- attended, no_show, cancelled, pending
  outcome_at       TIMESTAMP,
  source_row_hash  STRING    NOT NULL,
  loaded_at        TIMESTAMP NOT NULL
)
PARTITION BY DATE(booked_at)
CLUSTER BY location_code, outcome;
 
CREATE TABLE IF NOT EXISTS ops_raw.calls (
  call_id          STRING    NOT NULL,
  location_code    STRING    NOT NULL,
  click_id         STRING,
  click_id_type    STRING,
  booked_at        TIMESTAMP NOT NULL,  -- call start
  appointment_at   TIMESTAMP,
  outcome          STRING,
  outcome_at       TIMESTAMP,
  source_row_hash  STRING    NOT NULL,
  loaded_at        TIMESTAMP NOT NULL
)
PARTITION BY DATE(booked_at)
CLUSTER BY location_code, outcome;

Partition the log even though it is small, and not for the reason you think. Querying a table does not reset the 90 day long-term storage clock, but loading, DML and streaming into it do. An unpartitioned append-only log is written to every day, so it never ages into the cheaper tier. A partitioned one ages partition by partition, which is where two thirds of the storage line above comes from.

CREATE OR REPLACE VIEW ops_marts.conversions_to_upload AS
SELECT
  b.click_id,
  b.click_id_type,
  b.location_code,
  b.outcome_at AS conversion_time,
  TO_HEX(SHA256(CONCAT(b.booking_id, '|', b.click_id))) AS dedupe_key
FROM ops_raw.bookings AS b
WHERE b.outcome = 'attended'
  AND b.click_id IS NOT NULL
  AND b.outcome_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 88 DAY)
  AND DATE(b.booked_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 120 DAY);

Two guards there are worth copying. The window is 88 days rather than 90, so a run that failed overnight still has two days of slack against a wall that will not forgive you. And the predicate on booked_at makes the partition prune instead of scanning the whole table on every run. The dedupe key is a deterministic hash of booking id plus click id, because a generated row id does not survive a re-run, and a re-run is the normal case after a failed job, not the exception.

One posture note, stated as engineering rather than legal advice, and HIPAA-conscious rather than a compliance claim. BigQuery is on Google Cloud's list of products covered by the Google Cloud BAA. GA4 and Google Ads are not, and Google states plainly that there is no HHS-recognized HIPAA certification. The warehouse is therefore the one place in this stack where regulated data can legitimately sit, provided the practice signs the BAA and configures it. Google's guidance is to keep protected health information out of metadata, and dataset and column names are metadata, which is why the dataset above is ops_raw and nothing patient-shaped. None of that changes what leaves the building: only an anonymous click id, a value and a timestamp ever cross the line, and that boundary lives in the upload rather than the storage, which is the part of the marketing analytics work underneath it people most often get backwards.

Link the GA4 property to BigQuery today, take the daily export, then close the tab and build nothing else. The export does not backfill, so whatever it missed before today is gone permanently, and a table nobody queries sits in the free tier costing nothing. Keep the option, skip the project. Then set yourself a trigger rather than a deadline: the day one of those four questions changes its answer is the day this becomes a build, and not before.