В одном из наших прошлых материалов “Как рассчитать количество дней между покупками 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 дней.
Благодаря этим данным мы уже можем существенно улучшить качество коммуникаций с нашими клиентами, а также увеличить объем продаж за счет предсказания и сокращения оттока клиентской базы.
