GoogleAnalyticsのデータをBigQueryで分析する

社内での勉強資料としてのアウトプットになるため、細かい説明は記載しておりません。
ざっくりと理解していただく用、もしくは理解した上でコピペ用に使っていただけますと幸いです。

## 複数テーブルの照会
GoogleAnalyticsのデータをBigQueryへエクスポートしたときに生成されるテーブル名は、ga_sessions_yyyymmdd形式で1日ごとに生成されています。
このように類似した名前のテーブルが複数存在する場合、ワイルドカードテーブルを用いることで複数テーブルを照会することができます。

例えば、2019年2月1日から2019年2月7日までの期間でデータ抽出を行いたい場合は、以下のように記述します。

``` sql
SELECT
  *
FROM
  `{プロジェクトID}.{データセット名}.ga_sessions_*`
WHERE 
  _TABLE_SUFFIX BETWEEN '20190201'
  AND '20190207'
```

## RECORD型データをフラットにする
エクスポートしたデータの中にRECORD型と呼ばれるデータ型が存在します。
ユーザーが閲覧したページデータが格納されているhits.page.pagePathカラムもRECORD型データの1つで、

<table>
  <tr>
    <th>fullVisitorId</th>
    <th>hits.page.pagePath</th>
  </tr>
  <tr>
    <td rowspan=4>1234567</td>
    <td>/</td>
  </tr>
  <tr>
    <td>/search/shop_list/</td>
  </tr>
  <tr>
    <td>/shop/index/</td>
  </tr>
  <tr>
    <td>/shop/access/</td>
  </tr>
</table>

のような形式で保存されています。
この状態だと扱いづらいため、UNNEST関数を用いてフラットにすることをオススメします。

``` sql
SELECT
  fullVisitorId,
  hits.page.pagePath
FROM
  `{プロジェクトID}.{データセット名}.ga_sessions_*`, UNNEST(hits) AS hits
WHERE 
  _TABLE_SUFFIX BETWEEN '20190201'
  AND '20190207'
```

こうすることで、

<table>
  <tr>
    <th>fullVisitorId</th>
    <th>hits.page.pagePath</th>
  </tr>
  <tr>
    <td>1234567</td>
    <td>/</td>
  </tr>
  <tr>
    <td>1234567</td>
    <td>/search/shop_list/</td>
  </tr>
  <tr>
    <td>1234567</td>
    <td>/shop/index/</td>
  </tr>
  <tr>
    <td>1234567</td>
    <td>/shop/access/</td>
  </tr>
</table>

という状態に変換されます。

## ユニークユーザー数、ページビュー数、ユニークセッション数
fullVisitorId：ブラウザのクッキーに付与されるユニークID。
visitId：セッションID

ユニークユーザー数はGoogleAnalytics上で「ユーザー」という指標で表現されています。
しかし、実際にはブラウザに付与しているユニークIDの数のため、厳密には「ユーザー」ではなく「ユニークブラウザ」が正しい表現となります。
今回は、fullVisitorIdにDISTINCTをかけてカウントした数値をユニークユーザー数、かけずにカウントした数値をページビュー数、visitIdにDISTINCTをかけてカウントした数値をユニークセッション数としています。

また、ページ閲覧に対する指標のため、ヒットタイプを"PAGE"としています。

``` sql
SELECT
  COUNT(DISTINCT fullVisitorId) AS unique_user,
  COUNT(fullVisitorId) AS page_view,
  COUNT(DISTINCT visitId) AS unique_session
FROM
  `{プロジェクトID}.{データセット名}.ga_sessions_*`, UNNEST(hits) AS hits
WHERE 
  _TABLE_SUFFIX BETWEEN '20190201'
  AND '20190207'
  AND hits.type = 'PAGE'
```

## ページ、イベント
``` sql
SELECT
  hits.type,
  hits.page.pagePath,
  hits.eventInfo.eventCategory,
  hits.eventInfo.eventAction,
  hits.eventInfo.eventLabel
FROM
  `{プロジェクトID}.{データセット名}.{テーブル名}`, UNNEST(hits) AS hits  
WHERE 
  _TABLE_SUFFIX BETWEEN '20190201'
  AND '20190207'
```

## 流入元
``` sql
SELECT
  trafficSource.source AS utm_source,
  trafficSource.medium AS utm_medium,
  trafficSource.campaign AS utm_campaign
FROM
  `{プロジェクトID}.{データセット名}.{テーブル名}`, UNNEST(hits) AS hits
WHERE 
  _TABLE_SUFFIX BETWEEN '20190201'
  AND '20190207'
```

## 日、週、月
``` sql
SELECT
  FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS day,
  DATE_SUB(CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE), INTERVAL EXTRACT(DAYOFWEEK FROM CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE)) DAY) AS saturday,
  DATE_SUB(CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE), INTERVAL (EXTRACT(DAYOFWEEK FROM CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE)) - 1) DAY) AS sunday,
  DATE_SUB(CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE), INTERVAL (EXTRACT(DAYOFWEEK FROM CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE)) - 2) DAY) AS monday,
  DATE_SUB(CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE), INTERVAL (EXTRACT(DAYOFWEEK FROM CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE)) - 3) DAY) AS tuesday,
  DATE_SUB(CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE), INTERVAL (EXTRACT(DAYOFWEEK FROM CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE)) - 4) DAY) AS wednesday,
  DATE_SUB(CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE), INTERVAL (EXTRACT(DAYOFWEEK FROM CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE)) - 5) DAY) AS thursday,
  DATE_SUB(CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE), INTERVAL (EXTRACT(DAYOFWEEK FROM CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE)) - 6) DAY) AS friday,
  FORMAT_TIMESTAMP('%Y-%m', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS month
FROM
  `{プロジェクトID}.{データセット名}.{テーブル名}`, UNNEST(hits) AS hits
WHERE 
  _TABLE_SUFFIX BETWEEN '20190201'
  AND '20190207'
```

## カスタムディメンション
hits.customDimensionsカラムにデータが保存されており、hits.page.pagePathと同様RECORD型のデータになります。
ただ、やっかいなデータ構造をしており、

<table>
  <tr>
    <th rowspan=2>fullVisitorId</th>
    <th colspan=2>hits.customDimensions</th>
  </tr>
  <tr>
    <td>hits.customDimensions.index</td>
    <td>hits.customDimensions.value</td>
  </tr>
  <tr>
    <td rowspan=3>1234567</td>
    <td>1</td>
    <td>abc</td>
  </tr>
  <tr>
    <td>2</td>
    <td>def</td>
  </tr>
  <tr>
    <td>3</td>
    <td>ghi</td>
  </tr>
</table>

といった具合に、行列それぞれで入れ子の形式をとっています。
そのため、UNNEST(hits) AS hitsにより、

<table>
  <tr>
    <th rowspan=2>fullVisitorId</th>
    <th colspan=2>hits.customDimensions</th>
  </tr>
  <tr>
    <td>hits.customDimensions.index</td>
    <td>hits.customDimensions.value</td>
  </tr>
  <tr>
    <td>1234567</td>
    <td>1</td>
    <td>abc</td>
  </tr>
  <tr>
    <td>1234567</td>
    <td>2</td>
    <td>def</td>
  </tr>
  <tr>
    <td>1234567</td>
    <td>3</td>
    <td>ghi</td>
  </tr>
</table>

とした上で、以下のようにサブクエリを用いることで、それぞれの値を抽出することができます。


``` sql
SELECT
  (SELECT MAX(IF(index=1, value, null)) FROM UNNEST(hits.customDimensions)) AS custom_dimension_1,
  (SELECT MAX(IF(index=2, value, null)) FROM UNNEST(hits.customDimensions)) AS custom_dimension_2,
  (SELECT MAX(IF(index=3, value, null)) FROM UNNEST(hits.customDimensions)) AS custom_dimension_3
FROM
  `{プロジェクトID}.{データセット名}.{テーブル名}`, UNNEST(hits) AS hits
WHERE 
  _TABLE_SUFFIX BETWEEN '20190201'
  AND '20190207'
```