Трансформируем ваши данные в прибыль

Пн — Пт: с 10:00 до 19:00

ГлавнаяБлогКак рассчитать медианное значение и процентили между двумя покупками на SQL для управления оттока клиентской базы.

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

2 минут(ы)

В одном из наших прошлых материалов “Как рассчитать количество дней между покупками SQL” мы разбирали как мы можем по каждому пользователю в нашей базе данных узнать, сколько дней у них прошло между двумя определенными покупками. Рекомендую прочесть ее перед ознакомлением с данным материалом..

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

Зачем?
Представьте, что ваш бизнес основан на модели, когда пользователи должны постоянно что-то покупать.
Следовательно вы знаете статистические метрики между первой и второй покупками. Например, 75% процентиль равен 25 дням. Это значит, что если у вас есть пользователи, которые после своей первой покупки ничего не купили на 26 день, то уже можно начинать задумываться о том, чтобы сделать для них специальное предложение дабы удержать их в списке ваших постоянных клиентов.

Подобных бизнес задач встречается очень много, но как же их решить маркетологу?

Представим себе данные RFM анализа.
Исходные данные для примера (разделитель запятая):

date,Userid,transactionid,revenue
2020-01-01,1,1,1279
2020-01-02,2,2,438
2020-01-03,2,37,724
2020-01-03,3,3,1113
2020-01-03,8,47,811
2020-01-03,14,27,1268
2020-01-04,4,4,601
2020-01-04,12,48,617
2020-01-04,14,28,801
2020-01-04,19,38,763

Уникальным значением в строке является transactionid — идентификатор транзакции
revenue — средний чек
Userid — идентификатор уникального клиента
data — дата формирования транзакции

Для решения этой задачи мы возьмем запрос из прошлой статьи и добавим к нему функцию под названием PERCENTILE_CONT, которая позволяет нам рассчитать нужный процентиль. 50% является медианой.

Сам запрос будет выглядеть так:

WITH
num_buy_table as ( select 1 as num_buy)
--- здесь меняем номер покупки
SELECT
PERCENTILE_CONT(dif_date, 0 IGNORE NULLS) OVER() as min --указываем нулевой процентиль - минимальное значение
, PERCENTILE_CONT(dif_date, 0.25 IGNORE NULLS) OVER() as percentile25 --указываем 25% процентиль
, PERCENTILE_CONT(dif_date, 0.5 IGNORE NULLS) OVER() as median --указываем 50% процентиль - медиану
, PERCENTILE_CONT(dif_date, 0.75 IGNORE NULLS) OVER() as percentile75 --указываем 75% процентиль
, PERCENTILE_CONT(dif_date, 1 IGNORE NULLS) OVER() as max --указываем 100% процентиль - максимальное значение
FROM
(
SELECT
userid
,dif_date
,number_buy from
(
SELECT UserId
,date,
LEAD(date) OVER (PARTITION BY UserId ORDER BY date) AS next_date,
DATE_DIFF (LEAD(date) OVER (PARTITION BY UserId ORDER BY date), date, day) AS dif_date,
ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY date) as number_buy
FROM ВАША_ТАБЛИЦА
ORDER BY date, userid
)
WHERE number_buy in (select num_buy from num_buy_table)
ORDER BY userid -- сортировка по желанию
)
LIMIT 1 --если убрать это значение, то вы получите кучу строк, которые будут содержать одну и ту же информацию.

В результате вы получите таблицу следующего вида:
В результате вы получите таблицу следующего вида:

Теперь мы знаем, что медианное значение между нужными нам покупками составляет 43 дня. 75 процентиль — 180 дней.

Благодаря этим данным мы уже можем существенно улучшить качество коммуникаций с нашими клиентами, а также увеличить объем продаж за счет предсказания и сокращения оттока клиентской базы.

Рассчитайте медиану и перцентили в SQL

Управляйте оттоком клиентов с точными метриками