Jak policzyć First User Primary Channel Group w GA4 BigQuery

PrzezBartosz Spychalski Godziny Aktualizacja

(i dlaczego logika jest ważniejsza niż sam SQL)

Jeśli pracujesz z danymi GA4 w BigQuery, prędzej czy później trafisz na ten problem:
jak poprawnie odtworzyć „First User Primary Channel Group” poza interfejsem GA4.

Niby proste pole.
A w praktyce — złożona logika, dziesiątki warunków i łatwy sposób, żeby się pomylić.

Ten wpis pokazuje jak podejść do tego zadania poprawnie i dlaczego kluczem nie jest kopiowanie SQL-a, tylko zrozumienie logiki stojącej za channel groupingiem.

Dlaczego „First User Primary Channel Group” jest problematyczne?

W interfejsie Google Analytics 4:

  • wszystko „po prostu działa”
  • channel grouping jest liczony automatycznie

W BigQuery:

  • dostajesz surowe dane
  • całą logikę musisz odtworzyć sam

I tu zaczynają się schody.

Fundament: logika GA4 Default Channel Group

Podstawą jest GA4 Default Channel Group, czyli zestaw reguł opartych na:

  • traffic_source.source
  • traffic_source.medium
  • traffic_source.name (kampania)

Każdy kanał (Direct, Paid Search, Organic Social itd.) to:

  • konkretne warunki
  • regexy
  • listy źródeł
  • kombinacje source + medium + campaign

Bez tej logiki:
➡️ wyniki nigdy nie będą zgodne z GA4.

Krok 1: zdefiniuj kanały (bez SQL-a)

Zanim napiszesz choć jedną linijkę zapytania, musisz mieć jasną definicję kanałów.

Przykładowo:

  • Direct
    • source = (direct)
    • medium = (not set) lub (none)
  • Paid Shopping
    • source = platformy zakupowe LUB
    • campaign zawiera „shop / shopping”
    • medium = cpc / ppc / paid
  • Paid Search / Paid Social / Paid Video
    • osobne listy źródeł
    • medium płatne

To jest najbardziej czasochłonna część pracy — i jednocześnie najważniejsza.

Krok 2: tylko użytkownicy z session_start

Żeby policzyć First User:

  • bierzesz tylko użytkowników, którzy wywołali event session_start
  • pracujesz na user_pseudo_id

To pozwala:

  • uniknąć zduplikowanych użytkowników
  • zachować spójność z raportami GA4

Krok 3: agregacja po użytkowniku

Dalej:

  • przypisujesz jeden channel group do użytkownika
  • liczysz distinct user_pseudo_id

Efekt:

  • realna liczba użytkowników pozyskanych z danego kanału
  • a nie liczba eventów czy sesji

Krok 4: procentowy udział kanałów

Żeby raport był użyteczny:

  • liczysz udział % każdego kanału
  • odnosisz go do całkowitej liczby użytkowników

To dokładnie to, co widzisz w raportach akwizycji GA4 — tylko w wersji SQL.

Krok 5: wiersz „Total”

Mała rzecz, a robi ogromną różnicę:

  • suma użytkowników
  • 100% udziału
  • łatwe sanity-checki wyników

Jeśli suma się nie zgadza → logika jest błędna.

SQL to efekt końcowy, nie punkt startu

Finalnie:

  • zapytanie ma ponad 100 linii SQL
  • ale jego wygenerowanie zajmuje sekundy
-- Calculating First User Primary Channel Group.

WITH
  extracted_users AS (
    SELECT
      user_pseudo_id,
      CASE
        WHEN
          traffic_source.source = '(direct)'
          AND traffic_source.medium IN ('(not set)', '(none)')
          THEN 'Direct'
        WHEN
          (
            regexp_contains(
              traffic_source.source,
              'alibaba|amazon|google shopping|shopify|etsy|ebay|stripe|walmart')
            OR regexp_contains(
              traffic_source.name, '^(.*(([^a-df-z]|^)shop|shopping).*)$'))
          AND regexp_contains(traffic_source.medium, '^(.*cp.*|ppc|paid.*)$')
          THEN 'Paid Shopping'
        WHEN
          regexp_contains(
            traffic_source.source,
            'baidu|bing|duckduckgo|ecosia|google|yahoo|yandex')
          AND regexp_contains(traffic_source.medium, '^(.*cp.*|ppc|paid.*)$')
          THEN 'Paid Search'
        WHEN
          regexp_contains(
            traffic_source.source,
            'badoo|facebook|fb|instagram|linkedin|pinterest|tiktok|twitter|whatsapp')
          AND regexp_contains(traffic_source.medium, '^(.*cp.*|ppc|paid.*)$')
          THEN 'Paid Social'
        WHEN
          regexp_contains(
            traffic_source.source,
            'dailymotion|disneyplus|netflix|youtube|vimeo|twitch')
          AND regexp_contains(traffic_source.medium, '^(.*cp.*|ppc|paid.*)$')
          THEN 'Paid Video'
        WHEN
          traffic_source.medium IN (
            'display', 'banner', 'expandable', 'interstitial', 'cpm')
          THEN 'Display'
        WHEN
          regexp_contains(
            traffic_source.source,
            'alibaba|amazon|google shopping|shopify|etsy|ebay|stripe|walmart')
          OR regexp_contains(
            traffic_source.name, '^(.*(([^a-df-z]|^)shop|shopping).*)$')
          THEN 'Organic Shopping'
        WHEN
          regexp_contains(
            traffic_source.source,
            'badoo|facebook|fb|instagram|linkedin|pinterest|tiktok|twitter|whatsapp')
          OR traffic_source.medium IN (
            'social', 'social-network', 'social-media', 'sm', 'social network',
            'social media')
          THEN 'Organic Social'
        WHEN
          regexp_contains(
            traffic_source.source,
            'dailymotion|disneyplus|netflix|youtube|vimeo|twitch')
          OR regexp_contains(traffic_source.medium, '^(.*video.*)$')
          THEN 'Organic Video'
        WHEN
          regexp_contains(
            traffic_source.source,
            'baidu|bing|duckduckgo|ecosia|google|yahoo|yandex')
          OR traffic_source.medium = 'organic'
          THEN 'Organic Search'
        WHEN
          regexp_contains(traffic_source.source, 'email|e-mail|e_mail|e mail')
          OR regexp_contains(
            traffic_source.medium, 'email|e-mail|e_mail|e mail')
          THEN 'Email'
        WHEN traffic_source.medium = 'affiliate' THEN 'Affiliates'
        WHEN traffic_source.medium = 'referral' THEN 'Referral'
        WHEN traffic_source.medium = 'audio' THEN 'Audio'
        WHEN traffic_source.medium = 'sms' THEN 'SMS'
        WHEN
          traffic_source.medium LIKE '%push'
          OR regexp_contains(traffic_source.medium, 'mobile|notification')
          THEN 'Mobile Push Notifications'
        ELSE 'Unassigned'
        END AS channel_grouping
    FROM
      `dbrt-ga4.analytics_207472454.events_*`
    WHERE
      _table_suffix BETWEEN '20241001' AND '20241031'
      AND user_pseudo_id IS NOT NULL
      AND event_name = 'session_start'
      AND traffic_source.source IS NOT NULL
      AND traffic_source.medium IS NOT NULL
  ),
  aggregated AS (
    SELECT
      channel_grouping AS first_user_primary_channel_group,
      COUNT(DISTINCT user_pseudo_id) AS total_users
    FROM extracted_users
    GROUP BY channel_grouping
  ),
  final AS (
    SELECT
      first_user_primary_channel_group,
      total_users,
      round(100 * total_users / sum(total_users) OVER (), 2) AS percent_of_total
    FROM aggregated
    UNION ALL
    SELECT
      'Total',
      sum(total_users),
      100.00
    FROM aggregated
  )
SELECT *
FROM final
ORDER BY
  CASE WHEN first_user_primary_channel_group = 'Total' THEN 0 ELSE 1 END,
  total_users DESC

Cały czas schodzi na:
👉 zrozumienie logiki channel groupingu

Bez tego:

  • nawet poprawny syntaktycznie SQL
  • da błędne dane

Dlaczego to podejście się skaluje?

Bo gdy rozumiesz logikę, możesz:

  • użyć tego samego channel groupingu z innymi metrykami
  • testować alternatywne definicje kanałów
  • budować raporty użytkowników, sesji, przychodu
  • automatyzować analizy zamiast pisać SQL od zera

Gotowy SQL to jedno zastosowanie.
Zrozumiana logika → N zastosowań.

Podsumowanie

Liczenie First User Primary Channel Group w GA4 BigQuery to nie jest problem SQL-owy.
To problem koncepcyjny.

Jeśli:

  • rozumiesz GA4 Default Channel Group
  • wiesz, jak działa source / medium / campaign
  • nie mieszasz scope’ów

to:

SQL staje się tylko narzędziem, a nie przeszkodą.

Podobne wpisy