ナウでイケてる格安の分析基盤&BIツールをGoogleスプレッドシートとBigQueryを中心に構築する方法

こんにちは！株式会社LITALICOでエンジニアをしております石田です。
『LITALICO Engineers Advent Calendar 2016』 2日目の記事となります。

1日目は弊社CTOの岸田がサービス開発は9割が失敗する - 5つの診断パターンからみるサービス設計がうまくなるコツ（導入編）としてサービスを開発する際に考慮すべき事が記事でした。
2日目は、実際にサービスをリリースしたあとに、ユーザの行動を分析し改善するための、分析基盤&BIツールをLITALICOはどのように構築しているかを説明します。

サービスを運用する上での課題

Webサイトやアプリを運営していると、数値分析するのにGA(Google Analytics)では物足りなくなってくることがあります。特に、無料のGAでは生ログが扱えるわけではないのでユーザごとの細かい数値やJOINした数値などが取りにくいです。

その際に、自前で分析基盤を構築しようとなるのですが、よほどお金があるところでない限り高額なBIツールは使えないので分析する際にチームに共有するWeb UIに困ってしまいます。re:dashなどもあるのですが、サーバ構築しなければならず、やや面倒です。グラフもやや自由度にかけます。

解決策

LITALICOでは、かなり格安な割にいけてる分析基盤&BIツールを構築したのでその内容を共有します。概要は以下です。

• BIツールとして、Google Sheetsを利用。取得してある数値をグラフでいい感じに表示
• Google sheetsで以下のようなグラフが毎日自動更新される。
• Slackで数値を毎日共有
• データはBigQueryに集約し、Apps Scriptを通してSQLを発行し、Google Sheets上で表示
• BigQuery中間テーブルもApps Scriptを通して作成
• ユーザの行動ログはFluentdを通してBigQueryに集約

以下は、Google Sheets上でのKPIイメージです（数値は ランダム です）

上記の数値を出すために、以下のようなシステム構成を取っています。

分析基盤&BIツールのポイント

• BigQuery: すべてのデータは後からJoin出来るようにBigQueryに格納
  • DBスナップショットやマスターデータ: MySQLのデータはEmbulkで1日1回夜間に前日のデータをBigQueryにシンク
  • 行動ログ: ユーザの行動系のログ(クリックしたとか)はクライアントのデータならFirebaseを利用。サーバのデータならFluentdを経由してBigQueryに送る
• Apps Script: BigQueryへの操作などはApps Scriptを通して行う.
  • BigQuery上のテーブルをJoinしたりまとめた中間テーブルを作る
  • BigQueryにクエリを発行し、その結果をGoogle sheets上に反映する。トリガーの機能(cronのようなもの)により毎日定期的に更新できる
  • Google sheets上のKPIをSlackに毎日なげる
• Google Sheets
  • 詳細なKPIデータをグラフとともに表示。Apps Scriptが数値を1日おきに更新し、毎日最新に保たれる
  • Google Analyticsのプラグインを使いAnalyticsのデータをSheet上に反映

以下に、実際どのように実装しているかを記します。

BigQuery

基本的にデータはBigQueryにすべて入れています。理由は以下です。

• テーブルをまたがったJOINなどのクエリが高速に実行できる
• Google Sheet上からApps Scriptを使って操作できる
• 比較的安価に利用できる
  • ストレージ
    • $0.01 / GB / 月 # 90日以上更新がないテーブルのストレージ
    • $0.02 / GB / 月 # 上記以外　
  • ストリーミングインサート: $0.01 / 200MB
  • クエリ: $5 / TB
  • その他無料
• EmbulkやFluentdなどBigQueryにデータを入れるためのツールが揃っている。

別のところでRedshiftを使ったことがあったのですが、一度スキーマレスで入れれる楽さを覚えたら戻れなくなっています。

DBスナップショットやマスターデータ

MySQLにはいっている指定のテーブルをEmbulkに入れています。日別に分けてテーブルをいれてもいい(user_20161120, user_20161121)のですが、経験上あまり過去のデータは必要なかったりするのとストレージコストもかえりみて、userテーブルだったら同じ名前のテーブルをBigQueryに作ってます。

Digdagなどのワークフローツールで、テーブル同期を管理してもいいのですが、そこまでデータ量がないので、Embulkのスクリプトはcronで管理してます。

行動ログ

行動ログというのは、例えばユーザがクリックしたとか、ボタンを押したとか、ユーザの行動に起因するログです。
例えば、記事のPVをとっていたとして、データベースに意図的に行動ログを保存しなければ、最新のPVしかはいってません。そうすると、どのユーザがいつクリックしたかわかりません。
その為に、行動ごとにログを保存します。このような行動ログは協調フィルタのレコメンドにも使えたりします。

クライアント側の行動ログ(Firebase)

クライアントアプリからの行動ログはfirebaseを利用しています。2016年8月24日に「Announcing Realtime Exporting of your Analytics Data into BigQuery」の通り、リアルタイムでBigQueryに同期できるようになりました。20分ほどでほぼ同期されているので、よりリアルタイム性が高いサービスにも使えるようになりました。

1点注意として、BigQueryに同期されたFirebaseのレコードは以下のようにRECORD(配列)が入っていたりするので、ディメンションで絞り込んだりするのにクエリがかなり複雑になったりします。時々、Standard SQLを使ったりしなければならずやや不便です。そこらへんどうやっているかはいずれ書こうと思います。

サーバ側の行動ログ(Fluentd)

サーバ側はRailsからFluentdにログをはけるようにしてあります。
理想はユーザIDの付与してあるリクエストごとのログを取るのがいいのですが、例外起きたときはログを出さないようにするなど、やや面倒です。
行動ログ用のgemを作ったのですがそれは次回の記事で触れます。

Fluentdの設定などはユーザの行動ログをBigQueryで分析！RubyからFluentdを通して、BigQueryへ任意のログをJSON形式で保存するや、td-agentをAmazon Linuxに導入する
でふれています。

Apps Script

Apps Scriptは以下の3つの働きをしています。

1. BigQueryに中間テーブルをつくる
2. BigQueryにSQLクエリを発行し、その結果をGoogle sheetsのシートへ更新する
3. Google sheets上のデータをSlackになげKPIレポートとする

1に関しては、BigQueryでクエリのコストと実行時間を節約！ - Apps ScriptでBigQueryのテーブルからBigQuery上に中間テーブルを作成する で書きました。
2, 3に関してはYamottyさんがかいた「まだBIツールで消耗してるの？ ~ サーバレス・KPI分析ダッシュボードをGAS + Slackで」に詳しく書かれています。

Google Sheets

KPIのグラフはGoogle Sheetsを使って実現しています。シートは以下のようになってます。

• GrowthKPI: 主に見るシート。KPIグラフと数値が羅列してある。もとのデータはKPIマスタから取得する
• KPIマスタ: BigQueryなどから取得したデータを日付別、項目別に羅列したもの。すべてのデータはここにあり、GrowthKPIや他のシートはここを参照する
• Report Configuration: GAからデータをひっぱるGoogle Analytics Spreadsheet Add-onの設定用
• Queries: BigQueryにSQLを発行してシートに格納するための設定
• SummaryQueries: BigQueryに中間テーブルを作成するための設定

たくさんシートがあるのですが、重要なのは、GrowthKPIとKPIマスタをわけることです。
データをKPIマスタに集約しないと、1次データか2次データ(加工されたデータ)かわからないものがそこらかしこのシートにいってしまうので、いったんKPIマスタに1次的な数値を集約しています。

(ちょっと伝わるか心配ですが、ざっくわかっていただければなと思います)

KPIマスタシートの例

直近30日の日付別に、データを別のシートから集約していきます。
横は日付で、縦はKPI項目名です

GrowthKPIシートの例

まとめ

以上のようにLITALICOでは格安でいけてるBIツールをGoogle sheetsを中心に構築しています。
本当は全員がTableau DesktopやTableau Serverをつかっていくとなかなかいけてるのですが、費用的にきつい場合はGoogle Sheetsでもそこそこ代用できます。

BIツールにお悩みの方はGoogle Sheetsも視野にいれてみるといいのではと思います。

何か質問があったり、つっこみがあったりしましたお気軽に教えてください。Twitterは@wapa5powです。

次回は、klriutsaさんがRailsについて書いてくれます