Giới thiệu

Bên cạnh việc tạo và train model chay thì việc biết sử dụng các dịch vụ Cloud sẽ giúp các bạn tiết kiệm rất nhiều thời gian và công sức. Các dịch vụ này cung cấp những công cụ mạnh mẽ cho việc lưu trữ dữ liệu, xử lý tính toán, và triển khai mô hình. Thay vì phải đầu tư vào cơ sở hạ tầng vật lý, bạn có thể dễ dàng truy cập vào tài nguyên tính toán thông qua các nền tảng như Amazon Web Services (AWS), Google Cloud Platform (GCP), hoặc Microsoft Azure.

Trong bài viết này, chúng ta sẽ thử hands on BigQuery ML của Google Cloud. Đây là 1 công cụ giúp bạn thực thi các Machine learning model sử dụng các truy vấn SQL. Từ đó bạn có thể thực hiện phân tích, chạy model, đưa ra báo cáo 1 cách trực quan, nhanh chóng và chính xác. Mình sẽ thực hiện thực hành với BigQuery ML theo course của Google Cloud. Bạn có thể cùng thực hành tại link https://www.cloudskillsboost.google/ Đây là 1 nguồn rất hay để bạn học về Google Cloud.

Làm quen với BigQuery ML

Để bắt đầu, bạn cần vào console của Google Cloud và chọn BigQuery.

Để tạo 1 dataset mới, ta sẽ thực hiện chọn "Create dataset" như hình dưới.

Sau đó, ta sẽ nhập tên Dataset ở "Dataset ID", chẳng hạn mình sẽ đặt tên là bqml_lab. Sau đó chọn "CREATE DATASET".

Tiếp theo, ta thử tạo 1 query đơn giản. Query ở đây là tạo 1 model để dự đoán xem người dùng có thực hiện 1 giao dịch mua bán gì không.

#standardSQL
CREATE OR REPLACE MODEL `bqml_lab.sample_model`
OPTIONS(model_type='logistic_reg') AS
SELECT
  IF(totals.transactions IS NULL, 0, 1) AS label,
  IFNULL(device.operatingSystem, "") AS os,
  device.isMobile AS is_mobile,
  IFNULL(geoNetwork.country, "") AS country,
  IFNULL(totals.pageviews, 0) AS pageviews
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20160801' AND '20170631'
LIMIT 100000;

Đoạn code SQL này sử dụng cú pháp của BigQuery ML để tạo hoặc thay thế một mô hình Machine Learning, trong trường hợp này là một mô hình hồi quy logistic. Chi tiết như sau:

CREATE OR REPLACE MODEL bqml_lab.sample_model

• CREATE OR REPLACE MODEL: Tạo một mô hình mới hoặc thay thế mô hình hiện có.
• bqml_lab.sample_model: Tên của mô hình sẽ được tạo hoặc thay thế, nằm trong dataset bqml_lab và có tên là sample_model.

OPTIONS(model_type='logistic_reg')

• OPTIONS(model_type='logistic_reg'): Đặt loại mô hình là hồi quy logistic (logistic_reg). Hồi quy logistic được sử dụng để phân loại nhị phân, nghĩa là dự đoán một trong hai kết quả (ví dụ: 0 hoặc 1).

AS SELECT

• AS SELECT: Bắt đầu phần truy vấn để lựa chọn dữ liệu đầu vào cho mô hình.

IF(totals.transactions IS NULL, 0, 1) AS label

• IF(totals.transactions IS NULL, 0, 1) AS label: Tạo cột nhãn (label) cho mô hình. Nếu totals.transactions là NULL thì giá trị là 0, ngược lại là 1. Điều này giúp xác định liệu có giao dịch hay không.

IFNULL(device.operatingSystem, "") AS os

• IFNULL(device.operatingSystem, "") AS os: Tạo cột os (hệ điều hành) bằng cách lấy giá trị của device.operatingSystem. Nếu giá trị này là NULL, nó sẽ được thay thế bằng một chuỗi rỗng ("").

device.isMobile AS is_mobile

• device.isMobile AS is_mobile: Tạo cột is_mobile, cho biết thiết bị có phải là thiết bị di động hay không.

IFNULL(geoNetwork.country, "") AS country

• IFNULL(geoNetwork.country, "") AS country: Tạo cột country (quốc gia) bằng cách lấy giá trị của geoNetwork.country. Nếu giá trị này là NULL, nó sẽ được thay thế bằng một chuỗi rỗng ("").

IFNULL(totals.pageviews, 0) AS pageviews

• IFNULL(totals.pageviews, 0) AS pageviews: Tạo cột pageviews (số lượt xem trang) bằng cách lấy giá trị của totals.pageviews. Nếu giá trị này là NULL, nó sẽ được thay thế bằng 0.

FROM bigquery-public-data.google_analytics_sample.ga_sessions_*

• FROM bigquery-public-data.google_analytics_sample.ga_sessions_*: Chọn dữ liệu từ bảng ga_sessions_* trong dataset google_analytics_sample của dự án công khai bigquery-public-data. Dấu * đại diện cho các bảng phân vùng theo ngày.

WHERE _TABLE_SUFFIX BETWEEN '20160801' AND '20170631'

• WHERE _TABLE_SUFFIX BETWEEN '20160801' AND '20170631': Lọc các bảng theo phân vùng ngày, chỉ lấy dữ liệu từ ngày 1 tháng 8 năm 2016 đến ngày 31 tháng 6 năm 2017.

LIMIT 100000

• LIMIT 100000: Giới hạn số lượng hàng dữ liệu được chọn là 100,000 hàng.

Đoạn code SQL này tạo hoặc thay thế một mô hình hồi quy logistic dựa trên dữ liệu từ Google Analytics, sử dụng các đặc trưng như hệ điều hành, thiết bị di động, quốc gia, và số lượt xem trang để dự đoán liệu có giao dịch hay không.

Tiếp theo ta chọn "RUN" để thực thi code:

Muốn xem thông tin chi tiết mô hình, bạn có thể chọn "sample_model" như sau:

Để đánh giá chất lượng model ta sẽ chạy query sau:

#standardSQL
SELECT
  *
FROM
  ml.EVALUATE(MODEL `bqml_lab.sample_model`, (
SELECT
  IF(totals.transactions IS NULL, 0, 1) AS label,
  IFNULL(device.operatingSystem, "") AS os,
  device.isMobile AS is_mobile,
  IFNULL(geoNetwork.country, "") AS country,
  IFNULL(totals.pageviews, 0) AS pageviews
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'));

Kết quả thu được:

Tiếp theo, ta sẽ sử dụng câu query dưới để sử dụng model. Cụ thể, ta sẽ cần biết xem top 10 quốc gia có khả năng giao dịch nhiều nhất.

#standardSQL
SELECT
  country,
  SUM(predicted_label) as total_predicted_purchases
FROM
  ml.PREDICT(MODEL `bqml_lab.sample_model`, (
SELECT
  IFNULL(device.operatingSystem, "") AS os,
  device.isMobile AS is_mobile,
  IFNULL(totals.pageviews, 0) AS pageviews,
  IFNULL(geoNetwork.country, "") AS country
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'))
GROUP BY country
ORDER BY total_predicted_purchases DESC
LIMIT 10;

Ta cũng có thể thử đưa ra dự đoán trên từng user sử dụng query dưới:

#standardSQL
SELECT
  fullVisitorId,
  SUM(predicted_label) as total_predicted_purchases
FROM
  ml.PREDICT(MODEL `bqml_lab.sample_model`, (
SELECT
  IFNULL(device.operatingSystem, "") AS os,
  device.isMobile AS is_mobile,
  IFNULL(totals.pageviews, 0) AS pageviews,
  IFNULL(geoNetwork.country, "") AS country,
  fullVisitorId
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'))
GROUP BY fullVisitorId
ORDER BY total_predicted_purchases DESC
LIMIT 10;

Ta có kết quả như sau, đây là top 10 user có khả năng mua lớn nhất.

Hands on: Dự đoán lượng khách mua hàng

Trong phần này, bạn sẽ thực hiện các nhiệm vụ sau:

• Sử dụng BigQuery để tìm các public data
• Vọc vạch với dữ liệu thương mại điện tử
• Tạo tập dữ liệu train và val để sử dụng cho batch prediction
• Tạo một mô hình phân loại (hồi quy logistic) trong BigQuery ML
• Đánh giá và cải thiện hiệu suất của mô hình machine learning
• Dự đoán và sắp xếp xác suất một user sẽ thực hiện mua hàng

Đầu tiên, ta cần tạo project data-to-insights theo các bước:

• Trong thanh Explorer, chọn + ADD.
• Bảng Add data sẽ mở ra.
• Chọn Star a project by name dưới mục Additional sources.
• Nhập data-to-insights và nhấp vào Star.

Ta có tình huống như sau: Team Data Analyst thực hiện export log của Google Analytics của 1 trang web thương mại điện tử vào BigQuery. Sau đó, team tạo một bảng chứa tất cả dữ liệu thô về các phiên truy cập của khách hàng. Sử dụng dữ liệu này, bạn cần trả lời các câu hỏi sau.

Câu hỏi 1: Trong tổng số khách truy cập trang web, bao nhiêu % đã mua hàng?

Ta sẽ thực hiện truy vấn sau:

#standardSQL
WITH visitors AS(
SELECT
COUNT(DISTINCT fullVisitorId) AS total_visitors
FROM `data-to-insights.ecommerce.web_analytics`
),

purchasers AS(
SELECT
COUNT(DISTINCT fullVisitorId) AS total_purchasers
FROM `data-to-insights.ecommerce.web_analytics`
WHERE totals.transactions IS NOT NULL
)

SELECT
  total_visitors,
  total_purchasers,
  total_purchasers / total_visitors AS conversion_rate
FROM visitors, purchasers

Ta có kết quả:

Các bạn có thể thấy ta có tỷ lệ chuyển đổi là 2.69%

Câu hỏi 2: Top 5 sản phẩm bán chạy nhất là gì?

SELECT
  p.v2ProductName, -- Tên sản phẩm
  p.v2ProductCategory, -- Danh mục sản phẩm
  SUM(p.productQuantity) AS units_sold, -- Tổng số lượng sản phẩm đã bán
  ROUND(SUM(p.localProductRevenue / 1000000), 2) AS revenue -- Tổng doanh thu (triệu đô) làm tròn 2 chữ số
FROM `data-to-insights.ecommerce.web_analytics`,
UNNEST(hits) AS h, -- Phân rã mảng hits thành các hàng riêng lẻ
UNNEST(h.product) AS p -- Phân rã mảng product trong mỗi hit thành các hàng riêng lẻ
GROUP BY 1, 2 -- Nhóm theo tên và danh mục sản phẩm
ORDER BY revenue DESC -- Sắp xếp theo doanh thu giảm dần
LIMIT 5; -- Giới hạn kết quả trả về 5 sản phẩm

Kết quả:

Câu hỏi 3: Bao nhiêu user sẽ mua trong lần truy cập website tiếp theo?

# visitors who bought on a return visit (could have bought on first as well
WITH all_visitor_stats AS (
SELECT
  fullvisitorid, # 741,721 unique visitors
  IF(COUNTIF(totals.transactions > 0 AND totals.newVisits IS NULL) > 0, 1, 0) AS will_buy_on_return_visit
  FROM `data-to-insights.ecommerce.web_analytics`
  GROUP BY fullvisitorid
)

SELECT
  COUNT(DISTINCT fullvisitorid) AS total_visitors,
  will_buy_on_return_visit
FROM all_visitor_stats
GROUP BY will_buy_on_return_visit

Kết quả:

Tiếp theo, ta sẽ tạo một mô hình Machine Learning trong BigQuery để dự đoán liệu một người dùng mới có khả năng mua hàng trong tương lai hay không. Việc xác định những người dùng có giá trị cao này có thể giúp đội ngũ marketing giới thiệu với họ các chương trình khuyến mãi đặc biệt và các chiến dịch quảng cáo nhằm đảm bảo rằng họ sẽ mua hàng, trong khi họ đang phân vấn so sánh giá cả thể hiện ở các lần truy cập vào trang thương mại điện tử của bạn.

Để tạo 1 mô hình ngon thì ta phải cần có 1 bộ data và được trích chọn feature tốt. Giả sử, team của bạn quyết định kiểm tra xem liệu 2 feature sau có phải là đầu vào tốt cho mô hình phân loại hay không:

• totals.bounces (liệu khách truy cập có rời khỏi trang web ngay lập tức không)
• totals.timeOnSite (thời gian khách truy cập ở trên trang web)

Câu hỏi 1: Rủi ro khi chỉ sử dụng 2 field trên là gì?

Machine learning chỉ tốt khi dữ liệu huấn luyện được cung cấp đủ tốt. Nếu không có đủ thông tin để mô hình xác định và học mối quan hệ giữa các đặc trưng đầu vào và nhãn (trong trường hợp này là liệu khách truy cập có mua hàng trong tương lai hay không), bạn sẽ không thể có một mô hình chính xác. Mặc dù việc huấn luyện mô hình chỉ với 2 feature này là 1 khởi đầu sương sương , ta vẫn sẽ cần xem liệu chúng có đủ tốt để tạo ra một mô hình chính xác hay không.

SELECT
  * EXCEPT(fullVisitorId)
FROM

  # features
  (SELECT
    fullVisitorId,
    IFNULL(totals.bounces, 0) AS bounces,
    IFNULL(totals.timeOnSite, 0) AS time_on_site
  FROM
    `data-to-insights.ecommerce.web_analytics`
  WHERE
    totals.newVisits = 1)
  JOIN
  (SELECT
    fullvisitorid,
    IF(COUNTIF(totals.transactions > 0 AND totals.newVisits IS NULL) > 0, 1, 0) AS will_buy_on_return_visit
  FROM
      `data-to-insights.ecommerce.web_analytics`
  GROUP BY fullvisitorid)
  USING (fullVisitorId)
ORDER BY time_on_site DESC
LIMIT 10;

Ở đây ta có 2 cột features là bounces và time_on_site. Label là will_buy_on_return_visit. Chú ý là giá trị 2 features kia ta sẽ biết sau lần đầu tiên truy cập website của người dùng.

Câu hỏi 2: Nhìn vào kết quả dữ liệu ban đầu, bạn có nghĩ rằng "time_on_site" và "bounces" sẽ là chỉ số tốt để xác định liệu người dùng có quay lại và mua hàng hay không?

Thường thì việc đánh giá này quá sớm để có kết luận trước khi huấn luyện và đánh giá mô hình. Nhưng nhìn qua lần đầu, trong số 10 người có time_on_site cao nhất, chỉ có 1 người quay lại để mua hàng, điều này không mấy khả quan. Chúng ta hãy chờ xem mô hình sẽ hoạt động như thế nào

Tiếp theo, ta cần tạo 1 BigQuery Dataset để lưu lại model. Ta sẽ thực hiện các bước như trong 2 hình dưới

Với bài toán này, ta sẽ sử dụng classification model là hợp lý Đoạn truy vấn sau sẽ được dùng để train model.

CREATE OR REPLACE MODEL `ecommerce.classification_model`
OPTIONS
(
model_type='logistic_reg',
input_label_cols = ['will_buy_on_return_visit']
)
AS

#standardSQL
SELECT
  * EXCEPT(fullVisitorId)
FROM

  # features
  (SELECT
    fullVisitorId,
    IFNULL(totals.bounces, 0) AS bounces,
    IFNULL(totals.timeOnSite, 0) AS time_on_site
  FROM
    `data-to-insights.ecommerce.web_analytics`
  WHERE
    totals.newVisits = 1
    AND date BETWEEN '20160801' AND '20170430') # train on first 9 months
  JOIN
  (SELECT
    fullvisitorid,
    IF(COUNTIF(totals.transactions > 0 AND totals.newVisits IS NULL) > 0, 1, 0) AS will_buy_on_return_visit
  FROM
      `data-to-insights.ecommerce.web_analytics`
  GROUP BY fullvisitorid)
  USING (fullVisitorId)
;

Sau khi quá trình train kết thúc, bạn có thể tìm thấy model tại đây:

Để đánh giá chất lượng mô hình, ta sẽ sử dụng câu truy vấn sau:

SELECT
  roc_auc,
  CASE
    WHEN roc_auc > .9 THEN 'good'
    WHEN roc_auc > .8 THEN 'fair'
    WHEN roc_auc > .7 THEN 'decent'
    WHEN roc_auc > .6 THEN 'not great'
  ELSE 'poor' END AS model_quality
FROM
  ML.EVALUATE(MODEL ecommerce.classification_model,  (

SELECT
  * EXCEPT(fullVisitorId)
FROM

  # features
  (SELECT
    fullVisitorId,
    IFNULL(totals.bounces, 0) AS bounces,
    IFNULL(totals.timeOnSite, 0) AS time_on_site
  FROM
    `data-to-insights.ecommerce.web_analytics`
  WHERE
    totals.newVisits = 1
    AND date BETWEEN '20170501' AND '20170630') # eval on 2 months
  JOIN
  (SELECT
    fullvisitorid,
    IF(COUNTIF(totals.transactions > 0 AND totals.newVisits IS NULL) > 0, 1, 0) AS will_buy_on_return_visit
  FROM
      `data-to-insights.ecommerce.web_analytics`
  GROUP BY fullvisitorid)
  USING (fullVisitorId)

));

Kết quả:

Kết quả chưa ngon lắm, ta sẽ cần cải thiện thêm. Ý tưởng cơ bản là ta sẽ xem có feature mới nào có ích cho model không. Ta sẽ tạo mô hình mới như sau:

CREATE OR REPLACE MODEL `ecommerce.classification_model_2`
OPTIONS
  (model_type='logistic_reg', input_label_cols = ['will_buy_on_return_visit']) AS

WITH all_visitor_stats AS (
SELECT
  fullvisitorid,
  IF(COUNTIF(totals.transactions > 0 AND totals.newVisits IS NULL) > 0, 1, 0) AS will_buy_on_return_visit
  FROM `data-to-insights.ecommerce.web_analytics`
  GROUP BY fullvisitorid
)

# add in new features
SELECT * EXCEPT(unique_session_id) FROM (

  SELECT
      CONCAT(fullvisitorid, CAST(visitId AS STRING)) AS unique_session_id,

      # input_label_cols
      will_buy_on_return_visit,

      MAX(CAST(h.eCommerceAction.action_type AS INT64)) AS latest_ecommerce_progress,

      # behavior on the site
      IFNULL(totals.bounces, 0) AS bounces,
      IFNULL(totals.timeOnSite, 0) AS time_on_site,
      IFNULL(totals.pageviews, 0) AS pageviews,

      # where the visitor came from
      trafficSource.source,
      trafficSource.medium,
      channelGrouping,

      # mobile or desktop
      device.deviceCategory,

      # geographic
      IFNULL(geoNetwork.country, "") AS country

  FROM `data-to-insights.ecommerce.web_analytics`,
     UNNEST(hits) AS h

    JOIN all_visitor_stats USING(fullvisitorid)

  WHERE 1=1
    # only predict for new visits
    AND totals.newVisits = 1
    AND date BETWEEN '20160801' AND '20170430' # train 9 months

  GROUP BY
  unique_session_id,
  will_buy_on_return_visit,
  bounces,
  time_on_site,
  totals.pageviews,
  trafficSource.source,
  trafficSource.medium,
  channelGrouping,
  device.deviceCategory,
  country
);

Check lại kết quả mô hình mới thôi:

#standardSQL
SELECT
  roc_auc,
  CASE
    WHEN roc_auc > .9 THEN 'good'
    WHEN roc_auc > .8 THEN 'fair'
    WHEN roc_auc > .7 THEN 'decent'
    WHEN roc_auc > .6 THEN 'not great'
  ELSE 'poor' END AS model_quality
FROM
  ML.EVALUATE(MODEL ecommerce.classification_model_2,  (

WITH all_visitor_stats AS (
SELECT
  fullvisitorid,
  IF(COUNTIF(totals.transactions > 0 AND totals.newVisits IS NULL) > 0, 1, 0) AS will_buy_on_return_visit
  FROM `data-to-insights.ecommerce.web_analytics`
  GROUP BY fullvisitorid
)

# add in new features
SELECT * EXCEPT(unique_session_id) FROM (

  SELECT
      CONCAT(fullvisitorid, CAST(visitId AS STRING)) AS unique_session_id,

      # input_label_cols
      will_buy_on_return_visit,

      MAX(CAST(h.eCommerceAction.action_type AS INT64)) AS latest_ecommerce_progress,

      # behavior on the site
      IFNULL(totals.bounces, 0) AS bounces,
      IFNULL(totals.timeOnSite, 0) AS time_on_site,
      totals.pageviews,

      # where the visitor came from
      trafficSource.source,
      trafficSource.medium,
      channelGrouping,

      # mobile or desktop
      device.deviceCategory,

      # geographic
      IFNULL(geoNetwork.country, "") AS country

  FROM `data-to-insights.ecommerce.web_analytics`,
     UNNEST(hits) AS h

    JOIN all_visitor_stats USING(fullvisitorid)

  WHERE 1=1
    # only predict for new visits
    AND totals.newVisits = 1
    AND date BETWEEN '20170501' AND '20170630' # eval 2 months

  GROUP BY
  unique_session_id,
  will_buy_on_return_visit,
  bounces,
  time_on_site,
  totals.pageviews,
  trafficSource.source,
  trafficSource.medium,
  channelGrouping,
  device.deviceCategory,
  country
)
));

Kết quả ngon hơn rất nhiều rồi

Để sử dụng model này cho việc dự đoán, ta sẽ dùng câu truy vấn sau:

SELECT
*
FROM
  ml.PREDICT(MODEL `ecommerce.classification_model_2`,
   (

WITH all_visitor_stats AS (
SELECT
  fullvisitorid,
  IF(COUNTIF(totals.transactions > 0 AND totals.newVisits IS NULL) > 0, 1, 0) AS will_buy_on_return_visit
  FROM `data-to-insights.ecommerce.web_analytics`
  GROUP BY fullvisitorid
)

  SELECT
      CONCAT(fullvisitorid, '-',CAST(visitId AS STRING)) AS unique_session_id,

      # input_label_cols
      will_buy_on_return_visit,

      MAX(CAST(h.eCommerceAction.action_type AS INT64)) AS latest_ecommerce_progress,

      # behavior on the site
      IFNULL(totals.bounces, 0) AS bounces,
      IFNULL(totals.timeOnSite, 0) AS time_on_site,
      totals.pageviews,

      # where the visitor came from
      trafficSource.source,
      trafficSource.medium,
      channelGrouping,

      # mobile or desktop
      device.deviceCategory,

      # geographic
      IFNULL(geoNetwork.country, "") AS country

  FROM `data-to-insights.ecommerce.web_analytics`,
     UNNEST(hits) AS h

    JOIN all_visitor_stats USING(fullvisitorid)

  WHERE
    # only predict for new visits
    totals.newVisits = 1
    AND date BETWEEN '20170701' AND '20170801' # test 1 month

  GROUP BY
  unique_session_id,
  will_buy_on_return_visit,
  bounces,
  time_on_site,
  totals.pageviews,
  trafficSource.source,
  trafficSource.medium,
  channelGrouping,
  device.deviceCategory,
  country
)

)

ORDER BY
  predicted_will_buy_on_return_visit DESC;

Kết quả:

Kết luận

Qua bài viết, các bạn đã được làm quen những thao tác rất cơ bản trên BigQuery của Google Cloud. Đây là 1 công cụ rất hữu ích giúp bạn phân tích dữ liệu và chạy mô hình 1 cách nhanh chóng. Trong các bài viết tới chúng ta sẽ itmf hiểu những usecase phức tạp hơn của BigQuery.