少し間が空いてしまいましたが、今回の内容は前回予告していた通り、Google Analytics・Fusion Tables・Google Apps Scriptの3つを組み合せ、Google AnalyticsのデータをFusion Tablesに持ってきて、マッピングをできるようにしてみます。
なお、次回からはFusion Tables以外の話を書くつもりです。
気が付けばFusion Tables関連で6回もやっていました。ゴールをどこにしようか迷っていたとかじゃありませんよ。
実装したい内容の手動実行
実装を行う前に、具体的にどんなことを行おうとしているのかを、手動で実演してみます。
実装内容だけ知りたいという方は、ページ下部まで進んでください。
今回、実現したいことは、「Google Analyticsのデータを都道府県別に色分けできる状態にしたマップを作成する」ことの自動化です。
それを手動で行おうとすると、次のような手順を踏むことになります。
- Google Analyticsから地域別のデータをFusion Tablesに移行する
- 移行したデータの入ったテーブルと、都道府県名+都道府県の領域の情報が入ったテーブルをマージする
まず、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',);
④ベースとなるテーブルと、位置情報の入ったテーブルとでビューを作成する
手動で行っていたマージ作業にあたる処理です。
SQLでAnalyticsから取得したデータの入ったテーブルと、あらかじめ用意してある都道府県の境界データを持ったテーブルの結合ビューを作成しています。
また、Analyticsのデータを取得する期間は、前行日の先月1日から31日（28～30日）になるようにしているので、トリガー機能で月の初めに起動するようにしておけば、自動でAnalyticsから先月分の欲しいデータが揃ったテーブルを作成することができます。
手動で行っていたことが、これで自動化できるようになりました。
自動化以外のメリットとしては、Google Analytics APIを使ってデータを取得する場合、ブラウザ操作よりも細かい条件でデータを取得できるようになります。
ブラウザ操作の場合、"セカンダリディメンション"を利用することで、2つまで条件を指定できますが、Google Analytics APIを使ってデータを取得すると、3つ以上の条件を指定することができます。
例えば、ブラウザ操作では"ブラウザはIEを使っている"かつ"東京からアクセスしている"ことのまでは分かりますが、さらに"どのページにアクセスした"までは絞り込むことができません。
Google Analytics APIからであれば、上記の例でもデータを取得することができます。
冒頭でも書きましたが、Fusion Tablesに関しては今回で一度区切りとします。
次回書く内容は具体的に決められていませんが、OSSを使った内容にしてみようかと考えています。
0 件のコメント:
コメントを投稿