Bối Cảnh
Phòng Growth của một sản phẩm freemium cần biết tại một thời điểm chốt sổ (snapshot date), mỗi user đang ở trạng thái vòng đời nào. Bài toán định danh 7 trạng thái dựa trên 2 luồng: usage (sử dụng) và monetization (trả phí).
Trạng thái này là Metric thô nhất — mọi KPI/KR/Objective phía trên đều dựng từ con số này.
- Snapshot date (ngày chốt): Mốc thời gian để “chụp” trạng thái user. Mọi tính toán thời gian đều so với mốc này.
- Activation (kích hoạt): User thực hiện hành động lõi lần đầu sau đăng ký.
- Grace period (ân hạn): Khoảng thời gian sau khi hết hạn thanh toán mà vẫn coi user là đang trả phí.
Dữ Liệu Đầu Vào
Trạng thái user không lưu sẵn — phải suy ra từ hành vi thô trong 3 bảng:
Bảng users
| Cột | Ý nghĩa |
|---|---|
| user_id | Khóa chính. |
| signup_date | Ngày đăng ký. |
Bảng usage_events
| Cột | Ý nghĩa |
|---|---|
| event_id | Khóa chính. |
| user_id | Khóa ngoại. |
| event_date | Ngày dùng tính năng lõi. |
Bảng payments
| Cột | Ý nghĩa |
|---|---|
| payment_id | Khóa chính. |
| user_id | Khóa ngoại. |
| paid_at | Ngày giao dịch. |
| period_start | Ngày bắt đầu chu kỳ thanh toán. |
| period_end | Ngày kết thúc chu kỳ thanh toán. |
| amount_cents | Số tiền (> 0). |
Câu Hỏi Nghiệp Vụ
Tại snapshot date (mặc định 2024-04-30), phân loại mỗi user vào đúng một trong 7 trạng thái.
4 ngưỡng thời gian (threshold)
| Ngưỡng | Giá trị |
|---|---|
| Activation window (đăng ký → hành động lõi đầu tiên) | 7 ngày |
| Active → Inactive (không hoạt động) | > 14 ngày |
| Inactive → Churned (không hoạt động) | > 30 ngày |
| Paying → Lapsed (hết hạn + ân hạn) | grace 14 ngày sau period_end |
Định nghĩa 7 trạng thái
| Mã | Tên đầy đủ | Điều kiện |
|---|---|---|
| NS | New Sign-up | Chưa có hành động lõi và đăng ký ≤ 7 ngày. |
| AF | Active Free | Không trả phí, lần dùng gần nhất ≤ 14 ngày. |
| IF | Inactive Free | Không trả phí, lần dùng gần nhất 15–30 ngày. |
| CF | Churned Free | (Chưa hành động lõi & đăng ký > 7 ngày) hoặc (Không trả phí, lần dùng > 30 ngày, chưa từng trả phí). |
| AP | Active Paying | Đang trả phí, lần dùng gần nhất ≤ 14 ngày. |
| IP | Inactive Paying | Đang trả phí, nhưng lần dùng > 14 ngày hoặc chưa từng có hành động lõi. |
| CP | Churned Paying | Không trả phí, lần dùng > 30 ngày, từng trả phí. |
Output Mong Đợi
| Cột output | Ý nghĩa |
|---|---|
| user_id | Định danh user. |
| signup_date | Ngày đăng ký. |
| days_since_signup | Số ngày từ đăng ký tới snapshot. |
| last_usage_date | Ngày dùng gần nhất; NULL nếu chưa từng. |
| days_since_last_usage | Số ngày từ lần dùng gần nhất tới snapshot; NULL nếu chưa từng. |
| ever_paid | 1 nếu từng trả phí, ngược lại 0. |
| is_paying | 1 nếu đang trong vùng phủ thanh toán (+ grace), ngược lại 0. |
| user_state | Một trong: NS, AF, IF, CF, AP, IP, CP. |
Ví Dụ Minh Họa
Snapshot date = 2024-04-30
| user_id | days_since_signup | last_usage_date | days_since_last_usage | ever_paid | is_paying | user_state |
|---|---|---|---|---|---|---|
| 1 | 2 | NULL | NULL | 0 | 0 | NS |
| 2 | 20 | NULL | NULL | 0 | 0 | CF |
| 5 | 120 | 2024-03-20 | 41 | 0 | 0 | CF |
| 8 | 334 | 2024-03-15 | 46 | 1 | 0 | CP |
| 7 | 120 | 2024-04-05 | 25 | 1 | 1 | IP |
| 11 | 10 | NULL | NULL | 1 | 1 | IP |
| 10 | 151 | 2024-04-28 | 2 | 1 | 1 | AP |
| 12 | 151 | 2024-04-28 | 2 | 1 | 0 | AF |
- User 5 (CF) vs User 8 (CP): Cùng ngừng dùng > 30 ngày & không trả phí, chỉ khác
ever_paid. Tách được hao hụt tự nhiên (CF) và mất doanh thu (CP). - User 7 (IP) vs User 11 (IP): User 7 từng dùng rồi ngừng; User 11 chưa từng dùng. Cùng trạng thái IP nhưng
last_usage_datekhác biệt, cần can thiệp khác nhau. - User 10 (AP) vs User 12 (AF): User 10 hết hạn 2024-04-16 (cách snapshot 14 ngày) -> còn paying -> AP. User 12 hết hạn 2024-04-15 (cách snapshot 15 ngày) -> hết paying -> AF. Chỉ lệch 1 ngày đã lật trạng thái.
Tư Duy Xử Lý
Decision Tree
Thứ tự đánh giá CASE WHEN là bất biến. Nhánh hẹp hơn phải đặt trên nhánh rộng hơn.
┌─────────────────────────────┐
│ Bắt đầu: 1 dòng / user │
└──────────────┬──────────────┘
│
is_paying = 1?
┌───────────┴───────────┐
CÓ │ │ KHÔNG
▼ ▼
dlu IS NOT NULL last_usage IS NULL ?
AND dlu <= 14 ? (chưa từng hành động lõi)
┌─────────┴────────┐ ┌──────────┴──────────┐
CÓ │ │ KHÔNG CÓ│ │ KHÔNG
▼ ▼ ▼ ▼
┌────────┐ ┌────────┐ dss <= 7 ? bậc thang dlu
│ AP │ │ IP │ ┌─────┴─────┐ (làn free có usage)
└────────┘ └────────┘ ▼ ▼ ┌──────┬──────┬───────┐
┌────┐ ┌────┐ dlu dlu dlu>30
│ NS │ │ CF │ ≤14 15-30 │
└────┘ └────┘ ▼ ▼ ▼
┌────┐ ┌────┐ ever_paid?
│ AF │ │ IF │ ┌────┴────┐
└────┘ └────┘=1│ │=0
▼ ▼
┌────┐ ┌────┐
│ CP │ │ CF │
└────┘ └────┘
(Ký hiệu: dlu = days_since_last_usage, dss = days_since_signup)
Bảng ánh xạ điều kiện → nhánh CASE
| # | Điều kiện (đọc từ trên xuống, dừng ở dòng đầu thỏa) | Trạng thái |
|---|---|---|
| 1 | is_paying=1 AND dlu IS NOT NULL AND dlu<=14 | AP |
| 2 | is_paying=1 (còn lại) | IP |
| 3 | dlu IS NULL AND dss<=7 | NS |
| 4 | dlu IS NULL (còn lại) | CF |
| 5 | dlu<=14 | AF |
| 6 | dlu<=30 | IF |
| 7 | ever_paid=1 | CP |
| 8 | ELSE | CF |
Kiến trúc 3 tầng (Tái sử dụng & Tối ưu)
Đóng gói logic phân loại vào hàm inline TVF (iTVF) dbo.fn_user_state(@snapshot) thay vì viết CASE trực tiếp:
- Single source of truth: Sửa ngưỡng/trạng thái chỉ sửa ở 1 nơi.
- DRY không mất hiệu năng: iTVF được optimizer nội tuyến (inlined) vào query plan, chạy nhanh như viết CASE trực tiếp.
- Cú pháp gọi hàm cho nhiều snapshot cùng lúc dễ dàng phục vụ phân tích chuỗi thời gian.
Insight (Định vị Metric)
- Challenge này dừng ở tầng Metric (con số thô). Từ đây có thể tổng hợp lên KPI (Tỷ lệ paying, tỷ lệ active) và KR (Mục tiêu chuyển đổi).
- Action gắn với chỉ số: Đếm chính xác từng trạng thái là nền tảng để đo xác suất chuyển đổi (transition probability) ở các challenge sau. Biết được CP (mất doanh thu) hay IP (trả tiền quán tính) để có chiến lược can thiệp đúng.
Tầng Trung Gian (View)
Mục đích: Gom & chuẩn hóa từ OLTP về grain 1 dòng/user.
View này làm gì?
- Gom 3 bảng OLTP (users, usage_events, payments) về đúng 1 dòng mỗi user, giữ lại các mốc thời gian thô (raw features) cần thiết để phân loại trạng thái.
- Hiểu nhanh: giống như “phiếu tổng kết cá nhân” — mỗi user một dòng, ghi ngày đăng ký, lần dùng gần nhất, chu kỳ trả gần nhất, đã từng trả tiền chưa.
IF OBJECT_ID('dbo.v_user_state_features', 'V') IS NOT NULL
DROP VIEW dbo.v_user_state_features;
GO
CREATE VIEW dbo.v_user_state_features
AS
SELECT
u.user_id,
u.signup_date,
/*------------------------------------------------------------------
Cột: last_usage_date
MAX(event_date) từ bảng usage_events, gom theo user_id.
Ý nghĩa: Lần CUỐI CÙNG user dùng tính năng lõi.
------------------------------------------------------------------*/
ue.last_usage_date,
/*------------------------------------------------------------------
Cột: last_period_end
MAX(period_end) từ bảng payments, gom theo user_id.
Ý nghĩa: Ngày KẾT THÚC chu kỳ thanh toán gần nhất.
------------------------------------------------------------------*/
pm.last_period_end,
/*------------------------------------------------------------------
Cột: ever_paid
Kiểm tra xem user có dòng nào trong bảng payments hay không.
Ý nghĩa: Cờ nhị phân — user TỪNG trả phí ít nhất 1 lần hay chưa.
------------------------------------------------------------------*/
CASE WHEN pm.user_id IS NOT NULL
THEN 1 ELSE 0 END AS ever_paid
FROM dbo.users AS u
/*--------------------------------------------------------------------
Pre-aggregate #1: usage_events → 1 dòng/user
──────────────────────────────────────────────
Bảng gốc usage_events có grain: 1 dòng / 1 lần dùng tính năng lõi.
→ 1 user có THỂ có nhiều dòng (dùng nhiều lần).
Subquery gom: GROUP BY user_id, lấy MAX(event_date).
Kết quả: mỗi user tối đa 1 dòng, cột last_usage_date = lần dùng gần nhất.
--------------------------------------------------------------------*/
LEFT JOIN (
SELECT user_id, MAX(event_date) AS last_usage_date
FROM dbo.usage_events
GROUP BY user_id
) AS ue ON ue.user_id = u.user_id
/*--------------------------------------------------------------------
Pre-aggregate #2: payments → 1 dòng/user
──────────────────────────────────────────
Bảng gốc payments có grain: 1 dòng / 1 chu kỳ thanh toán.
→ 1 user có THỂ có nhiều dòng (trả nhiều tháng/năm).
Subquery gom: GROUP BY user_id, lấy MAX(period_end).
Kết quả: mỗi user tối đa 1 dòng, cột last_period_end =
ngày kết thúc chu kỳ GẦN NHẤT.
--------------------------------------------------------------------*/
LEFT JOIN (
SELECT user_id, MAX(period_end) AS last_period_end
FROM dbo.payments
GROUP BY user_id
) AS pm ON pm.user_id = u.user_id;
GO
SELECT COUNT(*) FROM dbo.v_user_state_features;
SELECT * FROM dbo.v_user_state_features ORDER BY user_id;
GO
Tầng Ngữ Nghĩa (Semantic Layer)
- Định nghĩa logic phân loại 7 trạng thái dưới dạng Inline.
- table-valued function (iTVF): Mọi nơi cần phân loại đều gọi hàm này, không lặp lại code.
Vì sao phải viết logic phân loại trong hàm (không viết Case thẳng trong solution)?
Single source of truth: Sửa luật phân loại chỉ sửa 1 nơi.
- Ví dụ nếu viết Case ở 3 nơi: report tuần, dashboard, pipeline đêm → cần thêm trạng thái “Dormant” → quên sửa 1 trong 3 nơi → 3 nguồn cho ra 3 kết quả khác nhau → khác dữ liệu.
- Đóng gói vào hàm → mọi nơi gọi cùng 1 hàm → sửa 1 lần, cập nhật khắp nơi.
IF OBJECT_ID('dbo.fn_user_state', 'IF') IS NOT NULL DROP FUNCTION dbo.fn_user_state;
IF OBJECT_ID('dbo.fn_user_state_ex', 'IF') IS NOT NULL DROP FUNCTION dbo.fn_user_state_ex;
GO
/*----------------------------------------------------------------------------
HÀM 1: fn_user_state_ex — 5 THAM SỐ
@snapshot : Ngày chốt sổ. Mọi DATEDIFF đều so với mốc này.
@aw : Activation Window — số ngày tối đa từ đăng ký đến hành động lõi
đầu tiên. Quá @aw ngày mà chưa dùng → coi là CF (never activates).
@inact : Ngưỡng Active → Inactive — bao nhiêu ngày không dùng thì
chuyển từ Active sang Inactive.
@churn : Ngưỡng Inactive → Churned — bao nhiêu ngày không dùng thì
chuyển từ Inactive sang Churned.
@grace : Grace Period — số ngày ân hạn sau period_end mà vẫn coi user
đang trả phí. Biên đóng: <= @grace vẫn paying.
Bước 1 (CTE feats): Quy đổi feature thô thành số ngày + cờ nhị phân.
Bước 2 (CASE WHEN): Áp luật theo thứ tự ưu tiên — nhánh HẸP trước, RỘNG sau.
----------------------------------------------------------------------------*/
CREATE FUNCTION dbo.fn_user_state_ex
(
@snapshot DATE, -- Ngày chốt sổ (snapshot date)
@aw INT, -- Activation Window (đăng ký → hành động lõi đầu)
@inact INT, -- Ngưỡng Active → Inactive (usage)
@churn INT, -- Ngưỡng Inactive → Churned (usage)
@grace INT -- Số ngày ân hạn sau period_end
)
RETURNS TABLE -- iTVF: chỉ RETURNS TABLE, không khai báo cấu trúc cột
AS
RETURN -- Ngay lập tức RETURN 1 câu SELECT duy nhất (bắt buộc iTVF)
(
/*======================================================================
BƯỚC 1: CTE "feats" — Quy đổi feature thô thành số ngày + cờ
Đầu vào: v_user_state_features (raw features từ 03_transform.sql)
- signup_date, last_usage_date, last_period_end, ever_paid
Đầu ra: thêm các cột đã tính toán:
- days_since_signup, days_since_last_usage, is_paying
======================================================================*/
WITH feats AS
(
SELECT
f.user_id,
f.signup_date,
DATEDIFF(day, f.signup_date, @snapshot) AS days_since_signup,
f.last_usage_date,
CASE WHEN f.last_usage_date IS NOT NULL
THEN DATEDIFF(day, f.last_usage_date, @snapshot) END AS days_since_last_usage,
f.ever_paid,
/*------------------------------------------------------------
is_paying = 1 nếu user CÒN trong vùng phủ thanh toán + ân hạn.
Công thức: DATEDIFF(day, last_period_end, @snapshot) <= @grace
Giải thích:
- last_period_end = ngày KẾT THÚC chu kỳ gần nhất.
- Nếu snapshot CÒN TRONG hoặc NGAY TRÊN BIÊN ân hạn
→ user vẫn được coi là đang trả phí.
- Ví dụ: period_end = 2024-04-16, snapshot = 2024-04-30
→ DATEDIFF = 14, grace = 14 → 14 <= 14 → is_paying = 1 (còn paying)
- Ví dụ: period_end = 2024-04-15, snapshot = 2024-04-30
→ DATEDIFF = 15, grace = 14 → 15 > 14 → is_paying = 0 (hết paying)
------------------------------------------------------------*/
CASE WHEN f.last_period_end IS NOT NULL
AND DATEDIFF(day, f.last_period_end, @snapshot) <= @grace
THEN 1 ELSE 0 END AS is_paying
FROM dbo.v_user_state_features AS f
)
/*======================================================================
BƯỚC 2: Áp luật phân loại — khối CASE theo thứ tự ưu tiên
"Hẹp hơn" = nhiều điều kiện AND → ít user rơi vào → đánh giá TRƯỚC.
"Rộng hơn" = ít điều kiện → nhiều user rơi vào → đánh giá SAU.
Xem lại Decision Tree
======================================================================*/
SELECT
user_id,
signup_date,
days_since_signup,
last_usage_date,
days_since_last_usage,
ever_paid,
is_paying,
CASE
WHEN is_paying = 1
AND days_since_last_usage IS NOT NULL
AND days_since_last_usage <= @inact THEN 'AP'
WHEN is_paying = 1 THEN 'IP'
WHEN days_since_last_usage IS NULL
AND days_since_signup <= @aw THEN 'NS'
WHEN days_since_last_usage IS NULL THEN 'CF'
WHEN days_since_last_usage <= @inact THEN 'AF'
WHEN days_since_last_usage <= @churn THEN 'IF'
WHEN ever_paid = 1 THEN 'CP'
ELSE 'CF'
END AS user_state
FROM feats
);
GO
/*----------------------------------------------------------------------------
HÀM 2: fn_user_state
----------------------------------------------------------------------------*/
CREATE FUNCTION dbo.fn_user_state (@snapshot DATE)
RETURNS TABLE
AS
RETURN
(
SELECT user_id, signup_date, days_since_signup, last_usage_date,
days_since_last_usage, ever_paid, is_paying, user_state
FROM dbo.fn_user_state_ex(@snapshot, 7, 14, 30, 14)
-- ↑ ↑ ↑ ↑
-- aw inact churn grace
-- 7 14 30 14 ← 4 ngưỡng CHUẨN của challenge
);
GOSolution Cuối Cùng
Phân loại toàn bộ user tại snapshot chuẩn
SELECT
user_id,
signup_date,
days_since_signup,
last_usage_date,
days_since_last_usage,
ever_paid,
is_paying,
user_state
FROM dbo.fn_user_state('2024-04-30') -- Chỉ truyền 1 tham số: ngày chốt sổ
ORDER BY user_id; -- Sắp xếp để dễ kiểm tra từng user
GOWHAT-IF — Phân tích ngưỡng nhạy cảm
- Nếu nới ngưỡng inactive từ 14 ngày lên 21 ngày thì bao nhiêu user chuyển từ IF sang AF?
- Chỉ cần thay tham số hàm fn_user_state_ex: Truyền @inact = 21 (thay vì 14), giữ nguyên 3 ngưỡng còn lại.
- Nếu logic phân loại viết rải rác ở nhiều nơi → không thể what-if nhanh.
- Nhờ iTVF → đổi 1 tham số → có kết quả ngay.
SELECT user_state, COUNT(*) AS n_users
FROM dbo.fn_user_state_ex('2024-04-30', 7, 21, 30, 14)
-- ↑ ↑ ↑ ↑
-- aw inact churn grace
-- 7 21* 30 14 ← chỉ đổi inact từ 14 → 21
GROUP BY user_state
ORDER BY user_state;
GOTham số hóa Snapshot – So sánh trạng thái giữa nhiều ngày chốt
- 1 snapshot cho biết “bức tranh tại 1 thời điểm” (as-of state).
- 2 snapshot liên tiếp cho biết “ai chuyển từ trạng thái nào sang nào” → đây chính là ma trận chuyển trạng thái (transition matrix)
- Cross Apply:
- mệnh đề VALUES tạo 1 bảng dẫn xuất d với 2 dòng (2 snapshot_date).
- Cross Apply gọi fn_user_state cho mỗi dòng của d → tương đương UNION ALL 2 câu truy vấn riêng biệt nhưng ngắn gọn hơn.
- fn_user_state là iTVF → optimizer inline vào từng CROSS APPLY, không tạo bảng tạm, không overhead gọi hàm.
SELECT d.snapshot_date, s.user_state, COUNT(*) AS n_users
FROM (VALUES (CAST('2024-04-30' AS DATE))
(CAST('2024-05-31' AS DATE))) AS d(snapshot_date)
CROSS APPLY dbo.fn_user_state(d.snapshot_date) AS s
GROUP BY d.snapshot_date, s.user_state
ORDER BY d.snapshot_date, s.user_state;
GO
Leave a Comment