GA4 + BigQuery export for agencies: the setup that survives ad-platform churn

TL;DR

GA4's free BigQuery export gives every agency client a row-level event log that doesn't depend on the GA4 UI continuing to expose the metrics you need. For clients spending >$10K/mo on paid, it's the foundation of attribution that survives platform reshapes, sampling thresholds, and the GA4-UI's quarterly habit of moving reports around. The setup that works: (1) link GA4 property → free BigQuery sandbox project, (2) flip on the streaming export (not just daily), (3) build the canonical events_unified view that joins your platforms by event_id, (4) materialise the 4 reports the agency reviews weekly. Skip any of these and the export is a dead $0/mo line item that nobody checks. Below: each step, plus the four pitfalls that make most agency BigQuery setups useless inside 6 months.

---

The pitch every BigQuery vendor uses is "own your data." That's true but vague. The concrete reason an agency wants the GA4 → BigQuery export is more specific: the GA4 UI is the most unstable analytical surface a client interacts with. Reports get removed, conversion definitions get re-shaped, the "value" column silently changes scope, the "key events" rename of mid-2024 broke half the conversion dashboards in our portfolio. None of that propagates to the BigQuery tables. The events table is the event log. It doesn't change because Google's PM team had a roadmap meeting.

This is the setup we ship for agency clients above the spend threshold where the lift pays for the maintenance. Below that threshold, the GA4 UI is fine — don't add a moving part.

Why agencies specifically benefit

Three reasons the agency case differs from the in-house case:

1. Cross-client comparability. An in-house team builds one dashboard for one product. An agency wants to compare conversion rates / CAC / time-to-first-purchase across 30 clients on one axis. GA4's UI doesn't do that — every client property is a silo. BigQuery, with one canonical schema, does.
2. Survival across handoffs. When a client onboards or churns, their GA4 UI access changes, the property gets renamed, custom dimensions get re-numbered. The BigQuery export from THE PERIOD you ran the account is intact regardless. Quarterly reviews three years later still work.
3. Independence from ad-platform attribution. Meta says the campaign drove $X. GA4 says it drove $0.6X. The CRM says it drove $1.4X. You need a referee. BigQuery, joined to your first-party order database, IS the referee.

For ad-spend below ~$10K/mo per client, those benefits don't pay for the setup time. Above $10K/mo, they're load-bearing.

The setup that works

Five steps; the first three are config, the last two are where agencies actually get value.

Step 1: Link GA4 → BigQuery (free tier)

GA4 → Admin → Product Linking → BigQuery Links → Link. Pick a Google Cloud project (create one if the agency doesn't have a shared sandbox yet). Pick a region close to the client's user base — US for North American B2C, EU for European, asia- northeast1 for Japan/Korea/SEA. Region matters because cross- region BigQuery queries cost more and are slower.

Free tier: GA4 sends up to 1 million events/day per property to BigQuery at zero cost. Above that, the property's BigQuery export falls back to the standard tier and you pay. Most agency clients sit comfortably under 1M events/day; the few that don't are your largest accounts where the per-month cost ($10–50/mo per property at typical volumes) is invisible against ad spend.

Step 2: Enable streaming export (not just daily)

In the same BigQuery Links config, the daily export is the default. Also turn on the streaming export. The daily export lands ~24h after midnight in the property's timezone — useless for "did the campaign launch I shipped this morning fire?" The streaming export lands events in BigQuery within a few seconds of the GA4 hit.

Cost: streaming export charges per-event ($0.05 per million events streamed). At a typical agency client's volume (50K-300K events/day), that's $0.00 to $0.50/day per property. Still rounding error against ad spend.

The two exports produce different table prefixes:

• events_YYYYMMDD — daily, settled
• events_intraday_YYYYMMDD — streaming, in-flight (today's data)

Real reports query a UNION of both so the dashboard is current through "5 minutes ago" without losing yesterday's settled numbers.

Step 3: Build the events_unified canonical view

Every cross-client report depends on a canonical event schema. Build it once, reference everywhere:

CREATE OR REPLACE VIEW `phloz_agency.events_unified` AS
WITH all_events AS (
  -- Settled events (yesterday and earlier)
  SELECT
    event_date,
    event_timestamp,
    event_name,
    event_params,
    user_pseudo_id,
    user_id,
    geo,
    device,
    traffic_source,
    -- One canonical client identifier so cross-client queries work
    'client-acme' AS phloz_client_id
  FROM `acme-ga4-export.analytics_<property_id>.events_*`
  WHERE _TABLE_SUFFIX BETWEEN
    FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
    AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
  UNION ALL
  -- Streaming events (today)
  SELECT
    event_date,
    event_timestamp,
    event_name,
    event_params,
    user_pseudo_id,
    user_id,
    geo,
    device,
    traffic_source,
    'client-acme' AS phloz_client_id
  FROM `acme-ga4-export.analytics_<property_id>.events_intraday_*`
  -- ... repeat the UNION ALL block per client property ...
)
SELECT * FROM all_events;

The view is the agency's "all events across all clients" table. Every per-client report is a WHERE phloz_client_id = ... filter on this view. Cross-client reports group by phloz_client_id.

When a new client onboards, you add a UNION ALL block. When a client churns, you remove it (or comment it out for one quarter in case they come back).

Step 4: Materialise the four agency-shaped reports

Don't run reports against the raw view every time — query cost adds up at scale. Materialise four scheduled queries per agency that run nightly and overwrite small report tables:

1. reports.conversion_rate_by_client_30d — last 30d conversions / sessions per client. The cross-client comparison table the agency principal looks at on Monday morning.
2. reports.cac_by_client_30d — joined to the agency's ad- spend table (which you maintain in the same BigQuery dataset from the Ad Platform Reporting API exports). CAC by client and by acquisition channel.
3. reports.attribution_diff_by_client_30d — for each conversion event, the count from GA4 vs the count claimed by each ad platform's API. The difference is the "platform exaggeration" factor and is what the quarterly review uses to recalibrate budget allocation.
4. reports.user_first_order_funnel_30d — for clients with a first-purchase event, the funnel from page_view → view_item → add_to_cart → begin_checkout → purchase. Identifies leaks at the per-client level.

Each runs nightly via a Scheduled Query. The materialised tables each total a few KB to a few MB; queries against them are basically free.

Step 5: Wire the reports into the agency operating cadence

The reports only matter if they trigger action. The cadence:

• Monday morning, agency-wide: the agency principal reads conversion_rate_by_client_30d + cac_by_client_30d filtered to "delta vs 30d ago" — surfaces clients whose performance shifted week-over-week.
• Quarterly client reviews: attribution_diff_by_client_30d plus the same client's user_first_order_funnel_30d make the CFO-readable artifact ("here's where revenue actually came from versus what each ad platform claimed").
• On-demand for client RFPs: export cac_by_client_30d filtered to comparable verticals — "here's what we delivered for similar clients" is one of the strongest sales surfaces an agency has, and it's only honest if the data is real.

In Phloz, every BigQuery report we ship has a corresponding node in the tracking infrastructure map — typed bigquery_view or bigquery_scheduled_query with the SQL stored as the node's metadata. Quarterly tracking-map audits flag any report whose last-success run is >7 days stale (e.g. credentials rotated, the GA4 export got re-linked, the view's underlying property got renamed).

The four pitfalls

These are the bugs we see at agency-takeover audits when a previous agency set up GA4 → BigQuery and never made it useful:

1. Daily export only — no streaming. "I want to know how the morning launch is going" turns into a 24-hour wait. Most agency teams stop checking the BigQuery reports within a quarter because they're always behind. Fix: turn on streaming.
2. Per-client BigQuery datasets, no canonical view. Each client's events live in their own dataset, queries are written per-client, cross-client comparison is a six-step copy-paste. The agency stops doing it within two months. Fix: the events_unified view in step 3 above.
3. Reports run on every dashboard load. The agency BI dashboard queries the raw events table directly. By month 3, the BigQuery bill is $200/mo per client and someone proposes turning the export off. Fix: scheduled queries that materialise to small tables; dashboards read the materialised tables.
4. No event-id contract with the ad platforms. GA4 sends an event_id parameter on conversion events that should match the event_id your CAPI / Conversions API calls send. Without matching event_ids, the attribution_diff report has no signal — it just shows two big numbers from different counting universes. Fix: the same event_id discipline covered in the Meta CAPI and LinkedIn Conversions API posts. Your GA4 conversion event must carry the same event_id you send to each ad platform's server-side API.

Where this fits in an agency's tracking discipline

The GA4 + BigQuery export is the first-party data layer below the dual-stack platform-tracking covered in the rest of the tracking series (Meta, TikTok, LinkedIn, Pinterest). Each ad platform's pixel + API gives you the platform's view of attribution. BigQuery gives you the independent view that lets you grade those platforms against each other.

For agency-managed accounts under $10K/mo per client, the platform- side dual-stack is enough — the BigQuery layer is overhead that doesn't pay back. Above $10K/mo, the BigQuery layer is what separates "we run ads" from "we own the measurement and bid discipline." That distinction is the difference between a commodity media-buying retainer and a 3-year strategic engagement, which is the relationship every agency principal actually wants.

If you've never set up the BigQuery export and you have a client above the threshold: book a half-day this quarter, follow steps 1–3 above for that client only, and let the reports (conversion_rate_by_client_30d, attribution_diff_by_client_30d) run for a month. Step 5's cadence — Monday morning numbers, quarterly review artifacts — is what makes it stick. The BigQuery export on its own is just a free $0/mo line item nobody opens.