Thẻ 6 — Dữ liệu thẻ cho Data & BI (thực chiến)

Đây là bài khép lại series Thẻ ngân hàng chuyên sâu, nhìn hệ sinh thái thẻ qua con mắt của Data Analyst / BI. Sau khi đã hiểu vòng đời thẻ, luồng thanh toán, interchange và rủi ro gian lận ở các bài trước, giờ ta sẽ trả lời câu hỏi thực chiến: dữ liệu thẻ nằm ở đâu, mô hình hóa thế nào, đo cái gì, và viết SQL ra sao để phục vụ báo cáo và ra quyết định.

Bài viết đi từ mô hình dữ liệu → KPI thẻ → phân tích chi tiêu & phát hiện bất thường → báo cáo lãnh đạo, và kèm nhiều khối SQL ▶ Chạy được trong SQL Builder trên schema sandbox để bạn thực hành ngay.

Dữ liệu thẻ đến từ đâu?

Một giao dịch thẻ đi qua nhiều hệ thống, và mỗi hệ thống để lại một loại "dấu vết dữ liệu" khác nhau. Bốn nguồn cốt lõi mà Data team thường phải hợp nhất:

• Authorization log (nhật ký cấp phép): phát sinh tại thời điểm quẹt thẻ. Chứa trạng thái approve/decline, mã lý do từ chối (insufficient funds, do-not-honor, fraud suspected), số tiền, merchant, thiết bị/kênh (POS, e-commerce, ATM). Đây là nguồn realtime, khối lượng lớn nhất.
• Clearing / Settlement (bù trừ & quyết toán): phát sinh T+1..T+2 khi merchant gửi file batch để đòi tiền. Đây mới là số tiền thực sự ghi nợ khách và tính interchange. Một authorization có thể không bao giờ được settle (khách hủy đơn) hoặc settle với số tiền khác (tip nhà hàng).
• Cardholder / Card master: thông tin thẻ và chủ thẻ — hạng thẻ, hạn mức, ngày kích hoạt, trạng thái (active/blocked/expired), liên kết với khách hàng.
• Merchant master: thông tin điểm chấp nhận thẻ — tên, MCC (Merchant Category Code), quốc gia, acquirer.

Điểm mấu chốt cho Analyst: authorization ≠ settlement. Báo cáo doanh số thường dùng settlement, còn báo cáo tỷ lệ approve/decline và fraud realtime lại dùng authorization. Nhầm nguồn là sai số ngay.

Mô hình dữ liệu thẻ trong kho (star schema)

Trong data warehouse, ta mô hình theo lược đồ sao (star schema): một bảng fact ở tâm với grain = 1 dòng cho 1 giao dịch thẻ, bao quanh là các dimension mô tả.

Vài nguyên tắc thiết kế quan trọng:

• Grain rõ ràng: 1 dòng = 1 giao dịch (authorization được enrich thêm giá trị settlement). Không trộn nhiều mức tổng hợp vào cùng một bảng fact.
• Measure phân loại: auth_amount và settle_amount là additive (cộng được theo mọi chiều). is_fraud, auth_status là cờ để lọc/đếm.
• Slowly Changing Dimension: hạn mức thẻ hay hạng khách thay đổi theo thời gian → dùng SCD Type 2 nếu cần phân tích lịch sử.
• MCC là "vàng": phân nhóm MCC (ăn uống, du lịch, xăng dầu, e-commerce...) là chiều phân tích chi tiêu giá trị nhất.

Luồng dữ liệu tổng thể từ nguồn tới báo cáo:

Bộ KPI thẻ cốt lõi

Đây là các chỉ số mà một Card Analyst phải nắm và biết tính. Bảng dưới ghi rõ công thức và ý nghĩa nghiệp vụ.

Lưu ý: fraud rate bps dùng số tiền chứ không dùng số giao dịch để phản ánh thiệt hại tài chính. 1 bps = 0,01%.

Thực hành SQL trên sandbox

Sandbox không có bảng thẻ riêng, nên ta mô phỏng "giao dịch thẻ" bằng schema đơn giản sau:

• customers(id, full_name, city, created_at)
• accounts(id, customer_id, account_no, balance, currency) — khóa chính là id (không phải account_id)
• transactions(id, account_id, amount, kind ['credit'|'debit'], created_at)

Quy ước mô phỏng: coi mỗi tài khoản như một "thẻ", mỗi dòng transactions với kind = 'debit' là một giao dịch chi tiêu thẻ, kind = 'credit' là nạp tiền/thanh toán. Đây chỉ là mô phỏng để luyện tư duy truy vấn; schema thẻ thật phong phú hơn nhiều (có auth/settle riêng, MCC, decline reason...).

Tổng chi tiêu (debit) theo tài khoản

Mô phỏng "tổng spend theo thẻ" — nền tảng cho spend per active card.

-- ▶ Chạy được trong SQL Builder
SELECT
  account_id,
  COUNT(*)      AS so_giao_dich,
  SUM(amount)   AS tong_chi_tieu
FROM transactions
WHERE kind = 'debit'
GROUP BY account_id
ORDER BY tong_chi_tieu DESC;

Số giao dịch theo tháng

Dùng date_trunc để dựng chuỗi thời gian — chính là chiều DIM_DATE trong star schema.

-- ▶ Chạy được trong SQL Builder
SELECT
  date_trunc('month', created_at) AS thang,
  COUNT(*)                        AS so_giao_dich,
  SUM(amount)                     AS gia_tri
FROM transactions
WHERE kind = 'debit'
GROUP BY date_trunc('month', created_at)
ORDER BY thang;

Top tài khoản theo số giao dịch (tần suất)

Mô phỏng KPI tần suất giao dịch — tìm "thẻ" hoạt động mạnh nhất.

-- ▶ Chạy được trong SQL Builder
SELECT
  a.id            AS account_id,
  a.account_no,
  COUNT(t.id)     AS so_giao_dich,
  SUM(t.amount)   AS tong_gia_tri
FROM accounts a
JOIN transactions t ON t.account_id = a.id
WHERE t.kind = 'debit'
GROUP BY a.id, a.account_no
ORDER BY so_giao_dich DESC
LIMIT 10;

Phát hiện tài khoản tần suất bất thường

Bài toán fraud kinh điển: một "thẻ" quẹt quá nhiều lần trong thời gian ngắn. Ở đây ta dùng HAVING COUNT(*) làm ngưỡng đơn giản.

-- ▶ Chạy được trong SQL Builder
SELECT
  account_id,
  COUNT(*)                          AS so_giao_dich,
  SUM(amount)                       AS tong_gia_tri,
  ROUND(AVG(amount), 2)             AS gia_tri_tb
FROM transactions
WHERE kind = 'debit'
GROUP BY account_id
HAVING COUNT(*) > 20
ORDER BY so_giao_dich DESC;

Phát hiện giao dịch giá trị bất thường (velocity theo ngày)

Nâng cấp: đếm số giao dịch trong cùng một ngày của mỗi tài khoản — mô phỏng luật "velocity" trong fraud engine.

-- ▶ Chạy được trong SQL Builder
SELECT
  account_id,
  date_trunc('day', created_at) AS ngay,
  COUNT(*)                      AS gd_trong_ngay,
  SUM(amount)                   AS tong_ngay
FROM transactions
WHERE kind = 'debit'
GROUP BY account_id, date_trunc('day', created_at)
HAVING COUNT(*) >= 5
ORDER BY gd_trong_ngay DESC, tong_ngay DESC;

Phân nhóm khách theo tổng chi tiêu (dùng CTE)

Mô phỏng segmentation — chia khách thành nhóm chi tiêu Cao / Trung bình / Thấp, tương tự phân hạng chủ thẻ.

-- ▶ Chạy được trong SQL Builder
WITH spend_per_customer AS (
  SELECT
    a.customer_id,
    SUM(t.amount) AS tong_chi_tieu
  FROM accounts a
  JOIN transactions t ON t.account_id = a.id
  WHERE t.kind = 'debit'
  GROUP BY a.customer_id
)
SELECT
  c.id,
  c.full_name,
  c.city,
  s.tong_chi_tieu,
  CASE
    WHEN s.tong_chi_tieu >= 10000 THEN 'Cao'
    WHEN s.tong_chi_tieu >= 3000  THEN 'Trung binh'
    ELSE 'Thap'
  END AS nhom_chi_tieu
FROM spend_per_customer s
JOIN customers c ON c.id = s.customer_id
ORDER BY s.tong_chi_tieu DESC;

Mô phỏng "approval rate" theo tháng

Coi credit như giao dịch "được duyệt/hoàn tất" và debit như phát sinh chi tiêu, ta có thể tính tỷ lệ giữa hai loại theo tháng để luyện tư duy tính tỷ lệ có điều kiện.

-- ▶ Chạy được trong SQL Builder
SELECT
  date_trunc('month', created_at) AS thang,
  COUNT(*)                                              AS tong_gd,
  SUM(CASE WHEN kind = 'debit'  THEN 1 ELSE 0 END)      AS so_debit,
  ROUND(
    100.0 * SUM(CASE WHEN kind = 'debit' THEN 1 ELSE 0 END) / COUNT(*),
    2
  )                                                     AS ty_le_debit_pct
FROM transactions
GROUP BY date_trunc('month', created_at)
ORDER BY thang;

Số dư và số "thẻ" active theo thành phố

Kết hợp 3 bảng — mô phỏng báo cáo phân bổ theo địa lý cho lãnh đạo.

-- ▶ Chạy được trong SQL Builder
SELECT
  c.city,
  COUNT(DISTINCT a.id)                       AS so_the,
  COUNT(DISTINCT t.account_id)               AS the_co_giao_dich,
  ROUND(SUM(CASE WHEN t.kind = 'debit' THEN t.amount ELSE 0 END), 2) AS tong_chi_tieu
FROM customers c
JOIN accounts a       ON a.customer_id = c.id
LEFT JOIN transactions t ON t.account_id = a.id
GROUP BY c.city
ORDER BY tong_chi_tieu DESC NULLS LAST;

Phân tích chi tiêu: MCC, merchant, thời gian

Trong dữ liệu thẻ thật, các trục phân tích chi tiêu giá trị nhất là:

• Theo MCC / nhóm MCC: khách đổ tiền vào ăn uống, du lịch hay xăng dầu? Đây là cơ sở cho chương trình hoàn tiền theo ngành hàng và phát hiện dịch chuyển hành vi.
• Theo merchant: top merchant hút chi tiêu → cơ hội hợp tác ưu đãi, đàm phán interchange.
• Theo thời gian: seasonality (Tết, cuối tháng lương về), giờ cao điểm — hữu ích cho capacity planning hệ thống auth và cho fraud (giao dịch 3h sáng đáng ngờ hơn).
• Theo hạng thẻ: thẻ premium chi tiêu khác thẻ chuẩn; đo spend per active card theo hạng để định giá phí thường niên.

Với sandbox, các truy vấn date_trunc và GROUP BY account_id ở trên chính là bản rút gọn của những phân tích này — thay account_id bằng mcc_group là ra báo cáo MCC thật.

Phát hiện bất thường & báo cáo lãnh đạo

Phát hiện bất thường trong thẻ thường theo mấy hướng:

• Velocity: quá nhiều giao dịch/thời gian ngắn (đã mô phỏng bằng HAVING COUNT >= 5 theo ngày).
• Số tiền lệch chuẩn: giao dịch lớn bất thường so với AVG lịch sử của chính thẻ đó (z-score).
• Geo/impossible travel: hai giao dịch ở hai quốc gia cách nhau vài phút.
• Decline bùng phát: chuỗi decline liên tiếp trước một approve — dấu hiệu dò thẻ (card testing).

Những luật này chạy realtime trong fraud engine; Data team đóng vai trò theo dõi fraud rate (bps), xây feature cho model ML, và điều tra hồi tố.

Báo cáo cho lãnh đạo nên tối giản và đúng nhịp quyết định:

• Trang tổng quan (1 màn hình): số thẻ active, activation rate, spend per active card, fraud bps, interchange revenue — kèm so sánh MoM/YoY.
• Cảnh báo ngoại lệ: chỉ hiển thị khi KPI vượt ngưỡng (fraud bps > 10, approval rate giảm > 2 điểm %).
• Drill-down: từ tổng → MCC → merchant → thẻ, để điều tra khi có bất thường.

Nguyên tắc vàng: lãnh đạo cần xu hướng và ngoại lệ, không cần bảng thô. Analyst giỏi là người biến hàng triệu dòng fact_card_txn thành 5 con số đáng để họp.

Tóm tắt

• Dữ liệu thẻ hợp nhất từ 4 nguồn: authorization, settlement, cardholder, merchant — và authorization ≠ settlement.
• Mô hình hóa bằng star schema: fact_card_txn grain 1 giao dịch, quanh là dimension thẻ/khách/merchant(MCC)/thời gian/loại giao dịch.
• Bộ KPI cốt lõi gồm số thẻ active, activation rate, spend per active card, approval/decline rate, fraud bps, interchange revenue, dư nợ & NPL thẻ.
• Ta luyện tư duy truy vấn qua sandbox customers/accounts/transactions (mô phỏng), với GROUP BY, HAVING, date_trunc, CASE, và CTE.
• Báo cáo lãnh đạo tốt = xu hướng + ngoại lệ, có drill-down, không phải bảng thô.

Tự kiểm tra

1. Vì sao báo cáo doanh số nên dùng settlement thay vì authorization, còn báo cáo approval rate lại dùng authorization?
2. Grain của fact_card_txn là gì, và tại sao không nên trộn nhiều mức tổng hợp vào cùng bảng fact?
3. Viết lại truy vấn "top tài khoản theo số giao dịch" nhưng chỉ tính các giao dịch trong 90 ngày gần nhất.
4. Fraud rate 8 bps nghĩa là bao nhiêu phần trăm doanh số bị gian lận? Ngưỡng này an toàn hay đáng lo?
5. Trong khối CTE phân nhóm chi tiêu, nếu muốn thêm nhóm "VIP" cho khách chi tiêu >= 50.000 thì sửa CASE thế nào?
6. Vì sao MCC được coi là chiều phân tích "vàng" của dữ liệu thẻ, và bạn sẽ mô phỏng nó ra sao trên sandbox?

Đọc tiếp

• Quay lại đầu series: Thẻ 1 — Hệ sinh thái thẻ để nối lại toàn cảnh.
• AI (fraud): tìm hiểu cách xây model phát hiện gian lận thẻ (feature velocity, geo, z-score) và đánh giá bằng precision/recall trên dữ liệu lệch lớp.
• Data Engineering: cách dựng pipeline nạp authorization + settlement vào warehouse, xử lý late-arriving settlement và mô hình SCD Type 2 cho dimension thẻ/khách.