E-commerce KPIs & Metrics

1-- Tính GMV vs Net Revenue trên ShopVN
2SELECT
3    -- GMV: tất cả đơn hàng
4    SUM(total_amount) AS gmv,
5
6    -- Gross Revenue: chỉ đơn Delivered
7    SUM(CASE WHEN status = 'Delivered' THEN total_amount ELSE 0 END) AS gross_revenue,
8
9    -- Net Revenue: Delivered trừ discount
10    SUM(CASE WHEN status = 'Delivered'
11        THEN total_amount - discount_amount ELSE 0 END) AS net_revenue,
12
13    -- Tỷ lệ Cancel
14    ROUND(
15        SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) * 100.0
16        / COUNT(*), 1
17    ) AS cancel_rate_pct
18
19FROM fact_orders
20WHERE YEAR(order_date) = 2024;

1-- GMV và Revenue theo tháng 2024
2SELECT
3    MONTH(o.order_date) AS month,
4    COUNT(DISTINCT o.order_id) AS total_orders,
5    SUM(o.total_amount) AS gmv,
6    SUM(CASE WHEN o.status = 'Delivered'
7        THEN o.total_amount ELSE 0 END) AS net_revenue,
8    ROUND(AVG(CASE WHEN o.status = 'Delivered'
9        THEN o.total_amount END), 0) AS aov
10FROM fact_orders o
11WHERE YEAR(o.order_date) = 2024
12GROUP BY MONTH(o.order_date)
13ORDER BY month;

1-- Top 10 categories theo doanh thu
2SELECT
3    p.category,
4    COUNT(DISTINCT oi.order_id) AS orders,
5    SUM(oi.line_total) AS revenue,
6    ROUND(AVG(oi.line_total), 0) AS avg_item_value,
7    ROUND(
8        SUM(CASE WHEN oi.is_returned = 1 THEN 1 ELSE 0 END) * 100.0
9        / COUNT(*), 1
10    ) AS return_rate_pct
11FROM fact_order_items oi
12JOIN dim_product p ON oi.product_id = p.product_id
13JOIN fact_orders o ON oi.order_id = o.order_id
14WHERE o.status = 'Delivered'
15GROUP BY p.category
16ORDER BY revenue DESC
17LIMIT 10;

1-- AOV theo tuần để phát hiện trends
2SELECT
3    t.year,
4    t.week_of_year,
5    COUNT(DISTINCT o.order_id) AS orders,
6    ROUND(AVG(o.total_amount), 0) AS aov,
7    ROUND(AVG(o.item_count), 1) AS avg_items_per_order
8FROM fact_orders o
9JOIN dim_time t ON o.order_date = t.date_key
10WHERE o.status = 'Delivered'
11    AND t.year = 2024
12GROUP BY t.year, t.week_of_year
13ORDER BY t.week_of_year;

1-- Ước lượng Conversion Rate theo tháng
2WITH monthly_visitors AS (
3    SELECT
4        MONTH(view_date) AS month,
5        COUNT(DISTINCT session_id) AS unique_sessions
6    FROM fact_page_views
7    WHERE YEAR(view_date) = 2024
8    GROUP BY MONTH(view_date)
9),
10monthly_orders AS (
11    SELECT
12        MONTH(order_date) AS month,
13        COUNT(DISTINCT order_id) AS total_orders
14    FROM fact_orders
15    WHERE YEAR(order_date) = 2024
16        AND status != 'Cancelled'
17    GROUP BY MONTH(order_date)
18)
19SELECT
20    v.month,
21    v.unique_sessions,
22    o.total_orders,
23    ROUND(o.total_orders * 100.0 / v.unique_sessions, 2) AS conversion_rate_pct
24FROM monthly_visitors v
25JOIN monthly_orders o ON v.month = o.month
26ORDER BY v.month;

1-- Phân tích New vs Returning customers theo tháng
2WITH first_purchase AS (
3    SELECT
4        customer_id,
5        MIN(order_date) AS first_order_date
6    FROM fact_orders
7    WHERE status = 'Delivered'
8    GROUP BY customer_id
9)
10SELECT
11    MONTH(o.order_date) AS month,
12    COUNT(DISTINCT CASE
13        WHEN MONTH(fp.first_order_date) = MONTH(o.order_date)
14            AND YEAR(fp.first_order_date) = YEAR(o.order_date)
15        THEN o.customer_id END) AS new_customers,
16    COUNT(DISTINCT CASE
17        WHEN MONTH(fp.first_order_date) != MONTH(o.order_date)
18            OR YEAR(fp.first_order_date) != YEAR(o.order_date)
19        THEN o.customer_id END) AS returning_customers
20FROM fact_orders o
21JOIN first_purchase fp ON o.customer_id = fp.customer_id
22WHERE YEAR(o.order_date) = 2024
23    AND o.status = 'Delivered'
24GROUP BY MONTH(o.order_date)
25ORDER BY month;

1-- Repeat Purchase Rate tổng thể
2SELECT
3    COUNT(DISTINCT customer_id) AS total_customers,
4    COUNT(DISTINCT CASE WHEN order_count >= 2
5        THEN customer_id END) AS repeat_customers,
6    ROUND(
7        COUNT(DISTINCT CASE WHEN order_count >= 2 THEN customer_id END) * 100.0
8        / COUNT(DISTINCT customer_id), 1
9    ) AS repeat_rate_pct
10FROM (
11    SELECT
12        customer_id,
13        COUNT(DISTINCT order_id) AS order_count
14    FROM fact_orders
15    WHERE status = 'Delivered'
16    GROUP BY customer_id
17) customer_orders;

1-- Cancel Rate theo payment method
2SELECT
3    payment_method,
4    COUNT(*) AS total_orders,
5    SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled,
6    ROUND(
7        SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) * 100.0
8        / COUNT(*), 1
9    ) AS cancel_rate_pct
10FROM fact_orders
11WHERE YEAR(order_date) = 2024
12GROUP BY payment_method
13ORDER BY cancel_rate_pct DESC;

1-- Return Rate theo product category
2SELECT
3    p.category,
4    COUNT(*) AS total_items,
5    SUM(oi.is_returned) AS returned_items,
6    ROUND(
7        SUM(oi.is_returned) * 100.0 / COUNT(*), 1
8    ) AS return_rate_pct,
9    ROUND(AVG(oi.unit_price), 0) AS avg_price
10FROM fact_order_items oi
11JOIN dim_product p ON oi.product_id = p.product_id
12GROUP BY p.category
13ORDER BY return_rate_pct DESC;

1-- On-time Delivery Rate theo shipping method
2SELECT
3    shipping_method,
4    COUNT(*) AS delivered_orders,
5    ROUND(AVG(delivery_days), 1) AS avg_days,
6    MIN(delivery_days) AS min_days,
7    MAX(delivery_days) AS max_days,
8    ROUND(
9        SUM(CASE
10            WHEN shipping_method = 'Standard' AND delivery_days <= 5 THEN 1
11            WHEN shipping_method = 'Express' AND delivery_days <= 2 THEN 1
12            WHEN shipping_method = 'Same-day' AND delivery_days <= 1 THEN 1
13            ELSE 0
14        END) * 100.0 / COUNT(*), 1
15    ) AS on_time_rate_pct
16FROM fact_orders
17WHERE status = 'Delivered'
18    AND delivery_days IS NOT NULL
19GROUP BY shipping_method;