BQ+dbt+Lookerを用いた、月間800億件以上の広告リクエストに対するデータモデリング

「みんなの考えた最強のデータ基盤アーキテクチャ2024前半おまとめ拡大版SP！」の発表資料
https://datatech-jp.connpass.com/event/319827/

Transcript

1. BQ+dbt+Lookerを用いた、 月間800億件以上の広告リクエストに 対するデータモデリング pixiv Inc. ucchi- 2024.8.21 pixiv Inc. mytk
2. 2 自己紹介 • 本名: 打出 紘基 • X: @hanon52_ • 2021年 ピクシブ株式会社に新卒入社 • アナリティクスエンジニア • 広告のデータ基盤構築、BIの整備、Salesforce の導 入など、データの入口から出口まで何でもやります • 美味しいご飯を食べたり作ったりするのが好き ucchi-
3. 会社紹介 3 SNS「pixiv」を中心に、 創作を楽しむサービスを複数展開
4. pixiv のユーザー数 4 出典：pixiv ユーザー情報自社調べ（ 2024年 6月） 2007 100,000,000 50,000,000 2024/6
5. 5 pixivに広告を掲載できる 運用型広告配信プラットフォーム アニメ・マンガ・ゲームなどのコンテンツや創作活動を愛するpixivユーザーにリーチできる、 エンタメ系商材に適した広告ソリューション です。 入稿から掲載までオンラインで完結でき、 クリック課金&オークション形式でpixivや関連サービスの広告枠に出稿できます。 アカウントを 開設 アカウントを 開設 広告配信の 設定 審査完了後 配信開始 オークション形式で 掲載を最適化 pixiv Ads の紹介 ©pixiv Artwork by 逢編いあむ
6. 発表の構成 • pixiv Adsのデータ基盤 • ディメンション • ファクト • まとめ 6
7. pixiv Ads のデータ基盤 7
8. pixiv Ads のデータ基盤アーキテクチャ 8 BigQuery Cloud SQL GKE Datastream Looker 利用者 Cloud Logging
9. pixiv Ads のデータモデリング 9
10. ディメンショナル モデリング 10 データを 「ディメンション」と 「ファクト」 に分けてモデリングする https://docs.getdbt.com/terms/dimensional-modeling
11. ディメンションとファクト ファクト: ビジネス活動の結果 ディメンション: 分析軸 例) ビジネス活動「製品の販売」 「8/1 に、営業員が、製品を販売した」 • ファクト: 販売 (売上) • ディメンション: 8/1 (日付)、営業員、製品 11
12. ディメンション 12
13. ディメンションのモデリング 13 数十個のテーブルを、 3つのディメンションに集約
14. 1対多のモデリング 14 ディメンショナルモデリングで、ドメインや概念ごとに作るディメンションは 1つ。 ただし、ディメンションに1対多であるものはテーブルを分ける。
15. 1対多の例：カルーセル広告 ユーザーがスワイプすると、複数の広告画像が表示される。 テーブルの粒度は「クリエイティブ ID × 広告画像ID」。 15
16. 1対多の難しさ 「カルーセル広告」を「クリエイティブ」に JOINする と、レコード数が膨らむ。 その結果、集計ミスにつながる。 例えば、クリエイティブ数を計算するために COUNT(*)を使うと、過剰集計してしまう。 16
17. dbt では「クリエイティブ」と「カルーセル広告」を分けて持ち、 Lookerで繋ぐ。 「粒度はcreative_id」と指定することで、Lookerは自動で1対多を対処できる。 creative_idに対してユニークになるように指標を集計してくれる。 view: creatives { label: “クリエイティブ” dimension: creative_id { label: “クリエイティブID” primary_key: yes } } 「クリエイティブ」と「カルーセル広告」を分けて持つ 17
18. dbtとLooker の使い分け dbt Looker - ディメンションの作成ロジック - ファクトの作成ロジック - ディメンションやファクト同士の結合ロジッ ク - 指標の計算ロジック 例) クリック率 = クリック数 ÷ 閲覧数 18 ポイントは、Lookerを薄くすること。 ディメンションやファクトの作成ロジックは複雑になりがち。 ロジックをdbtに寄せることで、改修の見通しが良くなる。
19. ディメンションを最強にするために 19
20. 最強のディメンション = 豊富な分析軸 • データ基盤とは、利用者がデータ分析を行うために存在する • ディメンションとは分析軸 → 最強のディメンションとは、より多くの洞察に繋がる、 豊富な分析軸の集まり 20
21. ファクトからディメンションを作る • 基本的に、ディメンションはDBのデータを参照して作られる • しかし、中には過去のユーザー行動履歴を分析軸として使うと便利なことがある • ファクトから分析軸を作成する定期実行処理を組み、ディメンションに繋げる 21
22. ディメンションを使いやすくする 豊富な分析軸を提供しても、その意味を伝えられないと利用者目線で使いづらい。 分析軸の説明文が必要。 一方で、ディメンションの説明文をきちんと管理するのは大変。 そこで、説明文を楽に管理するための工夫を紹介する。 22
23. 説明文の入力を楽にする dbt→dbt：dbt-osmosis を使うことで伝搬を自動化 dbt→Looker：句点（。）を用いた簡単な規約を設ける 23 # Looker view: dim_campaigns { dimension: campaign_type { label: “キャンペーンタイプ ” description: “外部/自社” } } # dbt models: - name: dim_campaigns columns: - name: campaign_type - description: キャンペーンタイプ。外部/自社
24. 説明文が存在することを楽に担保する dbtやLookerには、説明文の存在を担保する linterが充実している。 - dbt: dbt_project_evaluator - Looker: LAMS 説明文が無ければCIを落とす → 説明文が存在する状態を楽に担保できる。 24
25. ファクト 25
26. 26 自己紹介 • 本名: 三好 正剛 • X: @mytk0u0 • データエンジニア • 2018年 ピクシブ株式会社に新卒入社 • 普段は広告配信関連のシステム・ロジック・データ基 盤・BIを開発していることが多い • 趣味はゲーム (VALORANT, Splatoon等) mytk まいたけ
27. 27 pixiv Ads の fact ≒ 広告のリクエスト情報。 個々のイベントに対応するイベントログと、それらを統合した配信ログからなる。 ファクトテーブルの全体像
28. 28 配信ログは個々の広告表示イベントごとに記録されている。 クリックやCVはほとんど NULL になる。 配信ログ
29. 29 ① サービス定義上のクリックや CV は Imp と紐づけないと計測できないから。 例えばクリックイベントが有効であるためには「 0〜24時間前に同一イベントIDでのImpが発生し ている」必要がある。 ② 分析のほとんどがファクトを跨ぐから。 例えばクリックイベントの分析の際も「 n日に発生したクリック件数を知りたい」ではなく「 n日の Imp に対して発生したクリック件数を知りたい」ケースのほうが多い。 ③ ファクトが一箇所にまとまっているほうが Lookerで取り回しやすいから。 なぜ配信ログをつくるのか？
30. ファクトを最強にするために 30
31. 31 速報値バッチ → 10分ごとに直近2hの配信ログを更新 (更新漏れあり) 確定値バッチ → 1日ごとに直近数日の配信ログを更新 (更新漏れなし) 精度と鮮度が両立されている
32. 32 負荷や費用を抑えるために、タイムスタンプによるパーティションだけでなく、 - クリックフラグ - CVフラグ - サンプリング用ハッシュ値 によるクラスタリングを活用する。 BIツール (Looker) で直接利用できる abs(mod(farm_fingerprint(event_id), 100))
33. 33 Looker上では「WHERE clicked OR converted OR ハッシュ値 = 0」する (料金1/10程度)。 BIツールで直接利用できる # LookML view: fact_log_delivery { derived_table: { sql: SELECT * FROM fact_log_delivery WHERE (clicked OR converted OR event_id_hash = 0) ;; } ... } ※ 実際のサンプリング割合は可変
34. 34 Looker上では「WHERE clicked OR converted OR ハッシュ値 = 0」する (料金1/10程度)。 Impのようなほとんどの指標は、1/100サンプリングで集計した値を100倍して計算。 Click, CVは件数が少ないため、全件取得して誤差をなくす。 BIツールで直接利用できる select 100 * countif(suid_cluster_key = 0) as count_imp, countif(clicked) as count_click, countif(converted) as count_cv from `配信ログ` where (clicked or converted or suid_cluster_key = 0)
35. まとめ 35
36. 36 基本的にはふつうの構成・ふつうのモデリングをていねいに。 - dbtを用いたBigQueryでのデータ管理 - ディメンショナルモデリング - BIツール (Looker) への接続 そこから、実際に分析する際の使いやすさを考えて工夫を加えている。 - 豊富な分析軸やその説明文 - 鮮度と精度の両立 - Lookerからの直接参照 → 最強の利便性を提供する pixiv Ads における”最強”のデータ基盤とは
37. 一緒に働く仲間を募集中です！ 37