# 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. Source: https://docs.querylab.io/bigquery/ga4-attribution/ Updated: 2026-11-04 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 record** ```sql 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 (3 rows): | event | traffic_source | session_last_click | collected | | --- | --- | --- | --- | | session 2, first event | google / organic | newsletter / email | newsletter / email | | session 2, second event | google / organic | newsletter / email | NULL | | 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](https://docs.querylab.io/bigquery/wildcard-tables/) 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 session** ```sql 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 (2 rows): | session_key | first_source | last_source | session_source | | --- | --- | --- | --- | | a-1 | newsletter | google | newsletter | | 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: **direct sessions inherit the last source** ```sql 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 (3 rows): | session_day | source | attributed_to | | --- | --- | --- | | 2026-01-01 | google / organic | google / organic | | 2026-01-20 | (direct) / (none) | google / organic | | 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](https://docs.querylab.io/bigquery/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`: **a gclid overrides the source** ```sql 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 (2 rows): | session_key | source | medium | | --- | --- | --- | | a-1 | google | cpc | | b-1 | google | 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](https://docs.querylab.io/schema-browser/ga4-events/#extra-menu-items-on-a-ga4-dataset) 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 grouping** ```sql 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 (6 rows): | source | medium | channel | | --- | --- | --- | | (direct) | (none) | Direct | | blog.example.com | referral | Referral | | google | CPC | Paid | | google | organic | Organic Search | | newsletter | email | Email | | partner | sponsor | Unassigned | This is a short version of Google's [default channel group](https://support.google.com/analytics/answer/9756891). 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](https://docs.querylab.io/bigquery/user-defined-functions/) to have one definition. [CASE, IF and COALESCE](https://docs.querylab.io/bigquery/conditional-expressions/) 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 channel** ```sql 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 (2 rows): | channel | sessions | revenue | | --- | --- | --- | | Email | 1 | 150.0 | | Organic Search | 2 | 30.0 | Deduplicate purchases first, as in [GA4 ecommerce data](https://docs.querylab.io/bigquery/ga4-ecommerce/#duplicate-purchases). Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in. ## Sources Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery: - [BigQuery Export schema (Analytics Help)](https://support.google.com/analytics/answer/7029846) - [Default channel group (Analytics Help)](https://support.google.com/analytics/answer/9756891) - [About Analytics sessions (Analytics Help)](https://support.google.com/analytics/answer/9191807) - [BigQuery Export (Analytics Help)](https://support.google.com/analytics/answer/9358801)