Как выгружать данные с вложенной структурой из Google BigQuery на примере пользовательских параметров Google Analytics

Google BigQuery — популярная облачная база данных, которой пользуются компании по всему миру. Она особенно удобна для работы с “сырыми” данными Google Analytics: в GA 360 интеграция с BigQuery настраивается в несколько кликов, а для бесплатной версии существуют сторонние скрипты и модули.

В “сырых” данных Google Analytics каждая запись (строка) соответствует сеансу. Внутри такой записи находятся вложенные поля, которые соответствуют хитам сеанса:

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

На примере пользовательских параметров Google Analytics я постараюсь “на пальцах” объяснить, как хранятся вложенные данные в Google BigQuery и как их можно выгружать.

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

• Выгрузка строк
• Выгрузка с сохранением структуры вложенности
• Пример замены значений пользовательских параметров

Основы

BigQuery поддерживает 2 диалекта SQL: Legacy и Standard. Google рекомендует использовать более новый SQL Standard, на нем мы и будем писать запросы для выгрузки.
Все, кто хоть немного работал с SQL, знают стандартную конструкцию запроса:

Такая конструкция работает, если структура таблицы простая, без других, вложенных в ячейки полей:

Мы же рассматриваем таблицы с вложенными полями. Структура такой таблицы (например, пользовательские параметры Google Analytics):

Пользовательские параметры GA в BQ

В Google BigQuery у такой таблицы будут следующие названия столбцов (разделитель «.» показывает структуру вложенности):

Так как же нам выгрузить данные из вложенных полей?

Выгрузка строк

Вернемся к таблице с примером пользовательских параметров GA в BQ.

Столбцы customDimensions.index и customDimensions.value — это индексы и значения сессионных и пользовательских Custom Dimensions.

Столбцы hits.customDimensions.index и hits.customDimensions.value — индексы и значения хитовых Custom Dimensions.

В Google BigQuery есть еще один уровень действия пользовательских параметров — товар. Названия и значения товарных Custom Dimensions в Google BigQuery находятся в столбцах hits.product.customDimensions.index и hits.product.customDimensions.value. Они выгружаются по аналогии с хитовыми пользовательскими параметрами, необходимо лишь учесть еще один уровень вложенности.

Пользовательские параметры сессионного и пользовательского уровня

Как поступить, если нам необходимо для каждой даты выгрузить значения сессионных (пользовательских) Custom Dimensions без сохранения вложенной структуры (то есть построчно)?

Для ответа на вопрос давайте еще раз внимательнее посмотрим на таблицу с пользовательскими параметрами GA в BQ.
В ней видно, что значения ячеек столбца customDimensions представляют из себя еще одну таблицу:

Достаточно сделать подзапрос к этой таблице в основном запросе:

На выходе получаем таблицу:

Если нам нужно добавить столбец со значением другого пользовательского параметра — делаем еще один подзапрос:

Получаем следующее:

Пользовательские параметры хитового уровня

Хитовые пользовательские параметры выгружаются аналогично сессионным (пользовательским), за исключением того, что подзапрос нужно делать к вложенной таблице hits. Другими словами, значения ячеек столбца hits в таблице “сырых” данных Google Analytics представляет из себя вложенную таблицу, в которую вложена таблица customDimensions:

Запрос для выгрузки построчно хитовых пользовательских параметров будет такой:

Результатом выполнения запроса станет таблица:

Можно выгрузить несколько хитовых пользовательских параметров и добавить параметр hitNumber (порядковый номер хита в сессии):

Получим таблицу:

Сессионные (пользовательские) + хитовые пользовательские параметры

Если в одном запросе мы хотим выгрузить сессионные и хитовые пользовательские параметры, необходимо всего лишь сделать нужные подзапросы к основной и к вложенным таблицам:

Таблица, которая будет получена в результате выполнения запроса:

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

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

Пример
В Google Analytics в сессионный пользовательский параметр с индексом 12 и в хитовой пользовательский параметр с индексом 25 для пользователей из России передается название страны в полном формате: RUSSIA. Необходимо поменять формат страны на сокращенный: RUS.

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

Порядок решения задачи:

  1. Выгружаем все данные с сохранением структуры вложенности
  2. Заменяем значение пользовательского параметра со страной
  3. Перезаписываем таблицу

Для выгрузки данных с сохранением структуры вложенности необходимо использовать функцию ARRAY и конструкцию SELECT AS STRUCT. Разберемся, что это такое.

Синтаксис функции ARRAY следующий:

Она возвращает массив элементов.

Сравнение массива с построчной записью:

Слева — массив, справа — построчная запись

Если мы хотим сохранить вложенную структуру и выгрузить массив с несколькими колонками, необходимо использовать ARRAY(SELECT AS STRUCT …):

Массив с вложенной структурой

Пользовательские параметры сессионного и пользовательского уровня

Для выгрузки с сохранением структуры сессионных (пользовательских) Custom Dimensions используем запрос:

В результате его выполнения получается таблица, в которой сохранена структура вложенности “сырых” данных Google Analytics:

Пользовательские параметры хитового уровня

Для выгрузки значений хитовых пользовательских параметров из Google BigQuery с сохранением структуры вложенности важно учесть, что таблица customDimensions вложена в таблицу hits. Другими словами, необходимо 2 раза сделать подзапрос ARRAY(SELECT AS STRUCT…): сначала к вложенной таблице hits, потом к вложенной в нее таблице customDimensions:

Результатом такого запроса будет таблица:

Сессионные (пользовательские) + хитовые пользовательские параметры

Как и при построчной выгрузке, нам необходимо объединить в одном запросе подзапросы ARRAY(SELECT AS STRUCT…) к нужным вложенным таблицам:

Что получается в результате:

Пример замены значений пользовательских параметров

Вернемся к нашему примеру.
В предыдущем разделе мы получили запрос для выгрузки сессионных (пользовательских) и хитовых пользовательских параметров Google Analytics с сохранением структуры вложенности.
Дополним этот запрос конструкциями SELECT *REPLACE для выгрузки с заменой и CASE для обновления значений нужных пользовательских параметров:

В результате выполнения данного запроса мы получим оригинальную таблицу с “сырыми” данными из Google Analytics. У неё полностью сохранится оригинальная структура вложенности, но значения нужных пользовательских параметров изменятся на новые.

Тема работы с вложенной структурой данных в Google BigQuery не относится к легким.

Надеюсь, у меня получилось внести ясность в этот вопрос. Но, напомню, лучший способ научиться делать что-то — это больше практиковаться.

Источник