Bài toán retention

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

• Định nghĩa cohort và "hoạt động" trước khi viết SQL.
• Tính retention theo tháng bằng CTE và FILTER.
• Phân biệt retention, churn và quay lại.
• Chọn đúng mẫu số cho từng tháng.
• Trình bày giả định rõ ràng khi đề bài mơ hồ.

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

Câu hỏi retention trong phỏng vấn gần như luôn mơ hồ:

"Tính retention của người dùng."

Có ít nhất bốn thứ chưa được định nghĩa:

1. Cohort là gì? Nhóm người dùng đăng ký trong cùng tháng, cùng tuần, hay cùng ngày?
2. "Hoạt động" là gì? Đăng nhập, mở app, đặt hàng, hay có ít nhất một sự kiện bất kỳ?
3. Đơn vị thời gian? Retention theo ngày (D1, D7, D30) hay theo tháng (M1, M2, M3)?
4. Mẫu số? Tổng cohort ban đầu, hay số người còn hoạt động ở kỳ trước?

Trong phỏng vấn, hãy nói ra cách hiểu của bạn và đề nghị dùng cách hiểu đó. Ví dụ:

"Em định nghĩa cohort theo tháng đăng ký, hoạt động là có ít nhất một đơn hàng trong tháng, và retention tháng N là số người trong cohort còn hoạt động ở tháng thứ N chia cho toàn bộ cohort ban đầu."

Định nghĩa này cho ra classic retention. Một biến thể khác là rolling retention: còn hoạt động ở tháng N hoặc bất kỳ tháng nào sau đó. Hai cách cho số rất khác nhau, nên phải nói rõ.

Ví dụ chạy được

Bảng:

users(user_id, signup_date)
events(user_id, event_date, event_name)

Bước 1 — gán cohort.

with cohort as (
    select
        user_id,
        date_trunc('month', signup_date)::date as cohort_month
    from users
),

Bước 2 — tập hoạt động theo tháng.

activity as (
    select distinct
        user_id,
        date_trunc('month', event_date)::date as active_month
    from events
    where event_name = 'order_completed'
),

distinct là bắt buộc: một người đặt 20 đơn trong tháng vẫn chỉ là một người hoạt động.

Bước 3 — nối và tính số tháng chênh lệch.

joined as (
    select
        c.cohort_month,
        c.user_id,
        (extract(year from a.active_month) - extract(year from c.cohort_month)) * 12
        + (extract(month from a.active_month) - extract(month from c.cohort_month))
            as month_number
    from cohort c
    join activity a using (user_id)
    where a.active_month >= c.cohort_month
)

Bước 4 — tính tỷ lệ theo cohort.

Mẫu số phải là toàn bộ cohort, kể cả người không hề hoạt động lại. Vì vậy tính riêng:

cohort_size as (
    select cohort_month, count(distinct user_id) as users
    from cohort
    group by 1
)
select
    j.cohort_month,
    s.users as cohort_size,
    count(distinct j.user_id) filter (where j.month_number = 1) as m1,
    count(distinct j.user_id) filter (where j.month_number = 2) as m2,
    count(distinct j.user_id) filter (where j.month_number = 3) as m3,
    round(100.0 * count(distinct j.user_id) filter (where j.month_number = 1) / s.users, 1) as m1_pct,
    round(100.0 * count(distinct j.user_id) filter (where j.month_number = 2) / s.users, 1) as m2_pct,
    round(100.0 * count(distinct j.user_id) filter (where j.month_number = 3) / s.users, 1) as m3_pct
from joined j
join cohort_size s using (cohort_month)
group by 1, 2
order by 1;

Kết quả mẫu cho cohort tháng 1 với 1.000 người đăng ký:

Hai cách đọc bảng này:

• So với cohort ban đầu: M3 = 180/1.000 = 18%.
• So với tháng trước: M2/M1 = 240/400 = 60%; M3/M2 = 180/240 = 75%.

Cả hai đều đúng, nhưng phải nói rõ đang dùng cách nào. "Retention tháng 3 là 18%" và "retention tháng 3 là 75%" mô tả hai thứ khác nhau.

Chọn mẫu số đúng

Một lỗi rất hay gặp: tính M3 cho cohort tháng 3 vừa mới bắt đầu. Những người đó chưa có cơ hội quay lại ở tháng 6, nên tỷ lệ thấp giả tạo.

Quy tắc: chỉ tính retention tháng N cho cohort có cohort_month <= current_month - N.

where cohort_month <= date_trunc('month', current_date)::date - interval '3 months'

Nếu không lọc, bảng retention sẽ có hình tam giác: các cohort mới nhất chỉ có M0, M1, còn các cột bên phải là NULL. Người đọc dễ hiểu nhầm NULL là 0%.

Các biến thể

Ví dụ revenue retention: cohort M0 chi 100 triệu, M3 chi 120 triệu → retention doanh thu 120%, dù số người giảm còn 18%. Đây là chỉ số quan trọng với sản phẩm subscription.

Bẫy trong phỏng vấn

Đếm sự kiện thay vì đếm người. count(event_id) cho ra số sự kiện, không phải số người. Luôn count(distinct user_id).

Dùng ngày thay vì tháng khi đề hỏi tháng. Chênh lệch ngày không chia đều cho các tháng; dùng date_trunc hoặc chênh lệch năm-tháng.

Quên người dùng quay lại sau khoảng trống. Classic retention chỉ tính đúng tháng N. Người hoạt động M1, nghỉ M2, quay lại M3 vẫn tính cho M3. Không cần hoạt động liên tục.

Cohort theo ngày đăng ký nhưng báo cáo theo tháng. Phải gộp ngày vào tháng trước khi nối.

Không loại tài khoản test. Tài khoản nội bộ làm sai cả tử số và mẫu số.

Múi giờ. Sự kiện lúc 23:30 ngày cuối tháng theo giờ Việt Nam có thể rơi sang tháng sau nếu lưu UTC. Phải chốt múi giờ trước khi tính.

Cách trình bày

Trong 5 phút, nói theo thứ tự:

1. Định nghĩa: cohort theo tháng đăng ký; hoạt động là có đơn hoàn tất; retention tháng N chia cho cohort ban đầu.
2. Cấu trúc SQL: cohort → activity → nối → đếm distinct theo month_number → chia mẫu số.
3. Một con số minh hoạ: "Với cohort 1.000 người, M1 là 400 tức 40%."
4. Kiểm tra: loại tài khoản test, chốt múi giờ, chỉ tính cohort đủ tháng.
5. Mở rộng: "Nếu cần rolling retention, em đổi điều kiện thành active_month >= cohort_month + N."

Nêu được một biến thể và đánh đổi của nó thường gây ấn tượng hơn việc viết thêm 20 dòng SQL.

Thực hành

1. Viết truy vấn retention tháng cho bảng users và orders.
2. Tính M1, M2, M3 cho một cohort và kiểm tra tổng bằng tay.
3. Đổi sang rolling retention và so sánh kết quả.
4. Thêm điều kiện loại cohort chưa đủ tháng.
5. Trả lời miệng: "Retention 40% ở M1 nghĩa là gì, và 60% còn lại đã đi đâu?"

Lỗi thường gặp

Tóm tắt

• Retention cần bốn định nghĩa: cohort, hoạt động, đơn vị thời gian, mẫu số.
• Mẫu số là toàn bộ cohort ban đầu, không phải số người hoạt động tháng trước.
• count(distinct user_id) và date_trunc là hai thành phần cốt lõi.
• Chỉ tính tháng N cho cohort đã đủ N tháng.
• Nêu định nghĩa và một con số minh hoạ trước khi viết SQL.