使用 BigQuery ML 中的分類模型預測訪客購買狀況

2023/12/23閱讀時間約 30 分鐘

Overview

BigQuery 是 Google 完全代管、無需維運、低成本的分析數據庫。 使用 BigQuery，您可以查詢大量數據，而無需管理任何基礎架構或需要數據庫管理員。 BigQuery 使用 SQL 並可以利用按用量付費模式。 BigQuery 讓您可以專注於分析數據以找到有意義的見解。

BigQuery Machine Learning（BigQuery ML）是 BigQuery 中的一項功能，數據分析師可以使用SQL語句創建、訓練、評估和預測機器學習模型。

在這次實作將使用一個電子商務(Google 商品商店)數據集，其中包含數百萬條已加載到 BigQuery 的 Google Analytics 記錄。將使用這些數據來運行一些典型的查詢和模型訓練和預測，以讓企業更了解其客戶的購買習慣的。

Access the public dataset

在「Explorer pane」面板中，按一下「+ ADD」。
在「Additional sources」下方，按一下「Star a project by name」。
輸入「data-to-insights」資料集，然後按一下「Star」。

Explore data

在所有訪問電商網站的訪客中，有多少比例完成了購買?

/*--
使用 visitors 子查詢計算總訪客數，該數據來自於 data-to-insights.ecommerce.web_analytics 資料集中所有的 fullVisitorId 的計數。
--*/
WITH visitors AS(
SELECT
COUNT(DISTINCT fullVisitorId) AS total_visitors
FROM `data-to-insights.ecommerce.web_analytics`
),

/*--
使用 purchasers 子查詢計算總購買者數，該數據同樣來自於 data-to-insights.ecommerce.web_analytics 資料集，但僅計算有交易（totals.transactions）的 fullVisitorId 的計數。
--*/
purchasers AS(
SELECT
COUNT(DISTINCT fullVisitorId) AS total_purchasers
FROM `data-to-insights.ecommerce.web_analytics`
WHERE totals.transactions IS NOT NULL
)

/*--
主查詢部分選擇顯示總訪客數（total_visitors）、總購買者數（total_purchasers）以及轉換率（conversion_rate）。轉換率的計算方式是總購買者數除以總訪客數。
--*/
SELECT
  total_visitors,
  total_purchasers,
  total_purchasers / total_visitors AS conversion_rate
FROM visitors, purchasers

轉換率:2.69%

銷售前五名的產品是？

SELECT
  p.v2ProductName,
  p.v2ProductCategory,
  SUM(p.productQuantity) AS units_sold,
  ROUND(SUM(p.localProductRevenue/1000000),2) AS revenue
FROM `data-to-insights.ecommerce.web_analytics`,
UNNEST(hits) AS h,
UNNEST(h.product) AS p
GROUP BY 1, 2
ORDER BY revenue DESC
LIMIT 5;

/*--
選擇顯示商品名稱（v2ProductName）、商品類別（v2ProductCategory）、已售出的單位數量（units_sold）和總收入（revenue）。
使用 UNNEST 函數展平（flatten）資料結構，將 hits 中的每個元素以及其中的 product 屬性展平，以便提取商品相關的數據。
使用 SUM 函數計算每個商品的已售出的單位數量和總收入。已售出的單位數量來自 productQuantity，而總收入則是使用 localProductRevenue，並且將其轉換為百萬單位（除以 1000000）並四捨五入到兩位小數。
使用 GROUP BY 子句將結果按商品名稱和商品類別進行分組。
使用 ORDER BY 子句將結果按照總收入（revenue）降序排序。
使用 LIMIT 5 限制結果集的大小，只顯示前五個結果。
--*/

有多少訪客在再次訪問(subsequent visits)該網站時進行了購買？

# 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

/*--
使用 all_visitor_stats 子查詢，對每個 fullVisitorId 計算相應的統計信息。
fullvisitorid 表示每個獨特的訪客 ID。
IF 函數用於判斷該訪客是否在返回訪問時購買。條件是該訪客的 totals.transactions 大於 0（表示有購買），並且 totals.newVisits 為空（表示不是新訪問）。
will_buy_on_return_visit 列的值為 1（如果滿足條件）或 0（否則）。
在主查詢中，使用 COUNT(DISTINCT fullvisitorid) 計算總訪客數，並根據 will_buy_on_return_visit 分別計算購買和不購買的訪客數。
使用 GROUP BY will_buy_on_return_visit 將結果按照是否在返回訪問時購買進行分組。
--*/

分析結果表示，可以看到 (11873 / 741721) = 1.6% 的訪客總數會再次返回網站進行購買。(包括在第一次會話已經購買的訪客子集)

Select features and create your training dataset

Google Analytics（分析）可擷取有關使用者造訪該本電子商務網站資料集的各種維度和衡量標準。瀏覽BigQuery Export schema文件中的各種維度列表，查找有用的特徵，這些特徵將幫助機器學習模型了解訪問者首次訪問您的網站的數據與他們是否會返回購買之間的關係。

測試這兩個欄位是否適合您的分類模型：

totals.bounces（訪客是否立即離開網站）
totals.timeOnSite（訪客在我們網站上停留的時間）

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;

/*--
在子查詢中，選擇 data-to-insights.ecommerce.web_analytics 資料集中的 totals.newVisits = 1 的訪問數據，同時選擇 totals.bounces（彈跳次數）和 totals.timeOnSite（在站點上花費的時間），並使用 IFNULL 函數處理可能的空值。

在另一個子查詢中，計算每個訪客是否在返回訪問時購買，並使用 COUNTIF 函數進行判斷。將結果以 will_buy_on_return_visit 的形式表示。

在主查詢中，使用 JOIN 子句將這兩個子查詢的結果根據 fullVisitorId 進行合併。
使用 EXCEPT(fullVisitorId) 排除掉原始資料中的 fullVisitorId 列，只顯示其他特徵。
使用 ORDER BY time_on_site DESC 將結果按照在站點上花費的時間降序排序。
使用 LIMIT 10 限制結果集的大小，只顯示前十個結果。
--*/

我們可以得知:

輸入特徵(X)是「bounces」和「time_on_site」。標籤(Y)是「will_buy_on_return_visit」。
「bounces」和「time_on_site」在訪客第一次訪問網站後就會知道的。
「will_buy_on_return_visit」不是在第一次訪客訪問後得知的。您是在預測那些返回網站並購買的用戶子集。由於在預測時並不知道未來，無法確信新訪客是否會回來購買。我們將建立機器學習模型根據他們第一次會話的數據來獲取未來購買的機率。

Create a BigQuery dataset to store models

在左側窗格中的專案名稱旁的「...」圖示，然後按一下「 Create dataset」。
對於 Dataset ID，輸入「ecommerce」。
點擊 Create dataset。

Select a BigQuery ML model type and specify options

由於您將訪客分為“將來會購買”或“將來不會購買”，因此使用羅吉斯迴歸分析進行分類。

BigQuery ML目前支援的模型

建立模型

CREATE OR REPLACE MODEL `ecommerce.classification_model`
OPTIONS
(
model_type='logistic_reg',
labels = ['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)
;
/*-- 
使用 CREATE OR REPLACE MODEL 語句創建一個新的模型或替換現有的模型。模型的名稱為 ecommerce.classification_model。
使用 OPTIONS 子句指定模型的選項，包括模型類型為邏輯回歸（'logistic_reg'）和標籤為 will_buy_on_return_visit。
在 AS 子句之後，使用 SELECT 語句選擇模型的輸入特徵。這裡選擇了 totals.bounces 和 totals.timeOnSite，同時使用 IFNULL 函數處理可能的空值。
這些特徵是基於 data-to-insights.ecommerce.web_analytics 資料集中 totals.newVisits = 1 以及日期範圍為 '20160801' 到 '20170430'（前9個月）的數據。
使用 JOIN 子句將這些特徵和目標變數 will_buy_on_return_visit 進行合併。目標變數的值是根據每個訪客是否在返回訪問時購買的統計信息而得到的。
--*/

查看資料集並確認出現了classification_model。

Evaluate classification model performance

對於 ML 中的分類問題，您希望最小化False Positive（預測用戶返回購買，但他們沒有）並最大化True Positive（預測用戶返回購買，且他們確實這樣做）。
這種關係透過 ROC (Receiver Operating Characteristic) 曲線進行視覺化，如下所示，嘗試最大化曲線下面積：

在 BigQuery ML 中，roc_auc只是評估經過訓練的 ML 模型時的可查詢欄位。

- 執行下方查詢評估模型的效能 ML.EVALUATE

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)

));

模型效能的結果

Improve model performance with Feature Engineering

透過特徵工程提升模型效能:資料集中還有更多其他特徵可以幫助模型更好地理解訪客的第一次會話與他們在後續造訪中購買的可能性之間的關係
新增一些新特徵並建立第二個機器學習模型，名稱為classification_model_2：
- 訪客首次訪問時在結帳流程中花費了多長時間
- 訪客來自哪裡（流量來源：自然搜尋、推薦網站等..）
- 設備類別（行動裝置、平板電腦、桌上型電腦）
- 地理資訊（國家）

CREATE OR REPLACE MODEL `ecommerce.classification_model_2`
OPTIONS
  (model_type='logistic_reg', labels = ['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,

      # labels
      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
);

評估新模型效能的結果

#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,

      # labels
      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
)
));

新模型效能的結果

Predict which new visitors will come back and purchase

預測哪些新訪客會回來並進行購買。

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,

      # labels
      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;

預測是使用資料集的最後 1 個月（共 12 個月）的資料進行的。
查詢會輸出對 2017 年 7 月的預測。可以看到三個新新增的欄位：
- Predicted_will_buy_on_return_visit：模型是否認為訪客會稍後再購買（1 = 是）
- Predicted_will_buy_on_return_visit_probs.label：是/否的二元分類器
- Predicted_will_buy_on_return_visit.probs.prob：模型對其預測的信心度 (1 = 100%)

原始資料來源:Predict Visitor Purchases with a Classification Model in BigQuery ML

如果你喜歡這篇文章歡迎幫我按愛心鼓勵一下喔!~閱讀愉快!~

其他學習資訊

Marcos的方格子Marcos談科技

Marcos的方格子

17會員

32內容數

前往沙龍

歡迎來到「Marcos的方格子」！目前在「Marcos談科技」撰寫在職涯上學習到的知識，在「Marcos談書」分享我在日常的閱讀和心得，歡迎您的到來!!