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 đồng thời năm kênh: Facebook Ads, Google Ads, email, SEO (organic) và affiliate. Cuối tháng, mỗi kênh đều "vỗ ngực" báo cáo rằng họ mang về doanh số. Cộng tất cả lại, tổng doanh số "được quy công" gấp ba lần doanh số thật. Sếp quay sang hỏi bạn: "Vậy rốt cuộc tiền nên đổ vào kênh nào?"
Đây chính là bài toán attribution — quy công chuyển đổi. Một khách hàng hiếm khi mua ngay lần đầu thấy quảng cáo. Họ đi qua một hành trình: thấy Facebook → search Google → đọc email → quay lại qua organic → gõ thẳng địa chỉ web (direct) → mua. Năm điểm chạm (touchpoint), nhưng chỉ một đơn hàng. Câu hỏi sống còn: kênh nào thực sự "gây ra" đơn hàng đó? Nếu bạn quy hết công cho kênh cuối (direct), bạn sẽ cắt ngân sách Facebook — kênh đã khơi mào toàn bộ hành trình. Cắt sai chỗ, doanh số tháng sau sụt.
Là BA, bạn không quyết định ngân sách, nhưng bạn là người cung cấp con số để người khác quyết định. Nếu mô hình attribution của bạn sai, mọi quyết định phía sau đều lệch. Bài này dạy bạn dùng SQL để dựng các mô hình first-touch, last-touch và multi-touch — biến một mớ log sự kiện hỗn độn thành con số quy công minh bạch, có thể bảo vệ trước hội đồng.
Khái niệm cốt lõi
Touchpoint và hành trình khách hàng
Mọi mô hình attribution đều bắt đầu từ một bảng sự kiện (event log), thường gọi là bảng touchpoints hoặc sessions. Mỗi dòng là một lần khách tương tác với một kênh marketing, kèm thời điểm. Khi khách mua, ta có một dòng conversion. Nhiệm vụ của attribution là nối các touchpoint trước đó với conversion, rồi phân bổ "tín dụng" (credit) cho từng kênh.
Một schema tối giản:
-- touchpoints: mỗi lần khách chạm một kênh
-- user_id | channel | touch_time
-- 1001 | facebook | 2026-06-01 09:00
-- 1001 | google | 2026-06-03 14:00
-- 1001 | email | 2026-06-05 08:00
-- 1001 | direct | 2026-06-06 20:00-- conversions: đơn hàng
-- user_id | order_id | revenue | convert_time
-- 1001 | A789 | 1200000 | 2026-06-06 20:05
Ba họ mô hình attribution
First-touch (chạm đầu): quy 100% công cho kênh khách gặp đầu tiên. Triết lý: kênh nào kéo người lạ vào phễu xứng đáng nhận công. Mô hình này tôn vinh các kênh "khám phá" như Facebook, TikTok, display ads. Điểm yếu: bỏ qua hoàn toàn các kênh chốt đơn.
Last-touch (chạm cuối): quy 100% công cho kênh ngay trước khi mua. Đây là mặc định của hầu hết công cụ (kể cả Google Analytics phiên bản cũ). Triết lý: kênh nào "đẩy" khách qua vạch đích là kênh quan trọng nhất. Điểm yếu: thường thổi phồng direct và branded search — những kênh khách tự tìm đến khi đã quyết mua, chứ không thực sự thuyết phục họ.
Multi-touch (đa điểm chạm): chia công cho nhiều touchpoint trong hành trình. Có vài biến thể:
- Linear (tuyến tính): chia đều công cho mọi touchpoint. Năm điểm chạm thì mỗi điểm 20%.
- Time-decay (suy giảm theo thời gian): touchpoint càng gần lúc mua càng nhiều công, càng xa càng ít.
- Position-based / U-shaped: chạm đầu 40%, chạm cuối 40%, các chạm giữa chia nhau 20% còn lại. Tôn vinh cả kênh khám phá lẫn kênh chốt.
Cửa sổ quy công (attribution window)
Một chi tiết hay bị quên: không phải mọi touchpoint trong quá khứ đều được tính. Ta thường giới hạn cửa sổ — ví dụ 30 ngày trước khi mua. Một lần khách thấy Facebook cách đây 8 tháng không nên được quy công cho đơn hàng hôm nay. Cửa sổ này là tham số nghiệp vụ, cần thống nhất với team marketing trước khi viết query.
Tình huống thực tế
Ví dụ 1 — Sàn TMĐT và cuộc chiến "ai mang đơn về"
Một sàn TMĐT tại TP.HCM (gọi là ShopFast) chạy báo cáo last-touch và thấy: kênh direct chiếm 45% doanh số, Facebook chỉ 8%. Team đề xuất cắt 60% ngân sách Facebook. May mắn, BA chặn lại và dựng thêm báo cáo first-touch.
Kết quả first-touch lật ngược tình thế: Facebook khơi mào tới 38% hành trình, direct chỉ 5%. Lý do rõ ràng — khách thấy sản phẩm trên Facebook, vài ngày sau gõ thẳng "shopfast.vn" để mua, nên direct "ăn" công ở phút chót. Nếu cắt Facebook, dòng người lạ vào phễu sẽ cạn, và 2-3 tháng sau cả direct lẫn doanh số đều tụt.
BA trình bày cả ba mô hình (first, last, linear) cạnh nhau. Mô hình linear cho thấy Facebook xứng đáng ~22% công bằng — đủ để giữ ngân sách. Bài học: chỉ nhìn last-touch là cách nhanh nhất để bóp chết kênh đầu phễu. Luôn đặt ít nhất hai mô hình cạnh nhau.
Ví dụ 2 — Ứng dụng gọi xe và email "ăn theo"
Một app gọi xe khu vực Đông Nam Á gửi email khuyến mãi mỗi sáng. Báo cáo last-touch khoe email mang về 30% chuyến đặt. Đội email xin tăng tần suất gửi.
BA nghi ngờ và dựng query time-decay kèm kiểm tra khoảng cách thời gian giữa touchpoint email và lúc đặt xe. Phát hiện: 70% trường hợp khách mở email rồi đặt xe trong vòng 2 phút — nhưng phân tích sâu cho thấy đa số những khách này vốn đã là khách trung thành, mở app hằng ngày. Email chỉ tình cờ là touchpoint cuối, không thực sự "tạo" nhu cầu. Khi BA loại nhóm khách hoạt động hằng ngày và chỉ tính khách không mở app 7 ngày, công thực của email rớt xuống 11%. Bài học: last-touch dễ bị "ăn theo". Một touchpoint xuất hiện ngay trước conversion chưa chắc là nguyên nhân — cần kiểm tra ngữ cảnh, không chỉ thứ tự.
Ví dụ 3 — Fintech ví điện tử và hành trình dài
Một ví điện tử tại Việt Nam (giả định MoPay) có chu kỳ chuyển đổi dài: từ lúc khách thấy quảng cáo đến lúc thực hiện giao dịch đầu tiên trung bình 18 ngày, qua 6-7 touchpoint. Mô hình first và last đều quá thô.
BA dùng position-based (U-shaped): 40% cho chạm đầu (thường là TikTok/Facebook khám phá), 40% cho chạm cuối (thường là push notification hoặc Google branded search), 20% chia đều cho các chạm giữa (retargeting, email nuôi dưỡng). Mô hình này phản ánh đúng vai trò: kênh khơi mào và kênh chốt đều quan trọng, kênh nuôi dưỡng ở giữa có vai trò nhưng nhỏ hơn. Nhờ đó team phân bổ lại ngân sách hợp lý giữa "kéo khách mới" và "chốt đơn". Bài học: với hành trình dài nhiều điểm chạm, multi-touch position-based thường cho bức tranh cân bằng nhất.
Hướng dẫn từng bước
Ta sẽ dựng cả ba mô hình trên cùng bộ dữ liệu. Giả sử có hai bảng: touchpoints(user_id, channel, touch_time) và conversions(user_id, order_id, revenue, convert_time).
Bước 1 — Ghép touchpoint vào từng conversion trong cửa sổ quy công. Mỗi touchpoint phải xảy ra trước lúc mua và trong vòng 30 ngày:
WITH journey AS (
SELECT
c.order_id,
c.revenue,
c.convert_time,
t.channel,
t.touch_time
FROM conversions c
JOIN touchpoints t
ON t.user_id = c.user_id
AND t.touch_time <= c.convert_time
AND t.touch_time >= c.convert_time - INTERVAL '30 days'
)
SELECT * FROM journey;
Bước 2 — Đánh số thứ tự touchpoint trong mỗi hành trình. Dùng window function ROW_NUMBER() để biết đâu là chạm đầu, đâu là chạm cuối:
WITH journey AS ( / như bước 1 / ),
ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY touch_time) AS pos_asc,
ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY touch_time DESC) AS pos_desc,
COUNT(*) OVER (PARTITION BY order_id) AS total_touches
FROM journey
)
SELECT * FROM ranked;
pos_asc = 1 là first-touch, pos_desc = 1 là last-touch, total_touches là số điểm chạm để chia công cho linear.
Bước 3 — First-touch attribution. Chỉ giữ touchpoint đầu, gán toàn bộ doanh thu cho kênh đó:
SELECT channel, SUM(revenue) AS first_touch_revenue, COUNT(*) AS orders
FROM ranked
WHERE pos_asc = 1
GROUP BY channel
ORDER BY first_touch_revenue DESC;
Bước 4 — Last-touch attribution. Tương tự nhưng lọc pos_desc = 1:
SELECT channel, SUM(revenue) AS last_touch_revenue, COUNT(*) AS orders
FROM ranked
WHERE pos_desc = 1
GROUP BY channel
ORDER BY last_touch_revenue DESC;
Bước 5 — Multi-touch linear. Mỗi touchpoint nhận revenue / total_touches. Lưu ý ép kiểu để tránh chia số nguyên:
SELECT
channel,
SUM(revenue * 1.0 / total_touches) AS linear_revenue
FROM ranked
GROUP BY channel
ORDER BY linear_revenue DESC;
Bước 6 — Multi-touch position-based (U-shaped). Gán trọng số: chạm đầu 0.4, chạm cuối 0.4, phần còn lại chia đều 0.2. Xử lý riêng trường hợp hành trình chỉ có 1 hoặc 2 điểm chạm bằng CASE:
SELECT
channel,
SUM(revenue * weight) AS u_shaped_revenue
FROM (
SELECT channel, revenue,
CASE
WHEN total_touches = 1 THEN 1.0
WHEN total_touches = 2 AND pos_asc IN (1,2) THEN 0.5
WHEN pos_asc = 1 THEN 0.4
WHEN pos_desc = 1 THEN 0.4
ELSE 0.2 / (total_touches - 2)
END AS weight
FROM ranked
) w
GROUP BY channel
ORDER BY u_shaped_revenue DESC;
Bước 7 — So sánh các mô hình cạnh nhau. Đây là sản phẩm cuối BA giao cho sếp. Ghép kết quả thành một bảng để dễ đối chiếu:
SELECT
channel,
SUM(CASE WHEN pos_asc = 1 THEN revenue ELSE 0 END) AS first_touch,
SUM(CASE WHEN pos_desc = 1 THEN revenue ELSE 0 END) AS last_touch,
SUM(revenue * 1.0 / total_touches) AS linear
FROM ranked
GROUP BY channel
ORDER BY linear DESC;
Bảng này cho phép người đọc thấy ngay kênh nào "ăn" công ở last-touch nhưng yếu ở first-touch (dấu hiệu kênh chốt đơn ăn theo), và ngược lại.
Lỗi thường gặp & mẹo
Quên cửa sổ quy công. Nếu không giới hạn touch_time >= convert_time - INTERVAL '30 days', bạn sẽ quy công cho cả những lần chạm từ nửa năm trước. Con số phình to vô nghĩa. Luôn chốt cửa sổ với team trước khi chạy.
Đếm trùng khi khách mua nhiều lần. Nếu một khách có 3 đơn hàng, mỗi đơn cần một hành trình riêng. Đừng JOIN touchpoint với toàn bộ đơn rồi gộp — bạn sẽ nhân bản touchpoint. PARTITION theo order_id, không phải user_id, là chìa khóa tránh lỗi này.
Chia số nguyên cắt cụt. Trong nhiều CSDL, revenue / total_touches với cả hai là số nguyên sẽ làm tròn xuống (ví dụ 1200000 / 7 = 171428 và tổng lại không bằng 1200000). Luôn nhân 1.0 hoặc CAST sang DECIMAL để giữ phần lẻ. Sau khi tính, hãy kiểm tra: tổng doanh thu quy công của mỗi mô hình phải xấp xỉ bằng tổng doanh thu thật.
Tin mù một mô hình. Đây là lỗi tư duy, không phải lỗi SQL. Đừng bao giờ giao chỉ một con số last-touch. Luôn trình bày ít nhất hai mô hình và giải thích chúng khác nhau ở đâu.
Mẹo kiểm chứng: sau mỗi mô hình, chạy SELECT SUM(...) toàn bộ và so với SELECT SUM(revenue) FROM conversions. First-touch, last-touch, linear và position-based — cả bốn đều phải cho cùng một tổng (vì ta chỉ phân bổ lại 100% doanh thu, không tạo thêm). Nếu lệch, gần như chắc chắn bạn bị nhân bản dòng hoặc cắt cụt số.
Mẹo trình bày: thêm cột "chênh lệch first vs last" (first_touch - last_touch). Kênh dương lớn là kênh đầu phễu bị last-touch đánh giá thấp; kênh âm lớn là kênh chốt đơn dễ bị thổi phồng. Cột này nói lên nhiều điều hơn cả bảng số thô.
Bài tập thực hành
Với hai bảng touchpoints(user_id, channel, touch_time) và conversions(user_id, order_id, revenue, convert_time):
- Viết query first-touch attribution với cửa sổ quy công 14 ngày, trả về doanh thu quy công theo từng kênh, sắp xếp giảm dần.
- Dựng bảng so sánh first-touch, last-touch và linear cạnh nhau (mỗi kênh một dòng, ba cột số). Thêm cột thứ tư là chênh lệch
first_touch - last_touch. - Triển khai mô hình time-decay: touchpoint càng gần
convert_timecàng nhiều trọng số. Gợi ý — tính số ngày cách lúc mua, gán trọng sốPOWER(0.5, days_before / 7.0)(cứ mỗi 7 ngày trọng số giảm một nửa), rồi chuẩn hóa để tổng trọng số mỗi hành trình bằng 1 trước khi nhân với revenue. - Kiểm chứng: chứng minh tổng doanh thu quy công của cả ba mô hình ở câu 2 bằng nhau và bằng
SUM(revenue)của bảngconversions. Nếu lệch, tìm và sửa nguyên nhân. - (Nâng cao) Tìm các hành trình chỉ có đúng một touchpoint. Với những hành trình này, mọi mô hình đều cho kết quả giống nhau — giải thích vì sao, và tính tỷ lệ phần trăm đơn hàng "một chạm" trên tổng đơn.
Tóm tắt
Attribution là bài toán quy công chuyển đổi cho các kênh marketing trong một hành trình nhiều điểm chạm. Ba họ mô hình cốt lõi: first-touch tôn vinh kênh khám phá đầu phễu, last-touch tôn vinh kênh chốt đơn (và dễ thổi phồng direct/branded search), multi-touch (linear, time-decay, position-based) chia công cho nhiều điểm chạm để có bức tranh cân bằng.
Về kỹ thuật SQL, công thức chung là: JOIN touchpoint với conversion trong cửa sổ quy công, dùng ROW_NUMBER() để xác định chạm đầu/chạm cuối, rồi gán trọng số bằng CASE WHEN và tổng hợp bằng SUM. Ba cạm bẫy lớn nhất là quên cửa sổ quy công, nhân bản touchpoint khi khách mua nhiều lần, và chia số nguyên cắt cụt — luôn kiểm chứng bằng cách so tổng quy công với tổng doanh thu thật.
Quan trọng nhất, là BA bạn đừng bao giờ giao một con số duy nhất. Trình bày nhiều mô hình cạnh nhau, giải thích chúng khác nhau ra sao, để người ra quyết định thấy được toàn cảnh thay vì cắt nhầm kênh đang âm thầm nuôi cả phễu.