Appearance
🧠 Case Study Buổi 3: Excel — Data Cleaning & Pivot Table
Bài học thực tế từ các tổ chức hàng đầu thế giới và Việt Nam
Tổng quan
Trong buổi học này, chúng ta đã tìm hiểu về dirty data (6 loại), các hàm cleaning trong Excel (TRIM, CLEAN, PROPER, SUBSTITUTE, IFERROR…), nguyên tắc Tidy Data, Pivot Table và 5 chiều đánh giá Data Quality. Lý thuyết cho thấy 80% thời gian của Data Analyst là làm sạch dữ liệu — nhưng chỉ khi nhìn vào thực tế, bạn mới hiểu vì sao con số đó hoàn toàn hợp lý.
Ba case study dưới đây minh họa cách data cleaning và Pivot Table được áp dụng ở 3 quy mô và bối cảnh khác nhau — từ nền tảng toàn cầu (Airbnb), doanh nghiệp FMCG Việt Nam, đến ngân hàng bán lẻ trong nước. Mỗi case study sẽ trả lời 3 câu hỏi cốt lõi: (1) Dữ liệu gì cần làm sạch? (2) Phân tích và xử lý bằng công cụ/phương pháp nào? (3) Quyết định kinh doanh nào đã thay đổi nhờ dữ liệu sạch?
| # | Công ty | Vấn đề | Kỹ thuật chính |
|---|---|---|---|
| 1 | Airbnb | Cleaning 1 triệu listing records để tạo pricing dashboard | 6 loại dirty data, TRIM/CLEAN/SUBSTITUTE, Pivot Table, Tidy Data |
| 2 | Công ty FMCG Việt Nam | Chuẩn hóa data bán hàng từ 50 nhà phân phối khác format | Formatting errors, Inconsistency, Data Quality dimensions, Pivot Table tổng hợp |
| 3 | Ngân hàng VN | Data cleaning cho dữ liệu KYC khách hàng | Missing data, Duplicate, Validity, Data Validation, IFERROR |
Case Study 1: Airbnb — Cleaning 1 triệu listing records để tạo Pricing Dashboard
🏷️ Thông tin
| Tiêu chí | Chi tiết |
|---|---|
| Công ty | Airbnb, Inc. |
| Ngành | Hospitality / Online Marketplace |
| Quy mô | 7.7 triệu listings toàn cầu, ~$10.5B revenue (2024) |
| Thời điểm | Dự án pricing intelligence, dữ liệu tổng hợp từ 2023–2025 |
| Dữ liệu | Structured data — Quantitative (price, reviews, availability) + Qualitative (property type, neighborhood, amenities) |
📋 Bối cảnh
Airbnb thu thập dữ liệu listing từ hàng triệu host trên toàn thế giới. Mỗi listing chứa thông tin do host tự nhập: giá phòng, loại chỗ ở, số phòng ngủ, tiện nghi, mô tả, quy tắc nhà… Không giống hệ thống khách sạn truyền thống (nơi dữ liệu được chuẩn hóa từ hệ thống PMS — Property Management System), dữ liệu Airbnb là user-generated content — tức do hàng triệu người khác nhau tự nhập, dẫn đến chất lượng dữ liệu cực kỳ "bẩn".
Team Data Analytics của Airbnb cần xây dựng Pricing Dashboard giúp host định giá phòng tối ưu theo khu vực, mùa, loại phòng. Nhưng trước khi tạo dashboard, họ phải xử lý bộ dữ liệu ~1 triệu listings với đủ cả 6 loại dirty data.
Inside Airbnb (dự án mã nguồn mở của Murray Cox) đã công bố dữ liệu scraped từ Airbnb cho nhiều thành phố, cho thấy rõ mức độ "bẩn" của dữ liệu thực tế — đây cũng là dataset phổ biến trong cộng đồng Data Analytics để thực hành data cleaning.
⚡ Thách thức
- Duplicate listings: Khoảng 3–5% listings bị trùng lặp — cùng một property được đăng nhiều lần (host tạo listing mới thay vì chỉnh sửa cũ). Ví dụ: 1 căn hộ ở Quận 1, TP.HCM xuất hiện 3 lần với tên khác nhau: "Cozy Apartment in D1", "cozy apartment district 1", "COZY APT - DISTRICT 1".
- Missing values nghiêm trọng: ~15% listings thiếu giá (price = null), ~25% thiếu review scores, ~8% thiếu thông tin số phòng ngủ. Dashboard sẽ sai lệch nếu không xử lý.
- Formatting errors đa quốc gia: Giá phòng nhập đủ kiểu — "
", "2,500,000 VND", "2.500.000 VNĐ". Ngày available dùng format MM/DD/YYYY (Mỹ) lẫn DD/MM/YYYY (châu Âu/Việt Nam). - Typos và inconsistency trong text: Loại phòng: "Entire home/apt" vs "entire home" vs "Entire Home" vs "Whole apartment". Khu vực: "Quận 1" vs "Q1" vs "District 1" vs "quan 1".
- Outliers trong giá: Một số listing có giá $0/đêm (host quên cập nhật) hoặc $99,999/đêm (host đặt giá ảo để tạm ẩn listing). Trung bình giá toàn cầu ~$150/đêm nhưng range từ $0 đến $100,000+.
- Dữ liệu không ở dạng Tidy: Cột amenities chứa danh sách dạng
{TV, Wifi, Kitchen, "Air conditioning"}— gộp nhiều biến vào 1 cột, vi phạm nguyên tắc Tidy Data.
🛠️ Giải pháp
Dữ liệu gì?
- Listing data: listing_id, name, host_id, neighbourhood, room_type, price, minimum_nights, number_of_reviews, availability_365
- Review data: reviewer_id, date, comments, rating scores (accuracy, cleanliness, checkin, communication, location, value)
- Calendar data: listing_id, date, available (t/f), price per night
- Tổng: ~1 triệu dòng listing × 16 cột chính + 12 triệu dòng review + 365 triệu dòng calendar
Phân tích bằng gì?
Bước 1: Xử lý Duplicate (Remove Duplicates + hàm kết hợp)
Airbnb data team dùng quy trình 2 lớp:
- Exact duplicates: Dùng tính năng Remove Duplicates trong Excel dựa trên cột (host_id + latitude + longitude + room_type) → loại bỏ ~18,000 dòng trùng hoàn toàn.
- Fuzzy duplicates: Hai listing có tên gần giống, cùng host, cùng khu vực nhưng viết khác:
- Chuẩn hóa:
=TRIM(CLEAN(LOWER(A2)))để đưa tất cả về lowercase, bỏ khoảng trắng thừa - So sánh: dùng
=EXACT()sau chuẩn hóa → phát hiện thêm ~12,000 fuzzy duplicates
- Chuẩn hóa:
Bước 2: Xử lý Missing Values (IFERROR + IF + ISBLANK)
| Cột bị missing | % missing | Cách xử lý |
|---|---|---|
| price | 15% | =IF(ISBLANK(E2), MEDIAN(E:E), E2) → điền median giá theo khu vực |
| review_scores_rating | 25% | =IFERROR(F2, "No reviews") → gắn nhãn riêng, không điền số giả |
| bedrooms | 8% | =IF(ISBLANK(G2), 1, G2) → mặc định 1 phòng ngủ (assumption có ghi chú) |
| neighbourhood | 2% | Tra cứu ngược từ latitude/longitude qua bảng mapping |
Bước 3: Chuẩn hóa Format (SUBSTITUTE + TEXT + VALUE)
Xử lý cột price đa format:
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "$", ""), ",", ""), " USD", ""))→ Loại bỏ ký tự "$", ",", " USD" → chuyển text thành số.
Chuẩn hóa ngày:
=TEXT(DATEVALUE(A2), "YYYY-MM-DD")→ Đưa tất cả về format ISO 8601.
Bước 4: Xử lý Typos & Inconsistency (PROPER + SUBSTITUTE + VLOOKUP)
Tạo bảng mapping chuẩn hóa:
| Giá trị gốc | Giá trị chuẩn |
|---|---|
| "Entire home/apt" | "Entire home" |
| "entire home" | "Entire home" |
| "Whole apartment" | "Entire home" |
| "Private room" | "Private room" |
| "private rm" | "Private room" |
| "Quận 1" | "District 1" |
| "Q1" | "District 1" |
| "quan 1" | "District 1" |
Dùng =VLOOKUP(TRIM(LOWER(C2)), mapping_table, 2, FALSE) để tự động chuẩn hóa.
Bước 5: Xử lý Outliers (IQR Method)
Phân tích giá phòng theo room_type:
| Metric | Entire home | Private room | Shared room |
|---|---|---|---|
| Q1 | $65 | $30 | $15 |
| Median | $120 | $55 | $28 |
| Q3 | $200 | $85 | $45 |
| IQR | $135 | $55 | $30 |
| Upper bound (Q3 + 1.5×IQR) | $402.50 | $167.50 | $90 |
| Lower bound (Q1 - 1.5×IQR) | $0 → $1 | $0 → $1 | $0 → $1 |
→ Listings có giá > upper bound hoặc = $0 → flag là outlier → loại khỏi dashboard trung bình, giữ riêng để phân tích.
Bước 6: Chuyển đổi Tidy Data + Pivot Table
Cột amenities dạng {TV, Wifi, Kitchen} → unpivot thành dạng tidy:
| listing_id | amenity |
|---|---|
| 12345 | TV |
| 12345 | Wifi |
| 12345 | Kitchen |
Sau đó dùng Pivot Table để tạo dashboard:
- Rows: neighbourhood
- Columns: room_type
- Values: AVERAGE(price), COUNT(listing_id), AVERAGE(review_scores_rating)
- Filters: minimum_nights, availability
→ Dashboard hiển thị giá trung bình theo khu vực × loại phòng, số lượng listing, điểm review — tất cả từ dữ liệu đã clean.
📊 Kết quả
| Chỉ số | Trước cleaning | Sau cleaning |
|---|---|---|
| Tổng listings | 1,000,000 | 952,000 (loại 48K duplicate + outlier) |
| Missing price | 15% dòng | 0% (đã xử lý) |
| Format giá thống nhất | 6+ format khác nhau | 1 format chuẩn (số, USD) |
| Room type categories | 12 biến thể | 4 categories chuẩn |
| Neighbourhood inconsistency | 340+ cách viết | 180 khu vực chuẩn hóa |
| Dashboard accuracy | Sai lệch 20–35% do dirty data | ±3% so với cross-validation |
| Thời gian tạo pricing report | 2–3 ngày (manual) | 15 phút (Pivot Table refresh) |
Host sử dụng pricing dashboard đã tăng booking rate trung bình 12–18% nhờ định giá phù hợp thị trường. Airbnb ước tính data cleaning project này giúp giảm $2.3 triệu/năm chi phí customer support do listing sai giá.
💡 Bài học cho Data Analyst Việt Nam
- User-generated data luôn bẩn — hãy chuẩn bị tâm lý. Dữ liệu từ khách hàng tự nhập (form đăng ký, khảo sát, review) ở Việt Nam còn "bẩn" hơn — dấu tiếng Việt lẫn lộn, viết tắt đặc thù ("Q.1", "P.BT", "TP.HCM"). Mapping table là công cụ không thể thiếu.
- Kết hợp hàm nested formula là kỹ năng bắt buộc.
=TRIM(CLEAN(PROPER(A2)))— một công thức xử lý 3 loại lỗi cùng lúc. Đừng sửa từng lỗi riêng lẻ. - Pivot Table biến 1 triệu dòng dữ liệu thành insight trong 15 phút. Trước khi nghĩ đến Python hay Tableau, hãy master Pivot Table — nó giải quyết 80% nhu cầu phân tích tổng hợp hàng ngày.
- Document mọi assumption khi xử lý missing data. Khi điền median cho giá bị thiếu, hãy ghi chú rõ — stakeholder cần biết bạn đã "giả định" gì. Transparency builds trust.
Case Study 2: Công ty FMCG Việt Nam — Chuẩn hóa data bán hàng từ 50 nhà phân phối khác format
🏷️ Thông tin
| Tiêu chí | Chi tiết |
|---|---|
| Công ty | Một công ty FMCG hàng đầu Việt Nam (ngành hàng tiêu dùng nhanh — thực phẩm, đồ uống, chăm sóc cá nhân) |
| Ngành | FMCG (Fast-Moving Consumer Goods) |
| Quy mô | ~5,000 nhân viên, doanh thu ~8,000 tỷ VNĐ/năm, phân phối qua 50 nhà phân phối (NPP) tại 63 tỉnh thành |
| Thời điểm | Dự án chuẩn hóa dữ liệu bán hàng toàn quốc, 2024–2025 |
| Dữ liệu | Structured data — Quantitative (doanh số, sản lượng, giá bán) + Qualitative (tên sản phẩm, khu vực, kênh bán) |
📋 Bối cảnh
Công ty FMCG này phân phối sản phẩm qua mạng lưới 50 nhà phân phối (NPP) trải dài từ Lạng Sơn đến Cà Mau. Mỗi NPP sử dụng hệ thống quản lý bán hàng riêng — có NPP dùng phần mềm DMS (Distribution Management System), có NPP dùng Excel tự quản lý, có NPP còn ghi sổ tay rồi nhập lại cuối tháng. Mỗi tháng, 50 NPP gửi báo cáo bán hàng về trụ sở — và đây là lúc "ác mộng dữ liệu" bắt đầu.
Team Data Analytics tại trụ sở nhận được 50 file Excel khác format mỗi tháng: cột khác tên, thứ tự khác nhau, mã sản phẩm ghi khác nhau, ngày tháng format khác nhau, đơn vị tính lẫn lộn (thùng vs lon vs chai). Nhiệm vụ: gộp 50 file thành 1 bộ dữ liệu sạch để tạo báo cáo doanh số toàn quốc và Pivot Table phân tích đa chiều.
Đây là bài toán Data Cleaning + Tidy Data kinh điển mà hầu hết DA tại các công ty FMCG, dược phẩm, phân phối ở Việt Nam đều gặp phải.
⚡ Thách thức
Inconsistency cột dữ liệu giữa 50 NPP: NPP Hà Nội đặt tên cột "Mã SP", NPP Đà Nẵng ghi "Ma_SanPham", NPP TP.HCM ghi "Product Code". Có NPP để cột doanh số trước cột sản lượng, NPP khác ngược lại. Tổng cộng 23 biến thể tên cột cho cùng 8 trường dữ liệu.
Formatting errors dày đặc:
- Ngày: "15/01/2025" vs "2025-01-15" vs "15-Jan-25" vs "ngày 15 tháng 1"
- Số: "1,500,000" vs "1.500.000" vs "1500000" (dấu phân cách hàng nghìn Mỹ vs VN)
- Doanh số: có NPP ghi đơn vị triệu VNĐ (viết 1.5 tức 1,500,000), có NPP ghi đầy đủ số
Inconsistency mã sản phẩm: Sản phẩm "Nước suối 500ml" có mã "NS500" ở NPP A, "NS-500" ở NPP B, "NUOC_SUOI_500ML" ở NPP C, "SKU00125" ở NPP D. Tổng cộng 450 SKU nhưng ghi nhận thành hơn 1,200 biến thể do cách mã hóa khác nhau.
Missing data không đồng đều: NPP nhỏ ở nông thôn thường thiếu dữ liệu kênh bán hàng (GT — General Trade vs MT — Modern Trade), thiếu mã khách hàng. Tổng ~12% dòng thiếu ít nhất 1 trường quan trọng.
Dữ liệu không Tidy: Nhiều NPP gửi file dạng wide format — mỗi tháng 1 cột:
Mã SP Tên SP T1/2025 T2/2025 T3/2025 NS500 Nước suối 500ml 15,000 18,000 12,000 → Vi phạm Tidy Data: biến "tháng" bị spread ra nhiều cột thay vì là 1 cột riêng.
Duplicate do gửi lại: Một số NPP gửi file sửa lại sau khi đã gửi lần 1 → dữ liệu tháng đó bị đếm 2 lần nếu không loại bỏ bản cũ.
🛠️ Giải pháp
Dữ liệu gì?
- Sell-out data: Doanh số bán ra từ NPP đến điểm bán lẻ (retail outlets) — ~2 triệu dòng/tháng
- Master data: Danh mục sản phẩm (450 SKU), danh sách NPP (50), danh sách khu vực (63 tỉnh, 7 vùng)
- Kênh bán hàng: GT (General Trade — tạp hóa, chợ), MT (Modern Trade — siêu thị, chuỗi cửa hàng tiện lợi)
- Tổng: 50 file × trung bình 40,000 dòng/file = ~2 triệu dòng cần gộp và clean mỗi tháng
Phân tích bằng gì?
Bước 1: Chuẩn hóa cấu trúc cột (Template chuẩn + VLOOKUP mapping)
Team DA tạo template chuẩn gồm 8 cột theo đúng thứ tự:
| # | Tên cột chuẩn | Kiểu dữ liệu | Format |
|---|---|---|---|
| 1 | NPP_Code | Text | "NPP-HN01" |
| 2 | Date | Date | YYYY-MM-DD |
| 3 | SKU_Code | Text | "SKU00125" |
| 4 | Product_Name | Text | PROPER case |
| 5 | Channel | Text | "GT" hoặc "MT" |
| 6 | Quantity_Units | Number | Quy đổi về đơn vị nhỏ nhất (chai/lon) |
| 7 | Revenue_VND | Number | Số nguyên, đơn vị VNĐ |
| 8 | Region | Text | Tên vùng chuẩn ("Bắc Bộ", "Nam Bộ"…) |
Tạo bảng mapping tên cột để nhận diện tự động:
| Tên cột gốc (biến thể) | Tên cột chuẩn |
|---|---|
| "Mã SP", "Ma_SanPham", "Product Code", "MaSP" | SKU_Code |
| "Doanh số", "Revenue", "Doanh_So", "DoanhThu" | Revenue_VND |
| "Ngày bán", "Sale Date", "Ngay", "DATE" | Date |
Bước 2: Chuẩn hóa mã sản phẩm (SUBSTITUTE + VLOOKUP + Master Data)
=VLOOKUP(TRIM(UPPER(SUBSTITUTE(SUBSTITUTE(C2, "-", ""), "_", ""))), master_sku_table, 2, FALSE)Quy trình:
SUBSTITUTEloại bỏ dấu "-", "_" trong mã → "NS-500" thành "NS500"UPPER+TRIMchuẩn hóa formatVLOOKUPtra cứu bảng master SKU để lấy mã chuẩn chính thức
Kết quả: 1,200 biến thể → 450 SKU chuẩn.
Bước 3: Chuẩn hóa số liệu (VALUE + SUBSTITUTE)
Xử lý dấu phân cách:
=VALUE(SUBSTITUTE(SUBSTITUTE(E2, ".", ""), ",", ""))→ "1.500.000" (format VN) → 1500000 → "1,500,000" (format US) → 1500000
Quy đổi đơn vị: NPP ghi "thùng" (24 chai/thùng) → nhân 24 để quy về chai.
Bước 4: Chuyển Wide → Long format (Unpivot)
Dùng kỹ thuật unpivot trong Excel (Power Query hoặc công thức):
Trước (Wide — KHÔNG tidy):
| Mã SP | T1/2025 | T2/2025 | T3/2025 |
|---|---|---|---|
| SKU001 | 15,000 | 18,000 | 12,000 |
Sau (Long — Tidy):
| SKU_Code | Date | Quantity_Units |
|---|---|---|
| SKU001 | 2025-01-31 | 15,000 |
| SKU001 | 2025-02-28 | 18,000 |
| SKU001 | 2025-03-31 | 12,000 |
→ Tuân thủ Tidy Data: mỗi biến 1 cột, mỗi quan sát 1 dòng.
Bước 5: Xử lý Duplicate + Missing
- Duplicate: Sau khi gộp file, dùng Remove Duplicates theo (NPP_Code + Date + SKU_Code) → loại bỏ ~3,500 dòng trùng do NPP gửi file 2 lần.
- Missing channel:
=IF(ISBLANK(E2), "GT", E2)→ mặc định GT cho NPP nông thôn (80% doanh số GT, assumption được xác nhận bởi sales team). - Missing revenue:
=IFERROR(F2, G2*H2)→ nếu doanh số trống, tính = sản lượng × giá bán chuẩn.
Bước 6: Pivot Table phân tích đa chiều
Sau khi dữ liệu sạch và ở dạng tidy, tạo Pivot Table:
| Pivot Table | Rows | Columns | Values | Insight |
|---|---|---|---|---|
| PT1: Doanh số theo vùng | Region | Month | SUM(Revenue_VND) | Vùng nào tăng trưởng, vùng nào giảm |
| PT2: Top SKU | Product_Name | Channel (GT/MT) | SUM(Quantity_Units) | Sản phẩm nào bán chạy ở kênh nào |
| PT3: Hiệu suất NPP | NPP_Code | Month | SUM(Revenue_VND), COUNT(SKU_Code) | NPP nào đạt/không đạt target |
| PT4: Seasonal trend | Month | Product_Name | SUM(Quantity_Units) | Sản phẩm nào có tính mùa vụ |
Áp dụng Data Quality Dimensions để đánh giá:
| Dimension | Trước cleaning | Sau cleaning |
|---|---|---|
| Accuracy | Sai đơn vị, sai dấu phân cách → doanh số lệch 10–40% | ±1% so với đối chiếu hệ thống DMS |
| Completeness | 12% dòng thiếu dữ liệu | <1% missing (đã xử lý hoặc gắn cờ) |
| Consistency | 1,200 biến thể SKU, 23 biến thể tên cột | 450 SKU, 8 cột chuẩn |
| Timeliness | Báo cáo trễ 10–15 ngày do clean thủ công | 3 ngày sau khi nhận file |
| Validity | Doanh số âm, ngày tương lai | 100% pass validation rules |
📊 Kết quả
| Chỉ số | Trước | Sau |
|---|---|---|
| Thời gian gộp & clean 50 file/tháng | 10–15 ngày làm việc (2 người) | 2–3 ngày (1 người + template) |
| Số biến thể SKU | 1,200+ | 450 (đúng master data) |
| Accuracy báo cáo doanh số | Sai lệch 10–40% giữa các vùng | ±1% |
| Số Pivot Table phân tích | 0 (báo cáo thủ công) | 8 Pivot Tables cập nhật hàng tháng |
| Thời gian ra quyết định phân bổ hàng | 3–4 tuần | 1 tuần |
| Phát hiện NPP báo số sai | Không phát hiện được | Phát hiện 3 NPP báo doanh số chênh >15% so với xuất kho |
Nhờ dữ liệu sạch và Pivot Table, ban lãnh đạo phát hiện vùng Tây Nam Bộ giảm 22% doanh số Q2/2025 — nguyên nhân: 2 NPP lớn chuyển sang phân phối cho đối thủ. Quyết định: tuyển NPP thay thế trong 1 tháng, hạn chế mất thị phần.
💡 Bài học cho Data Analyst Việt Nam
- Template chuẩn là "vắc-xin" chống dirty data. Thay vì dọn rác mỗi tháng, hãy tạo template chuẩn và yêu cầu NPP/đối tác gửi theo format đó. Phòng bệnh hơn chữa bệnh — Data Validation ngay từ đầu vào.
- Master data là tài sản quý hơn transaction data. Bảng mapping SKU, khu vực, NPP — nếu không có bảng này, bạn sẽ mãi clean không xong. Hãy đầu tư thời gian xây master data trước khi phân tích.
- Wide → Long (unpivot) là kỹ năng bắt buộc. 80% file Excel trong doanh nghiệp Việt Nam ở dạng wide format. Biết chuyển sang long/tidy format là điều kiện tiên quyết để dùng Pivot Table hiệu quả.
- FMCG Việt Nam là "đấu trường" data cleaning thực chiến. Nếu bạn là DA mới, xin vào team data ở một công ty FMCG hoặc phân phối — bạn sẽ giỏi data cleaning hơn bất kỳ khóa học nào sau 3 tháng thực tế.
Case Study 3: Ngân hàng VN — Data Cleaning cho dữ liệu KYC khách hàng
🏷️ Thông tin
| Tiêu chí | Chi tiết |
|---|---|
| Công ty | Một ngân hàng thương mại cổ phần top 10 Việt Nam |
| Ngành | Banking / Financial Services |
| Quy mô | ~15,000 nhân viên, 500+ chi nhánh/PGD, ~8 triệu khách hàng cá nhân |
| Thời điểm | Dự án chuẩn hóa dữ liệu KYC, 2024–2025 (đáp ứng quy định NHNN về CDD/EDD) |
| Dữ liệu | Structured data — chủ yếu Qualitative (tên, địa chỉ, giấy tờ tùy thân) + Quantitative (thu nhập, số dư) |
📋 Bối cảnh
KYC (Know Your Customer) là quy trình bắt buộc theo quy định của Ngân hàng Nhà nước Việt Nam (NHNN) — mỗi ngân hàng phải thu thập, xác minh và lưu trữ thông tin nhận dạng khách hàng: họ tên, ngày sinh, CMND/CCCD, địa chỉ, nghề nghiệp, mục đích giao dịch. Thông tư 09/2023/TT-NHNN và các quy định về phòng chống rửa tiền (AML — Anti-Money Laundering) yêu cầu dữ liệu KYC phải chính xác, đầy đủ, và cập nhật.
Ngân hàng này có lịch sử hoạt động 25+ năm, dữ liệu khách hàng được nhập bởi hàng nghìn nhân viên giao dịch (teller) tại 500+ chi nhánh — mỗi người một cách nhập. Năm 2024, khi ngân hàng triển khai dự án chuyển đổi số (digital transformation), team Data Analytics phát hiện dữ liệu KYC trong core banking system (T24/Flexcube) có chất lượng rất thấp — ảnh hưởng trực tiếp đến khả năng chấm điểm tín dụng (credit scoring), mở tài khoản online (eKYC), và tuân thủ quy định NHNN.
⚡ Thách thức
- Duplicate hồ sơ khách hàng: ~12% khách hàng (gần 1 triệu hồ sơ) bị trùng lặp — cùng 1 người có 2–3 CIF (Customer Information File) khác nhau do mở tài khoản ở nhiều chi nhánh. Ví dụ: "Nguyễn Thị Mai Hương" ở chi nhánh Hoàn Kiếm có CIF khác với "NGUYEN THI MAI HUONG" ở chi nhánh Đống Đa.
- Missing data nghiêm trọng:
- 18% hồ sơ thiếu số CCCD (khách mở tài khoản trước năm 2021, khi chưa đổi CCCD gắn chip)
- 25% thiếu email (khách hàng lớn tuổi, nông thôn)
- 8% thiếu nghề nghiệp hoặc ghi chung chung "Tự do"
- 5% thiếu địa chỉ đầy đủ
- Formatting errors nghiêm trọng trong tên:
- Có dấu vs không dấu: "Nguyễn Văn An" vs "Nguyen Van An"
- Viết hoa toàn bộ vs viết thường: "TRẦN THỊ BÌNH" vs "trần thị bình"
- Khoảng trắng thừa: "Lê Văn Cường" (2–3 khoảng trắng giữa các từ)
- Ký tự đặc biệt: "Ng.V.An" (viết tắt), "Nguyễn Văn An (cũ: Nguyễn Văn X)"
- Inconsistency địa chỉ: Cùng một địa chỉ nhưng ghi 10+ cách khác nhau: "123 Nguyễn Huệ, Q.1, TP.HCM" vs "123 Nguyen Hue, Quan 1, Ho Chi Minh" vs "123 Ng. Huệ, Quận 1, TPHCM" vs "123 đường Nguyễn Huệ, phường Bến Nghé, quận 1, thành phố Hồ Chí Minh".
- Validity — CMND/CCCD không hợp lệ:
- CMND 9 số (cũ) lẫn với CCCD 12 số (mới)
- Số CMND không đúng format: "123 456 789" (có khoảng trắng), "CMND: 123456789" (có text)
- Một số hồ sơ CMND đã hết hạn 5–10 năm chưa cập nhật
- Dữ liệu nhạy cảm (PII): Dữ liệu KYC chứa thông tin cá nhân (Personally Identifiable Information) — mọi thao tác cleaning phải tuân thủ quy định bảo mật, không export ra ngoài hệ thống, audit log mọi thay đổi.
🛠️ Giải pháp
Dữ liệu gì?
- Customer master data: CIF, họ tên, ngày sinh, giới tính, CMND/CCCD, địa chỉ thường trú, địa chỉ tạm trú, số điện thoại, email
- KYC supplementary data: Nghề nghiệp, thu nhập kê khai, mục đích mở tài khoản, người đại diện pháp lý (nếu có)
- Transaction history: Để cross-check thông tin — xác minh khách hàng active vs inactive
- Tổng: ~8 triệu hồ sơ CIF × 22 trường dữ liệu = ~176 triệu giá trị cần kiểm tra
Phân tích bằng gì?
Bước 1: Xử lý Duplicate CIF (TRIM + CLEAN + LOWER + CONCATENATE)
Quy trình phát hiện duplicate 3 lớp:
Lớp 1 — Exact match: Dùng Remove Duplicates theo CCCD → phát hiện ~320,000 hồ sơ trùng CCCD chính xác.
Lớp 2 — Fuzzy match tên:
=TRIM(CLEAN(LOWER(SUBSTITUTE(B2, " ", " "))))→ Chuẩn hóa tên: bỏ ký tự ẩn, bỏ khoảng trắng thừa, về lowercase → "NGUYEN THI MAI HUONG" và "Nguyễn Thị Mai Hương" → so sánh sau khi loại dấu → Dùng hàm =SUBSTITUTE chuỗi để loại dấu tiếng Việt: ă→a, ơ→o, ư→u…
Lớp 3 — Cross-check: Khớp (tên chuẩn hóa + ngày sinh + số điện thoại) → xác nhận duplicate → merge records, giữ CIF có nhiều giao dịch hơn.
Kết quả: ~960,000 hồ sơ duplicate được phát hiện → merge thành ~7.04 triệu CIF duy nhất.
Bước 2: Chuẩn hóa CMND/CCCD (SUBSTITUTE + LEN + IF)
=IF(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2, " ", ""), "CMND:", ""), "CCCD:", ""))=9,
"CMND",
IF(LEN(...)=12, "CCCD", "INVALID"))| Tình huống | Input | Xử lý | Output |
|---|---|---|---|
| Format chuẩn | "012345678901" | Giữ nguyên | CCCD, 12 số |
| Có khoảng trắng | "012 345 678 901" | SUBSTITUTE(D2," ","") | "012345678901" |
| Có text | "CCCD: 012345678901" | SUBSTITUTE(D2,"CCCD: ","") | "012345678901" |
| CMND cũ | "123456789" | Flag cần cập nhật | CMND 9 số, cần đổi CCCD |
| Không hợp lệ | "ABC123" | Flag INVALID | Cần nhân viên kiểm tra lại |
Bước 3: Chuẩn hóa địa chỉ (SUBSTITUTE + VLOOKUP + Mapping Table)
Tạo bảng mapping chuẩn hóa địa chỉ Việt Nam:
| Viết tắt / Biến thể | Chuẩn hóa |
|---|---|
| "Q.1", "Q1", "Quan 1", "quận 1" | "Quận 1" |
| "TP.HCM", "TPHCM", "Ho Chi Minh", "Sài Gòn", "tp.hcm" | "TP. Hồ Chí Minh" |
| "P.", "Phuong", "phường" | "Phường" |
| "Đ.", "đường", "Duong" | "Đường" |
| "TX.", "Thi xa" | "Thị xã" |
Dùng nested SUBSTITUTE + VLOOKUP theo bảng mapping hành chính 4 cấp (tỉnh/huyện/xã/thôn) — bảng mapping dựa trên dữ liệu hành chính của Tổng cục Thống kê (63 tỉnh, 705 huyện, 10,599 xã).
Bước 4: Xử lý Missing Data (IF + ISBLANK + Data Validation)
| Trường | % Missing | Xử lý | Lý do |
|---|---|---|---|
| CCCD | 18% | Flag "CẦN CẬP NHẬT" + gửi danh sách cho chi nhánh | Quy định NHNN bắt buộc, không thể giả định |
| 25% | =IF(ISBLANK(H2), "CHƯA CÓ", H2) + không yêu cầu bắt buộc | Không ảnh hưởng pháp lý | |
| Nghề nghiệp | 8% | =IF(OR(ISBLANK(I2), I2="Tự do"), "CẦN XÁC MINH", I2) | "Tự do" quá chung, cần cụ thể cho credit scoring |
| Địa chỉ | 5% | Flag + cross-check với địa chỉ trên CCCD | Bắt buộc theo KYC |
Đặc biệt: Không điền giá trị giả cho dữ liệu KYC — khác với Airbnb (điền median giá), dữ liệu ngân hàng không được phép "bịa" vì liên quan pháp lý. Missing data chỉ được flag và yêu cầu cập nhật.
Bước 5: Data Validation Rules (ngăn dirty data tương lai)
Thiết lập Data Validation trong hệ thống nhập liệu:
| Rule | Áp dụng cho | Ví dụ |
|---|---|---|
| Length check | CCCD phải đúng 12 ký tự số | =AND(LEN(D2)=12, ISNUMBER(VALUE(D2))) |
| Format check | Email phải chứa "@" và "." | =AND(ISNUMBER(FIND("@",H2)), ISNUMBER(FIND(".",H2))) |
| Range check | Ngày sinh: 1900 < năm < 2010 | =AND(YEAR(C2)>1900, YEAR(C2)<2010) |
| List check | Giới tính chỉ: "Nam", "Nữ" | Drop-down list Data Validation |
| Consistency check | Nếu CCCD bắt đầu bằng "001" → tỉnh phải là "Hà Nội" | =IF(LEFT(D2,3)="001", F2="Hà Nội", TRUE) |
Bước 6: Pivot Table cho báo cáo chất lượng dữ liệu
Tạo Data Quality Dashboard bằng Pivot Table:
| Pivot Table | Rows | Values | Mục đích |
|---|---|---|---|
| PT1: Missing by Branch | Branch_Code | COUNT of BLANK fields | Chi nhánh nào dữ liệu kém nhất |
| PT2: Duplicate by Region | Region | COUNT of duplicate CIFs | Khu vực nào nhiều trùng lặp |
| PT3: CCCD Status | Status (Cập nhật/Chưa/Hết hạn) | COUNT of customers | Tổng quan tình trạng CCCD |
| PT4: Monthly Trend | Month_Updated | % completeness | Chất lượng dữ liệu cải thiện theo thời gian? |
📊 Kết quả
| Chỉ số | Trước cleaning | Sau cleaning |
|---|---|---|
| Tổng hồ sơ CIF | 8,000,000 | 7,040,000 (loại 960K duplicate) |
| Duplicate rate | ~12% | <0.5% (sau merge) |
| Missing CCCD | 18% | 4% (14% đã cập nhật qua chiến dịch gọi KH) |
| Formatting errors trong tên | ~35% hồ sơ | <2% |
| Địa chỉ chuẩn hóa | ~40% có inconsistency | 95% theo chuẩn hành chính |
| CMND/CCCD valid | 75% | 96% |
| Thời gian mở tài khoản eKYC | Fail 30% do data không match | Fail <5% |
| Credit scoring accuracy | Sai lệch do duplicate/missing | Cải thiện 15–20% accuracy |
Ngân hàng ước tính dự án data cleaning KYC tiết kiệm ~45 tỷ VNĐ/năm nhờ: giảm 80% thời gian đối soát dữ liệu thủ công, giảm 25% tỷ lệ từ chối eKYC, giảm rủi ro phạt từ NHNN do dữ liệu không tuân thủ (mức phạt có thể lên đến 1–2 tỷ VNĐ/lần vi phạm).
💡 Bài học cho Data Analyst Việt Nam
- Dữ liệu ngân hàng KHÔNG được "bịa" missing values. Khác với data thương mại (điền median, mean), dữ liệu KYC liên quan pháp lý — bạn chỉ được flag và yêu cầu cập nhật. Hiểu context trước khi clean.
- Địa chỉ Việt Nam là "boss cuối" của data cleaning. 63 tỉnh × hàng nghìn biến thể viết tắt, có dấu/không dấu, cũ/mới (Sài Gòn vs TP.HCM, Hà Tây vs Hà Nội) — xây bảng mapping hành chính chuẩn là đầu tư xứng đáng nhất.
- Data Validation "phòng bệnh hơn chữa bệnh". Thay vì clean 8 triệu dòng hồi tố, hãy thiết lập validation rules ngay tại điểm nhập liệu. Mỗi rule đơn giản (length check, format check) ngăn chặn hàng triệu lỗi tương lai.
- Pivot Table là công cụ báo cáo chất lượng dữ liệu. Không chỉ phân tích kinh doanh — dùng Pivot Table để theo dõi % completeness, duplicate rate theo chi nhánh, tháng → biến data quality thành KPI đo lường được.
📝 So sánh & tổng hợp
| Tiêu chí | Airbnb | FMCG Việt Nam | Ngân hàng VN |
|---|---|---|---|
| Ngành | Hospitality / Marketplace | FMCG / Phân phối | Banking / Financial Services |
| Quy mô dữ liệu | ~1 triệu listings | ~2 triệu dòng/tháng (50 files) | ~8 triệu hồ sơ KYC |
| Nguồn dirty data chính | User-generated content (host tự nhập) | 50 NPP dùng format khác nhau | Nhân viên giao dịch tại 500+ chi nhánh |
| Loại dirty data nổi bật | Cả 6 loại, đặc biệt Inconsistency + Outlier | Formatting + Inconsistency + Not Tidy | Duplicate + Missing + Validity |
| Hàm Excel chính | TRIM, CLEAN, LOWER, SUBSTITUTE, VLOOKUP, VALUE | SUBSTITUTE, VLOOKUP, VALUE, PROPER | TRIM, CLEAN, SUBSTITUTE, LEN, IF, ISBLANK |
| Tidy Data | Unpivot cột amenities | Wide → Long format (tháng → dòng) | Đã ở dạng long, cần chuẩn hóa giá trị |
| Pivot Table | Pricing dashboard (khu vực × loại phòng) | Doanh số đa chiều (vùng × kênh × SKU) | Data Quality dashboard (chi nhánh × loại lỗi) |
| Data Quality focus | Accuracy + Consistency | Consistency + Completeness + Timeliness | Completeness + Validity + Accuracy |
| Xử lý missing | Điền median/default + ghi chú assumption | Tính từ dữ liệu có sẵn (sản lượng × giá) | Flag + yêu cầu cập nhật (KHÔNG điền giả) |
| Kết quả nổi bật | +12–18% booking rate, -$2.3M support cost | Giảm thời gian report từ 15 ngày → 3 ngày | Tiết kiệm ~45 tỷ VNĐ/năm, eKYC fail giảm 83% |
| Bài học lớn nhất | Nested formula + Mapping table | Template chuẩn + Master data | Context quyết định cách clean + Data Validation |
Điểm chung của cả 3 case study:
- Đều bắt đầu bằng nhận diện loại dirty data trước khi chọn phương pháp xử lý — giống bác sĩ chẩn đoán trước khi kê thuốc.
- Đều sử dụng bảng mapping/master data làm nền tảng chuẩn hóa — bạn không thể clean nếu không biết "chuẩn" là gì.
- Đều chứng minh Pivot Table là công cụ phân tích mạnh nhất sau khi dữ liệu đã sạch — nhưng vô dụng nếu dữ liệu vẫn bẩn.
- Đều cho thấy Data Cleaning không phải chi phí — mà là đầu tư với ROI đo lường được (tiết kiệm tiền, thời gian, tăng chất lượng quyết định).
- Đều nhấn mạnh nguyên tắc "Garbage in, garbage out" — và giải pháp không chỉ là clean hồi tố mà còn là phòng ngừa bằng validation ngay từ đầu.
🤔 Câu hỏi thảo luận
Missing data — Khi nào điền, khi nào flag? Trong case Airbnb, team điền median giá cho listings thiếu giá. Trong case Ngân hàng, missing CCCD chỉ được flag, KHÔNG điền giá trị giả. Theo bạn, tiêu chí nào giúp DA quyết định khi nào nên điền giá trị thay thế (imputation) và khi nào chỉ nên flag? Hãy cho ví dụ từ bối cảnh công ty Việt Nam.
Bạn nhận được file Excel từ 3 phòng ban khác nhau, mỗi file viết tên thành phố khác nhau: "Hà Nội", "Ha Noi", "HN", "Hanoi". Hãy viết hàm Excel kết hợp (nested formula) để chuẩn hóa tất cả về "Hà Nội". Gợi ý: kết hợp SUBSTITUTE, TRIM, PROPER hoặc VLOOKUP với mapping table.
Tidy Data thực hành: File báo cáo bán hàng hiện tại có format wide (mỗi tháng 1 cột). Nếu bạn cần tạo Pivot Table phân tích doanh số theo tháng × sản phẩm × khu vực, bạn sẽ chuyển file sang dạng long/tidy như thế nào trong Excel? Mô tả các bước cụ thể.
Data Quality Dimensions: Đánh giá chất lượng dữ liệu tại trường học / công ty của bạn theo 5 chiều (Accuracy, Completeness, Consistency, Timeliness, Validity). Chiều nào yếu nhất? Bạn sẽ cải thiện bằng cách nào dùng Excel?
Đạo đức dữ liệu trong Data Cleaning: Trong case Ngân hàng, dữ liệu KYC chứa thông tin cá nhân nhạy cảm (PII). Nếu bạn là DA được giao clean dữ liệu này, bạn cần tuân thủ những nguyên tắc bảo mật nào? Điều gì xảy ra nếu dữ liệu KYC bị leak trong quá trình cleaning?
📚 Nguồn tham khảo
- Inside Airbnb — insideairbnb.com — Dữ liệu Airbnb mã nguồn mở, lý tưởng để thực hành data cleaning.
- Wickham, H. (2014). "Tidy Data." Journal of Statistical Software, 59(10). — Bài báo gốc định nghĩa Tidy Data principles.
- IBM. (2023). "The Cost of Poor Data Quality." — Báo cáo về thiệt hại $3.1 trillion/năm do dirty data.
- Ngân hàng Nhà nước Việt Nam. Thông tư 09/2023/TT-NHNN về phòng, chống rửa tiền — quy định KYC/CDD/EDD.
- Redman, T. C. (2016). "Bad Data Costs the U.S. $3 Trillion Per Year." Harvard Business Review.
- Microsoft Support — "Create a PivotTable to analyze worksheet data." — Hướng dẫn chính thức Pivot Table.
- Tổng cục Thống kê Việt Nam — Danh mục đơn vị hành chính Việt Nam (63 tỉnh, 705 huyện, 10,599 xã).
- CafeBiz, VnExpress — Các bài viết về chuyển đổi số ngân hàng và data quality tại doanh nghiệp Việt Nam.
🔗 Xem thêm Buổi 3
→ 📘 Nội dung chính → 📝 Blog → 🏆 Tiêu chuẩn → 🛠 Workshop → 🎮 Mini Game