Использование функции UNNEST в Google BigQuery

Данная статья посвящена использованию функции UNNEST в Google BigQuery для анализа параметров событий и свойств пользователей, которые вы получаете вместе с данными Google Analytics 4 / Firebase

Проблема работы с данными из Firebase состоит в том, что данные имеют вид не простых строк и столбцов, как мы привыкли видеть, например,

а вот такое

На практике в BigQuery это выглядит так:

Как видно само использование и анализ такой структуры не тривиальная задачка.

Введем запрос чтобы посмотреть информацию по событию пользователя "user_date_event"

SELECT *
FROM `business-metric.analytics_319368526.events_*`
WHERE event_name = 'user_data_event'
LIMIT 2

Как видим у события есть еще дополнительные параметры которые передаются вместе с ним такие как:

page_location
term
source
campaign
ga_session_id
page_title
ga_session_number
session_engaged
cid_ga4
medium
content

Такой тип поля называется RECORD (int_value, string_value, double_value и float_value). И как видим значения разделяются еще на столбцы, строковые, числовые, число с плавающей запятой. Например, поле page_title имеет формат - "строка", то значение будет находиться только в string_value, а в остальных будет null, так же как и в параметре события ga_session_number значение есть только в поле int_value, а в остальных null

Кажется все просто, чтобы посчитать количество события с источником трафика, например, yandex нужно написать такой запрос

SELECT *
FROM `business-metric.analytics_319368526.events_*`
WHERE event_name = 'user_data_event' and event_params.key = 'source' 
and event_params.value.string_value = 'yandex'

Но нет, так не получится, еще при написании запроса выдается ошибка.

Проблема здесь в том, что поле event_params по сути является массивом (на самом деле, говоря языком BigQuery, это «повторяющаяся запись» (record), но вы можете представлять её как массив). Таким образом, хотя event_params может содержать несколько строк, которые сами по себе имеют поле key, само поле event_params его не содержит.

Вот тут нам и пригодится функция UNNEST. Она позволяет вам взять массив, и развернуть его элементы в отдельные строки. Затем вы можете применить любое логическое выражение в блоке WHERE к каждому отдельному элементу этого массива.

SELECT
  event_name, param
FROM `business-metric.analytics_319368526.events_*`,
  UNNEST(event_params) as param
WHERE event_name = 'user_data_event' and param.key = 'cid_ga4'
limit 5

То есть теперь мы можем в блоке с WHERE выбрать нужный нам параметр события и его значение.

Вернемся к примеру в котором у нас получилась ошибка и используем функцию unnest

SELECT count(event_name) as count_event, param.key as event_parameter,param.value.string_value as source
FROM `business-metric.analytics_319368526.events_*`,
UNNEST(event_params) as param
WHERE event_name = 'user_data_event' and param.key = 'source' 
and param.value.string_value = 'yandex'
group by param.key,param.value.string_value

То есть мы, выбираем событие "user_data_event" в котором есть параметр "source" и этот параметр равен "yandex" которых находится в поле string_value. В начале запроса просто подсчитываем количество таких событий.

У нас получилось 175 событий из яндекса

Посмотрим какие вообще были источники трафика, список

SELECT distinct param.value.string_value as source
FROM `business-metric.analytics_319368526.events_*`,
UNNEST(event_params) as param
WHERE  param.key = 'source'

То есть как работает UNNEST на примере одной записи из нашей таблицы

Видимо, что описанный прием будет работать, только если вы запрашиваете не более одного значения из каждого вызова SELECT FROM UNNEST.
Вы также можете использовать SELECT FROM UNNEST несколько раз в одном и том же операторе SELECT!

Усложним запрос добавив в него несколько параметров события

SELECT event_name, event_timestamp, user_pseudo_id,  
  (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
FROM `business-metric.analytics_319368526.events_*`
WHERE event_name = 'user_data_event'

Так выглядит запись с выводом двух параметров события "user_data_event"

Таким образом можно добавить хоть все параметры события. Это время затратная работа, так как нужно сначала извлечь все параметры из каждого события и только потом в "WHERE key = '.....'" вводить название каждого параметра. Но другого пути нет.

Дополню пример добавив в него свойства пользователя "user_properties"

SELECT event_name, event_timestamp, user_pseudo_id,  
  (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,
  (SELECT value.string_value FROM UNNEST(user_properties) 
    WHERE key = 'client_id_ga4') AS cid, 
FROM `business-metric.analytics_319368526.events_*`
WHERE event_name = 'user_data_event'

Как видите, синтаксис не изменился, просто после запятой добавили еще один UNNEST

Посмотрим на полный путь по шагам по превращению данных в функциональный вид

Есть такая структура таблицы с видом

1. Разберемся, какие вообще события у нас есть и какие важны для нашего будущего отчета, например, в Google Data Studio

Для этого напишем просто запрос

select distinct event_name from `business-metric.analytics_319368526.events_*`

Результатом будет список неповторяющихся событий

Для примера возьмем три:

first_visit
user_data_event
click_phone_number

2. Теперь нужно понять какие параметры событий передаются вместе с этими тремя событиями (если вы не знаете)

select distinct params
from (
select params.key as params
from `business-metric.analytics_319368526.events_*`,
unnest (event_params) as params
where event_name in( 'first_visit', 'user_data_event', 'click_phone_number'))

Получили список параметров событий:

Все использовать не будем, а для примера возьмем несколько:

page_referrer
ga_session_id
page_title
element_location

Чтобы понять какой тип имеет параметр напишем запрос

select distinct params,string, value
from (
select params.key as params,params.value.string_value as string,params.value.int_value as value
from `business-metric.analytics_319368526.events_*`,
unnest (event_params) as params
where event_name in( 'first_visit', 'user_data_event', 'click_phone_number'))

В результате мы увидим какому типу принадлежит запись определенного параметра

Можно пройтись взглядом и найти выбранные нами параметры события или добавить еще одно условие в WHERE

Например, какой тип имеет "element_location"

select distinct params,string, value
from (
select params.key as params,params.value.string_value as string,params.value.int_value as value
from `business-metric.analytics_319368526.events_*`,
unnest (event_params) as params
where event_name in( 'first_visit', 'user_data_event', 'click_phone_number'))
where params = 'element_location'

В результате мы увидели что значение в параметре "element_location" имеет тип STRING

Запишем для каждого параметра события тип значения

page_referrer - STRING.VALUE
ga_session_id - INT.VALUE
page_title - STRING.VALUE
element_location - STRING.VALUE

Параметры события пока оставим, у нас еще есть свойства пользователя.

3. Узнаем какие передаются свойства пользователя с нашими выбранными событиями first_visit, user_data_event, click_phone_number
Пишем такой же запрос, только вместо event_params вставляем user_properties

select distinct properties
from (
select properties.key as properties
from `business-metric.analytics_319368526.events_*`,
unnest (user_properties) as properties
where event_name in( 'first_visit', 'user_data_event', 'click_phone_number'))

В свойства пользователя попадает только одно

client_id_ga4

Узнаем какой тип имеет эта переменная

select distinct properties, string, value
from (
select properties.key as properties,properties.value.string_value as string,properties.value.int_value as value
from `business-metric.analytics_319368526.events_*`,
unnest (user_properties) as properties
where event_name in( 'first_visit', 'user_data_event', 'click_phone_number'))

Переменная записана с типом STRING
То есть:

client_id_ga4 - STRING.VALUE

4. Определимся что мы хотим видеть из всего этого набора данных, какие параметры и показатели

user_pseudo_id, event_date, event_name, device.category, device.browser,platform, user_first_touch_timestamp, traffic_source.source, traffic_source.medium, geo.country. Для примера достаточно.

5. Финальный запрос

WITH flat_table AS (
SELECT
  user_pseudo_id,
  PARSE_DATE('%Y%m%d',event_date) AS date,
  user_first_touch_timestamp,
  device.category,
  event_name,
  traffic_source.source AS source,
  traffic_source.medium AS medium,
  CONCAT(traffic_source.source,' / ',traffic_source.medium) AS source_medium,
  (SELECT value.string_value FROM UNNEST(user_properties) 
    WHERE key = 'client_id_ga4') AS cid,
  (SELECT value.string_value FROM UNNEST(event_params) 
    WHERE key = 'page_referrer') AS page_referrer ,
  (SELECT value.int_value FROM UNNEST(event_params) 
    WHERE key = 'ga_session_id') AS ga_session_id ,
  (SELECT value.string_value FROM UNNEST(event_params) 
    WHERE key = 'page_title') AS page_title,
  (SELECT value.string_value FROM UNNEST(event_params) 
    WHERE key = 'element_location') AS element_location,
  device.browser,
  geo.city,
  platform
from `business-metric.analytics_319368526.events_*`
where event_name in( 'first_visit', 'user_data_event', 'click_phone_number')
     )    
SELECT * from flat_table

Результат - таблица с которой можно работать. Сохранить как view или другими способами.

Проверим параметр события "element_location". Так как не нашел в списке при поверхностном осмотре. Добавим в конце большого запроса для проверки ↑

SELECT
  *
FROM
  flat_table
  where element_location is not null

Как оказалось был только один клик по номеру телефона, и только в этом событии присутствует параметр "позиция элемента"

Если вас что-то смущает в данных, таким способом можно проверять и сверять данные с аналитикой.