Bài 11: Thiết kế Dashboard E-commerce

1-- Executive KPI Cards - Current Month vs Previous Month
2WITH current_month AS (
3  SELECT 
4      SUM(total_amount) AS gmv,
5      COUNT(DISTINCT order_id) AS total_orders,
6      ROUND(AVG(total_amount), 0) AS aov,
7      COUNT(DISTINCT customer_id) AS unique_customers
8  FROM fact_orders
9  WHERE order_status IN ('delivered', 'shipped', 'processing')
10    AND DATE_TRUNC('month', order_date) = DATE_TRUNC('month', CURRENT_DATE)
11),
12prev_month AS (
13  SELECT 
14      SUM(total_amount) AS gmv,
15      COUNT(DISTINCT order_id) AS total_orders,
16      ROUND(AVG(total_amount), 0) AS aov,
17      COUNT(DISTINCT customer_id) AS unique_customers
18  FROM fact_orders
19  WHERE order_status IN ('delivered', 'shipped', 'processing')
20    AND DATE_TRUNC('month', order_date) = DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
21)
22SELECT 
23  -- KPI Card 1: GMV
24  c.gmv AS current_gmv,
25  p.gmv AS prev_gmv,
26  ROUND((c.gmv - p.gmv) * 100.0 / p.gmv, 1) AS gmv_growth_pct,
27  
28  -- KPI Card 2: Orders
29  c.total_orders AS current_orders,
30  ROUND((c.total_orders - p.total_orders) * 100.0 / p.total_orders, 1) AS orders_growth_pct,
31  
32  -- KPI Card 3: AOV
33  c.aov AS current_aov,
34  ROUND((c.aov - p.aov) * 100.0 / p.aov, 1) AS aov_growth_pct,
35  
36  -- KPI Card 4: Customers
37  c.unique_customers AS current_customers,
38  ROUND((c.unique_customers - p.unique_customers) * 100.0 / p.unique_customers, 1) AS customers_growth_pct
39FROM current_month c, prev_month p;

1-- Monthly Revenue Trend (last 12 months)
2SELECT 
3  DATE_TRUNC('month', order_date) AS month,
4  SUM(total_amount) AS gmv,
5  SUM(CASE WHEN order_status = 'delivered' THEN total_amount ELSE 0 END) AS net_revenue,
6  COUNT(DISTINCT order_id) AS orders,
7  COUNT(DISTINCT customer_id) AS customers,
8  ROUND(AVG(total_amount), 0) AS aov
9FROM fact_orders
10WHERE order_date >= CURRENT_DATE - INTERVAL '12 months'
11AND order_status NOT IN ('cancelled')
12GROUP BY DATE_TRUNC('month', order_date)
13ORDER BY month;

1-- Daily Operational Metrics
2SELECT 
3  order_date,
4  
5  -- Volume metrics
6  COUNT(DISTINCT order_id) AS total_orders,
7  SUM(total_amount) AS daily_gmv,
8  
9  -- Fulfillment metrics
10  COUNT(CASE WHEN order_status = 'delivered' THEN 1 END) AS delivered,
11  COUNT(CASE WHEN order_status = 'shipped' THEN 1 END) AS in_transit,
12  COUNT(CASE WHEN order_status = 'processing' THEN 1 END) AS processing,
13  COUNT(CASE WHEN order_status = 'cancelled' THEN 1 END) AS cancelled,
14  
15  -- Rates
16  ROUND(
17      COUNT(CASE WHEN order_status = 'cancelled' THEN 1 END) * 100.0 / 
18      COUNT(*), 1
19  ) AS cancel_rate,
20  
21  -- Performance
22  ROUND(AVG(
23      CASE WHEN delivered_date IS NOT NULL 
24      THEN delivered_date - order_date END
25  ), 1) AS avg_delivery_days
26
27FROM fact_orders
28WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
29GROUP BY order_date
30ORDER BY order_date DESC;

1-- Sellers cần attention hôm nay
2SELECT 
3  s.seller_name,
4  s.seller_type,
5  COUNT(DISTINCT o.order_id) AS today_orders,
6  COUNT(CASE WHEN o.order_status = 'cancelled' THEN 1 END) AS cancelled,
7  ROUND(
8      COUNT(CASE WHEN o.order_status = 'cancelled' THEN 1 END) * 100.0 / 
9      NULLIF(COUNT(*), 0), 1
10  ) AS cancel_rate,
11  COALESCE(ROUND(AVG(r.review_score), 2), 0) AS avg_rating_last7d
12FROM fact_orders o
13JOIN fact_order_items oi ON o.order_id = oi.order_id
14JOIN dim_seller s ON oi.seller_id = s.seller_id
15LEFT JOIN fact_reviews r ON o.order_id = r.order_id
16  AND r.review_date >= CURRENT_DATE - INTERVAL '7 days'
17WHERE o.order_date >= CURRENT_DATE - INTERVAL '7 days'
18GROUP BY s.seller_name, s.seller_type
19HAVING COUNT(CASE WHEN o.order_status = 'cancelled' THEN 1 END) * 100.0 / 
20     NULLIF(COUNT(*), 0) > 10
21ORDER BY cancel_rate DESC;

1-- Category Performance Summary
2SELECT 
3  p.category,
4  COUNT(DISTINCT oi.order_id) AS total_orders,
5  SUM(oi.price * oi.quantity) AS revenue,
6  ROUND(
7      SUM(oi.price * oi.quantity) * 100.0 / 
8      SUM(SUM(oi.price * oi.quantity)) OVER(), 1
9  ) AS revenue_share_pct,
10  COUNT(DISTINCT oi.product_id) AS active_products,
11  ROUND(AVG(r.review_score), 2) AS avg_rating,
12  
13  -- Growth vs prev period
14  ROUND(
15      (SUM(CASE WHEN o.order_date >= CURRENT_DATE - INTERVAL '30 days' 
16            THEN oi.price * oi.quantity ELSE 0 END) -
17       SUM(CASE WHEN o.order_date BETWEEN CURRENT_DATE - INTERVAL '60 days' 
18            AND CURRENT_DATE - INTERVAL '30 days' 
19            THEN oi.price * oi.quantity ELSE 0 END)) * 100.0 /
20      NULLIF(SUM(CASE WHEN o.order_date BETWEEN CURRENT_DATE - INTERVAL '60 days' 
21            AND CURRENT_DATE - INTERVAL '30 days' 
22            THEN oi.price * oi.quantity ELSE 0 END), 0), 1
23  ) AS mom_growth_pct
24FROM fact_order_items oi
25JOIN dim_product p ON oi.product_id = p.product_id
26JOIN fact_orders o ON oi.order_id = o.order_id
27LEFT JOIN fact_reviews r ON oi.order_id = r.order_id 
28  AND oi.product_id = r.product_id
29WHERE o.order_status = 'delivered'
30GROUP BY p.category
31ORDER BY revenue DESC;