Bài 10: Phân tích Review & Đánh giá Khách hàng

1-- Tỷ lệ đơn hàng có review
2SELECT 
3  COUNT(DISTINCT r.order_id) AS reviewed_orders,
4  COUNT(DISTINCT o.order_id) AS total_delivered_orders,
5  ROUND(
6      COUNT(DISTINCT r.order_id) * 100.0 / 
7      COUNT(DISTINCT o.order_id), 1
8  ) AS review_rate_pct
9FROM fact_orders o
10LEFT JOIN fact_reviews r ON o.order_id = r.order_id
11WHERE o.order_status = 'delivered';

1-- Phân bố rating tổng thể
2SELECT 
3  review_score,
4  COUNT(*) AS review_count,
5  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct,
6  REPEAT('█', COUNT(*) * 50 / MAX(COUNT(*)) OVER()) AS bar_chart
7FROM fact_reviews
8GROUP BY review_score
9ORDER BY review_score DESC;

1-- Rating trend theo tháng
2SELECT 
3  DATE_TRUNC('month', review_date) AS review_month,
4  COUNT(*) AS total_reviews,
5  ROUND(AVG(review_score), 2) AS avg_rating,
6  ROUND(
7      COUNT(CASE WHEN review_score >= 4 THEN 1 END) * 100.0 / 
8      COUNT(*), 1
9  ) AS satisfaction_rate,
10  ROUND(
11      COUNT(CASE WHEN review_score <= 2 THEN 1 END) * 100.0 / 
12      COUNT(*), 1
13  ) AS complaint_rate
14FROM fact_reviews
15GROUP BY DATE_TRUNC('month', review_date)
16ORDER BY review_month;

1-- Thời gian từ delivery đến review
2SELECT 
3  CASE 
4      WHEN r.review_date - o.delivered_date <= 1 THEN '0-1 ngày'
5      WHEN r.review_date - o.delivered_date <= 3 THEN '2-3 ngày'
6      WHEN r.review_date - o.delivered_date <= 7 THEN '4-7 ngày'
7      WHEN r.review_date - o.delivered_date <= 14 THEN '8-14 ngày'
8      ELSE '15+ ngày'
9  END AS response_time,
10  COUNT(*) AS review_count,
11  ROUND(AVG(review_score), 2) AS avg_rating
12FROM fact_reviews r
13JOIN fact_orders o ON r.order_id = o.order_id
14WHERE o.delivered_date IS NOT NULL
15GROUP BY 
16  CASE 
17      WHEN r.review_date - o.delivered_date <= 1 THEN '0-1 ngày'
18      WHEN r.review_date - o.delivered_date <= 3 THEN '2-3 ngày'
19      WHEN r.review_date - o.delivered_date <= 7 THEN '4-7 ngày'
20      WHEN r.review_date - o.delivered_date <= 14 THEN '8-14 ngày'
21      ELSE '15+ ngày'
22  END
23ORDER BY avg_rating DESC;

1-- Rating trung bình theo category
2SELECT 
3  p.category,
4  COUNT(r.review_id) AS total_reviews,
5  ROUND(AVG(r.review_score), 2) AS avg_rating,
6  ROUND(
7      COUNT(CASE WHEN r.review_score = 5 THEN 1 END) * 100.0 / 
8      COUNT(*), 1
9  ) AS five_star_pct,
10  ROUND(
11      COUNT(CASE WHEN r.review_score <= 2 THEN 1 END) * 100.0 / 
12      COUNT(*), 1
13  ) AS low_rating_pct,
14  ROUND(AVG(r.delivery_rating), 2) AS avg_delivery_rating
15FROM fact_reviews r
16JOIN dim_product p ON r.product_id = p.product_id
17GROUP BY p.category
18ORDER BY avg_rating DESC;

1-- Sản phẩm có rating thấp và volume review cao (cần xử lý)
2SELECT 
3  p.product_id,
4  p.product_name,
5  p.category,
6  p.sub_category,
7  COUNT(r.review_id) AS review_count,
8  ROUND(AVG(r.review_score), 2) AS avg_rating,
9  ROUND(
10      COUNT(CASE WHEN r.review_score <= 2 THEN 1 END) * 100.0 / 
11      COUNT(*), 1
12  ) AS complaint_pct
13FROM fact_reviews r
14JOIN dim_product p ON r.product_id = p.product_id
15GROUP BY p.product_id, p.product_name, p.category, p.sub_category
16HAVING COUNT(r.review_id) >= 10  -- Đủ sample size
17 AND AVG(r.review_score) < 3.5  -- Rating thấp
18ORDER BY review_count DESC
19LIMIT 20;

1-- Mối quan hệ giữa rating và doanh số
2WITH product_metrics AS (
3  SELECT 
4      p.product_id,
5      p.category,
6      ROUND(AVG(r.review_score), 1) AS avg_rating,
7      COUNT(DISTINCT r.order_id) AS review_orders,
8      SUM(oi.price) AS total_revenue
9  FROM dim_product p
10  JOIN fact_reviews r ON p.product_id = r.product_id
11  JOIN fact_order_items oi ON r.order_id = oi.order_id 
12      AND r.product_id = oi.product_id
13  GROUP BY p.product_id, p.category
14)
15SELECT 
16  CASE 
17      WHEN avg_rating >= 4.5 THEN '⭐ 4.5-5.0 (Excellent)'
18      WHEN avg_rating >= 4.0 THEN '⭐ 4.0-4.4 (Good)'
19      WHEN avg_rating >= 3.5 THEN '⭐ 3.5-3.9 (Average)'
20      WHEN avg_rating >= 3.0 THEN '⭐ 3.0-3.4 (Below Avg)'
21      ELSE '⭐ < 3.0 (Poor)'
22  END AS rating_tier,
23  COUNT(*) AS product_count,
24  ROUND(AVG(total_revenue), 0) AS avg_revenue_per_product,
25  ROUND(SUM(total_revenue), 0) AS total_revenue
26FROM product_metrics
27GROUP BY 
28  CASE 
29      WHEN avg_rating >= 4.5 THEN '⭐ 4.5-5.0 (Excellent)'
30      WHEN avg_rating >= 4.0 THEN '⭐ 4.0-4.4 (Good)'
31      WHEN avg_rating >= 3.5 THEN '⭐ 3.5-3.9 (Average)'
32      WHEN avg_rating >= 3.0 THEN '⭐ 3.0-3.4 (Below Avg)'
33      ELSE '⭐ < 3.0 (Poor)'
34  END
35ORDER BY rating_tier DESC;

1-- Seller review scorecard
2SELECT 
3  s.seller_id,
4  s.seller_name,
5  s.seller_type,
6  COUNT(r.review_id) AS total_reviews,
7  ROUND(AVG(r.review_score), 2) AS avg_product_rating,
8  ROUND(AVG(r.delivery_rating), 2) AS avg_delivery_rating,
9  ROUND(
10      COUNT(CASE WHEN r.review_score = 5 THEN 1 END) * 100.0 / 
11      COUNT(*), 1
12  ) AS five_star_pct,
13  ROUND(
14      COUNT(CASE WHEN r.review_score <= 2 THEN 1 END) * 100.0 / 
15      COUNT(*), 1
16  ) AS complaint_pct,
17  -- NPS proxy
18  ROUND(
19      (COUNT(CASE WHEN r.review_score >= 4 THEN 1 END) - 
20       COUNT(CASE WHEN r.review_score <= 2 THEN 1 END)) * 100.0 / 
21      COUNT(*), 1
22  ) AS nps_proxy
23FROM fact_reviews r
24JOIN dim_seller s ON r.seller_id = s.seller_id
25GROUP BY s.seller_id, s.seller_name, s.seller_type
26HAVING COUNT(r.review_id) >= 20
27ORDER BY avg_product_rating DESC;

1-- So sánh product rating vs delivery rating theo seller
2WITH seller_ratings AS (
3  SELECT 
4      s.seller_id,
5      s.seller_name,
6      s.seller_type,
7      ROUND(AVG(r.review_score), 2) AS product_rating,
8      ROUND(AVG(r.delivery_rating), 2) AS delivery_rating,
9      COUNT(*) AS review_count
10  FROM fact_reviews r
11  JOIN dim_seller s ON r.seller_id = s.seller_id
12  GROUP BY s.seller_id, s.seller_name, s.seller_type
13  HAVING COUNT(*) >= 20
14)
15SELECT 
16  seller_name,
17  seller_type,
18  product_rating,
19  delivery_rating,
20  ROUND(product_rating - delivery_rating, 2) AS rating_gap,
21  CASE 
22      WHEN product_rating - delivery_rating > 0.5 
23          THEN '⚠️ Delivery kém hơn Product'
24      WHEN delivery_rating - product_rating > 0.5 
25          THEN '✅ Delivery tốt hơn Product'
26      ELSE '↔️ Tương đương'
27  END AS gap_assessment,
28  review_count
29FROM seller_ratings
30ORDER BY ABS(product_rating - delivery_rating) DESC
31LIMIT 15;

1-- Rating trend theo quý cho từng seller type
2SELECT 
3  s.seller_type,
4  DATE_TRUNC('quarter', r.review_date) AS review_quarter,
5  COUNT(r.review_id) AS review_count,
6  ROUND(AVG(r.review_score), 2) AS avg_rating,
7  ROUND(AVG(r.delivery_rating), 2) AS avg_delivery_rating
8FROM fact_reviews r
9JOIN dim_seller s ON r.seller_id = s.seller_id
10GROUP BY s.seller_type, DATE_TRUNC('quarter', r.review_date)
11ORDER BY s.seller_type, review_quarter;

1-- Phát hiện sản phẩm có rating giảm mạnh so với tháng trước
2WITH monthly_rating AS (
3  SELECT 
4      r.product_id,
5      p.product_name,
6      p.category,
7      DATE_TRUNC('month', r.review_date) AS month,
8      AVG(r.review_score) AS avg_rating,
9      COUNT(*) AS review_count
10  FROM fact_reviews r
11  JOIN dim_product p ON r.product_id = p.product_id
12  GROUP BY r.product_id, p.product_name, p.category, 
13           DATE_TRUNC('month', r.review_date)
14  HAVING COUNT(*) >= 5  -- Đủ sample
15),
16rating_change AS (
17  SELECT 
18      *,
19      LAG(avg_rating) OVER(
20          PARTITION BY product_id ORDER BY month
21      ) AS prev_month_rating,
22      LAG(review_count) OVER(
23          PARTITION BY product_id ORDER BY month
24      ) AS prev_month_reviews
25  FROM monthly_rating
26)
27SELECT 
28  product_name,
29  category,
30  month,
31  ROUND(prev_month_rating, 2) AS prev_rating,
32  ROUND(avg_rating, 2) AS current_rating,
33  ROUND(avg_rating - prev_month_rating, 2) AS rating_change,
34  review_count,
35  CASE 
36      WHEN avg_rating - prev_month_rating <= -1.0 THEN '🔴 CRITICAL DROP'
37      WHEN avg_rating - prev_month_rating <= -0.5 THEN '🟡 WARNING'
38      ELSE '🟢 NORMAL'
39  END AS alert_level
40FROM rating_change
41WHERE prev_month_rating IS NOT NULL
42AND avg_rating - prev_month_rating <= -0.5
43ORDER BY rating_change ASC
44LIMIT 20;

1-- Sellers cần intervention
2SELECT 
3  s.seller_id,
4  s.seller_name,
5  s.seller_type,
6  COUNT(r.review_id) AS total_reviews,
7  ROUND(AVG(r.review_score), 2) AS avg_rating,
8  ROUND(
9      COUNT(CASE WHEN r.review_score <= 2 THEN 1 END) * 100.0 / 
10      COUNT(*), 1
11  ) AS complaint_rate,
12  CASE 
13      WHEN AVG(r.review_score) < 3.0 AND COUNT(*) >= 50 
14          THEN '🔴 SUSPEND REVIEW'
15      WHEN AVG(r.review_score) < 3.5 AND COUNT(*) >= 30 
16          THEN '🟡 WARNING LETTER'
17      WHEN COUNT(CASE WHEN r.review_score <= 2 THEN 1 END) * 100.0 / 
18           COUNT(*) > 30 
19          THEN '🟠 HIGH COMPLAINT'
20      ELSE '🟢 OK'
21  END AS action_required
22FROM fact_reviews r
23JOIN dim_seller s ON r.seller_id = s.seller_id
24GROUP BY s.seller_id, s.seller_name, s.seller_type
25HAVING AVG(r.review_score) < 3.5 
26 OR (COUNT(CASE WHEN r.review_score <= 2 THEN 1 END) * 100.0 / COUNT(*) > 30)
27ORDER BY avg_rating ASC;

1-- Review behavior theo customer segment (kết hợp RFM)
2WITH customer_rfm AS (
3  SELECT 
4      customer_id,
5      NTILE(4) OVER(ORDER BY MAX(order_date) DESC) AS recency_q,
6      NTILE(4) OVER(ORDER BY COUNT(DISTINCT order_id)) AS frequency_q,
7      NTILE(4) OVER(ORDER BY SUM(total_amount)) AS monetary_q
8  FROM fact_orders
9  WHERE order_status = 'delivered'
10  GROUP BY customer_id
11),
12customer_segment AS (
13  SELECT 
14      customer_id,
15      CASE 
16          WHEN recency_q <= 2 AND frequency_q >= 3 AND monetary_q >= 3 
17              THEN 'Champions'
18          WHEN recency_q <= 2 AND frequency_q >= 2 
19              THEN 'Loyal'
20          WHEN recency_q <= 2 AND frequency_q = 1 
21              THEN 'New Customers'
22          WHEN recency_q >= 3 AND frequency_q >= 3 
23              THEN 'At Risk'
24          ELSE 'Others'
25      END AS segment
26  FROM customer_rfm
27)
28SELECT 
29  cs.segment,
30  COUNT(r.review_id) AS total_reviews,
31  ROUND(AVG(r.review_score), 2) AS avg_rating,
32  ROUND(AVG(r.delivery_rating), 2) AS avg_delivery_rating,
33  ROUND(
34      COUNT(CASE WHEN r.review_score >= 4 THEN 1 END) * 100.0 / 
35      COUNT(*), 1
36  ) AS satisfaction_rate
37FROM fact_reviews r
38JOIN customer_segment cs ON r.customer_id = cs.customer_id
39GROUP BY cs.segment
40ORDER BY avg_rating DESC;

1-- So sánh rating giữa đơn đầu tiên và đơn lặp lại
2WITH customer_orders AS (
3  SELECT 
4      o.customer_id,
5      o.order_id,
6      ROW_NUMBER() OVER(
7          PARTITION BY o.customer_id ORDER BY o.order_date
8      ) AS order_sequence
9  FROM fact_orders o
10  WHERE o.order_status = 'delivered'
11)
12SELECT 
13  CASE 
14      WHEN co.order_sequence = 1 THEN 'First Purchase'
15      WHEN co.order_sequence = 2 THEN '2nd Purchase'
16      WHEN co.order_sequence = 3 THEN '3rd Purchase'
17      WHEN co.order_sequence <= 5 THEN '4th-5th Purchase'
18      ELSE '6+ Purchases'
19  END AS purchase_order,
20  COUNT(r.review_id) AS review_count,
21  ROUND(AVG(r.review_score), 2) AS avg_rating,
22  ROUND(AVG(r.delivery_rating), 2) AS avg_delivery_rating
23FROM fact_reviews r
24JOIN customer_orders co ON r.order_id = co.order_id
25GROUP BY 
26  CASE 
27      WHEN co.order_sequence = 1 THEN 'First Purchase'
28      WHEN co.order_sequence = 2 THEN '2nd Purchase'
29      WHEN co.order_sequence = 3 THEN '3rd Purchase'
30      WHEN co.order_sequence <= 5 THEN '4th-5th Purchase'
31      ELSE '6+ Purchases'
32  END
33ORDER BY MIN(co.order_sequence);