BigQuery for Marketers

Why marketers end up in BigQuery

Every marketing platform ships a UI: GA4's reports, Looker Studio dashboards, ad platform interfaces. Each is fine in isolation, but every real question a marketer eventually asks — "what's the revenue from users who saw a paid social ad, then converted through organic search two weeks later?" — requires joining data no single UI holds.

BigQuery is Google's serverless, columnar data warehouse. It's the place GA4 exports its raw, unsampled event data for free, and it's where most MarTech stacks eventually centralize ad spend, CRM, and product data so they can be joined with SQL. You don't need to be a data engineer to use it productively — you need to understand its query model and the shape GA4 hands you data in.

The GA4 export schema, in practice

GA4's BigQuery export writes one table per day: events_YYYYMMDD (and events_intraday_YYYYMMDD for the current day, streamed in near real time). Every row is a single event. The columns you'll touch constantly:

- event_name — the event, e.g. page_view, purchase, session_start
- event_timestamp — microseconds since epoch
- user_pseudo_id — the client ID (GA4's device-scoped identifier)
- event_params — a repeated RECORD (an array of key-value structs) holding every custom parameter attached to the event
- items — a repeated RECORD for ecommerce line items
- device, geo, traffic_source — nested STRUCTs with sub-fields like device.category or traffic_source.source

The reason event_params is an array instead of flat columns is that every event type carries a different, variable set of parameters. Flattening that into one wide table would mean thousands of mostly-NULL columns. The nested/repeated design keeps the schema compact — at the cost of needing UNNEST to read it.

This is GoogleSQL, not standard SQL

BigQuery's dialect (GoogleSQL) is close to ANSI SQL but has extensions for nested and repeated fields — UNNEST, STRUCT, ARRAY_AGG — that don't exist in most other warehouses. If you already know Postgres or MySQL, 90% of your SQL transfers directly; the nested-field handling is the part you have to learn specifically for GA4 data.

unnest_event_params.sql

sql

-- Extract a single custom parameter from every event_params array
SELECT
  event_name,
  user_pseudo_id,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_url,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'value') AS event_value
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
  AND event_name = 'page_view'
LIMIT 100

Wildcard tables and cost control

The events_* wildcard with a _TABLE_SUFFIX filter (as above) is how you query a date range without scanning every table GA4 has ever created. This matters because BigQuery's on-demand pricing charges per byte scanned, not per query run. A query against events_* with no _TABLE_SUFFIX filter will scan your entire event history — potentially terabytes — every single time it runs.

Two habits keep this cheap: always filter _TABLE_SUFFIX (or better, use a partitioned/clustered custom table if you're querying the same range repeatedly), and use the query validator in the BigQuery console — it shows bytes-to-be-scanned before you run anything, so you catch a runaway query before it costs money.

SELECT * is expensive in a columnar warehouse

BigQuery only reads the columns you SELECT — it's columnar storage, not row storage. SELECT event_name FROM events_* is nearly free compared to SELECT * FROM events_*, because the latter forces a full read of every nested field across every row, including item arrays and params you don't need. Always select only the columns you actually use.

Building a session-level attribution table

The most common marketer request is: revenue attributed to each traffic source. GA4's own UI does this with a default attribution model, but it's a black box. In BigQuery you build it explicitly, which means you control the attribution window and model instead of trusting Google's default.

attribution_by_source.sql

sql

WITH purchases AS (
  SELECT
    user_pseudo_id,
    event_timestamp,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'transaction_id') AS transaction_id,
    (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value') AS revenue,
    traffic_source.source AS source,
    traffic_source.medium AS medium
  FROM `project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
    AND event_name = 'purchase'
)
SELECT
  source,
  medium,
  COUNT(DISTINCT transaction_id) AS orders,
  ROUND(SUM(revenue), 2) AS revenue
FROM purchases
GROUP BY source, medium
ORDER BY revenue DESC

Joining GA4 with ad spend for true ROAS

GA4's UI can show you conversions by source/medium, but not blended ROAS against actual ad spend — that number lives in Google Ads, Meta Ads Manager, and every other platform separately. The standard pattern is loading each platform's cost export (via BigQuery Data Transfer Service for Google Ads, or a connector like Fivetran/Airbyte for Meta) into its own dataset, then joining on date and campaign in a single query.

This is the query that actually earns BigQuery its keep for a marketing team: nobody's ad platform will ever show you cost next to GA4 revenue in the same row. SQL is what makes that join possible.

blended_roas.sql

sql

SELECT
  g.campaign_id,
  a.campaign_name,
  SUM(a.cost_micros) / 1e6 AS spend,
  SUM(g.revenue) AS revenue,
  SAFE_DIVIDE(SUM(g.revenue), SUM(a.cost_micros) / 1e6) AS roas
FROM ga4_revenue_by_campaign g
JOIN `project.google_ads_export.campaign_stats` a
  ON g.campaign_id = a.campaign_id AND g.date = a.date
GROUP BY g.campaign_id, a.campaign_name
ORDER BY roas DESC

SAFE_DIVIDE avoids the divide-by-zero crash

Marketing data is full of zero-spend or zero-revenue rows — a campaign that ran with no conversions, a day with no spend. Standard division throws an error on a zero denominator; SAFE_DIVIDE() returns NULL instead of crashing the whole query. It's a small function that saves a lot of debugging in reporting SQL specifically.

Scheduled queries: turning SQL into a pipeline

A query you run once is an analysis. A query that runs daily and writes its output to a destination table is a pipeline — and that's what feeds Looker Studio dashboards or a data warehouse table your CRM can read from. BigQuery's Scheduled Queries feature runs any saved query on a cron-like schedule and appends or overwrites results into a target table, with no external orchestration tool required.

The typical marketer pattern: write the attribution or LTV query once, schedule it to run every morning against yesterday's completed data, and point Looker Studio at the resulting summary table instead of the raw events_* wildcard. This keeps dashboards fast (querying a small, pre-aggregated table instead of scanning raw events on every dashboard load) and keeps costs predictable.

What's next

BigQuery gives you the warehouse; GA4 gives you the event stream. The next MarTech skill is designing what those events actually look like before they ever reach BigQuery — a consistent, documented event taxonomy is what makes every query in this lesson possible instead of painful.

Next: Event Taxonomy Design →