こんにちは。Studyplus事業部サーバーサイドエンジニアの山田です。

普段はバックエンドのRailsアプリケーションの開発をしていることが多いですが、今回はここ最近取り組んでいるデータ分析基盤の改善について紹介していきます。

はじめに
データ分析基盤の現状と課題
- データの収集や抽出に無駄が発生しやすい
- 改善が局所最適になりやすい/改善自体がおこなわれにくい
何から取り組んだか
全体構成を把握
- 課題のヒアリング
改善する課題/施策を選択する
- BigQueryのデータの使いづらさと料金面の課題
データウェアハウスの作成
終わりに

はじめに

スタディプラスではユーザーの学習記録をはじめ様々なデータを蓄積しており、それらのデータを活用していくためのデータ分析基盤が存在します。

わたし自身も問い合わせや障害時の調査、新しい機能の検討の中でデータが必要になった時などデータ分析基盤を使うことはよくあります。その中でこういうデータが保存されているとやりやすいのにとか、このデータはよく使うので簡単に抽出できるようにしたいなど改善したいことが出てくるようになりました。

そこで手を挙げてデータ分析基盤の改善を進めていくことになりました。

本記事では、その改善について以下の２点について紹介していきます。

どのようにデータ分析基盤の改善を始めたか
改善の中で実際に行った施策（データウェアハウスの作成）について

データ分析基盤の現状と課題

改善を進めていく上でまず出てきた大きな課題は、データの流れや使われ方などの全容を把握して管理している人がいない、またデータ分析基盤の専任のチームや人がいないということでした。

これによって以下のような状態が発生しやすくなっていました。

データの収集や抽出に無駄が発生する
改善が局所最適になりやすい/改善自体がおこなわれにくい

もう少し詳しく説明していきます。

データの収集や抽出に無駄が発生しやすい

弊社ではデータは主にBigQuery、Amazon S3、MySQLなどにためて見るようになっています。

各データソースに蓄積されているデータを把握していないと、例えばBigQueryからすぐに抽出できるデータを苦労してMySQLから抽出しようとしたり、抽出自体を諦めるといったことが発生していました。

実際にあったこととして、過去のある時点のユーザーデータを抽出しようとした時にMySQLとBigQueryから抽出しようとしたが実はS3にそのデータが保存してあるということがありました。データの保存場所を把握できていればすぐに抽出できたものをかなり時間をかけて対応をしてしまうということがありました。

データを保存し始めた当時は必要性があり追加されたが、その後あまり使われなくなり保存していること自体が知られていないデータが存在するようになっていました。

改善が局所最適になりやすい/改善自体がおこなわれにくい

BigQueryにためている主なデータはアプリのFirebaseイベントログです。一方、MySQLにはスタディプラスアプリが使うデータそのものを保存しています。

これらはそれぞれアプリの開発チームとバックエンドの開発チームが管理しているため、それぞれのチームが見える範囲での改善となりやすくなります。

またデータを使うディレクターや広告チームのメンバーがデータ抽出したい、ダッシュボードを改善したい場合に依頼先が決まっていないため、それぞれができる範囲で対応しようとすることが多くなっていました。

例えば、あるデータを抽出する時に複雑なSQLを書く必要があったり、一度に取得することができないため複数回に分けて実行し手作業でまとめたりなど抽出に時間がかかってしまうとことがありました。目の前のタスクを進めることを考えると手間はかかったとしても今の基盤からデータを抽出してしまった方が早い場合もあります。一方、トータルで考えると基盤自体の改善を進めた方がよいこともあります。

基盤を整備していく専任のチームがないためその知見を蓄積して改善を進めるというサイクルが回りにくいといった状況がありました。

何から取り組んだか

改善を行っていくにも現状を把握していないとどこから解決していくか道筋を立てることは、当たり前ですが難しいです。まずは現状の整理から進めました。

大きくは以下の２点を中心に進めました。

データ分析基盤の全体構成を把握して図にする
データを誰がどんなタイミングで利用し、どんな課題を感じでいるかを整理する

全体構成を把握

データの流れを知り、どんな使われ方をしているかを俯瞰してみれるようにまずは全体構成図を作成しました。

自分自身はサーバーサイドチームのエンジニアですが、データを蓄積するのはサーバーを経由しない場合もあります。例えば、スマホアプリのイベントログをFirebaseにためるような場合はアプリから直接Firebaseにデータをためることになります。また誰がどんなタイミングでデータを使っているか把握していない部分も多かったため社内のドキュメントをあさりつつ他のチームのメンバーにもヒアリング行い全体構成図を作成していきました。

実際に作成した図からは一部省略していますが以下のような図となりました。

データ分析基盤全体構成図

分析でよく使うデータソースは以下の３つになります。

BigQuery
- スマホアプリのイベントログ
Amazon RDS(MySQL)
- アプリケーションのデータ
Amazon S3
- サーバーのログや一部アプリケーションのデータを加工して保存

それぞれのデータソースは多くの場合はRedashを通してクエリを実行しています。また、KPIなど定期的に見るものはダッシュボード化されている状態でした。アドホックにデータを見る場合はRedashを経由せずにBigQueryのコンソール上で直接実行するような場合もあります。

課題のヒアリング

改善のためデータを活用している部署やメンバーに状況を聞くことは重要です。そこで各部署やメンバーがどんな用途でデータ使っているかや使う中で感じている課題をヒアリングして洗い出すということを行いました。

ヒアリングはMTGで聞いたり、スプレッドシートに記入してもらうよう呼びかける形で行いました。その結果以下のようなジャンルで課題を抽出することが出来ました。

分析に使いたいデータの不足
ダッシュボードの使いづらさ
SQLのパフォーマンスの問題
基盤の運用コスト
基盤やテーブル構成の把握の難しさ

改善する課題/施策を選択する

課題を抽出して改善点が見えてきたので、次は何から改善を進めていくかです。

データ分析基盤の改善は、現時点では組織としてチームを作ってトップダウンで行っているのではなく、草の根的にボトムアップで行っている段階でした。そのためまずは早期に成果を上げ、信頼を勝ち取っていくことが重要だと考え¹以下の観点で行っていく施策を検討していきました。

効果が出やすい
あまり大勢の人を巻き込まずに少数で進められる
改善を短期間でできる

BigQueryのデータの使いづらさと料金面の課題

ヒアリングした課題の中にBigQueryを使った分析での課題がありました。

アプリのFirebaseイベントログをBigQueryにためて分析できるようにしていますが、イベントログに対して直接SQLを発行して分析するような使い方になっていたため下記のような課題がありました。

コスト面の課題
- Firebaseのイベントログは膨大なためクエリを実行するたびにそれなりのデータ容量をスキャンすることになります。BigQueryは使用したデータ量で課金されるため、使いすぎるとコストが膨れ上がってしまいます。そのため、予算を超えないように一日に一定の料金以上のクエリを実行できないよう設定がされています。実際の利用者に確認したところ、分析で何回かクエリを実行しようとすると扱うデータによってはすぐに上限までいってしまうということが発生していました。
SQLの扱いづらさの課題
- BigQueryではネストされた列や繰り返し列を扱うことができます。分析によく使う列が繰り返し列になっていると毎回UNNESTするなどSQLが複雑になってしまうことが多くありました。

これらのことが影響して下記のようなことが発生していました。

料金を気にしてBigQueryのデータを使った分析を遠慮してしまう。
複雑なSQLを書くのに時間がかかる。SQLが得意な分析者以外はBigQueryを使うことを諦めてしまう。

データは使われて初めて価値があり蓄積するだけではただのコストになってしまうため、とてももったいない状況でした。

データウェアハウスの作成

上記のBigQueryの課題は解決できれば効果も大きいです。また、対応案を検討し短期間で少人数対応できる方法としてデータウェアハウス²の作成を選択しました。

ここから具体的に対応した施策について説明していきます。

よく使われているデータ/SQLを抽出する

利用者にヒアリングを行いよく使われるデータを抽出していきました。

例えば、アカウント登録やログインのイベント、DAUなどは分析の中でよく使いますがそれらと合わせてユーザーの属性を利用することも多いです。最初からそれらのデータが使いやすい形で保存されていると毎回イベントログ全てに対してSQLを実行する必要がなくなりデータ容量を抑えることができます。またSQL自体もシンプルになります。

以下のイメージのように予め扱いやすいよう加工したデータを組み合わせることでほしいデータが抽出しやすくなります。

DWHイメージ

ただし、あまりに多くのデータを別テーブルに入れても滅多に使われないテーブルが出てきます。そうするとデータを加工する時に実行するクエリのコストやデータの保存自体のコスト、多くのテーブルを管理する手間などがかかり逆にコストが上がってしまいます。

それを避けるためにまずは下記のようなデータに絞って別テーブルに入れました。

KPIなど定期的に抽出することがわかっているデータ
既存のクエリのWith句で頻出するデータ

また、Firebaseイベントログの中にほぼ分析で使わないイベントが多くあることもわかったため、それらを除いたテーブルを作成しました。それにより元々のイベントログのテーブルの容量から 1/20 ほど圧縮されたテーブルを作成して分析ではそちらを使うようにしました。

クエリのスケジュール実行

データウェアハウスは取り込んだ生のイベントログに対して日時でクエリを実行して作成していくようにしました。クエリをスケジュールして実行する仕組みは、BigQueryで用意されているクエリのスケジュール機能を選択しました。

理由は設定が簡単ですぐに使い始めることができるためです。手軽に設定できる分クエリが増えてくると管理が難しくなってくる可能性もあります。しかし、弊社のBigQueryの使用状況ではそこまで多くのクエリをスケジュールすることは当面の間なさそうなため問題ないと判断しました。

詳細な設定の手順は公式ドキュメントを参照いただければと思いますが、以下のようにクエリのスケジュールから画面に従い順番に設定してくことですぐに使い始めることができます。

スケジュールされたクエリの新規作成からスケジュールクエリ1

クエリのスケジュールや書き込み先のテーブルを設定スケジュールクエリ2

利用状況のモニタリング

施策をやりぱなっしで終わらせずに取り組んだ施策を継続的に改善していけるように、クエリの使用状況を可視化してモニタリングできるようにしました。

具体的には誰がどれぐらいBigQueryを使用しているかを可視化する簡単なダッシュボードを作成しました。 BigQueryのコスト可視化ダッシュボードを10分で作るを参考にさせてもらい、Googleデータポータルを使った以下のようなダッシュボードにしました。

クエリ使用状況

単純に全体のコストをみるだけであれば、GCPのコンソールで見ることも可能ですが、誰がどのようなタイミングで使っているかというのも見ていきたいのでユーザー別にクエリの使用状況を可視化するようにしました。

利用者の数が多い場合は、部署などグループ毎に可視化するほうが有効かもしれません。

改善後

アプリのイベントログに対して直接クエリを実行していたのを、データウェアハウスを作成してそちらを参照するようになりました。

データ分析基盤全体構成図_改善後

データの容量の課題

容量の小さくなったテーブルを見るようになったため、ダッシュボードによっては1/1000ぐらいのスケールで使用する容量を減らすことができました。使用できる容量の上限に余裕ができるようになったためアドホックな分析が必要になった場合も以前と比較して容量を気にせず気軽にクエリを実行して分析できるようになりました。