Xác định lần mua đầu tiên bằng SQL: Phân biệt khách mới thật và khách mới trong dữ liệu

Một khách đặt hàng ngày 10/1 và được báo cáo là khách mới tháng 1. Sau đó, đội dữ liệu bổ sung lịch sử từ năm trước và phát hiện người này đã mua từ tháng 11. Truy vấn tìm ngày nhỏ nhất vẫn trả về đúng kết quả trên tập đầu vào, nhưng cách diễn giải khách mới đã thay đổi.

Xác định lần mua đầu tiên bằng SQL cần kết hợp kỹ thuật truy vấn với hiểu biết về phạm vi quan sát. Trong bài này, chúng ta tiếp tục sử dụng lớp đơn hàng chuẩn của bài số 7, rồi xây dựng dữ liệu ngày mua đầu phục vụ Cohort Analysis và phân tích giữ chân.

Điểm cần nhớ

Ngày nhỏ nhất trong bảng là lần mua hợp lệ đầu tiên quan sát được. Mức độ chắc chắn rằng đó là lần mua đầu thực sự phụ thuộc lịch sử, độ phủ kênh và khả năng nhận diện khách hàng.

1. Khách mới thật và khách mới trong dữ liệu

1.1. Ba khái niệm cần tách biệt

Một người có thể là khách mới ở kênh online nhưng đã mua tại cửa hàng. Phạm vi doanh nghiệp, kênh hay ngành hàng phải được xác định trước khi gán nhãn.

1.2. Một đơn hàng chưa đủ để xác nhận khách mới thật

Số đơn quan sát được cho biết lịch sử hiện có. Khách chỉ có một đơn trong bảng vẫn có thể có nhiều giao dịch trước mốc bắt đầu dữ liệu hoặc dưới một mã khác. Ngược lại, khách mua lần đầu trong tháng có thể đặt thêm hai đơn ngay trong tháng đó và vẫn thuộc cohort khách mới của tháng.

2. Chuẩn bị dữ liệu và phạm vi quan sát

2.1. Tiếp tục từ bảng giao dịch chuẩn

Các truy vấn dùng PostgreSQL và View analytics_orders_standard đã giới thiệu ở bài trước. Mỗi dòng là một đơn sau khi chọn phiên bản, chuẩn hóa thời gian và ánh xạ định danh.

Cần kiểm tra tính duy nhất của khóa đơn, mã khách và điều kiện hợp lệ trước khi tìm lần mua đầu. Nếu đơn hủy sớm nhất còn trong tập đầu vào, kết quả MIN sẽ dùng chính đơn đó.

2.2. Dữ liệu minh họa

Giả sử bộ lịch sử đầy đủ của ví dụ gồm các đơn dưới đây. Tất cả đơn đều hợp lệ. Thời điểm được trình bày theo giờ Việt Nam.

Nếu chỉ giữ dữ liệu từ ngày 1/1/2026, C01 và C03 sẽ có lần mua đầu quan sát được ở tháng 1. Trong lịch sử đầy đủ của ví dụ, hai khách đã mua trước đó. C02 có hai đơn tháng 1 nhưng vẫn là khách mua lần đầu trong tháng này.

3. Xác định lần mua đầu tiên bằng SQL với MIN và GROUP BY

3.1. Tạo một dòng cho mỗi khách

MIN trả về giá trị nhỏ nhất trong các giá trị khác NULL. Kết hợp GROUP BY customer_key, ta tính một thời điểm sớm nhất cho mỗi khách [1].

SELECT
    customer_key,
    MIN(ordered_at) AS first_observed_purchase_at
FROM analytics_orders_standard
WHERE eligibility_reason = 'eligible'
GROUP BY customer_key;

Kết quả có một dòng mỗi khách. Tên first_observed_purchase_at nhắc người dùng rằng phép tính chỉ xét lịch sử được cung cấp.

3.2. Tìm ngày mua đầu trước, lọc kỳ báo cáo sau

Để đếm người có lần mua đầu quan sát được trong tháng 1, hãy tính MIN trên toàn bộ lịch sử hợp lệ rồi mới lọc ngày kết quả. Nếu đặt điều kiện tháng 1 ngay trong tập đầu vào của MIN, truy vấn chỉ tìm lần mua sớm nhất trong tháng 1.

WITH customer_first_purchase AS (
    SELECT
        customer_key,
        MIN(order_date) AS first_observed_purchase_date
    FROM analytics_orders_standard
    WHERE eligibility_reason = 'eligible'
    GROUP BY customer_key
)
SELECT COUNT(*) AS first_observed_customers
FROM customer_first_purchase
WHERE first_observed_purchase_date >= DATE '2026-01-01'
  AND first_observed_purchase_date < DATE '2026-02-01';

Với toàn bộ lịch sử ở bảng minh họa, kết quả là 1 khách, C02. Nếu nguồn chỉ còn từ tháng 1, kết quả sẽ thành 3 khách, gồm C01, C02 và C03. SQL áp dụng cùng phép tính, nhưng phạm vi đầu vào đã khác.

4. Lấy đúng đơn đầu bằng Window Function

4.1. Khi cần mã đơn và kênh ở lần mua đầu

MIN cho biết thời điểm sớm nhất. Nếu cần cả mã đơn và kênh của giao dịch đó, hãy xếp các đơn theo thời gian trong từng khách rồi chọn dòng đầu. ROW_NUMBER đánh số từ 1 trong mỗi partition [2].

WITH ranked_orders AS (
    SELECT
        customer_key,
        source_system,
        order_id,
        ordered_at,
        order_date,
        ROW_NUMBER() OVER (
            PARTITION BY customer_key
            ORDER BY ordered_at, source_system, order_id
        ) AS purchase_rank
    FROM analytics_orders_standard
    WHERE eligibility_reason = 'eligible'
)
SELECT
    customer_key,
    source_system AS first_order_channel,
    order_id AS first_order_id,
    ordered_at AS first_observed_purchase_at,
    order_date AS first_observed_purchase_date
FROM ranked_orders
WHERE purchase_rank = 1;

Khóa nguồn và mã đơn tạo thứ tự xác định khi hai đơn có cùng timestamp. Quy tắc này chọn ổn định một đơn trong các đơn đồng thời. Nếu nghiệp vụ cần gom các đơn cùng phiên mua, phải xác định thêm quy tắc phiên.

4.2. Giữ mọi đơn và gắn ngày mua đầu lên từng dòng

Với phân tích hành trình, có thể giữ nguyên từng giao dịch và tính MIN dưới dạng Window Function. Phần OVER chỉ dùng PARTITION BY nên lấy nhỏ nhất trên toàn bộ nhóm khách [2].

SELECT
    customer_key,
    source_system,
    order_id,
    order_date,
    MIN(order_date) OVER (
        PARTITION BY customer_key
    ) AS first_observed_purchase_date
FROM analytics_orders_standard
WHERE eligibility_reason = 'eligible';

5. Phân loại khách theo giới hạn lịch sử

5.1. Ghi nhận mức độ chắc chắn của nhãn

Nếu chỉ quan sát từ ngày 1/1, bạn thường không biết một người mua ngày 10/1 đã từng mua trước đó hay chưa. Có thể dùng ba nhãn trong báo cáo:

• Returning observed: đã có giao dịch hợp lệ trước kỳ trong dữ liệu.
• First observed, history incomplete: giao dịch sớm nhất quan sát được thuộc kỳ, nhưng lịch sử chưa đủ để xác nhận mua lần đầu thực sự.
• First purchase confirmed within scope: có cơ sở xác nhận lịch sử đầy đủ trong phạm vi đã công bố, chẳng hạn đủ dữ liệu từ khi doanh nghiệp bắt đầu hoạt động.

Ngày tạo tài khoản, ngày tham gia chương trình thành viên hoặc một thời gian dài không mua có thể cung cấp thêm ngữ cảnh. Mức độ đầy đủ cần được xác minh với nguồn vận hành và định danh khách.

5.2. Lookback Window tạo một định nghĩa vận hành

Doanh nghiệp có thể gọi người không có đơn trong 180 ngày trước một lần mua là khách mới theo cửa sổ 180 ngày. Cách đặt nhãn phải thể hiện rõ cửa sổ, vì người đó có thể đã mua cách đây 200 ngày.

Ví dụ dưới đây tìm người mua tháng 7 không có giao dịch hợp lệ từ ngày 2/1 đến trước ngày 1/7, tức 180 ngày trước kỳ. Đây là khoảng nhìn lại theo mốc kỳ, áp dụng chung cho mọi người mua tháng 7.

WITH july_buyers AS (
    SELECT DISTINCT customer_key
    FROM analytics_orders_standard
    WHERE eligibility_reason = 'eligible'
      AND order_date >= DATE '2026-07-01'
      AND order_date < DATE '2026-08-01'
)
SELECT b.customer_key
FROM july_buyers AS b
WHERE NOT EXISTS (
    SELECT 1
    FROM analytics_orders_standard AS h
    WHERE h.eligibility_reason = 'eligible'
      AND h.customer_key = b.customer_key
      AND h.order_date >= DATE '2026-07-01' - 180
      AND h.order_date < DATE '2026-07-01'
);

Chỉ dùng kết quả khi lịch sử trong toàn bộ khoảng nhìn lại đã đầy đủ. Tên báo cáo phù hợp là “Người mua tháng 7 không có đơn trong 180 ngày trước kỳ”. Nếu cần nhìn lại 180 ngày trước từng đơn cụ thể, mốc thời gian trong truy vấn phải thay đổi theo đơn đó.

6. Tạo cohort và kiểm tra kết quả

6.1. Tạo bảng ngày mua đầu dùng chung

CREATE VIEW analytics_customer_first_purchase AS
WITH first_purchase AS (
    SELECT
        customer_key,
        MIN(order_date) AS first_observed_purchase_date
    FROM analytics_orders_standard
    WHERE eligibility_reason = 'eligible'
    GROUP BY customer_key
)
SELECT
    customer_key,
    first_observed_purchase_date,
    DATE_TRUNC('month', first_observed_purchase_date)::date
        AS cohort_month
FROM first_purchase;

View tạo một nguồn định nghĩa dùng chung cho các báo cáo. Mỗi khách có một cohort theo tháng mua đầu quan sát được. Trong lịch sử đầy đủ của ví dụ, C01 thuộc tháng 11/2025, C03 thuộc tháng 12/2025, C02 thuộc tháng 1/2026 và C04 thuộc tháng 2/2026.

6.2. Kiểm tra bằng dữ liệu và tình huống biên

• Tính duy nhất: mỗi customer_key xuất hiện đúng một lần ở View ngày mua đầu.
• Đối chiếu đơn: ngày mua đầu phải tìm thấy trong ít nhất một đơn hợp lệ của khách.
• Thời gian: không có đơn hợp lệ nào sớm hơn ngày mua đầu đã gán.
• Định danh: kiểm tra khách có đơn online và tại cửa hàng đã được gộp đúng.
• Đối chiếu tổng: tổng số khách của các cohort phải bằng số khách riêng biệt trong tập đơn hợp lệ cùng phạm vi.

Một sai lệch cohort có thể làm thay đổi cả tử số lẫn mẫu số retention. Nếu C01 bị đưa nhầm vào nhóm khách mới tháng 1, lần mua tháng 1 của họ sẽ được hiểu là điểm bắt đầu quan hệ, trong khi đó là một lần quay lại trong lịch sử đầy đủ.

6.3. Cập nhật khi có giao dịch cũ đến trễ

Giả sử hệ thống bổ sung đơn tháng 11 của C01 sau khi đã công bố cohort tháng 1. Với báo cáo lịch sử được tính lại, cần cập nhật ngày mua đầu và cohort của khách này, sau đó tính lại các ô chịu ảnh hưởng.

Với mô hình Machine Learning hoặc báo cáo cần tái tạo như tại một thời điểm quá khứ, hãy lưu ngày chốt, phiên bản quy tắc và thời điểm dữ liệu được tiếp nhận. Tránh sử dụng đơn mà hệ thống chưa biết tại lúc dự đoán.

7. Những lỗi thường gặp khi tìm lần mua đầu

1. Lọc kỳ báo cáo trước MIN. Kết quả trở thành lần mua đầu trong kỳ. Hãy tính lịch sử trước và lọc cohort sau.
2. Ghép MIN thời gian với MIN mã đơn. Hai giá trị có thể đến từ hai đơn khác nhau. Dùng ROW_NUMBER khi cần lấy cả dòng đơn đầu.
3. JOIN theo ngày mua đầu rồi kỳ vọng một dòng. Khách có nhiều đơn cùng ngày sẽ tạo nhiều kết quả. Chọn quy tắc đơn đầu hoặc chủ động giữ toàn bộ đơn đồng thời.
4. Dùng mã khách từng kênh như mã toàn doanh nghiệp. Chuyển kênh mua có thể tạo khách mới giả. Kiểm tra ánh xạ định danh.
5. Gọi mọi lần mua đầu quan sát được là khách mới thật. Ghi rõ phạm vi và giới hạn lịch sử của chỉ số.

Để thực hành, hãy chạy cùng truy vấn trên toàn bộ lịch sử và trên dữ liệu chỉ từ đầu năm. Sau đó đối chiếu những khách thay đổi cohort và giải thích nguyên nhân từ từng giao dịch. Bài tập này giúp kết nối kỹ thuật SQL với ý nghĩa kinh doanh của ngày mua đầu.

8. Tài liệu tham khảo

1. PostgreSQL Documentation. Aggregate Functions. Tài liệu về MIN và các phép tổng hợp. Đọc tài liệu Aggregate Functions.
2. PostgreSQL Documentation. Window Functions. Tài liệu về ROW_NUMBER và aggregate dưới dạng window. Đọc tài liệu Window Functions.
3. PostgreSQL Documentation. Table Expressions. Tài liệu về WHERE, GROUP BY và xử lý biểu thức bảng. Đọc tài liệu Table Expressions.

9. Thực hành phân tích dữ liệu cùng MCNA

Để củng cố kỹ năng xử lý dữ liệu, xây dựng báo cáo và tiếp cận các bài toán 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.

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