Segmentação RFM com SQL

Marketing com orçamento finito não pode falar com todo mundo do mesmo jeito. Quem são os melhores clientes? Quem está prestes a abandonar? Sem segmentar, campanhas viram tiro no escuro.

A técnica RFM (Recência, Frequência, Valor) é o padrão para isso — e dá para fazer 100% em SQL.

Uma CTE calcula R, F e M por cliente; NTILE(5) divide cada métrica em 5 faixas (quintis); um CASE traduz as faixas em segmentos com nome de negócio:

WITH base AS (
  SELECT cu.customer_unique_id AS cid,
         JULIANDAY(:hoje) - JULIANDAY(MAX(o.order_purchase_timestamp)) AS recencia,
         COUNT(DISTINCT o.order_id) AS frequencia,
         SUM(pay.payment_value)     AS monetario
  FROM orders o
  JOIN customers cu  ON cu.customer_id = o.customer_id
  JOIN payments  pay ON pay.order_id   = o.order_id
  WHERE o.order_status = 'delivered'
  GROUP BY cu.customer_unique_id
),
scored AS (
  SELECT *,
         NTILE(5) OVER (ORDER BY recencia DESC) AS r,
         NTILE(5) OVER (ORDER BY frequencia)    AS f,
         NTILE(5) OVER (ORDER BY monetario)     AS m
  FROM base
)
SELECT CASE
         WHEN r>=4 AND f>=4 THEN 'Campeões'
         WHEN r>=3 AND f>=3 THEN 'Fiéis'
         WHEN r<=2 AND f>=3 THEN 'Em risco'
         WHEN r<=2 AND f<=2 THEN 'Hibernando / Perdidos'
         ELSE 'Atenção'
       END AS segmento,
       COUNT(*) AS clientes, ROUND(AVG(monetario)) AS ticket_medio
FROM scored GROUP BY segmento ORDER BY clientes DESC;