Học SQL Qua Challenges

SQL Project: Reactivation Campaign Analysis (07 Tasks) – Task 02

71 Views
7 min read

List 07 Tasks trong Project Reactivation Campaign Analysis -> Task 01 – Task 02 – Task 03 – Task 04 – Task 05 – Task 06 – Task 07

Các Task này mô phỏng lại 1 Case trong thực tế, các bạn có thể sử dụng để áp dụng vào công việc cũng như làm Portfolio Project dùng để phỏng vấn vị trí Data Analyst.

Task 02: Gửi Email Lần 1 và Đo Lường Kết Quả

Sau khi xác định được danh sách dormant ở Task 01, đội Marketing gửi Email lần 1 với nội dung “Chúng tôi nhớ bạn! Quay lại tập luyện nào!”. Cần đo lường: bao nhiêu người mở email, bao nhiêu người bấm link, và bao nhiêu người thực sự quay lại tập.

Tính các chỉ số cho Email lần 1: Open Rate, Click-Through Rate, Reactivation Rate. Phân tích theo ngày gửi để thấy xu hướng.

Link Download Source Code Task 02 (Data Analyst BootCamp)
WITH CampaignInteractions AS (
    SELECT 
        send_id,
        -- Su dung MAX(CASE WHEN...) de chuyen tu dang nhieu dong (1 dong cho moi tuong tac) sang 1 dong voi cac cot da duoc tinh toan (has_opened, has_clicked, has_unsubscribed)
        -- Neu co it nhat 1 dong co interaction_type = 'open' thi cot has_opened se la 1, nguoc lai se la 0. Tuong tu cho click va unsubscribe.
        -- Cach nay tranh viec dem lap khi 1 email co nhieu tuong tac, chi can biet CO hay KHONG cho moi loai tuong tac.
        -- Neu su dung COUNT(CASE WHEN...) thi se dem so lan xay ra tuong tac, neu 1 email mo 3 lan thi COUNT se la 3, khong phu hop voi muc tieu chi so nay.
        MAX(CASE WHEN interaction_type = 'open' THEN 1 ELSE 0 END) AS has_opened,
        MAX(CASE WHEN interaction_type = 'click' THEN 1 ELSE 0 END) AS has_clicked,
        MAX(CASE WHEN interaction_type = 'unsubscribe' THEN 1 ELSE 0 END) AS has_unsubscribed
    FROM email_interactions
    -- dung group by de gom nhom theo send_id, moi send_id se co 1 dong ket qua voi cac cot da duoc tinh toan, tranh viec dem lap.
    GROUP BY send_id 
),
EmailMetricsRaw AS (
    SELECT
        es.send_id, -- ID cua lan gui email, dung de noi ket voi bang CampaignInteractions
        CAST(es.sent_at AS DATE) AS send_date, -- Ngay gui email, chi lay phan ngay de tinh toan theo ngay
        -- Neu su dung MAX(CASE WHEN...) ma khong co tuong tac nao thi se tra ve NULL, khi tinh tong se bi NULL, su dung COALESCE de khac phuc van de nay.
        -- cu phap COALESCE(value, default_value) se tra ve value neu value khac NULL, nguoc lai se tra ve default_value. O day neu khong co tuong tac nao thi coi nhu la 0 (chua mo, chua bam, chua huy).
        COALESCE(ci.has_opened, 0) AS is_opened,
        COALESCE(ci.has_clicked, 0) AS is_clicked,
        COALESCE(ci.has_unsubscribed, 0) AS is_unsubscribed,

        -- Tai sao su dung CASE WHEN EXISTS de kiem tra su ton tai cua it nhat 1 dong trong bang workout_completions
        -- ban chi can quan tam nguoi dung co quay lai tap hay khong, khong can dem so lan quay lai, neu co it nhat 1 dong thoa man dieu kien thi coi nhu la da quay lai.
        -- exists se tra ve TRUE neu cau lenh con (subquery) tra ve it nhat 1 dong, nguoc lai se tra ve FALSE. Khi su dung trong CASE WHEN, neu EXISTS tra ve TRUE thi se gan gia tri la 1 (da quay lai), nguoc lai se gan gia tri la 0 (chua quay lai).
        CASE 
            WHEN EXISTS (
                SELECT 1  -- SELECT 1 chi can tra ve 1 dong nao do, khong can lay cot nao cu the, vi EXISTS chi quan tam den su ton tai cua dong do hay khong, khong quan tam den noi dung cua dong do.
                FROM workout_completions wc -- bang workout_completions chua thong tin ve viec nguoi dung da quay lai tap hay chua, co cac cot user_id va completed_at
                WHERE wc.user_id = es.user_id -- dieu kien de noi ket giua workout_completions va email_sends, so sanh user_id de biet duoc nguoi nao da quay lai tap
                  AND wc.completed_at >= es.sent_at
                  AND wc.completed_at <= DATEADD(HOUR, 48, es.sent_at) -- DATEADD(HOUR, 48, es.sent_at) se tinh toan thoi gian ket thuc cua khoang 48 gio sau khi email duoc gui, neu completed_at nam trong khoang tu sent_at den sent_at + 48 gio thi coi nhu la da quay lai tap
            ) THEN 1  -- Neu co it nhat 1 dong trong bang workout_completions thoa man dieu kien tren thi coi nhu la da quay lai tap, gan gia tri la 1
            ELSE 0 -- Neu khong co dong nao trong bang workout_completions thoa man dieu kien tren thi coi nhu la chua quay lai tap, gan gia tri la 0
        END AS is_reactivated

    FROM email_sends es
    LEFT JOIN CampaignInteractions ci ON es.send_id = ci.send_id
    WHERE es.campaign_id = 1                    -- Chi lay chien dich Email lan 1
      AND es.delivery_status = 'delivered'      --- Chi tinh toan cho nhung email da duoc gui thanh cong
)

SELECT
    em.send_date, -- Ngay gui email, tinh theo ngay gui
    COUNT(*) AS total_delivered, -- Tong so email da duoc gui thanh cong (tinh theo ngay gui)
    SUM(em.is_opened) AS total_opened, -- So nguoi mo email
    SUM(em.is_clicked) AS total_clicked, -- So nguoi bam vao link trong email
    SUM(em.is_unsubscribed) AS total_unsubscribed, -- So nguoi huy dang ky nhan email
    SUM(em.is_reactivated) AS total_reactivated, -- So nguoi quay lai tap sau khi nhan email
    CAST(100.0 * SUM(em.is_opened) / COUNT(*) AS DECIMAL(5,1)) AS open_rate_pct, -- Ty le mo email (%)
    CAST(100.0 * SUM(em.is_clicked) / COUNT(*) AS DECIMAL(5,1)) AS click_through_rate_pct, -- Ty le bam vao link trong email (%)
    CAST(100.0 * SUM(em.is_reactivated) / COUNT(*) AS DECIMAL(5,1)) AS reactivation_rate_pct, -- Ty le quay lai tap sau khi nhan email (%)
    CAST(100.0 * SUM(em.is_unsubscribed) / COUNT(*) AS DECIMAL(5,1)) AS unsubscribe_rate_pct -- Ty le huy dang ky nhan email (%)
FROM EmailMetricsRaw em
GROUP BY em.send_date
ORDER BY em.send_date;

Output Mẫu

send_datetotal_deliveredtotal_openedtotal_clickedtotal_unsubscribedtotal_reactivatedopen_rate_pctclick_through_rate_pctreactivation_rate_pctunsubscribe_rate_pct
2026-02-1922111100.050.050.050.0
2026-02-212110150.050.050.00.0
2026-02-233210066.733.30.00.0
CộtÝ nghĩa
send_dateNgày gửi email – dùng để phân tích xu hướng theo thời gian
total_deliveredSố email gửi thành công
total_openedSố người mở email ra đọc
total_clickedSố người bấm vào link trong email
total_unsubscribedSố người hủy nhận email
total_reactivatedSố người thực sự quay lại tập
open_rate_pctTỷ lệ mở = opened / delivered.
click_through_rate_pctTỷ lệ bấm = clicked / delivered.
reactivation_rate_pctTỷ lệ quay lại = reactivated / delivered.
unsubscribe_rate_pctTỷ lệ hủy = unsub / delivered. Nếu > 2% thì email đang gây phiền người dùng

Trong Task này cần chú ý:

  • Không phải mọi user quay lại đều do Email. Cần phân biệt để không tính sai ROI của kênh Email.
    • VD: Không mở email nhưng vẫn quay lại tập (Reactivation Rate 100% dù Open Rate chỉ 50%). Đây là trường hợp Organic Reactivation (tự quay lại) hoặc tác động từ kênh khác (Push/Ads), không phải do Email này.
  • Gap giữa Open và Click: Mở nhưng không bấm link.
  • Rủi ro Unsubscribe cao

Kiến Thức Viết Query Tối Ưu – Thứ Mà Chỉ Khi Đi Làm Bạn Mới Biết

Sử dụng EXISTS thay cho JOIN hoặc COUNT DISTINCT

  • Cơ chế: Khi dùng JOIN, SQL phải tìm tất cả các dòng khớp nhau. Khi dùng EXISTS, SQL chỉ cần tìm thấy duy nhất một dòng thỏa mãn là nó dừng lại ngay.
  • Ứng dụng: Với task 02, không quan tâm họ tập 5 hay 10 lần, bạn chỉ cần biết họ có tập hay không. EXISTS giúp tiết kiệm tài nguyên CPU và bộ nhớ đáng kể so với việc JOIN rồi dùng COUNT(DISTINCT user_id).

Kỹ thuật Gắn cờ nhị phân

  • Thay vì đếm trực tiếp, bạn chuyển các trạng thái thành 0 và 1 (MAX(CASE…) hoặc COALESCE).
  • Lợi ích:
    • Biến dữ liệu phức tạp thành dạng số đơn giản.
    • Cho phép bạn thực hiện nhiều phép tính tổng (SUM) trên cùng một tập dữ liệu chỉ với một lần quét bảng.
    • COALESCE: Chuyển NULL thành 0 đảm bảo tính toàn vẹn của báo cáo.

Tới đây, bạn đã hoàn thành xong Task 02 trong Project Reactivation Campaign Analysis. Hãy bước tiếp sang Task 03.

Nếu bạn muốn học thực chiến SQL qua Challenges và Cases thì có thể đăng ký khóa học SQL Data Analyst Thực Chiến 100+ Challenges hoặc Data Analyst BootCamp. Inbox mình Tại Đây

You may also like