One of the first shocks in the GA4 export is that the tidy channel labels from the interface – Organic Search, Paid Social, Direct – are simply not there. You have to rebuild them from raw source and medium. Here is how.

Where the source data lives

Channel grouping is derived, not stored, so you assemble it from traffic-source fields. Since July 2024, the cleanest input is session_traffic_source_last_click, which holds session-level last-click source and medium. Older data, or event-level detail, comes from collected_traffic_source or the source and medium event parameters.

The CASE logic

Recreating the grouping is a CASE statement that mirrors Google's rules, applied to source and medium:

SELECT
  session_key,
  CASE
    WHEN medium = '(none)' OR source = '(direct)' THEN 'Direct'
    WHEN medium = 'organic' THEN 'Organic Search'
    WHEN REGEXP_CONTAINS(medium, r'^(cpc|ppc|paidsearch)$') THEN 'Paid Search'
    WHEN REGEXP_CONTAINS(medium, r'^(social|social-network|social-media)$') THEN 'Organic Social'
    WHEN medium = 'email' THEN 'Email'
    WHEN medium = 'referral' THEN 'Referral'
    WHEN medium = '(not set)' THEN 'Unassigned'
    ELSE 'Other'
  END AS channel
FROM session_source

That covers the common channels; Google's full definition has more branches for paid social, display, and video.

Match Google's rules, not your guesses

The reason to follow Google's published definitions closely is consistency: if your CASE logic diverges, your BigQuery channels will not reconcile with the interface, and stakeholders will trust neither. Keep the rules and their order aligned with the official channel definitions.

Why order matters

A CASE statement stops at the first match, so the sequence of branches is part of the logic:

  • Put Direct first, since it depends on the absence of other sources.
  • Test paid mediums before generic ones, so cpc is not swallowed by a broader rule.
  • Keep a final ELSE for anything unmatched, so nothing silently vanishes.

The tidy channel labels you missed from the interface are gone from the export because they were always derived, and now you derive them. Pull source and medium from the last-click field, map them with an ordered CASE that follows Google's rules, and keep a catch-all. Do that and your BigQuery reports speak the same channel language as the interface, instead of a dialect only your query understands.

Want a stronger data analyst role or a raise? Grab the FREE Product Analyst Playbook and get the exact roadmap to your next offer.