#対象の人
SQL初心者の方、
これから会社などでGoogle Analytics 360 + BigQueryを使っていく事になった人
#目標
直帰率でINNER JOINを学ぶ。
出来上がりはこんな感じのものになります。
| 行 | Date | BouceRate |
|---|---|---|
| 1 | 2021-01-01 | 73.0 |
| 2 | 2021-01-02 | 72.0 |
| 3 | 2021-01-03 | 73.0 |
| 4 | 2021-01-04 | 73.0 |
| 5 | 2021-01-05 | 73.0 |
| 6 | 2021-01-06 | 72.0 |
直帰率は 直帰数 ÷ セッション数 で出せます。
実はこれ一つのテーブルでは出せなくて
もう一つテーブルを用意する必要があります。
具体的に見ていきましょう
#作業
##1.セッションを出す
SELECT
date AS Date,
COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitNumber AS string))) AS Sessions
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`, UNNEST(hits) AS hits
--パブリックデータセットの bigquery-public-data:google_analytics_sample を使ってます
WHERE
_TABLE_SUFFIX BETWEEN '20210101' AND '20210106'
AND hits.type = 'PAGE'
AND hits.isEntrance = TRUE
GROUP BY
Date
ORDER BY
Date ASC
;
###結果
| 行 | Date | Sessions |
|---|---|---|
| 1 | 2021-01-01 | 410662 |
| 2 | 2021-01-02 | 406283 |
| 3 | 2021-01-03 | 408354 |
| 4 | 2021-01-04 | 383339 |
| 5 | 2021-01-05 | 352970 |
| 6 | 2021-01-06 | 308588 |
これで日別のセッション数が出せました。
直帰率を出すには他に直帰数を出す必要があります。
##2.直帰数を出す
SELECT
date AS Date,
COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitNumber AS string))) AS Bounces
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`, UNNEST(hits) AS hits
--パブリックデータセットの bigquery-public-data:google_analytics_sample を使ってます
WHERE
_TABLE_SUFFIX BETWEEN '20210101' AND '20210106'
AND hits.type = 'PAGE'
AND totals.pageviews = 1
GROUP BY
Date
ORDER BY
Date ASC
;
###結果
| 行 | Date | Bounces |
|---|---|---|
| 1 | 2021-01-01 | 300153 |
| 2 | 2021-01-02 | 294170 |
| 3 | 2021-01-03 | 297509 |
| 4 | 2021-01-04 | 278443 |
| 5 | 2021-01-05 | 256012 |
| 6 | 2021-01-06 | 221387 |
直帰数(Bounces)は出し方はセッションと同じです。
fullVisitorId(ユニークユーザーID)とvisitNumber(ユーザーのセッション数)を文字列にしたものを
CONCATします。
ただ条件が違います。
条件を指定するWHERE句の「AND totals.pageviews = 1」。
totals.pageviewsというのはセッション中の合計ページビュー数の事です。
これが1つまり1ページしか見てない状態が直帰になります。
COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitNumber AS string)))というのは、
条件に「AND totals.pageviews = 1」にするかどうかでセッションになり直帰数にもなるので
同一テーブル内でこの2つをだせないという問題が生じます。
そこで2つのテーブルを連結させてしまうという手段を使います。
##3.直帰率を出す
SELECT
CONCAT(SUBSTR(Date_t1, 0, 4), '-', SUBSTR(Date_t1, 5, 2), '-', SUBSTR(Date_t1, 7, 2)) AS Date,
round(Bounces/Sessions * 100,0) AS BounceRate
FROM
(
--Sessions
SELECT
date AS Date_t1,
COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitNumber AS string))) AS Sessions
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`, UNNEST(hits) AS hits
--パブリックデータセットの bigquery-public-data:google_analytics_sample を使ってます
WHERE
_TABLE_SUFFIX BETWEEN '20210101' AND '20210106'
AND hits.type = 'PAGE'
AND hits.isEntrance = TRUE
GROUP BY
Date
)
INNER JOIN
(
--Bounces
SELECT
date AS Date_t2,
COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitNumber AS string))) AS Bounces
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`, UNNEST(hits) AS hits
--パブリックデータセットの bigquery-public-data:google_analytics_sample を使ってます
WHERE
_TABLE_SUFFIX BETWEEN '20210101' AND '20210106'
AND hits.type = 'PAGE'
AND totals.pageviews = 1
GROUP BY
Date
)
ON
Date_t1 = Date_t2
ORDER BY
Date ASC
;
###結果
| 行 | Date | BouceRate |
|---|---|---|
| 1 | 2021-01-01 | 73.0 |
| 2 | 2021-01-02 | 72.0 |
| 3 | 2021-01-03 | 73.0 |
| 4 | 2021-01-04 | 73.0 |
| 5 | 2021-01-05 | 73.0 |
| 6 | 2021-01-06 | 72.0 |
INNER JOINとは内部結合。
2つのテーブルをそれぞれ結合の対象となるカラムを指定し、データを結合するものです。
ご覧のようにFROM句に2つのテーブルが存在します。
それをINNER JOINで結合するという形になります。
テーブル1
INNER JOIN
テーブル2
ON
結合の対象となるカラム
結合の対象となるカラムはそれぞれのカラムに同じ値が格納されているもの今回ならDate_t1 = Date_t2を使用します。
セッションが含まれるテーブルと直帰数の含まれるテーブルを紐づけて1つのテーブルにすることにより同じテーブル内にセッションと直帰数の2つが含まれている状態を作り出す事により同一テーブル内からセッションと直帰数を取得できるようになるというイメージで考えればわかりやすいかと思います。
結合は覚えておくと
例えば天気のデータ(日付、晴れ、雲り、雨、雪などが入ったデータ)と
お店の売上のデータ(日付、売上金額などが入ったデータ)を持っているとして
2つを同じ値の日付で結合すれば天気と売上の関係などを分析できるようになるなど、
別個のデータを繋げて使うときなどに便利です。