RFM в работе маркетолога является незаменимым инструментом анализа данных и получению инсайтов по работе с клиентами.
Представим себе данные 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 — дата формирования транзакции
А теперь нам нужно свами узнать сколько дней у пользователей прошло между первой и второй транзакцией, а потом между второй и третьей и т.д.
Нужно это для того, чтобы мы могли построить распределение дней между двумя транзакциями, чтобы получить возможность влиять на поведение пользователей путем своевременных коммуникаций, направленных на стимулирование продаж.
Решить эту задачу можно при помощи SQL довольно специфическим образом:
WITH num_buy_table as ( select 1 as num_buy) --- вы можете заменить 1 на любое число. Значение 1 покажет вам количество дней по пользователю между первой и второй покупкой. Если нужно между второй и третьей, то ставьте число 2. SELECT userid ,difdate ,number_buy from ( SELECT UserId ,date ProductName, LEAD(date) OVER (PARTITION BY UserId ORDER BY date) AS l1, DATE_DIFF ( LEAD(date) OVER (PARTITION BY UserId ORDER BY date),date, day) AS difdate, ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY date) as number_buy FROM ВАША_ТАБЛИЦА WHERE УСЛОВИЕ_ФИЛЬТРАЦИИ_ЕСЛИ_НУЖНО ORDER BY userid ) WHERE number_buy in (select num_buy from num_buy_table) ORDER BY userid -- сортировка по желанию
В результате данного запроса вы получите результирующую таблицу со следующими столбцами:
Userid — идентификатор пользователя
Difdate — количество дней между двумя транзакциями, который мы указали в первой строке запроса
Number_buy — порядковый номер покупку, которую мы сравниваем из первой строки нашего запроса
Искренне надеюсь, что этот запрос облегчит вашу жизнь
