Customer Segmentation

1-- 1. Customer segmentation theo thu nhập
2SELECT 
3  CASE 
4      WHEN c.monthly_income >= 50000000 THEN 'Priority (>50M)'
5      WHEN c.monthly_income >= 20000000 THEN 'Affluent (20-50M)'
6      ELSE 'Mass (<20M)'
7  END AS customer_segment,
8  COUNT(*) AS customer_count,
9  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct_customers,
10  ROUND(AVG(c.monthly_income), 0) AS avg_income,
11  -- Loan metrics
12  COUNT(DISTINCT l.application_id) AS total_loans,
13  ROUND(AVG(l.loan_amount), 0) AS avg_loan_amount,
14  ROUND(AVG(l.default_flag) * 100, 2) AS default_rate_pct
15FROM dim_customer c
16LEFT JOIN fact_loan_application l 
17  ON c.customer_id = l.customer_id AND l.status = 'Approved'
18GROUP BY 
19  CASE 
20      WHEN c.monthly_income >= 50000000 THEN 'Priority (>50M)'
21      WHEN c.monthly_income >= 20000000 THEN 'Affluent (20-50M)'
22      ELSE 'Mass (<20M)'
23  END
24ORDER BY avg_income DESC;

1-- 2. Customer profile theo segment
2SELECT 
3  CASE 
4      WHEN c.monthly_income >= 50000000 THEN 'Priority'
5      WHEN c.monthly_income >= 20000000 THEN 'Affluent'
6      ELSE 'Mass'
7  END AS segment,
8  c.employment_type,
9  COUNT(*) AS count,
10  ROUND(AVG(c.age), 1) AS avg_age,
11  ROUND(AVG(c.years_employed), 1) AS avg_years_employed,
12  ROUND(AVG(l.credit_score), 0) AS avg_credit_score
13FROM dim_customer c
14LEFT JOIN fact_loan_application l ON c.customer_id = l.customer_id
15GROUP BY 
16  CASE 
17      WHEN c.monthly_income >= 50000000 THEN 'Priority'
18      WHEN c.monthly_income >= 20000000 THEN 'Affluent'
19      ELSE 'Mass'
20  END,
21  c.employment_type
22ORDER BY segment, count DESC;

1-- 3. RFM Calculation
2WITH rfm_base AS (
3  SELECT 
4      customer_id,
5      DATEDIFF(DAY, MAX(transaction_date), '2024-12-31') AS recency_days,
6      COUNT(*) AS frequency,
7      SUM(amount) AS monetary
8  FROM fact_transaction
9  GROUP BY customer_id
10),
11rfm_scores AS (
12  SELECT 
13      customer_id,
14      recency_days,
15      frequency,
16      monetary,
17      NTILE(5) OVER (ORDER BY recency_days DESC) AS r_score,
18      NTILE(5) OVER (ORDER BY frequency ASC) AS f_score,
19      NTILE(5) OVER (ORDER BY monetary ASC) AS m_score
20  FROM rfm_base
21)
22SELECT 
23  customer_id,
24  recency_days,
25  frequency,
26  monetary,
27  r_score,
28  f_score,
29  m_score,
30  CONCAT(r_score, f_score, m_score) AS rfm_segment,
31  CASE 
32      WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
33      WHEN r_score >= 4 AND f_score >= 3 THEN 'Loyal Customers'
34      WHEN r_score >= 3 AND f_score >= 1 AND m_score >= 3 THEN 'Potential Loyalists'
35      WHEN r_score >= 4 AND f_score <= 2 THEN 'New Customers'
36      WHEN r_score <= 2 AND f_score >= 3 THEN 'At Risk'
37      WHEN r_score <= 2 AND f_score >= 4 AND m_score >= 4 THEN 'Cannot Lose Them'
38      WHEN r_score <= 2 AND f_score <= 2 THEN 'Hibernating'
39      ELSE 'Need Attention'
40  END AS rfm_label
41FROM rfm_scores;

1-- 4. RFM Summary
2WITH rfm_labeled AS (
3  -- (use the CTE above)
4  SELECT customer_id, r_score, f_score, m_score, monetary,
5      CASE 
6          WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
7          WHEN r_score >= 4 AND f_score >= 3 THEN 'Loyal'
8          WHEN r_score >= 3 AND m_score >= 3 THEN 'Potential Loyalists'
9          WHEN r_score >= 4 AND f_score <= 2 THEN 'New Customers'
10          WHEN r_score <= 2 AND f_score >= 3 THEN 'At Risk'
11          WHEN r_score <= 2 AND f_score <= 2 THEN 'Hibernating'
12          ELSE 'Need Attention'
13      END AS rfm_label
14  FROM (
15      SELECT customer_id,
16          NTILE(5) OVER (ORDER BY DATEDIFF(DAY, MAX(transaction_date), '2024-12-31') DESC) AS r_score,
17          NTILE(5) OVER (ORDER BY COUNT(*) ASC) AS f_score,
18          NTILE(5) OVER (ORDER BY SUM(amount) ASC) AS m_score,
19          SUM(amount) AS monetary
20      FROM fact_transaction
21      GROUP BY customer_id
22  ) scores
23)
24SELECT 
25  rfm_label,
26  COUNT(*) AS customer_count,
27  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct,
28  ROUND(AVG(monetary), 0) AS avg_monetary
29FROM rfm_labeled
30GROUP BY rfm_label
31ORDER BY avg_monetary DESC;

1-- 5. Số sản phẩm mỗi KH đang dùng
2WITH customer_products AS (
3  SELECT 
4      c.customer_id,
5      c.monthly_income,
6      COUNT(DISTINCT l.product_id) AS num_products,
7      COUNT(DISTINCT p.product_type) AS num_product_types,
8      STRING_AGG(DISTINCT p.product_type, ', ') AS product_list
9  FROM dim_customer c
10  JOIN fact_loan_application l 
11      ON c.customer_id = l.customer_id AND l.status = 'Approved'
12  JOIN dim_product p ON l.product_id = p.product_id
13  GROUP BY c.customer_id, c.monthly_income
14)
15SELECT 
16  num_product_types,
17  COUNT(*) AS customer_count,
18  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct,
19  ROUND(AVG(monthly_income), 0) AS avg_income
20FROM customer_products
21GROUP BY num_product_types
22ORDER BY num_product_types;

1-- 6. KH có vay tiêu dùng nhưng chưa có thẻ tín dụng → cơ hội cross-sell
2WITH customer_product_flags AS (
3  SELECT 
4      c.customer_id,
5      c.monthly_income,
6      c.age,
7      c.city,
8      MAX(CASE WHEN p.product_type = 'Personal' THEN 1 ELSE 0 END) AS has_personal,
9      MAX(CASE WHEN p.product_type = 'Credit Card' THEN 1 ELSE 0 END) AS has_credit_card,
10      MAX(CASE WHEN p.product_type = 'Mortgage' THEN 1 ELSE 0 END) AS has_mortgage,
11      MAX(CASE WHEN p.product_type = 'Auto' THEN 1 ELSE 0 END) AS has_auto,
12      MIN(l.default_flag) AS any_default
13  FROM dim_customer c
14  JOIN fact_loan_application l 
15      ON c.customer_id = l.customer_id AND l.status = 'Approved'
16  JOIN dim_product p ON l.product_id = p.product_id
17  GROUP BY c.customer_id, c.monthly_income, c.age, c.city
18)
19SELECT 
20  'Personal → Credit Card' AS cross_sell_opportunity,
21  COUNT(*) AS eligible_customers,
22  ROUND(AVG(monthly_income), 0) AS avg_income,
23  ROUND(AVG(age), 0) AS avg_age
24FROM customer_product_flags
25WHERE has_personal = 1 
26  AND has_credit_card = 0 
27  AND any_default = 0
28  AND monthly_income >= 15000000;