BQML で分類モデルを使用して訪問者の購入を予測するをやってみた(その１)

## はじめに

[ML Study Jams : Machine Learning 初心者向けトレーニングプログラム
](https://events.withgoogle.com/ml-study-jams-2nd-beginners/)
を見かけたのでやってみることにしたメモ

## BQML で分類モデルを使用して訪問者の購入を予測する
BQMLって何かなって思ったらBigQueryMLのことか

最初の方だけ日本語
[BQML で分類モデルを使用して訪問者の購入を予測する](https://www.qwiklabs.com/focuses/1794?catalog_rank=%7B%22rank%22%3A1%2C%22num_filters%22%3A2%2C%22has_search%22%3Atrue%7D&locale=ja&parent=catalog)

## 目標
このラボでは、次のタスクの実行方法について学びます。

```
BigQuery を使用して一般公開データセットを見つける

e コマース データセットをクエリし、探索する

バッチ予測に使用するトレーニングと評価のデータセットを作成する

分類（ロジスティック回帰）モデルを BQML に作成する

機械学習モデルの性能を評価する

訪問者が購入する見込みを予測し、ランクを付ける
```

とりあえずこれができるようになるらしいのでやってみる

これから以降は英語(つらい)
[Predict Visitor Purchases with a Classification Model in BQML](https://www.qwiklabs.com/focuses/1794?catalog_rank=%7B%22rank%22%3A1%2C%22num_filters%22%3A2%2C%22has_search%22%3Atrue%7D&locale=ja&parent=catalog)

## Access the course dataset
データセットにアクセスする
BigQuery内で探したけど見つからなかったので[here](https://support.google.com/analytics/answer/3437719?hl=en)と書いてあるところを踏んだら自動的にとんだ

スキーマでどういうフィールドで型などがみれる
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/176479/017a1eb1-d5f6-ba61-cb10-53fd111544fb.png)

プレビューで具体的なデータの中身がみれる

## Explore ecommerce data

### シナリオ
データアナリストチームは、eコマースWebサイトのGoogle AnalyticsログをBigQueryにエクスポートし、探索するためのすべての生のeコマース訪問者セッションデータの新しいテーブルを作成しました。このデータを使用して、いくつかの質問に答えようとします。

### 問い
私たちのウェブサイトを訪れた総訪問者のうち、何％が購入しましたか？

Query Editorをひらいて、新しいクエリフィールドに下記のように書いてください

```sql
#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
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/176479/d913f00b-a948-f280-ee8a-01b14c58a2ee.png)

### 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 句](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_WITH_clause.html)

ちなみに`fullVisitorId`とは何かというと、プレビューで検索すると確認できる
一意なユーザーIDということなのでしょう(多分)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/176479/d5af4fdc-18df-43e8-9bca-bb0a363998de.png)


```
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 |　5 |Nest® Protect Smoke + CO White Battery Alarm-USA |Nest-USA |6340|178572.4 |


### SQL解読

```
UNNEST(hits) AS h,
UNNEST(h.product) AS p
```

`data-to-insights.ecommerce.web_analytics`というテーブルから取り出す
条件のUNNESTというのは階層構造になったデータを扱いやすくするために用いる
たとえば今回例に上がっているhitsというのは今回のお手本では下記の図のように階層構造的なデータ構造になっている。最終的に取り出したい値は、この`hits`の中にある`product`の値のなかの`v2ProductName`や`v2ProductCategory`である

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/176479/82dd2bbd-0ecd-eefe-5922-10e59657caa6.png)

上記のように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`のテーブルのカラムを見てみると下記の項目があることを確認した。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/176479/2e760e9c-35d3-6ba8-2fd3-d1f0c3676b4b.png)

`totals.transactions`は取引回数を示し,`totals.newVisits`は流入回数によって数が異なるようだ。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/176479/d2a97744-8866-e8bd-8a85-433f8284efcf.png)

前半のクエリでは`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シャツ、アクセサリーなど）にも当てはまります。

オンラインマーケティングの世界では、最初の訪問の特性に基づいてこれらの将来の顧客を識別してマーケティングすると、コンバージョン率が上がり、競合サイトへの流出が少なくなります。

続きはこちら
[BQML で分類モデルを使用して訪問者の購入を予測するをやってみた(その2)](https://qiita.com/nakagawa1017/items/c97d43270ee331126a0a)