Session and Attribution Analysis in BigQuery

Sessions in GA4 BigQuery: The Complication

Unlike Universal Analytics, GA4's BigQuery export does not have a pre-built session dimension. Instead, sessions are identified by the session_id parameter within event_params, which is unique per user-session combination (combined with user_pseudo_id). To work with sessions, you need to construct them from events.

Identifying Sessions

-- Session identification: user_pseudo_id + session_id = unique session
SELECT
  user_pseudo_id,
  (SELECT value.int_value FROM UNNEST(event_params)
   WHERE key = 'session_id') AS session_id,
  MIN(event_timestamp) as session_start,
  MAX(event_timestamp) as session_end,
  COUNT(*) as events_in_session
FROM `project.analytics_PROPERTY.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
GROUP BY user_pseudo_id, session_id

Session-Level Source/Medium

The traffic source for a session comes from the session_start event (event_name = 'session_start') rather than from the traffic_source column on all events (which captures the first session, not the current session):

SELECT
  user_pseudo_id,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'session_id') AS session_id,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') AS session_source,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS session_medium
FROM `project.analytics_PROPERTY.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
  AND event_name = 'session_start'

Attribution: Last-Click

To attribute a conversion to the last non-direct session source before the conversion:

WITH sessions AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'session_id') AS session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') AS source,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS medium,
    event_timestamp
  FROM `project.analytics_PROPERTY.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
    AND event_name = 'session_start'
),
purchases AS (
  SELECT
    user_pseudo_id,
    event_timestamp as purchase_time,
    (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
  FROM `project.analytics_PROPERTY.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
    AND event_name = 'purchase'
)
SELECT
  p.transaction_id,
  p.revenue,
  LAST_VALUE(s.source IGNORE NULLS) OVER (
    PARTITION BY p.user_pseudo_id
    ORDER BY s.event_timestamp
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS last_click_source
FROM purchases p
JOIN sessions s ON p.user_pseudo_id = s.user_pseudo_id
  AND s.event_timestamp <= p.purchase_time

Summary

GA4 BigQuery sessions are constructed by grouping on user_pseudo_id + session_id event parameter. Session-level source/medium comes from the session_start event's source and medium parameters. Attribution analysis requires joining session data to conversion events by user_pseudo_id and using window functions (LAST_VALUE, FIRST_VALUE) to assign attribution credit. These patterns are the foundation of custom attribution modelling in BigQuery.

See our BigQuery Setup service for attribution analysis development.

Need custom attribution analysis in BigQuery? Contact Adslytics.