Khi mỗi báo cáo tự xử lý đơn hủy, mã khách và ngày giao dịch theo một cách riêng, cùng một cửa hàng có thể xuất hiện nhiều kết quả mua lại khác nhau. Người phân tích phải dành thời gian đối chiếu định nghĩa trước khi giải thích hành vi khách hàng.
Một bảng giao dịch chuẩn giúp tập trung các quy tắc đó vào một lớp dữ liệu dùng chung. Từ lớp này, bạn có thể xây dựng chỉ số mua lại, bảng cohort và báo cáo tần suất mua với cùng nền tảng giao dịch.
Bài viết sử dụng PostgreSQL để minh họa quy trình từ dữ liệu nguồn đến một View phân tích. Các bảng và số liệu đều là giả định phục vụ học tập.
Nguyên tắc thiết kế
Mỗi dòng đại diện cho một đơn hàng. Mỗi đơn có khóa duy nhất, khách hàng đã được nhận diện, thời gian thống nhất và kết quả đánh giá tính hợp lệ có thể kiểm tra lại.
1. Bảng giao dịch chuẩn là gì?
1.1. Một lớp dữ liệu dùng chung cho các phép tính
Bảng giao dịch chuẩn là tập dữ liệu có cấu trúc và quy tắc xử lý thống nhất, được dùng làm đầu vào cho các bài phân tích tiếp theo.
Trong bài viết này, Grain của lớp dữ liệu là một dòng cho mỗi đơn hàng. Chi tiết sản phẩm và sự kiện hoàn tiền vẫn có thể được lưu ở bảng riêng, sau đó tổng hợp hoặc liên kết khi cần.
Ví dụ, đơn O101 có ba sản phẩm vẫn đóng góp một đơn khi tính Purchase Frequency. Nếu cần tìm sản phẩm được mua lại, bạn sử dụng thêm bảng chi tiết đơn với Grain tương ứng.
1.2. Phân biệt dữ liệu chuẩn và dữ liệu phục vụ từng báo cáo
Có thể tổ chức thành hai lớp:
- Lớp giao dịch chuẩn: giữ một dòng mỗi đơn, thông tin định danh, thời gian và các cờ chất lượng.
- Lớp mua hàng hợp lệ: chỉ lấy đơn đáp ứng điều kiện phân tích, rồi bổ sung thứ tự mua và cohort.
Cách tổ chức này giúp theo dõi cả các đơn bị loại và những giao dịch thực sự đi vào chỉ số.
Dữ liệu phiên bản đơn + Ánh xạ khách hàng
|
v
Giao dịch chuẩn, một dòng mỗi đơn
|
v
Đơn mua hợp lệ
|
v
Thứ tự mua + Lần mua đầu + Cohort
|
+---------+----------+
| | |
v v v
Mua lại Retention Tần suất mua2. Chuẩn bị dữ liệu nguồn và quy tắc
2.1. Bảng lịch sử phiên bản đơn
Giả sử bảng raw_order_versions lưu các phiên bản đầy đủ của đơn hàng. Mỗi phiên bản chứa toàn bộ thông tin cần thiết của đơn tại lần cập nhật đó.
| Trường | Ý nghĩa | Kiểu dữ liệu minh họa |
|---|---|---|
source_system |
Hệ thống phát sinh đơn | text |
order_id |
Mã đơn trong hệ thống nguồn | text |
source_customer_id |
Mã khách ở nguồn | text |
ordered_at |
Thời điểm đặt đơn có múi giờ | timestamptz |
order_status |
Trạng thái đơn | text |
is_test_order |
Cờ đơn thử nghiệm | boolean |
source_updated_at |
Thời điểm cập nhật tại nguồn | timestamptz |
version_id |
Thứ tự phiên bản khi trùng thời điểm cập nhật | bigint |
Trong ví dụ, cặp source_system và order_id nhận diện một đơn. Bộ ba gồm hai trường này và version_id nhận diện một phiên bản. Các trường khóa và thời điểm cập nhật được giả định đã có giá trị hợp lệ.
2.2. Bảng ánh xạ định danh khách hàng
Bảng customer_identity_map đưa mã khách từng nguồn về một mã dùng chung.
| source_system | source_customer_id | customer_key |
|---|---|---|
| web | W01 | C01 |
| pos | P88 | C01 |
| web | W02 | C02 |
W01 và P88 đã được xác minh thuộc cùng khách C01. Mỗi cặp hệ thống nguồn và mã khách phải ánh xạ đến tối đa một customer_key trong thiết kế minh họa này.
2.3. Quy tắc đơn hợp lệ của ví dụ
- Trạng thái sau chuẩn hóa bằng Completed.
- Cờ thử nghiệm phải xác nhận là FALSE.
- Có mã khách dùng chung.
- Có thời điểm đặt đơn.
Ví dụ tập trung vào hành vi mua theo trạng thái Completed và chưa tích hợp dữ liệu hoàn trả. Khi áp dụng thực tế, cần bổ sung chính sách hoàn trả nếu chính sách đó ảnh hưởng đến định nghĩa mua hợp lệ.
3. Xây dựng lớp giao dịch chuẩn bằng SQL
3.1. Chọn phiên bản mới nhất trước khi lọc đơn
CTE giúp chia truy vấn thành các bước có tên để kiểm tra riêng từng kết quả [1]. Trong bước đầu, ROW_NUMBER() đánh số các phiên bản của mỗi đơn theo thứ tự cập nhật giảm dần [2].
Truy vấn sau tạo View analytics_orders_standard. View cung cấp kết quả từ truy vấn mỗi khi được sử dụng, phù hợp để minh họa một lớp dữ liệu dùng chung [3].
CREATE VIEW analytics_orders_standard AS
WITH ranked_versions AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY source_system, order_id
ORDER BY source_updated_at DESC, version_id DESC
) AS version_rank
FROM raw_order_versions
),
latest_orders AS (
SELECT *
FROM ranked_versions
WHERE version_rank = 1
),
normalized_orders AS (
SELECT
source_system,
order_id,
source_customer_id,
ordered_at,
ordered_at AT TIME ZONE 'Asia/Ho_Chi_Minh'
AS ordered_at_local,
LOWER(BTRIM(order_status)) AS normalized_status,
is_test_order,
source_updated_at,
version_id
FROM latest_orders
)
SELECT
o.source_system,
o.order_id,
m.customer_key,
o.ordered_at,
o.ordered_at_local,
o.ordered_at_local::date AS order_date,
DATE_TRUNC('month', o.ordered_at_local)::date
AS order_month,
o.normalized_status,
o.source_updated_at,
o.version_id,
CASE
WHEN o.is_test_order IS NULL
THEN 'unknown_test_flag'
WHEN o.is_test_order = TRUE
THEN 'test_order'
WHEN o.normalized_status IS DISTINCT FROM 'completed'
THEN 'status_not_completed'
WHEN m.customer_key IS NULL
THEN 'unidentified_customer'
WHEN o.ordered_at IS NULL
THEN 'missing_order_time'
ELSE 'eligible'
END AS eligibility_reason
FROM normalized_orders AS o
LEFT JOIN customer_identity_map AS m
ON o.source_system = m.source_system
AND o.source_customer_id = m.source_customer_id;3.2. Giải thích từng bước xử lý
- ranked_versions: xếp phiên bản trong từng đơn.
version_idtạo thứ tự xác định khi thời điểm cập nhật bằng nhau. - latest_orders: giữ một phiên bản mới nhất của mỗi đơn trong dữ liệu đang có.
- normalized_orders: chuẩn hóa trạng thái và chuyển thời gian sang múi giờ báo cáo.
- LEFT JOIN: giữ cả đơn chưa ánh xạ được khách để theo dõi chất lượng định danh.
- eligibility_reason: ghi lý do đầu tiên khiến đơn chưa đủ điều kiện. Đơn đạt yêu cầu nhận giá trị eligible.
Trong PostgreSQL, cách dùng AT TIME ZONE phụ thuộc kiểu dữ liệu đầu vào [4]. Ví dụ đã quy định ordered_at là timestamptz. Nếu nguồn lưu thời gian chưa có múi giờ, cần xác định múi giờ nguồn trước khi dùng phép chuyển tương ứng.
Điểm cần kiểm tra
Bảng ánh xạ khách phải duy nhất theo cặp khóa nối. Nếu một mã nguồn có hai dòng ánh xạ, một đơn có thể bị nhân thành hai dòng ngay sau phép JOIN.
4. Bổ sung thứ tự mua và cohort
4.1. Chỉ xếp thứ tự sau khi chọn đơn hợp lệ
Ngày mua đầu và thứ tự mua phải được tính trên cùng định nghĩa đơn hợp lệ. Nếu xếp thứ tự trước rồi mới loại đơn hủy, đơn mua hợp lệ đầu tiên có thể bị mang số thứ tự 2.
CREATE VIEW analytics_customer_purchases AS
WITH eligible_orders AS (
SELECT *
FROM analytics_orders_standard
WHERE eligibility_reason = 'eligible'
),
purchase_history AS (
SELECT
source_system,
order_id,
customer_key,
ordered_at,
ordered_at_local,
order_date,
order_month,
ROW_NUMBER() OVER (
PARTITION BY customer_key
ORDER BY ordered_at, source_system, order_id
) AS purchase_sequence,
MIN(order_date) OVER (
PARTITION BY customer_key
) AS first_purchase_date
FROM eligible_orders
)
SELECT
*,
DATE_TRUNC('month', first_purchase_date)::date
AS cohort_month
FROM purchase_history;4.2. Đọc kết quả đầu ra
Giả sử C01 có một đơn hợp lệ trên website vào tháng 1 và một đơn hợp lệ tại cửa hàng vào tháng 2. C02 chỉ có một đơn hợp lệ trong tháng 2.
| Khách | Nguồn / đơn | Ngày mua | Thứ tự mua | Cohort |
|---|---|---|---|---|
| C01 | web / O101 | 10/01/2026 | 1 | 01/2026 |
| C01 | pos / O202 | 15/02/2026 | 2 | 01/2026 |
| C02 | web / O103 | 20/02/2026 | 1 | 02/2026 |
Đơn thứ hai của C01 vẫn thuộc cohort tháng 1. Nguồn giao dịch thay đổi từ website sang cửa hàng, nhưng mã khách dùng chung giúp duy trì liên kết lịch sử.
Nếu hai đơn có cùng thời điểm đặt, các trường nguồn và mã đơn giúp đánh số ổn định. Khi đó, thứ tự giữa hai đơn đồng thời là quy ước kỹ thuật. Bài toán cần phân biệt chính xác hành vi trước–sau phải có dữ liệu thời gian đủ chi tiết.
5. Kiểm tra kết quả đầu ra
5.1. Kiểm tra một dòng cho mỗi đơn
SELECT
source_system,
order_id,
COUNT(*) AS row_count
FROM analytics_orders_standard
GROUP BY source_system, order_id
HAVING COUNT(*) > 1;Kết quả mong đợi là không có dòng. Nếu xuất hiện, hãy kiểm tra bảng ánh xạ định danh và tính duy nhất của các khóa nguồn.
5.2. Theo dõi lý do đơn chưa đủ điều kiện
SELECT
eligibility_reason,
COUNT(*) AS order_count
FROM analytics_orders_standard
GROUP BY eligibility_reason
ORDER BY order_count DESC;Báo cáo này giúp nhận diện số đơn thử nghiệm, đơn chưa Completed hoặc đơn thiếu định danh. Mỗi đơn chỉ có một lý do ưu tiên trong thiết kế hiện tại. Nếu cần thống kê tất cả lỗi đồng thời của một đơn, có thể bổ sung các cờ chất lượng riêng.
5.3. Đối chiếu lịch sử của một khách
SELECT
source_system,
order_id,
order_date,
purchase_sequence,
first_purchase_date,
cohort_month
FROM analytics_customer_purchases
WHERE customer_key = 'C01'
ORDER BY purchase_sequence;Hãy đối chiếu một số khách mua đa kênh, khách có đơn hủy và khách có đơn được cập nhật nhiều lần. Cần giải thích được vì sao từng đơn xuất hiện hoặc bị loại, cũng như cách xác định lần mua đầu.
6. Tái sử dụng cho toàn bộ chuỗi phân tích
6.1. Tính Repeat Purchase Rate và Purchase Frequency trong kỳ
Ví dụ sau đo hai chỉ số trong tháng 2. Repeat Purchase Rate được định nghĩa là tỷ lệ người có ít nhất hai đơn trong chính tháng đó.
WITH customer_orders AS (
SELECT
customer_key,
COUNT(*) AS orders_in_month
FROM analytics_customer_purchases
WHERE order_date >= DATE '2026-02-01'
AND order_date < DATE '2026-03-01'
GROUP BY customer_key
)
SELECT
COUNT(*) AS purchasing_customers,
SUM(orders_in_month) AS total_orders,
100.0 * COUNT(*) FILTER (
WHERE orders_in_month >= 2
) / NULLIF(COUNT(*), 0) AS repeat_purchase_rate_pct,
AVG(orders_in_month) AS purchase_frequency
FROM customer_orders;Khách từng mua tháng trước nhưng chỉ có một đơn tháng 2 sẽ có orders_in_month = 1. Nếu cần tỷ lệ khách quay lại dựa trên toàn bộ lịch sử, phải dùng định nghĩa và truy vấn tương ứng.
6.2. Tạo dữ liệu hoạt động theo cohort
SELECT
cohort_month,
order_month AS activity_month,
COUNT(DISTINCT customer_key) AS active_customers
FROM analytics_customer_purchases
GROUP BY cohort_month, order_month
ORDER BY cohort_month, activity_month;Kết quả là số khách có mua theo cohort và tháng hoạt động. Để tính Retention Rate, cần chia cho quy mô cohort ban đầu và bổ sung các ô bằng 0 ở kỳ đã hoàn tất nhưng không có người mua.
Các kỳ tương lai hoặc chưa đủ dữ liệu phải được đánh dấu riêng. Việc bảng hoạt động không có dòng chưa đủ để kết luận tỷ lệ bằng 0.
6.3. Tạo dữ liệu cho RFM và mô hình dự đoán
Từ lớp mua hợp lệ, có thể tổng hợp ngày mua gần nhất và số đơn trước một mốc đánh giá để tạo Recency và Frequency. Monetary cần thêm trường giá trị giao dịch với chính sách tiền tệ, giảm giá và hoàn tiền rõ ràng.
Với Machine Learning, mỗi quan sát cần gắn với ngày đánh giá. Đặc trưng sử dụng giao dịch đã biết tại ngày đó, còn nhãn được tạo từ cửa sổ tương lai đã theo dõi đầy đủ.
7. Cập nhật dữ liệu và bảo toàn lịch sử
7.1. Lần mua đầu phụ thuộc độ dài lịch sử
first_purchase_date là ngày mua hợp lệ đầu tiên trong dữ liệu hiện có. Nếu lịch sử bị thiếu, cần ghi rõ đây là lần mua đầu tiên quan sát được.
Khi một đơn cũ đến trễ, ngày mua đầu và cohort có thể thay đổi. Nếu cập nhật tăng dần, cần tính lại lịch sử của những khách bị ảnh hưởng. Chỉ thêm dòng mới mà giữ nguyên cohort cũ có thể tạo kết quả không nhất quán.
7.2. View mới nhất và Snapshot có mục đích khác nhau
Các View trong bài phản ánh dữ liệu nguồn và ánh xạ khách đang có khi truy vấn. Đơn đổi trạng thái hoặc định danh được sửa có thể làm kết quả lịch sử thay đổi.
Nếu cần tái tạo chính xác báo cáo đã công bố hoặc dữ liệu tại thời điểm dự đoán, hãy lưu Snapshot, phiên bản quy tắc và lịch sử tiếp nhận dữ liệu. Khi đó, việc chọn phiên bản đơn cần xét cả thời điểm thông tin được hệ thống phân tích biết đến.
7.3. Chọn cách lưu theo quy mô sử dụng
View phù hợp để tập trung logic và thuận tiện kiểm tra. Khi khối lượng lớn hoặc nhiều báo cáo sử dụng cùng kết quả, có thể cân nhắc bảng được cập nhật theo lịch hoặc Materialized View.
Lựa chọn nên dựa trên thời gian truy vấn thực tế, yêu cầu độ mới và khả năng cập nhật lại dữ liệu bị điều chỉnh. CTE chủ yếu giúp tổ chức logic; hiệu năng cần được đánh giá trên kế hoạch thực thi và dữ liệu cụ thể.
8. Tài liệu tham khảo
- PostgreSQL Documentation. WITH Queries — Common Table Expressions. Tài liệu về tổ chức truy vấn thành các bước có tên. Đọc tài liệu CTE.
- PostgreSQL Documentation. Window Functions. Tài liệu về ROW_NUMBER và các phép tính theo nhóm dòng. Đọc tài liệu Window Functions.
- PostgreSQL Documentation. CREATE VIEW. Tài liệu về tạo và sử dụng View. Đọc tài liệu CREATE VIEW.
- PostgreSQL Documentation. Date/Time Functions and Operators. Tài liệu về DATE_TRUNC, AT TIME ZONE và xử lý thời gian. Đọc tài liệu xử lý thời gian.
9. Thực hành phân tích dữ liệu cùng MCNA
Để thực hành xử lý dữ liệu giao dịch, xây dựng chỉ số và trình bày báo cáo phân tích khách hàng, bạn có thể tham khảo các chương trình tại MCNA Technology School.
Combo 5 khóa AI & Power BI
Phát triển kỹ năng phân tích dữ liệu và xây dựng báo cáo phục vụ các bài toán kinh doanh.
Combo Python
Thực hành lập trình và xử lý dữ liệu để tiếp cận các bài toán phân tích khách hàng.
10. Tác giả
Nguyễn Quốc Hải – Data Analyst tại MCNA Technology School
MCNA Technology School là đơn vị tiên phong đào tạo AI, Big Data, Business Intelligence tại Việt Nam. Chuyên sâu về Power BI, Python, SQL và Data Analytics — đối tác đào tạo của 300+ doanh nghiệp lớn như Viettel Global, Masan Group, Techcombank, VPBank, Daikin.
🌐 Website: mcna.vn
📘 Facebook: facebook.com/mcnatechnologyschool
▶️ YouTube: youtube.com/@mcna.technology.school
💼 LinkedIn: linkedin.com/company/mcna-vn
📞 Hotline: 0939.866.825 (Mr. Minh Khang)
📍 Hà Nội: 30 Trung Liệt, Đống Đa | Liền kề 44B, TT2, Văn Quán, Hà Đông
📍 TP.HCM: The BIB Space, 50B Phan Tây Hồ, Cầu Kiệu
