Retention

Bạn sẽ học được

• Retention là gì và khác churn ở đâu.
• Định nghĩa "còn hoạt động" bằng dữ liệu thực: ended_on.
• Dùng COUNT(*) FILTER (WHERE ...) để đếm nhiều chỉ số trong một lượt quét.
• Chia retention theo cohort và theo gói.
• Vì sao ranh giới ngày và mẫu số quyết định con số cuối cùng.

Khái niệm cốt lõi

Retention là tỷ lệ người dùng còn ở lại sau một khoảng thời gian. Churn là phần bù của nó — tỷ lệ đã rời đi. Với thuê bao, "còn ở lại" thường được định nghĩa bằng ended_on IS NULL tại thời điểm quan sát, hoặc bằng việc thuê bao tồn tại đủ N ngày.

Đề bài retention tháng đầu dùng định nghĩa thứ hai:

retained_m1: ended_on là NULL hoặc ended_on >= started_on + 30.

Nghĩa là: chưa kết thúc, hoặc kết thúc sau khi đã dùng ít nhất 30 ngày. Đây là định nghĩa "sống sót qua tháng đầu", không phải "còn hoạt động ở ngày thứ 31".

Cú pháp FILTER cho phép đếm nhiều nhóm trong một lần đọc bảng:

SELECT COUNT(*) AS tong,
       COUNT(*) FILTER (WHERE ended_on IS NULL) AS dang_hoat_dong,
       COUNT(*) FILTER (WHERE ended_on IS NOT NULL) AS da_ket_thuc,
       COUNT(*) FILTER (WHERE ended_on IS NULL OR ended_on >= started_on + 30) AS giu_chan_m1
FROM subscriptions;

Kết quả 12 | 8 | 4 | 12. Mười hai thuê bao, 8 còn mở, 4 đã kết thúc, và cả 12 đều sống qua 30 ngày. Con số cuối cùng gây bất ngờ: kể cả bốn người đã rời, tất cả đều ở lại hơn một tháng.

Vì sao? Xem từng thuê bao đã kết thúc:

Ngắn nhất là 61 ngày — vẫn vượt xa ngưỡng 30. Vì vậy retention_m1_pct = 100.0 cho mọi cohort. Một kết quả "đẹp" như vậy không có nghĩa sản phẩm hoàn hảo; nó có nghĩa ngưỡng đo quá dễ. Nếu đổi sang "còn hoạt động sau 180 ngày", bức tranh sẽ khác:

SELECT COUNT(*) FILTER (WHERE ended_on IS NULL OR ended_on >= started_on + 180) AS giu_chan_6_thang,
       COUNT(*) AS tong
FROM subscriptions;

Thuê bao 2 (161 ngày), 7 (153 ngày) và 12 (112 ngày) đều không đạt; thuê bao 5 mới 61 ngày cũng vậy. Chỉ 8 thuê bao còn mở (ended_on IS NULL) được tính, vì chúng chưa kết thúc nên đương nhiên sống sót. Con số rơi từ 12 xuống 8 — đúng bản chất: không ai trong nhóm đã rời trụ được sáu tháng. Bài học: retention luôn phải đi kèm định nghĩa ngưỡng, nếu không con số vô nghĩa.

Tính theo cohort:

SELECT TO_CHAR(started_on, 'YYYY-MM') AS cohort,
       COUNT(*) AS started,
       COUNT(*) FILTER (WHERE ended_on IS NULL OR ended_on >= started_on + 30) AS retained_m1,
       ROUND(100.0 * COUNT(*) FILTER (WHERE ended_on IS NULL OR ended_on >= started_on + 30)
             / COUNT(*), 1) AS retention_m1_pct
FROM subscriptions
GROUP BY cohort
ORDER BY cohort;

Tám dòng, mỗi cohort đều 100.0:

Không có tháng 7 vì không ai bắt đầu thuê bao tháng đó. GROUP BY bỏ qua tháng trống, nên báo cáo "đủ tháng" cần generate_series.

Chia theo gói:

SELECT plan,
       COUNT(*) AS tong,
       COUNT(*) FILTER (WHERE ended_on IS NULL) AS dang_hoat_dong,
       COUNT(*) FILTER (WHERE ended_on IS NULL OR ended_on >= started_on + 30) AS retained_m1,
       ROUND(100.0 * COUNT(*) FILTER (WHERE ended_on IS NULL OR ended_on >= started_on + 30)
             / COUNT(*), 1) AS pct
FROM subscriptions
GROUP BY plan
ORDER BY plan;

Hai dòng: basic và pro đều có 6 thuê bao, 4 đang mở, 2 đã kết thúc, retention M1 100%. Cơ cấu giống hệt nhau — dữ liệu mẫu không cho thấy khác biệt giữa hai gói ở ngưỡng 30 ngày.

Tính churn tổng:

SELECT ROUND(100.0 * COUNT(*) FILTER (WHERE ended_on IS NOT NULL) / COUNT(*), 1) AS churn_pct
FROM subscriptions
WHERE started_on >= DATE '2025-01-01';

Kết quả 33.3. Bốn trên mười hai thuê bao đã kết thúc. Chú ý mẫu số ở đây là toàn bộ thuê bao bắt đầu trong năm 2025, không phải số còn hoạt động. Đổi mẫu số là đổi ý nghĩa hoàn toàn:

Ba công thức đều được gọi là "churn" trong tài liệu khác nhau. Khi đọc báo cáo của người khác, luôn tìm mẫu số trước khi so sánh với số của mình.

Ví dụ chạy được

Retention theo số ngày thực tế, thay vì ngưỡng cố định:

SELECT id, plan, started_on, ended_on,
       COALESCE(ended_on, DATE '2025-12-31') - started_on AS so_ngay,
       CASE
         WHEN ended_on IS NULL OR ended_on >= started_on + 180 THEN 'giữ chân 6 tháng'
         WHEN ended_on >= started_on + 90 THEN 'giữ chân 3 tháng'
         WHEN ended_on >= started_on + 30 THEN 'giữ chân 1 tháng'
         ELSE 'rời sớm'
       END AS nhom
FROM subscriptions
ORDER BY so_ngay DESC;

Mỗi thuê bao nhận đúng một nhóm, nhờ thứ tự nhánh trong CASE từ khắt khe nhất đến dễ nhất. Đổi thứ tự sẽ khiến mọi người rơi vào nhánh đầu tiên — lỗi rất dễ mắc.

Retention theo tháng quan sát, dùng payments làm tín hiệu hoạt động:

SELECT date_trunc('month', p.paid_on)::date AS thang,
       COUNT(DISTINCT p.subscription_id) AS so_thue_bao_tra_tien
FROM payments p
WHERE p.status = 'paid'
GROUP BY 1
ORDER BY 1;

Kết quả cho thấy số thuê bao có thanh toán thành công mỗi tháng — một định nghĩa "hoạt động" khác, dựa trên hành vi trả tiền thay vì trạng thái hợp đồng. Hai định nghĩa có thể lệch nhau khi có thanh toán thất bại hoặc hoàn tiền: trong dữ liệu mẫu có 20 giao dịch, 18 paid, 1 failed, 1 refunded.

So sánh ba định nghĩa trong một câu:

SELECT
  COUNT(DISTINCT s.id) FILTER (WHERE s.ended_on IS NULL) AS theo_hop_dong,
  COUNT(DISTINCT s.id) FILTER (WHERE s.ended_on IS NULL OR s.ended_on >= s.started_on + 30) AS song_sot_30_ngay,
  COUNT(DISTINCT p.subscription_id) FILTER (WHERE p.status = 'paid') AS co_thanh_toan
FROM subscriptions s
LEFT JOIN payments p ON p.subscription_id = s.id;

Ba con số: 8 thuê bao đang mở, 12 sống sót qua 30 ngày, và 12 thuê bao từng có ít nhất một thanh toán paid. Ba con số khác nhau cho ba câu hỏi khác nhau. Không có con số nào "đúng" tuyệt đối — chỉ có con số phù hợp với định nghĩa đã nêu.

COUNT(DISTINCT s.id) là bắt buộc vì LEFT JOIN payments nhân dòng: thuê bao 1 có ba giao dịch nên xuất hiện ba lần. Nếu dùng COUNT(*), theo_hop_dong sẽ đếm số dòng thanh toán chứ không phải số thuê bao.

Thực hành

1. Chạy câu đếm tổng và xác nhận 12 | 8 | 4 | 12.
2. Tính retention M1 theo cohort và kiểm tra cả tám dòng đều 100.
3. Đổi ngưỡng từ 30 thành 180 ngày và giải thích vì sao kết quả giảm mạnh.
4. Tính churn theo ba mẫu số khác nhau và viết một câu mô tả mỗi con số bằng lời.
5. Thêm generate_series cho 2025-01 đến 2025-12 và xác nhận tháng 7, 10, 11, 12 không có cohort.

Lỗi thường gặp

Lỗi "retention luôn 100%" không phải lỗi kỹ thuật mà là lỗi chọn chỉ số. Một chỉ số mà mọi người đều đạt không phân biệt được ai tốt ai xấu. Trước khi công bố, hãy tự hỏi: chỉ số này có thể khác nhau giữa các nhóm không? Nếu câu trả lời là không, cần ngưỡng khác hoặc chỉ số khác.

Tóm tắt

• Retention đo tỷ lệ ở lại; churn là phần bù, nhưng mẫu số phải được nêu rõ.
• FILTER đếm nhiều nhóm trong một lần quét.
• ended_on IS NULL OR ended_on >= started_on + 30 là định nghĩa sống sót 30 ngày.
• Cả 12 thuê bao đều đạt M1 — ngưỡng quá dễ, cần kiểm tra bằng ngưỡng dài hơn.
• Chọn định nghĩa "hoạt động" theo câu hỏi: hợp đồng, thanh toán, hay đăng nhập.