Adam Brzostek
26.10.2024
Czas czytania: 4:00

Źródła ruchu w Google Big Query

Pracując przy analizie ruchu internetowego z wykorzystaniem Google Analytics 4 po pewnym czasie zdamy sobie sprawę z ograniczeń i często ułomności interfejsu. Okaże się, jedyna słuszna droga do wiarygodnych i sensowny danych wiedzie przez Google Big Query.

Jakby nie było to wystarczające utrudnienie to mimo, że ciężko w to uwierzyć, Google w samym BQ do niedawna utrudniał nam zadanie jeszcze bardziej.

Mimo, że sama struktura exportu GA4 do BQ jest w moim odczuciu dużo prostsza, to poprawne raportowanie źródeł ruchu wymagało umiejętności ninja, a czasami było wręcz niemożliwe. A co najdziwniejsze to problem ten dotyczył głównie kampanii google / cpc. Źródło to praktycznie nie pojawia się w BQ co mija się zupełnie z realiami.

Objaśnienie problemu

Jedną z powszechnie opisywanych metod wyciągania źródeł ruchu jest wykorzystanie parametrów zdarzenia przypisywanych przy zdarzeniu session_start:

select concat(user_pseudo_id, (select value.int_value from unnest(event_params) where key = 'ga_session_id')) as session_id, user_pseudo_id, max((select value.string_value from unnest(event_params) where key = 'campaign')) as campaign, max((select value.string_value from unnest(event_params) where key = 'source')) as source, max((select value.string_value from unnest(event_params) where key = 'medium')) as medium, from `bettersteps.analytics_269424883.events_*` group by 1,2

Teoretycznie zapytanie to powinno nam zwrócić wartość źródła, medium oraz kampanii dla konkretnego id sesji, które jest konkatenacją user_pseudo_id oraz ga_session_id.

Zapytanie faktycznie zwraca nam te dane, natomiast robiąc szybkie porównanie z interfejsem GA4 od razu możemy zorientować się, że coś jest nie tak.

Widzimy od razu, że źródło google / cpc jest całkowicie pominięte w BQ, a proporcje innych źródeł są na tyle zachwiane, że informacja ta staje się całkowicie niewiarygodna.

Jest to dla mnie całkowicie niezrozumiałe jak Google nie mógł rozwiązać tak fundamentalnej kwestii, jak raportowanie źródeł ruchu, a projektując GA4 od razu wiadome było, że zaawansowane raportowanie bez BQ nie będzie możliwe.

Już samo podejście do sesji, która może mieć przypisane wiele źródeł budzi spore wątpliwości to w dodatku można stwierdzić, że Google próbował skazać nas na ślepe zaufanie temu co pokazuje interfejs.

W końcu coś dobrego

GA4 release note

Problem opisany powyżej był powszechnie znany i inżynierowie Google po blisko 3 latach od premiery postanowili wsłuchać się w głos ludu. Wraz z aktualizacją GA4 z 19 lipca 2024 do exportu BQ zostały dodane nowe pola zawierające informacje o źródłach ruchu na poziomie sesji.

BQ export new fields

Ta aktualizacja częściowo rozwiązuje problem, ale nadal potrzebna jest pewna gimnastyka przy pisaniu zapytania.

Medium cpc w przypadku źródła google nie jest podane na wprost w parametrze medium. Żeby określić, które sesje pochodzą ze źródła google / cpc musimy wykorzystać pole session_traffic_source_last_click.google_ads_campaign.campaign_name na podstawie, którego ustalamy warunek, że jeżeli jego wartość jest podana to parametr medium przyjmuje wartość cpc.

Zapytanie powinno wyglądać w ten sposób:

SELECT DISTINCT user_pseudo_id as ga_client_id, cast(event_date as date format 'yyyymmdd') as partition_date, event_name, event_timestamp, (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id, Concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id') ) as ga_session_id, (case when session_traffic_source_last_click.google_ads_campaign.campaign_name is not null then "google" else session_traffic_source_last_click.manual_campaign.`source` end) as source, (case when session_traffic_source_last_click.google_ads_campaign.campaign_name is not null then "cpc" else session_traffic_source_last_click.manual_campaign.medium end) as medium, session_traffic_source_last_click.manual_campaign.campaign_name, session_traffic_source_last_click.google_ads_campaign.campaign_name as campaign_ads, FROM `project.property.events_*`

Czy metryka sesji w GA4 jest jakkolwiek wiarygodna?

GA vs BQ v1

Porównując dane z interfejsu GA z danymi z BQ wygenerowanymi na podstawie opisanej wyżej metody zaobserwowałem jeszcze jedną niezgodność.

Byłem świadomy, że liczba sesji nie będzie się zgadzać, co jest związane z ustawieniami tożsamości raportowej, danymi modelowanymi i innej tajemniczej metody obliczania sesji przez GA4, natomiast spodziewałem się zachowania tych samych proporcji źródeł ruchu.

Ponieważ nieznacznie się one różniły zacząłem to weryfikować bardziej szczegółowo.

Okazało się, że w BQ jedna sesja ma przypisane więcej niż 1 źródło.

Nie byłoby w tym nic nadzwyczajnego ale okazało się, że ta sama sesji zliczana jest w interfejsie GA4 dwukrotnie

Dzieje się tak, kiedy z nieznanego mi jeszcze powodu zdarzenie user_engagement wywołuje się przed zdarzeniem session_start.

Oznacza to, że powinniśmy usunąć źródło (not set) z naszych danych.

Oto przykład zapytania jak to zrobić.

with raw_bq as ( SELECT DISTINCT user_pseudo_id as ga_client_id, cast(event_date as date format 'yyyymmdd') as partition_date, select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id, Concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id') ) as ga_session_id, (case when session_traffic_source_last_click.google_ads_campaign.campaign_name is not null then "google" else session_traffic_source_last_click.manual_campaign.`source` end) as source, (case when session_traffic_source_last_click.google_ads_campaign.campaign_name is not null then "cpc" else session_traffic_source_last_click.manual_campaign.medium end) as medium, session_traffic_source_last_click.manual_campaign.campaign_name, session_traffic_source_last_click.google_ads_campaign.campaign_name as campaign_ads, FROM `project.property.events_*` order by 4 ), raw_bq_mark_duplicates as ( SELECT DISTINCT *, (case when source != "(not set)" then 1 else 2 end) as score from raw_bq ), raw_bq_ranking as ( SELECT *, row_number() over (partition by ga_session_id order by score) as ranking from raw_bq_mark_duplicates order by 4,10 ) SELECT CONCAT(source, " / ", medium) as source_medium, campaign_name as campaign, campaign_ads, count(distinct ga_session_id) as sessions from raw_bq_ranking where ranking = 1 group by 1,2,3 order by 4 desc

W momencie, w którym z obydwu zbiorów wykluczyliśmy (not set) to liczba sesji stała się bardziej zgodna.

Podział ruchu wg źródeł nadal nie jest identyczny, ale z pewnością na potrzeby analizy możemy w końcu uznać informacje o źródłach ruchu pochodzącą z BQ na w miarę wiarygodną.

Natomiast poddaje to pod wątpliwość czy można jakkolwiek poważnie traktować metrykę sesji w interfejsie GA4 - uważam, że trzeba do tego podchodzić z dużą rezerwą.

Jeżeli chciałbyś przejść w swojej firmie na raportowanie w oparciu o Big Query, gdzie będziesz mógł łączyć dane z różnych systemów to serdecznie zapraszamy do kontaktu. Zajmujemy się tym od wielu lat i wiemy jak to robić.

Powiązane artykuły