Google Analytics Standard版のトラッキングデータをBigQueryにためてLookerで分析をしたい 01

# 背景や課題感
Google Analytics Standard版（以下GA）を使っているので、BigQueryにデータ連携ができない。（[こちら](https://support.google.com/analytics/answer/3437618?hl=ja)を参照）
だが、GAのデータをBigQueryにためて、[Looker](https://looker.fbpp.jp/)でビジュアライズしたい。
ゆくゆくは、トラッキングデータのorder_idを受注データベースのorder_idと紐づけて、より細かな分析を行なっていきたい。

# この記事の想定対象者
技術よりのビジネスサイドの方。
分析基盤を構築したいと考えているが、どういう構成したらいいのかお悩みの方。
Google Cloud Platform（以下GCP）を利用して、手軽に始めたいとお考えの方。
または、それに準じる方。

あとは、ビジネスサイドとエンジニアサイドの橋渡しになるような記事になればと考えています。

# 前提条件
- Google Analytics Standard版を利用
- Web SiteはSPA（ Single Page Application ）

# ざっくりな構成図
![Web Analytics Architecture.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70138/e7c03b52-a6c4-635c-c240-965e884ee821.png)

簡単にまとめると以下の流れになります。

1. [Web Site](https://www.fbpp.jp/) からイベントデータをGAに送信する
2. [Reporting API](https://developers.google.com/analytics/devguides/reporting/core/v4/?hl=ja)を利用して、GAからデータを引っ張ってくる
3. 引っ張ってきたデータをBigQueryにロードする
4. GA取得＆BigQueryロードの日次バッチ処理
5. BigQueryにためたデータを[Looker](https://looker.fbpp.jp/)で結合し、分析用の派生テーブルを作成する
6. Dashbordを作るもよし、アドホック分析をしてもよし、データを料理し放題

それでは各フェーズを細かく説明していきます。

# 1. Web Site からイベントデータをGAに送信する
以下の流れにそって説明します。

1. イベントデータの概念
2. イベントデータの定義
3. イベントデータ送信処理の設計・実装

イベントデータを送信する前に、「何を送信するのか？」を定義する必要があります。
ただ、その前にトラッキングのイベントデータの概念を理解する必要があります。

## イベントデータの概念
![Web Tracking Structure.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70138/38c6c2ac-3c18-0d07-33f5-10466f869f1a.png)

ユーザー `User` は様々なデバイスを用いて、Web SiteやAppsに訪問 `Session` し、様々な行動 `Hit` をしています。

例えば…

- 通勤中は電車の中からスマホで
- 勤務中は、デスクトップPCで

などです。
上記の概念を元に、どのデータが必要なのかを定義していきます。

## イベントデータの定義からGA実装まで
イベントデータの定義については、以下のデータが必要になります。（詳細は別記事にする予定）

いずれもGAの標準のディメンションに存在していないので、カスタムディメンションを作成する必要があります。
また、下記以外のディメンションについては、GAから引っ張ってこられるので詳細は [Reporting APIのリファレンス](https://developers.google.com/analytics/devguides/reporting/core/dimsmets)を参照し、適時決定していきます。

|  # | ディメンション名 | 説明 | 参考例 |
| :--- | :--- | :--- | :--- |
|  1 | hit_id | トラッキングデータをRAWデータとして扱うためのID<br/>各ヒットととりまとめるために必要<br/>timestampとuser_idもしくはclient_idと組み合わせることで一意とする | timestamp + ( user_id or cliend_id ) |
|  2 | user_id | 各サービスで利用しているユーザーID<br/>クロスデバイストラッキングで必要<br/>ない場合は、cliend_idで代用（ただし、クロスデバイストラッキングはできない） |  |
|  3 | client_id | GAでブラウザごとに吐き出されるID<br/>session_idを作成するときに必要 | Cookieに保存されている「_ga」を取得、送信する |
|  4 | datetime | GAだと秒のディメンションがないので、ヒットのタイミングでGAに送るように仕込んでおくと何かと便利 | `yyyy-MM-dd HH:mm:ss` を取得し、送信する |
|  5 | session_sequence_number | 各セッションにおける処理順番号<br/>DWH上でシーケンス番号の処理をしてもいいが、ヒットのタイミングでシーケンスナンバーを送るように仕込んでおくと、DWHのリソースを食わなくていい（データが大量になるほど）<br/>ただ、GAの仕組み上、クライアントサイドに依存する仕組みなので、データの品質やDWHのリソース等のトレードオフで、DWH側で処理する判断をしても良い | Cookieで処理をする |
|  6 | order_id | 注文時に発行されるID |  |

※ ちなみに、GAから引っ張ってこられるディメンションは、ほぼ全て引っ張ってきています。

## イベントデータ送信処理の設計・実装
弊社では、Web SiteがSPA（Single Page Application）で構築されている想定で送信処理を設計・実装していきます。
また、[gtag.js](https://developers.google.com/analytics/devguides/collection/gtagjs/?hl=ja)を利用しているので、そちらをカスタマイズする方針で設計・実装をしています。

### 実装の大まかな流れ
1. [GA](https://analytics.google.com/)で、カスタムディメンションを作成（作成するディメンションは上記表の通り）
2. [gtag.js](https://developers.google.com/analytics/devguides/collection/gtagjs/?hl=ja)で、カスタムディメンションをGAに送信できるようにコードをカスタマイズ
3. [Googleタグマネージャー](https://tagmanager.google.com/)で、発火タイミングの調整

#### 1. GAで、カスタムディメンションを作成
作成するカスタムディメンションは以下の通り。
<img width="1440" alt="カスタムディメンション.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70138/8b6696ef-a169-f5a9-a8d0-e6b11ed8c872.png">

|  # | カスタムディメンション名 | 範囲 |
| :--- | :--- | :--- |
|  1 | hitId | ヒット |
|  3 | clientId | ユーザー |
|  4 | datetime | ヒット |
|  5 | sessionNumber | ヒット |
| 6 | order_id | ヒット |

インデックスはのちに使います。

#### 2. gtag.jsで、カスタムディメンションをGAに送信するコード
ここで、上記のカスタムディメンションの他に `page_title` と `page_path` をイベントが起きた際にURLのハッシュ以降を取得してGAに送信できるようにもする必要があります。
Googleタグマネージャーとの複合技になるので、ここでは説明は割愛しますが、別途説明ページを設ける予定です。 -> [Google Analyticsのgtag.js(カスタムディメンション)とGoogleタグマネージャーで、SPA(Single Page Application)のWebSiteをトラッキングできるようにする](https://qiita.com/fbp-yamashita/items/8b321cd974a560a52268)を作成しましたので、参考にしていただければと思います。

#### 3. Googleタグマネージャーで、タグの発火タイミングの調整
こちらについても別ページに説明を設ける予定です。
いずれにしてもSPAのタグ発火タイミングはGoogleタグマネージャーを利用します。

## イベントデータまとめ
以上で、イベントデータの定義からGA実装までの流れです。

# 2. Reporting APIを利用して、GAからデータを引っ張ってくる
GAからデータを引っ張ってくるには、[Reporting API](https://developers.google.com/analytics/devguides/reporting/core/v4/?hl=ja)を利用するのですが、有効化しないと使えません。
以下の流れにそって説明します。

*Reporting API利用の事前準備*
1. Reporting APIを利用するためにGCPのプロジェクトを作成
2. Reporting APIの有効化
3. GCPサービスアカウントの作成
4. サービスアカウントのJSON形式の認証キーをダウンロード
5. サービスアカウントをGAに登録

*インスタンスの用意・環境構築*
1. GCPで作成したプロジェクト配下で、インスタンスを立ち上げる
2. 立ち上げたインスタンスに以下をインストール

- Node.js
  - @google-cloud/datastore
  - @google-cloud/storage
- gcloudコマンドラインツール

3. サービスアカウントで作成したJSON形式の認証キーをインスタンスにアップロード

*BigQueryの用意*
1. GAのデータをためておくBigQueryを用意

*実装*
1. Reporting APIからデータを引っ張る処理の実装

それでは、詳細の説明に入ります。

## API利用の事前準備
### 1. Reporting APIを利用するためにGCPのプロジェクトを作成
まずは、GCPでプロジェクトを持っていない場合はプロジェクトを作成します。
もしくは、プロジェクトをすでに持っていて、そのプロジェクトは以下の環境を利用する場合は、次の `Reporting APIの有効化` に進んでください。
インスタンス、BigQueryを作成するための元となるプロジェクトです。
[GCP](https://console.cloud.google.com/getting-started)にアクセスし、プロジェクトを作成します。

<img width="1440" alt="01.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70138/5ba65fdd-be34-d63c-232d-1e7e1a84499e.png">

<img width="906" alt="02.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70138/a5c27923-09a0-b826-8faf-acbebf1bc6f8.png">

これでGCPにプロジェクトが作成されます。

### 2. Reporting APIの有効化
次に、 `Reporting API` を有効化します。
GCPの[APIとサービス](https://console.cloud.google.com/apis/dashboard)にアクセスします。

<img width="1440" alt="03.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70138/062931b5-561b-ad10-2f0e-f6bc0af43097.png">

<img width="1440" alt="04.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70138/8b1ae81e-a6b1-e393-f137-5bfff4d3b776.png">

<img width="1440" alt="05.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70138/9bbd938f-be4e-6941-4af8-43748a4f987c.png">

<img width="1440" alt="06.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70138/076b73a7-37e3-1307-e000-d3378b844d7c.png">

<img width="1440" alt="07.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70138/60138a29-a30f-2a24-83c1-600b857363a4.png">

`Reporting API` が有効化されました。

### 3. GCPサービスアカウントの作成
Reporting APIを利用するサービスアカウントを作成します。
サービスアカウントからAPIにアクセスします。

<img width="1440" alt="08.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70138/a2f866b5-86d5-073a-8f99-b62a64c4d215.png">

<img width="1440" alt="09.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70138/6802a19e-1ec2-af4c-d969-f1829c432d68.png">

<img width="1440" alt="10.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70138/54e062a5-7623-71e2-5b04-f309039c183b.png">

![11.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70138/7c3004dc-dd30-0e12-3f6b-0140d064a8cf.png)

<img width="1440" alt="12.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70138/0cc6bbcb-7d0a-1d3d-029f-d270f209ae8c.png">

### 4. サービスアカウントのJSON形式の認証キーをダウンロード

3の手順でJSON形式の認証キーは自動的にダウンロードされます。
大切に保管します。

<img width="1440" alt="13.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70138/b69cedcb-a9eb-58d1-9aaf-c81db30ece4a.png">

### 5. サービスアカウントをGAに登録
作成したサービスアカウントをGAに登録をしておきます。
これをしておかないと、APIを投げた時に弾かれてしまいます。

[GA](https://analytics.google.com/)にアクセスします。

![18.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70138/b395e257-4c24-7533-45fb-48d022af29ba.png)

<img width="1440" alt="19.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70138/81a560b3-2fc8-573c-4254-ab215ce1baa9.png">

<img width="1440" alt="20.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70138/86920146-f183-6141-5a1d-111a6b122c65.png">

以上で、GAにサービスアカウントを登録できます。

## インスタンスの用意・環境構築
`Reporting API` からデータを取得し、BigQueryにロードするための環境を構築します。
日次バッチ処理でデータを引っ張ってきます。

### 1. GCPで作成したプロジェクト配下で、インスタンスを立ち上げる
[GCP Compute Engine](https://console.cloud.google.com/compute/instances)にアクセスします。
以下はCompute Engineでインスタンスを立ち上げたことがない状態のコンソールです。
インスタンスを作成していきます。

![14.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70138/2e4985b4-b760-d792-ffd1-642a5891742a.png)

![15.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70138/ce86a778-ac27-6526-5814-385c29b8d7a1.png)

インスタンスが作成されました。

### 2. 立ち上げたインスタンスに以下をインストール
それでは、環境を作っていきます。
手順としては以下です。

1. aptをアップグレードし、gitなどをインストール
2. nvmをインストール
3. node.jsをインストール
4. npmを初期化
5. `googleapis` `@google-cloud/storage` `@google-cloud/datastore` をインストール
6. `gcloudコマンドラインツール` をインストール

まずは、作成したインスタンスにSSH接続を行います。

<img width="1440" alt="16.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70138/421d43be-6f3f-cd7d-2f96-b8addeccdc21.png">

![17.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70138/a82641be-cf3c-b5c6-8cfa-b449a024c4af.png)

接続できました。

#### 1. aptをアップグレードし、gitなどをインストール
以下を実行します。

```
sudo apt update
sudo apt upgrade
sudo apt install build-essential curl git m4 ruby texinfo libbz2-dev libcurl4-openssl-dev libexpat-dev libncurses-dev zlib1g-dev gettext apache2
```

#### 2. nvmをインストール
以下を実行します。

```
curl -o- https://raw.githubusercontent.com/creationix/nvm/v0.34.0/install.sh | bash
```

実行後、以下を実行します。

```
export NVM_DIR="$HOME/.nvm"
[ -s "$NVM_DIR/nvm.sh" ] && \. "$NVM_DIR/nvm.sh"  # This loads nvm
[ -s "$NVM_DIR/bash_completion" ] && \. "$NVM_DIR/bash_completion"  # This loads nvm bash_completion
```

#### 3. Node.jsをインストール
以下を実行します。

```
# インストール一覧を確認
nvm ls-remote

# v6.9.0をインストールする
nvm install v6.9.0

# v6.9.0をdefaultにする
nvm alias default v6.9.0
```

`v6.9.0` をインストールする理由は、最新の安定バージョンを使うと、 `@google-cloud/storage` をインストールできないからです。
ソースは[こちら](https://github.com/particle-iot/google-cloud-datastore-tutorial/issues/8)をご確認ください。

#### 4. npmを初期化
以下を実行します。

```
# npm初期化しpackage.jsonの中身は適当の設定しておく
npm init
```

#### 5. `googleapis` `@google-cloud/storage` `@google-cloud/datastore` をインストール
以下を実行します。

```
npm install --save node-gyp
npm rebuild -save node-gyp
npm install -g node-pre-gyp
npm install --save googleapis
npm install --save @google-cloud/storage
npm install --save @google-cloud/datastore
```

#### 6. `gcloudコマンドラインツール` をインストール
[Google SDK -> 入門ガイド -> SDKのインストール -> apt-getの使用（DebianとUbuntuのみ）](https://cloud.google.com/sdk/docs/downloads-apt-get?hl=ja)の手順通りインストールを行います。
以下、抜粋します。

```
# 正しく配布されるように、環境変数を作成します。
export CLOUD_SDK_REPO="cloud-sdk-$(lsb_release -c -s)"

# Google Cloud の公開鍵をインポートします
echo "deb http://packages.cloud.google.com/apt $CLOUD_SDK_REPO main" | sudo tee -a /etc/apt/sources.list.d/google-cloud-sdk.list

# Cloud SDK を更新してインストールします。
sudo apt update
sudo apt install google-cloud-sdk
```

### 3. サービスアカウントで作成したJSON形式の認証キーをインスタンスにアップロード
先ほどの手順で用意したJSON形式の認証キーをインスタンスにアップロードします。
前提として、アップロードするローカルPCにも `gcloudコマンドラインツール` をインストールしている必要があります。
詳細は、[Cloud SDK](https://cloud.google.com/sdk/docs/?hl=ja)を参照してください。

アップロードは[gcloud compute scp](https://cloud.google.com/sdk/gcloud/reference/compute/scp)で可能です。

```
# コード
gcloud compute scp --recurse [LOCAL_FILE_PATH] [INSTANCE_NAME]:~/

# 参考例
gcloud compute scp --recurse /Users/kenichiroyamashita/Downloads/reporting/ google-analytics-reporting:/home/kenichiro_yamashita/google-analytics/
```
## BigQueryの用意
### 1. GAのデータをためておくBigQueryを用意
データをためておく側のBigQueryを用意します。
[BigQuery](https://console.cloud.google.com/bigquery)にアクセスします。

<img width="1440" alt="21.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70138/53b391cb-a12a-23fe-9df3-5ff459a7f9b5.png">

<img width="1440" alt="22.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70138/27229299-4f60-1bcb-247d-a0b422dbbb22.png">

GAをためておくデータセットが作成されました。

## 実装
### 1. Reporting APIからデータを引っ張る処理の実装
さて、いよいよ実装です。
Node.jsを利用します。

以下、手順です。

1. Node.jsのバージョンを最新安定板に変更
2. ディレクトリの用意 例 -> `~/{user_hame}/ga/reporting/`
3. 上記ディレクトリのパーミッションを変更
4. ローカルで用意したファイルをインスタンスにアップロード
5. 初回処理バッチを作成

#### 1. Node.jsのバージョンを最新安定板に変更
環境構築した際に `v6.9.0` 出会ったNode.jsのバージョンを最新安定板に変更する。
これをしておかないと、 `Reporting API` からデータ取得するために `async` を使っているんですが、これが動かなくなります。

```
# インストール一覧を確認
nvm ls-remote

# 最新安定板をインストールする
nvm install stable

# 最新安定板をdefaultにする
nvm alias default stable
```

#### 2. ディレクトリの用意 例 -> `~/{user_hame}/ga/reporting/`
ディレクトリを用意します。
以下は一例です。

```
mkdir ga
cd ga
mkdir reporting
```

#### 3. 上記ディレクトリのパーミッションを変更
パーミッションを変更しておかないと、ローカルからファイルをアップロードできません。
以下は一例です。

```
sudo chmod -R 777 ~/{user_name}/ga
```

#### 4. ローカルで用意したファイルをインスタンスにアップロード
いよいよ、GAから `Reporting API` で引っ張ってくる実装になります。
コード例は以下になります。
こちらは、エクスチュア社の[Google Analytics StandardのデータをBigQueryで分析するための力技](https://ex-ture.com/blog/2018/05/09/google-analytics-standard-with-hitid-on-bigquery/)を参考にさせていただきました。
権さん、その節はありがとうございました！

ファイル名は `main_r_dimensions.js` としました。

インスタンスへのアップロードは上記のJSON形式の認証キーをアップロードした手順を参考にしてください。

```javascript
if (process.argv.length < 4) {
  console.log('Usage: node main_r_dimensions.js dimensions YYYY-MM-DD YYYY-MM-DD');
  process.exit(1);
}

let dname = process.argv[2];
let start = process.argv[3]; // 30daysAgoなど
let end = process.argv[4]; // yesterdayなど

const {google} = require('googleapis');

// Each API may support multiple version. With this sample, we're getting
// v3 of the blogger API, and using an API key to authenticate.
const analyticsreporting = google.analyticsreporting({
  version: 'v4'
});

// 各種定義を行う
let apikey = require('./various-datasets-fdad08a1c355.json'); // Google API Consoleで設定したKeyを読み込む
let viewid = 'xxxxxxxxx'; // 取得したいGoogle AnalyticsのビューIDを設定する
let pagesize = 10000; // デフォルトは1000 10000
j = 1;

const client = new google.auth.JWT(
  apikey.client_email,
  null,
  apikey.private_key,
  // scope は以下を指定
  ['https://www.googleapis.com/auth/analytics'],
  null
);

let runReport = async() => {
  let res = await analyticsreporting.reports.batchGet({
    resource: {
      reportRequests: [{
        "viewId": viewid,
        "pageSize": pagesize,
        "dateRanges": [{
          "startDate": start,
          "endDate": end
        }],
        "samplingLevel": "LARGE",
        "dimensions": [{
          "name": "ga:dimension3" // hit_idを設定したdimension{indexNo}を指定
        },{
          "name": dname
        }],
        "metrics": [{
          "expression": "ga:hits"
        }]
      }]
    },
    auth: client
  });

  let d = res.data.reports[0].data.rows;
  if(!res.data.reports[0].nextPageToken){
    for(let i = 0; i < d.length; i++){
      console.log(d[i].dimensions[0] + '\t' + d[i].dimensions[1]);
      j++;
    }
  }else{
    for(let i = 0; i < d.length; i++){
      console.log(d[i].dimensions[0] + '\t' + d[i].dimensions[1]);
      j++;
      if(j == res.data.reports[0].nextPageToken){
        nextPageToken = res.data.reports[0].nextPageToken;
        client.authorize().then(c => runReportNextPage());
      }
    }
  }
};

let runReportNextPage = async() => {
  let res = await analyticsreporting.reports.batchGet({
    resource: {
      reportRequests: [{
        "viewId": viewid,
        "pageSize": pagesize,
        "dateRanges": [{
          "startDate": start,
          "endDate": end
        }],
        "samplingLevel": "LARGE",
        "dimensions": [{
          "name": "ga:dimension3"
        },{
          "name": dname
        }],
        "metrics": [{
          "expression": "ga:hits"
        }],
        "pageToken": nextPageToken
      }]
    },
    auth: client
  });

  let d = res.data.reports[0].data.rows;
  if(!res.data.reports[0].nextPageToken){
    for(let i = 0; i < d.length; i++){
      console.log(d[i].dimensions[0] + '\t' + d[i].dimensions[1]);
      j++;
    }
  }else{
    for(let i = 0; i < d.length; i++){
      console.log(d[i].dimensions[0] + '\t' + d[i].dimensions[1]);
      j++;
      if(j == res.data.reports[0].nextPageToken){
        nextPageToken = res.data.reports[0].nextPageToken;
        client.authorize().then(c => runReportNextPage());
      }
    }
  }
};

client.authorize().then(c => runReport());
```

コードについては、また別途記事にまとめたいと思います。
詳細説明は割愛します。

#### 5. 初回処理バッチを作成
バッチ処理で、欲しいデータを全て取得してきます。
バッチ処理については以下です。

ファイル名を `report_first.sh` としました。

```shell
#!/bin/sh
today=`date "+%Y%m%d_%H%M%S"`
mkdir ${today}

# 初回取得条件を定義する
start="2019-03-01"
end="today"

# 初回取得を実行する
# get dimension
node main_r_dimensions.js ga:userType ${start} ${end} > ./${today}/ecommercega_userType.txt
node main_r_dimensions.js ga:sessionCount ${start} ${end} > ./${today}/ecommercega_sessionCount.txt
node main_r_dimensions.js ga:sessionDurationBucket ${start} ${end} > ./${today}/ecommercega_sessionDurationBucket.txt
node main_r_dimensions.js ga:fullReferrer ${start} ${end} > ./${today}/ecommercega_fullReferrer.txt
node main_r_dimensions.js ga:socialNetwork ${start} ${end} > ./${today}/ecommercega_socialNetwork.txt
node main_r_dimensions.js ga:browser ${start} ${end} > ./${today}/ecommercega_browser.txt
node main_r_dimensions.js ga:browserVersion ${start} ${end} > ./${today}/ecommercega_browserVersion.txt
node main_r_dimensions.js ga:operatingSystem ${start} ${end} > ./${today}/ecommercega_operatingSystem.txt
node main_r_dimensions.js ga:operatingSystemVersion ${start} ${end} > ./${today}/ecommercega_operatingSystemVersion.txt
node main_r_dimensions.js ga:mobileDeviceBranding ${start} ${end} > ./${today}/ecommercega_mobileDeviceBranding.txt
node main_r_dimensions.js ga:mobileDeviceModel ${start} ${end} > ./${today}/ecommercega_mobileDeviceModel.txt
node main_r_dimensions.js ga:mobileInputSelector ${start} ${end} > ./${today}/ecommercega_mobileInputSelector.txt
node main_r_dimensions.js ga:mobileDeviceInfo ${start} ${end} > ./${today}/ecommercega_mobileDeviceInfo.txt
node main_r_dimensions.js ga:mobileDeviceMarketingName ${start} ${end} > ./${today}/ecommercega_mobileDeviceMarketingName.txt
node main_r_dimensions.js ga:deviceCategory ${start} ${end} > ./${today}/ecommercega_deviceCategory.txt
node main_r_dimensions.js ga:browserSize ${start} ${end} > ./${today}/ecommercega_browserSize.txt
node main_r_dimensions.js ga:dataSource ${start} ${end} > ./${today}/ecommercega_dataSource.txt
node main_r_dimensions.js ga:latitude ${start} ${end} > ./${today}/ecommercega_latitude.txt
node main_r_dimensions.js ga:longitude ${start} ${end} > ./${today}/ecommercega_longitude.txt
node main_r_dimensions.js ga:pagePath ${start} ${end} > ./${today}/ecommercega_pagePath.txt
node main_r_dimensions.js ga:pageTitle ${start} ${end} > ./${today}/ecommercega_pageTitle.txt
node main_r_dimensions.js ga:exitPagePath ${start} ${end} > ./${today}/ecommercega_exitPagePath.txt
node main_r_dimensions.js ga:eventLabel ${start} ${end} > ./${today}/ecommercega_eventLabel.txt
node main_r_dimensions.js ga:dimension2 ${start} ${end} > ./${today}/ecommercega_dimension2.txt
node main_r_dimensions.js ga:dimension4 ${start} ${end} > ./${today}/ecommercega_dimension4.txt
node main_r_dimensions.js ga:dimension5 ${start} ${end} > ./${today}/ecommercega_dimension5.txt
node main_r_dimensions.js ga:channelGrouping ${start} ${end} > ./${today}/ecommercega_channelGrouping.txt
```

# 3. 引っ張ってきたデータをBigQueryにロードする
上記でひっぱてきたデータをBiqQueryにロードします。
ロードについての[リファレンス](https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-csv?hl=ja#bigquery_load_table_gcs_csv-cli)からもわかる通り、以下でロードが可能です。

```
# コード例
bq load --source_format=[FORMAT] [DATASET].[TABLE] [PATH_TO_SOURCE] [SCHEMA]

# 参考
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.{table_name} ./userType.txt hit_id:string,user_type:string
```

上記のGA取得タイミングで、BigQueryへとデータをロードする実装にします。
すると、BigQueryへのロード部分は以下になります。

```shell
#!/bin/sh
today=`date "+%Y%m%d_%H%M%S"`
mkdir ${today}

# 初回取得条件を定義する
start="2019-03-01"
# start="30daysAgo"
end="today"

# BigQueryへロード
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.user_type ./${today}/userType.txt hit_id:string,user_type:string
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.session_counts ./${today}/sessionCount.txt hit_id:string,session_counts:integer
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.session_duration_bucket ./${today}/sessionDurationBucket.txt hit_id:string,session_duration_bucket:integer
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.full_referrer ./${today}/fullReferrer.txt hit_id:string,full_referrer:string
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.social_network ./${today}/socialNetwork.txt hit_id:string,social_network:string
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.browsers ./${today}/browser.txt hit_id:string,browsers:string
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.browser_version ./${today}/browserVersion.txt hit_id:string,browser_version:string
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.operating_system ./${today}/operatingSystem.txt hit_id:string,operating_system:string
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.operating_system_version ./${today}/operatingSystemVersion.txt hit_id:string,operating_system_version:string
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.mobile_device_branding ./${today}/mobileDeviceBranding.txt hit_id:string,mobile_device_branding:string
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.mobile_device_model ./${today}/mobileDeviceModel.txt hit_id:string,mobile_device_model:string
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.mobileInput_selector ./${today}/mobileInputSelector.txt hit_id:string,mobileInput_selector:string
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.mobile_device_info ./${today}/mobileDeviceInfo.txt hit_id:string,mobile_device_info:string
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.mobile_device_marketing_names ./${today}/mobileDeviceMarketingName.txt hit_id:string,mobile_device_marketing_names:string
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.device_categories ./${today}/deviceCategory.txt hit_id:string,device_categories:string
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.browser_size ./${today}/browserSize.txt hit_id:string,browser_size:string
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.data_sources ./${today}/dataSource.txt hit_id:string,data_sources:string
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.latitude ./${today}/latitude.txt hit_id:string,latitude:float
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.longitude ./${today}/longitude.txt hit_id:string,longitude:float
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.page_path ./${today}/pagePath.txt hit_id:string,page_path:string
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.page_title ./${today}/pageTitle.txt hit_id:string,page_title:string
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.exit_page_path ./${today}/exitPagePath.txt hit_id:string,exit_page_path:string
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.event_labels ./${today}/eventLabel_output.txt hit_id:string,event_labels:string,order_id:integer
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.dimension2 ./${today}/dimension2.txt hit_id:string,dimension2:string
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.dimension4 ./${today}/dimension4.txt hit_id:string,dimension4:datetime
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.dimension5 ./${today}/dimension5.txt hit_id:string,dimension5:integer
bq load --replace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.channel_grouping ./${today}/channelGrouping.txt hit_id:string,channel_grouping:string
```
# 4. GA取得＆BigQueryロードの日次バッチ処理
日次バッチ用のファイルを作成します。
ファイル名は `report_daily.sh` としました。

注意点としては以下。

- 初回ロードを `2daysAgo` のデータとした
- 初回の日次バッチを `yesterday` のデータとした
- 後は日次（実行時間は1:05とする）でバッチを回すだけ

```shell
#!/bin/sh
today=`date "+%Y%m%d_%H%M%S"`
mkdir ${today}

# 初回取得条件を定義する
start="yesterday"
end="yesterday"

# 初回取得を実行する
# get dimension
node main_r_dimensions.js ga:userType ${start} ${end} > ./${today}/userType.txt
node main_r_dimensions.js ga:sessionCount ${start} ${end} > ./${today}/sessionCount.txt
node main_r_dimensions.js ga:sessionDurationBucket ${start} ${end} > ./${today}/sessionDurationBucket.txt
node main_r_dimensions.js ga:fullReferrer ${start} ${end} > ./${today}/fullReferrer.txt
node main_r_dimensions.js ga:socialNetwork ${start} ${end} > ./${today}/socialNetwork.txt
node main_r_dimensions.js ga:browser ${start} ${end} > ./${today}/browser.txt
node main_r_dimensions.js ga:browserVersion ${start} ${end} > ./${today}/browserVersion.txt
node main_r_dimensions.js ga:operatingSystem ${start} ${end} > ./${today}/operatingSystem.txt
node main_r_dimensions.js ga:operatingSystemVersion ${start} ${end} > ./${today}/operatingSystemVersion.txt
node main_r_dimensions.js ga:mobileDeviceBranding ${start} ${end} > ./${today}/mobileDeviceBranding.txt
node main_r_dimensions.js ga:mobileDeviceModel ${start} ${end} > ./${today}/mobileDeviceModel.txt
node main_r_dimensions.js ga:mobileInputSelector ${start} ${end} > ./${today}/mobileInputSelector.txt
node main_r_dimensions.js ga:mobileDeviceInfo ${start} ${end} > ./${today}/mobileDeviceInfo.txt
node main_r_dimensions.js ga:mobileDeviceMarketingName ${start} ${end} > ./${today}/mobileDeviceMarketingName.txt
node main_r_dimensions.js ga:deviceCategory ${start} ${end} > ./${today}/deviceCategory.txt
node main_r_dimensions.js ga:browserSize ${start} ${end} > ./${today}/browserSize.txt
node main_r_dimensions.js ga:dataSource ${start} ${end} > ./${today}/dataSource.txt
node main_r_dimensions.js ga:latitude ${start} ${end} > ./${today}/latitude.txt
node main_r_dimensions.js ga:longitude ${start} ${end} > ./${today}/longitude.txt
node main_r_dimensions.js ga:pagePath ${start} ${end} > ./${today}/pagePath.txt
node main_r_dimensions.js ga:pageTitle ${start} ${end} > ./${today}/pageTitle.txt
node main_r_dimensions.js ga:exitPagePath ${start} ${end} > ./${today}/exitPagePath.txt
node main_r_dimensions.js ga:eventLabel ${start} ${end} > ./${today}/eventLabel.txt
node main_r_dimensions.js ga:dimension2 ${start} ${end} > ./${today}/dimension2.txt
node main_r_dimensions.js ga:dimension4 ${start} ${end} > ./${today}/dimension4.txt
node main_r_dimensions.js ga:dimension5 ${start} ${end} > ./${today}/dimension5.txt
node main_r_dimensions.js ga:channelGrouping ${start} ${end} > ./${today}/channelGrouping.txt

# BigQueryに追加ロード
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.user_type ./${today}/userType.txt hit_id:string,user_type:string
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.session_counts ./${today}/sessionCount.txt hit_id:string,session_counts:integer
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.session_duration_bucket ./${today}/sessionDurationBucket.txt hit_id:string,session_duration_bucket:integer
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.full_referrer ./${today}/fullReferrer.txt hit_id:string,full_referrer:string
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.social_network ./${today}/socialNetwork.txt hit_id:string,social_network:string
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.browsers ./${today}/browser.txt hit_id:string,browsers:string
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.browser_version ./${today}/browserVersion.txt hit_id:string,browser_version:string
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.operating_system ./${today}/operatingSystem.txt hit_id:string,operating_system:string
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.operating_system_version ./${today}/operatingSystemVersion.txt hit_id:string,operating_system_version:string
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.mobile_device_branding ./${today}/mobileDeviceBranding.txt hit_id:string,mobile_device_branding:string
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.mobile_device_model ./${today}/mobileDeviceModel.txt hit_id:string,mobile_device_model:string
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.mobileInput_selector ./${today}/mobileInputSelector.txt hit_id:string,mobileInput_selector:string
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.mobile_device_info ./${today}/mobileDeviceInfo.txt hit_id:string,mobile_device_info:string
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.mobile_device_marketing_names ./${today}/mobileDeviceMarketingName.txt hit_id:string,mobile_device_marketing_names:string
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.device_categories ./${today}/deviceCategory.txt hit_id:string,device_categories:string
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.browser_size ./${today}/browserSize.txt hit_id:string,browser_size:string
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.data_sources ./${today}/dataSource.txt hit_id:string,data_sources:string
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.latitude ./${today}/latitude.txt hit_id:string,latitude:float
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.longitude ./${today}/longitude.txt hit_id:string,longitude:float
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.page_path ./${today}/pagePath.txt hit_id:string,page_path:string
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.page_title ./${today}/pageTitle.txt hit_id:string,page_title:string
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.exit_page_path ./${today}/exitPagePath.txt hit_id:string,exit_page_path:string
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.event_labels ./${today}/eventLabel_output.txt hit_id:string,event_labels:string,order_id:integer
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.dimension2 ./${today}/dimension2.txt hit_id:string,dimension2:string
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.dimension4 ./${today}/dimension4.txt hit_id:string,dimension4:datetime
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.dimension5 ./${today}/dimension5.txt hit_id:string,dimension5:integer
bq load --noreplace --source_format=CSV --field_delimiter="\t" {project_name}:{dataset_name}.channel_grouping ./${today}/channelGrouping.txt hit_id:string,channel_grouping:string
```

以上です。
これで、構築は一通り完了です。
ぜひ、試してみてください。

# 5. BigQueryにためたデータを[Looker](https://looker.fbpp.jp/)で結合し、分析用の派生テーブルを作成する

BigQueryにデータはたまっていくので、次にデータを結合し、分析用の派生テーブルを作っていくのですが、[Looker](https://looker.fbpp.jp/)以降に関しては別記事でまとめていこうと思います。
※流石に記事内容が長くなりすぎる…

# 併せて読みたい
- [Google Analyticsのgtag.js(カスタムディメンション)とGoogleタグマネージャーで、SPA(Single Page Application)のWebSiteをトラッキングできるようにする](https://qiita.com/fbp-yamashita/items/8b321cd974a560a52268)