PPC Rebels cover for the 2026 guide to the GA4 BigQuery export for paid traffic

GA4 BigQuery Export for Paid Traffic 2026: Limits, Schema and 6 SQL Queries

Byadmin

The GA4 interface answers questions somebody else chose for you. “How many conversions did this campaign drive?” — fine. “What share of users who arrived from paid traffic on Monday reached step two of the form and came back within 14 days?” — and now you are fighting sampling, cardinality limits and the (other) row that swallows your long tail. Questions like that are answered in raw data, which is what the GA4 BigQuery export hands you.

The GA4 BigQuery export removes that ceiling. You get raw events, one row per event, unsampled and unaggregated, and you ask your own questions. Below: export types and the limits that actually matter, the schema, six working SQL queries for paid traffic, and the cost rules that keep your BigQuery bill from becoming a surprise.

What the GA4 BigQuery export gives you that the UI cannot

QuestionGA4 interfaceBigQuery export
GranularityAggregates across dimensionsOne row per event, with parameters and user
SamplingPossible in explorations and at volumeNone
CardinalityLong tail collapses into (other)Every value preserved
RetentionBound by property settingsAs long as you keep the tables
Attribution logicGoogle’s modelWhatever you write in SQL
Joining other dataNot availableCRM, cost, feed data in one dataset

For paid media, the last row is the whole point. With Google Ads data and GA4 events in the same project, spend, clicks, leads and revenue reconcile in a single query. Setting up the other half of that pairing is covered in our guide to the Google Ads BigQuery Data Transfer.

Export types and the limits that decide your design

When you link a property to BigQuery you enable each export type independently:

  • Daily. Once a day, the previous day’s complete, unsampled events. Typically mid-afternoon in the property’s time zone, sometimes later. Available to Standard and 360 properties.
  • Fresh Daily. GA4 360 only (Normal and Large properties). Data usually lands by 5am with batched updates through the day, generally within 60 minutes. Requires Google Cloud billing to be enabled.
  • Streaming. Near real-time, usually within minutes. Best-effort, so gaps are possible. No volume cap, but new-user and new-session traffic source data is excluded from the stream.

Two tables matter:

  • events_YYYYMMDD — the complete daily export. Query this for anything that has to be stable.
  • events_intraday_YYYYMMDD — the streaming staging table for today, deleted once the daily table completes. Useful for an operational dashboard, unsuitable for reporting.

The one-million-events ceiling

Standard properties are capped at 1 million events per day for the daily export; 360 properties go up to 20 billion. This is the limit mid-sized sites run into, and it is far better to learn about it now than on the morning a table comes back truncated.

What teams actually do about it:

  1. Filter events at the export level. When configuring the link you can exclude events you do not need in the warehouse. The usual suspects are scroll, user_engagement and auto-tracked clicks on every button. On a typical site that is 40–70% of volume — treat that as a rough benchmark, since it depends on how generously enhanced measurement is configured.
  2. Stop collecting noise in the first place. The cheapest reduction is not sending junk to GA4 at all, rather than filtering it on the way out.
  3. Enable streaming as insurance. Streaming has no volume cap, so if the daily export truncates, the intraday stream is still complete. You pay for ingestion in exchange.

What it costs

The export itself is free; BigQuery charges you for storage and processing. Streaming adds $0.05 per gigabyte ingested, where a gigabyte is roughly 600,000 events — a benchmark, since it varies with event size. The free BigQuery sandbox is fine for evaluation, but anything beyond its limits is billed.

In practice, a site at a million events a day spends on the order of $50 a month on streaming plus storage and query costs. That is cheaper than most external BI stacks — provided your queries are written sensibly, which is the next section but one.

The schema, in the order you will need it

One row is one event. The part that trips everyone up: parameters live in nested arrays, so you cannot select them as columns — you need UNNEST.

FieldContentsWhat to remember
event_name, event_timestampEvent name and UTC time in microsecondsDivide by 1,000,000 before converting to a date
event_paramsArray of key + value (string/int/float/double)You must pick the type explicitly: value.string_value and so on
user_pseudo_idDevice/browser identifierNot a person — a new browser is a new id
user_idYour own identifierPresent only if you send it. The foundation of honest LTV
user_propertiesUser-scoped propertiesValue as of that event, not the current one
traffic_sourceSource of the user’s first visitNever changes afterwards. Missing in streaming for new users
collected_traffic_sourceWhat actually arrived in the URL, including gclidWhere to look when traffic_source reads “Not Available”. gbraid and wbraid are not included
session_traffic_source_last_clickLast-click source within the sessionThe closest thing to “the campaign that drove this session”
itemsArray of products on ecommerce eventsNeeds UNNEST; holds price, category, item_id
privacy_infoConsent mode signalsCookieless pings are included in the export

One detail that matters when reconciling numbers: attribution for existing users takes roughly 24 hours to settle, so attribution reporting belongs on the daily table, not the stream. If you are chasing why GA4 and Google Ads still disagree at the click level, that has its own breakdown in Google Ads vs GA4 conversion discrepancies.

Six queries worth starting from

1. Sessions and conversions by campaign

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='campaign') AS campaign,
  COUNT(DISTINCT CONCAT(user_pseudo_id, '-',
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key='ga_session_id'))) AS sessions,
  COUNTIF(event_name='purchase') AS purchases,
  SUM(CASE WHEN event_name='purchase' THEN ecommerce.purchase_revenue END) AS revenue
FROM `project.analytics_XXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261007'
  AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key='medium') = 'cpc'
GROUP BY campaign
ORDER BY revenue DESC

2. A funnel for paid traffic only

Counts people rather than events, so you can see which step paid traffic specifically drops at.

WITH paid AS (
  SELECT user_pseudo_id, event_name
  FROM `project.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261007'
    AND session_traffic_source_last_click.google_ads_campaign.campaign_name IS NOT NULL
)
SELECT
  COUNT(DISTINCT user_pseudo_id) AS visitors,
  COUNT(DISTINCT IF(event_name='view_item', user_pseudo_id, NULL)) AS viewed,
  COUNT(DISTINCT IF(event_name='begin_checkout', user_pseudo_id, NULL)) AS checkout,
  COUNT(DISTINCT IF(event_name='purchase', user_pseudo_id, NULL)) AS purchased
FROM paid

3. Duplicate transactions

The standard cause of inflated revenue: purchase firing again when someone reloads the thank-you page.

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='transaction_id') AS tx,
  COUNT(*) AS hits
FROM `project.analytics_XXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261007'
  AND event_name='purchase'
GROUP BY tx HAVING hits > 1 ORDER BY hits DESC

If duplicates show up, fix the collection rather than the report — methods are in duplicate conversions and deduplication.

4. LTV cohorts by first-touch campaign

The query that changes how paid media gets judged: what a user acquired by a given campaign is worth over 30 and 90 days, not in a single session.

WITH first_touch AS (
  SELECT user_pseudo_id,
         MIN(event_timestamp) AS first_ts,
         ANY_VALUE(traffic_source.name) AS first_campaign
  FROM `project.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260701' AND '20261007'
  GROUP BY user_pseudo_id
),
rev AS (
  SELECT user_pseudo_id, event_timestamp, ecommerce.purchase_revenue AS r
  FROM `project.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260701' AND '20261007' AND event_name='purchase'
)
SELECT f.first_campaign,
       COUNT(DISTINCT f.user_pseudo_id) AS users,
       SUM(IF(r.event_timestamp <= f.first_ts + 30*86400*1000000, r.r, 0)) AS ltv_30d,
       SUM(IF(r.event_timestamp <= f.first_ts + 90*86400*1000000, r.r, 0)) AS ltv_90d
FROM first_touch f LEFT JOIN rev r USING (user_pseudo_id)
GROUP BY f.first_campaign ORDER BY ltv_90d DESC

5. Daily paid sessions for reconciliation

SELECT PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) AS day,
       COUNT(DISTINCT CONCAT(user_pseudo_id, '-',
         (SELECT value.int_value FROM UNNEST(event_params) WHERE key='ga_session_id'))) AS paid_sessions
FROM `project.analytics_XXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260901' AND '20261007'
  AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key='medium')='cpc'
GROUP BY day ORDER BY day

When the gap against Google Ads clicks jumps on a specific date, the cause is almost always the URL layer — a dropped query string, duplicated UTMs, a stray campaign-level tracking template. That layer is dissected in parallel tracking and URL options in Google Ads.

6. Paid landing pages ranked by conversion rate

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='page_location') AS lp,
  COUNT(DISTINCT user_pseudo_id) AS users,
  COUNT(DISTINCT IF(event_name='generate_lead', user_pseudo_id, NULL)) AS leads,
  SAFE_DIVIDE(COUNT(DISTINCT IF(event_name='generate_lead', user_pseudo_id, NULL)),
              COUNT(DISTINCT user_pseudo_id)) AS cvr
FROM `project.analytics_XXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261007'
  AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key='medium')='cpc'
GROUP BY lp HAVING users > 100 ORDER BY cvr DESC

How not to burn money on queries

BigQuery bills scanned bytes, not elapsed time. Which gives a short list of rules:

  • Always constrain _TABLE_SUFFIX. A query against events_* without a date filter scans your entire history. It is the most expensive typo in GA4 analytics.
  • Never write SELECT *. Columnar storage means you pay only for the fields you select — and for all of them when you use a star.
  • Build aggregate tables. One nightly query that rolls sessions, spend, conversions and revenue up by day and campaign turns a dashboard from a thousand raw-event scans into a single small read.
  • Use the dry-run estimate. The console shows how much a query will scan before you run it. The habit of glancing at that number saves more than any optimisation.
  • Set a project quota. Cloud lets you cap processed bytes — cheap insurance against an accidental full-history scan.

Rule of thumb: if a dashboard refreshes every five minutes against raw events, you are not paying for analytics, you are paying to rescan the same data. Raw tables are for investigation; aggregates are for recurring reporting.

Six mistakes worth avoiding

  1. No streaming, no today. Teams enable daily only, then wonder why the dashboard is a day behind. Turn streaming on when you create the link.
  2. Time zones. Table names use the property’s time zone while event_timestamp is UTC. Mix them and your daily totals drift a few percent from the UI.
  3. Treating user_pseudo_id as a person. It identifies a browser. Any cross-device analysis needs user_id, which means sending your own identifier.
  4. Ignoring consent mode. Cookieless pings are in the export; if you do not account for them, some events have no user and your funnel appears to lose people. The mechanics are in Consent Mode v2 and ad privacy.
  5. Expecting backfill. The export only moves forward: data from before you created the link never appears. Which is the argument for enabling it the day you hear about it, even if you write queries six months later.
  6. Demanding an exact match with the UI. The interface applies its own attribution model and session-stitching rules. A perfect match is neither achievable nor desirable — understand the delta instead of chasing it. What the model contributes is covered in DDA vs last-click attribution models.

The aggregate table that pays for everything else

Nine dashboards out of ten answer the same handful of questions: spend, sessions, leads and revenue by day and campaign. If the dashboard rescans raw events every time it loads, you are paying for the same scan dozens of times a day. The fix is one nightly job that rolls those facts into a small table.

CREATE OR REPLACE TABLE `project.marts.paid_daily`
PARTITION BY day AS
WITH sessions AS (
  SELECT PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) AS day,
         (SELECT value.string_value FROM UNNEST(event_params) WHERE key='campaign') AS campaign,
         COUNT(DISTINCT CONCAT(user_pseudo_id,'-',
           (SELECT value.int_value FROM UNNEST(event_params) WHERE key='ga_session_id'))) AS sessions,
         COUNTIF(event_name='generate_lead') AS leads,
         SUM(CASE WHEN event_name='purchase' THEN ecommerce.purchase_revenue END) AS revenue
  FROM `project.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 400 DAY))
                          AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key='medium')='cpc'
  GROUP BY day, campaign
)
SELECT * FROM sessions

Join spend and clicks from the Google Ads transfer onto this by date and campaign, and you have a single source of truth that reads instantly and costs almost nothing. Three rules make it trustworthy:

  • Partition by date. Otherwise every “last 30 days” query scans the whole table.
  • Rebuild with a buffer. Recompute the last three to seven days, not just yesterday: attribution for existing users settles late, so the day before yesterday can still change.
  • One set of definitions. What counts as a session, a lead, revenue — those definitions live in this one SQL file, not re-implemented differently in every dashboard. Half of all “why do we have two different numbers” arguments start right here.

Joining CRM data, and why paid media needs it

GA4 knows a form was submitted. It does not know the lead qualified and closed for real money six weeks later. That join is what turns the export from an analytics toy into a budgeting instrument.

The pattern is simple: export deals from your CRM with gclid (or your own session identifier), date, stage and amount. Join that to GA4 events in BigQuery and you get two things the interface cannot give you:

  1. Traffic quality by campaign. Not CPL, but cost per qualified lead and cost per closed deal. It is common for the campaign with the worst CPL to be the best on cost per deal — and without this data it is the first one cut.
  2. Your real conversion lag. The distribution of click-to-deal time tells you which conversion window honestly reflects your sales cycle. If 40% of deals close between day 25 and day 45, a 30-day window systematically understates half your campaigns.

Practical minimum: gclid captured on the form, a nightly deal export, one join. What to capture and where parameters most often get lost is covered in parallel tracking and URL options in Google Ads.

Implementation checklist

  • GA4 → BigQuery link created, with daily and streaming both enabled.
  • Noise events excluded from the export; daily volume comfortably under 1 million on a Standard property.
  • Dataset lives in the same project as the Google Ads transfer, so joins need no federation.
  • A processed-bytes quota is set on the project.
  • One nightly job builds a “day × campaign × sessions × conversions × revenue” aggregate.
  • Dashboards read aggregates; investigation reads raw tables.
  • user_id is sent wherever it is known — logged-in areas, checkout.
  • Someone owns the weekly reconciliation against Google Ads.

If the stack is still being built while budget is already running, the order is: clean collection and link scheme first, export second, analytics on top. Reversed, you get elegant queries against bad data. Our Google Ads agency accounts cover the account side of that when scaling is the constraint, and PPC Rebels can walk the measurement setup with you.

FAQ

What does the GA4 BigQuery export cost?

The export is free. You pay BigQuery for storage and query processing, plus $0.05 per gigabyte ingested if streaming is on — roughly 600,000 events per gigabyte as a benchmark.

What is the limit on a Standard property?

One million events per day for the daily export. 360 properties go to 20 billion. Streaming has no volume cap.

What do I do when I hit the million-event ceiling?

Exclude unnecessary events when configuring the link (scroll and auto-click tracking are usually the bulk), stop collecting noise in GA4 itself, and enable streaming as a safety net.

Can I get historical data?

No. The export populates tables only from the moment the link is created. That is the main reason to enable it early.

How does events_intraday_ differ from events_?

The intraday table is the streaming stage for the current day and is deleted once the daily table is built. The daily table is the complete export and the one to use for stable reporting.

Why is traffic_source empty on some rows?

Streaming excludes source data for new users and new sessions, and attribution for existing users takes about a day to settle. Use the daily table for source analysis; gclid can be read from collected_traffic_source in the meantime.

Are gbraid and wbraid in the export?

No. gclid is included; gbraid and wbraid are not, which matters when you are stitching app and iOS traffic.

Do I need a paid Google Cloud account?

The free sandbox is enough to start, with limits on storage and features. Fresh Daily (360 only) requires billing. Any production setup effectively needs billing enabled.

How do I join GA4 and Google Ads data?

Put both exports in the same BigQuery project and join on date, campaign ID and gclid. Spend and clicks from Ads then line up with events and revenue from GA4 in one query.

Will the numbers match the GA4 interface?

Not exactly, and that is expected: the UI applies its own attribution model and session rules. Document the delta rather than trying to eliminate it.

Do I need a BI tool on top?

It helps for recurring reporting — but point it at aggregate tables, not raw events, or every dashboard refresh costs money.

Can I delete old tables?

Yes, and it is the main lever on storage cost. A practical policy: keep raw events for 13–14 months so year-over-year comparisons work, and keep aggregates indefinitely since they are a rounding error in size.

Related reading: Ask Advisor in Google Ads 2026: What the AI Agent Actually Changes and Where to Fence It In

Similar Posts