BigQuery GA4 Export Schema Reference & Production SQL Query Library

Reconstructs web sessions from raw unthresholded event tables, unnesting ga_session_id and calculating session duration.

-- Reconstruct user sessions and map first touch attribution
WITH session_events AS (
  SELECT
    user_pseudo_id,
    event_date,
    event_timestamp,
    (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,
    traffic_source.source AS source,
    traffic_source.medium AS medium,
    event_name,
    event_value_in_usd
  FROM
    `your-gcp-project.analytics_123456789.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20260801' AND '20260822'
)
SELECT
  user_pseudo_id,
  session_id,
  MIN(TIMESTAMP_MICROS(event_timestamp)) AS session_start,
  MAX(TIMESTAMP_MICROS(event_timestamp)) AS session_end,
  TIMESTAMP_DIFF(MAX(TIMESTAMP_MICROS(event_timestamp)), MIN(TIMESTAMP_MICROS(event_timestamp)), SECOND) AS session_duration_seconds,
  COUNT(1) AS event_count,
  COUNTIF(event_name = 'page_view') AS page_view_count,
  ARRAY_AGG(source IGNORE NULLS ORDER BY event_timestamp LIMIT 1)[OFFSET(0)] AS initial_source,
  ARRAY_AGG(medium IGNORE NULLS ORDER BY event_timestamp LIMIT 1)[OFFSET(0)] AS initial_medium,
  COALESCE(SUM(event_value_in_usd), 0.0) AS total_revenue_usd
FROM
  session_events
WHERE
  session_id IS NOT NULL
GROUP BY
  user_pseudo_id,
  session_id;