Продвинутая аналитика на SQL
2026-02-26 17:17 Diff
https://ru.hexlet.io/courses/analytics-with-sql/lessons/funnel-analysis/theory_unit

В этом уроке мы рассмотрим, что такое Funnel analysis, для чего он используется, и как строить воронки. Мы проведем агрегацию по количеству посещений страниц сайта и покупок с них. В итоге убедимся, что во второй месяц происходит отток покупателей. Также мы проверим три гипотезы и найдем причины оттока.

Умение использовать Funnel analysis позволяет эффективно работать с такими данными, как таблицы действий пользователей системы, а еще выдвигать и проверять гипотезы.

Funnel analysis

Funnel analysis — это метод, который помогает узнать, как посетители пользуются веб-сайтом или приложением. Он используется в маркетинге и разработке продуктов, чтобы понять, где пользователи могут отказаться от покупки или заполнения формы, и где скрыта проблема.

Разберем это на примере. Представим, что у нас есть таблица с действиями посетителя на сайте интернет-магазина:

сlickstream

Мы видим таблицу действий посетителя на сайте. Такие таблицы еще называются clickstream. Здесь есть следующие столбцы:

  • datetime — дата и время действия на сайте
  • device — устройство, с которого посетитель совершил действие
  • browser — браузер посетителя
  • clientId — ID клиента
  • page — страница сайта
  • action — действие, которое совершил посетитель

Нам нужно проанализировать, как посетители интернет-магазина вели себя на сайте. В итоге нам нужно понять, почему в последнюю неделю происходит отток клиентов, и что на это влияет.

Funnel analysis позволяет дата-аналитику выявить источник проблем, например, почему в какой-то момент уменьшился поток клиентов.

Когда мы занимаемся Funnel analysis, мы строим воронку. Воронка — это диаграмма, которая позволяет статистически проследить, к примеру, сколько людей посетили сайт и сколько из них совершили покупку.

Посмотрим на пример воронки:

На этой воронке видно два столбца: toPage и buy. Около 120 пользователей посетили сайт, и около 25 совершили на нем покупку.

В этом уроке мы агрегируем синтетические данные действий посетителей сайта для построения воронок и выясним причины оттока покупателей во второй месяц.

Агрегация для Funnel analysis

Мы будем строить воронки и определять причины оттока в несколько этапов:

  • Проанализируем данные с помощью агрегации для столбцов device, browser, page и action и определим уникальные значения
  • Построим воронки посещения и покупок на страницах сайта за первый и второй месяцы и проверим, что есть отток покупателей
  • Выдвинем гипотезы о том, почему происходит отток
  • Проверим каждую из этих гипотез и сделаем выводы

Агрегация для воронки

График воронки

Мы будем анализировать данные из базы clickstream. В ней содержится одна таблица clickstream. Посмотрим на эту таблицу с помощью SQL-запроса:

В результате мы видим таблицу из 300 строк, которая содержит данные за два месяца. Таблица выглядит так:

сlickstream

Ссылка на таблицу

В этой таблице есть шесть параметров:

  • datetime — дата и время события
  • device и browser — говорят, с какого устройства и браузера посетитель зашел на сайт
  • clientId — уникальный ID посетителя
  • page — страница, на которую зашел посетитель
  • action — действие посетителя

Проверим, какие существуют уникальные устройства, браузеры, страницы и действия, и сколько их. Для этого мы агрегируем данные по интересующему для нас параметру и используем функцию count. Также мы отсортируем значения от самых популярных устройств до наименее популярных.

Напишем запрос для устройств:

В результате запроса мы получили такую таблицу:

devices

Ссылка на таблицу

В этой таблице мы видим, что самое популярное устройство — POCO M4 Pro, а самое непопулярное — Xiaomi RedmiBook. Всего уникальных устройств восемь.

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

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

browsers

pages

actions

Ссылка на таблицу

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

Рассмотрим подробнее таблицу pages. В ней есть главная страница main и другие страницы, которые обозначают категории товаров.

В таблице actions есть три возможных действия:

  • toPage — переход на страницу
  • buy — покупка
  • пустое значение — отсутствие действия

Анализ оттока покупателей

Определить причину оттока покупателей — это задача, которая часто встречается в работе дата-аналитика. Project manager сообщил нам, что во второй месяц в наших данных наблюдается отток покупателей на сайте. Проверим это.

Построим воронки для каждого отдельного месяца в виде столбчатых диаграмм и сравним их.

В таблице clickstream мы видим данные за период с 1 февраля 2023 года по 31 марта 2023 года. Возьмем первый месяц и проведем агрегацию по действиям:

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

First month actions

Ссылка на таблицу

В этой таблице мы видим, что за первый месяц совершилось 116 переходов на страницы и 34 покупки. При этом нет пустых значений, когда не было совершено ни одного действия.

Скопируем эту таблицу в Google Sheets и построим столбчатую диаграмму:

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

Теперь агрегируем данные для второго месяца и построим воронку:

Ссылка на таблицу

Здесь мы видим, что число покупок уменьшилось, и появились посетители, которые не совершили никаких действий с текущей страницы.

Для поиска проблемы дата-аналитик выдвигает гипотезы, которые он потом проверяет. Мы предполагаем, что проблема во второй месяц может быть в некорректном отображении сайта у некоторых посетителей. В таблице clickstream есть информация об устройстве, браузере и страницах. Поэтому мы проверим три гипотезы:

  1. На каком-то из устройств сайт отображается некорректно
  2. В каком-то из браузеров сайт отображается некорректно
  3. Какая-то из страниц сайта отображается некорректно

Проверим каждую из этих гипотез.

Проверка гипотезы об устройствах

Чтобы построить воронку за первый месяц, нам понадобятся две вспомогательные таблицы: для агрегации действий toPage и buy по устройствам. Создадим их:

Агрегируем данные по устройствам для действия toPage:

В результате у нас есть таблица:

toPage_count_first_month

Ссылка на таблицу

Мы агрегировали данные для действия toPage по устройствам за первый месяц.

Такую же агрегацию мы проведем и для действия buy:

Ссылка на таблицу

Теперь мы получили две таблицы, которые мы объединим в одну с помощью LEFT JOIN:

Наша таблица после LEFT JOIN выглядит так:

first_month_device_count

Ссылка на таблицу

Мы объединили две таблицы, в которых агрегировали количество переходов на страницу и покупок по устройствам.

Такие таблицы еще называются pivot table: по оси х мы агрегируем таблицу по одному параметру, а по оси y — по другому.

Скопируем эту таблицу в Google Sheets и построим столбчатую диаграмму:

Google Sheets также дает возможность строить pivot table. Построим с его помощью pivot table для второго месяца.

Для этого получим данные за второй месяц SQL-запросом:

Ссылка на таблицу

Скопируем таблицу clickstream за второй месяц в Google Sheets. Теперь перейдем по «Вставка» - «Создать сводную таблицу». Откроется новый лист с пустой сводной таблицей:

На рисунке мы видим пустую pivot table.

Чтобы провести агрегацию, нужно заполнить ее параметры. В параметре «Строки» выберем «device», в параметре «Столбцы» — «action» и в «Значения» — «action». Теперь для строк укажем сортировку по убыванию, а в «Сортировать по» — «COUNTA для параметра "action" по» -> «toPage».

Столбцы отсортируем по убыванию по параметру «action», чтобы получить тот же порядок столбцов, что и в диаграмме за первый месяц. В итоге у нас есть такая таблица:

На этом рисунке мы видим итоговую pivot table для устройств за второй месяц. Здесь есть агрегация не только по устройствам и действиям, но и сумма всех значений «Итого».

Выделим строки и столбцы, не включая «Итого», и построим по ним столбчатую диаграмму:

Мы построили воронку посещений и покупок с разных устройств за второй месяц.

По сравнению с первым месяцем в этот месяц не было совершено покупок с устройства Xiaomi Redmi 10C, хотя в прошлом месяце их количество составляло девять. Поэтому есть вероятность, что с устройства Xiaomi Redmi 10C сайт отображается некорректно. Запомним это и перейдем к агрегации по браузерам и страницам.

Проверка гипотез о браузерах и страницах

Для агрегации по браузерам и страницам мы также используем сводные таблицы в Google Sheets. Построим сводные таблицы для браузеров и изобразим две воронки рядом для удобства сравнения:

Мы построили две воронки посещений и покупок в разных браузерах за первый и второй месяцы. Значимых различий между ними нет.

Теперь проделаем то же для разных страниц:

Мы построили воронки для посещений и покупок с разных страниц сайта за первый и второй месяцы. Из значимых отличий мы видим, что во втором месяце отсутствуют покупки со страницы «childrenClothes», хотя в первом месяце было много покупок с нее. Поэтому есть вероятность, что на странице «childrenClothes» есть ошибка, которая не позволяет пользователям совершить покупку.

Подведем итоги. В ходе funnel analysis мы обнаружили отток покупателей за второй месяц пользованием сайтом интернет-магазина. Мы выдвинули три гипотезы о том, что сайт может отображаться некорректно на одном из устройств, браузеров или страницы сайта.

Гипотеза о браузерах не подтвердилась, однако гипотезы об устройствах и страницах отклонить нельзя. С этими гипотезами дата-аналитики идут к разработчикам, которые должны их проверить и устранить возможные ошибки.

Выводы

В этом уроке мы определили, что такое Funnel analysis и как с помощью этого метода анализировать данные типа clickstream. Мы выдвинули три гипотезы о том, почему происходит отток покупателей во второй месяц на сайте интернет-магазина, агрегировали данные и построили по ним воронки в виде столбчатых диаграмм. Также мы опровергли одну из гипотез и отдали разработчикам на проверку две другие.