Как сервис BigQuery помогает интернет-маркетологу: несколько приёмов с SQL и визуализация отчётов в Google Data Studio

---

Какие ограничения Firebase Analytics существуют и что с ними делать

Чтобы описать свой опыт решения этой проблемы, обратимся к приложению по продаже авиабилетов. Когда отключился Google Analytics и на смену пришёл Firebase Analytics, перед нами встала задача сохранить для заказчика прежнюю глубину анализа пользовательского поведения, оставить возможность быстро настраивать новые нестандартные отчёты и при этом обеспечить красивую доступную визуализацию.

В Google Analytics мы могли посмотреть, на какие экраны ходят пользователи, на какие направления ищут билеты, из каких они городов, сколько из них авторизованы в приложении, а сколько — анонимны. Плюс мы всегда видели, на какую сумму купили билетов на каждое направление, как выросли продажи на определённые направления после рекламной акции и так далее. С Firebase Analytics эта вторая часть статистики, в которой мы могли подробно анализировать конверсию, была доступна только в сыром виде, значит, нужен способ её обогатить.

Вот что мы можем сделать в Firebase Analytics:

• настраиваем события покупки;
• в параметрах указываем название товара — направление или его идентификатор и цену;
• после, в отчёте на сайте, мы можем посмотреть, сколько билетов на какой рейс было продано, среднюю цена покупки, на какую сумму всего было совершено покупок.

Здесь мы видим, на какое направление какое количество билетов каким количеством пользователей куплено. Заказчик хочет знать, например, на какую сумму купили билетов на направление «Екатеринбург-Москва». Таких ответов Firebase Analytics не даёт.

Информативность отчёта ограничивается стандартным набором параметров — мы видим только общую картину.

Другой пример анализа данных, который в нашем случае не удалось в полной мере реализовать в Firebase Analytics: в приложении показывается внутренняя реклама рейсов и направлений. Заказчик хотел знать, сколько пользователей, посмотревших рекламу, впоследствии купили билеты. И, конечно же, с разбиением дохода по товарам, акциям и так далее. И снова стандартные средства не дали нам такой возможности.

Что происходит после подключения

После подключения BigQuery к Firebase Analytics мы получаем возможность видеть данные, которые собираются в сыром виде. Мы получаем доступ только к тем данным, которые собирались после подключение BigQuery к нашему проекту. Если вы сегодня подключаете BigQuery, то можете обрабатывать данные, которые получены, начиная с сегодняшнего дня, вчерашних данных не будет.

Итак, мы всё подключили, заходим на главную страницу сервиса. В ресурсах мы видим наш проект. В данных одну таблицу — events. Сюда скапливаются все данные из Firebase Analytics.

На самом деле, это не одна таблица. Данные за каждый день помещаются в таблицу с названием events_<дата>, например events_20200308.

Посмотрим на сами данные. Все события из Firebase Analytics записаны в таблицы events_*. Каждая строка в таблице — отдельное событие. Многочисленные столбцы представляют собой параметры события: дата, информация об устройстве, информация о пользователе и так далее. Хоть данные и отображаются в виде таблицы, она не совсем обычная. Это скорее табличное представление древовидной структуры. Ниже представлен пример JSON-структуры строки таблицы. Для краткости в структуру внесены не все данные, но общую картину понять по ней можно:

Посмотрев на структуру данных, можно заметить, что в ней есть:

• Простые поля. Это простые значения, какой-то столбец в строке. Пример: event_date, event_timestamp, event_name.
• Поля-массивы. Как, например, event_params или user_properties. Каждая строка в таблице — событие. А у события может быть несколько параметров и свойств пользователя. Поэтому тут такая связь — один-ко-многим. Пример того, как работать с таким полями в запросах будет
• Поля-объекты. Пример — device. Эти поля можно воспринимать как набор полей. То есть в данном случае это просто три поля — device.category, device.operating_system и device.operating_system_version.

Если сначала структура данных кажется сложной, то при внимательном рассмотрении всё становится проще. В конечном итоге, у нас в руках информация по всем событиям из Firebase Analytics. И нам просто надо вытащить из неё те данные, которые нам нужны.
Попробуем сделать какие-нибудь запросы. Например, выведем все даты событий:

Увидим результат:

project_name.data_set.events_20200202 в этом случае — название конкретной таблицы, которое складывается из названия проекта, названия набора данных и ежедневной таблицы с событиями из Firebase Analytics. То есть в данном запросе мы получили даты событий из таблицы, в которой были события за 2 февраля 🙂 Не очень полезно, но как пример запроса сойдёт. В реальности скорее пригодится делать выборку из всех доступных данных. В этом случае можно вместо конкретной таблицы указывать project_name.data_set.events_*. Добавим полезности в запрос и выясним, например, даты и города событий с названием «booking_purchase»:

Получим:

Интерес вызывают только особые поля в таблице — массивы. Например, event_params. Для работы с такими полями рекомендуется использовать оператор UNNEST. Этот оператор берёт поле-массив и превращает его в таблицу.

Улучшим наш запрос и выведем значение параметра «direction»:

Результат:

Итак, что мы добавили. Мы применили оператор UNNEST к полю event_params. В результате получили таблицу, в которой строчки — параметры события, а столбцы — свойства этих параметров. Свойств у параметров два: key и value. value — это объект с 4 полями: string_value, int_value, float_value и double_value. Эти поля нужны для разных типов данных, ведь значение параметра может быть строкой, int, float, double. Затем через подзапрос мы вытащили строковое значение параметра с полем key равным direction. Таким вот образом можно работать с полями-массивами в таблице.

Давайте получим то, что нам не смогла дать Firebase Analytics — разбивку дохода по каждому проданному в приложении товару:

 В Firebase Analytics мы передаём событие покупки «booking_purchase».

 В нём мы передаём два параметра: «direction» и «price». direction — идентификатор продукта, price — его цена.

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

Результат:

Мы получили те данные, которые хотели.

---

Как визуализировать отчёты в Data Studio

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

Результаты запросов можно визуализировать в сервисе Data Studio. Сервис позволяет представить данные в виде таблиц, графиков, диаграмм, по красоте и функциональности не уступающих аналогичным из Firebase Analytics. Посмотрим, как это можно сделать.

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

Отчёт можно построить по таблице, сохранённому представлению или непосредственно запросу. Последний вариант позволяет использовать параметры даты. Используя эти параметры, можно ограничивать выборку данных по дате, тем самым оптимизируя объём обрабатываемых данных. Результат напоминает интерфейс Google Analytics и Firebase — примерно такие же формы, функции. Корпорация как будто взяла свои наработки в плане визуализации и сделала их общедоступными:

Мы дописали условие, чтобы выборка была только по тем событиям, которые произошли между параметрами DS_START_DATE и DS_END_DATE. Эти параметры будут передаваться в запрос непосредственно из форм отчёта. Создаём отчёт и сразу видим примерно такую картину:

Далее можно добавить выбор диапазона дат. Для этого добавим на форму соответствующий компонент:

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

Таким же образом можно добавить на форму и настроить другие компоненты — графики, диаграммы, изображения, текст и так далее. После этого отчётом можно поделиться через шеринг ссылки или предоставив доступ нужным аккаунтам.