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

Google Analytics、Fusion Tables、Google Apps Scriptの3つを組み合せ、Google AnalyticsのデータをFusion Tablesに持ってきて、マッピングをできるようにしてみます。
※マッピングの基礎編はこちらを参照してください。

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

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

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

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

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

まず、Google Analyticsから地域別のデータを表示します。
なお、例として利用しているデータは"国"に"Japan"を指定しているので、都道府県別のデータが表示されています。

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

なお、Google Analyticsは表示されている1ページ分のデータしかエクスポートしてくれないので、表示行数を調整して、欲しいデータが入るようにしておきましょう。

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

エクスポートに成功したので、作成されたスプレッドシートを確認してみます。

次はスプレッドシートのデータをFusion Tablesにインポートします。
Fusion Tablesの新規作成から"Google Spreadsheets"を選択し、先ほど作成したスプレッドシートをインポート対象にします。

スプレッドシートからインポートする場合、何行目をカラムとして見なすかを選択します。

スプレッドシートでは6行目にカラム名が記入されていたので、"6"を選択しました。

テーブル名などを新規作成前に設定できますが、特に変更せずに"Finish"を選択します。

これでようやくGoogle Analyticsから、Fusion Tablesへデータを移行することができました。

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

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

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

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

マージ機能を使うには、"File"メニューの中段下あたりにある"Merge"を選択します。

マージする対象のテーブルを選択する画面が開くので、都道府県の境界データを持った"Japan"テーブルを選択します。
（基礎編で作成していたテーブルで、デフォルトで用意されているわけではないので注意）

テーブルを選択すると、2つのテーブルから、それぞれカラムを選択する画面が開きます。
ここで選択したカラム同士で、合致するデータがあるレコードのみ表示するビューを作成することになります。

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

このままではカラム同士のデータ合致判定が思った通りに機能してくれません。

仕方がないので、どちらかのデータを修正します。
手早く修正するために、Google Analyticsからエクスポートしたスプレッドシートの" Prefecture"を一斉置換で削除し、新しくテーブルを作成することにします。
（Fusion Tablesだと1つずつしかレコードを修正できず、SQLの発行もGoogle Apps Scriptなど外部実装を介さなくてはできないので、少し時間がかかります）

データを修正したテーブルで改めてマージをしてみます。互いに都道府県名の入ったカラムを選択します。

次に、どのカラムを表示するのかを選択します。
Google Analyticsのカラムはとりあえず全て表示させておきますが、もう片方のテーブルで欲しいカラムは、都道府県の境界データを持ったカラムだけなので、それ以外のカラムのチェックを外します。

これでMergeを選択すると、設定した条件のビューが作成されます。

Google Analyticsからエクスポートしたテーブルをベースとして、都道府県の境界データの入ったカラムが追加されています。（一番右のカラム）

このビューのマップ表示をしてみると、まだ数値ごとの色分けこそされていませんが、都道府県が赤で塗られています。（赤で塗られていないところは、データが存在しないことを示しています）
ここまで来れば、後はどのカラムを色分けの基準にするか、どんな値ごとに色分けするかを設定するだけです。

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

実装による自動化

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

自動化

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から先月分の欲しいデータが揃ったテーブルを作成することができます。

手動で行っていたことが、これで自動化できるようになりました。

自動化まとめ

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

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

以上