この記事は、All About Group（株式会社オールアバウト） Advent Calendar 2020 13日目の記事です。
こんにちは、データエンジニアの@ondaljhです。
自分が所属しているマーケティング開発部のデータ基盤チームではデータ基盤の整備やデータガバナンス活動と一緒に各部署へのデータ活用も声かけています。今回はそのデータ活用のために行ったことを紹介したいと思います。
想定読者
今回はBIツールで利用するための中間テーブルを作成した話がメインになります。従って、下記のことで悩んでる方をこの記事の想定読者としています。
- あるテーブルのレコード数が多すぎて重くてデータ活用し辛い
- その上、データ粒度が細かすぎてBIツールで利用するためには加工が必要だな...
- 日次データはあるけどBIツール利用のためにはまとまったデータが欲しいな...
やりたいこと
オールアバウトではタイアップ記事やタイアップ記事へ誘導するための広告クリエイティブを作成する企画制作グループがありまして、今回の話はこの企画制作グループからの問い合わせがきっかけになりました。企画制作グループでは今まで数多くのタイアップ記事や広告クリエイティブを作ってきて、これからも作っていくことでしょう。そこで、特定のキーワードを使って過去に実績が良かった広告クリエイティブを簡単に検索できないかの問い合わせがデータ基盤チームに来ました。何回かの打ち合わせの結果、企画制作グループで必要とするものの要件が決まりました。
- キーワード検索で、関連する過去広告クリエイティブ一覧が表示される
- キーワードは複数でも検索できること
- 一覧には検索キーワードの類似度も表示されてほしい
- 実績として下記のような指標が欲しい
- Impression
- Click
- Ctr
- 広告クリエイティブを経由したタイアップ記事からの送客数
- 広告クリエイティブから遷移されたタイアップ記事の平均読了率と平均滞在時間
※各指標の説明は割愛させていただきます。
検索キーワードの類似度はデータ基盤チームのデータサイエンティストに任せることにしました。指標についてはデータ基盤の方では常に集計しているデータで、社内用のBIツールでも出していたので、データの確保は問題ない認識でした。(が、大きな問題がありました...)
現状
問い合わせがあった最初には、各指標がそれぞれのテーブルに格納されてはいるけど、既にデータはあるから少し複雑にはなるけど1つのSQLを作成してBIツールのRedashに登録するだけで実装できると思っていました。そこで実際にSQLを作成して実行してみました！が、全然戻ってこない...なんでだろう。。？
問題はシンプルで、Impressionデータを格納しているテーブルでした。ただ下記のだけです。
- テーブルのレコード数が3億件以上
うん、レコード数が3億件あるよねーうん？？3億件？？
そうか...仕方ないのか...このテーブルは粒度も細かいし、2016年10月に現役デビューしたから、もう4年分以上の日次データがたまっているのか...
ちなみに、今回作成したSQLは下記のような構成です。
- 検索キーワードからヒットするクリエイティブ情報を取得する
- 1で取得したクリエイティブ毎の指標データを取得する
Impressionテーブルは日次の集計データが入っていて日付カラムを基準としてパーティションやインデックスが作成されています。今回のようなクリエイティブに関するカラムでのパーティションは作成していなかったのでSQL処理が重くなるのは仕方ないですねー
考えたこと
ここで問い合わせの原点に立ち戻って考えることにしました。必要なデータは広告クリエイティブの実績。
- ここで言ってる実績は日毎の実績ではなく、トータルでの実績があればいいので、わざわざ日次集計データからデータを取得してグルーピングしたりする必要はない！
- また、「過去実績」の確認になるため、現時点で配信中の広告クリエイティブは出さなくていい！
この2点に着目して、既に配信が終わってるクリエイティブについては、トータル実績を格納する中間テーブルを作るだけで良いのでは？という結論になりました。ただ、下記の理由で日次集計テーブルと今回の中間テーブルの間にもう1つのテーブル(以降、集約テーブル)を用意することにしました。
- システムはトラブルがつきものであるため、リカバリしやすい構成にしたい
- Impressionデータだけではなく、他の指標も一緒に格納した中間テーブルにした方がBIツールからの呼び出しが早い
また、集約テーブルにはImpressionの細かい粒度ではなく、BIツールで利用するための必要最小限の粒度にすることで無駄なレコード数の増加を防げました。
ここまでの構成を図で表しますと下記のようになります。
結果
実際にこの構成にして、中間テーブルを参照するようにSQLを作成して実行した結果、5秒以内で求めてたデータが戻ってきました！やった！当たり前といえば当たり前ですが、中間テーブルの場合データ件数が3万件ちょっとくらいなのですぐ戻ってきますよねー
ちなみに、集約テーブルも含めて、各段階でのレコード件数は下記のようになります。
実際のMySQLの実行計画を見てもその違いが分かります。
※実行計画は一部のカラムのみ公開させていただきます。
各指標毎のテーブル利用時の実行計画
中間テーブル利用時の実行計画
さいごに
今回用意した中間テーブルとデータサイエンティストが用意した類似度をBIツールで合わせて表示することができ、企画制作グループからも喜びの声をいただきました。データがあるからそのデータをそのまま使うのではなく、目的に合わせて中間テーブルを導入するなど、一工夫することでデータ活用しやすいデータが整備されたと思います。
レコード数が多くて今のままではSQL結果が戻ってこない等で悩んでる方やBIツール活用のためのデータ構造を検討中の方に、この記事が少しでも役に立ちましたら幸いですー