Как сократить расходы в четыре раза при работе с сырыми данными в Google BigQuery

Всем привет! Меня зовут Павел Земсков, я занимаюсь аналитикой проектов VK. Сейчас я отвечаю за развитие группы продуктов для среднего и малого бизнеса внутри социальной сети ВКонтакте и на площадке Юлы. 

Я расскажу о проверенном мной подходе к работе с запросами в базу данных, и о том, как это помогло сократить затраты на обращение к сырым данным в четыре раза. Статья будет полезна тем, кто записывает сырые данные по событиям пользователей в формате, близком к GA360 (GA360, OWOX, SegmentStream), и задумывается об оптимизации работы с данными и сокращении расходов на обработку запросов в Google BigQuery. Те, кто хранит данные в Clickhouse и начали задумываться об ускорении выполнения запросов, тоже могут использовать этот подход.

Устраивайтесь поудобнее! 

Зачем нужна работа аналитика?

Ответ и простой, и сложный. Каждый интернет-проект нуждается в оценке эффективности своей работы. А для этого нужны данные, много данных. Без них неизбежна потеря приоритетов и, как результат, денег, пользователей и ресурсов. Что мы делаем для анализа эффективности проекта: сайта или приложения? Мы собираем данные. Без преувеличения, мы хотим записывать в базу данных каждый «чих» пользователя. В дальнейшем эти сырые данные выгружаем, очищаем и обрабатываем. Мы формулируем и проверяем гипотезы, принимаем уверенные решения, и продукт становится удобнее. И вот тут возникает проблема, о решении которой я расскажу: чем больше данных мы собрали, тем «дороже» для нас обходится формирование необходимых семплов и отчётов. Например, в BigQuery от Google за выполнение запросов в базу данных нужно платить деньги. И даже если вы используете собственную БД, которая лежит на вашем сервере, запросы не бесплатны, их стоимость пропорциональна скорости выполнения запросов.

Как покрыть основные запросы в базу данных агрегированными таблицами (агрегатами)

Дано: каждый запрос в БД — это деньги или затраты, пропорциональные скорости обработки запроса. Когда запросов становиться много, затраты начинают пугать, а потом и вовсе приводить в ужас. Не меня, но бизнес точно 🙂 Проекты хотят быть прибыльными, для этого затраты должны быть разумными и обоснованными.

Когда я заметил, что большинство запросов однотипны и обращаются к одним и тем же полям, то понял, что это отличная возможность оптимизировать подход к работе с данными. Решение, которое пришло мне в голову, — создать таблицы на основе сырых данных, в которых собрана только основная информация, которая используется почти в каждом запросе, и на выгрузку которой мы каждый раз тратим время или деньги. Чтобы воплотить идею в жизнь, мне предстояло:

1. определить параметры, которые описывают бо̒льшую часть запросов при обращении к данным;

2. разработать подход к работе с такими таблицами — обновление, хранение, безопасность, доступность;

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

Метрики, которые можно анализировать при обращении к агрегированным таблицам (агрегатам)

• Продуктовые метрики: 

  • LTV — выручка с клиента за время использования сервиса.

  • Retention — как новые пользователи превращаются в активных.

  • CR — конверсия из посетителей в платящих.

• Маркетинговые метрики:

  • CAC (Customer Acquisition Cost) — стоимость привлечения клиента.

  • ROAS (return on ad spend) — эффективность затрат на рекламу (доходы и расходы).

  • CR — конверсия из посетителей в платящих для оценки рекламных каналов.

• Метрики роста:

  • DAU/MAU/WAU — посетители за промежуток времени.

  • Revenue/Orders — выручка, заказы.

Недостатки работы с таблицей, содержащей сырые данные:

1. нужен хороший SQL (например unnest в BigQuery), так как в таблице каждая строка, по сути, структура. Например, при передаче события о просмотре карточек товара в каталоге мы передаём одну строчку, которая содержит весь набор просмотренных карточек с их характеристиками.

2. Из предыдущего пункта вытекает вторая проблема: большие расходы на выполнение запросов при обращении к таблице.

Стало понятно, что идеальным решением будет простая и лёгкая агрегированная таблица, обеспечивающая удобство работы и доступность данных. Я начал с детализации проблемы.

Детализация проблемы

Инфраструктура

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

1. Сайт или мобильное приложение передают данные о каждом событии пользователя в базу данных BigQuery или Clickhouse. Передачу можно настроить либо с помощью внутренней команды разработчиков, либо через подрядчиков, которые предлагают готовые решения. Я рассматривал этот вариант на примере OWOX BI.
   В результате получаем таблицу с сырыми данными (сессионный стриминг). Она содержит 188 полей, но для отчетов используются далеко не все из них. Примерно к 20 полям обращение происходит на порядок чаще, чем к остальным 168. Например, разбивка данных по признаку mobile или  desktop используется гораздо чаще, чем выгрузка данных по признаку «размер экрана». Если обращаться к сессионному стримингу напрямую, то нужно использовать сложные вычисления, что генерирует дополнительные расходы в BigQuery. 

2. Данные о расходах на рекламу из рекламного кабинета мы также грузим в БД BigQuery или Clickhouse. В дальнейшем на их основе считаем эффективность затрат на рекламу. Передачу данных можно настроить через API рекламных кабинетов, либо через подрядчиков и их готовые инструменты.

3. Внутренние базы проекта также передают в BigQuery или Clickhouse. Например, информацию о движении заказов после их формирования на сайте, так мы отслеживаем конечную выкупаемость заказов.

4. Обработка данных и создание агрегатов. Как раз этот пункт мы с вами рассмотрим подробнее: какие метрики и параметры выбрать, чтобы покрыть бо̒льшую часть отчётов.

5. Создание дашбордов. После формирования простых запросов к агрегированным данным мы визуализируем их в различных BI-инструментах.

Описание решения

Чтобы сократить размер запросов в BigQuery и Clickhouse, я решил создать две таблицы и обращаться не напрямую к сырым данным, а к агрегированным образцам в табличном формате.

Потребность в 80 % отчётов покрывают:

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

2. Агрегирующая таблица, где сессии группируются по основным метрикам. Собирается из предыдущей таблицы.

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

Преимущества решения: 

1. Достаточно базового владения SQL, чтобы обратиться к простой таблице без вложенных структур.

2. Расходы на BigQuery снижаются более чем в четыре раза по сравнению с обращением к базовой таблице с событиями пользователей.

Решение

Общий процесс

Вновь прибегну в карточкам Miro, чтобы наглядно показать, как работает решение:

1. Создание агрегата на уровне клиент+сессия (Daily Session) я детально опишу ниже, а также привёл пример запроса, по которому можно собрать подобный агрегат.

2. На этом этапе агрегируем нужную нам информацию, схлопывая сессии клиентов по выбранным параметрам (о которых тоже расскажу ниже). Справочники, которые чаще всего будут использоваться (каким образом их быстро собирать — об этом тоже ниже):

• Campaign (кампания). Очень часто рекламные кампании в процессе могут изменить свое название (например, была ошибка в написании); здесь мы можем передать новое название по старым данным.

• Medium (канал) тоже можем поменять. Например, изначально решили не выделять канал как CPA и записывали в CPC.

• Source (источник). Например чтобы схлопнуть yandex и Yandex в одну строчку.

• Region. Здесь пропишем только те регионы, которые хотим видеть в отчёте несхлопнутыми, а всё остальное свернем в Other_Regions. 

3. Непосредственное использование агрегата в отчётах, как самостоятельно, так и совмещая с данными по заказам.

Сравним использование агрегированных таблиц и Сырых данных:

За 30 дней использования агрегированных таблиц мы получили суммарную экономию (в рублях и времени) более 80 % от затрат за предыдущий период, когда обращались к сырым данным (сессионный стриминг).

Daily Session: создание таблицы уровня клиент+сессия

Пример структуры таблицы: ссылка.

Шаблон запроса в BigQuery для создания таблицы: ссылка.

Основные отличия от сессионного стриминга

Основное отличие в том, что я взял только некоторые поля (около 20 параметров) и перешёл с уровня «событие» на уровень «клиент-сессия». Не нужно переходить на уровень «клиент», ведь мы хотим отдельно отслеживать сессии и источники трафика. В некоторых случаях (например, при когортном анализе) надо анализировать клиентов, а не их сессии, поэтому важно, чтобы в агрегированной таблице такая возможность сохранялась.

Другие отличия работы с таблицей от сессионного стриминга:

1. Исключены дублирующие отправки транзакций. Во многих проектах в качестве транзакции принято считать загрузку ThankYouPage (на сайтах). В этом случае при обновлении страницы «Спасибо за покупку» повторно отправляется в базу данных информация о транзакции. В скрипте, который я прописал для настройки агрегированной таблицы, я «схлопнул» дубликаты транзакций, чтобы избежать искусственного завышения конверсии.

2. В случае сессионного стриминга бывает, что первое событие пользователя отправляется без client_id. Когда я настраивал таблицу, я «схлопнул» все такие события, чтобы они не создавали искусственного увеличения количества пользователей. Если страница входа по какой-то причине пришла пустая (без ID), то в таблицу попадут данные из первого хита. Так мы получаем больше информации о странице входа.

Daily Result: создание агрегированной таблицы по дням

Шаблон запроса в BigQuery для создания таблицы: ссылка.

Основные отличия таблицы Daily Result от Daily Session:

1. Удобство в работе. В Daily Result мы подключили справочники и исключаем менее подробные разрезы. Например, мы хотим отслеживать эффективность в каждом из двух регионов, в которых мы осуществляем деятельность. В этом случае удобнее и все остальные регионы «схлопнуть» в отдельную группу. В результате прямо на основании этих данных можно построить сводную таблицу. Получается некий аналог Google Analytics «Источники трафика». 

2. Эту таблицу можно без проблем вытянуть на отдельный лист Google sheets тремя способами:

   1. С помощью дополнения Bigquery -> Google Sheets,а затем построить на его основе сводную таблицу. Кстати, похожим образом можно выгрузить данные в Excel.

   2. Использовать скрипт Google Apps, а затем написать миниотчёты (приведу скрины примеров визуализации ниже).

   3. Сразу подключиться к этой таблице через любую BI-систему.

3. Лёгкость расширения отчётов. В таблице Daily Result выделены отдельные сессии с ID транзакции, благодаря этому можно без проблем сопоставить трафик с воронкой по заказам и расширить отчёты под запрос.

Примеры визуализации — дашборды.

Пример отчёта по месяцам и последним дням в разрезе источников трафика и региона. С помощью этого отчета мы оцениваем эффективность потраченных на рекламу денег (ниже пример, данные условные):

Пример отчёта по сравнению аналогичных периодов, в котором сразу смотрим на показатели в различных разрезах — по основным источникам рекламы, по регионам, по типам закупки рекламного трафика (также приведены условные цифры):

Заключение

Работа с данными — необходимое условие развития интернет-проекта. Данные в огромном объёме поступают и регистрируются в системе с многомиллионной аудиторией. Сложность работы с ними заключается в необходимости постоянно обращаться к источнику (базе данных) при подготовке каждого вида отчёта. Долго, дорого, трудоёмко. Ну, вы и сами знаете. Большая зависимость от данных — это жизнь 🙂 Но можно её хакнуть: рецепт я привёл выше, а ещё бонусом приложил полезные ссылки на Git. Буду рад, если окажется полезным и вы сможете сэкономить не меньше времени, нервов и денег, чем я когда-то, собрав всё самое нужное в агрегированные таблицы.

А как формируете дашборды о действиях и событиях в продукте вы? Какой опыт считаете наиболее успешным – собственные разработки, готовые решения подрядчиков, BI-продукты крупных компаний?