Google Analytics 360 + BigQueryのド基本を学ぶ

#対象の人
これから会社などでGoogle Analytics 360 + BigQueryを使っていく事になった人が最初に任されがちな依頼例を実際に
Google Analytics 360用のSQLを書いて紹介します。

#目標
では今回はこんな感じのものが出せるようになるのを目指してみましょう

<table>
<thead>
<tr>
<th>行</th>
<th>Date</th>
<th>PV</th>
<th>Sessions</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>2021-01-01</td>
<td>2518630</td>
<td>341160</td>
</tr>
<tr>
<td>2</td>
<td>2021-01-02</td>
<td>2617295</td>
<td>351097</td>
</tr>
<tr>
<td>3</td>
<td>2021-01-03</td>
<td>2517898</td>
<td>346030</td>
</tr>
<tr>
<td>4</td>
<td>2021-01-04</td>
<td>2624950</td>
<td>351061</td>
</tr>
<tr>
<td>5</td>
<td>2021-01-05</td>
<td>2515262</td>
<td>342054</td>
</tr>
<tr>
<td>6</td>
<td>2021-01-06</td>
<td>2417454</td>
<td>331461</td>
</tr>
</tbody>
</table>

#作業
##1.PVを出す

```SQL:standardSQL
SELECT
 COUNT(fullVisitorId) AS PV
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'
;
```
###結果
<table>
<thead>
<tr>
<th>行</th>
<th>PV</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>24652378</td>
</tr>
</tbody>
</table>

hits.type = ‘PAGE’ に絞って
fullVisitorId(ユニーク ユーザー ID)をCOUNTすることによってPVを出せます。
2021-01-01～2021-01-06までの期間の全てのPVを出してます。
hitsはネストされたRECORD型のカラムになってるので、UNNEST関数でフラットにします。

##2.日付を出す
```SQL:standardSQL
SELECT
 CONCAT(SUBSTR(Date, 0, 4), '-', SUBSTR(Date, 5, 2), '-', SUBSTR(Date, 7, 2)) AS Date,
 COUNT(fullVisitorId) AS PV
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'
GROUP BY
  Date
ORDER BY
  Date ASC
;

```
###結果
<table>
<thead>
<tr>
<th>行</th>
<th>Date</th>
<th>PV</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>2021-01-01</td>
<td>2518630</td>
</tr>
<tr>
<td>2</td>
<td>2021-01-02</td>
<td>2617295</td>
</tr>
<tr>
<td>3</td>
<td>2021-01-03</td>
<td>2517898</td>
</tr>
<tr>
<td>4</td>
<td>2021-01-04</td>
<td>2624950</td>
</tr>
<tr>
<td>5</td>
<td>2021-01-05</td>
<td>2515262</td>
</tr>
<tr>
<td>6</td>
<td>2021-01-06</td>
<td>2417454</td>
</tr>
</tbody>
</table>

さっきのは2021-01-01～2021-01-06までの合計のPVでしたが今回は日別のPVになります。
GROUP BY句でDateをグループ化することにより日別に分けられたPVが出せます。
日付はCONCAT関数を使って年-月-日という形にしてます。
昇順、降順はORDER BY句を使います。
今回はDate(日付)を昇順(ASC)にしてます。

##3.セッションを出す
```SQL:standardSQL
SELECT
 CONCAT(SUBSTR(Date, 0, 4), '-', SUBSTR(Date, 5, 2), '-', SUBSTR(Date, 7, 2)) AS Date,
 COUNT(fullVisitorId) AS PV,
 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'
GROUP BY
  Date
ORDER BY
  Date ASC
;
```

###結果
<table>
<thead>
<tr>
<th>行</th>
<th>Date</th>
<th>PV</th>
<th>Sessions</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>2021-01-01</td>
<td>2518630</td>
<td>341160</td>
</tr>
<tr>
<td>2</td>
<td>2021-01-02</td>
<td>2617295</td>
<td>351097</td>
</tr>
<tr>
<td>3</td>
<td>2021-01-03</td>
<td>2517898</td>
<td>346030</td>
</tr>
<tr>
<td>4</td>
<td>2021-01-04</td>
<td>2624950</td>
<td>351061</td>
</tr>
<tr>
<td>5</td>
<td>2021-01-05</td>
<td>2515262</td>
<td>342054</td>
</tr>
<tr>
<td>6</td>
<td>2021-01-06</td>
<td>2417454</td>
<td>331461</td>
</tr>
</tbody>
</table>

fullVisitorId(ユニークユーザーID)とvisitNumber(ユーザーのセッション数)を文字列にしたものを
CONCATすることによりユニークユーザーIDとセッション数を紐づき
ユニークユーザーIDあたりのセッション数がわかります。そのユニーク数をカウントしたのがセッション数になります。