NTPM

Rysunek motywu technologicznego i tabela z komputerem Wielokrotne narażenie Pojęcie informacji

BigQuery unnest – jak analizować zagnieżdżone dane z GA4?

Jakub Graca 7 min. czytania

W erze danych marketingowych Google Analytics 4 (GA4) stał się nieodzownym narzędziem dla marketerów, analityków i właścicieli biznesów online. Jednak surowe dane z GA4 eksportowane do BigQuery często przybierają formę zagnieżdżonych struktur (nested data), co komplikuje ich analizę. Funkcja UNNEST „rozpakowuje” te struktury, umożliwiając precyzyjne filtrowanie, agregację i szybkie wyciąganie insightów. W tym przewodniku krok po kroku wyjaśniamy, jak skutecznie używać UNNEST do analizy parametrów zdarzeń GA4 – wraz z gotowymi przykładami SQL.

Dlaczego dane GA4 w BigQuery są zagnieżdżone i dlaczego to problem?

GA4 eksportuje dane do BigQuery w schemacie event-driven, gdzie każde zdarzenie (np. page_view, click, purchase) zawiera tablicę event_params – zagnieżdżoną strukturę ARRAY<STRUCT>. Każdy parametr ma klucz (key) i wartość przechowywaną w jednym z pól: string_value (tekst), int_value (liczby całkowite), float_value lub double_value (liczby zmiennoprzecinkowe).

Problem zagnieżdżenia – standardowe zapytania SQL nie radzą sobie z tablicami wewnątrz wierszy. Bez UNNEST dane pozostają „zamknięte”, uniemożliwiając proste filtrowanie po konkretnych parametrach, jak czas zaangażowania (engagement_time_msec) czy tytuł strony (page_title). To blokuje zaawansowane analizy, np. obliczanie średniego czasu na stronie czy segmentację użytkowników po źródłach ruchu.

Aby lepiej zrozumieć, co zyskujesz dzięki UNNEST, zwróć uwagę na poniższe korzyści:

  • spłaszczanie (flattening) – konwertuje tablice na płaskie wiersze, gotowe do JOIN-ów i agregacji;
  • precyzyjne filtrowanie – wyciągaj tylko potrzebne parametry;
  • skalowalność – idealne dla dużych zbiorów danych GA4 (miliony zdarzeń dziennie);
  • integracja z narzędziami – łatwe połączenie z Looker Studio, Data Studio czy Power BI.

Przed rozpoczęciem upewnij się, że masz aktywny eksport GA4 do BigQuery (Ustawienia usługi > BigQuery linking). Dane lądują w tabelach events_* z sufiksami dat (np. 20230425).

Podstawy składni UNNEST w BigQuery

Funkcja UNNEST traktuje tablicę jak tymczasową tabelę. Podstawowa składnia wygląda tak:

SELECT ...
FROM `projekt.dataset.events_*`,
UNNEST(event_params) AS param

Co oznaczają poszczególne elementy zapytania:

  • UNNEST(event_params) – rozpakowuje tablicę event_params do wierszy;
  • AS param – nadaje alias strukturze (dostęp do pól np. param.key, param.value.string_value);
  • _TABLE_SUFFIX – służy do filtrowania dat, np. WHERE _TABLE_SUFFIX BETWEEN '20230101' AND '20231231'.

Ważna uwaga: zawsze sprawdzaj typ wartości! Np. page_title to string_value, a engagement_time_msec to int_value. Błędny typ = wartości NULL.

Przykład 1 – proste rozpakowanie parametrów dla page_view

Wyodrębnij czas zaangażowania i tytuł strony ze zdarzeń page_view:

SELECT
event_name,
param.key,
param.value.string_value,
param.value.int_value,
param.value.double_value
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
UNNEST(event_params) AS param
WHERE event_name = 'page_view'
AND (param.key = 'engagement_time_msec' OR param.key = 'page_title')

Rezultat – powstaje płaska tabela z kluczami i wartościami, którą możesz łatwo agregować (np. średni czas: AVG(IF(param.key = 'engagement_time_msec', param.value.int_value, NULL))).

Zaawansowane techniki UNNEST – subquery i funkcje tymczasowe

Aby ograniczyć koszty i złożoność zapytań, unikaj wielokrotnego UNNEST, a zamiast tego stosuj subquery lub funkcje tymczasowe (TEMP FUNCTION).

Przykład 2 – wyciąganie sesji i numeru sesji

Oto przykładowe zapytanie z subquery, które pobiera numer sesji bez pełnego UNNEST całego zbioru:

SELECT
user_pseudo_id,
event_date,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS ga_session_number
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE event_name = 'session_start'
ORDER BY 1, 3 DESC

To podejście minimalizuje liczbę rozpakowań i przyspiesza wykonanie.

Przykład 3 – funkcje tymczasowe dla wielokrotnego użycia

Utwórz funkcje do wielokrotnego użycia:

CREATE TEMP FUNCTION GetParamString(event_params ANY TYPE, param_name STRING) AS (
(SELECT ANY_VALUE(value.string_value) FROM UNNEST(event_params) WHERE key = param_name)
);
CREATE TEMP FUNCTION GetParamInt(event_params ANY TYPE, param_name STRING) AS (
(SELECT ANY_VALUE(value.int_value) FROM UNNEST(event_params) WHERE key = param_name)
);

SELECT
user_pseudo_id,
event_timestamp,
GetParamInt(event_params, 'ga_session_id') AS ga_session_id,
GetParamString(event_params, 'page_location') AS page_location,
GetParamString(event_params, 'page_title') AS page_title
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE event_name = 'page_view'
AND _TABLE_SUFFIX BETWEEN '20210120' AND '20210131'
AND GetParamString(event_params, 'page_title') = 'Google Online Store'
ORDER BY user_pseudo_id, event_timestamp

Zalety: czytelniejszy kod, mniejsza podatność na błędy, łatwa skalowalność na parametry niestandardowe (np. utm_source).

Praktyczne studia przypadków – analizy marketingowe z UNNEST

Przypadek 1 – aktywność page_view wśród użytkowników z wysokim numerem sesji

Analizuj użytkowników z >10 sesjami, grupując po tytule strony:

SELECT
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS session_number,
COUNT(*) AS event_count
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE event_name = 'page_view'
AND (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') > 10
AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') = 'Home'
GROUP BY event_date, page_title, session_number
ORDER BY event_date DESC, event_count DESC

Wniosek – identyfikuj lojalnych użytkowników (wysoki session_number) i optymalizuj strony o niskim zaangażowaniu.

Przypadek 2 – analiza UTM: źródło i medium dla odsłon

Grupuj odsłony po źródle i medium:

SELECT
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') AS source,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS medium,
COUNT(*) AS page_view_count
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE event_name = 'page_view'
GROUP BY source, medium
ORDER BY page_view_count DESC

Wniosek – „google / cpc” dominuje? Rozważ zwiększenie budżetu PPC.

Przypadek 3 – modelowanie danych: sesje i unikalni użytkownicy po URL

Buduj widok (materialized table) dla dashboardów:

WITH ga4_events AS (
SELECT
PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(_table_suffix,'[0-9]+')) AS event_date,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS url,
user_pseudo_id,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id
FROM `projekt.dataset.events_*`
WHERE REGEXP_EXTRACT(_table_suffix,'[0-9]+') > '20230101'
),
ga4_total_sessions AS (
SELECT
url,
COUNT(DISTINCT CONCAT(user_pseudo_id, session_id)) AS sessions
FROM ga4_events
WHERE url IS NOT NULL
GROUP BY url
)
-- Dodaj JOIN-y dla users, dates itd.
SELECT * FROM ga4_total_sessions;

Zastosowanie – zasilanie Looker Studio dla dziennych raportów konwersji.

Przypadek 4 – normalizacja web vs app (cross-platform)

Web używa session_engaged (string), aplikacja engaged_session_event (int). Normalizuj metrykę w jednym polu:

MAX(
CASE
WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged') = '1' THEN 1
ELSE (SELECT SAFE_CAST(value.int_value AS STRING) FROM UNNEST(event_params) WHERE key = 'engaged_session_event')
END
) AS session_engaged_normalized

Wniosek – jedna spójna metryka ułatwia zintegrowaną analizę web + app.

Najczęstsze błędy i najlepsze praktyki

Najczęstsze błędy

Unikaj poniższych problemów podczas pracy z danymi GA4:

  • błąd #1 – zapominanie o typach wartości → używaj SAFE_CAST lub funkcji jak powyżej;
  • błąd #2 – duplikaty z wielokrotnym UNNEST → stosuj subquery lub QUALIFY ROW_NUMBER() = 1 na deduplikację;
  • błąd #3 – wysokie koszty skanowania → filtruj _TABLE_SUFFIX i korzystaj z partycjonowania tabel.

Najlepsze praktyki

Stosuj poniższe rekomendacje, aby przyspieszyć i uporządkować analizy:

Aspekt Zalecenie
Skala Używaj wildcard events_* z BETWEEN dla dat.
Wydajność Twórz VIEW/MATERIALIZED VIEW zamiast uruchamiać surowe zapytania.
Debug Testuj na zbiorze: bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*.
Integracja Podpinaj do Looker Studio przez custom connector.

Podobne artykuły