Строим продуктовую воронку при помощи SQL в BigQuery
Есть множество различных способов строить продуктовые воронки. Доступ к “сырым данным” предоставляет много пространства для творческих решений.
Совсем недавно Todd Kerpelman написал отличную статью, в которой показал несколько интересных вариантов построения воронки с примерами SQL-запросов.
Как дополнение хочу привести пример простого SQL-запроса, которого в большинстве случаев достаточно для построения продуктовых воронок.
Возьмем данные того же тестового приложения и те же события, формирующие шаги воронки:
first_open- первое открытие приложения пользователем.completed_5_levels- завершение 5-го уровня в игре.use_extra_steps- использование дополнительного шага.
Данные по использованию приложения есть в публичном доступе в BigQuery.
Для построения воронки с данными необходимо совершить два преобразования:
- Сагрегировать по идентификатору пользователя с пометкой достижения по каждому шагу.
- Просуммировать количество пользователей по необходимым условиям.
Пример SQL-запроса будет выглядеть так:
Однако это простой способ построения воронки и он не учитывает последовательность достижения шагов воронки. А иногда это имеет значение. В статье можно найти несколько способов построения воронок с учетом последовательностей событий.
От себя могу добавить еще один способ, который будет полезен не только для построения воронки, но и для более широкого спектра задач. Например, для анализа первой сессии или подготовки данных для расчета моделей атрибуции (о которых я напишу в следующих своих публикациях).
Способ предусматривает использование полезной аналитической функции “String_agg”, она позволяет “склеивать” значения в одно поле по различным правилам.
В запросе ниже мы склеиваем названия ивентов по каждому пользователю по времени:
В результате у нас есть последовательная цепочка выполнения событий, по которой мы можем подсчитать различные интересующие нас комбинации взаимодействий по каждому пользователю.
Заключение: в статье я показал два способа построения продуктовых воронок. Из своего опыта могу сказать, что их достаточно для большинства случаев и они просты в использовании.
Если есть новые идеи или решения подобных задач, сообщите мне или оставляйте комментарии. А также подписывайтесь на мой Телеграм-канал BigQuery Insights, в котором я делюсь интересными решениями аналитики в Google BigQuery.
Вам также может быть интересно:
Запуск SQL-запросов по расписанию в BigQuery через Schedule Query
Примеры работы с данными Firebase: ссылка 1, ссылка 2, ссылка 3 и ссылка 4.
