Phân tích cohort

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

• Cohort là gì và vì sao phân tích theo cohort tốt hơn nhìn tổng.
• Chọn mốc bắt đầu cohort: ngày đăng ký hay ngày mua/đăng ký dịch vụ đầu tiên.
• Dựng bảng kích thước cohort bằng date_trunc và GROUP BY.
• Đọc khoảng trống trong chuỗi cohort.
• Phân biệt cohort theo thời gian với phân khúc theo thuộc tính.

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

Cohort là một nhóm người dùng có cùng một mốc thời gian bắt đầu. Thay vì hỏi "trung bình khách hàng ở lại bao lâu", cohort hỏi "nhóm khách tháng 3 ở lại bao lâu so với nhóm khách tháng 4". Cách hỏi thứ hai loại bỏ ảnh hưởng của việc trộn các thế hệ khách hàng khác nhau vào một con số trung bình.

Ví dụ với bảng subscribers — mỗi người đăng ký tài khoản một lần:

SELECT date_trunc('month', signup_date)::date AS cohort,
       COUNT(*) AS so_nguoi
FROM subscribers
GROUP BY 1
ORDER BY 1;

Sáu cohort:

Mười người, chia theo tháng đăng ký. Nhóm tháng 1 gồm Ann và Bảo; tháng 2 gồm Chi và Duy; tháng 3 gồm En và Phong; tháng 4 gồm Giang và Huy; tháng 5 có Iris; tháng 6 có Jun.

Nhưng "đăng ký tài khoản" và "bắt đầu trả tiền" là hai mốc khác nhau. Nếu phân tích doanh thu, cohort nên tính theo thuê bao, không theo tài khoản:

SELECT date_trunc('month', started_on)::date AS cohort,
       COUNT(*) AS so_thue_bao
FROM subscriptions
GROUP BY 1
ORDER BY 1;

Tám cohort:

Hai điểm khác biệt đáng chú ý:

Không có cohort tháng 7. Không ai bắt đầu thuê bao trong tháng đó. GROUP BY không tạo dòng cho tháng trống — muốn thấy số 0 phải dựng generate_series như bài trước. Trong báo cáo cohort, khoảng trống này rất quan trọng: nó có thể là dấu hiệu mùa vụ, hoặc dấu hiệu kênh acquisition bị đứt.

Hai người xuất hiện hai lần. En (subscriber 5) có thuê bao tháng 3 và thuê bao mới tháng 8. Bảo (subscriber 2) có thuê bao tháng 1 và thuê bao mới tháng 9. Nếu đếm theo người, En chỉ thuộc cohort tháng 3; nếu đếm theo lần bắt đầu dịch vụ, cô thuộc cả tháng 3 và tháng 8. Cả hai cách đều đúng — nhưng phải chọn theo câu hỏi:

Việc chọn sai đơn vị khiến En bị đếm hai lần trong một báo cáo "số khách hàng" hoặc bị mất lần quay lại trong báo cáo "doanh thu". Đây không phải lỗi SQL mà là lỗi định nghĩa — và là lỗi khó phát hiện nhất trong phân tích cohort.

Một cách trình bày phổ biến là bảng cohort × kỳ: mỗi dòng một cohort, mỗi cột một tháng kể từ khi bắt đầu. Bài retention sẽ dựng bảng đó; ở đây chỉ cần nắm nguyên tắc: cột 0 là lúc bắt đầu, cột 1 là tháng thứ nhất sau bắt đầu, và mỗi ô là tỷ lệ còn lại.

Cohort khác segment ở chỗ nào? Segment chia theo thuộc tính (thành phố, gói dịch vụ, mức chi tiêu); cohort chia theo thời gian. Hai cách bổ sung cho nhau:

SELECT date_trunc('month', started_on)::date AS cohort,
       plan,
       COUNT(*) AS so_thue_bao
FROM subscriptions
GROUP BY 1, 2
ORDER BY 1, 2;

Kết quả 12 dòng — mỗi thuê bao góp một dòng vào cặp (cohort, gói). Cohort tháng 1 có 1 basic (Ann) và 1 pro (Bảo); tháng 3 có 1 basic (En) và 1 pro (Phong); tháng 5 chỉ có 1 basic (Iris); tháng 6 chỉ có 1 pro (Jun). Cách gom hai chiều này cho thấy cơ cấu gói trong từng thế hệ, và là nền tảng cho phân tích "cohort nào mua gói cao".

Ví dụ chạy được

So sánh kích thước cohort theo hai định nghĩa trong một câu:

WITH by_subscriber AS (
  SELECT date_trunc('month', signup_date)::date AS cohort, 'tai_khoan' AS loai,
         COUNT(*) AS so_luong
  FROM subscribers
  GROUP BY 1, 2
), by_subscription AS (
  SELECT date_trunc('month', started_on)::date AS cohort, 'thue_bao' AS loai,
         COUNT(*) AS so_luong
  FROM subscriptions
  GROUP BY 1, 2
)
SELECT * FROM by_subscriber
UNION ALL
SELECT * FROM by_subscription
ORDER BY cohort, loai;

Mười bốn dòng. Từ tháng 5 trở đi hai cách bắt đầu lệch nhau: tháng 8 và tháng 9 chỉ có ở cách đếm thuê bao. Đây là minh chứng định lượng cho việc định nghĩa cohort quyết định kết quả.

Cohort theo tháng đầu tiên phát sinh doanh thu, dùng bảng payments:

WITH first_payment AS (
  SELECT subscription_id, MIN(paid_on) AS ngay_dau
  FROM payments
  WHERE status = 'paid'
  GROUP BY subscription_id
)
SELECT date_trunc('month', ngay_dau)::date AS cohort_doanh_thu,
       COUNT(*) AS so_thue_bao
FROM first_payment
GROUP BY 1
ORDER BY 1;

Cách này trả lời câu hỏi "tháng nào thực sự có tiền vào". Khác với started_on khi có thanh toán thất bại hoặc hoàn tiền — bài retention sẽ bàn kỹ. Với dữ liệu mẫu, gần như trùng nhau vì hầu hết thanh toán đầu tiên diễn ra ngay ngày bắt đầu.

Đếm cohort kèm tỷ trọng:

WITH cohorts AS (
  SELECT date_trunc('month', started_on)::date AS cohort, COUNT(*) AS so_thue_bao
  FROM subscriptions
  GROUP BY 1
)
SELECT cohort, so_thue_bao,
       ROUND(100.0 * so_thue_bao / SUM(so_thue_bao) OVER (), 1) AS phan_tram
FROM cohorts
ORDER BY cohort;

Cohort tháng 1 chiếm 16,7% (2/12); tháng 5, 6, 8, 9 mỗi tháng 8,3% (1/12). Tổng 100%. Cột tỷ trọng giúp thấy ngay thế hệ nào đóng góp nhiều thuê bao nhất — hữu ích khi quyết định đầu tư kênh acquisition.

Thực hành

1. Chạy cohort theo signup_date và theo started_on. Xác nhận sáu so với tám cohort.
2. Giải thích vì sao En và Bảo xuất hiện hai lần ở cách đếm theo thuê bao.
3. Thêm cột plan vào cohort và mô tả cơ cấu gói của tháng 1 và tháng 3.
4. Dựng generate_series từ 2025-01 đến 2025-12 và nối trái với cohort để thấy tháng 7 và các tháng 10–12 bằng 0.
5. Viết một câu trả lời cho câu hỏi "tháng 4 có bao nhiêu khách bắt đầu dùng dịch vụ, gồm những ai?" — cần nối thêm bảng nào?

Lỗi thường gặp

Lỗi cuối cùng là lỗi kinh điển của báo cáo tăng trưởng: đếm doanh thu theo cohort nhưng không tách khách quay lại, khiến một người được tính ở hai thế hệ. Cách xử lý là thêm cờ la_khach_moi dựa trên lần mua đầu tiên, hoặc định nghĩa rõ mỗi cohort chỉ chứa người chưa từng xuất hiện trước đó.

Tóm tắt

• Cohort gom người dùng theo mốc bắt đầu để so sánh các thế hệ.
• Chọn mốc và đơn vị đếm theo câu hỏi: người hay thuê bao, đăng ký hay bắt đầu trả tiền.
• date_trunc + GROUP BY dựng cohort; generate_series để không mất tháng trống.
• Cohort khác segment: thời gian so với thuộc tính.
• Người quay lại có thể thuộc nhiều cohort — phải quyết định và ghi rõ.