[email protected]
PL EN
Porady. Pandas. Python. Django. Microsoft Power BI. Tableau.

BigQuery. UNNEST - Jak analizować parametry zdarzeń z GA4?

| cloud | google analytics 4 | bigquery

Podczas pracy z surowymi danymi Google Analytics 4 w BigQuery, jednym z najczęstszych zadań, które musimy wykonać, jest analiza parametrów przypisanych do konkretnych zdarzeń. Te parametry, jak page_location czy session_id, są przechowywane w zagnieżdżonej kolumnie event_params typu ARRAY. Bezpośrednie odpytanie tej kolumny jest niemożliwe i prowadzi do analitycznej pułapki.

Problem: Jak odfiltrować zdarzenia po wartości konkretnego parametru?

Załóżmy, że w naszej tabeli z danymi GA4 potrzebujemy znaleźć wszystkie zdarzenia page_view, które wystąpiły na konkretnej stronie (np. /koszyk). Pierwszym odruchem mogłoby być odwołanie się bezpośrednio do klucza w tablicy:

-- UWAGA: TO NIEPRAWIDŁOWE PODEJŚCIE!
SELECT
  event_name,
  event_timestamp
FROM
  `project.dataset.events_*`
WHERE
  event_name = 'page_view'
  AND event_params.key = 'page_location' -- BŁĄD: Nie można odwołać się do pola 'key' w całej tablicy
  AND event_params.value.string_value = '/koszyk'

Co jest nie tak?

To zapytanie zwróci błąd, ponieważ event_params to tablica (ARRAY) struktur. BigQuery nie wie, do którego elementu tablicy się odwołać przy sprawdzaniu warunku event_params.key = 'page_location'. Musimy najpierw "rozpakować" tę tablicę.

Rozwiązanie: Poprawne "spłaszczenie" tablicy za pomocą UNNEST

Aby prawidłowo uzyskać dostęp do parametrów, musimy przekształcić każdy element tablicy event_params w osobny wiersz. Do tego właśnie służy operator UNNEST.

-- PRAWIDŁOWE ROZWIĄZANIE
SELECT
  event_name,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id
FROM
  `project.dataset.events_*`
WHERE
  event_name = 'page_view'
  AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') = '/koszyk'

Jeszcze lepszym i bardziej czytelnym podejściem jest użycie UNNEST w klauzuli FROM:

-- PRAWIDŁOWE ROZWIĄZANIE (WERSJA 2 - CZYTELNIEJSZA)
SELECT
  event_name,
  event_timestamp,
  param.value.string_value as page_location
FROM
  `project.dataset.events_*`,
  UNNEST(event_params) AS param -- "Rozpakowanie" tablicy na osobne wiersze
WHERE
  event_name = 'page_view'
  AND param.key = 'page_location'
  AND param.value.string_value = '/koszyk'

Jak to działa?

UNNEST(event_params) AS param to operator, który pobiera tablicę event_params i tworzy z niej tymczasową tabelę, gdzie każdy wiersz to jeden parametr (jeden element z oryginalnej tablicy). Nadanie aliasu param jest istotne dla dalszego odwoływania się do poszczególnych pól.

FROM ..., UNNEST(...) działa jak CROSS JOIN. Każdy wiersz zdarzenia jest łączony z wierszami wygenerowanymi przez UNNEST z jego własnej tablicy event_params. Dzięki temu dla każdego zdarzenia otrzymujemy tyle wierszy, ile parametrów zawiera jego tablica event_params.

WHERE param.key = 'page_location' pozwala nam teraz bezpiecznie filtrować po kluczu i wartości parametru, ponieważ każdy parametr znajduje się w osobnym wierszu.

Praca z różnymi typami wartości

Warto pamiętać, że parametry w GA4 mogą mieć różne typy wartości. W strukturze event_params znajdziemy:

  • string_value dla wartości tekstowych
  • int_value dla liczb całkowitych
  • float_value i double_value dla liczb zmiennoprzecinkowych

Podsumowanie

Praca z danymi GA4 w BigQuery jest nieodłącznie związana z UNNEST. Gdy potrzebujemy filtrować lub wyciągać dane z kolumny event_params (lub user_properties), zawsze używajmy UNNEST, aby przekształcić tablicę w zbiór wierszy. Dzięki temu nasze zapytania będą nie tylko poprawne, ale też czytelne i wydajne.