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:
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
| Row | event | traffic_source | session_last_click | collected |
|---|---|---|---|---|
| 1 | session 2, first event | google / organic | newsletter / email | newsletter / email |
| 2 | session 2, second event | google / organic | newsletter / email | null |
| 3 | session 3, direct | google / organic | newsletter / email | null |
| Record | Scope | Use it for |
|---|---|---|
traffic_source | User | How users were acquired |
collected_traffic_source | Event | Rebuilding your own logic |
session_traffic_source_last_click | Session | Sessions 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:
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
| Row | session_key | first_source | last_source | session_source |
|---|---|---|---|---|
| 1 | a-1 | newsletter | newsletter | |
| 2 | b-1 | null | null | (direct) |
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:
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
| Row | session_day | source | attributed_to |
|---|---|---|---|
| 1 | 2026-01-01 | google / organic | google / organic |
| 2 | 2026-01-20 | (direct) / (none) | google / organic |
| 3 | 2026-05-01 | (direct) / (none) | (direct) / (none) |
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 clicks with no source
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:
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
| Row | session_key | source | medium |
|---|---|---|---|
| 1 | a-1 | cpc | |
| 2 | b-1 | organic |
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:
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
| Row | source | medium | channel |
|---|---|---|---|
| 1 | (direct) | (none) | Direct |
| 2 | blog.example.com | referral | Referral |
| 3 | CPC | Paid | |
| 4 | organic | Organic Search | |
| 5 | newsletter | ||
| 6 | partner | sponsor | Unassigned |
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:
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
| Row | channel | sessions | revenue |
|---|---|---|---|
| 1 | 1 | 150.0 | |
| 2 | Organic Search | 2 | 30.0 |
Deduplicate purchases first, as in GA4 ecommerce data.