はじめに
ML Study Jams : Machine Learning 初心者向けトレーニングプログラム
を見かけたのでやってみることにしたメモ
BQML で分類モデルを使用して訪問者の購入を予測する
BQMLって何かなって思ったらBigQueryMLのことか
最初の方だけ日本語
BQML で分類モデルを使用して訪問者の購入を予測する
目標
このラボでは、次のタスクの実行方法について学びます。
BigQuery を使用して一般公開データセットを見つける
e コマース データセットをクエリし、探索する
バッチ予測に使用するトレーニングと評価のデータセットを作成する
分類（ロジスティック回帰）モデルを BQML に作成する
機械学習モデルの性能を評価する
訪問者が購入する見込みを予測し、ランクを付ける
とりあえずこれができるようになるらしいのでやってみる
これから以降は英語(つらい)
Predict Visitor Purchases with a Classification Model in BQML
Access the course dataset
データセットにアクセスする
BigQuery内で探したけど見つからなかったのでhereと書いてあるところを踏んだら自動的にとんだ
スキーマでどういうフィールドで型などがみれる
プレビューで具体的なデータの中身がみれる
Explore ecommerce data
シナリオ
データアナリストチームは、eコマースWebサイトのGoogle AnalyticsログをBigQueryにエクスポートし、探索するためのすべての生のeコマース訪問者セッションデータの新しいテーブルを作成しました。このデータを使用して、いくつかの質問に答えようとします。
問い
私たちのウェブサイトを訪れた総訪問者のうち、何％が購入しましたか？
Query Editorをひらいて、新しいクエリフィールドに下記のように書いてください
#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
SQL解読
#standardSQL
スタンダードなSQLで書きますよというおまじないみたいなものだと理解している
WITH visitors AS(
SELECT
COUNT(DISTINCT fullVisitorId) AS total_visitors
FROM `data-to-insights.ecommerce.web_analytics`
),
data-to-insights.ecommerce.web_analyticsというテーブルからfullVisitorIdを被りなしでカウントしてtotal_visitorsという名前の新しいカラムを作る。総訪問者数をだしています。
これをvisitorsという一時テーブルを定義している
WITH 句ってなんだと思ったので参考文献
Amazon Redshift WITH 句
ちなみにfullVisitorIdとは何かというと、プレビューで検索すると確認できる
一意なユーザーIDということなのでしょう(多分)
purchasers AS(
SELECT
COUNT(DISTINCT fullVisitorId) AS total_purchasers
FROM `data-to-insights.ecommerce.web_analytics`
WHERE totals.transactions IS NOT NULL
)
今度はdata-to-insights.ecommerce.web_analyticsというテーブルから、totals.transactionsの項目がnullじゃないという条件でfullVisitorIdを被りなしで数を数えてtotal_purchasersという名前のカラムを作ってpurchasersという一時テーブルを作っています。
購入者のID一覧表をだしたようです。
SELECT
total_visitors,
total_purchasers,
total_purchasers / total_visitors AS conversion_rate
FROM visitors, purchasers
一時テーブルで作ったvisitors, purchasersをつかって
total_visitorsとtotal_purchasersとtotal_purchasers / total_visitors
(購入者/訪問者）をconversion_rateというカラム名をつけて計算したものが2.69%だったということでした。
問い
トップ5の販売商品は何ですか？
クエリエディタに下記のように記入してください
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 |
| 1 | Nest® Learning Thermostat 3rd Gen-USA - Stainless Steel | Nest-USA | 17651 | 870976.95 |
| 2 | Nest® Cam Outdoor Security Camera - USA | Nest-USA | 16930 | 684034.55 |
| 3 | Nest® Cam Indoor Security Camera - USA | Nest-USA | 14155 | 548104.47 |
| 4 | Nest® Protect Smoke + CO White Wired Alarm-USA | Nest-USA | 6394 | 178937.6 |
SQL解読
UNNEST(hits) AS h,
UNNEST(h.product) AS p
data-to-insights.ecommerce.web_analyticsというテーブルから取り出す
条件のUNNESTというのは階層構造になったデータを扱いやすくするために用いる
たとえば今回例に上がっているhitsというのは今回のお手本では下記の図のように階層構造的なデータ構造になっている。最終的に取り出したい値は、このhitsの中にあるproductの値のなかのv2ProductNameやv2ProductCategoryである
上記のようにUNNESTを使って別名を付けている
GROUP BY 1, 2
これはp.v2ProductNameとp.v2ProductCategoryそれぞれグループ化して集計している
ORDER BY revenue DESC
LIMIT 5;
revenue(収入)の多い順番から並べて上から５番目までのテーブルをつくっている
Question: How many visitors bought on subsequent visits to the website?
問い：その後のWebサイトへの訪問で、何人の訪問者が購入しましたか。
記入するクエリ
# 帰りの訪問で購入した訪問者（最初に購入した人もいるかもしれない）
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
帰ってくる結果
| 行 | total_visitors | will_buy_on_return_visit |
| 1 | 729848 | 0 |
| 2 | 11873 | 1 |
SQL解読
全体を２つに分けて考える
前半部分all_visitor_statsの一時テーブルをつくる
WITH all_visitor_stats AS (
SELECT
fullvisitorid, # 741,721 ユニーク訪問者
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
)
左カラムのリソースから下記のデータを探しだす
data-to-insights.ecommerce.web_analytics
web_analyticsのテーブルのカラムを見てみると下記の項目があることを確認した。
totals.transactionsは取引回数を示し,totals.newVisitsは流入回数によって数が異なるようだ。
前半のクエリではdata-to-insights.ecommerce.web_analyticsテーブルから、fullvisitoridのユニーク訪問者とtotals.transactions=取引回数が0より多いか,
totals.newVisits=訪問回数がNullか数を数え、それが０より多ければ1 0であれば 0のカラムをつくる.
またこのカラムはwill_buy_on_return_visitという別名をつける
最後にGROUP BY fullvisitorid:ユニーク訪問者でまとめる
ここまでのSQL結果
このような感じでfullvisitoridごとに条件に当てはまるか1か0のカラムを作成している。
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
*
FROM all_visitor_stats
| 行 | fullvisitorid | will_buy_on_return_visit | |
|---|---|---|---|
| 1 | 984664620436210937 | 0 | |
| 2 | 5191483628527152394 | 0 | |
| 3 | 3382042356771002552 | 0 |
後半のSQLは前半で作ったクエリに対して、下記のSQLを実行する
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の被りなしのカラムをつくり、カラム名はtotal_visitorsとして数える
will_buy_on_return_visitでそれぞれまとめる。
その後のWebサイトへの訪問で、何人の訪問者が購入したか結果を出力できた
この結果から何がわかったのか
結果を分析すると、（11873/729848）=総訪問者数の1.6％がWebサイトに戻って購入することがわかります。 これには、最初のセッションで購入してから戻ってきて再び購入した訪問者のサブセットが含まれます。
Question: What are some of the reasons a typical ecommerce customer will browse but not buy until a later visit?
問:典型的なeコマースの顧客が閲覧して後で購入するまで購入しない理由のいくつかは何ですか？
回答：正しい答えはありませんが、一般的な理由の1つは、最終的に購入を決定する前に、さまざまなeコマースサイト間でショッピングを比較することです。 これは、決定する前に顧客がかなりの先行研究と比較を必要とする高級車には非常に一般的ですが（ただし、自動車の購入を考える）、このサイトの商品（Tシャツ、アクセサリーなど）にも当てはまります。
オンラインマーケティングの世界では、最初の訪問の特性に基づいてこれらの将来の顧客を識別してマーケティングすると、コンバージョン率が上がり、競合サイトへの流出が少なくなります。