В работе столкнулся с задачей сделать когортный анализ на чистом SQL и посчитать - Churn rate. Быстрый поиск в Яндексе не дал самое главное - как же строить запрос для вычисления этого модного показателя. Поэтому я решил в этой статье дать полное и развернутое объяснение с точки зрения расчета в SQL.
Что же такое когортный анализ и Сhurn rate? Объясню для тех, кто еще не знает, что это и ищет подробную информацию. Объяснение расчета SQL ниже.
Когортный анализ - это сравнение каких-нибудь сущностей, объединенных общими характеристиками. Например, это могут быть клиенты, которые пришли из одного рекламного канала и месяца первой покупки. Может возникнуть вопрос: "А для чего группировать клиентов по этим двум характеристикам?". Это может быть нужно, чтобы оценить стоимость привлечения этих клиентов, процент удержания и, наоборот, % клиентов, которые перестали пользоваться продуктом или услугами после первой покупки - Churn Rate.
Собственно, Churn Rate - это показатель в когортном анализе, для оценки количества клиентов, которые "отвалились". Как правило он рассчитывается в динамике: по оси Y - количество клиентов, по X - какой-то период (у нас будет количество месяцев). Каждая кривая на диаграмме может быть отдельной когортной. Например, возможная когорта - клиенты, которые пришли к нам из рекламы в VK в январе 2024 года.
Приступим к технической части
Вводные:
- Все вычисления будут происходить в MS SQL 2019
- За основу я беру таблицу Orders из учебной базы Northwind . Ниже запрос для подготовки таких же данных, как у меня
Шаг 1. Анализ исходных данных
Итак, у нас есть таблица, которая содержит 3 колонки: клиент, месяц первой покупки, страна клиента. Для начала нам нужно определить первую дату покупки клиента
Шаг 2. Определение первой даты покупки клиента
Далее рассчитаем через какое количество месяцев, после первой покупки, и какое количество клиентов купили что-либо.
Шаг 3. Расчет периодов и количества клиентов
Колонка cohort_retained - это то количество клиентов, которое мы удержали спустя N месяцев. На основании данного расчета вычислим размер нашей когорты (значение за нулевой месяц) для каждой строки и посчитаем % удержанных клиентов (Retention Rate) и потерянных (Churn Rate).
Шаг 4. Расчет Churn Rate и Retention Rate
Таким образом, мы получили совокупный показатель Churn rate за весь рассматриваемый период. Представим это на графике.
Как видим, 70-75% клиентов у нас отваливается после первой покупки. Но это совокупный анализ. Выделим когорты. Для начала выделим клиентов по году первой покупки и посмотрим Churn rate. Обогатим наш код в SQL.
Для наглядности, результат представим на графике
Уже результат дает больше информации. Можно посмотреть, как меняется привязанность клиентов в зависимости от года первой покупки.
Помимо построения когорт по дате, можно строить по внешним характеристикам. Например, по странам.
По странам, получается слишком большое количество линий. Для примера оставим только Германию и Францию.
В данной статье, я постарался изложить максимально подробно всю ту информацию, которая находится на просторах интернета, чтобы аналитик любого уровня мог разобраться.
Если данная статья была вам полезна, то комментируйте, ставьте лайки и подписывайтесь, чтобы Дзен продвигал эту статью по своим алгоритмам. Желаю вам только чистых данных!