Unsubcribe Analysis – Pinterest (Part 03)

Để so sánh hành vi sau unsubscribe theo từng phân khúc, bước đầu tiên là gán nhãn user_state cho mỗi user tại ngày tham chiếu T_REF = 2026-05-01. Có 6 trạng thái: core, casual, marginal, new, dormant, resurrected.

                Mỗi user (anh A, chị B, anh C, ...)
                              │
                              ▼
         Đếm số ngày active trong 28 ngày trước T_REF
                              │
        ┌─────────────────────┼─────────────────────┐
        ▼                     ▼                     ▼
   signup_date              active_days           active_days
   trong 28 ngày            >= 20                 = 0
        │                     │                     │
        ▼                     ▼                     ▼
      [new]                [core]              kiểm tra trước đó
                                                    │
                                       ┌────────────┴────────────┐
                                       ▼                         ▼
                              có active xa trước              không có
                                       │                         │
                                       ▼                         ▼
                                 [dormant]              (loại khỏi tập)
        ┌─────────────────────┐
        ▼                     ▼
   active_days 8-19       active_days 1-7
        │                     │
        ▼                     ▼
    [casual]              kiểm tra 28 ngày liền trước
                              │
                  ┌───────────┴───────────┐
                  ▼                       ▼
             0 ngày active          >0 ngày active
                  │                       │
                  ▼                       ▼
            [resurrected]            [marginal]

Ví dụ minh họa

Anh A (user_id=1001): signup 2024-01-10, active 24/28 ngày → core
Chị B (user_id=2001): signup 2024-06-01, active 12/28 ngày → casual
Anh C (user_id=3001): signup 2025-01-10, active 4/28 ngày, có active trước đó → marginal
Chị D (user_id=4001): signup 2024-03-10, active 0/28 ngày, có active 2025-10 → dormant
Anh E (user_id=5001): signup 2026-04-05 (trong 28 ngày) → new
Chị F (user_id=6001): active 3/28 ngày, 28 ngày trước đó 0 ngày → resurrected

Cột	Ý Nghĩa	Ví Dụ
user_id	Mã user	1001
signup_date	Ngày đăng ký	2024-01-10
active_days_28d	Số ngày active trong 28 ngày trước T_REF	24
active_days_prev_28d	Số ngày active trong 28 ngày liền trước cửa sổ trên	22
had_activity_before_window	Có active trước cửa sổ state hay không	1/0
user_state	Nhãn phân loại cuối cùng	core

Ví dụ: (user_id=1001, active_days_28d=24, user_state=core) → user 1001 active 24/28 ngày, được xếp vào nhóm core.

• Phân phối user_state cho biết cấu trúc base người dùng tại ngày T_REF.
• Nếu nhóm marginal + dormant chiếm tỷ trọng quá lớn → cảnh báo về sức khỏe sản phẩm.
• Tỷ lệ new cao → có chiến dịch acquisition tốt nhưng cần quan sát onboarding.
• Tỷ lệ resurrected cao → các chiến dịch winback hoặc nội dung mới đang phát huy tác dụng.
• Bảng này là input bắt buộc cho các challenges sau.

/*
   PHỤ THUỘC: shared_views.sql (xem lại Part 02)
       fn_user_state(@T_REF)  — trả về user_id kèm user_state
   ================================================================ */

DECLARE @T_REF DATE = '2026-05-01';

SELECT * FROM dbo.fn_user_state(@T_REF)
ORDER BY user_state, user_id;
GO

SELECT user_state, COUNT(*) AS user_count
FROM dbo.fn_user_state(@T_REF)
GROUP BY user_state
ORDER BY user_count DESC;
GO