追記: 12/2 18:38

こちらの記事は当初Livesense Advent Calendar 2015の2日目の記事として投稿しましたが、会社の大先輩であるy-kenさんに後押しされて、急遽Treasure Data Advent Calendar 2015の2日目としても登録させていただきました。

進捗どうですか？

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

要約

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

KPIを可視化しよう

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

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

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

作り方

Googleスプレッドシートを用意します。

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

Treasure Dataを用意します

転職会議では各種KPIの測定にTreasure Dataを利用しています。とても便利です。

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

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

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

結果の出力設定をします

クエリ書くところの上にこんなボタンがあるかと思います。ここで設定します。

出力設定

例えば、「KPI_dashboard」というスプレッドシートの「raw_data」Sheetにデータを出力させたいならこんな感じです。

なお、注意がある通り、Googleアカウントの認証が別途settingsページで必要です。

実行スケジュール

今回は毎時のデータがほしかったのでhourlyを使いましたが、crontabの記法で細やかな設定をすることも可能です。

設定ができました！

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

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

データを整形する

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

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

ピボットテーブル使えば一瞬...なんですけど、後述する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		Treasure DataによってSheetが追加されて
2		元々のSheet名に`_tmp`とつけられて
3		追加されたSheetの方が元のSheet名になって
4		元々あったSheetは削除される

結果: 関数の参照がうまくいかない

そこで、文字列をセルの参照へと変換する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)で割った日毎の目標を並べるといい感じです。

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

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

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

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

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

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

最後に一言

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

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