SQL nâng cao 4 — Phân tích Cohort & Retention

Có một câu hỏi mà mọi lãnh đạo mảng khách hàng đều hỏi, và gần như không dashboard "tổng số khách" nào trả lời được: "Những khách mở tài khoản tháng 1 năm nay, sau nửa năm còn bao nhiêu người thực sự dùng?". Con số tổng luôn đi lên vì khách mới cứ chảy vào, che lấp việc khách cũ âm thầm rơi rụng. Muốn nhìn xuyên qua lớp che đó, ta cần cohort analysis — cắt khách thành từng nhóm theo mốc gia nhập rồi theo dõi hành vi của đúng nhóm đó qua thời gian.

Bài này dựng toàn bộ pipeline cohort → retention chỉ bằng SQL, không cần công cụ BI. Đây là kỹ thuật nối tiếp mảng phân tích chuỗi thời gian ở SQL nâng cao 3 — Gaps & Islands và là nền cho phễu chuyển đổi ở SQL nâng cao 5 — Funnel & time-series.

1. Hai khái niệm gốc

Cohort (nhóm đồng hạng) là tập hợp các đối tượng chia sẻ cùng một mốc khởi đầu trong một khoảng thời gian. Mốc phổ biến nhất trong ngân hàng là tháng mở tài khoản đầu tiên của khách (customers.created_at gom về tháng). Khách mở tài khoản tháng 3/2025 thuộc cohort 2025-03, bất kể sau đó họ làm gì. Cohort không đổi theo thời gian — một khi đã gán, khách ở nguyên cohort đó vĩnh viễn. Đây là điểm mấu chốt: ta luôn so sánh cùng một nhóm người với chính họ ở các thời điểm khác nhau.

Retention (giữ chân) là tỉ lệ thành viên của một cohort còn hoạt động sau N kỳ kể từ mốc khởi đầu. Nếu cohort 2025-03 có 100 khách, và 62 người trong số đó có giao dịch ở tháng thứ 3 sau khi mở, thì retention kỳ 3 = 62%. Chỉ số này trả lời trực tiếp câu hỏi "khách có ở lại không", điều mà tổng số khách hay tổng giao dịch không bao giờ nói được.

Cần phân biệt rõ hai trục thời gian:

Sức mạnh của cohort nằm ở trục tương đối: nó "dịch" mọi cohort về cùng gốc 0 để đặt chồng lên nhau so sánh. Cohort tháng 1 ở kỳ 3 và cohort tháng 5 ở kỳ 3 đều là "3 tháng sau khi mở" — dù rơi vào tháng lịch khác nhau.

2. Kiến trúc dựng cohort trong SQL

Toàn bộ bài toán quy về bốn bước, và mỗi bước ánh xạ thẳng vào một mảnh SQL:

Điểm cần khắc cốt: kích thước cohort đến từ bảng khách (mẫu số, cố định), còn số khách hoạt động theo kỳ đến từ bảng hoạt động (tử số, thay đổi theo n). Nối hai nguồn này bằng khoá khách là ra retention.

Ba kỹ thuật SQL cốt lõi

• date_trunc('month', ts) — "cắt cụt" một timestamp/date về đầu tháng, biến 2025-03-17 và 2025-03-29 thành cùng một giá trị 2025-03-01. Đây là công cụ gom kỳ. Đổi 'month' thành 'week' hay 'quarter' là ra biến thể tuần/quý.
• Tính khoảng kỳ — số kỳ giữa mốc hoạt động và mốc cohort. Với tháng, công thức bền vững nhất là dùng AGE() rồi quy ra số tháng: DATE_PART('year', age) * 12 + DATE_PART('month', age). AGE(a, b) trả về một interval gồm năm/tháng/ngày, tránh được lỗi khi hai mốc rơi khác năm.
• COUNT(DISTINCT customer_id) — đếm số khách phân biệt, không phải số giao dịch. Một khách giao dịch 40 lần trong tháng vẫn chỉ tính là 1 người còn hoạt động. Nhầm chỗ này là sai lệch retention nghiêm trọng nhất.

Ngoài ra FILTER (WHERE ...) giúp gom nhiều điều kiện đếm vào một lần quét bảng (pivot ngang), sẽ dùng ở phần use case.

3. Bước 1 & 2 — Gán cohort và đo kích thước

Trong sandbox, cohort của khách là tháng mở tài khoản. Nếu lấy customers.created_at trực tiếp thì đó chính là ngày khách gia nhập. Bắt đầu bằng việc đếm kích thước từng cohort — mẫu số của toàn bộ phép tính retention:

-- ▶ Chạy được
SELECT
  date_trunc('month', created_at)::date AS cohort_month,
  COUNT(*) AS cohort_size
FROM customers
GROUP BY 1
ORDER BY 1;

Mỗi khách trong customers xuất hiện đúng một lần nên COUNT(*) = số khách. Kết quả là danh sách "tháng mở → bao nhiêu khách mới". Trên dữ liệu sandbox nhỏ, có thể mỗi tháng chỉ vài khách; điều quan trọng là logic đúng — con số sẽ đúng khi chạy trên dữ liệu thật hàng chục nghìn khách.

Nếu muốn cohort dựa trên giao dịch đầu tiên thay vì ngày tạo hồ sơ (định nghĩa "khách bắt đầu hoạt động"), ta lấy MIN(created_at) của giao dịch cho mỗi khách rồi mới gom tháng:

-- ▶ Chạy được
WITH first_txn AS (
  SELECT
    a.customer_id,
    MIN(t.created_at) AS first_activity
  FROM transactions t
  JOIN accounts a ON a.id = t.account_id
  GROUP BY a.customer_id
)
SELECT
  date_trunc('month', first_activity)::date AS cohort_month,
  COUNT(*) AS cohort_size
FROM first_txn
GROUP BY 1
ORDER BY 1;

Hai định nghĩa cohort cho hai bức tranh khác nhau: "ngày mở hồ sơ" đo hiệu quả thu hút, "giao dịch đầu tiên" đo hiệu quả kích hoạt (activation). Cần thống nhất trước khi báo cáo; bài này dùng chủ đạo customers.created_at cho gọn.

4. Bước 3 — Tính "kỳ thứ n" của mỗi hoạt động

Đây là trái tim kỹ thuật. Với mỗi giao dịch, ta cần biết nó xảy ra ở kỳ thứ mấy kể từ khi khách gia nhập. Nối transactions → accounts → customers để có cả mốc hoạt động lẫn mốc cohort, rồi lấy hiệu số tháng:

-- ▶ Chạy được
SELECT
  c.id AS customer_id,
  date_trunc('month', c.created_at)::date AS cohort_month,
  date_trunc('month', t.created_at)::date AS activity_month,
  DATE_PART('year',  AGE(date_trunc('month', t.created_at),
                         date_trunc('month', c.created_at))) * 12
  + DATE_PART('month', AGE(date_trunc('month', t.created_at),
                           date_trunc('month', c.created_at))) AS period_number
FROM transactions t
JOIN accounts  a ON a.id = t.account_id
JOIN customers c ON c.id = a.customer_id
ORDER BY c.id, activity_month
LIMIT 100;

period_number = 0 nghĩa là giao dịch xảy ra ngay trong tháng khách mở; = 1 là tháng kế tiếp, v.v. Việc date_trunc cả hai vế trước khi lấy AGE đảm bảo ta đếm theo ranh giới tháng lịch, không bị lệch bởi ngày trong tháng. Nếu để nguyên ngày, AGE giữa 2025-01-31 và 2025-03-01 sẽ ra "1 tháng 1 ngày" và làm sai kỳ.

Vì sao không dùng phép trừ trực tiếp? PostgreSQL không cho date - date ra số tháng (nó ra số ngày). Còn AGE xử lý đúng số ngày khác nhau của từng tháng. Đây là mẫu chuẩn để tính kỳ tháng bền vững.

5. Bước 4 — Ma trận cohort (số khách hoạt động)

Ghép ba mảnh trên lại: gán cohort + kỳ cho từng giao dịch, rồi COUNT(DISTINCT customer_id) theo (cohort_month, period_number). Đây chính là ma trận cohort — hàng là cohort, cột là kỳ, ô là số khách còn hoạt động:

-- ▶ Chạy được
WITH activity AS (
  SELECT
    c.id AS customer_id,
    date_trunc('month', c.created_at)::date AS cohort_month,
    DATE_PART('year',  AGE(date_trunc('month', t.created_at),
                           date_trunc('month', c.created_at))) * 12
    + DATE_PART('month', AGE(date_trunc('month', t.created_at),
                             date_trunc('month', c.created_at)))::int AS period_number
  FROM transactions t
  JOIN accounts  a ON a.id = t.account_id
  JOIN customers c ON c.id = a.customer_id
)
SELECT
  cohort_month,
  period_number,
  COUNT(DISTINCT customer_id) AS active_customers
FROM activity
WHERE period_number >= 0
GROUP BY cohort_month, period_number
ORDER BY cohort_month, period_number;

Kết quả ở dạng "dài" (long): mỗi dòng là một ô của ma trận. Hình dung ma trận đầy đủ như sau — mỗi cohort là một hàng, giá trị giảm dần khi đi sang phải vì khách rơi rụng theo thời gian:

Dạng "dài" là dạng ta nên lưu và để công cụ BI xoay (pivot) thành lưới. Muốn pivot ngay trong SQL, dùng COUNT(DISTINCT ...) FILTER (WHERE period_number = k) cho từng cột — sẽ minh hoạ ở use case. Lưu ý ô period_number = 0 chính là "số khách của cohort có phát sinh giao dịch ngay tháng đầu", có thể nhỏ hơn cohort_size ở mục 3 (vì có khách mở tài khoản nhưng chưa giao dịch tháng đó).

6. Retention — chia active cho kích thước cohort

Bước cuối: nối ma trận (tử số) với kích thước cohort (mẫu số) và chia. Đây là chỗ bắt buộc ép ::numeric: phép chia hai số nguyên trong PostgreSQL cho ra số nguyên (làm tròn xuống), nên 55/100 sẽ ra 0 nếu không ép kiểu. Ép một vế sang numeric trước khi chia, rồi ROUND cũng cần ::numeric để dùng được dạng 2 tham số:

-- ▶ Chạy được
WITH activity AS (
  SELECT
    c.id AS customer_id,
    date_trunc('month', c.created_at)::date AS cohort_month,
    DATE_PART('year',  AGE(date_trunc('month', t.created_at),
                           date_trunc('month', c.created_at))) * 12
    + DATE_PART('month', AGE(date_trunc('month', t.created_at),
                             date_trunc('month', c.created_at)))::int AS period_number
  FROM transactions t
  JOIN accounts  a ON a.id = t.account_id
  JOIN customers c ON c.id = a.customer_id
),
matrix AS (
  SELECT cohort_month, period_number,
         COUNT(DISTINCT customer_id) AS active_customers
  FROM activity
  WHERE period_number >= 0
  GROUP BY cohort_month, period_number
),
sizes AS (
  SELECT date_trunc('month', created_at)::date AS cohort_month,
         COUNT(*) AS cohort_size
  FROM customers
  GROUP BY 1
)
SELECT
  m.cohort_month,
  s.cohort_size,
  m.period_number,
  m.active_customers,
  ROUND(m.active_customers::numeric / s.cohort_size, 4) AS retention_rate
FROM matrix m
JOIN sizes s ON s.cohort_month = m.cohort_month
ORDER BY m.cohort_month, m.period_number;

retention_rate là số thập phân trong khoảng 0..1 (nhân 100 nếu muốn phần trăm). Vì mẫu số cố định theo cohort, hàng period_number = 0 cho biết tỉ lệ khách kích hoạt ngay tháng đầu, và các hàng sau cho biết tỉ lệ còn ở lại. Đọc theo hàng ngang thấy đường cong tụt của một cohort; đọc theo cột dọc so sánh chất lượng các cohort ở cùng độ tuổi.

Trên sandbox dữ liệu nhỏ, có thể mỗi cohort chỉ vài khách và retention nhảy bậc thô (ví dụ 1/3 = 0.33). Logic vẫn đúng nguyên vẹn; đưa lên bảng vài chục nghìn khách là đường cong mượt và có ý nghĩa thống kê.

7. Các biến thể quan trọng

Retention theo tuần — chỉ đổi đơn vị date_trunc:

-- ▶ Chạy được
WITH activity AS (
  SELECT
    c.id AS customer_id,
    date_trunc('week', c.created_at)::date AS cohort_week,
    (date_trunc('week', t.created_at)::date
     - date_trunc('week', c.created_at)::date) / 7 AS period_number
  FROM transactions t
  JOIN accounts  a ON a.id = t.account_id
  JOIN customers c ON c.id = a.customer_id
)
SELECT cohort_week, period_number,
       COUNT(DISTINCT customer_id) AS active_customers
FROM activity
WHERE period_number >= 0
GROUP BY cohort_week, period_number
ORDER BY cohort_week, period_number;

Với tuần, vì mọi tuần đều dài đúng 7 ngày, ta có thể trừ ngày trực tiếp rồi chia 7 — gọn hơn AGE. (Chỉ mẹo này dùng được cho tuần/ngày; với tháng/quý phải quay lại AGE vì độ dài tháng không đều.)

Cohort theo thành phố — thêm chiều phân khúc bằng cách đưa customers.city vào khoá gom, để so sánh chất lượng giữ chân giữa các địa bàn:

-- ▶ Chạy được
WITH activity AS (
  SELECT
    c.id AS customer_id,
    c.city,
    date_trunc('month', c.created_at)::date AS cohort_month,
    DATE_PART('year',  AGE(date_trunc('month', t.created_at),
                           date_trunc('month', c.created_at))) * 12
    + DATE_PART('month', AGE(date_trunc('month', t.created_at),
                             date_trunc('month', c.created_at)))::int AS period_number
  FROM transactions t
  JOIN accounts  a ON a.id = t.account_id
  JOIN customers c ON c.id = a.customer_id
)
SELECT city, cohort_month, period_number,
       COUNT(DISTINCT customer_id) AS active_customers
FROM activity
WHERE period_number BETWEEN 0 AND 3
GROUP BY city, cohort_month, period_number
ORDER BY city, cohort_month, period_number;

Rolling retention khác với "classic retention" ở định nghĩa tử số. Classic đòi khách hoạt động đúng kỳ n. Rolling (còn gọi "unbounded") coi khách là còn giữ chân ở kỳ n nếu họ hoạt động ở bất kỳ kỳ nào ≥ n — nghĩa là "lần hoạt động cuối cùng rơi vào kỳ ≥ n". Rolling cho đường cong cao và mượt hơn, phù hợp sản phẩm dùng thưa (như tiền gửi có kỳ hạn) nơi khách không nhất thiết giao dịch mỗi tháng. Về SQL, thay điều kiện đếm bằng MAX(period_number) của mỗi khách rồi so với ngưỡng n.

Use case thực tế

Bối cảnh NCB. Khối Khách hàng cá nhân chạy chiến dịch mở tài khoản số đẹp đầu 2025 và cần biết: khách hút về có ở lại giao dịch không, hay chỉ mở rồi bỏ. Tổng số tài khoản vẫn tăng đều nên không ai thấy vấn đề — cho đến khi nhìn theo cohort.

Yêu cầu. Với mỗi cohort tháng mở tài khoản, cần một bảng gọn: kích thước cohort và số khách còn hoạt động ở tháng 0, 1, 2, 3 — dạng lưới ngang để dán thẳng vào báo cáo. Ta pivot bằng COUNT(DISTINCT ...) FILTER:

-- ▶ Chạy được
WITH activity AS (
  SELECT
    c.id AS customer_id,
    date_trunc('month', c.created_at)::date AS cohort_month,
    DATE_PART('year',  AGE(date_trunc('month', t.created_at),
                           date_trunc('month', c.created_at))) * 12
    + DATE_PART('month', AGE(date_trunc('month', t.created_at),
                             date_trunc('month', c.created_at)))::int AS period_number
  FROM transactions t
  JOIN accounts  a ON a.id = t.account_id
  JOIN customers c ON c.id = a.customer_id
),
sizes AS (
  SELECT date_trunc('month', created_at)::date AS cohort_month,
         COUNT(*) AS cohort_size
  FROM customers
  GROUP BY 1
)
SELECT
  s.cohort_month,
  s.cohort_size,
  COUNT(DISTINCT a.customer_id) FILTER (WHERE a.period_number = 0) AS m0,
  COUNT(DISTINCT a.customer_id) FILTER (WHERE a.period_number = 1) AS m1,
  COUNT(DISTINCT a.customer_id) FILTER (WHERE a.period_number = 2) AS m2,
  COUNT(DISTINCT a.customer_id) FILTER (WHERE a.period_number = 3) AS m3
FROM sizes s
LEFT JOIN activity a ON a.cohort_month = s.cohort_month
GROUP BY s.cohort_month, s.cohort_size
ORDER BY s.cohort_month;

Và bảng retention phần trăm tương ứng, ép ::numeric từng cột trước khi chia và ROUND:

-- ▶ Chạy được
WITH activity AS (
  SELECT
    c.id AS customer_id,
    date_trunc('month', c.created_at)::date AS cohort_month,
    DATE_PART('year',  AGE(date_trunc('month', t.created_at),
                           date_trunc('month', c.created_at))) * 12
    + DATE_PART('month', AGE(date_trunc('month', t.created_at),
                             date_trunc('month', c.created_at)))::int AS period_number
  FROM transactions t
  JOIN accounts  a ON a.id = t.account_id
  JOIN customers c ON c.id = a.customer_id
),
sizes AS (
  SELECT date_trunc('month', created_at)::date AS cohort_month,
         COUNT(*) AS cohort_size
  FROM customers
  GROUP BY 1
)
SELECT
  s.cohort_month,
  s.cohort_size,
  ROUND(COUNT(DISTINCT a.customer_id) FILTER (WHERE a.period_number = 1)::numeric
        / s.cohort_size * 100, 1) AS ret_m1_pct,
  ROUND(COUNT(DISTINCT a.customer_id) FILTER (WHERE a.period_number = 3)::numeric
        / s.cohort_size * 100, 1) AS ret_m3_pct
FROM sizes s
LEFT JOIN activity a ON a.cohort_month = s.cohort_month
GROUP BY s.cohort_month, s.cohort_size
ORDER BY s.cohort_month;

Diễn giải. Giả sử kết quả (minh hoạ trên dữ liệu thật) cho thấy cohort tháng 3 — đúng đỉnh chiến dịch số đẹp — có cohort_size lớn nhất nhưng ret_m1_pct chỉ 41% so với 63% của cohort tháng 1. Nghĩa là chiến dịch hút được nhiều khách nhưng phần lớn mở rồi bỏ: một cohort rơi rụng nhanh. LEFT JOIN giữ lại cả những cohort có khách nhưng không giao dịch kỳ nào (ô ra 0 thay vì biến mất), nên ta thấy được cả các cohort "chết". Phát hiện này chuyển hành động từ "khoe số tài khoản mới" sang "thiết kế onboarding giữ chân trong 30 ngày đầu" cho đúng nhóm khách rủi ro. Cách quy các con số này thành chỉ số theo dõi định kỳ được bàn ở BI — Metrics & KPI.

Ghi nhớ

• Cohort = nhóm theo mốc gia nhập cố định (thường là date_trunc('month', customers.created_at)); một khi gán thì khách ở nguyên cohort đó. Retention = active(n) / cohort_size.
• Phân biệt hai trục: calendar (tháng lịch thật) và kỳ n / age (số kỳ kể từ mốc cohort). Cohort mạnh vì dịch mọi nhóm về gốc 0 để so sánh cùng độ tuổi.
• Bốn bước: gán cohort → đo kích thước (mẫu số, từ customers) → tính kỳ n của hoạt động → COUNT(DISTINCT) theo (cohort, kỳ) ra ma trận (tử số, từ transactions).
• Tính kỳ tháng/quý dùng AGE() + DATE_PART (độ dài tháng không đều); kỳ tuần/ngày có thể trừ ngày rồi chia. Luôn date_trunc cả hai vế trước khi lấy AGE.
• Đếm distinct khách, không đếm giao dịch — một khách nhiều giao dịch vẫn là 1 người còn hoạt động.
• Khi chia để ra retention, ép ::numeric (số nguyên / số nguyên ra số nguyên) và ROUND(...::numeric, k).
• Dùng FILTER (WHERE period = k) để pivot ma trận ngang ngay trong SQL; LEFT JOIN từ bảng kích thước để không đánh rơi cohort "chết".
• Biến thể: retention tuần/tháng/quý (đổi date_trunc), cohort theo city, và rolling retention (đếm khách có kỳ hoạt động cuối ≥ n) cho sản phẩm dùng thưa.

Xem thêm: SQL nâng cao 3 — Gaps & Islands, SQL nâng cao 5 — Funnel & time-series, BI — Metrics & KPI.

Nguồn tham khảo

• PostgreSQL Documentation — "Date/Time Functions and Operators" (date_trunc, AGE, DATE_PART): https://www.postgresql.org/docs/current/functions-datetime.html
• PostgreSQL Documentation — "Aggregate Functions" (COUNT(DISTINCT ...), mệnh đề FILTER): https://www.postgresql.org/docs/current/functions-aggregate.html
• PostgreSQL Documentation — "Data Type Formatting Functions" và mục "Numeric Types" (ép ::numeric, ROUND): https://www.postgresql.org/docs/current/datatype-numeric.html
• Ralph Kimball, Margy Ross — The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, 3rd Edition (Wiley) — mô hình chiều thời gian và phân tích theo nhóm khách hàng.
• Kimball Group — "Dimensional Modeling Techniques" (kimballgroup.com) — nền tảng thiết kế fact/dimension cho phân tích hành vi khách hàng theo thời gian.