GA4 BigQuery Export for Marketers: Link It, Understand the Tables, and Run the Five Queries That Replace Most Reports

The BigQuery export is the most under-used feature in GA4. It costs nothing to turn on, keeps your data forever, removes sampling and thresholding, and makes dashboards immune to API quota errors. The barrier is that the data arrives as raw events and needs a little SQL. This guide gets you from linking to five working queries.

Step 1: Link GA4 to BigQuery

1. Create a Google Cloud project if you do not have one, with billing enabled; the free tier applies but a billing account is required.
2. In GA4 Admin, Product links, BigQuery links, click Link, choose the project and a data location close to you.
3. Enable Daily export. Add Streaming only if you need same-day data.
4. Optionally exclude events you never analyse to save storage.
5. Save. The first daily table appears the next day under a dataset named analytics_<property id>.

The Tables

Each day is a table named events_YYYYMMDD. Each row is one event with:

• event_date, event_timestamp, event_name
• event_params: an array of key and value pairs, where the value sits in one of string_value, int_value, float_value or double_value
• user_pseudo_id: the client ID; user_id if you send one
• traffic_source: the user's first source, medium and campaign
• session_traffic_source_last_click and collected_traffic_source: session-level attribution fields
• device, geo, platform
• ecommerce and items: for ecommerce events, with items as an array

Parameters live in an array, so most queries unnest them with a helper pattern:

(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id

Query 1: Sessions and Key Events by Source and Medium

SELECT
  event_date,
  session_traffic_source_last_click.manual_campaign.source AS source,
  session_traffic_source_last_click.manual_campaign.medium AS medium,
  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,
  COUNTIF(event_name = 'purchase') AS purchases
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY))
                        AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY 1, 2, 3
ORDER BY sessions DESC;

If session_traffic_source_last_click is not populated in your export, use collected_traffic_source or derive source from the source and medium event parameters on the session start event.

Query 2: Landing Page Performance

WITH first_page AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
    event_timestamp,
    ROW_NUMBER() OVER (PARTITION BY user_pseudo_id,
      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
      ORDER BY event_timestamp) AS rn
  FROM `your-project.analytics_123456789.events_*`
  WHERE event_name = 'page_view'
    AND _TABLE_SUFFIX BETWEEN '20261001' AND '20261028'
),
conversions AS (
  SELECT DISTINCT user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id
  FROM `your-project.analytics_123456789.events_*`
  WHERE event_name = 'generate_lead'
    AND _TABLE_SUFFIX BETWEEN '20261001' AND '20261028'
)
SELECT
  REGEXP_REPLACE(fp.page_location, r'\?.*$', '') AS landing_page,
  COUNT(*) AS sessions,
  COUNT(c.session_id) AS lead_sessions,
  SAFE_DIVIDE(COUNT(c.session_id), COUNT(*)) AS lead_rate
FROM first_page fp
LEFT JOIN conversions c USING (user_pseudo_id, session_id)
WHERE fp.rn = 1
GROUP BY 1
ORDER BY sessions DESC
LIMIT 50;

Query 3: Purchases With Items

SELECT
  event_date,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'transaction_id') AS transaction_id,
  ecommerce.purchase_revenue AS revenue,
  item.item_id,
  item.item_name,
  item.quantity,
  item.price
FROM `your-project.analytics_123456789.events_*`,
UNNEST(items) AS item
WHERE event_name = 'purchase'
  AND _TABLE_SUFFIX BETWEEN '20261001' AND '20261028'
ORDER BY event_date DESC;

Query 4: Key Events by Campaign With Cost Join

Export Google Ads cost to a table, for example through the Google Ads data transfer or a scheduled sheet, then:

WITH ga AS (
  SELECT event_date,
    session_traffic_source_last_click.manual_campaign.campaign_name AS campaign,
    COUNTIF(event_name = 'generate_lead') AS leads
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261028'
  GROUP BY 1, 2
)
SELECT ga.event_date, ga.campaign, ga.leads, cost.cost,
  SAFE_DIVIDE(cost.cost, ga.leads) AS cost_per_lead
FROM ga
LEFT JOIN `your-project.ads.daily_campaign_cost` cost
  ON cost.date = PARSE_DATE('%Y%m%d', ga.event_date) AND cost.campaign_name = ga.campaign
ORDER BY ga.event_date DESC;

Query 5: A Daily Summary Table for Looker Studio

Schedule this to run every morning and write to a table; connect Looker Studio to the result.

CREATE OR REPLACE TABLE `your-project.reporting.daily_summary` AS
SELECT
  PARSE_DATE('%Y%m%d', event_date) AS date,
  session_traffic_source_last_click.manual_campaign.source AS source,
  session_traffic_source_last_click.manual_campaign.medium AS medium,
  session_traffic_source_last_click.manual_campaign.campaign_name AS campaign,
  COUNT(DISTINCT user_pseudo_id) AS users,
  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,
  COUNTIF(event_name = 'purchase') AS purchases,
  SUM(IF(event_name = 'purchase', ecommerce.purchase_revenue, 0)) AS revenue
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 400 DAY))
GROUP BY 1, 2, 3, 4;

Cost Control

• Always filter _TABLE_SUFFIX to the dates you need; querying events_* without it scans everything.
• Select only the columns you use.
• Use scheduled summary tables for dashboards instead of querying raw events on every view.
• Set a project-level daily query cost limit in BigQuery settings.

Outcome

Permanent, unsampled, unthresholded data, dashboards that never hit a quota, and the ability to join website behaviour with orders and leads. Five queries cover most of what a marketing team asks; everything else is a variation.