BigQuery ML ロジスティック回帰で、次のセッションの注文を予測する

概要

以下のチュートリアルをやってみた。
Getting started with BigQuery ML for data analysts | BigQuery | Google Cloud
これを少しアレンジして、
Google Analyticsのデータから、次のセッションでcv(transaction)が発生するか、BigQuery ML のロジスティック回帰で予測を行ってみる。

モチベーションとしては、例えばリマーケティング広告や販促メールの送付先を検討するには、次のセッションでコンバージョンが発生しそうかどうかに意味があると考えたためです。

チュートリアル同様、google_analytics_sample データを利用する。

ステップ1: 準備

データセットの作成

チュートリアルと同様に、bqml_tutorial データセットを使用する。こちらに完成したモデルを格納する。

変数として追加出来そうなデータを探索する

チュートリアルで使用された変数以外に、google_analytics_sample で使用できそうなデータを探す。ぱっと思いつくのは

特定のページを見たかどうか(あるいは見た回数)
特定のイベントが発生したかどうか(あるいは発生した回数)

など

ページについては、サイト構造をよく理解していないので今回は使用しない。
発生しているイベントを調べてみる。

#StandardSql
SELECT
  hits.eventInfo.eventAction,
  hits.eventInfo.eventCategory,
  hits.eventInfo.eventLabel,
  COUNT(*) AS event_cnt
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` AS ga,
  ga.hits AS hits
GROUP BY
  1,
  2,
  3
ORDER BY
  4 DESC

結果がこちら

EventActionにAdd to Cart などそれっぽいものがある。
他のEventActionも含め、セッションで特定のイベントが発生したがどうか、bool型のカラムを作成することにする。

ステップ2: モデルを作成する

次のセッションでtransactionが発生するかどうかが今回の目的変数なので、LEAD関数を使用する。
Standard SQL Functions & Operators | BigQuery | Google Cloud

また、今回は次のセッションが発生しなかったユーザーは除外した。
モデルの格納先は、bqml_tutorial.model001_nextSessionTransaction。

#standardSQL
CREATE MODEL `bqml_tutorial.model001_nextSessionTransaction`
OPTIONS(model_type='logistic_reg') AS (
  SELECT
  #次のセッションが発生したセッションに絞る
    *
  FROM (
    SELECT
      #今回の目的変数。次のセッションでtransactionが発生するかどうか。
      LEAD(IF(totals.transactions >0,1,0 )) OVER(partition by fullVisitorId  ORDER BY visitStartTime)  AS label,
      #以下が組成。このセッションでの行動から作成する。
      IF(totals.transactions IS NULL, 0, 1) AS transaction,
      IFNULL(device.operatingSystem, "") AS os,
      device.isMobile AS is_mobile,
      IFNULL(geoNetwork.country, "") AS country,
      IFNULL(totals.pageviews, 0) AS pageviews,
      IFNULL(totals.bounces, 0) AS bounces,
      IFNULL(totals.timeOnSite , 0) AS timeOnSite,
      IFNULL(totals.newVisits  , 0) AS newVisits,
      #session内で、特定のeventActionが存在するかどうかを判定
      EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Quickview Click") AS is_qc,
      EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Add to Cart") AS is_ac,
      EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Product Click") AS is_pc,
      EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Remove from Cart") AS is_rc,
      EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Promotion Click") AS is_prmc
    FROM
      `bigquery-public-data.google_analytics_sample.ga_sessions_*` AS ga
    WHERE
      _TABLE_SUFFIX BETWEEN '20160801' AND '20170630'
  )
  WHERE label IS NOT NULL
)

ステップ3: モデルを確認する

bqml_tutorial データセットに移動し、先ほど作成したモデル model001_nextSessionTransaction を選択肢する。

損失が徐々に減っていっていることが確認できる。

係数のweightを確認する。

また、ML.WEIGHTS関数を使用して、各係数に振られている重みを確認できる。カテゴリ変数と数値変数のweightをまとめ、weightが大きい順に並べてみる。

WITH
  base AS (
  SELECT
    *
  FROM
    ML.WEIGHTS(MODEL `bqml_tutorial.model001_nextSessionTransaction`)),
  num_var AS (
  #数値変数のみ
  SELECT
    processed_input,
    "---" AS category,
    weight
  FROM
    base
  WHERE
    weight IS NOT NULL ),
  cat_var AS (
  #カテゴリ変数のみ
  SELECT
    processed_input,
    category_weights.category,
    COALESCE(base.weight,
      category_weights.weight) AS weight
  FROM
    base,
    base.category_weights AS category_weights )
SELECT
  *
FROM
  num_var
UNION ALL (
  SELECT
    *
  FROM
    cat_var)
ORDER BY
  weight DESC

結果がこちら。

上の方だけ見ると、

is_ac(Add to Cartが発生した)
countryがNicaragua
countryがVenezuela
osがChrome OS
pageviewsが多い

というセッションについて、次のセッションのtransaction発生率が高いと言うモデルになっているみたい。

ステップ4: モデルを評価する

ML.EVALUATE 関数を使用して、性能を評価する。

#standardSQL
SELECT
  *
FROM
  ML.EVALUATE(MODEL  `bqml_tutorial.model001_nextSessionTransaction`,
 (
  SELECT
  #次のセッションが発生したセッションに絞る
    *
  FROM (
    SELECT
      #今回の目的変数。次のセッションでtransactionするかどうか。
      LEAD(IF(totals.transactions >0,1,0 )) OVER(partition by fullVisitorId  ORDER BY visitStartTime)  AS label,
      #以下が組成。このセッションでの行動から作成する。
      IF(totals.transactions IS NULL, 0, 1) AS transaction,
      IFNULL(device.operatingSystem, "") AS os,
      device.isMobile AS is_mobile,
      IFNULL(geoNetwork.country, "") AS country,
      IFNULL(totals.pageviews, 0) AS pageviews,
      IFNULL(totals.bounces, 0) AS bounces,
      IFNULL(totals.timeOnSite , 0) AS timeOnSite,
      IFNULL(totals.newVisits  , 0) AS newVisits,
      EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Quickview Click") AS is_qc,
      EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Add to Cart") AS is_ac,
      EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Product Click") AS is_pc,
      EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Remove from Cart") AS is_rc,
      EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Promotion Click") AS is_prmc
    FROM
      `bigquery-public-data.google_analytics_sample.ga_sessions_*` AS ga
    WHERE
      _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'
  )
  WHERE label IS NOT NULL
)
)

結果がこちら

precisionがNaN、recallが0.0 となっている。
これはすべてのデータについて、目的変数(次のセッションでtransactionが発生するかどうか)が最終的には0で予測されているためです。
一方で、roc_aucは0.822となっている。目的変数(次のセッションでtransactionが発生するかどうか)が1である確率が高いデータ順に並べてデータを取り出すとき、実際にtransactionが発生しているデータを(無作為に取り出すより)効率よく取り出すことができている。

なお、各カラムの詳細については以下をご確認ください。
https://cloud.google.com/bigquery/docs/bigqueryml-analyst-start#run_the_mlevaluate_query

また、transactionするか(=1)しないか(=0)の2値分類ではなく、transactionする確率、しない確率は、以下のクエリで確認できる。predicted_label_probs.prob に、それぞれのラベルの確率が格納されている。

SELECT
  *
FROM
  ML.PREDICT(MODEL  `bqml_tutorial.model001_nextSessionTransaction`,
 (
  SELECT
  #次のセッションが発生したセッションに絞る
    *
  FROM (
    SELECT
      #今回の目的変数。次のセッションでtransactionするかどうか。
      LEAD(IF(totals.transactions >0,1,0 )) OVER(partition by fullVisitorId  ORDER BY visitStartTime)  AS label,
      #以下が組成。このセッションでの行動から作成する。
      IF(totals.transactions IS NULL, 0, 1) AS transaction,
      IFNULL(device.operatingSystem, "") AS os,
      device.isMobile AS is_mobile,
      IFNULL(geoNetwork.country, "") AS country,
      IFNULL(totals.pageviews, 0) AS pageviews,
      IFNULL(totals.bounces, 0) AS bounces,
      IFNULL(totals.timeOnSite , 0) AS timeOnSite,
      IFNULL(totals.newVisits  , 0) AS newVisits,
      EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Quickview Click") AS is_qc,
      EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Add to Cart") AS is_ac,
      EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Product Click") AS is_pc,
      EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Remove from Cart") AS is_rc,
      EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Promotion Click") AS is_prmc
    FROM
      `bigquery-public-data.google_analytics_sample.ga_sessions_*` AS ga
    WHERE
      _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'
  )
  WHERE label IS NOT NULL
)
)

参考
The ML.PREDICT Function | BigQuery | Google Cloud

ステップ5: ML.ROC_CURVE 関数を実行し、閾値を調整する

ステップ4でroc_aucが0.822であることを確認した。これはこのモデルを使えば、次のセッションでtransactionが発生しそうなセッションを効率よく抽出できることを意味する。

ML.ROC_CURVE関数を実行し、実行結果をデータポータルで調べてみる。
The ML.ROC_CURVE Function | BigQuery | Google Cloud

#standardSQL
SELECT
  *
FROM
  ML.ROC_CURVE (MODEL  `bqml_tutorial.model001_nextSessionTransaction`,
 (
  SELECT
  #次のセッションが発生したセッションに絞る
    *
  FROM (
    SELECT
      #今回の目的変数。次のセッションでtransactionするかどうか。
      LEAD(IF(totals.transactions >0,1,0 )) OVER(partition by fullVisitorId  ORDER BY visitStartTime)  AS label,
      #以下が組成。このセッションでの行動から作成する。
      IF(totals.transactions IS NULL, 0, 1) AS transaction,
      IFNULL(device.operatingSystem, "") AS os,
      device.isMobile AS is_mobile,
      IFNULL(geoNetwork.country, "") AS country,
      IFNULL(totals.pageviews, 0) AS pageviews,
      IFNULL(totals.bounces, 0) AS bounces,
      IFNULL(totals.timeOnSite , 0) AS timeOnSite,
      IFNULL(totals.newVisits  , 0) AS newVisits,
      EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Quickview Click") AS is_qc,
      EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Add to Cart") AS is_ac,
      EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Product Click") AS is_pc,
      EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Remove from Cart") AS is_rc,
      EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Promotion Click") AS is_prmc
    FROM
      `bigquery-public-data.google_analytics_sample.ga_sessions_*` AS ga
    WHERE
      _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'
  )
  WHERE label IS NOT NULL
)
)

例えば閾値を 0.03837951 とすると、次のセッションでtransactionするセッションの82%、transactionしないセッションの31%が抽出される。

ステップ6: 採用した閾値で予測をし直す

ML.PREDICTを使って、再度予測をする。
なお、2018-12-31現在日本語ページには書いていないが、ML.PREDICT関数には引数として閾値(threshold)を渡すことができる。また、閾値(threshold)のデフォルト値は0.5となっているらしい。
The ML.PREDICT Function | BigQuery | Google Cloud

#standardSQL
SELECT
  *
FROM
  ML.PREDICT (MODEL  `bqml_tutorial.model001_nextSessionTransaction`,
 (
  SELECT
  #次のセッションが発生したセッションに絞る
    *
  FROM (
    SELECT
      #今回の目的変数。次のセッションでtransactionするかどうか。
      LEAD(IF(totals.transactions >0,1,0 )) OVER(partition by fullVisitorId  ORDER BY visitStartTime)  AS label,
      #以下が組成。このセッションでの行動から作成する。
      IF(totals.transactions IS NULL, 0, 1) AS transaction,
      IFNULL(device.operatingSystem, "") AS os,
      device.isMobile AS is_mobile,
      IFNULL(geoNetwork.country, "") AS country,
      IFNULL(totals.pageviews, 0) AS pageviews,
      IFNULL(totals.bounces, 0) AS bounces,
      IFNULL(totals.timeOnSite , 0) AS timeOnSite,
      IFNULL(totals.newVisits  , 0) AS newVisits,
      EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Quickview Click") AS is_qc,
      EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Add to Cart") AS is_ac,
      EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Product Click") AS is_pc,
      EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Remove from Cart") AS is_rc,
      EXISTS(SELECT h.eventInfo.eventAction FROM UNNEST(ga.hits) as h WHERE h.eventInfo.eventAction = "Promotion Click") AS is_prmc
    FROM
      `bigquery-public-data.google_analytics_sample.ga_sessions_*` AS ga
    WHERE
      _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'
  )
  WHERE label IS NOT NULL
)
, STRUCT(0.03837951) #先程採用した閾値をML.EVALUATEの引数として渡す
)

以上です。

アクセス解析担当者のBigQuery日記

普段はアクセス解析やウェブ広告の運用をしています。主にBigQueryについて、記録していきます。