このエントリーは、GMOアドマーケティング Advent Calendar 2018
（ https://qiita.com/advent-calendar/2018/gmo-am ）の【12/11】の記事です。
GMOアドマーケティングとしては初のAdvent Calendar参戦です。

背景

サービスの売上やKPIを常に把握し、定点観測できる状態を作る必要が出てくることがあるかと思います。
WEBサイト・メディアのPV、UUといった数値であればGoogle Analyticsで確認可能だと思いますが、弊社では自社WEBサービスの売上などをレポートする管理画面を複数サービスで用意しており、各サービスの数値を横断して管理する必要がありました。

Google Apps Script (通称GAS)
Google Data Portal （旧Data Studio）
Google Spread Sheet

を使うことで、非エンジニアのディレクターでも簡単にWEB上からデータ取得、集計、加工、グラフ表示、自動更新する専用ダッシュボードをつくることができます。

もちろん自社サービスでなくても、WEB上のデータであれば取得可能なため、自社WEBサービスのディレクターでなくても応用可能です。

特にGASは、「環境構築が不要」「お手軽」「定期実行が容易」「プログラミング経験が浅くても習得ハードルが低い」など、非エンジニアでもトライしやすくなっています。

エンジニアのリソースがどうしても取れない。。
外部の会社が提供するツールなのでレポート画面を直してもらうことが難しい

など、ディレクター八方塞がりの状況でもなんとかなりますのでご安心を。

やりたいこと

社内で２つあるサービスのそれぞれのWEBレポート画面（ログイン認証有）から数値取得し、それをマージしたカスタムのダッシュボードを用意したい。

（前提）取得するWEBレポート画面の構成

ID/パスワードにてログイン認証有の管理画面
レポートはCSVでもダウンロード可能
ダウンロードする項目をフォームで指定可能（集計期間、抽出条件等）

手順

スプレッドシートを用意
GASでWEBレポート画面の数値をスクレイピングし、スプレッドシートに保存
一連の処理を定期実行設定
スプレッドシートに保存したデータを加工
データをGoogleデータポータルに読み込ませ、ダッシュボード作成

GASでWEBレポート画面の数値をスクレイピングし、スプレッドシートに保存

後のステップで取得するデータを保存するスプレッドシートを用意。

[ツール]>[スクリプトエディタ]をクリック
ここで立ち上がる画面がGASを編集する環境となります。

上記のコードを以下のように編集していきます。

function get_CSV() {
  //今日の日付
  var today = new Date();
  //今月月初の日付
  var beginnig_this_month = new Date(today.getFullYear(), today.getMonth(), 1);
  //日付フォーマット化
  today = Utilities.formatDate( today, 'Asia/Tokyo', 'YYYY-MM-dd');
  beginnig_this_month = Utilities.formatDate( beginnig_this_month, 'Asia/Tokyo', 'YYYY-MM-dd');
  var start_date = beginnig_this_month;
  var end_date = today;

  // ログインページ
  var LOGIN_URL = "https://hogehoge.com/login";
  // CSVファイルダウンロード先（UTF-8）
  var CSV_DOWNLOAD_URL = "https://hogehoge.com/report";
  // ログインID
  var id = "*******";
  // パスワード
  var password = "*******";

  // HTTPリクエストのパラメータをobjectで設定
  // POSTで渡すフォームデータはpayloadで指定
  var options = {
    method : "post",
    followRedirects: false, // リダイレクト無し
    payload : {
      id: id,
      pass: password
    }
  };
  // ログイン
  var response = UrlFetchApp.fetch(LOGIN_URL, options);
  // レスポンスヘッダーからcookie取得
  var cookies = response.getHeaders()["Set-Cookie"];

  // 取得したcookieをリクエストヘッダーに入れ、ログイン状態保持
  var headers = { 'Cookie' : cookies };
  options = {
    method : "post",
    headers : headers,
    followRedirects: true, //リダイレクトあり
    payload : {
      //取得したいデータの抽出WEBフォームの入力値指定
      start_date:start_date,
      end_date:end_date,
      report_type:"daily",
      goods_type:"all",
      export_type:"csv",
      button:"ダウンロード"
    }
  };

  // CSVファイル取得
  response = UrlFetchApp.fetch(CSV_DOWNLOAD_URL, options);
  var data = response.getContentText("shift_jis");

  // スプレッドシートへの書き込み
  var spreadsheetObj = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheetObj.getSheetByName("csv");
  var csv = Utilities.parseCsv(data);
  sheet.getRange(1, 1, csv.length, csv[0].length).setValues(csv);
}

GASはJavascriptベースのプログラム言語となっており、Javascriptを扱える方であれば習得が早いかと思います。
ここでは非エンジニアでも大まかに把握できるよう、基本的な文法や細かいコードの意味解釈は省き、パーツ毎に何を行っているかを説明します。

抽出期間の日付設定

レポート画面にて集計期間の指定ができるため、今月月初〜今日までの日付を指定します。
日付をtimestamp型から任意の指定書式に変換しています。


  //今日の日付
  var today = new Date();
  //今月月初の日付
  var beginnig_this_month = new Date(today.getFullYear(), today.getMonth(), 1);
  //日付フォーマット化
  today = Utilities.formatDate( today, &apos;Asia/Tokyo&apos;, &apos;YYYY-MM-dd&apos;);
  beginnig_this_month = Utilities.formatDate( beginnig_this_month, &apos;Asia/Tokyo&apos;, &apos;YYYY-MM-dd&apos;);
  var start_date = beginnig_this_month;
  var end_date = today;

ログイン処理

ログインが必要なページにあるレポート画面へは、直接指定してもアクセスできず取得できませんので、一度ログインURLに対しURLfetchし、認証に必要なcookieを取得しておきます。


  // ログインページ
  var LOGIN_URL = "https://hogehoge.com/login";
  // CSVファイルダウンロード先（UTF-8）
  var CSV_DOWNLOAD_URL = "https://hogehoge.com/report";
  // ログインID
  var id = "*******";
  // パスワード
  var password = "*******";

  // HTTPリクエストのパラメータをobjectで設定
  // POSTで渡すフォームデータはpayloadで指定
  var options = {
    method : "post",
    followRedirects: false, // リダイレクト無し
    payload : {
      id: id,
      pass: password
    }
  };
  // ログイン
  var response = UrlFetchApp.fetch(LOGIN_URL, options);
  // レスポンスヘッダーからcookie取得
  var cookies = response.getHeaders()["Set-Cookie"];

フォームの入力条件を指定しCSV取得

CSVをダウンロードするページURLに対してURLfetchしますが、先程取得したcookieをSetしてリクエストします。
URLfetch の optionsの、
headers にて先程のcookieを、
payload にて入力(抽出)条件を指定します。

  // 取得したcookieをリクエストヘッダーに入れ、ログイン状態保持
  var headers = { 'Cookie' : cookies };
  options = {
    method : "post",
    headers : headers,
    followRedirects: true, //リダイレクトあり
    payload : {
      //取得したいデータの抽出WEBフォームの入力値指定
      //取得したいデータの抽出WEBフォームの入力値指定
      start_date:start_date,
      end_date:end_date,
      report_type:"daily",
      goods_type:"all",
      export_type:"csv",
      button:"ダウンロード"
    }
  };

  // CSVファイル取得
  response = UrlFetchApp.fetch(CSV_DOWNLOAD_URL, options);
  var data = response.getContentText("shift_jis");

payloadではWEBフォームの入力情報（サーバーへの送信情報）を記載していますが、Google chromeのDeveloper Toolsで調べれば簡単に確認可能です。
例として、気象庁過去の気象データ・ダウンロードのページで説明すると、、、

Developer Tools（右クリック>検証）を開き、送りたい項目を選択しレポートをダウンロードします。
その際、フォーム情報を送信する通信が発生するかと思いますので、そのリクエストURLと送信しているFrom DataをGAS上でも引用してくれば、GASのプログラム経由でもレポートCSVをダウンロードしてこれるかと思います。
（※期間の項目など、動的に変更したい項目はGAS上で変数として設定してください）

取得したデータをスプレッドシートに保存

取得したデータ(csv)を、任意のスプレッドシートに格納します。
getSheetByName("csv") にて格納したいシート名を指定します。

  // スプレッドシートへの書き込み
  var spreadsheetObj = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheetObj.getSheetByName("csv");
  var csv = Utilities.parseCsv(data);
  sheet.getRange(1, 1, csv.length, csv[0].length).setValues(csv);

一連の処理を定期実行設定

GASではcron等の設定不要で、定期実行を設定することが可能です。

遷移したトリガー設定の画面で、トリガー追加していきます。
実行する関数、実行条件を指定できます。これで自動でデータが更新できることになります。

スプレッドシートに保存したデータを加工

GASとスプレッドシートを組み合わせることの扱いやすいメリットは、スプレッドシート側で数式を用いて後からデータ加工がしやすい点にあります。

例えば、以下のようにデータを格納した後、
スプレッドシートのVLOOKUP関数で別データを付け合せする等も簡単です。
DB操作を行わなくてよいのは非常にお手軽です。

データをデータポータルに読み込ませ、ダッシュボード作成

Googleデータポータルは、GoogleがWEB上で用意するBIツールライクなサービスです。これを使いスプレッドシートよりデータを読込み、ダッシュボードを作成します。
スプレッドシートでグラフを作成するよりも、データ表示期間やフィルタの選択・ソートが可能等、使いやすい点が多くオススメです。

以下より新しいレポートを作成します。

レポート画面に対しては、データソースを紐付ける必要があります。
[新しいデータソースを作成]を選び、データソース元を選択します。
Google各サービスの他、各種サードパーティのデータも連携できます。
ここで、スプレッドシートを選択します。

スプレッドシート > ワークシート > データ取得範囲選択
というようにブレイクダウンしてデータ元を指定していきます。

列のヘッダー名が重複している場合は読み込めませんので、変更してくだい。
また、一度データソースを読み込んだ後、新しいデータカラムをスプレッドシート側に追加した場合、この画面にて再読込が必要になりますので注意してください。

フィールドを追加

上記の②フィールドを追加を選択することで、読み込んだスプレッドシートのデータをデータポータル上で加工出来ます。
スプレッドシート側でも計算できるのですが、こちらで計算することで後からの集計・データ加工が簡単に行なえます。

データポータル関数リストから使える関数リファレンスを確認できますのでご利用ください。

グラフを設定

読み込んだデータソースをレポートに追加すると、レポート編集画面側からグラフを作成できるようになります。作成したグラフは、右カラムで表示する「ディメンション」「指標」を編集できます。この中に、先程作成した追加したフィールドも含まれています。
また、作成したレポート上で期間や表示項目のフィルタを設定できる機能を入れることも出来ます。（図中③）

チームで共有し、KPI進捗の把握を高速化！

作成したダッシュボードは、他の人へ共有や、サイト内埋込みが可能です。プロジェクトやチーム内でKPI・数値共有を把握しやすく気軽にできるようになると、円滑に進行させられるだけでなく、施策の考案にもつながりやすいかもしれないですね！
初めて取り組む方でも、作成には正味2時間程度あればできると思うので、是非トライして見てください。

明日は、【@zakisanbaiman】の【OWASP ZAPの使い方】についてのお話です。お楽しみに。

ディレクターだけどGAS＋データポータル使ってKPIダッシュボード作ってみた

背景