Marketing Attribution

1-- Traffic source performance: sessions, conversion, revenue
2WITH source_sessions AS (
3    SELECT
4        traffic_source,
5        COUNT(DISTINCT session_id) AS sessions,
6        COUNT(DISTINCT CASE WHEN page_type = 'Product'
7            THEN session_id END) AS product_views,
8        COUNT(DISTINCT customer_id) AS unique_visitors,
9        ROUND(AVG(duration_seconds), 1) AS avg_duration,
10        ROUND(
11            SUM(CASE WHEN is_bounce = 1 THEN 1 ELSE 0 END) * 100.0
12            / COUNT(*), 1
13        ) AS bounce_rate_pct
14    FROM fact_page_views
15    WHERE YEAR(view_date) = 2024
16    GROUP BY traffic_source
17),
18source_orders AS (
19    SELECT
20        traffic_source,
21        COUNT(DISTINCT order_id) AS orders,
22        SUM(total_amount) AS revenue,
23        COUNT(DISTINCT customer_id) AS buying_customers,
24        ROUND(AVG(total_amount), 0) AS aov
25    FROM fact_orders
26    WHERE YEAR(order_date) = 2024
27        AND status = 'Delivered'
28    GROUP BY traffic_source
29)
30SELECT
31    s.traffic_source,
32    s.sessions,
33    s.unique_visitors,
34    COALESCE(o.orders, 0) AS orders,
35    COALESCE(o.revenue, 0) AS revenue,
36    COALESCE(o.aov, 0) AS aov,
37    s.bounce_rate_pct,
38    s.avg_duration,
39    ROUND(COALESCE(o.orders, 0) * 100.0 / NULLIF(s.sessions, 0), 2) AS conversion_rate_pct,
40    ROUND(COALESCE(o.revenue, 0) * 100.0
41        / NULLIF(SUM(COALESCE(o.revenue, 0)) OVER(), 0), 1) AS revenue_share_pct
42FROM source_sessions s
43LEFT JOIN source_orders o ON s.traffic_source = o.traffic_source
44ORDER BY revenue DESC;

1-- Last-click Attribution: revenue và orders theo source
2SELECT
3    traffic_source,
4    COUNT(DISTINCT order_id) AS orders,
5    SUM(total_amount) AS revenue,
6    ROUND(AVG(total_amount), 0) AS aov,
7    COUNT(DISTINCT customer_id) AS unique_buyers,
8    ROUND(
9        SUM(total_amount) * 100.0 / SUM(SUM(total_amount)) OVER(), 1
10    ) AS revenue_attribution_pct
11FROM fact_orders
12WHERE status = 'Delivered'
13    AND YEAR(order_date) = 2024
14GROUP BY traffic_source
15ORDER BY revenue DESC;

1-- Performance theo device type
2SELECT
3    o.device_type,
4    COUNT(DISTINCT o.order_id) AS orders,
5    SUM(o.total_amount) AS revenue,
6    ROUND(AVG(o.total_amount), 0) AS aov,
7    ROUND(
8        COUNT(DISTINCT o.order_id) * 100.0
9        / SUM(COUNT(DISTINCT o.order_id)) OVER(), 1
10    ) AS order_share_pct
11FROM fact_orders o
12WHERE o.status = 'Delivered'
13    AND YEAR(o.order_date) = 2024
14GROUP BY o.device_type
15ORDER BY orders DESC;

1-- Orders theo ngày trong tuần
2SELECT
3    t.day_name,
4    t.day_of_week,
5    COUNT(DISTINCT o.order_id) AS avg_daily_orders,
6    ROUND(AVG(o.total_amount), 0) AS aov,
7    ROUND(SUM(o.total_amount) / COUNT(DISTINCT t.date_key), 0) AS avg_daily_revenue
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.day_name, t.day_of_week
13ORDER BY t.day_of_week;

1-- Monthly revenue trend + YoY comparison
2SELECT
3    t.month,
4    t.month_name,
5    SUM(CASE WHEN t.year = 2024 THEN o.total_amount ELSE 0 END) AS revenue_2024,
6    SUM(CASE WHEN t.year = 2023 THEN o.total_amount ELSE 0 END) AS revenue_2023,
7    ROUND(
8        (SUM(CASE WHEN t.year = 2024 THEN o.total_amount ELSE 0 END)
9        - SUM(CASE WHEN t.year = 2023 THEN o.total_amount ELSE 0 END))
10        * 100.0
11        / NULLIF(SUM(CASE WHEN t.year = 2023 THEN o.total_amount ELSE 0 END), 0)
12    , 1) AS yoy_growth_pct
13FROM fact_orders o
14JOIN dim_time t ON o.order_date = t.date_key
15WHERE o.status = 'Delivered'
16GROUP BY t.month, t.month_name
17ORDER BY t.month;