Агрегация для Funnel analysis — Продвинутая аналитика на SQL

• Funnel analysis
• Агрегация для Funnel analysis
  • Агрегация для воронки
    • График воронки
  • Анализ оттока покупателей
  • Проверка гипотезы об устройствах
  • Проверка гипотез о браузерах и страницах
• Выводы

В этом уроке мы рассмотрим, что такое 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-запроса:

SELECT * FROM clickstream;

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

сlickstream

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

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

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

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

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

SELECT
    device,
    count(device) AS device_count
FROM clickstream
GROUP BY device
ORDER BY device_count DESC;

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

devices

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

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

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

SELECT browser, count(browser) as browser_count
from clickstream
group by browser
order by browser_count desc;

SELECT
    page,
    count(page) AS page_count
FROM clickstream
GROUP BY page
ORDER BY page_count DESC;

SELECT
    action,
    count(action) AS action_count
FROM clickstream
GROUP BY action
ORDER BY action_count DESC;

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

browsers

pages

actions

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

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

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

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

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

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

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

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

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

SELECT
    action,
    COUNT(action) AS action_count
FROM clickstream
WHERE datetime LIKE '%.02.2023%'
GROUP BY action
ORDER BY action_count DESC;

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

First month actions

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

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

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

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

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

SELECT
    action,
    COUNT(action) AS action_count
FROM clickstream
WHERE datetime LIKE '%.03.2023%'
GROUP BY action
ORDER BY action_count DESC;

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

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

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

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

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

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

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

CREATE table to_page_count_first_month (
    device VARCHAR,
    toPage_count INT,
);


CREATE table buy_count_first_month (
    device VARCHAR,
    buy_count INT,
);

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

INSERT INTO to_page_count_first_month (
    SELECT
        device,
        COUNT(*) AS to_page_count
    FROM clickstream
    WHERE action = 'toPage' AND datetime LIKE '%.02.2023%'
    GROUP BY device
    ORDER BY topage_count DESC
);

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

toPage_count_first_month

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

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

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

INSERT INTO buy_count_first_month (
    SELECT
        device,
        COUNT(*) AS buy_count
    FROM clickstream
    WHERE action = 'buy' AND datetime LIKE '%.02.2023%'
    GROUP BY device
    ORDER BY buy_count DESC
);

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

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

SELECT
    to_page_count_first_month.device,
    to_page_count,
    buy_count
FROM to_page_count_first_month LEFT JOIN
    buy_count_first_month
    ON to_page_count_first_month.device = buy_count_first_month.device;

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

first_month_device_count

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

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

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

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

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

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

SELECT * FROM clickstream
WHERE datetime LIKE '%.03.2023%';

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

Скопируем таблицу 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. Мы выдвинули три гипотезы о том, почему происходит отток покупателей во второй месяц на сайте интернет-магазина, агрегировали данные и построили по ним воронки в виде столбчатых диаграмм. Также мы опровергли одну из гипотез и отдали разработчикам на проверку две другие.

Самостоятельная работа

В самостоятельной работе вы будете использовать базу данных clickstream_self_study. В ней есть данные за апрель и май того же сайта. Постройте воронки и посмотрите, происходит ли во второй месяц отток покупателей. Если да, то проверьте три гипотезы из урока. Агрегируйте данные по соответствующим параметрам и постройте воронки.