データ分析をやろうと思ったけど、同じようなクエリ何回も書いてるし、マートの設計ワケワカメになるし、なんかいい塩梅に管理できないかなぁ、と最適化を行ったら、クソほど楽ができた話
こんにちは、taskeyの田代です。
最近はゴリゴリにRailsを書いております。
弊社サービス
データ分析においてSQLをゴリゴリで書いていると、「あれ、このクエリよく書くなぁ、使いまわしたいなぁ」やら「このマートってどういう設計だっけ？これって抽出やら定義やらあってるっけ？」など、ちょこちょこと変な手間が発生しがちです。
peepは2020年7月初旬にプラン設計を大きく変更したことにより、分析作業量が急増。
上記の問題を避けて、少ないリソースで効率良く分析が行えるよう、データマート整備から着手、その後、分析作業を行いました。
今回は、その時のデータマート最適化の取り組みをご紹介させて頂けたらと思います。
【関連技術】
- SQL
- BigQuery
- Redash
- trocco
- Adjust
- firebase
【対象者】
- マーケター
- 分析担当のエンジニア
- SQL好きな人
- その他分析をしたいすべての人達
分析基盤ってどんな構成になってるの？
こんな感じ
こちらが弊社の分析基盤の全体図となっております。
基本的にはBigQueryをデータマートとしてそこから抽出、分析をおこなっております。
ちなみにRedashはVersion7をHerokuに立てて使用しております。
（そろそろ8にしたい。。グリッド機能使いたい）
データってどうやって移行しているの？
弊社ではデータ移行に関してはtroccoというサービスを使っております。
2018年サービス開始の急成長スタートアップのSaaSです。
データ移行を自社で開発する場合、embulkやらdigdugといったライブラリを使用する事が多いと思いますが、それらのSaaSだと思って頂けたら伝わりやすいかと思います。
troccoとても良いのでメリットを並べさせていただきます。
【メリット一覧】
- 転送出来るDBの種類が豊富
- 転送時の加工がSQLで行えるので、楽
- 転送に通知設定を付けられるので、転送失敗時に直ぐに修復を行える
- 転送間でフックを設定して、転送Aが終わり次第Bの開始といった順番に依存する転送を安全に行える
- supportが充実（slackでのやり取り、秒で返ってくる返信）
とりあえず僕は最高だと思っております。
個人的要望としては、転送設定一覧取得API等があれば、そこからデータを取得して転送SQLの管理、さらにコードベースで出来てちょっとうれしいかなとも思っております。
troccoさんめちゃめちゃ開発スピードが早いのでそのうち追加されちゃっててもおかしくないです。
おすすめのデータ転送SaaSですので、転送部分で悩んでいる方は是非調べてみてください。
※ サービスDBへの直繋ぎ分析は、分析SQLは複雑になりやすいという性質からとても危険です。データマートを通しての分析を強くおすすめします。
最適化されたデータマートの設計ってどんな感じ？それは完璧なの？
つい1年ほど前は、僕がほぼ一人でSQLを書いていたので、「あ、この分析あそこのSQLつかえるな」と直ぐにわかって何も困らなかったので最適化は特に必要ありませんでした。
ただ、社員の増加に伴い、最近は他のエンジニアまたはPMまでSQLを書くようになりました。
「あれ、似たような分析あったけどどうやって書いてるんだろう？」というようなことが増えたので、最適化を行うことになりました。
最適化のルールは下記のとおりです。
【データマート最適化ルール】
- 加工時のSQLに関しては規約を定める
- アプリケーションの動きを気にしなくて良いので、そこまでの厳密な正規化は必要ない
- 基本的にuser_id + 必要な付加情報で設計する
- table名、カラム名の命名規約の統一
- 正確性が何より大切になるので加工SQLのチェックは二重で行う
- datetime型の場合はUTC基準、JSTにしたい場合はカラム名をhoge_jst_at等にする
- データセットのコンテキストの整備（BigQuery）
- 謎のデータマートを作らない
完璧かと言われると「はい、そうです」とはなかなか言うことは出来ません。
ですが、現フェーズのリソースとのバランス的にはちょうど良いチューニングだったと思います。
実際にはデータマートを用いてどんな分析をしているの？
毎朝届くSlack通知
CMS内の作品アナリティクス
ざっくりと下記分析をデータマートから行っています。
【Redashダッシュボード】
- マーケ指標: 流入媒体別
- 経営指標: 売上、継続率等
- グロース指標: 1DayRRや各種機能の利用率等
- CS用: ユーザコード入力による、ログの確認
【Slack通知アナリティクス 】
- 毎日10:00に通知でアナリティクスが届く
- 習慣化により、よりデータドリブンで動けるようになる
【サービスCMS内編集部、作家向けアナリティクス】
- 作品作りでの指標として活用
最適化ってどうやってすすめるのが良いのだろうか？
進め方に関しては大体1週間で下記順番通りに行いました。
1. スプレッドシートに現状で存在するデータマートの一覧を羅列する
2. スプレッドシートを見ながら担当者全員で各テーブルにおいてチューニングを考える
※ 3人
3. チューニング開始
※ チューニング作業2人 チェック1人（作業者とチェック者の二重チェック）
4. チューニングされたデータマートを元に棚卸しした分析項目を可視化
※ 必要の無い分析は捨てる
5. データマートに過不足があれば再度チューニング
1番のスプレットシートをしっかり用意しておくことにより、2番での会議時間の削減出来ます。※ シートに関してはwikiとかに残しておきましょう。
3番から個別の作業となるため、リソースの無駄無く、効率良く進める事ができます。
5番の再度チューニングは必ず起きると思いますが、一度シートでの管理するようにしておけば変更が起きた際でも社内メンバーへの情報共有はスムーズです。
Reviewが入ることによりSQLレベルの向上も期待できます。
最適化を行ってみた結果どうだった？
ルールを元に最適化を行ったことにより、
データズレの減少
社内を通してのSQLの一定の基準を担保
新規分析可視化時の時間削減
等の恩恵は受ける事ができております。
ただ、サービスの成長 = table、可視化項目の増加ですので、毎月ある程度の時間を取って追従する必要は勿論あります。
今回最適化によってノウハウはある程度蓄えられたので、追従に関しても、とても大変ということは無いはずです。
また、Redash内に散らばるSQLに関して、ちょっと前に作ったRubyでredash_backupをいうgemが役に立ってくれています。
ARを用いてRedashDBにアクセスして、ダッシュボード単位でsqlファイルをまとめるというシンプルなgemです。
サクッと試せるのでよかったら触ってみてください。
grep検索出来るだけで最高ですし、抽出元のtableがわかれば依存が把握出来るので、変更の際の影響範囲が見えやすいです。
※ シンプルなgemなので、カスタマイズして使ってみてください。
