Większość stron ma dziś wdrożony Consent Mode w wersji advanced – dane lecą do Google Analytics nawet bez zgody użytkownika, ale w formie zanonimizowanej. Problem? Tych danych nie zobaczysz w raportach GA4, chyba że masz włączone modelowanie. A czy modelowanie faktycznie pokazuje poprawne liczby? To już inna historia.
Poniższy SQL pozwala mi sprawdzać liczbę sesji wraz ze źródłem/medium z podziałem na zgodę i brak zgody. Jest to przydatne nawet wtedy, gdy w GA4 masz aktywne modelowanie danych, ponieważ daje kontrolę nad tym ile sesji rozpoczynają konkretne źródła. A także zobaczysz jaki % użytkowników tak naprawdę rozpoczyna swoje sesji bez zgód na śledzenie.
Podsumowanie audio NotebookLM: posłuchaj wygenerowany podcast.
Uwaga 🚨: Ten kod zadziała tylko, jeśli masz wdrożony Consent Mode v2 i masz aktywne połączenie Google Analytics z BigQuery.
WITH InitialData AS (
SELECT
event_date,
event_name,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS medium_raw,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') AS source_raw,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign') AS campaign_raw,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'term') AS utm_term_raw,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'content') AS utm_content_raw,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer') AS page_referrer_raw,
privacy_info.analytics_storage,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id
FROM
`test-bk-1567a.analytics_377239886.events_*`
WHERE
event_name = 'session_start'
AND COALESCE((SELECT value.string_value FROM unnest(event_params) WHERE key = 'page_referrer'), '') NOT LIKE '%test.pl%' --tutaj wpisz adres swojej strony
--użyj poniższej opcji jeśli chcesz sprawdzać dane z dnia dzisiejszego
--and regexp_extract(_table_suffix, r'[0-9]+') = format_date('%Y%m%d', current_date())
--użyj poniższej opcji jeśli chcesz dopisywać do tabeli dane z poprzedniego dnia
--and regexp_extract(_table_suffix, r'[0-9]+') = format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
--użyj poniższej opcji jeśli chcesz dopisywać do tabeli dane z przedwczorajszego dnia
--and regexp_extract(_table_suffix, r'[0-9]+') = format_date('%Y%m%d',date_sub(current_date(), interval 2 day))
--użyj poniższej opcji jeśli chcesz nadpisywać dane w tabli do dnia wczorajszego
--and regexp_extract(_table_suffix, r'[0-9]+') between '20240401' and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
)
SELECT
event_date,
event_name,
COALESCE(medium_raw, '(none)') AS medium,
COALESCE(source_raw, '(direct)' ) AS source,
COALESCE(campaign_raw, '(not set)') AS campaign,
COALESCE(utm_term_raw, '(not set)') AS utm_term_session,
COALESCE(utm_content_raw, '(not set)') AS utm_content_session,
page_referrer_raw AS page_referrer,
analytics_storage,
COUNT(*) AS count_event
FROM
InitialData
GROUP BY
event_date,
event_name,
medium,
source,
campaign,
utm_term_session,
utm_content_session,
page_referrer,
analytics_storage
ORDER BY
count_event DESCDlaczego ten SQL jest tak ważny?
- Weryfikacja modelowania GA4: Pozwala sprawdzić, czy modelowane dane w raportach GA4 odzwierciedlają rzeczywistość.
- Odkrycie ukrytego ruchu: Ujawnia, ile sesji i z jakich źródeł pochodzi od użytkowników bez zgody na śledzenie. Możesz się zdziwić, jak duży to procent.
- Lepsze decyzje mediowe: Jeśli widzisz, że np. duża część ruchu z konkretnego kanału to użytkownicy bez zgody, wiesz, że Twoje raporty w GA4 mogą zaniżać wyniki tego kanału.
🚀 Wnioski? Czasem okazuje się, że spory procent sesji pochodzi od użytkowników bez zgody – a to oznacza, że Twoje decyzje oparte wyłącznie na raportach GA4 mogą być obarczone błędem.
Spis treści:
00:00 Wstęp
00:44 Połączenie GA4 i BigQuery
01:56 Zbiór danych i Analytics storage
03:27 SQL consent mode source/medium
08:03 Podgląd raportu Looker Studio
10:22 Ustawianie okresu danych i harmonogram zapytania SQL
14:29 Zakończenie