04_customer_segmentation.sql

-- Create base view
CREATE TEMP VIEW customer_rfm AS
SELECT c.customer_unique_id,
	   '2018-12-31' - MAX(order_purchase_timestamp)::date AS days_from_last_purchase,
	   COUNT(DISTINCT o.order_id) AS order_times,
	   COALESCE(SUM(i.price + i.freight_value), 0) AS total_spent
FROM customers c
LEFT JOIN delivered_orders o
ON c.customer_id = o.customer_id
LEFT JOIN order_items i
ON o.order_id = i.order_id
GROUP BY c.customer_unique_id
HAVING COUNT(DISTINCT o.order_id) > 0;


SELECT *
FROM customer_rfm;

-- Customer Segment
CREATE TEMP VIEW customer_segment AS
WITH customer_rfm_score AS (
	SELECT *,
		   NTILE(5) OVER(ORDER BY days_from_last_purchase DESC) AS recency_score,
		   NTILE(5) OVER(ORDER BY order_times) AS frequency_score,
		   NTILE(5) OVER(ORDER BY total_spent) AS monetary_score
	FROM customer_rfm
), customer_rfm_total AS (
	SELECT *, (recency_score + frequency_score + monetary_score) AS total_rfm_score
	FROM customer_rfm_score
)
SELECT *,
	   CASE
		   WHEN recency_score <= 2 THEN 'At Risk'
		   WHEN total_rfm_score > 12 THEN 'VIP'
		   WHEN total_rfm_score > 9 THEN 'Loyal Customers'
		   ELSE 'Regular Customers'
	   END AS segment
FROM customer_rfm_total;

SELECT *
FROM customer_segment;

SELECT segment, COUNT(DISTINCT customer_unique_id) AS number_of_customers,
	   SUM(total_spent) AS total_spent
FROM customer_segment
GROUP BY segment
ORDER BY number_of_customers DESC;