GA4 BigQuery Schema Explained:
A Practical Reference

The subquery SELECT pattern is the most common in all GA4 BigQuery work. Use it wherever you need a single parameter value from event_params.

-- Extract page_location from event_params
SELECT
  event_name,
  (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 = 'ga_session_id')  AS session_id

FROM `project.analytics_ID.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d',
  DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
LIMIT 10

Sessions are not a column — they're derived by counting distinct combinations of user_pseudo_id and ga_session_id. Counting just user_pseudo_id gives users. Counting just event rows gives events. Counting distinct session IDs gives sessions.

-- Correct session count
SELECT
  COUNT(DISTINCT
    CONCAT(
      user_pseudo_id, '.',
      (SELECT value.int_value
       FROM UNNEST(event_params)
       WHERE key = 'ga_session_id')
    )
  ) AS sessions

FROM `project.analytics_ID.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d',
  DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
-- No event_name filter needed — every session has a session_start,
-- but filtering on session_start then counting distinct IDs also works

Always filter by _TABLE_SUFFIX to control which partitioned table files are scanned. Without this, BigQuery scans every table in the dataset — expensive at scale. The suffix is the YYYYMMDD string in the table name.

-- Yesterday only
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d',
  DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

-- Last 7 days
WHERE _TABLE_SUFFIX BETWEEN
  FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
  AND
  FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

-- Specific date range
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'

-- From a specific date forward
WHERE _TABLE_SUFFIX >= '20240101'

The purchase event has both event_params and the ecommerce record. Revenue is accessible via both paths — ecommerce.purchase_revenue is simpler, but event_params contains the value parameter which is what GA4's own reports use. Both should match; if they don't, investigate your purchase event implementation.

-- Daily revenue and transaction count
SELECT
  PARSE_DATE('%Y%m%d', event_date)    AS date,
  COUNT(*)                              AS transactions,
  SUM(ecommerce.purchase_revenue)       AS revenue,

  -- Also accessible via event_params
  SUM((SELECT value.double_value
       FROM UNNEST(event_params)
       WHERE key = 'value'))            AS revenue_via_params,

  COUNT(DISTINCT
    (SELECT value.string_value
     FROM UNNEST(event_params)
     WHERE key = 'transaction_id'))    AS unique_transactions

FROM `project.analytics_ID.events_*`
WHERE _TABLE_SUFFIX >= '20240101'
  AND event_name = 'purchase'
GROUP BY date
ORDER BY date DESC

The items column is a repeated record — one entry per product in a transaction. To analyse at the item level, UNNEST items. This creates one row per item per event rather than one row per event.

-- Top products by revenue
SELECT
  item.item_id,
  item.item_name,
  item.item_category,
  SUM(item.quantity)                  AS units_sold,
  SUM(item.item_revenue)              AS revenue
FROM
  `project.analytics_ID.events_*`,
  UNNEST(items) AS item
WHERE
  _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
  AND event_name = 'purchase'
GROUP BY
  item.item_id, item.item_name, item.item_category
ORDER BY
  revenue DESC
LIMIT 50

To analyse which events occurred within each session — building a funnel — create a session-level CTE that aggregates the events that fired per session using COUNTIF or MAX with a boolean flag. This avoids the expensive cross-join approach.

-- Checkout funnel: how many sessions reached each step
WITH sessions AS (
  SELECT
    CONCAT(user_pseudo_id, '.',
      (SELECT value.int_value
       FROM UNNEST(event_params)
       WHERE key = 'ga_session_id'))      AS session_id,

    COUNTIF(event_name = 'view_item')     > 0  AS viewed_item,
    COUNTIF(event_name = 'add_to_cart')  > 0  AS added_to_cart,
    COUNTIF(event_name = 'begin_checkout') > 0  AS began_checkout,
    COUNTIF(event_name = 'purchase')      > 0  AS purchased

  FROM `project.analytics_ID.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
  GROUP BY session_id
)

SELECT
  COUNTIF(viewed_item)    AS sessions_viewed_item,
  COUNTIF(added_to_cart)  AS sessions_added_to_cart,
  COUNTIF(began_checkout) AS sessions_began_checkout,
  COUNTIF(purchased)      AS sessions_purchased
FROM sessions

traffic_source contains session-level acquisition data — source, medium, and campaign. These are top-level record fields and don't require UNNEST. Note that traffic_source reflects the first session's attribution — for cross-session attribution analysis you need a more complex approach using the collected_traffic_source event parameter.

-- Sessions and revenue by channel
SELECT
  traffic_source.source,
  traffic_source.medium,
  traffic_source.name                     AS campaign,
  COUNT(DISTINCT CONCAT(user_pseudo_id, '.',
    (SELECT value.int_value
     FROM UNNEST(event_params)
     WHERE key = 'ga_session_id')))     AS sessions,
  SUM(IF(event_name = 'purchase',
    ecommerce.purchase_revenue, 0))      AS revenue

FROM `project.analytics_ID.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY 1, 2, 3
ORDER BY sessions DESC

The ga_session_number event parameter tells you which session number this is for the user — session number 1 means a new user in GA4's terminology. This is more reliable than using user_first_touch_timestamp for segmenting new vs returning.

-- New vs returning user sessions
SELECT
  CASE
    WHEN (SELECT value.int_value
          FROM UNNEST(event_params)
          WHERE key = 'ga_session_number') = 1
    THEN 'New'
    ELSE 'Returning'
  END                                     AS user_type,
  COUNT(DISTINCT CONCAT(user_pseudo_id, '.',
    (SELECT value.int_value
     FROM UNNEST(event_params)
     WHERE key = 'ga_session_id')))     AS sessions

FROM `project.analytics_ID.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d',
  DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
  AND event_name = 'session_start'
GROUP BY user_type