Автоматизируем отчет с когортным анализом

В прошлой статье мы с вами узнали что такое когортный анализ и как его сделать с помощью лучшего друга любого веб-аналитика — 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-запросе задавать не конкретные даты, а динамические периоды.

Полезные ссылки:

Об авторе
Свежие записи

Роман Романчук

Аналитик данных с более чем 10-летним опытом работы в российских и международных финтех-проектах, включая Сравни, Xsolla и Т-Банк. За это время прошел путь от рядового аналитика до директора, погружаясь в разработку стратегий, оптимизацию метрик и решение сложных бизнес-задач.

Что вы ищите?