Phương pháp tổ chức dữ liệu database: Tự dựng Data Warehouse – Phần 3

Link Phần 2: 4 thành phần chính trong Business Intelligence: Tự Dựng DATA WAREHOUSE – Phần 2

Như đã hứa trong phần trước, phần này tôi sẽ trình bày phương pháp tổ chức số liệu database để tạo ra các báo cáo trên dashboards.

Tổ chức dữ liệu database

Cách tiếp cận của tôi là đi từ dashboards. Tôi sẽ xác định trước các KPI mà mình cần hiển thị và từ đó thiết kế ra các bảng chứa các dữ liệu đó. Các bạn có thể thấy như trong hình 1, tôi sử dụng dữ liệu mẫu để vẽ dashboard bằng Tableau.

Bài toán hiện tại của chúng ta là đi tìm CAC, CIR theo các chiều: chiến dịch, nguồn kênh, nội dung quảng cáo, từ khóa, tỉnh/thành phố. Như vậy, để có thể vẽ được dashboard như hình 1, tôi cần 1 bảng dữ liệu như hình 2

Bảng dữ liệu này xoay quanh 2 metrics chính là

• Chi phí quảng cáo
• Doanh thu được tạo ra bởi các khách hàng đăng ký thông tin lần đầu trong ngày phát sinh chi phí quảng cáo

Ở đây tôi lưu ý 1 chút. Có các cách tiếp cận CAC và CIR khác nhau đối với từng công ty/ngành. Trong trường hợp bài viết này, tôi muốn tính xem liệu ngân sách quảng cáo trong ngày 15/7/2019 hiệu quả đến đâu bằng cách tìm hết các khách hàng (không phải đơn hàng) được acquired từ hoạt động marketing trong ngày 15/7/2019.
Một khách hàng có thể mua nhiều sản phẩm ở các thời điểm khác nhau. Tất cả các đơn hàng này tôi sẽ quy công lao về cho quảng cáo ngày 15/7 mà thôi.

Bảng dữ liệu thô

Quay lại cái bảng dữ liệu bên trên, tôi đặt tên cho nó là cir, đặt trong schema (nhóm các bảng tương đồng) bi_cir. Để điền dữ liệu còn thiếu vào bảng này, chúng ta sẽ cần đến những bảng dữ liệu thô như hình 3. Mỗi bảng dữ liệu thô sẽ chứa 1 phần mảnh ghép của bức tranh.

Ví dụ: Trong bảng crm.crm_telesale, chúng ta có ngày tạo ra contact (contact_creation_date), mã sản phẩm, đơn giá, số lượng, ngày đặt hàng. Chúng ta có thể dễ dàng cộng doanh thu theo contact_creation_date với vài dòng lệnh SQL (phần này tôi sẽ nói sau).

Tính năng Google Analytics để tạo bảng dữ liệu

Tuy nhiên, khi nhìn vào bảng crm.crm_telesale, bạn sẽ thấy không có những thông tin về utm_source, utm_medium.
Những thông tin này có thể được inject thẳng vào crm thông qua form đăng ký của khách hàng nếu bạn sử dụng các link quảng cáo có chứa utm parameters.

Nhưng utm parameters trong url sẽ không chứa những nguồn như organic search hay direct, do đó, sẽ có trường hợp trường utm_source trong crm không có dữ liệu.
Để giải quyết vấn đề này, tôi sử dụng dữ liệu từ Google Analytics. Như các bạn đã biết, GA có thể cung cấp cho chúng ta chính xác nguồn của contact đến từ đâu. Nhưng vấn đề bây giờ là làm sao map được thông tin về nguồn này với dữ liệu trong CRM.
Rất may, GA còn cung cấp thêm 1 tính năng để định danh từng cá nhân tương tác với website của chúng ta là Google Analytics Client ID. Nếu bạn config form đăng ký đúng cách, thông tin này sẽ được gán cho từng cá nhân đăng ký tài khoản và đi cùng các thông tin định danh khác (số điện thoại, email, địa chỉ, tên) vào CRM. Như vậy, chúng ta đã có cơ sở để truy vấn rất nhiều thông tin của 1 khách hàng chỉ bằng GA.

Doanh thu và chi phí trong bảng dữ liệu

Trở lại bài toán bên trên, do tôi cần phân chia cụ thể doanh thu và chi phí theo:

• Campaign
• Source
• Medium
• Ngày tạo contact

Câu lệnh LEFT JOIN

Tôi sẽ map 2 bảng crm.crm_telesale và ga.client_id_source_channel trước khi đẩy vào bảng bi_cir.cir. Để làm được việc này tôi sẽ join 2 bảng với nhau bằng câu lệnh LEFT JOIN.

• SELECT
• A.contact_creation_date
• ,A.ga_client_id
• ,A.product_id
• ,A.product_price
• ,A.product_quantity
• ,A.order_id
• ,A.order_date
• ,A.utm_campaign
• ,B.utm_source
• ,B.utm_medium
• ,B.utm_term
• ,B.utm_content
• FROM
• (SELECT
• contact_creation_date
• ,ga_client_id
• ,product_id
• ,product_price
• ,product_quantity
• ,order_id
• ,order_date
• ,utm_campaign
• FROM crm.crm_telesale) A
• LEFT JOIN
• (SELECT
• ga_client_id
• ,utm_source
• ,utm_medium
• ,utm_term
• ,utm_content
• ,ga_date
• ,goal_complete_registration
• FROM ga.client_id_source_channel
• WHERE goal_complete_registration > 1) B
• on A.ga_client_id = B.ga_client_id
• AND A.contact_creation_date = B.ga_date

Trong câu lệnh này, tôi tìm cách lấy các dữ liệu từ GA để điền vào chỗ còn thiếu trên bảng crm.crm_telesale bằng hàm LEFT JOIN. Sau khi thực hiện xong lệnh này, mỗi khách hàng (được định danh bằng ga_client_id) trong CRM sẽ có thêm các thông tin là utm_source, utm_medium, utm_term và utm_content đi kèm.

Sau khi có những thông tin này, bạn có thể lựa chọn lưu bảng mới join thành 1 bảng tạm để xử lý tiếp, hoặc bạn có thể mở rộng câu lệnh SQL bên trên để tính toán tiếp tổng doanh thu (ít bước hơn, phức tạp hơn)

Câu lệnh SQL mới

SELECT .
Giả sử bạn lựa chọn phương án lưu kết quả bên trên ra 1 bảng tạm. Đặt tên nó là temp.crm_telesale_ga_source. Chúng ta có thể tính được doanh thu theo ngày, campaign, source, medium… bằng 1 câu lệnh SQL mới:

• SELECT
• contact_creation_date
• ,count (ga_client_id) AS customer_quantity
• ,sum(product_price*product_quantity) AS total_revenue
• ,utm_campaign
• ,utm_source
• ,utm_medium
• ,utm_term
• ,utm_content
• FROM temp.crm_telesale_ga_source
• GROUP BY
• contact_creation_date
• ,utm_campaign
• ,utm_source
• ,utm_medium
• ,utm_term
• ,utm_content

Như thế, chúng ta đã ra 1 bảng mới có các trường thông tin: contact_creation_date, customer_quantity, total_revenue, utm_campaign, utm_source, utm_medium, utm_term, utm_content. Một nửa của bức tranh đã được vẽ. Thứ còn thiếu là chi phí cho từng kênh.

Kết Luận

Phần này tôi chỉ tạm nói đến đây. Hy vọng các bạn đã hiểu sơ khoáng về cách tiếp cận đối với quy trình xây dựng database của tôi: top-down, đi từ yêu cầu business đến cách tổ chức dữ liệu trong database và cách để lấy được thông tin về database.
Cách tiếp cận này có thể chưa phải tối ưu và cách lấy dữ liệu của tôi cũng chưa chắc tốt nhất. Hy vọng các bạn đóng góp thêm để tôi hoàn thiện hơn cách làm của mình.

==> Đọc thêm:

Tác dụng của data warehouse: Câu chuyện tự dựng data warehouse – Phần 1

Nguồn: Cuong Tran