The RFM engine that taught Coca-Cola's eB2B who to talk to.

WITH base AS (
  SELECT outlet_id,
    DATEDIFF(day, MAX(order_date), CURRENT_DATE) AS recency_days,
    COUNT(*)         AS frequency,
    SUM(net_value)   AS monetary
  FROM silver.unified_orders
  WHERE order_date >= DATEADD(month, -12, CURRENT_DATE)
  GROUP BY outlet_id
), scored AS (
  SELECT outlet_id,
    NTILE(5) OVER (ORDER BY recency_days ASC) AS r_score,  -- 5 = most recent
    NTILE(5) OVER (ORDER BY frequency)         AS f_score,
    NTILE(5) OVER (ORDER BY monetary)          AS m_score
  FROM base
)
SELECT outlet_id, r_score, f_score, m_score,
  CASE
    WHEN r_score=5  AND f_score=5  AND m_score=5  THEN 'champions'
    WHEN r_score=1  AND f_score>=4                THEN 'cant_lose_them'
    WHEN r_score>=4 AND f_score>=4 AND m_score>=4 THEN 'loyal_customers'
    WHEN r_score<=2 AND f_score>=3 AND m_score>=3 THEN 'at_risk'
    WHEN r_score=5  AND f_score=1                 THEN 'recent_customers'
    WHEN r_score=4  AND f_score=1                 THEN 'promising'
    WHEN r_score>=4 AND f_score>=2                THEN 'potential_loyalist'
    WHEN r_score=3  AND f_score>=3                THEN 'needing_attention'
    WHEN r_score=3  AND f_score<=2                THEN 'about_to_sleep'
    WHEN r_score<=2 AND f_score=1                 THEN 'lost'
    ELSE                                               'hibernating'
  END AS cohort
FROM scored;