Приложение SQL: когортный анализ

Когортный анализ — один из наиболее часто проводимых анализов, особенно если вы работаете в B2C-компании. Итак, что такое когортный анализ?

Когортный анализ – это аналитический метод, который классифицирует и делит данные на группы с общими характеристиками перед анализом. Этот метод обычно используется, чтобы облегчить организациям выделение, анализ и обнаружение закономерностей в жизненном цикле пользователя, улучшить удержание пользователей и лучше понять поведение пользователей в определенной когорте. — https://www.heavy.ai/technical-glossary/cohort-analysis

Случаи использования

Обычное использование когортного анализа:

• Воронка регистрации
• Поведение пользователей
• Тенденции оттока/удержания
• Я добавлю больше .. (или, если у вас есть другой распространенный вариант использования, напишите в разделе комментариев ниже)

Синтаксис SQL

Прежде чем мы начнем, я хотел бы сообщить, что CTE активно используются при создании когортной таблицы. Чтобы освежить память, посетите SQL 101 — CTE (Common Table Expression).

Для этого примера давайте создадим ежемесячную когорту удержания пользователей вымышленной электронной коммерции The Look. Я буду использовать общедоступный набор данных BigQuery и data из bigquery-public-data.thelook_ecommerce.order_items.

Всего существует 5 последовательных шагов для создания ежемесячной когорты удержания пользователей The Look (4 CTE и 1 последний запрос). Итак, начнем.

Подготовка данных

1. Определить первую покупку (месяц) для всех пользователей.

with first_buy as(
      select 
        user_id,
        min(date_trunc(created_at,month)) first_time_buy
      from `bigquery-public-data.thelook_ecommerce.order_items` o
      where created_at between '2020-01-01' and '2020-12-31'
      group by 1
      order by 2 asc
),

Во-первых, нам нужно определить, когда была первая транзакция пользователя. Мы можем просто использовать min(created_at) и group by user_id, чтобы получить минимальную дату транзакции для каждого пользователя. Однако для нашей когорты нам нужны только год и месяц, поэтому я использовал date_trunc(created_at, month), чтобы получить первую дату месяца.

Например:

Если min(created_at) равно 2022–02–18, то использование min(date_trunc(created_at,month)) даст нам 2022–02–01.

Я применил другой фильтр created_at between ‘2020–01–01’ and ‘2020–12–31’, потому что хочу анализировать данные только за 2020 год.

Результат SQL:

2. Рассчитать интервал до следующей покупки

next_purchase as (
      select 
        o.user_id,
        date_diff(date(date_trunc(created_at, month)),first_time_buy, month) buy_interval
      from `bigquery-public-data.thelook_ecommerce.order_items` o
        inner join first_buy f on o.user_id = f.user_id
      where created_at between '2020-01-01' and '2020-12-31'
),

Затем, чтобы иметь возможность подсчитать, сколько пользователей совершают транзакции в каждом месяце, нам нужно рассчитать интервал каждой транзакции, созданной каждым пользователем, и дату first_time_buy. В этом случае, поскольку мы хотим создать месячное удержание, интервал будет рассчитываться в месяцах.

Результат SQL:

Объединение данных

3. Создать размер когорты

initial_users as (select
  first_time_buy,
  count(distinct user_id) users
from first_buy
group by 1),

Нашим первым first_buy CTE по-прежнему являются данные по пользователям. Чтобы иметь возможность сгруппировать их как одну когорту, нам нужно агрегировать их, чтобы иметь возможность группировать их по месяцам, используя количество различных. Мы хотим узнать, сколько уникальных пользователей совершили первую покупку в каждом месяце.

Результат SQL:

Это означает, что 928 уникальных пользователей совершили свою первую транзакцию в январе 2020 года.

4. Создайте удержание

retention as (
      select 
        first_time_buy,
        buy_interval,
        count(distinct n.user_id) users
      from first_buy f
      left join next_purchase n on n.user_id = f.user_id
      where buy_interval is not null
      group by 1,2
),

Узнав, что 928 уникальных пользователей совершили свои транзакции в январе 2020 года, мы хотим знать, сколько пользователей из этих 928 пользователей повторяют свои транзакции в последующие месяцы. Мы уже знаем интервал каждой транзакции каждого пользователя из next_purchase CTE, осталось сгруппировать их для подсчета количества уникальных пользователей.

Результат SQL:

Это означает, что из 928 пользователей, совершивших первую транзакцию в январе 2020 года:

• 988 пользователей совершили транзакцию в месяце 0 — очевидно, потому что месяц 0 — это месяц самой первой транзакции.
• Только 40 пользователей из 928 совершили еще одну транзакцию в первый месяц, то есть в февраль.
• Только 25 пользователей из 928 совершили еще одну транзакцию во втором месяце, то есть в марте.
• и т. д.

5. Создайте когортную таблицу, объединив предыдущие CTE.

with first_buy as(
      select 
        user_id,
        date(min(date_trunc(created_at,month))) first_time_buy
      from `bigquery-public-data.thelook_ecommerce.order_items` o
      where created_at >= '2020-01-01'
      group by 1
      order by 2 asc
),
next_purchase as (
      select 
        o.user_id,
        date_diff(date(date_trunc(created_at, month)),first_time_buy, month) buy_interval
      from `bigquery-public-data.thelook_ecommerce.order_items` o
        inner join first_buy f on o.user_id = f.user_id
      where 
        created_at >= '2020-01-01' and
        created_at < '2021-01-01'
),
initial_users as (select
  first_time_buy,
  count(distinct user_id) users
from first_buy
group by 1),
retention as (
      select 
        first_time_buy,
        buy_interval,
        count(distinct n.user_id) users_transacting
      from first_buy f
      left join next_purchase n on n.user_id = f.user_id
      where buy_interval is not null
      group by 1,2
)
select
  r.first_time_buy,
  i.users ,
  r.buy_interval,
  r.users_transacting
from retention r
left join initial_users i on i.first_time_buy = r.first_time_buy
order by 1,3

Теперь у нас уже есть все необходимое, давайте объединим их в одну таблицу!

Результат SQL:

Вот мой полный запрос: https://console.cloud.google.com/bigquery?sq=736962362501:78866ef27fe64441ad59fd39f7b1e2d5

Чтобы представить его лучше, визуализируйте свой результат в инструментах визуализации данных, таких как Tableau, Data Studio или Microsoft Excel. Это будет окончательный результат после поворота и раскраски тепловой карты (я использую Data Studio).

Если у вас есть какие-либо вопросы или предложения, не стесняйтесь комментировать!