マーケターがredash&スプレッドシートでKPI管理してみた

スマートキャンプでマーケターをしている佐々木です。

最近は自販機でペットボトルを購入することにハマっています。

私は弊社の運営する資料請求サイト「ボクシル」のマッチング最適化を生業として生きているのですが、追うべきKPI・可視化したデータの共有にはスプレッドシートを好んで使っています。

SQLで取り出したデータであればRe:dashで共有するのがライトなのですが、

SQLでとってきたもの以外のデータを上手く組み合わせられない
様々なアレルギー反応を起こす人がいる（英語UIがダメな人、クエリ見ると卒倒しちゃう人etc...）
権限管理が大変

などダッシュボードとして利用していくにあたって、特に非エンジニアに浸透しづらいというのがあります。

そこでスプレッドシートをダッシュボードとして活用していく方法を紹介したいと思います。

概要
- ツールごとの役割分担
- 使った技術
流れ
補足
まとめ

概要

f:id:kohta_sasaki:20190710130358p:plain

Re:dashのAPIとスプレッドシートのimportdata関数を利用することでシートに最新のデータを反映できます。

ツールごとの役割分担

・データの抽出→Re:dash ・データの加工・ビジュアライズ→スプレッドシート

使った技術

データの抽出
- SQL（Google Bigquery）
クエリの定期実行
- Re:dash
API
- Re:dash
APIの定期実行
- Google App Script

流れ

全体

Re:dashでクエリ作成
Re:dashのクエリ実行頻度設定
Re:dashでAPI取得&スプレッドシートで importdata 関数をかく
シートを開くor更新するたびにAPIを実行し、最新のデータを反映させるためのGASを作成

1. Re:dashでクエリ作成

f:id:kohta_sasaki:20190710133657p:plain

2. Re:dashのクエリ実行頻度設定

f:id:kohta_sasaki:20190710134758p:plain

f:id:kohta_sasaki:20190710135459p:plain

f:id:kohta_sasaki:20190710135849p:plain

3. Re:dashでAPI取得&スプレッドシートで `importdata` 関数をかく

f:id:kohta_sasaki:20190710142758p:plain

f:id:kohta_sasaki:20190710153015p:plain

f:id:kohta_sasaki:20190710153211p:plain

↓こんな感じでRe:dashで出力したデータがスプレッドシートに表示されます。

f:id:kohta_sasaki:20190711190122p:plain

4. シートを開くor更新するたびにAPIを実行し、最新のデータを反映させるためのGASを作成

GAS（Google App Script）とは、スプレッドシートやフォーム、カレンダーなどGoogleのサービスをカスタマイズするための言語でJavaScriptをベースとされています。G suiteを導入している企業であれば、業務改善にも役立ちます。

GASの実装方法

f:id:kohta_sasaki:20190711190221p:plain

f:id:kohta_sasaki:20190711122450p:plain

f:id:kohta_sasaki:20190711190328p:plain

今回書いたGAS

やりたいこと=常に最新のデータが見れるようにしたい
- 要件
  - シートを開くたびにRe:dashのAPIを実行
  - リロードしたらRe:dashのAPIを実行

var configs = [
  {
  //ここでシートを指定
    "sheetName": "シート1",
  //importdataを記述したセルを指定
    "cell": "A2",
  //ここに先程importdataで使ったのと同じurlをいれる
    "url": "http://redash.xxx/api/queries/xxx/results.csv?api_key=~~~~~~~"
  },
  /* 下のような形式で設定をカンマで区切って追加して保存すれば、反映されます(色んなところのカンマは忘れがちなので気をつけてください)
  ,
  {
    "sheetName": "",
    "cell": "",
    "url": ""
  }
  */
];

// ファイルが開かれた or ブラウザが更新されたときに実行される関数
function onOpen() {
  // 上で設定したconfigsのそれぞれ(forEach)のconfigを元にsetImportRedash関数を実行する
  configs.forEach(function(config){
    setImportRedash(config.sheetName, config.cell, config.url);
  });
}

// 「シート名、セル番号、redashのURL」 を受け取ってセル(redashから取ってくるデータ)を更新する関数
function setImportRedash(sheetName, cell, url) {
  var timestamp = Utilities.formatDate(new Date(), "JST", "yyyyMMddHHmmss")
  var formula = Utilities.formatString('=IMPORTDATA("%s&seed=%s")', url, timestamp)
  SpreadsheetApp
  .getActiveSpreadsheet()
  .getSheetByName(sheetName)
  .getRange(cell)
  .setValue(formula);
}