🔒Открыть пост
и посмотреть медиа
и посмотреть медиа
RFM-анализ на SQL
Один из довольно часто встречающихся на практике кейсов - сегментация пользователей (или RFM-анализ). Цель анализа - разделить клиентов на группы (сегменты), чтобы понять, кто из них самый ценный, кто требует внимания, а кого, возможно, уже не вернуть.
Осуществляется на основе:
▶️Recency — когда последний раз покупал?
▶️Frequency — сколько раз покупал?
▶️Monetary — сколько потратил?
Базовый запрос выглядит так:
WITH clients as (
SELECT
customer_id,
CURRENT_DATE - MAX(order_date) AS recency_days,
COUNT(order_id) AS frequency,
SUM(price) AS monetary_value
FROM orders
GROUP BY customer_id)
Из таблицы заказов считается разница между сегодняшним числом (или отчетной датой) и датой последнего заказа, а также считается число заказов и их сумма в разрезе каждого клиента.
Далее подключается скоринговая модель. Основной смысл таков: разбить клиентов на 5 квантилей и на основании этих 3 показателей присвоить им рейтинги. Для Recency ценятся наиболее свежие даты, а для Frequency и Monetary - чем больше, тем лучше.
Используем оконную функцию NTILE(5), которая делит клиентов на 5 равных частей:
SELECT
customer_id,
NTILE(5) OVER (ORDER BY recency_days) AS r_score,
NTILE(5) OVER (ORDER BY frequency DESC) AS f_score,
NTILE(5) OVER (ORDER BY monetary_value DESC) AS m_score
FROM clients
На выходе получится сгруппированная ранее таблица клиентов, к которой добавится 3 столбца, в каждом из которых будет число от 1 до 5 - это будет рейтинг клиента.
С этими рейтингами можно поступать по-разному. Можно всё агрегировать в единый итоговый 15-балльный рейтинг, а можно оставить как есть и смотреть рейтинги в разрезе 3 показателей - тут зависит от целей.