BigQueryをGoogleAppsScriptとGoogleSpreadsheetを組み合わせてつかう #GoogleAppsScript

社内では基本的に1人で分析したり集計したりしているので、
愚直に集計、分析のサイクルを回していると何もできなくなるため
定期レポートのようなものはGoogleAppsScript(GAS)をつかって
ひたすら自動化するようにしている

実際にやってる内容をメモがてら書いてゆく

GoogleAnalyticsアドオンの利用

分析には主にGoogleAnalytics(GA)を利用するので、
ちょっとした数字をちゃっちゃと見たい時に便利

特に詳細は説明しないが、スケジュール設定機能があるので
定期的に出したい簡単な値があればコレを使うようにしている

GAで見る毎日見る数値は限られてる、かつ、GAの管理画面がめっちゃ重い
なんて状況であれば、スケジュールで毎日特定の値だけだすように設定しておけば
ストレスフリーにGAの数値が確認できる

GoogleSpreadの内容を読み込んで定期実行

GASはジョブ管理システムとしてみているけれど、GoogleSpreadsheet(GS)と
組み合わせることでさらに強力になる

基本的にはGSのセルに値を持たせておいて、GAS内でそいつを使うようにする

BigQueryのクエリの一部を毎月変更して定期実行

たとえばBigQueryで、日付だけ変えて毎月定期実行したいが、
日付を変える作業も実行する作業もめんどくさい場合がある

その場合、次のように設定していくとすべて自動化されて幸せになる

以下の日付部分を変えて定期的に実行したいとする


#standardSQL

SELECT
    DISTINCT fullVisitorId AS user_id
FROM
    `projectId.datasetName.ga_sessions_*`
WHERE
    -- 前月の初日 ~ 末日で設定したい
    _TABLE_SUFFIX BETWEEN "20170801" AND "20170831"

セルへの日付の取得

｢yearMonth｣というシートを作って、適当なセルに前月の日付をyyyyMMの形で
取ってくるように関数を指定する。

たとえば、手動でも設定できるように考慮してセルを作ると
上記のB2セルに次の関数を指定しておく

=IF(ISBLANK(B1),YEAR(EOMONTH(TODAY(),-1)) & TEXT(MONTH(EOMONTH(TODAY(),-1)),"00"),B1)

これで勝手に日付が変わるところまで設定できた

GASでセルを参照して値を取得する

スプレッドシートのメニューから｢ツール > スクリプトエディタ｣と選んで
スクリプトの設定画面に遷移して、以下の関数を追加する

function getYearMonth() {
  var sheet = spreadsheet.getSheetByName('yearMonth');
  var range = sheet.getRange(2, 2);
  var values = range.getValues();

  return String(values[0]);
}

これでyyyyMMまで取得できる。次に初日と末日を取得して文字列型で返すとこまで実行する
以下の関数をスクリプトエディタに追加する

function getPeriodFromYearMonth() {
  var yearMonth = getYearMonth()
  var startDate = yearMonth + '01';
  var newDate = new Date(yearMonth.substr(0, 4), Number(yearMonth.substr(4, 2)) - 1, 1);
  newDate.setMonth(newDate.getMonth() + 1);
  newDate.setDate(newDate.getDate() - 1);
  var endDate = Utilities.formatDate(newDate, 'Asia/Tokyo', 'yyyyMMdd');

  return {
    startDate: startDate,
    endDate: endDate
  };
}

これで初日と末日のyyyyMMdd形式の日付をオブジェクトで返すとこまでできた
次にこいつをBigQueryのクエリの中に突っ込んで返す関数を追加する

function generateQuery() {
  var period = getPeriodFromYearMonth();
  var startOfMonth = period.startDate;
  var endOfMonth = period.endDate;

  return [
    '#standardSQL'
    , ''
    , 'SELECT'
    , '    DISTINCT fullVisitorId AS user_id'
    , 'FROM'
    , '    `projectId.datasetName.ga_sessions_*`'
    , 'WHERE'
    , '    -- 前月の初日 ~ 末日で設定したい'
    , '    _TABLE_SUFFIX BETWEEN "' + startOfMonth +  '" AND "' + endOfMonth + '"'
    , ';'
  ].join('\n');
}

これでクエリの発行までできた
あとは実際にスクリプトエディタのメニューから｢リソース > Googleの拡張サービス｣へと
移動して、BiGQueryAPIをオンにして、BigQueryオブジェクトにクエリを渡せば実行できる

そのあたりの具体的な方法は以下を参照するだけでOKだとおもう

定期実行する仮にクエリを発行するところまでをゴールとすると最終的なスクリプトは以下の様になっている最後のloggerはログがあったほうがわかりやすいかと思っていま足した

function getYearMonth() {
  var sheet = spreadsheet.getSheetByName('yearMonth');
  var range = sheet.getRange(2, 2);
  var values = range.getValues();

  return String(values[0]);
}

function getPeriodFromYearMonth() {
  var yearMonth = getYearMonth()
  var startDate = yearMonth + '01';
  var newDate = new Date(yearMonth.substr(0, 4), Number(yearMonth.substr(4, 2)) - 1, 1);
  newDate.setMonth(newDate.getMonth() + 1);
  newDate.setDate(newDate.getDate() - 1);
  var endDate = Utilities.formatDate(newDate, 'Asia/Tokyo', 'yyyyMMdd');

  return {
    startDate: startDate,
    endDate: endDate
  };
}

function generateQuery() {
  var period = getPeriodFromYearMonth();
  var startOfMonth = period.startDate;
  var endOfMonth = period.endDate;

  return [
    '#standardSQL'
    , ''
    , 'SELECT'
    , '    DISTINCT fullVisitorId AS user_id'
    , 'FROM'
    , '    `projectId.datasetName.ga_sessions_*`'
    , 'WHERE'
    , '    -- 前月の初日 ~ 末日で設定したい'
    , '    _TABLE_SUFFIX BETWEEN "' + startOfMonth +  '" AND "' + endOfMonth + '"'
    , ';'
  ].join('\n');
}

function logger() {
  Logger.log(generateQuery());
}

loggerを定期的に実行するときはスクリプトエディタのメニューから
｢編集 > 現在のプロジェクトのトリガー｣から実行したい関数をどの頻度で実行するのか設定する

こうすることでGASが毎月1日にloggerを定期実行してくれるようになり、
日付の変わったクエリをログで確認できるようになる。この処理自体は意味が無いけど、
定期的に実行したい処理があれば便利

あとはライブラリの話とか、設定の一元管理とかがあるけど次回書く