GA4 channel and campaign SQL: session source, medium and conversions from BigQuery

Everyone wants the same table: sessions and conversions split by source, medium and campaign, straight from the raw export so nobody can argue with it. The catch is that the GA4 export records traffic source at the event level and inside the traffic_source record, and neither is the session-scoped, last-non-direct number the GA4 acquisition reports show. So you build the session grain yourself, and you say plainly where your figure and Google's will part ways.

This entry derives each session's source and medium from the event parameters, falls back to the traffic_source record when the params are absent, then aggregates sessions and conversions by channel. It is read-only, and it is deliberately a starting point you calibrate against your own account, not a promise to reproduce the UI.

Before you start

• The export dataset is named analytics_XXXXX, where XXXXX is your GA4 property ID; the daily tables are events_YYYYMMDD, queried with the events_* wildcard.
• Replace your_project.analytics_XXXXX with your own project and dataset in every query.
• Decide which events count as conversions for you and edit the conversion_events list in the query. GA4 exports carry every event; "conversion" is your definition, not a column.
• The _TABLE_SUFFIX date filter near the top of each query controls how many daily tables BigQuery scans, and therefore the cost. Keep the window tight while you calibrate.

The query

The session is the grain. Each session is the pair of user_pseudo_id and ga_session_id; within it you take the first non-empty source and medium seen (preferring the event params, then the traffic_source record), then join sessions to their conversions:

-- Session-scoped source / medium / campaign with sessions and conversions.
-- Replace your_project.analytics_XXXXX with your project and dataset.
-- Edit conversion_events to match the events you treat as conversions.
WITH params AS (
  SELECT
    CONCAT(
      user_pseudo_id, '-',
      CAST((SELECT value.int_value FROM UNNEST(event_params)
            WHERE key = 'ga_session_id') AS STRING)
    ) AS session_id,
    event_timestamp,
    event_name,
    (SELECT value.string_value FROM UNNEST(event_params)
     WHERE key = 'source') AS ev_source,
    (SELECT value.string_value FROM UNNEST(event_params)
     WHERE key = 'medium') AS ev_medium,
    (SELECT value.string_value FROM UNNEST(event_params)
     WHERE key = 'campaign') AS ev_campaign,
    traffic_source.source AS ts_source,
    traffic_source.medium AS ts_medium,
    traffic_source.name AS ts_campaign
  FROM `your_project.analytics_XXXXX.events_*`
  -- _TABLE_SUFFIX controls how many daily tables are scanned, and the cost.
  WHERE _TABLE_SUFFIX BETWEEN '20260601' AND '20260607'
),
sessions AS (
  SELECT
    session_id,
    -- First non-empty source/medium seen in the session; params win,
    -- then the traffic_source record; default to (direct)/(none).
    COALESCE(
      MAX(IF(ev_source IS NOT NULL AND ev_source != '', ev_source, NULL)),
      MAX(ts_source),
      '(direct)'
    ) AS source,
    COALESCE(
      MAX(IF(ev_medium IS NOT NULL AND ev_medium != '', ev_medium, NULL)),
      MAX(ts_medium),
      '(none)'
    ) AS medium,
    COALESCE(
      MAX(IF(ev_campaign IS NOT NULL AND ev_campaign != '', ev_campaign, NULL)),
      MAX(ts_campaign),
      '(not set)'
    ) AS campaign,
    COUNTIF(event_name IN ('purchase', 'generate_lead')) AS conversions
  FROM params
  GROUP BY session_id
)
SELECT
  source,
  medium,
  campaign,
  COUNT(*) AS sessions,
  SUM(conversions) AS conversions,
  SAFE_DIVIDE(SUM(conversions), COUNT(*)) AS conversions_per_session
FROM sessions
GROUP BY source, medium, campaign
ORDER BY sessions DESC
LIMIT 100;

Set it up

1. Open the BigQuery console on the project holding your export dataset.
2. Paste the query and replace your_project.analytics_XXXXX with your project and dataset.
3. Edit the two events in COUNTIF(event_name IN ('purchase', 'generate_lead')) to your own conversion events.
4. Set the _TABLE_SUFFIX dates to your window, check the estimated scan size, then run.
5. Compare the totals against the GA4 Traffic acquisition report for the same dates, and read the next section before you conclude anything is broken.

Why it will not match the GA4 UI (and that is expected)

The numbers will be close but not identical, for reasons that are about definition, not error:

• Attribution scope differs. This query takes each session's own first non-empty source, a session-scoped view. The GA4 acquisition reports apply last-non-direct-click attribution across the user journey and, for conversions, a selected attribution model that can spread credit across earlier sessions. Same events, different accounting.
• Processing and modelling. GA4 applies consent modelling, thresholding, (not set) grouping and its own channel-grouping rules on top of the raw events; the export is pre-modelling.
• Session definition edge cases. Session unification across a midnight boundary or across timezones is handled by GA4's processing; a raw ga_session_id count can diverge slightly.

So treat this as your channel picture built on transparent, auditable rules, not as a rebuild of the UI. Where the two disagree, the useful question is which definition you want to stand behind, not which one is wrong. If you need a defined channel grouping (Organic Search, Paid Social and so on) rather than raw source/medium, add a CASE expression over source and medium; it will still be your grouping, honestly your own, and still will not match Google's to the row.