В этой статье я расскажу о том, как использовать функцию UNNEST в Google BigQuery для анализа параметров событий и свойств пользователей, которые вы получаете вместе с данными Google Analytics.

От автора перевода:

Изначально я планировал написать собственную полноценную статью, но на глаза попался неплохой англоязычная материал про UNNEST. Поэтому я решил не изобретать велосипед, а просто перевести её для вас.

Если вы интересуетесь анализом данных возможно вам будут интересены мои telegram и youtube канал. Большая часть контента посвящена языку программирования R.

Cложность работы с данными Firebase в BigQuery - и это относится не только к данным Analytics, но и к данным Crashlytics - заключается в том, что ваши данные не организованы в виде простых строк и столбцов, как на примере ниже:

Вместо этого ваши данные состоят из строк, которые больше похожи на объекты JSON, где столбцы могут содержать один бит данных, но они также могут содержать вложенные массивы, структуры или даже массивы структур. В итоге ваши данные на самом деле выглядят так:

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

Давайте начнем с изучения общедоступных данных игры Flood-it, которые мы будем использовать в этой статье в качестве примера.

В частности, меня интересует событие level_complete_quickplay. Это событие, которое мы записываем, когда пользователь успешно завершает уровень в игре. Давайте взглянем на несколько записей:

SELECT *
FROM `firebase-public-project.analytics_153293282.events_20180915`
WHERE event_name = "level_complete_quickplay"
LIMIT 10

Выполнив приведённый выше запрос, вы получите записи событий прохождения уровня, а также несколько других параметров событий, которые могут показаться интересными. У нас есть параметр value, который регистрирует количество ходов, которые потребовались игроку, чтобы пройти уровень, и параметр board, который записывает размер игрового поля (S, M или L).

Вы также можете заметить, что каждый из этих параметров содержит не один столбец значений, а несколько: int_value, string_value, double_value и float_value.

Вы можете рассматривать это как некую структуру, которую мы используем для хранения значений ваших параметров, на практике большинство из них в конечном счёте остаются пустми. Благодаря этой структуре мы можем взять несколько параметров имеющих разный тип данных и сохранить их согласованным образом.

Давайте попробуем посчитать среднюю оценку пользователя, посмотрев на параметры событий, у которых поле key равно “value”. На первый взгляд выглядит довольно просто, не так ли?

SELECT *
FROM `firebase-public-project.analytics_153293282.events_20180915`
WHERE event_name = "level_complete_quickplay"
AND event_params.key = "value"
LIMIT 10

В результате мы получим не особо информативное сообщение об ошибке.

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

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

Для начала рассмотрим более простой пример. Представим, что у нас есть таблица, заполненная информацией о космических кораблях. А экипаж (поле crew) - это массив, очень похожий на event_params в наших таблицах из Analytics.

Если бы я попробовал найти космические корабли, в которых есть член экипажа по имени Zoe, я бы не смог сделать это с помощью приведённого ниже запроса.

SELECT * FROM `spaceships` WHERE crew = "Zoe"

Но я получу нужный результат используя следующий запрос:

SELECT * FROM `spaceships` CROSS JOIN UNNEST(crew) as crew_member

По приведённому выше запросу BigQuery возьмет каждого отдельного члена команды в массиве crew и добавит его в качестве отдельной строки нового столбца crew_member. Он будет добавлять новую строку по мере необходимости для каждого нового значения из исходного массива crew. Анимация более наглядно отобразить этот процесс:

Как только я разобью массив crew на отдельные строки нового столбца crew_member , мне не составит труда выбрать космические корабли с членами экипажа по имени Zoe.

SELECT * FROM `spaceships` 
CROSS JOIN UNNEST(crew) as crew_member 
WHERE crew_member = "Zoe"

На практике большинство пользователей BigQuery заменяют CROSS JOIN запятой, например:

SELECT * FROM `spaceships`,
UNNEST(crew) as crew_member 
WHERE crew_member = "Zoe"

Этот запрос делает то же самое что и предыдущий, но выглядит компактнее и аккуратнее.

Итак, возвращаясь к нашему основному примеру, мы, по сути, имеем массив (или повторяющуюся запись) event_params Если я напишу:

SELECT * FROM `my_analtyics_table`,
UNNEST(event_params) as param

… то BigQuery возьмет каждый отдельный параметр события, развернет его в новый столбец с именем param. Как в следующей анимации:

Обратите внимание, сам параметр param по-прежнему является вложенным объектом. Но это единичная запись, а не повторяющаяся. (Опять же, вроде как структура.) Таким образом, мы можем обращаться к таким полям как param.key или param.value.int_value. В частности, теперь мы можем искать только те строки, в которых param.key равен «value».

Поскольку на практике несколько странно иметь полный повторяющийся объект event_params рядом с каждым невложенным параметром, я обычно отбрасываю его и вместо этого просто использую объект param.

Давайте вернемся к задаче анализа среднего балла пользователей, которые прошли уровень игры. Я могу получить только нужные события вместе с развёрнутым параметром value.

SELECT event_name, param
FROM `firebase-public-project.analytics_153293282.events_20180915`,
UNNEST(event_params) AS param
WHERE event_name = "level_complete_quickplay"
AND param.key = "value"

Теперь мне не составит труда получить фактическое значение параметра value, посмотрев на param.value.int_value.

SELECT event_name, param.value.int_value AS score
FROM `firebase-public-project.analytics_153293282.events_20180915`,
UNNEST(event_params) AS param
WHERE event_name = "level_complete_quickplay"
AND param.key = "value"

Над полученной таблицы я могу производить различные вычисления, например рассчитать среднее значение, получить значения квантилей и стандартного отклонения …

SELECT AVG(param.value.int_value) AS average, 
  APPROX_QUANTILES(param.value.int_value, 2) AS quantiles, 
  STDDEV(param.value.int_value) AS stddev
FROM `firebase-public-project.analytics_153293282.events_20180915`,
UNNEST(event_params) AS param
WHERE event_name = "level_complete_quickplay"
AND param.key = "value"

Пока я рассчитываю описательные статистики, давайте построим гистограмму всех значений!

SELECT param.value.int_value AS score, COUNT(1) AS count
FROM `firebase-public-project.analytics_153293282.events_20180915`,
UNNEST(event_params) AS param
WHERE event_name = "level_complete_quickplay"
AND param.key = "value"
GROUP BY 1
ORDER BY 1

Итак … это интересно. Есть довольно большой пик в районе 21 или 22 ходов… но если вы продолжите поиск, то найдёте еще несколько более мелких пиков в районе 29 ходов и в диапазоне 34–36 ходов.

Причина тому - другой параметр, о котором я говорил - board. Большая часть игр ведется на маленькой доске (S), есть и другие игры, которые проводятся на средних (M) и больших (L) досках, и они, вероятно, составляют меньшие пики.

Фактически, мы можем увидеть это, проанализировав количество ходов с привязкой к размеру игрового поля.

SELECT param
FROM `firebase-public-project.analytics_153293282.events_20180915`,
UNNEST(event_params) AS param
WHERE event_name = "level_complete_quickplay"
AND (param.key = "value" OR param.key = "board")

Всё верно, параметр value , т.е. количество сделанных пользователем ходов, выше, когда игра проходит на поле размером M или L.

Но как мы можем проверить эту теорию в BigQuery? Глядя на полученный выше результат сделать этот анализ довольно сложно, потому что у нас данные о размере поля и количество ходов пользователя для одного события находятся в разных строках.

Есть способ объединить эти строки вместе. Если мы добавим поле pseudo_user_id (по сути, уникальный идентификатор, назначенный каждому экземпляру приложения) и event_timestamp мы сможем сгруппировать параметры для одного и того же события на основе этих двух значений.

SELECT MAX(if(param.key = "value", param.value.int_value, NULL)) AS score,
  MAX(if(param.key = "board", param.value.string_value, NULL)) AS board_type
FROM (
  SELECT user_pseudo_id, event_timestamp, param
  FROM `firebase-public-project.analytics_153293282.events_20180915`,
  UNNEST(event_params) AS param
  WHERE event_name = "level_complete_quickplay"
  AND (param.key = "value" OR param.key = "board")
)
GROUP BY user_pseudo_id, event_timestamp

Затем мы можем проанализировать результат в разрезе размера игрового поля.

SELECT ANY_VALUE(board_type) as board, AVG(score) as average_score
FROM  (
  SELECT MAX(if(param.key = "value", param.value.int_value, NULL)) AS score,
    MAX(if(param.key = "board", param.value.string_value, NULL)) AS board_type
  FROM (
    SELECT user_pseudo_id, event_timestamp, param
    FROM `firebase-public-project.analytics_153293282.events_20180915`,
    UNNEST(event_params) AS param
    WHERE event_name = "level_complete_quickplay"
    AND (param.key = "value" OR param.key = "board")
  )
  GROUP BY user_pseudo_id, event_timestamp
) 
GROUP BY board_type

Такой вариант решения кажется не самым удачным. Есть и более простой способ получить значение размера игрового поля, и количество ходов в одной строке, об этом вы узнаете в моей следующей статье.