Docs/Google Analytics 4/Attribution
Google Analytics 4

GA4 attribution in BigQuery

The GA4 export has three traffic source records. traffic_source is the user's first source, collected_traffic_source is what an event carried, and session_traffic_source_last_click is the session's last-click source.

Practice this in a free lesson on learn.querylab.io: Attribution by channel.

The GA4 export has three records for where traffic came from, and each answers a different question. traffic_source is the source that first acquired the user. collected_traffic_source is what one event carried, such as UTM values and click IDs, and many events carry nothing. session_traffic_source_last_click is the session’s last-click source across Google Ads and manual campaigns. To report sessions or revenue by source, use the session record if your export has it.

Three scopes

A user first came from Google search, then returned from a newsletter, then typed the address. The three columns disagree:

one user seen through each recordOpen in Querylab.io
12345678910
WITH events AS (
  SELECT * FROM UNNEST([
    STRUCT(1 AS step, 'session 2, first event' AS event, 'google / organic' AS traffic_source, 'newsletter / email' AS session_last_click, 'newsletter / email' AS collected),
    (2, 'session 2, second event', 'google / organic', 'newsletter / email', CAST(NULL AS STRING)),
    (3, 'session 3, direct', 'google / organic', 'newsletter / email', NULL)
  ])
)
SELECT event, traffic_source, session_last_click, collected
FROM events
ORDER BY step
Result in BigQuery
Roweventtraffic_sourcesession_last_clickcollected
1session 2, first eventgoogle / organicnewsletter / emailnewsletter / email
2session 2, second eventgoogle / organicnewsletter / emailnull
3session 3, directgoogle / organicnewsletter / emailnull
3 rows
RecordScopeUse it for
traffic_sourceUserHow users were acquired
collected_traffic_sourceEventRebuilding your own logic
session_traffic_source_last_clickSessionSessions and revenue by source

Don’t group sessions or purchases by traffic_source: it credits everything to the first visit. It is also empty in intraday tables. Don’t treat it as a fixed property of a user either: in Google’s obfuscated sample, 42,260 of 270,154 users show more than one value over three months.

A wildcard query uses the newest table’s schema, so a field added later reads as NULL on older shards. Google’s public sample has traffic_source only.

Build a session source yourself

For older data, or for your own rules, work from collected_traffic_source and take a value per session. IGNORE NULLS skips events that carried nothing. A session with no source at all is direct:

first and last source in a sessionOpen in Querylab.io
12345678910111213141516
WITH events AS (
  SELECT * FROM UNNEST([
    STRUCT('a-1' AS session_key, 100 AS ts, CAST(NULL AS STRING) AS source, CAST(NULL AS STRING) AS medium),
    ('a-1', 200, 'newsletter', 'email'),
    ('a-1', 300, 'google', 'cpc'),
    ('b-1', 150, NULL, NULL)
  ])
)
SELECT
  session_key,
  ARRAY_AGG(source IGNORE NULLS ORDER BY ts LIMIT 1)[SAFE_OFFSET(0)] AS first_source,
  ARRAY_AGG(source IGNORE NULLS ORDER BY ts DESC LIMIT 1)[SAFE_OFFSET(0)] AS last_source,
  IFNULL(ARRAY_AGG(source IGNORE NULLS ORDER BY ts LIMIT 1)[SAFE_OFFSET(0)], '(direct)') AS session_source
FROM events
GROUP BY session_key
ORDER BY session_key
Result in BigQuery
Rowsession_keyfirst_sourcelast_sourcesession_source
1a-1newslettergooglenewsletter
2b-1nullnull(direct)
2 rows

In a real query the source is collected_traffic_source.manual_source. Google’s sample has no such record. It has source, medium and campaign event parameters, filled on 67,364 of 245,209 events in one week. Google’s own session source comes from the session_start event’s gclid, UTM parameters and referrer.

Non-direct last click

GA4 uses non-direct last click for sessions: a direct visit keeps the last known non-direct source inside a lookback window, 90 days by default. So a session typed in by hand can still be credited to Google search. To reproduce that from your session rows, take the last non-direct source in a 90-day range frame:

direct sessions inherit the last sourceOpen in Querylab.io
1234567891011121314151617
WITH sessions AS (
  SELECT * FROM UNNEST([
    STRUCT('user_a' AS user_pseudo_id, DATE '2026-01-01' AS session_day, 'google / organic' AS source),
    ('user_a', DATE '2026-01-20', '(direct) / (none)'),
    ('user_a', DATE '2026-05-01', '(direct) / (none)')
  ])
)
SELECT
  session_day,
  source,
  IFNULL(LAST_VALUE(IF(source = '(direct) / (none)', NULL, source) IGNORE NULLS) OVER (
    PARTITION BY user_pseudo_id
    ORDER BY UNIX_DATE(session_day)
    RANGE BETWEEN 90 PRECEDING AND CURRENT ROW
  ), source) AS attributed_to
FROM sessions
ORDER BY session_day
Result in BigQuery
Rowsession_daysourceattributed_to
12026-01-01google / organicgoogle / organic
22026-01-20(direct) / (none)google / organic
32026-05-01(direct) / (none)(direct) / (none)
3 rows

The January 20 visit inherits google / organic. The May 1 visit is more than 90 days after the last non-direct visit, so it stays direct. UNIX_DATE turns the date into a number of days, which a RANGE frame needs. Window functions explains frames.

Google Ads traffic can show up as “Not Available” in traffic_source, and Google’s export page says to find the click through collected_traffic_source.gclid. One correction treats any session with a gclid as google / cpc:

a gclid overrides the sourceOpen in Querylab.io
123456789101112
WITH sessions AS (
  SELECT * FROM UNNEST([
    STRUCT('a-1' AS session_key, 'Not Available' AS source, 'Not Available' AS medium, 'Cj0abc' AS gclid),
    ('b-1', 'google', 'organic', CAST(NULL AS STRING))
  ])
)
SELECT
  session_key,
  IF(gclid IS NOT NULL, 'google', source) AS source,
  IF(gclid IS NOT NULL, 'cpc', medium) AS medium
FROM sessions
ORDER BY session_key
Result in BigQuery
Rowsession_keysourcemedium
1a-1googlecpc
2b-1googleorganic
2 rows

wBRAID and gBRAID identifiers aren’t in the export, so this can’t catch every paid click. In Querylab.io, the GA4 Model Generator builds session tables with this gclid correction and channel grouping.

Channels

The export has source and medium. Channel names such as Organic Search are defined in the GA4 interface, so you rebuild them in a CASE. The first match wins, so the order matters, and matching is case-insensitive in GA4, which is why the function is called with LOWER:

a simplified channel groupingOpen in Querylab.io
12345678910111213141516
CREATE TEMP FUNCTION channel(source STRING, medium STRING) AS (
  CASE
    WHEN source = '(direct)' AND medium IN ('(none)', '(not set)') THEN 'Direct'
    WHEN REGEXP_CONTAINS(medium, r'^(.*cp.*|ppc|retargeting|paid.*)$') THEN 'Paid'
    WHEN medium = 'organic' THEN 'Organic Search'
    WHEN REGEXP_CONTAINS(CONCAT(source, ' ', medium), r'email|e-mail|e_mail|e mail') THEN 'Email'
    WHEN medium IN ('referral', 'app', 'link') THEN 'Referral'
    ELSE 'Unassigned'
  END
);
SELECT source, medium, channel(LOWER(source), LOWER(medium)) AS channel
FROM UNNEST([
  STRUCT('google' AS source, 'CPC' AS medium), ('google', 'organic'),
  ('(direct)', '(none)'), ('newsletter', 'email'), ('blog.example.com', 'referral'), ('partner', 'sponsor')
])
ORDER BY source, medium
Result in BigQuery
Rowsourcemediumchannel
1(direct)(none)Direct
2blog.example.comreferralReferral
3googleCPCPaid
4googleorganicOrganic Search
5newsletteremailEmail
6partnersponsorUnassigned
6 rows

This is a short version of Google’s default channel group. The real rules also use lists of search, social, video and shopping sites, and they change over time, so expect small differences from the reports. Put the CASE in a temp or persistent function to have one definition. CASE, IF and COALESCE covers CASE itself.

Revenue by channel

Attribute at the session level, then join purchases to sessions. Start from sessions with a LEFT JOIN, so sessions with no purchase still count. Count sessions with DISTINCT, because a session with two purchases appears twice after the join:

sessions and revenue per channelOpen in Querylab.io
1234567891011121314151617
WITH sessions AS (
  SELECT * FROM UNNEST([
    STRUCT('a-1' AS session_key, 'Email' AS channel),
    ('b-1', 'Organic Search'), ('c-1', 'Organic Search')
  ])
),
purchases AS (
  SELECT * FROM UNNEST([STRUCT('a-1' AS session_key, 100.0 AS revenue), ('a-1', 50.0), ('c-1', 30.0)])
)
SELECT
  s.channel,
  COUNT(DISTINCT s.session_key) AS sessions,
  IFNULL(SUM(p.revenue), 0) AS revenue
FROM sessions AS s
LEFT JOIN purchases AS p USING (session_key)
GROUP BY s.channel
ORDER BY revenue DESC
Result in BigQuery
Rowchannelsessionsrevenue
1Email1150.0
2Organic Search230.0
2 rows

Deduplicate purchases first, as in GA4 ecommerce data.

Open the playground. It is the real app on public datasets and needs no sign-in.