Treasure Data で集計した結果を Google Spreadsheet に出力する


同じチームのメンバーが書いた記事に便乗します。

【入門編】TreasureDataでサイトのアクセス解析をしてみた~第2弾!~

で紹介しております 曜日・時間帯別のセッション数 のヒートマップを毎週 Google Spreadsheet に出力し、さらにそのヒートマップを自動的に Slack に通知する、ということを書きたいと思います。
今回は Treasure Data での集計結果を Google Spreadsheet へ出力する ところまでやります。

クエリの作成

クエリは、引用元で紹介されているクエリをベースに、抽出期間が毎週自動的に更新されるようにしましょう。
TD_SCHEDULED_TIME() はスケジュール実行設定で定めた時間になりますので、これを使用します。
ついでに出力結果がクロス集計の状態になるよう CASE 文と MAX を使ってラッピングします。

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

スケジュールの登録

次にクエリを実行するスケジュールを設定します。
Schedule をクリックしてしてください。

query_setting_1

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 を使うと良いでしょう。

td_schedule

Google Spreadsheet の準備

Google Spreadsheet を新規作成します。
適当なタイトルとシート名をつけます。今回は “access_heatmap” というタイトルに “fromTD” というシート名に設定しました。

行数、列数の調整は必要ありません。TD から出力されると自動でサイズ変更されます。

blank_sheet

出力先の設定

Result Export の Add をクリックしてください。

query_setting_2

Export to:Google Spreadsheet に設定します。そのほかの項目も入力しましょう。
Mode: は、出力結果を置き換える (replace) か 最下行に追加する (append) かを選びます。
今回は置き換えにしたいので replace に設定します。
※ もし Google Account を Treasure Data に登録していない場合は、先に登録する必要があります。

詳細 : https://docs.treasuredata.com/articles/result-into-google-spreadsheet

td_result_export

出力結果

指定した日時になるとクエリが実行され、結果が Google Spreadsheet に出力されるはずです。

monochrome_sheet

今回の出力結果のシートは、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 を使うには ツール > スクリプトエディタ… をクリックします。

spreadsheet_tool_script_editor

スクリプトエディタが開きました。ここに処理内容を書いていきます。

gas_new

今回カラースケールの条件付き書式を適用させるメソッドかライブラリがないか、探したのですが、
なかなか見つからなかったので、簡易的なヒートマップ適用のスクリプトを作成しました。

// 指定範囲内における値の順位を元に、ヒートマップカラーの背景色を適用する
//
// 色を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]);
  });
}

スクリプトエディタの書き込みフィールドに上記のスクリプトを記述します。

TreasureData から書き込みされたり、手動で数値を変更したりするたびに、ヒートマップ化するために、applyHeatMap() という関数を リソース > 現在のプロジェクトのトリガー に設定します。イベントは スプレッドシート, 値の変更 に設定します。

gas_trigger

これで スプレッドシートに変更がある度に処理が行われ、ヒートマップの適用が実行されます。

heatmap_sheet

今回はここまで。
毎週、手動でクエリを投げて、結果を Excel でダウンロードして、条件付き書式を設定でヒートマップを作成して、というのは手間がかかりますが、今回のように Spreadsheet を出力先にすることで、簡単に可視化する仕組みを構築することができました。

私の開発チームとしても、予測値の精度や機能の利用者数などを Google Spreadsheet を利用して、継続的に確認しています。異常な変化を察知するのによいですね。

次回はヒートマップを Slack へ通知する部分を書きたいと思います。


DACエンジニア採用情報

  関連記事

l_077
fastavroとjqでAVRO形式のファイルからデータを取得しよう

AVRO形式のファイルを取り扱いたい AVROとはApacheプロジェクトのひとつとして開発されているデータ交換形式です。 コンパクトなバイナリで高速なシリアライズ・デシリアライズが行えるため、サーバーログなどに利用されています。 弊社内での一部システムのログデータにも利用されているのですが、専用の …

The 簡素
【電子工作入門】Arduino + Processingでアラート監視してみた

こんにちは、プラットフォームワンのyamakenです。普段はフロントエンド中心にDSP周りの開発をやっています。現在新卒2年目です。 広告会社のエンジニアブログということで皆さんビッグデータやインフラなどためになりそうな記事をあげていらっしゃいますが、今回は少し趣向を変えて電子工作チックな内容をお届 …