How to Get Session Source and Medium in GA4 BigQuery Data

User-scoped: first-touch attribution

traffic_source.source and traffic_source.medium describe how the user was first acquired, not how this session arrived. Grouping sessions by it silently turns every report into first-touch.

SELECT
  traffic_source.source,
  traffic_source.medium,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM `myproject.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260801' AND '20260828'
GROUP BY 1, 2
ORDER BY users DESC;

Session-scoped: session_traffic_source_last_click

Exports written since mid-2024 include a session_traffic_source_last_click record with last-click attribution per session. If your daily tables have it, this is the closest match to the UI's session source/medium.

SELECT
  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, '-',
    CAST((SELECT value.int_value
          FROM UNNEST(event_params)
          WHERE key = 'ga_session_id') AS STRING)
  )) AS sessions
FROM `myproject.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20260801' AND '20260828'
GROUP BY 1, 2
ORDER BY sessions DESC;

Older exports: derive it from the session's first event

Before the session-scoped record existed, the standard workaround was taking source and medium from event-scoped data at the start of each session: collected_traffic_source.manual_source and .manual_medium on the session's first event, falling back to the source/medium event parameters. It reconstructs roughly what the UI computes, and exactly matching the UI is not a realistic goal, since GA4 applies its own attribution processing that the raw export does not carry.

Do more with BigQuery

• Export BigQuery to Google Sheets on a schedule
• Send BigQuery results to Slack
• BigQuery SQL client in the browser