Funnel Analysis

1-- Funnel Analysis: đếm unique sessions ở mỗi bước
2WITH funnel AS (
3    SELECT
4        -- Step 1: Tất cả sessions
5        COUNT(DISTINCT session_id) AS total_sessions,
6
7        -- Step 2: Sessions có xem Product
8        COUNT(DISTINCT CASE
9            WHEN page_type = 'Product' THEN session_id
10        END) AS product_views,
11
12        -- Step 3: Sessions có vào Cart
13        COUNT(DISTINCT CASE
14            WHEN page_type = 'Cart' THEN session_id
15        END) AS cart_sessions,
16
17        -- Step 4: Sessions có vào Checkout
18        COUNT(DISTINCT CASE
19            WHEN page_type = 'Checkout' THEN session_id
20        END) AS checkout_sessions
21    FROM fact_page_views
22    WHERE YEAR(view_date) = 2024
23)
24SELECT
25    total_sessions,
26    product_views,
27    cart_sessions,
28    checkout_sessions,
29    -- Drop-off rates
30    ROUND((1 - product_views * 1.0 / total_sessions) * 100, 1) AS visit_to_view_dropoff,
31    ROUND((1 - cart_sessions * 1.0 / product_views) * 100, 1) AS view_to_cart_dropoff,
32    ROUND((1 - checkout_sessions * 1.0 / cart_sessions) * 100, 1) AS cart_to_checkout_dropoff
33FROM funnel;

1-- Full funnel bao gồm Purchase
2WITH page_funnel AS (
3    SELECT
4        MONTH(view_date) AS month,
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 CASE WHEN page_type = 'Cart'
9            THEN session_id END) AS cart_sessions,
10        COUNT(DISTINCT CASE WHEN page_type = 'Checkout'
11            THEN session_id END) AS checkout_sessions
12    FROM fact_page_views
13    WHERE YEAR(view_date) = 2024
14    GROUP BY MONTH(view_date)
15),
16order_data AS (
17    SELECT
18        MONTH(order_date) AS month,
19        COUNT(DISTINCT order_id) AS purchases
20    FROM fact_orders
21    WHERE YEAR(order_date) = 2024
22        AND status NOT IN ('Cancelled')
23    GROUP BY MONTH(order_date)
24)
25SELECT
26    f.month,
27    f.sessions,
28    f.product_views,
29    f.cart_sessions,
30    f.checkout_sessions,
31    o.purchases,
32    ROUND(o.purchases * 100.0 / f.sessions, 2) AS overall_conversion_pct
33FROM page_funnel f
34JOIN order_data o ON f.month = o.month
35ORDER BY f.month;

1-- Funnel theo device type (Mobile vs Desktop)
2WITH device_funnel AS (
3    SELECT
4        device_type,
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 CASE WHEN page_type = 'Cart'
9            THEN session_id END) AS cart_sessions,
10        COUNT(DISTINCT CASE WHEN page_type = 'Checkout'
11            THEN session_id END) AS checkout_sessions
12    FROM fact_page_views
13    WHERE YEAR(view_date) = 2024
14    GROUP BY device_type
15),
16device_orders AS (
17    SELECT
18        device_type,
19        COUNT(DISTINCT order_id) AS purchases
20    FROM fact_orders
21    WHERE YEAR(order_date) = 2024
22        AND status NOT IN ('Cancelled')
23    GROUP BY device_type
24)
25SELECT
26    f.device_type,
27    f.sessions,
28    f.product_views,
29    f.cart_sessions,
30    o.purchases,
31    ROUND(o.purchases * 100.0 / f.sessions, 2) AS conversion_rate_pct,
32    ROUND(f.cart_sessions * 100.0 / f.product_views, 2) AS add_to_cart_rate_pct
33FROM device_funnel f
34JOIN device_orders o ON f.device_type = o.device_type
35ORDER BY f.sessions DESC;

1-- Funnel theo traffic source
2SELECT
3    pv.traffic_source,
4    COUNT(DISTINCT pv.session_id) AS sessions,
5    COUNT(DISTINCT CASE WHEN pv.page_type = 'Product'
6        THEN pv.session_id END) AS product_views,
7    COUNT(DISTINCT CASE WHEN pv.page_type = 'Cart'
8        THEN pv.session_id END) AS cart_sessions,
9    ROUND(
10        COUNT(DISTINCT CASE WHEN pv.page_type = 'Cart' THEN pv.session_id END) * 100.0
11        / NULLIF(COUNT(DISTINCT CASE WHEN pv.page_type = 'Product' THEN pv.session_id END), 0)
12    , 2) AS add_to_cart_rate_pct
13FROM fact_page_views pv
14WHERE YEAR(pv.view_date) = 2024
15GROUP BY pv.traffic_source
16ORDER BY sessions DESC;

1-- Cart Abandonment: sessions có vào Cart nhưng không Checkout
2WITH cart_analysis AS (
3    SELECT
4        session_id,
5        MAX(CASE WHEN page_type = 'Cart' THEN 1 ELSE 0 END) AS reached_cart,
6        MAX(CASE WHEN page_type = 'Checkout' THEN 1 ELSE 0 END) AS reached_checkout,
7        MAX(device_type) AS device,
8        MAX(traffic_source) AS source
9    FROM fact_page_views
10    WHERE YEAR(view_date) = 2024
11    GROUP BY session_id
12    HAVING MAX(CASE WHEN page_type = 'Cart' THEN 1 ELSE 0 END) = 1
13)
14SELECT
15    device,
16    COUNT(*) AS cart_sessions,
17    SUM(reached_checkout) AS checkout_sessions,
18    COUNT(*) - SUM(reached_checkout) AS abandoned_carts,
19    ROUND(
20        (COUNT(*) - SUM(reached_checkout)) * 100.0 / COUNT(*), 1
21    ) AS cart_abandonment_rate_pct
22FROM cart_analysis
23GROUP BY device
24ORDER BY cart_sessions DESC;