Mở đầu — vì sao bài này quan trọng
Hãy tưởng tượng bạn là BA tại một sàn thương mại điện tử. Sếp marketing chạy vào hỏi: "Mình đang bỏ ra 80.000 đồng để có một khách hàng mới qua quảng cáo Facebook. Như vậy là lời hay lỗ?". Bạn không thể trả lời câu này nếu chỉ nhìn vào doanh thu một đơn hàng. Một khách có thể mua lần đầu chỉ 120.000 đồng, nhưng trong hai năm tiếp theo họ quay lại mua thêm 15 lần nữa. Câu hỏi thật sự là: một khách hàng mang về tổng cộng bao nhiêu tiền trong suốt vòng đời quan hệ với doanh nghiệp?
Đó chính là Customer Lifetime Value (LTV, đôi khi viết là CLV hoặc CLTV — Giá trị vòng đời khách hàng). Đây là một trong những chỉ số nền tảng nhất mà mọi BA cần biết tính bằng SQL, vì nó là mẫu số chung kết nối ba phòng ban: marketing (được phép chi bao nhiêu để thu hút khách), tài chính (định giá doanh nghiệp), và sản phẩm (nên đầu tư giữ chân nhóm khách nào).
Trong bài này, bạn sẽ học cách dựng các truy vấn LTV từ dữ liệu giao dịch thô — từ phiên bản đơn giản nhất (historical LTV, giá trị đã thực sự xảy ra) đến phiên bản dự báo cơ bản. Đây là kỹ năng tiếp nối tự nhiên sau RFM analysis (Bài 31) và sẽ là nền cho churn detection (Bài 33). Chúng ta tập trung đúng vào LTV, không lấn sang phân loại RFM hay phát hiện churn.
Khái niệm cốt lõi
LTV là gì?
LTV là tổng giá trị (doanh thu hoặc lợi nhuận) mà một khách hàng mang lại trong toàn bộ vòng đời quan hệ với doanh nghiệp. Có hai cách nhìn quan trọng mà BA hay nhầm lẫn:
- Historical LTV: tổng những gì khách đã thực sự chi đến thời điểm hiện tại. Đây là số liệu đã xảy ra, tính từ dữ liệu giao dịch có thật. Rất chắc chắn, dễ kiểm chứng.
- Predicted LTV: ước lượng tổng giá trị khách sẽ mang lại trong tương lai, kể cả những đơn hàng chưa xảy ra. Đây là một mô hình dự báo, có giả định và sai số.
Doanh thu hay lợi nhuận?
Đây là quyết định đầu tiên và quan trọng nhất. LTV theo doanh thu (revenue) dễ tính nhưng dễ gây hiểu lầm: nếu biên lợi nhuận của bạn chỉ 15%, một khách mang về 10 triệu doanh thu thực ra chỉ đóng góp 1,5 triệu lợi nhuận gộp. Khi so với chi phí thu hút khách (CAC), bạn phải dùng LTV theo lợi nhuận, nếu không sẽ ra kết luận sai và đốt tiền marketing oan.
Quy ước: LTV gross margin = Doanh thu × Biên lợi nhuận gộp.
Công thức LTV dự báo cơ bản
Công thức kinh điển mà BA cần thuộc:
LTV = AOV × Tần suất mua/năm × Tuổi thọ khách (năm) × Biên lợi nhuận gộp
Trong đó:
- AOV (Average Order Value): giá trị đơn hàng trung bình = tổng doanh thu / tổng số đơn.
- Tần suất mua/năm: trung bình mỗi khách mua bao nhiêu lần một năm.
- Tuổi thọ khách: số năm trung bình một khách còn gắn bó. Nếu tỷ lệ churn hàng năm là c, thì tuổi thọ ≈ 1 / c.
- Biên lợi nhuận gộp: phần trăm còn lại sau giá vốn.
Mô hình dữ liệu giả định
Suốt bài này ta dùng một bảng orders đơn giản, kiểu schema bạn sẽ gặp ở bất kỳ sàn TMĐT Việt Nam nào:
-- orders(order_id, customer_id, order_date, revenue, status)
-- status: 'completed', 'cancelled', 'refunded'
Tình huống thực tế
Tình huống 1 — Tiki và bài toán "khách này lời hay lỗ"
Một sàn TMĐT giả định kiểu Tiki muốn biết historical LTV theo lợi nhuận của từng khách, để đội CRM ưu tiên chăm sóc. Biên lợi nhuận gộp trung bình của ngành bán lẻ online ở đây giả định là 18%.
SELECT
customer_id,
COUNT(order_id) AS so_don,
SUM(revenue) AS tong_doanh_thu,
ROUND(SUM(revenue) * 0.18, 0) AS historical_ltv_loi_nhuan,
MIN(order_date) AS ngay_mua_dau,
MAX(order_date) AS ngay_mua_gan_nhat
FROM orders
WHERE status = 'completed' -- chỉ tính đơn hoàn tất, loại hủy/hoàn
GROUP BY customer_id
ORDER BY historical_ltv_loi_nhuan DESC;
Diễn giải: Điều kiện WHERE status = 'completed' là chi tiết sống còn. Một khách "đặt 50 đơn" nhưng hủy 40 đơn thì LTV thật rất khác con số gộp. Nếu quên lọc, bạn sẽ thổi phồng LTV và đội marketing sẽ chi quá tay.
Kết quả mẫu cho thấy top 5% khách đóng góp historical LTV trên 3 triệu đồng/người, trong khi nhóm đáy chỉ vài chục nghìn. Đội CRM nhìn vào đây quyết định: nhóm top được tặng voucher sinh nhật và hỗ trợ riêng; nhóm đáy chỉ nhận email tự động.
Bài học: Historical LTV luôn phải lọc theo trạng thái đơn hợp lệ. Doanh thu gộp không phải LTV.
Tình huống 2 — Shopee tính LTV trung bình theo cohort tháng đăng ký
Một sàn giả định kiểu Shopee muốn so sánh: khách đăng ký tháng nào "có giá trị" hơn? Họ nhóm khách theo tháng có đơn đầu tiên (cohort) rồi tính LTV trung bình từng nhóm. Đây là cách trả lời câu "chất lượng khách quý 1 năm nay có tốt hơn quý 4 năm ngoái không?".
WITH first_order AS (
SELECT
customer_id,
DATE_FORMAT(MIN(order_date), '%Y-%m') AS cohort_thang
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
),
ltv_per_customer AS (
SELECT
customer_id,
SUM(revenue) AS ltv_doanh_thu
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
)
SELECT
f.cohort_thang,
COUNT(*) AS so_khach,
ROUND(AVG(l.ltv_doanh_thu), 0) AS ltv_tb_doanh_thu,
ROUND(AVG(l.ltv_doanh_thu) * 0.18, 0) AS ltv_tb_loi_nhuan
FROM first_order f
JOIN ltv_per_customer l ON f.customer_id = l.customer_id
GROUP BY f.cohort_thang
ORDER BY f.cohort_thang;
Diễn giải: Ta dùng CTE (đã học ở Bài 16) để tách hai bước rõ ràng: xác định cohort của mỗi khách, và tính LTV của mỗi khách. Sau đó JOIN lại và lấy trung bình theo cohort.
Một cảnh báo quan trọng về bias thời gian: cohort tháng 1 có 12 tháng để tích lũy đơn, còn cohort tháng 11 chỉ mới 2 tháng. Nên LTV cohort cũ luôn cao hơn — không phải vì khách tốt hơn, mà vì họ có nhiều thời gian hơn. Để so công bằng, BA giỏi sẽ chốt một mốc cố định, ví dụ "LTV trong 90 ngày đầu kể từ đơn đầu tiên" (xem Tình huống 3).
Bài học: Khi so sánh LTV giữa các cohort, luôn cẩn thận với thời gian quan sát không bằng nhau.
Tình huống 3 — Be (gọi xe) và predicted LTV để định ngân sách marketing
Một nền tảng gọi xe giả định kiểu Be muốn biết: với mỗi khách mới, được phép chi tối đa bao nhiêu để thu hút? Họ cần predicted LTV theo công thức, dùng dữ liệu thực để ước lượng từng tham số.
WITH metrics AS (
SELECT
COUNT(DISTINCT customer_id) AS so_khach,
COUNT(order_id) AS tong_don,
SUM(revenue) AS tong_doanh_thu,
-- AOV
SUM(revenue) / COUNT(order_id) AS aov,
-- Tần suất mua/năm = tổng đơn / số khách / số năm dữ liệu
COUNT(order_id) / COUNT(DISTINCT customer_id)
/ (DATEDIFF(MAX(order_date), MIN(order_date)) / 365.0) AS tan_suat_nam
FROM orders
WHERE status = 'completed'
)
SELECT
ROUND(aov, 0) AS aov,
ROUND(tan_suat_nam, 2) AS tan_suat_nam,
3.0 AS tuoi_tho_nam, -- giả định churn ~33%/năm
0.20 AS bien_loi_nhuan,
ROUND(aov tan_suat_nam 3.0 * 0.20, 0) AS predicted_ltv
FROM metrics;
Diễn giải: Giả sử kết quả cho AOV = 55.000đ, tần suất = 24 chuyến/năm, tuổi thọ 3 năm, biên 20%. Predicted LTV ≈ 55.000 × 24 × 3 × 0,20 = 792.000đ. Nghĩa là đội marketing có thể chi tới khoảng 790 nghìn để thu hút một khách mới mà vẫn hòa vốn về dài hạn — thực tế họ sẽ đặt trần thấp hơn (ví dụ 1/3 con số đó) để đảm bảo có lời.
Lưu ý tham số tuoi_tho_nam = 3 ở đây là giả định, suy ra từ churn ~33%/năm (1/0,33 ≈ 3). Trong thực tế bạn nên ước lượng churn từ dữ liệu thật. Đó là lý do Bài 33 về churn detection bổ trợ trực tiếp cho LTV.
Bài học: Predicted LTV biến công thức kinh tế thành truy vấn SQL. Mỗi tham số phải nói rõ "đây là số đo từ dữ liệu" hay "đây là giả định" — minh bạch điều này là dấu hiệu của một BA chuyên nghiệp.
Hướng dẫn từng bước
Khi nhận yêu cầu "tính LTV", hãy đi theo quy trình sau:
Bước 1 — Chốt định nghĩa với người yêu cầu. Hỏi ngay ba câu: (1) Doanh thu hay lợi nhuận? (2) Historical hay predicted? (3) Mốc thời gian nào (toàn bộ lịch sử, hay 90/180/365 ngày đầu)? Đừng viết một dòng SQL nào trước khi có ba câu trả lời này.
Bước 2 — Làm sạch dữ liệu giao dịch. Quyết định trạng thái đơn nào được tính. Thông thường: tính completed, loại cancelled. Với refunded, lý tưởng là trừ phần hoàn tiền ra khỏi doanh thu:
SELECT
customer_id,
SUM(CASE WHEN status = 'completed' THEN revenue
WHEN status = 'refunded' THEN -refund_amount
ELSE 0 END) AS net_revenue
FROM orders
GROUP BY customer_id;
Bước 3 — Tính historical LTV per customer. Đây là GROUP BY customer_id với SUM(revenue), rồi nhân biên lợi nhuận nếu cần (như Tình huống 1).
Bước 4 — Tính các chỉ số tổng hợp. AOV, tần suất mua, tuổi thọ — phục vụ predicted LTV hoặc báo cáo tổng quan (như Tình huống 3).
Bước 5 — Cắt lát theo phân khúc. LTV trung bình của cả tệp khách thường ít hữu ích. Hãy chia theo kênh thu hút, thành phố, cohort, hoặc loại sản phẩm để tìm ra "khách từ nguồn nào có LTV cao nhất". Ví dụ thêm GROUP BY acquisition_channel.
Bước 6 — So với CAC. Bước cuối luôn là đặt LTV cạnh chi phí thu hút. Tỷ lệ LTV:CAC khỏe mạnh thường được xem là từ 3:1 trở lên. Đây là con số sếp thật sự muốn nghe.
Lỗi thường gặp & mẹo
Lỗi 1 — Dùng doanh thu thay vì lợi nhuận khi so với CAC. Đây là sai lầm tốn kém nhất. Một khách "LTV 5 triệu doanh thu" với biên 15% chỉ đáng 750 nghìn lợi nhuận. Nếu CAC là 1 triệu, bạn đang lỗ dù con số 5 triệu trông rất đẹp. Luôn nhân biên lợi nhuận trước khi so với CAC.
Lỗi 2 — Quên lọc trạng thái đơn. SUM(revenue) trên toàn bảng gồm cả đơn hủy và hoàn sẽ thổi phồng LTV. Luôn có WHERE status = 'completed' hoặc xử lý CASE WHEN cho hoàn tiền (kỹ thuật CASE WHEN đã học ở Bài 23).
Lỗi 3 — So sánh LTV giữa các cohort có thời gian quan sát khác nhau. Cohort cũ luôn có LTV cao hơn đơn giản vì có nhiều thời gian tích lũy. Mẹo: cố định cửa sổ, ví dụ "LTV trong N ngày đầu". Thêm điều kiện order_date <= first_order_date + INTERVAL 90 DAY vào truy vấn.
Lỗi 4 — Để outlier kéo lệch số trung bình. Một khách doanh nghiệp mua sỉ có thể có LTV gấp 1.000 lần khách lẻ, làm AVG vô nghĩa. Mẹo: báo cáo cả median bên cạnh mean, và cân nhắc tách nhóm B2B ra khỏi B2C.
Lỗi 5 — Lẫn lộn historical và predicted. Đừng trình bày một con số dự báo như thể nó là sự thật đã xảy ra. Trong báo cáo, luôn ghi rõ nhãn "đã ghi nhận" hay "dự báo" và liệt kê các giả định.
Mẹo — Dùng CTE để truy vấn dễ đọc và dễ kiểm tra. Như các tình huống trên, tách từng bước (cohort, LTV per customer, tổng hợp) thành CTE riêng giúp bạn debug từng phần và giúp người khác đọc được logic của bạn.
Bài tập thực hành
Dùng bảng orders(order_id, customer_id, order_date, revenue, status) với refund_amount cho đơn hoàn. Biên lợi nhuận gộp giả định 20%.
- Cơ bản: Viết truy vấn tính historical LTV theo doanh thu cho từng
customer_id, chỉ tính đơncompleted, sắp xếp giảm dần. Hiển thị thêm số đơn và ngày mua gần nhất.
- Trung bình: Tính historical LTV theo lợi nhuận (doanh thu × 20%), đồng thời xử lý đúng đơn
refundedbằng cách trừrefund_amountra khỏi doanh thu trước khi nhân biên.
- Cohort: Nhóm khách theo tháng có đơn đầu tiên và tính LTV doanh thu trung bình của mỗi cohort. Giải thích bằng một câu vì sao cohort cũ có LTV cao hơn.
- Predicted LTV: Tính AOV và tần suất mua/năm của toàn tệp khách từ dữ liệu thật, rồi áp công thức
LTV = AOV × tần suất × 2,5 năm × 0,20để ra predicted LTV một khách trung bình.
- Nâng cao (thách thức): Tính "LTV trong 90 ngày đầu" cho mỗi khách — chỉ cộng doanh thu của các đơn nằm trong vòng 90 ngày kể từ đơn đầu tiên của chính khách đó. Gợi ý: dùng CTE lấy
MIN(order_date)per customer, JOIN lại bảngorders, lọc theo khoảng ngày.
Tóm tắt
- LTV là tổng giá trị một khách mang lại trong vòng đời quan hệ. Phân biệt rõ historical (đã xảy ra, chắc chắn) và predicted (dự báo, có giả định).
- Luôn quyết định doanh thu hay lợi nhuận trước tiên. Khi so với chi phí thu hút khách (CAC), bắt buộc dùng LTV theo lợi nhuận.
- Historical LTV trong SQL là bài toán
GROUP BY customer_id+SUM(revenue), với điều kiện lọc trạng thái đơn đúng đắn. - Predicted LTV dùng công thức
AOV × tần suất × tuổi thọ × biên lợi nhuận, mỗi tham số ước lượng từ dữ liệu hoặc nêu rõ là giả định. - Cẩn thận với ba cái bẫy: bias thời gian giữa các cohort, outlier kéo lệch trung bình, và lẫn lộn doanh thu với lợi nhuận.
- Đích đến cuối cùng của mọi báo cáo LTV là tỷ lệ LTV:CAC — con số quyết định doanh nghiệp được phép chi bao nhiêu để tăng trưởng bền vững.