Dữ liệu xuất khẩu thương mại điện tử hầu hết đều lộn xộn. Một tệp có thể chứa nhiều biến thể SKU, thông tin khách hàng không nhất quán, trường địa chỉ kết hợp, giá trị trùng lặp hoặc giá trị rỗng và chi tiết về giá rải rác. Nếu bạn đã từng thử phân tích dữ liệu đó trong Excel hoặc Power BI, bạn sẽ biết mọi thứ trở nên phức tạp nhanh chóng như thế nào. Đây là lúc Power Query trở nên vô cùng hữu ích. Bất kể bạn đang sử dụng công cụ nào—Excel hay Power BI—Power Query giúp dọn dẹp và định hình lại dữ liệu thương mại điện tử thô mà không cần viết các công thức phức tạp.
Trong hướng dẫn này, chúng ta sẽ tìm hiểu 5 phép biến đổi Power Query cần thiết cho dữ liệu bán hàng thương mại điện tử, đặc biệt hữu ích cho tập dữ liệu cửa hàng trực tuyến.
Bỏ tổng hợp các cột biến thể SKU
Nhiều cửa hàng xuất khẩu thương mại điện tử đã đặt hàng sản phẩm trên nhiều cột, chẳng hạn như Stock_S, SKU 1, SKU 2, SKU 3, Variant 1, Variant 2 và Variant 3, mỗi cột chứa một số lượng. Cấu trúc này khó phân tích vì mỗi đơn hàng trải rộng chi tiết sản phẩm trên nhiều cột. Bảng tổng hợp, số đo DAX và công thức SUM không thể tổng hợp một cách hiệu quả trên định dạng rộng này. Việc hủy xoay vòng sẽ tạo ra một bảng dữ kiện dài, được chuẩn hóa, lý tưởng cho việc phân tích.
Các bước:
- Tải dữ liệu của bạn vào Power Query
- Chọn cột SKU (Kho S, M, L, XL)
- Đi tới Biến đổi tab>> chọn Bỏ xoay cột
- Power Query chuyển đổi các cột SKU này thành hai cột mới và đổi tên chúng:
- Thuộc tính: Kích thước
- Giá trị: Số lượng
Sử dụng phép chuyển đổi này khi quá trình xuất của bạn lưu trữ nhiều sản phẩm được đặt hàng trong các cột riêng biệt thay vì các hàng riêng biệt.
Chia chuỗi địa chỉ giao hàng thành nhiều trường
Khoảng 90% tệp thương mại điện tử thô lưu trữ toàn bộ địa chỉ giao hàng trong một chuỗi. Việc tách trường này sẽ cho phép báo cáo thuế cấp tiểu bang, tối ưu hóa chi phí vận chuyển, phân tích hiệu suất khu vực và lập bản đồ.
Các bước:
- Chọn Địa chỉ giao hàng cột
- Truy cập Trang chủ tab>> chọn Tách cột>> chọn Theo dấu phân cách
- Dựa trên dữ liệu của bạn, hãy sử dụng dấu phân cách như:
- Dấu phẩy
- Dấu nối
- Ngắt dòng
- Dấu phân cách tùy chỉnh
- Đối với dữ liệu hiện có, hãy chọn Dấu phẩy làm dấu phân cách
- Nhấp vào OK
- Đổi tên các cột kết quả thành các nhãn có ý nghĩa:Đường, Thành phố, Mã bưu điện và Quốc gia
Sau khi phân chia địa chỉ, bạn có thể phân tích đơn đặt hàng theo thành phố, hiệu suất giao hàng theo khu vực, doanh số bán hàng theo khu vực và khách hàng thường xuyên từ các địa điểm cụ thể. Điều này đặc biệt hữu ích cho các doanh nghiệp giao hàng địa phương và báo cáo doanh số theo khu vực.
Ghi chú nâng cao: Không phải tất cả các địa chỉ đều có cùng định dạng. Một số có thể có các bộ phận thừa, bộ phận còn thiếu hoặc chi tiết căn hộ. Trong những trường hợp này, bạn có thể:
- Cắt bớt khoảng trắng sau khi tách
- Hợp nhất các phần đã chọn lại với nhau
- Sử dụng Trích xuất văn bản trước/sau dấu phân cách
- Tạo cột tùy chỉnh để xử lý ngoại lệ
Cắt xén, làm sạch và chuẩn hóa các loại dữ liệu
Trong bộ dữ liệu thương mại điện tử lớn, tên khách hàng, SKU, địa chỉ email và danh mục sản phẩm thường chứa dấu cách ở đầu hoặc cuối, ký tự không in được và cách viết hoa không nhất quán. Các loại dữ liệu cũng có thể không khớp, chẳng hạn như các giá trị số được lưu dưới dạng văn bản.
Những vấn đề nhỏ này có thể gây ra những vấn đề nghiêm trọng. Dữ liệu giống nhau có thể xuất hiện dưới dạng các giá trị khác nhau, không thành công khi hợp nhất hoặc tạo các nhóm trùng lặp.
Xóa dữ liệu:
- Cắt bớt khoảng trắng:
- Chọn các cột văn bản
- Đi tới Chuyển đổi>> chọn Định dạng>> chọn Cắt tỉa
- Việc này sẽ loại bỏ các khoảng trắng thừa ở đầu và cuối
- Xóa các ký tự ẩn:
- Với các cột tương tự được chọn,
- Chọn Định dạng>> chọn Dọn dẹp
- Việc này sẽ loại bỏ các ký tự không in được
- Chuẩn hóa cách viết chữ:
- Đi tới Chuyển đổi>> chọn Định dạng>> chọn:
- Viết hoa từng từ để biết tên
- Chữ thường để nhận email nếu cần
Chuẩn hóa các loại dữ liệu:
- Chọn cột ngày
- Truy cập Trang chủ tab>> chọn Loại dữ liệu>> chọn Ngày
- Nếu cần, hãy thêm một cột:
- Đi tới Thêm cột tab>> mở rộng Ngày
- Chọn Năm , Tháng , Ngày , Quý , Ngày trong tuần
- Chọn các cột số như Số lượng và Giá
- Mở rộng biểu tượng tiêu đề cột>> chọn Số thập phân
- Hoặc đi tới phần Chuyển đổi tab>> chọn Loại dữ liệu>> chọn Số thập phân hoặc Số nguyên
Việc chuyển đổi này rất cần thiết vì nó làm cho các hoạt động nhóm, hợp nhất, lọc và tra cứu trở nên đáng tin cậy hơn nhiều.
Xóa trùng lặp và xử lý giá trị null
Null là phổ biến trong dữ liệu thương mại điện tử. Một số là vô hại, trong khi một số khác lại phá vỡ tính toán. Cổng thanh toán và quy trình đồng bộ hóa có thể tạo ID đơn hàng trùng lặp và số lượng hoặc giá bằng 0 có thể phá vỡ tổng số và hình ảnh. Nếu giá trị rỗng không được xử lý chính xác thì kết quả có thể bị sai lệch.
Xóa các bản sao:
- Chọn ID đơn hàng và Ngày đặt hàng làm khóa (điều này ngăn việc xóa các đơn đặt hàng lặp lại hợp pháp trong cùng ngày)
- Truy cập Trang chủ tab>> chọn Xóa nội dung trùng lặp
- Chỉ xóa các hàng thực sự bị hỏng:
- Truy cập Trang chủ tab>> chọn Xóa hàng trống (trước tiên lọc ra các hàng có OrderID rỗng)
Thay thế các giá trị:
- Nếu giảm giá trống có nghĩa là không giảm giá, hãy thay giá trị rỗng bằng 0
- Chọn tất cả các cột Số lượng và Giá cả
- Đi tới Biến đổi tab>> chọn Thay thế giá trị :
- Giá trị cần tìm:null
- Thay thế bằng:0
Xóa giá trị văn bản:
- Xóa các tiền tố không cần thiết như Stock_
- Chọn cột Kích thước
- Đi tới Biến đổi tab>> chọn Thay thế giá trị :
- Giá trị cần tìm:Kho_
- Thay thế bằng:(để trống)
Bây giờ các giá trị Kích thước trông rõ ràng và dễ đọc.
Điền các giá trị lặp lại:
- Đôi khi chỉ có hàng đầu tiên của đơn hàng chứa OrderID hoặc tên khách hàng
- Chọn cột
- Đi tới Chuyển đổi>> chọn Điền>> chọn Xuống
Thận trọng quan trọng: Đừng mù quáng thay thế mọi giá trị rỗng. Giá trị bị thiếu có thể cho biết “không áp dụng”, “không xác định” hoặc có vấn đề về dữ liệu. Luôn hiểu rõ ý nghĩa kinh doanh trước khi thay thế các giá trị.
Tạo cột tùy chỉnh được tính toán
Từ dữ liệu thương mại điện tử, bạn có thể lấy cột doanh thu hoặc lợi nhuận. Mặc dù điều này có thể được thực hiện bằng cách sử dụng các công thức Excel, nhưng những công thức đó sẽ bị gián đoạn khi làm mới. Các cột tùy chỉnh Power Query tự động tính toán lại và vẫn là một phần của quy trình ETL của bạn.
Doanh thu ròng:
- Đi tới Thêm cột tab>> chọn Cột tùy chỉnh
- Nhập tên
- Chèn công thức sau
[Unit_Price] * [Qty] * (1 - [Discount_Pct])
Tổng lợi nhuận:
[Net_Revenue] - ([Cost_Per_Unit] * [Qty])
Lợi nhuận_Pct (%):
if [Net_Revenue] = 0 then 0 else [Gross_Margin] / [Net_Revenue]
- Đặt loại dữ liệu một cách rõ ràng:Net_Revenue và Gross_Margin thành Tiền tệ , Margin_Pct đến Phần trăm
Quan trọng: Luôn bảo vệ các công thức chia. Số 0 trong mẫu số có thể gây ra lỗi trên toàn bộ cột trong Power Query và có thể dẫn đến việc bỏ hàng trong khi tải. Sử dụng mẫu nếu [X] =0 thì null nếu không … cho bất kỳ tỷ lệ được tính toán nào.
Nhóm và tổng hợp cho các bảng tóm tắt hiệu suất cao
Các tệp thương mại điện tử thô có hàng triệu hàng có thể làm chậm báo cáo. Việc nhóm tạo các bảng tổng hợp hiệu quả cho trang tổng quan trong khi vẫn giữ lại các truy vấn chi tiết để phân tích chi tiết.
Các bước:
- Đi tới Chuyển đổi>> chọn Nhóm theo
- Chọn Danh mục>> nhấp vào Thêm tổng hợp :
- Tổng của Net_Revenue
- Trung bình của số tiền ký quỹ_Pct
- Số lượng đơn hàng_ID (hàng)
- Đặt tên cho truy vấn mới Sales_Summary và giữ bản gốc là Sales_Detail để tìm hiểu sâu hơn
Mẹo chuyên nghiệp: Trong Power BI, hãy tắt tùy chọn “Bật tải” đối với truy vấn chi tiết và chỉ tải bảng tóm tắt và bảng thứ nguyên để cải thiện hiệu suất.
Bước cuối cùng:
- Truy cập Trang chủ tab>> chọn Đóng và áp dụng để tải dữ liệu
Kết luận
Bây giờ bạn có thể áp dụng năm chuyển đổi Power Query thiết yếu này cho dữ liệu bán hàng thương mại điện tử. Power Query là một trong những công cụ hiệu quả nhất để làm sạch các tập dữ liệu thương mại điện tử vì nó xử lý cả các phép biến đổi đơn giản và phức tạp theo cách có thể lặp lại. Nó có thể giảm đáng kể thời gian chuẩn bị dữ liệu. Đây không phải là các bước dọn dẹp chung chung—chúng trực tiếp giải quyết các thách thức về báo cáo thương mại điện tử trong thế giới thực và giúp cho quá trình phân tích tiếp theo hiệu quả hơn nhiều. Sau khi cảm thấy thoải mái với những chuyển đổi này, bạn có thể xây dựng mẫu Power Query có thể sử dụng lại cho mỗi lần xuất thị trường mới.
Nhận MIỄN PHÍ Bài tập Excel nâng cao có Giải pháp!