Данная статья посвящена использованию функции 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 нужно написать такой запрос
Но нет, так не получится, еще при написании запроса выдается ошибка. Проблема здесь в том, что поле event_params по сути является массивом (на самом деле, говоря языком BigQuery, это «повторяющаяся запись» (record), но вы можете представлять её как массив).
Таким образом, хотя event_params может содержать несколько строк, которые сами по себе имеют поле key, само поле event_params его не содержит. Вот тут нам и пригодится функция UNNEST . Она позволяет вам взять массив, и развернуть его элементы в отдельные строки. Затем вы можете применить любое логическое выражение в блоке WHERE к каждому отдельному элементу этого массива.
То есть теперь мы можем в блоке с WHERE выбрать нужный нам параметр события и его значение.
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'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
Вернемся к примеру в котором у нас получилась ошибка и используем функцию unnest
То есть мы, выбираем событие "user_data_event" в котором есть параметр "source" и этот параметр равен "yandex" которых находится в поле string_value. В начале запроса просто подсчитываем количество таких событий.
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
У нас получилось 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! Усложним запрос добавив в него несколько параметров события
Так выглядит запись с выводом двух параметров события "user_data_event"
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'
Таким образом можно добавить хоть все параметры события. Это время затратная работа, так как нужно сначала извлечь все параметры из каждого события и только потом в "WHERE key = '.....'" вводить название каждого параметра. Но другого пути нет. Дополню пример добавив в него свойства пользователя "user_properties"
Как видите, синтаксис не изменился, просто после запятой добавили еще один UNNEST
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'
Посмотрим на полный путь по шагам по превращению данных в функциональный вид Есть такая структура таблицы с видом
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"
В результате мы увидели что значение в параметре "element_location" имеет тип STRING
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'
Запишем для каждого параметра события тип значения 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_numbe r Пишем такой же запрос, только вместо event_params вставляем user_properties
В свойства пользователя попадает только одно client_id_ga4
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'))
Узнаем какой тип имеет эта переменная
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. Финальный запрос
Результат - таблица с которой можно работать. Сохранить как view или другими способами.
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
Проверим параметр события "element_location". Так как не нашел в списке при поверхностном осмотре. Добавим в конце большого запроса для проверки ↑
SELECT
*
FROM
flat_table
where element_location is not null
Как оказалось был только один клик по номеру телефона, и только в этом событии присутствует параметр "позиция элемента" Если вас что-то смущает в данных, таким способом можно проверять и сверять данные с аналитикой.
