В прошлой статье мы с вами узнали что такое когортный анализ и как его сделать с помощью лучшего друга любого веб-аналитика — MS Excel. Сегодня я предлагаю вам избавиться от рутины и автоматизировать данный отчет при помощи BigQuery и Data Studio.
Что мы хотим проанализировать?
Для начала, давайте определимся какой именно показатель мы хотим вывести в отчете и как собственно построить отчет.
Чаще всего когортный анализ используют для определения Retention Rate или Коэффициента удержания, то есть доли или количества пользователей вернувшихся на ваш сайт и сделавших определенное целевое действие.
Предлагаю посчитать количество пользователей сделавших заказ в определенный месяц и их Retention в течение года, то есть сколько из них вернулось для совершения повторных заказов в следующие месяцы.
Получаем данные
Данные для анализа я буду брать из публичного датасета Google Merchandise Store — интернет-магазина, который продает товары под торговой маркой Google (более подробно в статье о SQL). Вы же можете брать данные из любой другой базы, в которой ваш бизнес хранит информацию о заказах.
Для анализа нам понадобится:
- уникальный идентификатор пользователя;
- дата первого заказа пользователя;
- даты всех последующих заказов пользователя;
- количество заказов.
Ниже привожу пример SQL-запроса с комментариями:
WITH
-- получаем дату первой транзакции (заказа) и группируем по идентификатору пользователя
table_1 AS (
SELECT
MIN(date) AS firstTransaction,
fullVisitorId AS id
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20160801'
AND '20170801'
AND totals.transactions IS NOT NULL
GROUP BY
fullVisitorId ),
-- получаем даты и количество всех транзакций, сгруппированных по дате и идентификатору пользователя
table_2 AS (
SELECT
date,
fullVisitorId AS id,
SUM(totals.transactions) AS transactions
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20160801'
AND '20170801'
AND totals.transactions IS NOT NULL
GROUP BY
date,
fullVisitorId ),
-- склеиваем два подзапроса в единую таблицу; обращаю ваше внимание, что тут я делаю из дат месяцы для более удобного анализа
table_join AS (
SELECT
SUBSTR(table_1.firstTransaction, 1, 6) AS firstMonthTransaction,
SUBSTR(table_2.date, 1, 6) AS monthTransaction,
table_2.id AS id,
SUM(table_2.transactions) AS transactions
FROM
table_1
LEFT JOIN
table_2
ON
table_1.id = table_2.id
GROUP BY
firstMonthTransaction,
monthTransaction,
id)
-- выводим результат
SELECT
*
FROM
table_join
В результате выполнения запроса получится примерно такая таблица с данными:
Визуализируем отчет
Идем в Data Studio и создаем новый отчет в котором, в качестве источника данных, выбираем Google BigQuery.
После подключения источника нам осталось добавить в отчет сводную таблицу, в которой вывести данные таким образом:
Далее сортируем столбцы и строки по возрастанию месяцев, применяем к таблице тепловую карту и наслаждаемся результатом:
Сам отчет доступен по ссылке https://datastudio.google.com/open/1ONO0fUmCI4MnSyTg1ysEvd8Imtil-AE0
Напоследок
Вместо Data Studio и BigQuery вы можете использовать связку из любой другой базы и инструмента визуализации, например MS SQL и Excel.
А чтобы сделать отчет автоматическим, вам нужно в SQL-запросе задавать не конкретные даты, а динамические периоды.
Полезные ссылки:
- Когортный анализ. Сколько пользователей к вам вернулось?
- Осваиваем SQL на примере данных интернет-магазина Google
- Data Studio — базовые знания
- Как правильно организовать работу с гипотезами? - 21.11.2023
- Кейс: как построить отдел аналитики в большой компании? - 06.05.2022
- Учимся применять оконные функции - 29.09.2020