Challenge này thuộc khóa học Data Driven Insight – Từ Business Domain Đến SQL Analyst với AI – Learning by Doing 100+ Challenges Thực Tế. Để đăng ký khóa học này, bạn có thể inbox mình TẠI ĐÂY
Mindset: Cùng Một Con Số, Hai Câu Chuyện Khác Nhau
Một doanh nghiệp bán phần mềm theo dạng đăng ký (SaaS) thường công bố ba con số: số lượng khách hàng, số lượng khách hàng trả tiền, và tổng doanh thu. Cả ba con số này đều là giá trị chụp tại một thời điểm — giống như chụp một bức ảnh tại thời điểm đó.
Tuy nhiên, tăng trưởng thực tế sinh ra từ sự thay đổi giữa hai thời điểm — có khách hàng mới thêm vào, có khách hàng cũ rời đi, có khách hàng nâng cấp gói dịch vụ, có khách hàng hạ gói dịch vụ. Một con số chụp tại một thời điểm không cho thấy những thay đổi đó đứng sau nó như thế nào.
Ví dụ: doanh thu tháng này bằng doanh thu tháng trước. Điều này có thể ứng với hai tình huống hoàn toàn khác nhau:
- Tình huống 1: Không có khách hàng nào rời đi, không có khách hàng nào thêm vào. Mọi thứ giữ nguyên.
- Tình huống 2: Nhiều khách hàng rời đi, nhưng phần doanh thu mất đi được bù đắp bởi những khách hàng còn lại nâng cấp gói dịch vụ đắt hơn.
Cùng một con số doanh thu không đổi, nhưng bản chất của hai tình huống này rất khác nhau.
Ví dụ cụ thể: 10 khách hàng, cùng một tháng, hai kết luận trái ngược
Xét một nhóm 10 khách hàng, mỗi khách hàng trả $25 mỗi tháng. Tổng doanh thu ban đầu là $250 mỗi tháng. Kịch bản diễn ra trong tháng tiếp theo
- 6 khách hàng rời đi (không trả tiền nữa), mang theo $150 doanh thu mỗi tháng.
- 4 khách hàng còn lại quyết định nâng cấp gói dịch vụ. Tổng doanh thu từ 4 khách hàng này tăng từ $100 lên $310.
Bây giờ tính hai chỉ số:
- Chỉ số 1: Đo theo số lượng khách hàng (Logo Retention)
- 4 khách hàng còn lại / 10 khách hàng ban đầu = 40%
- Kết luận: 60% khách hàng rời đi. Tỷ lệ giữ chân khách hàng thấp. Doanh nghiệp đang mất khách hàng nghiêm trọng.
- Chỉ số 2: Đo theo doanh thu (Net Dollar Retention)
- Doanh thu hiện tại từ nhóm này / Doanh thu ban đầu = $310 / $250 = 124%
- Kết luận: Doanh thu từ nhóm khách hàng này tăng 24% so với ban đầu. Doanh nghiệp duy trì và tăng trưởng được doanh thu từ khách hàng hiện có.→ Doanh thu tăng 24%
Hai kết luận trái ngược nhưng đều đúng. Cùng một nhóm 10 khách hàng, cùng một tháng:
- Đo theo số lượng khách hàng → mất 60% → rò rỉ nặng.
- Đo theo doanh thu → tăng 24% → bền vững.
Hai kết luận này không mâu thuẫn với nhau. Chúng mô tả hai khía cạnh khác nhau của cùng một tập dữ liệu:
- Khía cạnh số lượng: Phần lớn khách hàng rời đi. Điều này cho thấy sản phẩm hoặc dịch vụ không giữ được số đông.
- Khía cạnh doanh thu: Một nhóm nhỏ khách hàng còn lại nâng cấp gói dịch vụ đủ mạnh để bù đắp toàn bộ phần doanh thu mất do 6 khách hàng rời đi, và còn vượt thêm. Điều này cho thấy sản phẩm mang lại giá trị cao cho những khách hàng phù hợp.
Trạng thái này gọi là rò rỉ về số lượng khách hàng nhưng bền vững về doanh thu.
Vì sao một chỉ số duy nhất là không đủ
Nếu chỉ theo dõi một đường cong tổng hợp — ví dụ “tổng doanh thu theo tháng” — thì doanh nghiệp chỉ nhìn thấy một đường đi lên hoặc đi xuống. Đường cong đó trộn lẫn hai nhóm khách hàng (nhóm rời đi và nhóm nâng cấp) thành một con số duy nhất, nên không tách được từng nhóm ra để xem riêng.
Khi không tách được, doanh nghiệp không biết được:
- Khách hàng rời đi nhiều hay ít?
- Doanh thu tăng đến từ khách hàng mới hay khách hàng cũ nâng cấp?
- Nên tập trung giữ chân khách hàng hay khuyến khích nâng cấp?
Để hiểu rõ cả hai khía cạnh, cần theo dõi hai chỉ số đặt cạnh nhau:
| Chỉ số | Đo cái gì | Ý nghĩa |
|---|---|---|
| Logo Retention (tỷ lệ giữ chân theo số lượng khách hàng) | Số lượng khách hàng | Cho biết bao nhiêu khách hàng tiếp tục trả tiền sau một thời gian |
| Net Dollar Retention / NDR (tỷ lệ giữ chân theo doanh thu) | Giá trị doanh thu | Cho biết doanh thu từ nhóm khách hàng ban đầu thay đổi thế nào sau một thời gian (tính cả nâng cấp và hạ gói) |
Khoảng cách giữa hai chỉ số này chính là thước đo định lượng cho hiện tượng “rò rỉ về số lượng nhưng bền vững về doanh thu”. Khoảng cách càng lớn, nghĩa là sự chênh lệch giữa số lượng khách hàng rời đi và mức độ nâng cấp của nhóm khách hàng còn lại càng rõ rệt.
Challenge
1. Yêu Cầu
1.1. Phân biệt giá trị chụp tại một thời điểm và giá trị thay đổi giữa hai thời điểm
Dữ liệu đầu vào là giá trị chụp tại một thời điểm: mỗi tháng, mỗi khách hàng trả bao nhiêu tiền. Con số này cho biết tình trạng tại tháng đó, nhưng không cho biết điều gì đã xảy ra để dẫn đến tình trạng đó.
Điều cần biết là giá trị thay đổi giữa hai thời điểm: khách hàng nào mới đến, khách hàng nào nâng cấp gói dịch vụ, khách hàng nào hạ gói dịch vụ, khách hàng nào rời đi. Chỉ những thay đổi này mới giải thích được tăng trưởng.
Ví dụ: doanh thu tháng này là $10,000. Con số này không cho biết nó đến từ “thêm ít khách, mất ít khách” hay “thêm nhiều khách, mất nhiều khách”. Cần nhìn vào sự thay đổi giữa tháng trước và tháng này mới hiểu được.
1.2. Xác định nhóm khách hàng gốc theo tháng liền trước
Với mỗi tháng cần phân tích (gọi là tháng M), nhóm khách hàng gốc là những khách hàng đang trả tiền ở tháng liền trước đó (tháng M−1).
Khách hàng mới xuất hiện lần đầu ở tháng M không được tính vào nhóm gốc. Lý do: các chỉ số giữ chân chỉ đo diễn biến của những khách hàng đã có sẵn từ trước. Nếu trộn khách hàng mới vào nhóm gốc, con số sẽ bị sai lệch vì khách hàng mới luôn “còn ở lại” ở tháng đầu tiên (họ vừa mới đến).
- Nhóm gốc = khách hàng trả tiền ở tháng M−1.
- Khách hàng mới ở tháng M = nhóm riêng, không tính vào retention.
1.3. Tính tỷ lệ giữ chân theo số lượng khách hàng (Logo Retention)
Lấy nhóm khách hàng gốc ở tháng M−1. Đếm xem trong số đó, bao nhiêu khách hàng vẫn còn trả tiền ở tháng M.
Logo Retention = (số khách hàng vẫn còn trả tiền ở tháng M) / (số khách hàng trong nhóm gốc ở tháng M−1) × 100%Ví dụ: nhóm gốc có 10 khách hàng. Ở tháng M, chỉ còn 4 khách hàng trả tiền
- Logo Retention = 4 / 10 × 100% = 40%
- Ý nghĩa: Tỷ lệ này cho biết phần trăm khách hàng tiếp tục trả tiền sau một tháng. Tỷ lệ thấp nghĩa là phần lớn khách hàng chỉ trả 1–2 tháng rồi ngừng. Đây là góc nhìn theo số lượng khách hàng.
1.4. Tính tỷ lệ giữ chân theo doanh thu (Net Dollar Retention — NDR)
Lấy cùng nhóm khách hàng gốc ở tháng M−1. Tính tổng doanh thu định kỳ (MRR) của nhóm này ở tháng M−1, gọi là doanh thu đầu kỳ. Sau đó tính tổng doanh thu của cùng nhóm đó ở tháng M, gọi là doanh thu cuối kỳ.
NDR = doanh thu cuối kỳ / doanh thu đầu kỳ × 100%Lưu ý: doanh thu cuối kỳ bao gồm cả phần tăng do khách hàng nâng cấp và phần giảm do khách hàng hạ gói hoặc rời đi.
Ví dụ: nhóm gốc có 10 khách hàng, mỗi người trả $25/tháng. Doanh thu đầu kỳ = $250. Trong tháng M, 6 khách hàng rời đi (mất $150), 4 khách hàng còn lại nâng cấp lên gói đắt hơn, tổng doanh thu của 4 người này là $310.
- NDR = 310 / 250 × 100% = 124%
- Ý nghĩa: NDR lớn hơn 100% nghĩa là doanh thu từ nhóm khách hàng cũ tăng so với ban đầu, ngay cả khi một nửa số khách hàng đã rời đi. Đây là góc nhìn theo giá trị doanh thu.
1.5. Tách chỉ số GDR (Gross Dollar Retention) ra khỏi NDR
NDR bao gồm cả phần doanh thu tăng do khách hàng nâng cấp. Để biết phần doanh thu giữ được nếu không tính phần nâng cấp, cần một chỉ số riêng gọi là GDR (Gross Dollar Retention).
GDR = (doanh thu đầu kỳ − doanh thu mất do khách rời đi − doanh thu giảm do khách hạ gói) / doanh thu đầu kỳ × 100%GDR không cộng phần doanh thu tăng do nâng cấp. Vì không cộng phần tăng, GDR luôn không vượt quá 100%.
NDR = GDR + phần trăm doanh thu tăng do nâng cấp (expansion%)Lý do phải tách GDR ra riêng:
- Cùng một NDR = 125% có thể đến từ hai cấu trúc hoàn toàn khác nhau:
| Cấu trúc | GDR | Expansion | Ý nghĩa |
|---|---|---|---|
| Cấu trúc A | 120% | 5% | Mất rất ít doanh thu, nâng cấp thêm một chút → nền tảng vững |
| Cấu trúc B | 80% | 45% | Mất nhiều doanh thu, nâng cấp bù đắp lớn → nền tảng yếu, dựa vào nâng cấp để bù |
- Hai cấu trúc này cho cùng một NDR là 125%, nhưng bản chất rất khác nhau. Chỉ khi nhìn GDR riêng biệt mới phân biệt được “giữ được doanh thu vì ít mất” với “giữ được doanh thu vì nâng cấp bù đắp”.
1.6. Phân rã NDR thành ba luồng doanh thu
NDR không nên trình bày như một con số tổng duy nhất. NDR cần được phân rã thành ba luồng doanh thu riêng biệt:
| Luồng | Định nghĩa | Ví dụ |
|---|---|---|
| Doanh thu mất do khách rời đi (churn $) | Phần doanh thu của khách hàng tụt về 0 (ngừng trả tiền hoàn toàn) | Khách hàng trả $25/tháng, ngừng trả → mất $25 |
| Doanh thu giảm do khách hạ gói (contraction $) | Phần doanh thu giảm của khách hàng vẫn còn hoạt động nhưng chuyển sang gói rẻ hơn | Khách hàng từ gói $50/tháng xuống gói $25/tháng → giảm $25 |
| Doanh thu tăng do khách nâng cấp (expansion $) | Phần doanh thu tăng của khách hàng nâng cấp lên gói đắt hơn | Khách hàng từ gói $25/tháng lên gói $50/tháng → tăng $25 |
NDR = 100% − (churn% + contraction%) + expansion%- churn% = doanh thu mất do khách rời đi / doanh thu đầu kỳ
- contraction% = doanh thu giảm do khách hạ gói / doanh thu đầu kỳ
- expansion% = doanh thu tăng do khách nâng cấp / doanh thu đầu kỳ
1.7. Đặt hai chỉ số cạnh nhau và đo khoảng cách giữa chúng
retention_gap_pp = NDR% − Logo Retention%- Gap dương lớn (ví dụ +50 điểm phần trăm): NDR cao nhưng Logo Retention thấp. Điều này cho thấy doanh nghiệp đang ở trạng thái mất nhiều khách hàng về số lượng nhưng doanh thu vẫn tăng nhờ nhóm khách hàng còn lại nâng cấp. Đây là bằng chứng định lượng cho trạng thái đó.
- Gap thấp hoặc âm: Hai góc nhìn (số lượng và doanh thu) đồng thuận với nhau. Nếu cả hai đều cao, doanh nghiệp khỏe. Nếu cả hai đều thấp, doanh nghiệp đang yếu.
2. Output mong muốn
| # | Tên cột | Ý nghĩa |
|---|---|---|
| 1 | analysis_month | Tháng đang xét (tháng M). Nhóm khách hàng gốc là những khách hàng đang trả tiền ở tháng M−1. |
| 2 | customers_start | Số lượng khách hàng trong nhóm gốc (số khách hàng trả tiền ở tháng M−1). Đây là mẫu số của Logo Retention. |
| 3 | logo_retention_pct | Tỷ lệ phần trăm khách hàng trong nhóm gốc vẫn còn trả tiền ở tháng M. Góc nhìn theo số lượng khách hàng → nghiêng về phát hiện rò rỉ. |
| 4 | gross_dollar_retention_pct | GDR = (doanh thu đầu kỳ − doanh thu mất do rời đi − doanh thu giảm do hạ gói) / doanh thu đầu kỳ. Đo doanh thu giữ được nếu không tính phần nâng cấp. Không vượt quá 100%. Thể hiện sức bền thuần của nhóm gốc. |
| 5 | dollar_quick_ratio | Doanh thu tăng do nâng cấp / (doanh thu mất do rời đi + doanh thu giảm do hạ gói). Cho biết mỗi $1 doanh thu mất đi được bù bằng bao nhiêu $ tăng thêm. Lớn hơn 1 = phần tăng do nâng cấp thắng phần mất. Trống (không có giá trị) = tháng đó không mất đồng nào. |
| 6 | net_dollar_retention_pct | Doanh thu cuối kỳ / doanh thu đầu kỳ của nhóm gốc. Góc nhìn theo giá trị doanh thu → nghiêng về phát hiện bền vững. Phần tăng do nâng cấp suy ra bằng cột 6 trừ cột 4. |
| 7 | retention_gap_pp | Cột 6 trừ cột 3 (đơn vị: điểm phần trăm). Kết quả chính: gap dương lớn = mất nhiều khách hàng về số lượng nhưng doanh thu vẫn tăng. |
Data Model
1. Mô hình dữ liệu ghi nhận giao dịch (OLTP)
Đây là cấu trúc dữ liệu phản ánh cách hệ thống thanh toán ghi nhận giao dịch thực tế. Mỗi lần thu tiền từ một khách hàng trong một tháng được ghi thành một dòng riêng biệt.
Bảng 1: oltp.customers
| Cột | Vai trò |
|---|---|
| customer_id | Mã định danh duy nhất của mỗi khách hàng (khóa chính) |
| signup_date | Ngày khách hàng đăng ký |
| country | Quốc gia của khách hàng |
Bảng 2: oltp.billing_log
| Cột | Vai trò |
|---|---|
| billing_id | Mã định danh duy nhất của mỗi giao dịch (khóa chính) |
| customer_id | Mã khách hàng thực hiện giao dịch (khóa ngoại, liên kết với bảng customers) |
| billing_date | Ngày ghi nhận giao dịch |
| plan_tier | Gói dịch vụ khách hàng đang sử dụng |
| mrr_amount | Số tiền khách hàng trả trong tháng đó (Monthly Recurring Revenue) |
| status | Trạng thái giao dịch |
Mô hình này được thiết kế để ghi nhận từng sự kiện khi nó xảy ra. Hệ thống liên tục thêm dòng mới mỗi khi có giao dịch thanh toán. Dữ liệu được chuẩn hóa: không lặp thông tin dư thừa giữa các bảng.
Mô hình này phù hợp cho việc ghi nhận giao dịch hàng ngày nhưng không tối ưu cho việc tổng hợp số liệu theo nhiều góc nhìn (ví dụ: tính doanh thu theo tháng, theo quốc gia, theo gói dịch vụ cùng lúc). Để phân tích, cần chuyển sang một cấu trúc khác tối ưu hơn cho việc tổng hợp.
Trong mô hình này, một khách hàng không hoạt động trong một tháng sẽ không có dòng nào trong bảng billing_log cho tháng đó. Việc vắng mặt dòng giao dịch đồng nghĩa với việc khách hàng đã ngừng trả tiền. Đây là chi tiết quan trọng khi tính các chỉ số giữ chân: cần phát hiện sự vắng mặt để xác định khách hàng đã rời đi.
2. Mô hình dữ liệu phân tích (OLAP)
Để phân tích và tính các chỉ số giữ chân, cần chuyển dữ liệu từ mô hình ghi nhận giao dịch sang mô hình tối ưu cho việc tổng hợp theo nhiều chiều. Mô hình phân tích này sử dụng cấu trúc gọi là star schema (lược đồ hình sao).
- Gom dữ liệu: Từ bảng billing_log (nhiều dòng cho một khách hàng trong một tháng), gom lại thành một dòng duy nhất cho mỗi khách hàng trong mỗi tháng. Số tiền khách hàng trả trong tháng đó được ghi vào cột mrr_amount.
- Dòng dữ liệu đã gom được đặt vào một bảng trung tâm gọi là bảng fact (bảng sự kiện).
- Liên kết với bảng mô tả: Bảng fact liên kết với các bảng mô tả (bảng chiều) để thêm thông tin về khách hàng và thời gian.
Bảng 1: olap.dim_customer
| Cột | Vai trò |
|---|---|
| customer_key | Mã định danh duy nhất trong hệ thống phân tích (khóa chính) |
| customer_id | Mã khách hàng gốc, liên kết với dữ liệu giao dịch |
| country | Quốc gia của khách hàng |
Bảng 2: olap.dim_date
| Cột | Vai trò |
|---|---|
| date_key | Mã định danh duy nhất cho mỗi ngày (khóa chính) |
| full_date | Ngày đầy đủ (ví dụ: 2025-01-01) |
| month_start | Ngày đầu tiên của tháng chứa ngày đó |
| month_label | Nhãn tháng dạng văn bản (ví dụ: “2025-01”) |
| month_index | Số thứ tự tháng liên tục trong toàn bộ lịch |
month_index là số thứ tự tháng liên tục trong toàn bộ lịch. Ví dụ: tháng 2024-01 có month_index = 1, tháng 2024-02 có month_index = 2, … tháng 2025-01 có month_index = 13. Số thứ tự này liên tục qua ranh giới năm, không reset lại vào tháng 1 năm sau. Tính liên tục này cho phép nối hai tháng liền kề bằng phép tính đơn giản: tháng tiếp theo = month_index + 1. Nếu dùng tháng và năm riêng biệt, việc nối qua ranh giới năm (tháng 12 năm trước sang tháng 1 năm sau) sẽ phức tạp hơn.
Bảng 3: olap.fact_customer_mrr
| Cột | Vai trò |
|---|---|
| customer_key | Khóa ngoại, liên kết với bảng dim_customer |
| date_key | Khóa ngoại, liên kết với bảng dim_date (trỏ đến ngày đầu tháng) |
| mrr_amount | Doanh thu định kỳ hàng tháng của khách hàng trong tháng đó |
Solution
/* ===========================================================================
PHÂN TÍCH: TÍNH CÁC CHỈ SỐ GIỮ CHÂN KHÁCH HÀNG
===========================================================================
Mục tiêu của toàn bộ phần này:
- Từ dữ liệu doanh thu chụp tại từng tháng (giá trị tĩnh), tái tạo lại
các thay đổi giữa hai tháng liền kề (giá trị động): ai mới đến, ai rời đi,
ai nâng cấp, ai hạ gói.
- Tính hai chỉ số đặt cạnh nhau:
+ Logo Retention : giữ được bao nhiêu phần trăm khách hàng (theo số lượng)
+ Net Dollar Retention (NDR) : doanh thu từ nhóm khách hàng cũ thay đổi
thế nào (theo giá trị tiền)
- Đo khoảng cách giữa hai chỉ số đó. Khoảng cách dương lớn nghĩa là tình trạng
"mất nhiều khách hàng về số lượng nhưng doanh thu vẫn tăng".
Cấu trúc: 5 bước (B1 đến B5), viết dưới dạng CTE (Common Table Expression —
mỗi bước là một khối WITH, kết quả của bước trước dùng làm đầu vào cho bước sau).
===========================================================================*/
WITH
/* ===========================================================================
BƯỚC 1 — LẤY DỮ LIỆU ĐẦU KỲ: MỖI KHÁCH HÀNG × MỖI THÁNG ĐANG TRẢ TIỀN
===========================================================================
Mục đích:
- Lấy ra tất cả các dòng trong bảng fact_customer_mrr. Mỗi dòng là doanh thu
định kỳ (MRR) của một khách hàng trong một tháng.
- Ghi nhận thêm month_index (số thứ tự tháng) từ bảng dim_date để biết tháng
liền sau đó là tháng nào (month_index + 1).
Giải thích từng cột:
- customer_key : mã khách hàng trong hệ thống phân tích
- start_index : số thứ tự tháng của tháng hiện tại (gọi là tháng gốc M-1)
- start_mrr : số tiền khách hàng trả trong tháng gốc đó
Lưu ý: Bảng fact_customer_mrr chỉ chứa những dòng có mrr_amount > 0
(khách hàng đang trả tiền). Khách hàng không trả tiền trong một tháng sẽ
KHÔNG có dòng nào trong bảng fact cho tháng đó. Đặc điểm này quan trọng:
sự vắng mặt dòng = khách hàng đã ngừng trả tiền = churn.
=========================================================================== */
base AS (
SELECT f.customer_key, -- mã khách hàng
d.month_index AS start_index, -- số thứ tự tháng gốc (M-1)
f.mrr_amount AS start_mrr -- doanh thu tháng gốc
FROM olap.fact_customer_mrr f -- bảng fact: 1 dòng = 1 khách × 1 tháng
JOIN olap.dim_date d ON d.date_key = f.date_key -- nối với bảng thời gian để lấy month_index
),
/* ===========================================================================
BƯỚC 2 — NỐI THÁNG GỐC VỚI THÁNG LIỀN SAU: TÁI TẠO SỰ THAY ĐỔI
===========================================================================
Mục đích:
- Với mỗi khách hàng trong tháng gốc (M-1), tìm xem khách hàng đó còn trả
tiền ở tháng liền sau (M) hay không.
- Nếu KHÔNG tìm thấy dòng nào cho khách hàng đó ở tháng M, ghi nhận
end_mrr = 0. Điều này có nghĩa là khách hàng đã rời đi (churn).
Đây chính là bước chuyển từ "giá trị tĩnh" sang "giá trị động":
- Giá trị tĩnh: biết doanh thu của mỗi khách ở mỗi tháng riêng biệt.
- Giá trị động: so sánh doanh thu của cùng khách hàng giữa hai tháng liền kề
để phát hiện sự thay đổi.
Chi tiết kỹ thuật:
1. INNER JOIN dim_date (với điều kiện day_num = 1):
- Chỉ giữ những tháng gốc mà tháng liền sau đó CÓ TỒN TẠI trong bảng
dim_date. Nếu tháng M nằm ngoài khoảng ngày đã dựng sẵn (2024-01-01
đến 2026-12-31), dòng đó bị loại bỏ.
- Điều kiện day_num = 1 giới hạn chỉ lấy ngày đầu tháng, vì fact
tham chiếu dim_date qua ngày đầu tháng.
2. LEFT JOIN fact_customer_mrr:
- LEFT JOIN giữ lại tất cả khách hàng từ tháng gốc, kể cả khi không
tìm thấy dòng nào ở tháng M.
- Khi không tìm thấy, COALESCE trả về 0.00 cho end_mrr.
- Việc nối thực hiện qua customer_key và month_index (= start_index + 1),
KHÔNG nối qua date_key trực tiếp vì date_key trong fact là ngày giao dịch
thực tế, không nhất thiết là ngày đầu tháng.
3. WHERE EXISTS:
- Chỉ giữ lại những tháng gốc mà tháng liền sau có ÍT NHẤT một dòng dữ
liệu trong bảng fact. Điều này loại bỏ các tháng không có dữ liệu giao
dịch (ví dụ: tháng chưa thu tiền, tháng ngoài khoảng dữ liệu).
=========================================================================== */
paired AS (
SELECT b.customer_key, -- mã khách hàng
b.start_index, -- số thứ tự tháng gốc (M-1)
b.start_index + 1 AS end_index, -- số thứ tự tháng liền sau (M)
b.start_mrr, -- doanh thu tháng gốc
COALESCE(f.mrr_amount, 0.00) AS end_mrr -- doanh thu tháng liền sau (0 nếu khách rời đi)
FROM base b -- dữ liệu đầu kỳ từ Bước 1
/* Nối với bảng thời gian: chỉ giữ tháng gốc nếu tháng liền sau tồn tại */
INNER JOIN olap.dim_date d_next
ON d_next.month_index = b.start_index + 1 -- tháng liền sau = tháng gốc + 1
AND d_next.day_num = 1 -- chỉ lấy ngày đầu tháng
/* LEFT JOIN với bảng fact: tìm doanh thu của cùng khách ở tháng liền sau.
Nếu không tìm thấy (khách vắng mặt), end_mrr = 0 (khách đã rời đi). */
LEFT JOIN olap.fact_customer_mrr f
ON f.customer_key = b.customer_key -- cùng khách hàng
AND f.date_key IN ( SELECT d3.date_key -- tìm ngày đầu tháng M
FROM olap.dim_date d3
WHERE d3.month_index = b.start_index + 1 )
/* Chỉ giữ lại tháng gốc nếu tháng liền sau có ít nhất một dòng dữ liệu
trong bảng fact (đảm bảo tháng M là kỳ phân tích hợp lệ). */
WHERE EXISTS ( SELECT 1
FROM olap.fact_customer_mrr fx
JOIN olap.dim_date dx ON dx.date_key = fx.date_key
WHERE dx.month_index = b.start_index + 1 )
),
/* ===========================================================================
BƯỚC 3 — PHÂN LOẠI SỰ THAY ĐỔI CỦA TỪNG KHÁCH HÀNG
===========================================================================
Mục đích:
- So sánh doanh thu đầu kỳ (start_mrr) và doanh thu cuối kỳ (end_mrr) của
từng khách hàng để xác định khách hàng đó thuộc loại thay đổi nào.
- Phân thành các luồng sau:
┌─────────────────────┬─────────────────────────────────────────────┐
│ Luồng │ Điều kiện │
├─────────────────────┼─────────────────────────────────────────────┤
│ Giữ nguyên │ end_mrr > 0 VÀ end_mrr = start_mrr │
│ Nâng cấp (expansion) │ end_mrr > start_mrr (khách trả tiền nhiều hơn)│
│ Hạ gói (contraction) │ 0 < end_mrr < start_mrr (vẫn trả nhưng ít hơn)│
│ Rời đi (churn) │ end_mrr = 0 (ngừng trả tiền hoàn toàn) │
└─────────────────────┴─────────────────────────────────────────────┘
Giải thích từng cột đầu ra:
- retained_logo: ghi nhận 1 nếu khách hàng vẫn còn trả tiền ở tháng M
(end_mrr > 0), ghi nhận 0 nếu khách hàng đã rời đi
(end_mrr = 0). Cột này đếm theo số lượng khách hàng,
không xét đến số tiền.
- churn_dollar: ghi nhận toàn bộ doanh thu của khách hàng nếu khách đó
rời đi (end_mrr = 0). Ghi nhận 0 nếu khách vẫn còn trả tiền.
Ví dụ: khách trả $25/tháng, rời đi → churn_dollar = $25.
- contraction_dollar: ghi nhận phần doanh thu GIẢM của khách hàng vẫn
còn hoạt động nhưng chuyển sang gói rẻ hơn.
Tính bằng start_mrr − end_mrr, nhưng chỉ khi số đó
dương (end_mrr < start_mrr) và khách vẫn còn hoạt
động (end_mrr > 0). Nếu khách rời đi (end_mrr = 0),
contraction_dollar = 0 (phần mất đã tính vào
churn_dollar rồi, không tính hai lần).
- expansion_dollar: ghi nhận phần doanh thu TĂNG của khách hàng nâng cấp
lên gói đắt hơn. Tính bằng end_mrr − start_mrr, nhưng
chỉ khi số đó dương (end_mrr > start_mrr).
Hàm GREATEST(a, b): trả về giá trị lớn hơn giữa a và b.
- GREATEST(start_mrr − end_mrr, 0): nếu start_mrr > end_mrr (giảm), trả về
phần giảm; nếu start_mrr ≤ end_mrr (tăng hoặc giữ nguyên), trả về 0.
- GREATEST(end_mrr − start_mrr, 0): nếu end_mrr > start_mrr (tăng), trả về
phần tăng; nếu end_mrr ≤ start_mrr (giảm hoặc giữ nguyên), trả về 0.
Lưu ý: Hàm GREATEST/LEAST có sẵn trong SQL Server 2022 trở lên.
=========================================================================== */
classified AS (
SELECT end_index, -- số thứ tự tháng M (tháng đang phân tích)
start_mrr, -- doanh thu đầu kỳ của khách
end_mrr, -- doanh thu cuối kỳ của khách
/* retained_logo: 1 = khách vẫn còn trả tiền, 0 = khách đã rời đi */
CASE WHEN end_mrr > 0 THEN 1 ELSE 0 END AS retained_logo,
/* churn_dollar: toàn bộ doanh thu mất khi khách rời đi (end_mrr = 0) */
CASE WHEN end_mrr = 0 THEN start_mrr ELSE 0 END AS churn_dollar,
/* contraction_dollar: phần doanh thu giảm khi khách hạ gói (vẫn còn
trả tiền nhưng ít hơn). Dùng GREATEST để chỉ ghi nhận khi thực sự
giảm, và chỉ khi khách vẫn còn hoạt động (end_mrr > 0). */
CASE WHEN end_mrr > 0
THEN GREATEST(start_mrr - end_mrr, 0) ELSE 0 END AS contraction_dollar,
/* expansion_dollar: phần doanh thu tăng khi khách nâng cấp
(end_mrr > start_mrr). Dùng GREATEST để chỉ ghi nhận khi thực sự
tăng, trả về 0 khi giảm hoặc giữ nguyên. */
GREATEST(end_mrr - start_mrr, 0) AS expansion_dollar
FROM paired -- dữ liệu đã nối tháng gốc với tháng liền sau
),
/* ===========================================================================
BƯỚC 4 — TỔNG HỢP THEO THÁNG: CỘNG THEO SỐ LƯỢNG KHÁCH VÀ THEO GIÁ TRỊ TIỀN
===========================================================================
Mục đích:
- Gom tất cả khách hàng theo tháng phân tích (end_index).
- Cộng các con số để ra tổng theo tháng.
Giải thích từng cột đầu ra:
- customers_start: đếm tổng số khách hàng trong nhóm gốc. Đây là mẫu số
của Logo Retention. Ví dụ: 8 khách hàng trả tiền ở tháng
M-1 → customers_start = 8.
- customers_retained: đếm số khách hàng vẫn còn trả tiền ở tháng M
(tổng cột retained_logo từ Bước 3). Ví dụ: trong 8
khách gốc, 5 khách vẫn trả tiền ở tháng M →
customers_retained = 5.
- mrr_start: tổng doanh thu đầu kỳ của toàn nhóm gốc. Đây là mẫu số của
GDR và NDR. Ví dụ: 8 khách hàng trả tổng cộng $220/tháng →
mrr_start = $220.
- churn_d: tổng doanh thu mất do khách hàng rời đi (tổng cột churn_dollar).
Ví dụ: 3 khách rời đi, mỗi người trả $25 → churn_d = $75.
- contraction_d: tổng doanh thu giảm do khách hàng hạ gói (tổng cột
contraction_dollar). Ví dụ: 1 khách từ $50 xuống $25 →
contraction_d = $25.
- expansion_d: tổng doanh thu tăng do khách hàng nâng cấp (tổng cột
expansion_dollar). Ví dụ: 1 khách từ $25 lên $75 →
expansion_d = $50.
Sau bước này, mỗi tháng có đúng một dòng chứa tất cả các tổng cần thiết
để tính 7 cột kết quả.
=========================================================================== */
agg AS (
SELECT end_index, -- số thứ tự tháng M
COUNT(*) AS customers_start, -- (2) tổng số khách gốc = mẫu số Logo Retention
SUM(retained_logo) AS customers_retained, -- số khách vẫn còn trả tiền ở tháng M
SUM(start_mrr) AS mrr_start, -- tổng doanh thu đầu kỳ = mẫu số GDR và NDR
SUM(churn_dollar) AS churn_d, -- tổng doanh thu mất do khách rời đi
SUM(contraction_dollar) AS contraction_d, -- tổng doanh thu giảm do khách hạ gói
SUM(expansion_dollar) AS expansion_d -- tổng doanh thu tăng do khách nâng cấp
FROM classified -- dữ liệu đã phân loại từng khách
GROUP BY end_index -- gom theo tháng phân tích
)
/* ===========================================================================
BƯỚC 5 — KẾT XUẤT 7 CỘT CUỐI CÙNG
===========================================================================
Mối quan hệ cốt lõi giữa các chỉ số:
NDR = GDR + phần trăm doanh thu tăng do nâng cấp (expansion%)
Trong đó:
- GDR (cột 4): tính phần doanh thu MẤT (rời đi + hạ gói), KHÔNG cộng phần
doanh thu TĂNG (nâng cấp). Vì không cộng phần tăng, GDR luôn
không vượt quá 100%. GDR trả lời câu hỏi: "Nếu không có khách
hàng nào nâng cấp, nhóm khách hàng cũ giữ được bao nhiêu phần
trăm doanh thu?" — đây là thước đo sức bền thuần.
- NDR (cột 6): tính phần doanh thu giữ được CỘNG thêm phần doanh thu tăng
do nâng cấp. Vì có cộng phần tăng, NDR có thể vượt 100%.
NDR trả lời câu hỏi: "Tổng doanh thu từ nhóm khách hàng cũ
thay đổi thế nào sau một tháng?"
- dollar_quick_ratio (cột 5): tỷ lệ giữa doanh thu tăng do nâng cấp và
doanh thu mất (rời đi + hạ gói). Trả lời câu hỏi: "Mỗi $1
doanh thu mất đi được bù bằng bao nhiêu $ doanh thu tăng
thêm?" Lớn hơn 1 = phần tăng do nâng cấp thắng phần mất.
Không có giá trị (NULL) = tháng đó không mất đồng nào.
- retention_gap_pp (cột 7): NDR% trừ đi Logo Retention%. Khoảng cách dương
lớn = bằng chứng định lượng cho tình trạng "mất nhiều khách
hàng về số lượng nhưng doanh thu vẫn tăng".
Giải thích chi tiết từng cột trong câu lệnh SELECT bên dưới.
=========================================================================== */
SELECT
/* CỘT 1 — analysis_month: nhãn tháng đang phân tích (ví dụ: "2025-02").
Lấy từ bảng dim_date, nối qua end_index và ngày đầu tháng. */
dm.month_label AS analysis_month,
/* CỘT 2 — customers_start: số lượng khách hàng trong nhóm gốc.
Đây là mẫu số của Logo Retention. */
a.customers_start AS customers_start,
/* CỘT 3 — logo_retention_pct: tỷ lệ phần trăm khách hàng trong nhóm gốc
vẫn còn trả tiền ở tháng M.
Công thức: (số khách còn lại / tổng số khách gốc) × 100
Góc nhìn theo SỐ LƯỢNG khách hàng → phát hiện RÒ RỈ.
Ví dụ: 5/8 × 100 = 62.5% → mất 37.5% khách hàng. */
CAST(100.0 * a.customers_retained / a.customers_start AS DECIMAL(6,1))AS logo_retention_pct,
/* CỘT 4 — gross_dollar_retention_pct (GDR): tỷ lệ doanh thu giữ được
nếu KHÔNG tính phần tăng do nâng cấp.
Công thức: (doanh thu đầu kỳ − doanh thu mất do rời đi − doanh thu
giảm do hạ gói) / doanh thu đầu kỳ × 100
GDR luôn ≤ 100% vì không cộng phần tăng.
GDR thấp = mất nhiều doanh thu → nền tảng yếu.
GDR cao = mất ít doanh thu → nền tảng vững. */
CAST(100.0 * (a.mrr_start - a.churn_d - a.contraction_d)
/ a.mrr_start AS DECIMAL(6,1)) AS gross_dollar_retention_pct,
/* CỘT 5 — dollar_quick_ratio: tỷ lệ giữa doanh thu tăng do nâng cấp và
doanh thu mất (rời đi + hạ gói).
Công thức: doanh thu tăng / (doanh thu mất do rời đi + doanh thu
giảm do hạ gói)
Hàm NULLIF(..., 0) trả về NULL nếu mẫu số = 0 (tháng đó không mất đồng
nào), tránh lỗi chia cho 0.
> 1 = phần tăng do nâng cấp thắng phần mất.
< 1 = phần tăng không bù đủ phần mất.
NULL = tháng đó không mất doanh thu nào. */
CAST(a.expansion_d / NULLIF(a.churn_d + a.contraction_d, 0)
AS DECIMAL(6,2)) AS dollar_quick_ratio,
/* CỘT 6 — net_dollar_retention_pct (NDR): tỷ lệ doanh thu từ nhóm khách
hàng gốc ở tháng M so với tháng M-1, bao gồm cả phần tăng do nâng cấp.
Công thức: (doanh thu đầu kỳ − doanh thu mất − doanh thu giảm +
doanh thu tăng) / doanh thu đầu kỳ × 100
Tương đương: GDR + expansion%
NDR > 100% = doanh thu từ khách hàng cũ tăng (nhờ nâng cấp vượt phần mất).
NDR < 100% = doanh thu từ khách hàng cũ giảm.
Góc nhìn theo GIÁ TRỊ DOANH THU → phát hiện BỀN VỮNG. */
CAST(100.0 * (a.mrr_start - a.churn_d - a.contraction_d + a.expansion_d)
/ a.mrr_start AS DECIMAL(6,1)) AS net_dollar_retention_pct,
/* CỘT 7 — retention_gap_pp: khoảng cách giữa NDR và Logo Retention,
tính bằng điểm phần trăm.
Công thức: NDR% − Logo Retention%
Gap dương lớn = NDR cao nhưng Logo Retention thấp = tình trạng
"mất nhiều khách hàng về số lượng nhưng doanh thu vẫn tăng".
Gap thấp = hai góc nhìn đồng thuận.
Đây là kết quả chính cần đo. */
CAST( 100.0 * (a.mrr_start - a.churn_d - a.contraction_d + a.expansion_d) / a.mrr_start
- 100.0 * a.customers_retained / a.customers_start
AS DECIMAL(6,1)) AS retention_gap_pp
FROM agg a -- bảng tổng hợp từ Bước 4
/* Nối với bảng thời gian để lấy nhãn tháng (month_label) cho cột đầu ra.
Điều kiện day_num = 1 giới hạn chỉ lấy ngày đầu tháng. */
JOIN olap.dim_date dm ON dm.month_index = a.end_index AND dm.day_num = 1
ORDER BY dm.month_label; -- sắp xếp kết quả theo tháng tăng dần
GO
Leave a Comment