Число покупателей по RFM-сегментам
SQL, по которому посчитано
WITH base AS (
SELECT
customer_id,
max(date) AS last_deal,
count(*) AS deals_count,
sum(amount) AS revenue
FROM deals
WHERE status = 'Выиграна'
AND date > (SELECT max(date) FROM deals) - INTERVAL 12 MONTH
GROUP BY customer_id
),
calc AS (
SELECT
customer_id,
date_diff('day', last_deal, (SELECT max(date) FROM deals)) AS recency_days,
deals_count,
revenue
FROM base
),
scored AS (
SELECT
customer_id,
recency_days,
deals_count,
revenue,
ntile(5) OVER (ORDER BY recency_days DESC) AS r_score,
ntile(5) OVER (ORDER BY deals_count) AS f_score,
ntile(5) OVER (ORDER BY revenue) AS m_score
FROM calc
),
seg AS (
SELECT
recency_days,
deals_count,
revenue,
CASE
WHEN r_score >= 4 AND f_score >= 4 THEN 'Чемпионы'
WHEN r_score >= 4 AND f_score = 3 THEN 'Лояльные'
WHEN r_score >= 4 AND f_score <= 2 THEN 'Новички'
WHEN r_score = 3 AND f_score >= 3 THEN 'Перспективные'
WHEN r_score = 3 AND f_score <= 2 THEN 'Нужно вовлечь'
WHEN r_score <= 2 AND f_score >= 3 AND m_score >= 3 THEN 'В зоне риска'
WHEN r_score <= 2 AND f_score >= 3 THEN 'Не потерять'
WHEN r_score = 2 THEN 'Засыпающие'
ELSE 'Потерянные'
END AS segment
FROM scored
)
SELECT
segment,
count(*) AS customers,
round(count(*) * 1.0 / sum(count(*)) OVER (), 3) AS share,
round(avg(recency_days), 0) AS avg_recency_days,
round(avg(deals_count), 1) AS avg_deals,
round(avg(revenue), 0) AS avg_revenue,
round(sum(revenue), 0) AS total_revenue
FROM seg
GROUP BY segment
ORDER BY customers DESC