Portfolio Project #03: SQL query for Segmentation & Sales Analytics

Tranh thủ đang có thời gian chút, mình lại share với mọi người thêm một project. Project này sẽ sử dụng SQL để phân nhóm giao dịch theo tổng doanh thu và tính các chỉ số như số lượng khách hàng, giao dịch, giá trị trung bình mỗi đơn hàng,… cho từng phân khúc, tìm ra các khách hàng mua nhiều, sản phẩm bán chạy,… sau đó đưa ra recommendation.

Mọi người có thể xem thêm các project khác mình làm trong Series Data Portfolio Projects nhé!

Đề bài:

Đây là Dataset mình được cung cấp. Data này ghi chép các giao dịch bán hàng ở cửa hàng Tiffany – TTP, bao gồm các cột:

• Date: ngày phát sinh giao dịch (ví dụ 5/12/2021, 5/13/2021…)

• Scheme Name: Loại tài khoản thực hiện giao dịch (ví dụ: PLANTIUM, MEMBER,…)

• Store Name: tên cửa hàng thực hiện giao dịch (“TIFFANY – TTP”)

• Item Name: tên mặt hàng (ví dụ “SS RED RTTM2NHRTDP1618IN-63520594”)

• Member Account Code: mã khách hàng (ví dụ 60080458)

• Invoice: mã hóa đơn giao dịch (cùng một giao dịch sẽ có chung một mã)

• Sales Qty: số lượng mặt hàng trên dòng đó (dương = bán, âm = trả)

• Sales Amt: giá trị tiền tương ứng với cột Sales Qty (có dấu âm nếu là trả hàng)

Đây là data chi tiết để bạn dễ tưởng tượng:

Từ Data này, sẽ cần thực hiện 2 nhiệm vụ chính:

1. Segment khách hàng thành các nhóm & tính các chỉ số

2. Tìm ra top 10 sản phẩm & khách hàng, đưa ra insight & Recommendation từ những gì khai thác được

• Liệt kê Top 10 Member Account theo Sales Quantity & Sales Amount

• Liệt kê Top 10 Items sold theo Sales Quantity & Sales Amount

Đây là cách mình xử lý bài toán này

1. Segment khách hàng thành các nhóm & tính các chỉ số

Ở đây mình sẽ đi tính từng dòng một, sau đó union các dòng lại với nhau

Tính dòng Total

Với dòng total thì cách tính khá đơn giản, mình sử dụng count, sum để tính ra các metrics như đầu bài yêu cầu:

Điểm khó ở đây là tính cột Transactions with 2 items (các giao dịch có từ 2 cột trở lên), phần này không có sẵn ở bảng để tính, mà mình sẽ cần sử dụng GROUP BY để đếm số giao dịch có từ 2 items trở lên, sau đó mới thay vào code bên trên.

Tính các dòng PLANTIUM, GOLD,…

Cách tính khá tương tự như dòng Total, nên mình sẽ copy code để tính dòng total xuống và thêm điều kiện để lọc:

• PLANTIUM: Giao dịch có Sales Amt > 50.000$

• GOLD: Giao dịch có Sales Amt từ 25.000$ - 50.000$

• SILVER: Giao dịch có Sales Amt từ 10.000$ - 25.000$

• CT: Giao dịch có Sales Amt từ 3.000$ - 10.000$

• OTHERS: Các giao dịch còn lại

Một số lưu ý:

• Do đơn vị ở Data được cung cấp là VNĐ nên sẽ cần đổi qua $ để Segment cho đúng

• Cần thêm điều kiện để lọc vào cả code của phần tính cột Transactions with 2 items

Union các hàng lại với nhau

Bước cuối cùng mình sử dụng Union để ghép các cột lại với nhau để tạo thành bảng hoàn chỉnh.

Kiểm tra tính chính xác của kết quả query

Một bước quan trọng cần làm sau khi query chính là kiểm tra kết quả query xem có chính xác hay không. Ở đây mình cộng tổng của các ô Total Clients, Total Transactions, Items Sold, Transactions with 2 items để check của hàng PLANTIUM, GOLD, SILVEL, CT, và OTHERS xem có bằng dòng Total không và thấy kết quả đã chính xác. Ngoài ra còn check lại phần đơn vị, làm tròn xem đã khớp nhau chưa.

Sau khi đã ổn như hình dưới đây, minh tiếp tục đi trả lời các câu hỏi tiếp theo.

2. Tìm ra top 10 sản phẩm & khách hàng, đưa ra insight & Recommendation từ những gì khai thác được

• Liệt kê Top 10 Member Account theo Sales Quantity & Sales Amount

Có thể thấy có sự đối lập rõ rệt giữa số đơn và giá trị chi tiêu của từng khách hàng. Nhóm mua nhiều đơn hàng lại có tổng chi tiêu thấp, (ví dụ 05002597: 10 đơn; 2002141: 6 đơn nhưng giá trị chỉ khoảng $13K).

Trong khi những member có giá trị đơn hàng cao thường mua rất ít đơn. Ví dụ 23000715 chỉ có 1 đơn nhưng chi đến hơn $150K.

• Liệt kê Top 10 Items sold theo Sales Quantity & Sales Amount

Tương tự với nhóm sản phẩm, cũng có thể thấy được xu hướng tương tự.

Những mặt hàng bán chạy về số lượng thường chỉ mang về doanh thu khiêm tốn, dao động 30–130 triệu VND (ví dụ 18R T SMILE SM PDT16 18-35189432 bán được 10 chiếc nhưng chỉ thu về ~ $10K).

Ngược lại, những sản phẩm cao cấp dù bán rất ít (1–2 chiếc) lại tạo ra doanh thu khủng (ví dụ PT RD DI 2 65 D VVS1-70168111 chỉ bán 1 chiếc nhưng đem về hơn $150K).

Kết hợp với bảng Segmentation khách hàng vừa tính toán ở trên có thể đưa ra chiến lược cho từng nhóm khách hàng

1. Nhóm PLANTIUM & GOLD: Nhóm mua ít đơn hàng/sản phẩm, nhưng giá trị đơn hàng rất cao

Đầu tư dịch vụ VIP & upsell cho nhóm chi tiêu cao (PLATINUM, GOLD) để khai thác tối đa giá trị mỗi khách. Cụ thể:

• Ưu tiên giới thiệu các sản phẩm cao cấp - Top 10 Items Sold có Sales Amount cao nhất.

• Cross-sell phụ kiện & dịch vụ gia tăng (extended warranty, cleaning service, hộp trưng bày…) kèm theo sản phẩm chính để tăng số items trên mỗi đơn.

• Gửi voucher giá trị cao cho đơn hàng tiếp theo hoặc dành riêng cho nhóm này nhằm kích thích mua lại.

2. Nhóm SLIVER & CT: Nhóm mua số lượng tương đối nhiều, mỗi đơn thường sẽ trên 1 item, nhưng giá trị mỗi đơn vẫn ở mức trung bình

Đẩy mạnh bán theo bundle & cross-sell để tăng ATV và UPT. Cụ thể:

• Đẩy mạnh bundle & combo: Thiết kế gói “mua 3 tặng 1” hoặc “mua 5 thanh toán 4” (cho các sản phẩm bán chạy nhất - top 10 Items Sold có Sales Quantity cao nhất).

• Cross-sell ngay checkout: Hiển thị “Bạn có thể thích…” với phụ kiện hoặc sản phẩm bổ trợ, khuyến khích thêm 1–2 item.

• Loyalty points: Tặng điểm cho mỗi món mua, nhân đôi điểm khi ghép đơn ≥3 items để kích thích UPT tăng tiếp.

3. Nhóm OTHERS: Nhóm có chi tiêu rất thấp và hầu như các đơn hàng chỉ có 1 sản phẩm

Cần kích thích nhóm này mua nhiều sản phẩm hơn trong một đơn hàng. Cụ thể:

• Min-order: Áp dụng giá trị đơn tối thiểu để được free shipping hoặc tặng voucher nhỏ.

• Cross-sell ngay checkout: Gợi ý 1–2 sản phẩm giá rẻ bổ sung ngay trong giỏ hàng khi họ đang check out

Tạm kết

Trên đây là Project #03 của mình. Hơi technical một tẹo, hy vọng sẽ hữu ích cho mọi người. File Code SQL mọi người có thể xem ở đây.

Nếu bạn thấy bài viết này hữu ích hãy share thêm cho mọi người cùng đọc nhaa!

Nếu bạn đang tìm kiếm các nguồn tài liệu học Data thì có thể thử tham khảo trang tài liệu mà mình đã tổng hợp tại đây. Và đừng quên subcribe mình để nhận được thông báo khi có bài viết mới nha 😀