Fusion Tables×Google Apps Script（マッピング実用編）～Google AnalyticsのデータをインプットにFusion Tablesでマッピングする～ 

Google Analytics、Fusion Tables、Google Apps Scriptの3つを組み合せ、Google AnalyticsのデータをFusion Tablesに持ってきて、マッピングをできるようにしてみます。
※マッピングの基礎編は[こちら](http://qiita.com/U11/items/241a52a04ec08e25d5a7)を参照してください。

#実装したい内容の手動実行

実装を行う前に、具体的にどんなことを行おうとしているのかを、手動で実演してみます。
 実装内容だけ知りたいという方は、ページ下部まで進んでください。

 今回、実現したいことは、「Google Analyticsのデータを都道府県別に色分けできる状態にしたマップを作成する」ことの自動化です。

それを手動で行おうとすると、次のような手順を踏むことになります。
1.Google Analyticsから地域別のデータをFusion Tablesに移行する
2.移行したデータの入ったテーブルと、都道府県名+都道府県の領域の情報が入ったテーブルをマージする

なお、"都道府県名+都道府県の領域の情報が入ったテーブル"に関しては、[マッピング基礎編](http://qiita.com/U11/items/241a52a04ec08e25d5a7)に書いてありますので、詳細についてはそちらご参照ください。

まず、Google Analyticsから地域別のデータを表示します。
なお、例として利用しているデータは"国"に"Japan"を指定しているので、都道府県別のデータが表示されています。
![マッピング実用01.png](https://qiita-image-store.s3.amazonaws.com/0/82305/a78fa43d-51af-4948-f1d8-88eb3e76e166.png)

Google Analyticsのエクスポート機能を使ってFusion Tablesへ移行したいのですが、Google Analyticsから直接Fusion Tablesへはエクスポートできません。
 代替手段として、一度スプレッドシートへエクスポートし、Fusion Tablesからスプレッドシートのデータをインポートします。

なお、Google Analyticsは表示されている1ページ分のデータしかエクスポートしてくれないので、表示行数を調整して、欲しいデータが入るようにしておきましょう。
![マッピング実用02.png](https://qiita-image-store.s3.amazonaws.com/0/82305/25b9f9c8-0adc-aa0b-b3c4-1fa853a95920.png)

スプレッドシートへのエクスポートを選択すると、確認画面が開きます。問題がなければ"はい。データをインポートします"を選択します。

![マッピング実用03.png](https://qiita-image-store.s3.amazonaws.com/0/82305/56228b81-a2d3-cc8a-9f6c-e273de47055e.png)

エクスポートに成功したので、作成されたスプレッドシートを確認してみます。
![マッピング実用04.png](https://qiita-image-store.s3.amazonaws.com/0/82305/bf96b35d-e874-70ca-6f66-ab54e42f5c02.png)


次はスプレッドシートのデータをFusion Tablesにインポートします。
Fusion Tablesの新規作成から"Google Spreadsheets"を選択し、先ほど作成したスプレッドシートをインポート対象にします。
![マッピング実用05.png](https://qiita-image-store.s3.amazonaws.com/0/82305/bcdb0ed9-45fc-e690-f4e8-38cc0b937547.png)

スプレッドシートからインポートする場合、何行目をカラムとして見なすかを選択します。
![マッピング実用06.png](https://qiita-image-store.s3.amazonaws.com/0/82305/4e14b376-1b8d-1e46-f61d-bce544191c46.png)

スプレッドシートでは6行目にカラム名が記入されていたので、"6"を選択しました。
![マッピング実用07.png](https://qiita-image-store.s3.amazonaws.com/0/82305/4963d8d5-8d34-ce85-8ee7-3f8221c2c89f.png)

テーブル名などを新規作成前に設定できますが、特に変更せずに"Finish"を選択します。
![マッピング実用08.png](https://qiita-image-store.s3.amazonaws.com/0/82305/b4cf0e99-7b42-fcb8-3769-3ab3b7fa31e2.png)

これでようやくGoogle Analyticsから、Fusion Tablesへデータを移行することができました。
![マッピング実用09.png](https://qiita-image-store.s3.amazonaws.com/0/82305/db66c5ee-d7bc-a52a-bb1f-41565f036bbe.png)

ただし、基礎編で説明したように、この状態では都道府県ごとのマッピングはできません。都道府県ごとの境界がどこからどこなのかを、レコードごとに与えなければなりません。

 都道府県ごとの境界のデータを持つテーブルは前回作成していますが、境界のデータを1レコードずつ入力するのは非常に手間がかかるので、できれば避けたいところです。

そこでFusion Tablesのマージ機能を利用します。
Fusion Tablesのマージ機能を一般的なDBで言うなら、"結合ビューの作成"でしょうか。

 今回はGoogle Analyticsからエクスポートしたテーブルをベースに、都道府県の境界データを持ったテーブルをマージしてみます。

マージ機能を使うには、"File"メニューの中段下あたりにある"Merge"を選択します。
![マッピング実用10.png](https://qiita-image-store.s3.amazonaws.com/0/82305/335f8c91-c591-d9b8-1ca4-36028cfe7ae9.png)

マージする対象のテーブルを選択する画面が開くので、都道府県の境界データを持った"Japan"テーブルを選択します。
（基礎編で作成していたテーブルで、デフォルトで用意されているわけではないので注意）
![マッピング実用11.png](https://qiita-image-store.s3.amazonaws.com/0/82305/a6739991-4a94-2fff-9ab2-fd48ed13ea17.png)

テーブルを選択すると、2つのテーブルから、それぞれカラムを選択する画面が開きます。
ここで選択したカラム同士で、合致するデータがあるレコードのみ表示するビューを作成することになります。
![マッピング実用12.png](https://qiita-image-store.s3.amazonaws.com/0/82305/fc4fe009-4053-798b-15a4-110d966709ec.png)

それぞれ都道府県名が入ったカラムを選択してみましたが、ここで1つ問題が発生しました。
Google Analyticsからエクスポートしたテーブルでは、都道府県名の後ろに"Prefecture"が付いていることがありますが、もう片方のテーブルではそういった文字列は付いていません。

このままではカラム同士のデータ合致判定が思った通りに機能してくれません。
![マッピング実用13.png](https://qiita-image-store.s3.amazonaws.com/0/82305/bf3fc9b3-87d2-a83a-dc75-897d741a4997.png)

仕方がないので、どちらかのデータを修正します。
 手早く修正するために、Google Analyticsからエクスポートしたスプレッドシートの" Prefecture"を一斉置換で削除し、新しくテーブルを作成することにします。
 （Fusion Tablesだと1つずつしかレコードを修正できず、SQLの発行もGoogle Apps Scriptなど外部実装を介さなくてはできないので、少し時間がかかります）
![マッピング実用14.png](https://qiita-image-store.s3.amazonaws.com/0/82305/02b501bc-ae61-4134-481e-a29963298ba1.png)

![マッピング実用15.png](https://qiita-image-store.s3.amazonaws.com/0/82305/3b76c5c1-fdb4-668d-a71b-a0a2733b2438.png)

データを修正したテーブルで改めてマージをしてみます。互いに都道府県名の入ったカラムを選択します。
![マッピング実用16.png](https://qiita-image-store.s3.amazonaws.com/0/82305/5548df4d-5617-95b8-3707-03370a530982.png)

次に、どのカラムを表示するのかを選択します。
Google Analyticsのカラムはとりあえず全て表示させておきますが、もう片方のテーブルで欲しいカラムは、都道府県の境界データを持ったカラムだけなので、それ以外のカラムのチェックを外します。
![マッピング実用17.png](https://qiita-image-store.s3.amazonaws.com/0/82305/662e09f3-5de9-dbae-0fb4-82eb0dcda995.png)

![マッピング実用18.png](https://qiita-image-store.s3.amazonaws.com/0/82305/21dc76fa-b9f0-8d09-4038-379489bfd40c.png)

これでMergeを選択すると、設定した条件のビューが作成されます。
![マッピング実用19.png](https://qiita-image-store.s3.amazonaws.com/0/82305/ca1bc95b-f1ee-8cd2-c7e9-9db9b53c16f5.png)



Google Analyticsからエクスポートしたテーブルをベースとして、都道府県の境界データの入ったカラムが追加されています。（一番右のカラム）
![マッピング実用20.png](https://qiita-image-store.s3.amazonaws.com/0/82305/724a3257-b953-a0bb-7d70-28c0385778e1.png)

このビューのマップ表示をしてみると、まだ数値ごとの色分けこそされていませんが、都道府県が赤で塗られています。（赤で塗られていないところは、データが存在しないことを示しています）
ここまで来れば、後はどのカラムを色分けの基準にするか、どんな値ごとに色分けするかを設定するだけです。
![マッピング実用21.png](https://qiita-image-store.s3.amazonaws.com/0/82305/ff0212f8-dd38-cfd6-6c11-d2480f9ede43.png)


ここまでかなり長くなりましたが、やりたいことのイメージとしては、Google Analyticsのデータをインプットとして、日本地図に色が塗られているテーブルを、ひと月ごとに自動作成してくれる実装です。



#実装による自動化
 手動で行った手順を元に実装していきます。ただし、手動とは違ってわざわざスプレッドシートに一度エクスポートする必要はありません。また、手動で修正していた都道府県名の" Prefecture"の削除も内部処理で一括して行います。

```lang:自動化
function main() {
  var LOCATION_TABLE_ID = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';
  var PROFILE_ID = "ga:XXXXXXXX";
  var startDate;
  var endDate;
  var nowDate = new Date();
  
  endDate = new Date(nowDate.getFullYear(), nowDate.getMonth(), 0);
  startDate = new Date(endDate.getFullYear(), endDate.getMonth(), 1);
  
  startDate = dateFormat(startDate);
  endDate = dateFormat(endDate);
  
  var datas = getAnalyticsData(PROFILE_ID, startDate, endDate);
  
  var tableName = 'Analytics from ' + startDate + ' to ' + endDate;
  var tableId = createTable(tableName);
  
  insertDatas(tableId, datas);
  
  var viewName = 'merge ' + tableName + ' and Location';
  createView(tableId, LOCATION_TABLE_ID, viewName);
}


// ①Google Analyticsからのデータ取得
function getAnalyticsData(id, startDate, endDate) {
  var metrics = "ga:visits";
  var optArgs = {
    'dimensions': 'ga:region',
    'filters' : 'ga:country==Japan'
  };
 

  var ga = Analytics.Data.Ga.get(id, startDate, endDate, metrics,optArgs).rows;
  for(var index in ga) {
    ga[index][0] = ga[index][0].replace(/ Prefecture/g, "");
  }
  return ga;
}


// ②Fusion Tablesにテーブルを新規作成
function createTable(tableName) {
  var resource = {
    "name": tableName,
    "columns": [
      {
        "name": "id",
        "type": "NUMBER",
        "kind": "fusiontables#column"
      },
      {
        "name": "session",
        "type": "NUMBER",
        "kind": "fusiontables#column"
      },
      {
        "name": "region",
        "type": "LOCATION",
        "kind": "fusiontables#column"
      },
      ],
      "isExportable": false,
      "kind": "fusiontables#table"
    };

  var id = FusionTables.Table.insert(resource).tableId;

  return id;
}


// ③Fusion Tablesのテーブルに指定したデータを挿入する
function insertDatas(tableId, datas) {
  var rows = '';

  for(var index in datas) {
    rows += (parseInt(index) + 1) + ','
    + datas[index][1] + ',' 
    + datas[index][0] + "\n" ;
  }
  var blob = Utilities.newBlob(rows, "application/octet-stream");
  FusionTables.Table.importRows(tableId, blob);
}

// ④ベースとなるテーブルと、位置情報の入ったテーブルとでビューを作成する
function createView(tableId, lTableId, viewName) {
  var sql = 'CREATE VIEW ' + '\'' + viewName + '\'' + ' AS ' 
  + '(SELECT id, session, region, Japan.geometry FROM ' + tableId + ' AS Analytics '
  + 'LEFT OUTER JOIN ' + lTableId +' AS Japan '
  + 'ON Analytics.region = Japan.ObjName_1)';

  FusionTables.Query.sql(sql);
}

function dateFormat(date) {
  return date.getFullYear() + '-' + ('0' + (date.getMonth() + 1) ).slice(-2) + '-' + ('0' + date.getDate()).slice(-2);
}
```

##コード解説
①Google Analyticsからのデータ取得
Google Analyticsからデータを取得する方法は過去のブログで紹介していますので、そちらをご参照ください。

今回はメトリクスにはセッション数を指定し、フィルタオプションとして日本の都道府県別にデータを取得するように指定しています。
メトリクスやフィルタオプションに関しては、下記のサイトを参考にしました。
http://www.kagua.biz/api/gaapijp.html

ちなみに、処理の終わりに都道府県名の" Prefecture"を削除しています。


②Fusion Tablesにテーブルを新規作成
JSON形式でテーブルの内容を定義し、それを元にテーブルを新規作成しています。

 今回は"id"、"session"、"region"の3カラムからなるシンプルなテーブルを作成しています。
なお、"session"にはGoogle Analyticsからデータ取得したセッション数、"region"には同じくGoogle Analyticsからデータ取得した都道府県名が入ります。


③Fusion Tablesのテーブルに指定したデータを挿入する
Google Analyticsから取得したデータを、処理途中に作成したテーブルに挿入するのですが、SQLを利用する場合、1件ずつしか入れることしかできません。（2015年4月時点）
※下記のようなデータ挿入ができません
　INSERT INTO table(id, session, region) VALUES 
　(1, 'XXXX', 'YYYY',),
　(2, 'XXXX', 'YYYY',),
　(3, 'XXXX', 'YYYY',);


そのため、1度に全データを挿入するため、Blobオブジェクトを利用する方法を採っています。


④ベースとなるテーブルと、位置情報の入ったテーブルとでビューを作成する
手動で行っていたマージ作業にあたる処理です。
SQLでAnalyticsから取得したデータの入ったテーブルと、あらかじめ用意してある都道府県の境界データを持ったテーブルの結合ビューを作成しています。


また、Analyticsのデータを取得する期間は、前行日の先月1日から31日（28～30日）になるようにしているので、トリガー機能で月の初めに起動するようにしておけば、自動でAnalyticsから先月分の欲しいデータが揃ったテーブルを作成することができます。

 手動で行っていたことが、これで自動化できるようになりました。
![マッピング実用22.png](https://qiita-image-store.s3.amazonaws.com/0/82305/cd94434d-aab6-852c-a4a7-160976a1c273.png)


##自動化まとめ
自動化以外のメリットとしては、Google Analytics APIを使ってデータを取得する場合、ブラウザ操作よりも細かい条件でデータを取得できるようになります。
ブラウザ操作の場合、"セカンダリディメンション"を利用することで、2つまで条件を指定できますが、Google Analytics APIを使ってデータを取得すると、3つ以上の条件を指定することができます。

 例えば、ブラウザ操作では"ブラウザはIEを使っている"かつ"東京からアクセスしている"ことのまでは分かりますが、さらに"どのページにアクセスした"までは絞り込むことができません。
Google Analytics APIからであれば、上記の例でもデータを取得することができます。

以上