追記: 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に書いてるんだろうと思いつつ...職種を問わず、この情報が少しでも役立つ人がいればうれしいです！