Mở đầu — vì sao bài này quan trọng
Hãy tưởng tượng bạn là BA cho một ứng dụng giao đồ ăn. Sếp marketing hớn hở khoe: "Tháng này có 50,000 user mới đăng ký!". Nghe rất hoành tráng. Nhưng câu hỏi sống còn là: trong 50,000 người đó, sau một tháng còn bao nhiêu người thực sự quay lại đặt đơn? Nếu chỉ còn 3,000 người, thì 47,000 người kia là một cái xô thủng — bạn đang đổ tiền quảng cáo vào để rót nước qua một cái xô rò rỉ.
Đây chính là lý do cohort retention analysis (phân tích giữ chân theo nhóm) là một trong những kỹ năng phân tích quý giá nhất mà một BA có thể mang đến cho doanh nghiệp. Trong khi các báo cáo thông thường chỉ cho bạn con số tổng (tổng user, tổng doanh thu), retention analysis trả lời câu hỏi sâu hơn nhiều: Sản phẩm của chúng ta có thực sự giữ chân được người dùng theo thời gian không? Và nhóm khách nào quay lại, nhóm nào bỏ đi?
Với người làm sản phẩm và tăng trưởng, retention là chỉ số quan trọng bậc nhất. Một câu nói nổi tiếng trong giới startup: "Retention is the king" — vì nếu bạn không giữ được người dùng, mọi nỗ lực acquisition (thu hút) đều vô nghĩa. Là BA, nếu bạn viết được query trả lời câu hỏi này, bạn không còn là người "lấy số liệu" nữa, mà trở thành người giúp công ty nhìn thấy sự thật về sức khỏe sản phẩm.
Bài này tập trung hoàn toàn vào cohort retention — cách định nghĩa cohort, cách tính tỷ lệ retention qua từng kỳ, và cách dựng bảng cohort bằng SQL thuần. Các pattern phân tích liên quan như funnel, RFM, churn hay LTV sẽ được dành cho những bài riêng; ở đây ta đào sâu đúng một thứ: user quay lại.
Khái niệm cốt lõi
Cohort là gì?
Cohort (nhóm đồng hành) là một tập user chia sẻ chung một đặc điểm khởi đầu trong cùng một khoảng thời gian. Phổ biến nhất là signup cohort — nhóm theo thời điểm đăng ký. Ví dụ: "cohort tháng 1/2026" là tất cả user đăng ký lần đầu trong tháng 1/2026.
Điểm mấu chốt: một user chỉ thuộc về đúng một cohort và ở lại đó mãi mãi. User đăng ký tháng 1 thì vĩnh viễn thuộc cohort tháng 1, dù sau này họ hoạt động ở tháng nào. Đây là điều giúp ta theo dõi "số phận" của từng nhóm qua thời gian.
Ngoài signup cohort, cohort còn có thể định nghĩa theo đặc điểm khác: cohort theo kênh quảng cáo (Facebook vs Google), cohort theo thành phố (Hà Nội vs TP.HCM), cohort theo gói sản phẩm. Nhưng bản chất phân tích vẫn giống nhau.
Retention là gì?
Retention rate (tỷ lệ giữ chân) là phần trăm user của một cohort còn "active" (hoạt động) sau N kỳ kể từ lúc gia nhập. "Active" được định nghĩa tùy ngữ cảnh: với app giao đồ ăn là có đặt đơn, với SaaS là có đăng nhập, với e-commerce là có mua hàng.
Ta thường gọi:
- Period 0 (kỳ 0): kỳ đầu tiên user gia nhập — theo định nghĩa luôn là 100%.
- Period 1, 2, 3...: số kỳ trôi qua sau đó. Period 1 retention = % user của cohort còn active ở kỳ kế tiếp.
Bảng cohort (cohort table)
Kết quả cuối cùng thường được trình bày dưới dạng một bảng tam giác:
| Cohort | Period 0 | Period 1 | Period 2 | Period 3 |
|---|---|---|---|---|
| 2026-01 | 100% | 42% | 31% | 25% |
| 2026-02 | 100% | 45% | 33% | |
| 2026-03 | 100% | 40% |
Hình tam giác xuất hiện vì cohort gần đây chưa "đủ già" để có dữ liệu của các period xa. Cohort tháng 3 mới sống được 1 kỳ nên chỉ có Period 0 và Period 1.
Hai chỉ số cần phân biệt
- Period retention (classic/cohort retention): user phải active đúng ở period N. Đây là cách chuẩn ta dùng trong bài này.
- Rolling/range retention: user active ở period N hoặc bất kỳ kỳ nào sau đó. Mềm hơn, hợp với sản phẩm dùng không đều.
Tình huống thực tế
Ví dụ 1 — App giao đồ ăn "FoodFast" phát hiện cái xô thủng
FoodFast (giả định, mô phỏng kiểu Baemin/ShopeeFood) đổ 2 tỷ đồng/tháng vào quảng cáo và tự hào với 50,000 user mới mỗi tháng. Một BA mới vào dựng bảng cohort retention theo tháng dựa trên hành vi đặt đơn. Kết quả gây sốc:
| Cohort | M0 | M1 | M2 | M3 |
|---|---|---|---|---|
| 2025-10 | 100% | 12% | 7% | 5% |
| 2025-11 | 100% | 11% | 6% |
Ví dụ 2 — SaaS "Sổ Bán Hàng VN" so sánh hai cohort để đo tác động onboarding
Một startup SaaS bán phần mềm quản lý cửa hàng cho tiểu thương Việt nghi ngờ quy trình onboarding (hướng dẫn ban đầu) quá rối khiến user bỏ ngang. Tháng 4 họ tung onboarding mới gọn hơn. BA dựng bảng cohort retention theo đăng nhập hàng tuần:
| Cohort | W0 | W1 | W2 | W4 |
|---|---|---|---|---|
| Tháng 3 (cũ) | 100% | 38% | 27% | 19% |
| Tháng 4 (mới) | 100% | 51% | 40% | 31% |
Ví dụ 3 — Sàn TMĐT phân tích retention theo kênh thu hút
Một sàn TMĐT (mô phỏng Tiki) muốn biết kênh marketing nào mang về khách chất lượng chứ không chỉ đông. BA dựng cohort không theo thời gian signup mà theo kênh acquisition, đo retention mua hàng sau 30/60/90 ngày:
| Kênh | D0 | D30 | D60 | D90 |
|---|---|---|---|---|
| Organic (tìm tự nhiên) | 100% | 34% | 28% | 24% |
| Google Ads | 100% | 21% | 15% | 11% |
| Affiliate giảm giá | 100% | 9% | 4% | 2% |
Hướng dẫn từng bước
Giả sử ta có bảng orders với các cột user_id, order_date. Mục tiêu: dựng bảng monthly cohort retention dựa trên hành vi đặt đơn. Ta đi từng bước.
Bước 1 — Xác định cohort của từng user (tháng đầu tiên họ active).
Mỗi user thuộc cohort = tháng của đơn hàng đầu tiên. Dùng aggregate để tìm ngày sớm nhất:
SELECT
user_id,
DATE_TRUNC('month', MIN(order_date)) AS cohort_month
FROM orders
GROUP BY user_id;
DATE_TRUNC('month', ...) cắt ngày về đầu tháng (PostgreSQL). Trên MySQL dùng DATE_FORMAT(MIN(order_date), '%Y-%m-01').
Bước 2 — Gắn cohort vào từng hoạt động và tính số kỳ trôi qua (period number).
Với mỗi đơn hàng, ta cần biết nó xảy ra ở period mấy so với cohort của user đó. Ta join hành vi với cohort, rồi tính khoảng cách tháng:
WITH user_cohort AS (
SELECT
user_id,
DATE_TRUNC('month', MIN(order_date)) AS cohort_month
FROM orders
GROUP BY user_id
),
activity AS (
SELECT
o.user_id,
c.cohort_month,
DATE_TRUNC('month', o.order_date) AS activity_month,
(EXTRACT(YEAR FROM o.order_date) - EXTRACT(YEAR FROM c.cohort_month)) * 12
+ (EXTRACT(MONTH FROM o.order_date) - EXTRACT(MONTH FROM c.cohort_month))
AS period_number
FROM orders o
JOIN user_cohort c ON o.user_id = c.user_id
)
SELECT * FROM activity;
Ở đây period_number = 0 là tháng user gia nhập, 1 là tháng kế tiếp, v.v. Công thức nhân 12 đảm bảo tính đúng kể cả khi vắt qua năm (tháng 1/2026 so với tháng 11/2025).
Bước 3 — Đếm số user duy nhất ở mỗi (cohort, period).
... (tiếp nối CTE trên)
SELECT
cohort_month,
period_number,
COUNT(DISTINCT user_id) AS active_users
FROM activity
GROUP BY cohort_month, period_number
ORDER BY cohort_month, period_number;
COUNT(DISTINCT user_id) cực kỳ quan trọng: một user có thể đặt 5 đơn trong tháng nhưng chỉ tính là 1 người active. Quên DISTINCT là lỗi kinh điển khiến retention vọt lên trên 100%.
Bước 4 — Tính cohort size (số user ở period 0) và lấy phần trăm.
Cohort size chính là số user ở period_number = 0. Ta lấy nó làm mẫu số:
WITH user_cohort AS (...),
activity AS (...),
cohort_counts AS (
SELECT cohort_month, period_number,
COUNT(DISTINCT user_id) AS active_users
FROM activity
GROUP BY cohort_month, period_number
),
cohort_size AS (
SELECT cohort_month, active_users AS total_users
FROM cohort_counts
WHERE period_number = 0
)
SELECT
cc.cohort_month,
cc.period_number,
cc.active_users,
cs.total_users,
ROUND(100.0 * cc.active_users / cs.total_users, 1) AS retention_pct
FROM cohort_counts cc
JOIN cohort_size cs ON cc.cohort_month = cs.cohort_month
ORDER BY cc.cohort_month, cc.period_number;
Lưu ý 100.0 (số thực) để tránh integer division — nếu viết 100 * với số nguyên, nhiều DB sẽ làm tròn về 0.
Bước 5 — Xoay thành bảng tam giác (pivot) để dễ đọc.
Kết quả bước 4 là dạng "dài" (mỗi dòng một period). Để có bảng tam giác như ở trên, ta pivot bằng CASE WHEN:
SELECT
cohort_month,
MAX(CASE WHEN period_number = 0 THEN retention_pct END) AS m0,
MAX(CASE WHEN period_number = 1 THEN retention_pct END) AS m1,
MAX(CASE WHEN period_number = 2 THEN retention_pct END) AS m2,
MAX(CASE WHEN period_number = 3 THEN retention_pct END) AS m3
FROM (/ truy vấn bước 4 /) t
GROUP BY cohort_month
ORDER BY cohort_month;
Nhiều BA chỉ chạy đến bước 4 rồi đẩy dạng "dài" sang Excel hoặc Metabase để pivot ở đó — cũng hoàn toàn ổn, và thường linh hoạt hơn. Bước 5 chỉ cần khi bạn muốn bảng đẹp ngay trong SQL.
Lỗi thường gặp & mẹo
Lỗi 1 — Quên COUNT(DISTINCT) khiến retention vượt 100%. Đây là lỗi số một. Nếu bạn đếm số đơn hàng thay vì số user, một khách mua nhiều lần sẽ bị tính nhiều lần. Luôn nhớ: retention đếm người, không đếm sự kiện.
Lỗi 2 — Định nghĩa cohort không nhất quán với định nghĩa active. Nếu cohort dựa trên ngày đăng ký (bảng users) nhưng active dựa trên đặt đơn (bảng orders), thì period 0 có thể không phải 100% — vì có user đăng ký nhưng chưa bao giờ đặt đơn. Điều này không sai, nhưng bạn phải hiểu rõ và giải thích được. Khi mới học, hãy để cả cohort lẫn active cùng dựa trên một hành vi (như đặt đơn) để period 0 luôn là 100%.
Lỗi 3 — Nhầm lẫn cohort gần đây bị "thiếu period" với retention kém. Cohort tháng này chưa thể có số liệu period 3 vì chưa đủ 3 tháng trôi qua. Ô trống không phải retention = 0. Đừng so sánh cohort non với cohort già ở period mà cohort non chưa "sống tới".
Lỗi 4 — So sánh các cohort có cohort size quá nhỏ. Một cohort chỉ 20 user thì retention 40% hay 50% chênh nhau có 2 người — nhiễu thống kê. Luôn để cột total_users cạnh bên để người đọc cân nhắc độ tin cậy.
Mẹo 1 — Chọn đơn vị thời gian theo nhịp sản phẩm. Sản phẩm dùng hằng ngày (gọi xe, mạng xã hội) nên đo theo tuần. Sản phẩm dùng thưa (đặt vé máy bay, mua đồ nội thất) nên đo theo tháng hoặc quý. Chọn sai đơn vị sẽ vẽ ra bức tranh sai.
Mẹo 2 — Dùng calendar/spine để không "mất" các period rỗng. Nếu một cohort không có ai active ở period 2, query GROUP BY sẽ bỏ qua dòng đó thay vì hiện 0%. Khi cần bảng đầy đủ, tạo một bảng các period (0..N) rồi LEFT JOIN để các ô rỗng hiện ra rõ ràng.
Mẹo 3 — Bắt đầu nhỏ rồi mở rộng. Khi build query lần đầu, lọc WHERE cohort_month >= '2026-01-01' cho vài cohort để kiểm tra logic, đối chiếu tay vài user, rồi mới chạy toàn bộ.
Bài tập thực hành
Cho bảng orders(user_id, order_date, amount) của một sàn TMĐT Việt Nam.
- Cơ bản: Viết query xác định
cohort_month(tháng đặt đơn đầu tiên) của mỗi user.
- Trung bình: Dựng bảng cohort retention theo tháng dạng "dài" (cohort_month, period_number, active_users, retention_pct). Kiểm tra rằng mọi dòng
period_number = 0đều cho retention 100%. Nếu không, hãy lý giải vì sao.
- Nâng cao — pivot: Xoay kết quả câu 2 thành bảng tam giác với các cột M0–M6. Cohort nào có M1 retention cao nhất?
- Vận dụng: Thay vì cohort theo tháng signup, hãy chia cohort theo
acquisition_channel(giả sử có cột này trong bảngusers) và đo retention sau 30/60/90 ngày. Kênh nào mang lại khách ở lại lâu nhất? Viết 2–3 câu khuyến nghị cho team marketing dựa trên kết quả.
- Tư duy phản biện: Nếu một cohort có M1 = 45% nhưng cohort size chỉ 30 user, bạn có nên đưa con số này vào báo cáo trình sếp không? Vì sao? Bạn sẽ trình bày thế nào để tránh hiểu nhầm?
Tóm tắt
Cohort retention analysis trả lời câu hỏi quan trọng nhất về sức khỏe sản phẩm: user có quay lại không, và nhóm nào quay lại? Quy trình SQL gồm năm bước rõ ràng: (1) xác định cohort của mỗi user bằng MIN ngày active, (2) gắn cohort vào từng hoạt động và tính period number, (3) đếm COUNT(DISTINCT user_id) theo (cohort, period), (4) chia cho cohort size để ra phần trăm, và (5) pivot thành bảng tam giác nếu cần.
Hãy khắc cốt ba nguyên tắc: luôn đếm người chứ không phải sự kiện (DISTINCT), luôn giữ cohort size bên cạnh để đánh giá độ tin cậy, và đừng nhầm ô trống của cohort non với retention kém. Khi nắm vững, bạn không chỉ kéo được số liệu — bạn giúp công ty nhìn thấy cái xô có thủng hay không, và thủng ở đâu. Đó là giá trị thật sự của một BA giỏi.