E-Commerce Revenue & Customer Segmentation

WITH customer_rfm AS (
  SELECT
    c.customer_unique_id,
    MAX(o.order_purchase_timestamp)          AS last_purchase,
    COUNT(DISTINCT o.order_id)               AS frequency,
    SUM(p.payment_value)                     AS monetary
  FROM orders o
  JOIN customers c  ON c.customer_id = o.customer_id
  JOIN order_payments p ON p.order_id = o.order_id
  WHERE o.order_status = 'delivered'
  GROUP BY c.customer_unique_id
),
scored AS (
  SELECT *,
    NTILE(5) OVER (ORDER BY last_purchase)        AS r_score,
    NTILE(5) OVER (ORDER BY frequency)            AS f_score,
    NTILE(5) OVER (ORDER BY monetary)             AS m_score
  FROM customer_rfm
)
SELECT
  CASE
    WHEN r_score >= 4 AND f_score >= 4 THEN 'Champions'
    WHEN r_score >= 4 AND f_score <= 2 THEN 'New / Promising'
    WHEN r_score <= 2 AND f_score >= 3 THEN 'At Risk'
    WHEN r_score <= 2 AND f_score <= 2 THEN 'Hibernating'
    ELSE 'Loyal / Regular'
  END                                        AS segment,
  COUNT(*)                                   AS customers,
  ROUND(SUM(monetary)::numeric, 0)           AS revenue,
  ROUND(AVG(monetary)::numeric, 2)           AS avg_customer_value
FROM scored
GROUP BY 1
ORDER BY revenue DESC;

WITH delivery_gap AS (
  SELECT
    o.order_id,
    r.review_score,
    o.order_delivered_customer_date::date
      - o.order_estimated_delivery_date::date AS days_late
  FROM orders o
  JOIN order_reviews r ON r.order_id = o.order_id
  WHERE o.order_status = 'delivered'
    AND o.order_delivered_customer_date IS NOT NULL
)
SELECT
  CASE
    WHEN days_late <= 0  THEN 'On time / early'
    WHEN days_late <= 3  THEN '1-3 days late'
    WHEN days_late <= 7  THEN '4-7 days late'
    ELSE                      '8+ days late'
  END                                   AS delivery_bucket,
  COUNT(*)                              AS orders,
  ROUND(AVG(review_score), 2)           AS avg_review,
  ROUND(100.0 * AVG(CASE WHEN review_score <= 2
        THEN 1 ELSE 0 END), 1)          AS pct_1_2_star
FROM delivery_gap
GROUP BY 1
ORDER BY MIN(days_late);