Praca z danymi GA4 w BigQuery, jak radzić sobie z polami w trybie “REPEATED”
Tutorial na konkretnym przykładzie prezentuje jak przygotować zapytanie do wyeksportowanej tabeli danych z GA4 do BigQuery w którym wykorzystywane są pola typu RECORD, a których tryb zdefiniowany został jako REPEATED. Takich pól w tabelach eksportowanych z Google Analytics 4 jest kilka, standardowo są to pola: event_params, user_properties i items.
Opis zadania i wgląd w zawartość tabeli danych
Sklep internetowy dla którego pracuję, do GA4 wysyła jako parametr zdarzenia informację o swojej dostępności. ( Nie jest to standardowe rozwiązanie i jeśli chcesz, by działało ono także u Ciebie, musisz dodać ten parametr do swojej ramki DataLayer). Produkty dostępne na magazynie uzyskują wartość parametru “in stock”, niedostępne na magazynie “out of stock”. Na podstawie tych danych chcemy określić jak dużo wyświetleń wszystkich stron produktów przypada na produkty, których użytkownik nie może kupić, gdyż nie są dostępne na magazynie. Możemy wylistować jak dużo posiadamy takich produktów, które z niedostępnych produktów są najczęściej wyświetlane.
Dana zawierająca informację o dostępności produktu po imporcie do BigQuery znajduje się w jednym z rekordów event_params zdarzenia “view_item”.
Schemat tabeli danych z GA4 wygląda następująco:
Zapytanie umożliwiające wybór okresu czasowego przeszukiwanych danych zaimportowanych z GA4
Tabele danych z Google Analytics 4 w BigQuery wyglądają dość specyficznie. Na przykładzie udostępnionych danych demo od Google, w eksploratorze bazy danych, możemy zobaczyć wszystkie dane jako zgrupowany zestaw 92 tabel, zwierających informacje o zdarzeniach z każdego dnia kalendarzowego. W przypadku danych demo Google, obejmują one okres 3 miesięcy: od 1 listopada 2020 do 31 stycznia 2021.
W związku z takim zgrupowaniem tabel, gdy zadamy do bazy następujące zapytanie:
SELECT
*
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
WHERE
event_name = 'purchase'
Otrzymamy tylko informacje o zdarzeniach jakim było złożenie zamówienia na sklepie Google w dniu 31 stycznia 2021 roku. Takich zamówień było 19.
Jeśli chcemy rozszerzyć zakres zapytania o większą liczbę dni, musimy skorzystać z sufiksu tabeli, który zdefiniuje początkową datę, a więc i nazwę tabeli danych, które przeszukamy.
Spróbujmy sprawdzić ile zakupów zrobiono w sklepie Google między 29.01 a 31.01.2021 roku.
Modyfikujemy zapytanie, które będzie wyglądać następująco:
SELECT
*
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
_TABLE_SUFFIX > '20210128'
AND event_name = 'purchase'
Jako wynik uzyskujemy 104 rekordy danych. Zwróć uwagę, że w odwołaniu do nazwy tabeli w zapytaniu zamiast konkretnego sufiksu tabeli, pojawia się gwiazdka. Wartość sufiksu definiujemy w warunku zapytania: _TABLE_SUFFIX > ‘20210128’
Umiemy zatem już wybierać okres czasu w którym mamy dokonać obserwacji.
Wyszukujemy wszystkie zdarzenia "view_item" w których wyświetlono produkty z parametrem "out of stock"
Jak wspominałem wcześniej status “out of stock” w mojej tabeli danych znajduje się w jednym z rekordów danych event_params. Sprawdźmy co się stanie, gdy spróbujemy go wywołać bezpośrednio w warunku zapytania do bazy.
SELECT
*
FROM
`NAZWA_PROJEKTU.analytics_288901227.events_*`
WHERE
_TABLE_SUFFIX > '20230209'
AND event_name = 'view_item'
AND event_params.key = 'stock_status'
AND event_params.value.string_value = 'out of stock'
BigQuery zwróci nam błąd:
Cannot access field key on a value with type ARRAY<STRUCT...
Chcąc odszukać w tabeli rekord o wartości “out of stock”, który jest jednym z wielu parametrów danego zdarzenia musimy wykorzystać polecenie UNNEST.
Zwróć uwagę jak powinno wyglądać zapytanie z prawidłowym użyciem tej komendy.
SELECT
*
FROM
`NAZWA_PROJEKTU.analytics_288901227.events_*`,
UNNEST(event_params) AS event_params
WHERE
_TABLE_SUFFIX > '20230209'
AND event_name = 'view_item'
AND event_params.key = 'stock_status'
AND event_params.value.string_value = 'out of stock'
Powyższe zapytanie zostanie przetworzone już prawidłowo.
Zawężamy liczbę danych, by móc łatwiej wyciągać wnioski
Do opracowania raportu na temat liczby wyświetleń niedostępnych produktów nie potrzebujemy ogromnej liczby informacji “wypluwanych” przez powyższe zapytanie. Zbędne są informacje o poszczególnych wyświetleniach produktów, a konieczna będzie statystyka pozwolająca uzyskać dane na temat tego ile razy w rozpatrywanym okresie dany produkt o statusie “out of stock” był wyświetlony. Zatem jako dane wsadowe potrzebujemy rekord zawierający informację o statusie dostępności, a także SKU produktu i/lub jego nazwę.
Identyfikator produktu i jego nazwa w tabeli danych z Google Analytics 4 znajduje się w zagnieżdżonych rekordach pola “items“, konkretnie są to pola: item_id i item_name.
Ponieważ “items” to pole z trybem “repeated”, znów korzystam z polecenia UNNEST by dostać się do zawartych w nim danych.
SELECT
items.item_id AS SKU,
items.item_name AS item_name,
event_params.value.string_value AS value
FROM
`NAZWA_PROJEKTU.analytics_288901227.events_*`,
UNNEST(event_params) AS event_params,
UNNEST(items) AS items
WHERE
_TABLE_SUFFIX > '20230209'
AND event_name = 'view_item'
AND event_params.key = 'stock_status'
AND event_params.value.string_value = 'out of stock'
Otrzymujemy w wyniku tego zapytania już bardzo przyjaźnie wyglądającą tabelę, która listuje rekordy produktów ze statusem “out of stock” wyświetlonych w dniach od 10.02.2023.
Obliczamy liczbę wyświetleń poszczególnych produktów, przygotowanie końcowej tabeli danych
Poprzednie zapytanie przygotowało nam bardzo przejrzysty zestaw danych. Potraktujmy je jako tymczasową tabelę, którą grupujemy po ID produktu, jednocześnie zliczając liczbę rekordów wyświetleń danego produktu jakie się w nim znalazły za danych okres.
W tworzeniu docelowego zapytania i stworzeniu tabeli tymczasowej wykorzystamy polecenie WITH. Do zliczenia liczby rekordów wykorzystamy funkcję COUNT.
WITH
PREP_ALL AS(
SELECT
items.item_id AS SKU,
items.item_name AS item_name,
event_params.value.string_value AS value
FROM
`NAZWA_PROJEKTU.analytics_288901227.events_*`,
UNNEST(event_params) AS event_params,
UNNEST(items) AS items
WHERE
_TABLE_SUFFIX > '20230209'
AND event_name = 'view_item'
AND event_params.key = 'stock_status'
AND event_params.value.string_value = 'out of stock')
SELECT
SKU,
item_name,
COUNT(value) AS item_views,
'out of stock' AS flag
FROM
PREP_ALL
GROUP BY
PREP_ALL.SKU,
PREP_ALL.item_name
ORDER BY
item_views DESC
LIMIT
10
Jak widać powyżej nasze zapytanie z poprzedniego paragrafu znalazło się w nawiasach definiujących tymczasową tabelę PREP_ALL. Następnie pobieramy dane z tej tabeli grupując ją według danych produktowych i zliczając liczbę rekordów wystąpienia danego produktu w wyświetleniach. Dopisujemy do wyniku kolumnę z statusem niedostępności “out of stock”. Wyświetlamy ją porządkując wg. liczby wyświetleń malejąco. Ograniczyliśmy liczbę wyświetleń do 10 najpopularniejszych wyników.
Z takimi danymi możemy już spokojnie udać się np. do działu zakupu odpowiedzialnego za zaopatrzenie naszego sklepu.
