同じチームのメンバーが書いた記事に便乗します。
【入門編】TreasureDataでサイトのアクセス解析をしてみた~第2弾!~
で紹介しております 曜日・時間帯別のセッション数 のヒートマップを毎週 Google Spreadsheet に出力し、さらにそのヒートマップを自動的に Slack に通知する、ということを書きたいと思います。 今回は Treasure Data での集計結果を Google Spreadsheet へ出力する ところまでやります。
クエリの作成
クエリは、引用元で紹介されているクエリをベースに、抽出期間が毎週自動的に更新されるようにしましょう。 TD_SCHEDULED_TIME() はスケジュール実行設定で定めた時間になりますので、これを使用します。 ついでに出力結果がクロス集計の状態になるよう CASE 文と MAX を使ってラッピングします。
[sql] SELECT Hour_in_day, MAX (CASE WHEN Day_in_week = 'Monday' THEN sessions ELSE null END) as Monday, MAX (CASE WHEN Day_in_week = 'Tuesday' THEN sessions ELSE null END) as Tuesday, MAX (CASE WHEN Day_in_week = 'Wednesday' THEN sessions ELSE null END) as Wednesday, MAX (CASE WHEN Day_in_week = 'Thursday' THEN sessions ELSE null END) as Thursday, MAX (CASE WHEN Day_in_week = 'Friday' THEN sessions ELSE null END) as Friday, MAX (CASE WHEN Day_in_week = 'Saturday' THEN sessions ELSE null END) as Saturday, MAX (CASE WHEN Day_in_week = 'Sunday' THEN sessions ELSE null END) as Sunday FROM ( SELECT TD_TIME_FORMAT(TD_TIME_PARSE(session_start_time),'EEEE','JST') AS Day_in_week, TD_TIME_FORMAT(TD_TIME_PARSE(session_start_time),'HH','JST') AS Hour_in_day, COUNT(session_id) AS sessions FROM test.demo_www_sessions WHERE -- 集計期間を直近1週間分に限定 TD_TIME_RANGE( time, TD_TIME_ADD(TD_SCHEDULED_TIME(),'-7d'), TD_SCHEDULED_TIME(), 'JST' ) GROUP BY TD_TIME_FORMAT(TD_TIME_PARSE(session_start_time),'yyyy-MM-dd','JST'), TD_TIME_FORMAT(TD_TIME_PARSE(session_start_time),'EEEE','JST'), TD_TIME_FORMAT(TD_TIME_PARSE(session_start_time),'HH','JST') ) s GROUP BY Hour_in_day ORDER BY Hour_in_day [/sql]
スケジュールの登録
次にクエリを実行するスケジュールを設定します。 Schedule をクリックしてしてください。
Schedule から Custom Cron... を選択します。
Cron Schedule : 0 0 * * 1 とすることにより 毎週月曜日 00:00 に実行するよう設定できます。
5つの値の意味は左から 分、時間、日、月、曜日 です。
* はワイルドカード、"指定しない"の意味です。
詳細 : https://docs.treasuredata.com/articles/schedule#cron-schedule
Delay はクエリ実行時間を 上記スケジュール時間から何秒遅らせるかを指定できます。 この Delay はクエリ内の TD_SCHEDULE_TIME() に影響しません。 つまり今回の場合、 Delay を何秒に設定しようと、 TD_SCHEDULE_TIME() は 月曜日 00:00 です。 ログがテーブルに入るのに遅れがある場合やジョブが空いている時間帯に実行したい時は、この Delay を使うと良いでしょう。
Google Spreadsheet の準備
Google Spreadsheet を新規作成します。 適当なタイトルとシート名をつけます。今回は "access_heatmap" というタイトルに "fromTD" というシート名に設定しました。
行数、列数の調整は必要ありません。TD から出力されると自動でサイズ変更されます。
出力先の設定
Result Export の Add をクリックしてください。
Export to: を Google Spreadsheet に設定します。そのほかの項目も入力しましょう。 Mode: は、出力結果を置き換える (replace) か 最下行に追加する (append) かを選びます。 今回は置き換えにしたいので replace に設定します。 ※ もし Google Account を Treasure Data に登録していない場合は、先に登録する必要があります。
詳細 : https://docs.treasuredata.com/articles/result-into-google-spreadsheet
出力結果
指定した日時になるとクエリが実行され、結果が Google Spreadsheet に出力されるはずです。
今回の出力結果のシートは、B2 ~ H25 を選択して、条件付き書式 > カラースケール を選べば、お手軽ヒートマップを作成することができます。しかし、この条件付き書式設定は TD から "replace" される度にリセットされてしまいます。 そこで、"replace" されたタイミングを検知して、色付けする操作を Google Apps Script を使って実現します。
Apps Script でヒートマップ
Apps Script は Spreadsheet の操作を自動化したり、新しい関数を作成したりできます。 個人的には Excel で VBA マクロを書くのに似ていると感じました。 Gmail, Calender, Drive, Map, 翻訳 といった Google の機能との連携も簡単です。
Google Apps Script ドキュメント : https://developers.google.com/apps-script/
Apps Script を使うには ツール > スクリプトエディタ... をクリックします。
スクリプトエディタが開きました。ここに処理内容を書いていきます。
今回カラースケールの条件付き書式を適用させるメソッドかライブラリがないか、探したのですが、 なかなか見つからなかったので、簡易的なヒートマップ適用のスクリプトを作成しました。
[js] // 指定範囲内における値の順位を元に、ヒートマップカラーの背景色を適用する // // 色をRGB各チャネル 0 ~ 255 の配列で表現する // 例 : 黒→[0,0,0], 白→[255,255,255], 青→[0,0,255]
var RGB_ON_MAX_VALUE = [240, 179, 34]; // 最大値の色 var RGB_ON_MIN_VALUE = [255, 255, 255]; // 最小値の色 var HEATMAP_RANGE = 'B2:H25'; // 適用範囲
// 公差1の等差数列を作成する関数 function generateNumArray(begin, length) { var array = ; for(i = 0; i < length; i++) { array[i] = begin + i; } return array; }
// グラデーションの配列を作成する関数 function createGradation(maxRGB, minRGB, steps) { var gradation = generateNumArray(0, steps).map(function(i) { var r = maxRGB[0] * i / steps + minRGB[0] * (1 - i / steps); var g = maxRGB[1] * i / steps + minRGB[1] * (1 - i / steps); var b = maxRGB[2] * i / steps + minRGB[2] * (1 - i / steps); return [r, g, b]; }); return gradation; }
function paintBackground(range, RGB){ return range.setBackgroundRGB(RGB[0], RGB[1], RGB[2]); }
function compareValue(preCell, nextCell){ return preCell.getValue() - nextCell.getValue(); }
function calcAveColor(RGBs, colorChannel){ return RGBs.map(function(RGB){ return RGB[colorChannel]; }).reduce(function(p, a){ return p + a; }) / RGBs.length; }
// 平均の色を計算する関数 function calcAveRGB(RGBs){ var aveR = calcAveColor(RGBs, 0); var aveG = calcAveColor(RGBs, 1); var aveB = calcAveColor(RGBs, 2); return [aveR, aveG, aveB]; }
// 同順位の値のセルを同じ色で塗りつぶすためにグラデーションを調整する関数 function adjustToCellsValue(gradation, cells){ var k = 0; while (k < cells.length){ tmpCells = [cells[k]]; tmpGrads = [gradation[k]]; var l = 1; while (l + k < cells.length) { if (cells[k].getValue() === cells[k + l].getValue()) { tmpCells.push(cells[k + l]); tmpGrads.push(gradation[k + l]); l++; } else { break; } } var aveRGB = calcAveRGB(tmpGrads); for (m = k; m < k + l; m++){ gradation[m] = aveRGB; } k = k + l; } return gradation; }
// この関数を ツールバー > リソース > 現在のプロジェクトのトリガー に イベント: スプレッドシート、値の変更 で登録しておく
function applyHeatMap() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var range = sheet.getRange(HEATMAP_RANGE);
var numColumns = range.getNumColumns();
var numRows = range.getNumRows();
var steps = numColumns * numRows;
// 指定範囲のセルを、その値の小さい順にソートした配列 var cells = generateNumArray(1, numColumns).map(function(column) { return generateNumArray(1, numRows).map(function(row) { return range.getCell(row, column); }); }).reduce(function(pre,current) { return pre.concat(current); }, ).sort(compareValue);
var gradation = createGradation(RGB_ON_MAX_VALUE, RGB_ON_MIN_VALUE, steps); var adjustedGradation = adjustToCellsValue(gradation, cells);
// セルの背景を調整済みグラデーションで塗りつぶす cells.forEach(function(cell, i, cells){ paintBackground(cell, ajustedGradation[i]); }); } [/js]
スクリプトエディタの書き込みフィールドに上記のスクリプトを記述します。
TreasureData から書き込みされたり、手動で数値を変更したりするたびに、ヒートマップ化するために、applyHeatMap() という関数を リソース > 現在のプロジェクトのトリガー に設定します。イベントは スプレッドシート, 値の変更 に設定します。
これで スプレッドシートに変更がある度に処理が行われ、ヒートマップの適用が実行されます。
今回はここまで。 毎週、手動でクエリを投げて、結果を Excel でダウンロードして、条件付き書式を設定でヒートマップを作成して、というのは手間がかかりますが、今回のように Spreadsheet を出力先にすることで、簡単に可視化する仕組みを構築することができました。
私の開発チームとしても、予測値の精度や機能の利用者数などを Google Spreadsheet を利用して、継続的に確認しています。異常な変化を察知するのによいですね。
次回はヒートマップを Slack へ通知する部分を書きたいと思います。
