Mở đầu — vì sao bài này quan trọng
Có một sự thật phũ phàng mà nhiều doanh nghiệp Việt Nam chỉ nhận ra khi đã quá muộn: kiếm một khách hàng mới tốn gấp 5–7 lần giữ chân một khách hàng cũ. Khi bạn đổ tiền chạy quảng cáo Facebook, Google, TikTok để kéo khách về, mà cửa sau lại rò rỉ khách đi mất, thì giống như bạn đang đổ nước vào một cái thùng thủng. Churn — hiện tượng khách hàng rời bỏ sản phẩm — chính là cái lỗ thủng đó.
Vấn đề là: phần lớn khách hàng không "thông báo" khi họ rời đi. Một người dùng MoMo không gửi email "tôi sẽ không dùng app của bạn nữa". Một khách Shopee không nhắn tin "tôi chuyển sang Lazada rồi nhé". Họ chỉ đơn giản là... biến mất. Nhiệm vụ của BA (Business Analyst) là dùng dữ liệu để phát hiện sự "biến mất" này — lý tưởng nhất là phát hiện trước khi nó xảy ra hẳn, để đội ngũ giữ chân khách (retention) còn kịp ra tay.
Bài này dạy bạn cách dùng SQL để định nghĩa churn cho đúng business của mình, viết query phát hiện khách đã rớt hoặc sắp rớt, và tính được con số churn rate mà ban lãnh đạo cần. Đây là một trong những kỹ năng phân tích "đắt giá" nhất của BA, vì nó gắn trực tiếp với doanh thu.
Khái niệm cốt lõi
Churn là gì và tại sao định nghĩa quan trọng hơn query
Churn (đọc là "chơn") nghĩa là khách hàng ngừng quan hệ với sản phẩm. Nhưng "ngừng" nghĩa là gì? Đây là điểm mấu chốt mà nhiều BA non kinh nghiệm bỏ qua: không có một định nghĩa churn chung cho mọi business. Bạn phải định nghĩa nó cùng đội kinh doanh trước khi viết bất kỳ dòng SQL nào.
Có hai loại churn lớn:
1. Contractual churn (churn theo hợp đồng/gói) — áp dụng cho mô hình subscription, nơi khách phải chủ động hủy. Ví dụ: gói cước FPT Play, Spotify Premium, gói SaaS như Base.vn. Ở đây churn rất rõ ràng: có một dòng dữ liệu ghi nhận thời điểm khách hủy, hoặc gói hết hạn mà khách không gia hạn.
2. Non-contractual churn (churn ẩn) — áp dụng cho e-commerce, ví điện tử, game, nơi khách không hề "hủy" mà chỉ ngừng dùng. Đây là loại khó hơn nhiều, vì bạn phải tự đặt ra một ngưỡng thời gian: "Nếu khách không có giao dịch nào trong X ngày, ta coi là đã churn."
Ngưỡng X này không được lấy đại. Nó phụ thuộc vào chu kỳ mua tự nhiên của sản phẩm. Một app gọi xe như Be có thể đặt ngưỡng 30 ngày (vì người ta đi lại thường xuyên). Một sàn nội thất bán giường tủ thì 90 ngày còn quá ngắn — chẳng ai mua giường mỗi quý. Quy tắc thực dụng: nhìn vào phân bố khoảng cách giữa hai lần mua liên tiếp của khách hàng (gap), rồi lấy ngưỡng ở khoảng percentile 90–95.
Active, churned, và at-risk
Khi làm churn analysis, bạn thường chia khách thành ba nhóm trạng thái dựa trên ngày hoạt động gần nhất:
- Active (đang hoạt động): có hoạt động trong khoảng thời gian gần đây, dưới ngưỡng cảnh báo.
- At-risk (có nguy cơ): đã im ắng một thời gian, vượt ngưỡng cảnh báo nhưng chưa tới ngưỡng churn. Đây là nhóm vàng — còn cứu được.
- Churned (đã rớt): im ắng quá lâu, vượt ngưỡng churn.
DATEDIFF(CURRENT_DATE, MAX(last_activity)) (MySQL) hoặc CURRENT_DATE - MAX(last_activity) (PostgreSQL).Churn rate — con số ban lãnh đạo muốn
Churn rate là tỷ lệ phần trăm khách rời đi trong một khoảng thời gian:
Churn rate = (Số khách churn trong kỳ) / (Số khách active đầu kỳ) × 100%
Ví dụ đầu tháng có 10.000 khách active, cuối tháng 500 người rớt, thì monthly churn = 5%. Nghe nhỏ, nhưng 5%/tháng nghĩa là mỗi năm bạn mất gần một nửa khách hàng (1 − 0.95^12 ≈ 46%). Đó là lý do con số này khiến CEO mất ngủ.
Tình huống thực tế
Tình huống 1 — Ví điện tử "PayQuick" và bài học chọn sai ngưỡng
PayQuick (giả định, mô phỏng MoMo/ZaloPay) có một BA tên Lan. Sếp yêu cầu báo cáo churn hàng tháng. Lan ban đầu định nghĩa churn là "không giao dịch trong 30 ngày" và báo cáo churn rate lên tới 35%/tháng — một con số gây hoảng loạn cả phòng.
Khi đào sâu, Lan phát hiện vấn đề: nhiều người dùng PayQuick chỉ dùng app để đóng tiền điện, tiền nước, tiền internet — những hóa đơn theo chu kỳ tháng hoặc thậm chí 2–3 tháng/lần. Họ hoàn toàn không churn, chỉ là chu kỳ tự nhiên dài hơn 30 ngày. Lan đã đếm nhầm người dùng bình thường thành churned.
Cô viết lại query để xem phân bố khoảng cách giao dịch:
WITH gaps AS (
SELECT user_id,
transaction_date,
LAG(transaction_date) OVER (
PARTITION BY user_id ORDER BY transaction_date
) AS prev_date
FROM transactions
)
SELECT
PERCENTILE_CONT(0.90) WITHIN GROUP (
ORDER BY transaction_date - prev_date
) AS p90_gap_days
FROM gaps
WHERE prev_date IS NOT NULL;
Kết quả: p90 của gap là 58 ngày. Lan điều chỉnh ngưỡng churn lên 60 ngày, và churn rate hợp lý hóa về 12%/tháng — đúng với cảm nhận của đội kinh doanh.
Bài học: Đừng bao giờ chọn ngưỡng churn theo cảm tính. Để dữ liệu nói. Một định nghĩa sai có thể biến một báo cáo thành tin giả nội bộ.
Tình huống 2 — Sàn TMĐT "ChợViet" và việc phát hiện at-risk kịp thời
ChợViet (giả định, mô phỏng Tiki/Sendo) muốn không chỉ biết ai đã rớt, mà ai sắp rớt để gửi voucher giữ chân. Họ định nghĩa: ngưỡng cảnh báo at-risk là 60 ngày không mua, ngưỡng churn là 90 ngày.
BA viết query phân loại toàn bộ tập khách:
SELECT
c.customer_id,
c.full_name,
MAX(o.order_date) AS last_order,
DATEDIFF(CURRENT_DATE, MAX(o.order_date)) AS days_since_last,
CASE
WHEN DATEDIFF(CURRENT_DATE, MAX(o.order_date)) > 90 THEN 'churned'
WHEN DATEDIFF(CURRENT_DATE, MAX(o.order_date)) > 60 THEN 'at_risk'
ELSE 'active'
END AS churn_status
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.full_name;
Lọc riêng nhóm at_risk rồi join thêm tổng chi tiêu lịch sử, đội marketing tập trung voucher vào nhóm at-risk có giá trị cao. Kết quả sau 2 tháng: 23% khách at-risk được "đánh thức", quay lại mua hàng — một con số mà nếu chờ họ churn hẳn thì chi phí kéo lại sẽ cao hơn gấp nhiều lần.
Bài học: Giá trị thật của churn analysis không nằm ở việc khám nghiệm tử thi (ai đã chết), mà ở việc cấp cứu kịp thời (ai sắp chết). Luôn tách riêng nhóm at-risk.
Tình huống 3 — SaaS "Basework" và contractual churn
Basework (giả định, mô phỏng Base.vn) bán phần mềm quản lý theo gói tháng. Ở đây churn rõ ràng: mỗi subscription có start_date, end_date, và status. Một khách churn nếu gói hết hạn trong kỳ mà không có gói mới nối tiếp.
BA tính monthly churn rate cho tháng 5/2026:
WITH active_start AS (
SELECT COUNT(DISTINCT account_id) AS n
FROM subscriptions
WHERE start_date <= '2026-05-01'
AND end_date >= '2026-05-01'
),
churned AS (
SELECT COUNT(DISTINCT s.account_id) AS n
FROM subscriptions s
WHERE s.end_date BETWEEN '2026-05-01' AND '2026-05-31'
AND NOT EXISTS (
SELECT 1 FROM subscriptions s2
WHERE s2.account_id = s.account_id
AND s2.start_date > s.end_date
)
)
SELECT
churned.n AS churned_accounts,
active_start.n AS active_accounts,
ROUND(100.0 * churned.n / active_start.n, 2) AS churn_rate_pct
FROM churned, active_start;
Điểm tinh tế ở đây là mệnh đề NOT EXISTS: nó loại bỏ những khách gia hạn (có gói mới bắt đầu sau khi gói cũ hết). Nếu thiếu nó, mỗi lần khách gia hạn lại bị đếm nhầm thành một lần churn.
Bài học: Trong contractual churn, cạm bẫy lớn nhất là nhầm "hết hạn rồi gia hạn" với "rời bỏ". Luôn kiểm tra xem có gói nối tiếp hay không.
Hướng dẫn từng bước
Đây là quy trình chuẩn để xây dựng một churn analysis từ con số không:
Bước 1 — Thống nhất định nghĩa churn với business. Trước khi gõ SQL, ngồi với đội kinh doanh/sản phẩm. Xác định: business của ta là contractual hay non-contractual? "Hoạt động" được tính bằng gì (mua hàng, đăng nhập, giao dịch)? Viết định nghĩa ra giấy và để mọi người ký duyệt. Đây là bước quan trọng nhất.
Bước 2 — Với non-contractual, tìm ngưỡng bằng dữ liệu. Dùng LAG() để tính khoảng cách giữa các lần hoạt động liên tiếp của mỗi khách, rồi lấy percentile 90–95 làm ngưỡng churn. Đừng đoán.
Bước 3 — Tính recency cho từng khách. Gom nhóm theo khách, lấy MAX(activity_date), rồi tính số ngày tới hôm nay.
SELECT customer_id,
MAX(order_date) AS last_active,
DATEDIFF(CURRENT_DATE, MAX(order_date)) AS recency_days
FROM orders
GROUP BY customer_id;
Bước 4 — Phân loại trạng thái bằng CASE WHEN. Áp các ngưỡng vào để gán nhãn active / at_risk / churned (như tình huống 2). Đặt query ở bước 3 vào một CTE rồi xếp loại bên ngoài cho dễ đọc.
Bước 5 — Tính churn rate tổng hợp. Đếm số khách mỗi trạng thái, chia cho tổng để ra tỷ lệ. Nếu cần churn theo tháng, dùng cách cohort như tình huống 3 (so sánh đầu kỳ vs cuối kỳ).
Bước 6 — Làm giàu nhóm at-risk để hành động. Join thêm tổng chi tiêu, hạng khách, kênh đăng ký... để đội retention biết ưu tiên ai. Một danh sách at-risk không kèm giá trị khách thì gần như vô dụng.
Bước 7 — Lên lịch chạy định kỳ. Churn không phải báo cáo chạy một lần. Biến nó thành query/view chạy hàng tuần để theo dõi xu hướng.
Lỗi thường gặp & mẹo
Lỗi 1 — Dùng INNER JOIN làm mất khách chưa từng mua lại. Khi join customers với orders bằng INNER JOIN, những khách đã rời đi từ rất lâu (hoặc chỉ mua đúng một lần rồi biến mất) vẫn còn trong orders nên không sao. Nhưng nếu bạn muốn đếm cả khách đăng ký mà chưa từng mua, INNER JOIN sẽ loại họ. Cân nhắc LEFT JOIN tùy mục tiêu.
Lỗi 2 — Quên xử lý NULL khi khách chưa có hoạt động nào. Nếu một khách có last_active = NULL, phép DATEDIFF trả về NULL và CASE WHEN có thể rơi vào nhánh ELSE sai. Hãy xử lý NULL rõ ràng, ví dụ coi họ là một nhóm "never_active" riêng.
Lỗi 3 — Lấy CURRENT_DATE trên dữ liệu cũ. Nếu bảng dữ liệu của bạn chỉ cập nhật đến tháng trước nhưng bạn so với hôm nay, mọi khách đều trông như churned. Khi làm với data warehouse có độ trễ, hãy so với "ngày dữ liệu mới nhất" (MAX(order_date) toàn bảng) thay vì CURRENT_DATE.
Lỗi 4 — Đếm trùng khi join nhiều bảng. Join với bảng order_items hoặc transactions chi tiết dễ làm một khách bị nhân bản thành nhiều dòng. Luôn dùng COUNT(DISTINCT customer_id) khi đếm khách, không phải COUNT(*).
Lỗi 5 — Nhầm gia hạn thành churn (contractual). Như tình huống 3, luôn kiểm tra gói nối tiếp bằng NOT EXISTS.
Mẹo: Khi trình bày cho sếp, đừng chỉ đưa một con số churn rate. Hãy đưa kèm xu hướng theo thời gian (tháng này so tháng trước) và phân tách theo phân khúc (churn của khách VIP khác hẳn khách thường). Một con số đơn lẻ không kể được câu chuyện.
Mẹo: Lưu định nghĩa churn và các ngưỡng vào data dictionary của bạn, để 6 tháng sau không ai phải đoán "tại sao hồi đó lấy 60 ngày".
Bài tập thực hành
Giả sử bạn có bảng orders(order_id, customer_id, order_date, amount) của một sàn TMĐT. Ngưỡng: at_risk > 45 ngày, churned > 90 ngày. Ngày "hôm nay" lấy theo dữ liệu mới nhất trong bảng.
- Cơ bản: Viết query liệt kê mỗi
customer_idkèmlast_order_datevà số ngày kể từ lần mua cuối (so vớiMAX(order_date)toàn bảng).
- Phân loại: Mở rộng query trên, thêm cột
churn_status(active / at_risk / churned) bằngCASE WHEN.
- Churn rate: Tính tỷ lệ phần trăm khách ở mỗi trạng thái trên tổng số khách.
- At-risk có giá trị: Lọc riêng nhóm at_risk, kèm tổng chi tiêu lịch sử (
SUM(amount)), sắp xếp giảm dần để đội marketing ưu tiên top 100 khách giá trị cao nhất.
- Nâng cao: Dùng
LAG()tính khoảng cách trung bình giữa các đơn của mỗi khách, rồi tìm percentile 90 của khoảng cách đó để đề xuất một ngưỡng churn hợp lý cho sàn này. So sánh với ngưỡng 90 ngày cho sẵn — nó cao hay thấp hơn?
Tóm tắt
Churn detection là kỹ năng phân tích gắn trực tiếp với doanh thu, vì giữ khách rẻ hơn nhiều so với kiếm khách mới. Những điểm cốt lõi cần nhớ:
- Định nghĩa trước, query sau. Churn có hai loại: contractual (có hủy rõ ràng) và non-contractual (phải tự đặt ngưỡng im ắng).
- Ngưỡng phải dựa trên dữ liệu, lý tưởng là percentile 90–95 của khoảng cách hoạt động tự nhiên — đừng đoán bằng cảm tính (bài học PayQuick).
- Recency (
DATEDIFFtừ hoạt động cuối) là công cụ chính để phân loại active / at-risk / churned. - Giá trị thật nằm ở nhóm at-risk — phát hiện và cứu kịp, đừng chỉ khám nghiệm khách đã chết (bài học ChợViet).
- Cẩn thận cạm bẫy: nhầm gia hạn thành churn, dùng
CURRENT_DATEtrên data trễ, đếm trùng khi join, và bỏ quên NULL.