10年選手の広告プラットフォームのデータモデリングをいい感じにした話

Slide 1

Slide 1 text

10年選手の広告プラットフォームのデータモデリングをいい感じにした話 CARTA HOLDINGS x ちゅらデータ合同イベントデータモデリングとデータ基盤の構築・運用村上柊 @shu22203 CARTA HOLDINGS fluct 開発本部

Slide 2

Slide 2 text

自己紹介 ● しゅう ○ 村上柊 ○ @shu22203 ● 2019年新卒入社4年目 ● 好きなもの ○ Celeste ○ ずっと真夜中でいいのに ○ スノボー ● 嫌いなもの ○ データがズレること ○ CASE式で頑張ってるSQL

Slide 3

Slide 3 text

今日話すこと ● レポート作り直しに至った経緯 ● 既存モデリングの辛い話 ● どうしたか ● これから

Slide 4

Slide 4 text

今日話さないこと ● ETL基盤をリプレイスした話 ● データ分析基盤の話

Slide 5

Slide 5 text

まずはじめに再モデリングにおいてやったことはこれだけです

Slide 6

Slide 6 text

集計層では1つの事実を正しく集める見たい側の都合は別の層で解決する

Slide 7

Slide 7 text

以上ですご清聴ありがとうございました

Slide 8

Slide 8 text

本題です

Slide 9

Slide 9 text

fluct ● メインはSSP事業 ● メディア向け広告配信プラットフォーム ● JSタグを貼るといい感じに広告が出るシステム ● 2010年スタートの国内最大級 SSP

Slide 10

Slide 10 text

SSPの広告レポートとは・特徴 ● ある期間において発生したインプレッション数(広告表示回数)・クリック数・売上などが知りたい ● 見たいディメンション・指標がとにかく多い ○ サイト / 広告枠 / デバイス / OS / etc … ● 数がとても多い ○ x億 impresssions / day 以上 ○ 生のログに対してレポーティング文脈でクエリするのは現実的ではない ○ ある程度の集約が必要

Slide 11

Slide 11 text

作り直しに至った経緯

Slide 12

Slide 12 text

時は2020年(入社2年目) fluct の人々のレポートに対する不満は青天井状態であった

Slide 13

Slide 13 text

人々の想い ● PC, スマホレポートで画面が別れてるのが辛いです！(見れる軸も違う) ● 長めの期間で引こうとするとレポートが全然返ってこないです！ ● テーブルの繋がりが全然分からなくてSQLが書けないです！ ● あの画面とこの画面で数字がズレててどっちが正しいんですか？なんでズレてるんですか？ ● 新しくこういうディメンション・指標でレポートが見たいんですけど出来ないですか？ ● xx(サイト名)を変更したので再取り込みお願いします！

Slide 14

Slide 14 text

どうしてこうなったか(想像含) ● fluct 初期、当然スマートフォンは存在していなかった ○ (詳しい人向け) RTB すら無かった ● 今ほどデータウェアハウス・計算機・ストレージなどが充実しておらず、部分ごとに集計するのが精一杯だった ● 運用システムのノリでモデリングしてしまい必要以上に正規化されていた ○ クエリが難しい・パフォーマンスも悪い ○ 一方で不適切な非正規化もあり本来不要な再取り込み依頼が多発 ● → 10年間のビジネスドメインの変化・技術進化にシステムが追いつけていなかった ● → 技術的負債

Slide 15

Slide 15 text

新卒2年目半ばの私私 > もはや既存の仕組みの上でこれらを改善するのは無理だから、全部作り変えて最強のレポート基盤作って世界救ってやんよ！(絶望) (そのとき当時の私より詳しいレポート戦士はみな巣立っていってしまっていた(絶望)) > よろしく！！ CTO みたいな感じでスタート

Slide 16

Slide 16 text

何をやったか ● データモデリング ● データストアの刷新 ● ETL基盤刷新 ● BIツールの社内導入

Slide 17

Slide 17 text

何をやったか ● データモデリング ←今回はここ ● データストアの刷新 ● ETL基盤刷新 ● BIツールの社内導入

Slide 18

Slide 18 text

データモデリング

Slide 19

Slide 19 text

ディメンションモデリング

Slide 20

Slide 20 text

ディメンションモデリングとの出会いいくつかモデリング案を出すもしっくりこない日々を過ごす中・・ > こんな本見つけたから読んでみるわドンッ(鈍器) (英書850ページ)

Slide 21

Slide 21 text

スタースキーマの衝撃 ● 運用DBのモデリングとは全く発想が違うことを思い知った。 ○ 詳しく説明する時間が無いので本を読んだりしてください ■ FYI: https://zenn.dev/pei0804/articles/star-schema-design ● 1つの事実(ファクト)の周囲に複数のディメンションが存在するモデリング手法 ● 冗長であっても分析に関心のあるディメンションであれば非正規化して記録する ● これならなんとか戦えそうという気持ちになれた

Slide 22

Slide 22 text

ここからは実例とともに

Slide 23

Slide 23 text

失敗事例1 複数タイミングに発生する事実が 1つのテーブルに集約されている

Slide 24

Slide 24 text

どうなっていたか 1テーブルに impression, click カラムが存在していた。集計バッチではまず impression を記録し、click はとりあえず0を入れる。その後 click 集計で↑で入れたレコードを UPDATE する。

Slide 25

Slide 25 text

何が辛いかあるタイミングで見ると、本当にクリックが0なのかまだ集計されていないだけなのか分からない。 → テーブルの利用者が集計側の知識を知っていないといけない。 →集計に依存が発生しているので、impression がコケると click も入らない。どちらかのファクトの粒度に引きづられて、もう片方の集計の粒度を落とさなければいけなくなる。 → レポーティング・分析の幅が狭くなる。

Slide 26

Slide 26 text

どうしたか impression, click でそれぞれ素直に事実を集める。ファクトを横断して見るために、ビューを作った。 before after ※ 説明のために簡素化しています

Slide 27

Slide 27 text

失敗事例2 同じタイミングに発生する事実が複数のテーブルに重複して記録されている

Slide 28

Slide 28 text

どうなっていたかさっきの逆 impression という事実が様々な切り口で複数テーブルに記録されている。見る画面もその数だけ存在している。 ● オーダーレポート ● 枠別レポート ● デマンドレポート ● PCレポート ● スマホレポート

Slide 29

Slide 29 text

何が辛いかテーブルによって集計方法がばらばらなので、画面によって数字のズレが起こる。 → どれが正しいんですか？ズレの原因はなんですか？というお問い合わせ対応で本来やるべき仕事の時間が奪われる。

Slide 30

Slide 30 text

どうしたか before after 1つのファクトに対するテーブルは1つにしてレポーティング・分析対象のディメンションを全てそこに含める ※ 説明のために簡素化しています

Slide 31

Slide 31 text

失敗事例3 不必要な正規化・非正規化が行われている

Slide 32

Slide 32 text

どうなっていたか ● あるディメンションから導出できるディメンションが記録されていない ○ 例）サイト → 媒体社は導出できるので記録しない ● 運用システムで頻繁に変更されうる値がファクトテーブルにそのまま記録されている(※ あえて発生時の値を記録しておく手法もある (Slow Changing Dimension)) ○ そしてサロゲートキーは記録されていない

Slide 33

Slide 33 text

何が辛いか ● 運用DBのモデリングに詳しくないとSQLが書けない。書けたとしても重厚長大な SQLになる。 ● 運用DBで変更が起きた際に再取り込み依頼が頻発する。 ○ xx(サイト名など)を変更したので再取り込みお願いします！！ ○ 変更前の値には関心がないケースがほとんど

Slide 34

Slide 34 text

どうしたか before after レポーティング・分析文脈で関心のあるディメンションは非正規化し冗長に記録するディメンションはサロゲートキーを記録するするとクエリは概ね depth 1 JOIN で済むようになる ※ 説明のために簡素化しています

Slide 35

Slide 35 text

届いた喜びの声

Slide 36

Slide 36 text

これから ● 情報が正しく見れるという当たり前を整備するところまでやっただけ。 ○ みんなにどんどん使ってもらう ○ 今まで雰囲気でせざるを得なかった業務を根拠を持って意思決定できるように ○ 仮説検証を早く回しデータドリブンな意思決定が出来るように促進する ● ディメンションモデリング思想の伝承・啓蒙 ○ DBの寿命はアプリケーションより長いが、破綻するのも一瞬。 ○ ディメンションモデリング完全理解者を増やしてモデリングをサステナブルにする。

Slide 37

Slide 37 text

まとめ

Slide 38

Slide 38 text

集計層では1つの事実を正しく集める見たい側の都合は別の層で解決する

Slide 39

Slide 39 text

ありがとうございました