Customer Segmentation & Cohort Analysis

WITH rfm_raw AS (
  SELECT
    customer_id,
    DATEDIFF(day, MAX(order_date), CURRENT_DATE) AS recency_days,
    COUNT(DISTINCT order_id) AS frequency,
    SUM(order_value) AS monetary
  FROM orders
  WHERE order_date >= DATEADD(year, -1, CURRENT_DATE)
  GROUP BY customer_id
),
rfm_scored AS (
  SELECT
    customer_id,
    recency_days,
    frequency,
    monetary,
    NTILE(5) OVER (ORDER BY recency_days ASC) AS r_score,
    NTILE(5) OVER (ORDER BY frequency ASC) AS f_score,
    NTILE(5) OVER (ORDER BY monetary ASC) AS m_score
  FROM rfm_raw
)
SELECT
  customer_id,
  r_score,
  f_score,
  m_score,
  CONCAT(r_score, f_score, m_score) AS rfm_segment,
  CASE
    WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
    WHEN r_score >= 4 AND f_score >= 3 THEN 'Loyal Customers'
    WHEN r_score >= 4 AND f_score <= 2 THEN 'New Customers'
    WHEN r_score <= 2 AND f_score >= 3 THEN 'At Risk'
    WHEN r_score <= 2 AND f_score <= 2 AND m_score >= 3 THEN 'Can''t Lose Them'
    WHEN r_score <= 2 AND f_score <= 2 AND m_score <= 2 THEN 'Hibernating'
    ELSE 'Needs Attention'
  END AS segment_label
FROM rfm_scored
ORDER BY monetary DESC;

WITH cohorts AS (
  SELECT
    customer_id,
    DATE_TRUNC('month', first_order_date) AS cohort_month
  FROM customers
),
activity AS (
  SELECT
    c.customer_id,
    c.cohort_month,
    DATE_TRUNC('month', o.order_date) AS activity_month,
    DATEDIFF('month', c.cohort_month, DATE_TRUNC('month', o.order_date)) AS months_since_first
  FROM cohorts c
  JOIN orders o ON c.customer_id = o.customer_id
)
SELECT
  cohort_month,
  months_since_first,
  COUNT(DISTINCT customer_id) AS active_customers,
  ROUND(
    COUNT(DISTINCT customer_id) * 100.0 /
    FIRST_VALUE(COUNT(DISTINCT customer_id)) OVER (
      PARTITION BY cohort_month ORDER BY months_since_first
    ), 1
  ) AS retention_pct
FROM activity
GROUP BY cohort_month, months_since_first
ORDER BY cohort_month, months_since_first;