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

Игорь Галичин, руководитель отдела мобильной разработки компании Axmor, рассказал блогу Нетологии о том, как маркетологу со знанием SQL (либо с небольшой помощью коллег-инженеров) обойти проблему с предоставлением пользовательского поведения в Android-приложениях.

Статья пригодится тем, кто работает с Android-приложениями, запускает в них рекламные кампании, анализирует результаты, и кто огорчён, что больше для этого нельзя использовать Google Analytics. В статье есть инструкция, как подключить BigQuery к Firebase и красиво визуализировать в Data Studio.

Компания Axmor разрабатывает мобильные приложения с 2010 года. В 2016 году одна из крупнейших авиакомпаний России заказала нам доработку и последующее развитие своего клиентского Android-приложения для продажи билетов. В том числе, в наши задачи входила разработка инструментов для сбора аналитики по рекламным акциям, которые регулярно проходят внутри приложения, и предоставление данных в наглядном виде. 

До 2020 года для этих целей — а также и в других своих приложениях — мы использовали Google Analytics. Но 4 февраля корпорация отключила такую возможность и рекомендовала перейти на Firebase Analytics. Оказалось, что эта SDK (от англ. software development kit) не даёт всех возможностей, которые предоставляла предыдущая, в частности, не позволяет строить нестандартные отчёты.   

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

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

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

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

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

Информация на иллюстрациях не соответствует реальной, все цифры изменены в интересах коммерческой тайны. На содержательность и наглядность примера это не влияет, нам принципиально лишь показать возможности Firebase.

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

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

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

Как использовать BigQuery для анализа продаж внутри Android-приложения

Мы начали искать способ, как получить быстрые наглядные отчёты в разных разрезах. В случаях, когда нужен более глубокий анализ данных, Google советует подключить веб-сервис BigQuery. Но в нашем понимании это было как из пушки по воробьям, ведь инструмент заявляется для работы с большими данными. Однако при детальном изучении инструмента оказалось, что он подходит даже под задачи, где требуется анализ сравнительно небольшого количества данных, но при этом нестандартный. Вот уж точно, за последние пару лет сменилась концепция Big Data — теперь это всё, что неудобно обрабатывать в Excel.

Подключение BigQuery

Подключение BigQuery к Firebase Analytics простое. Почти на каждой странице Firebase Analytics Google предлагает это сделать. Есть подробная инструкция для этого.

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

На бесплатном тарифе через BigQuery можно получить доступ только к данным Crashlytics, Predictions, Cloud Messaging и Performance Monitoring. 

Надо понимать, что BigQuery — не часть Firebase Analytics. Это отдельный сервис Google, сделанный для обработки больших массивов данных. В данном случае Firebase Analytics для BigQuery — один из возможных источников данных. Подключение BigQuery даст возможность находить корреляции и больше инсайтов. 

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

После подключения 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. И нам просто надо вытащить из неё те данные, которые нам нужны. 
Попробуем сделать какие-нибудь запросы. Например, выведем все даты событий:
    

SELECT event_date

FROM `project_name.data_set.events_20200202`

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

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

SELECT geo.city, event_date

FROM `project_name.data_set.events_*`

WHERE event_name = "booking_purchase" and geo.city != ""

Получим:

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

SELECT 
geo.city, 
event_date,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = "direction") AS direction,
    	
FROM
`project_name.data_set.events_*`
    	
WHERE
event_name = "booking_purchase" and geo.city != ""

Результат:

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

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

1. В Firebase Analytics мы передаём событие покупки "booking_purchase". 
2. В нём мы передаём два параметра: "direction" и "price". direction — идентификатор продукта, price — его цена.

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

SELECT
  	
direction,
  	
count(direction) as count,
  	
sum(price) as total_sum
FROM
(
    	
SELECT
      	
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = "direction") AS direction,
      	
(SELECT value.double_value FROM UNNEST(event_params) WHERE key = "price") AS price
    	
FROM
        	
`project_name.data_set.events_*`
    	
WHERE
      	
event_name = "booking_purchase"
      	
)
group by direction
order by total_sum desc

Результат:

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

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

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

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

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

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

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

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

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

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

BiqQuery — эффективный инструмент, которого не стоит бояться

Мобильные приложения — это мощный инструмент продаж и маркетинга, особенно, когда они используют подход, основанный на данных. Не стоит бояться BiqQuery, и считать, что этот инструмент сложный, и вообще, Big Data — это слишком круто для вас. BigQuery поднимет ваш отдел аналитики на уровень Spotify, Delivery Food и других гигантов в области данных и предоставит ту же производительность, которой пользуются они, за совсем небольшую цену, при помощи простейшего SQL, освоить который — миссия каждого прогрессивного аналитика, хоть в маркетинге, хоть в продукте. 

Преимущества BigQuery:

• Быстро настраивается и позволяет за считанные секунды обработать данные. Без серверов, дорогостоящей инфраструктуры и администратора. 
• Возможность управлять процессом анализа данных, выстраивать сложные схемы корреляции, кастомизировать формат отчётов под себя, извлекать из своих данных максимум информации: сегментировать клиентов по любой комбинации параметров, анализировать движение клиента через воронку продаж, оценивать вклад рекламных кампаний в приложении в офлайн-продажи, сопоставляя с данными из CRM.
• Плата только за те ресурсы, которые используете — занимаемое место в хранилище и вычислительные мощности.  
• Для работы требуются базовые знания SQL — ничего сверхъестественного. 
• Есть отличный помощник Data Studio, с помощью которого можно визуализировать аналитику и предоставлять в наглядном виде.

От редакции Нетологии

• Онлайн-курс «SQL».
• Профессия «Аналитик мобильных приложений». 
• Онлайн-курс «Big Data с нуля». 
• Профессия «Маркетолог-аналитик».