Treasure DataとGoogleスプレッドシートで作るお手軽KPIダッシュボード


# 追記: 12/2 18:38
こちらの記事は当初[Livesense Advent Calendar 2015](http://qiita.com/advent-calendar/2015/livesense_1)の2日目の記事として投稿しましたが、会社の大先輩であるy-kenさんに後押しされて、急遽[Treasure Data Advent Calendar 2015](http://qiita.com/advent-calendar/2015/td)の2日目としても登録させていただきました。

# 進捗どうですか？

なんて、しょっちゅう言われるのイヤですよね。
今日はそうならないようにKPIを見える化するためのお手軽な方法を書きます。

# 要約
Treasure Dataの出力先をGoogleスプレッドシートにして、`INDIRECT()`関数を利用した集計Sheetでグラフ作って公開すると、KPIが可視化されるので、ビジネス的にもチームのモチベーション的にもいい感じ。

# KPIを可視化しよう

僕の所属する転職会議メディアグループでは、過去にもこんな感じのプロジェクトを発足させてKPIの向上に取り組んでいました。

* <strong> <a href="//www.slideshare.net/KurosawaChihiro/2-42758053" title="登録数2倍にしてと言われた時の正しい対処法" target="_blank">登録数2倍にしてと言われた時の正しい対処法</a> </strong>
* <strong> <a href="//www.slideshare.net/tsuyoshika/2-52482724" title="「コンバージョン数を2倍にしてくれ」と言われた時の対処法 " target="_blank">「コンバージョン数を2倍にしてくれ」と言われた時の対処法 </a> </strong>

僕もこの2ヶ月ほど、そんなプロジェクトに取り組んでいたのですが、言うまでもなくそこで大事だなぁと思ったのがKPIの可視化です。
**個々の施策の振り返りがしやすい**、 **次やるべきことの分析を立てやすい**といった、比較的想像しやすいメリットに加えて、 自分のコードがチームやプロダクトにどれだけ貢献してるのか可視化されるので **メンバーのモチベーションアップ**にも繋がります。

リブセンスの転職会議メディアグループがどんな方法でKPIを可視化してるかご紹介します。

# 作り方
## Googleスプレッドシートを用意します。
先にTreasure Dataが結果を出力するスプレッドシートを用意しておきます。
例えば今回、タイトルは「KPI_dashboard」とします。
※なお、リブセンスではGoogle appsで各人の法人用Googleアカウントが用意されています。

## Treasure Dataを用意します
転職会議では各種KPIの測定に[Treasure Data](https://www.treasuredata.com/jp/)を利用しています。とても便利です。

### まず、Treasure Dataのクエリを書きます

例えばこんな感じでしょうか。
`kpi_analytics`テーブルから、2015/12/1から今までのレコードにおいて、 `action = conversion` であるレコードを毎時のデバイスごとにカウントしています。

```sql
SELECT 
  TD_TIME_FORMAT(time,
    'yyyy-MM-dd HH',
    'JST'),
  device,
  COUNT(1)
FROM
  kpi_analytics
WHERE
  action = 'conversion'
  AND device IN ('smartphone', 'pc')
  AND TD_TIME_RANGE(time,
    '2015-12-01',
    NULL,
    'JST')
GROUP BY
  TD_TIME_FORMAT(time,
    'yyyy-MM-dd HH',
    'JST'),
  device
ORDER BY
  TD_TIME_FORMAT(time,
    'yyyy-MM-dd HH',
    'JST'),
  device
```

### 結果の出力設定をします
クエリ書くところの上にこんなボタンがあるかと思います。ここで設定します。
<img width="1064" alt="td1.png" src="https://qiita-image-store.s3.amazonaws.com/0/51449/395f1ac2-f6ca-af5e-dfc8-acff971b5c0e.png">

#### 出力設定
例えば、「KPI_dashboard」というスプレッドシートの「raw_data」Sheetにデータを出力させたいならこんな感じです。
<img width="571" alt="スクリーンショット 2015-12-01 21.56.52.png" src="https://qiita-image-store.s3.amazonaws.com/0/51449/1e092d5b-3e61-cf72-36bd-9dbd62394590.png">
なお、注意がある通り、Googleアカウントの認証が別途settingsページで必要です。

#### 実行スケジュール
今回は毎時のデータがほしかったのでhourlyを使いましたが、crontabの記法で細やかな設定をすることも可能です。
<img width="576" alt="スクリーンショット 2015-12-01 21.57.12.png" src="https://qiita-image-store.s3.amazonaws.com/0/51449/99162567-d78d-5694-9885-33ad76d29d6c.png">


#### 設定ができました！
<img width="1065" alt="スクリーンショット 2015-12-01 21.29.57.png" src="https://qiita-image-store.s3.amazonaws.com/0/51449/5a86a33c-b2b7-a4ca-19ec-421d6ba08e08.png">

一度実行してみましょう。

## Googleスプレッドシートに戻ります

### データを整形する

実行が無事に完了していれば「raw_data」というSheetができているはずです。
ダッシュボード化するために、これをグラフ化したいのですが、生データのままだと、グラフにするには扱いにくかったりします。
今はこんな感じのレコードが並んでいると思うので。

<img width="286" alt="スクリーンショット 2015-12-01 22.53.22.png" src="https://qiita-image-store.s3.amazonaws.com/0/51449/d556b711-98cc-5e40-afca-d54ff5834fea.png">

せっかくならこんな感じにしたいですよね。

<img width="604" alt="スクリーンショット 2015-12-01 22.54.48.png" src="https://qiita-image-store.s3.amazonaws.com/0/51449/bf7e98b8-339f-c02b-42bb-2c0a8fc6f2ca.png">

ピボットテーブル使えば一瞬...なんですけど、後述する`INDIRECT()`関数の利用もあり、ぐっとこらえて別Sheetを作ります。
deviceが`hoge`かつ日付が`XXXX-XX-XX XX`である値を取ってくる集計用のSheetを作れば済むのでちょっとだけ頑張りましょう。

こういう`VLOOKUP()` で取りにくい複数条件の値を取得したいとき、僕は `sumproduct()`関数を使うことが多いです。これはSIerにいた頃の手癖です。

```
=SUMPRODUCT((raw_data!$A$2:$A=$A2)*(raw_data!$B$2:$B=B$1)*(raw_data!$C$2:$C))
```

相対参照と絶対参照を考えると、smartphone列とpc列のセルに上の関数をペーストすればうまくいきそうです。

### 注意 -- `INDIRECT()`関数を利用しよう

実は上の`SUMPRODUCT()`関数では不完全です。
というのも、Treasure Dataが「raw_data」というSheetを出力する際、既に「raw_data」というSheetがあると(つまり二度目以降の出力のとき)、スプレッドシートに同名のSheetは作成できないという制約上、Sheet名を一時的なものに変更しながら出力をしてくれます。

その結果、上の`SUMPRODUCT()`関数で記述していたSheet名が、スプレッドシートが一時的なSheet名に書き換えてくれるという挙動をとるため、このままではTreasure Dataのデータ出力の度に関数が書き換えられてしまいます。

|No.|画像|説明|
|:-:|:--|:---|
|1|<img width="258" alt="スクリーンショット 2015-12-01 23.38.46.png" src="https://qiita-image-store.s3.amazonaws.com/0/51449/b59c5a72-1902-60a2-cf16-79ab4da51904.png">|Treasure DataによってSheetが追加されて
|2|<img width="290" alt="スクリーンショット 2015-12-01 23.39.06.png" src="https://qiita-image-store.s3.amazonaws.com/0/51449/6254e77e-cf44-97a0-e403-9398764a860e.png">|元々のSheet名に`_tmp`とつけられて
|3|<img width="227" alt="スクリーンショット 2015-12-01 23.39.25.png" src="https://qiita-image-store.s3.amazonaws.com/0/51449/078a2e9a-302a-4a82-dd4f-2bc659f192e9.png">|追加されたSheetの方が元のSheet名になって
|4|<img width="126" alt="スクリーンショット 2015-12-01 23.46.47.png" src="https://qiita-image-store.s3.amazonaws.com/0/51449/26b63ec9-bb6b-914f-1ae1-46b23e98e5ee.png">|元々あったSheetは削除される|

結果: 関数の参照がうまくいかない
<img width="754" alt="スクリーンショット 2015-12-01 23.49.45.png" src="https://qiita-image-store.s3.amazonaws.com/0/51449/a789ff23-55fa-ce0c-303a-c446e7bb033e.png">

そこで、文字列をセルの参照へと変換する`INDIRECT()`関数を利用します。
こんな感じですね。

```
=INDIRECT("raw_media!$A2:$A")
```

これと先ほどの`SUMPRODUCT()`関数を組み合わせるとこんな風になります。

```
=SUMPRODUCT((INDIRECT("raw_data!$A$2:$A")=$A2)*(INDIRECT("raw_data!$B$2:$B")=B$1)*(INDIRECT("raw_data!$C$2:$C")))
```
~~長い...インデントしたい...。~~
こちらを各セルに貼ればOKです。

### グラフを公開する
あとは、グラフをさくっと作っちゃいましょう！
その際、日毎の実績を積み上げた数字と、月間の目標を(日数 * 24)で割った日毎の目標を並べるといい感じです。

<img width="833" alt="スクリーンショット 2015-12-02 0.07.28.png" src="https://qiita-image-store.s3.amazonaws.com/0/51449/8b15210e-03ca-df56-e20c-66a07214c054.png">

あとはA列とE列・F列を選択して、上のメニューから挿入->グラフを選択し、折れ線グラフやタイムラインを選択すると完成です！

<img width="656" alt="スクリーンショット 2015-12-02 0.08.58.png" src="https://qiita-image-store.s3.amazonaws.com/0/51449/a9612f10-337b-c48d-9135-8bd37d3f2e48.png">

こうやって見るとグラフが目標を超えた瞬間や、だんだん目標に追い上げられていることが一目瞭然ですね。
(今回は例として短い期間で作りましたが、月間の毎時でグラフを作るとハラハラします)

せっかくなんでこのグラフ、スプレッドシートの機能を使って、社内の情報共有ツールで公開します。

<img width="668" alt="スクリーンショット 2015-12-02 0.09.16.png" src="https://qiita-image-store.s3.amazonaws.com/0/51449/d79169b2-e150-73b4-9aa4-a060407a6b2a.png">

グラフの右上の▼を押して、「グラフを公開」を押せば、リンクまたはiframeでグラフを公開することが可能です。

リブセンスでは、情報共有のツールにAtlassianのConfluenceを利用していますので、必要なグラフを集めたダッシュボードページに、このグラフも埋め込みます。
Qiita:Teamでもiframeタグの埋め込みができるようですね。

あとは、社内のディスプレイに常時映したり、定例のミーティングなどで確認したりすればいいと思います！

# 最後に一言
今年SIerからWeb系企業に転職してきたのに、なんで ~~Excel~~スプレッドシートの関数をQiitaに書いてるんだろうと思いつつ...職種を問わず、この情報が少しでも役立つ人がいればうれしいです！