Tweet
Tweet

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

2 自己紹介 ● 本名: 打出 紘基 ● X: @hanon52_ ● 2021年 ピクシブ株式会社に新卒入社 ● アナリティクスエンジニア ● 広告のデータ基盤構築、BIの整備、Salesforce の導 入など、データの入口から出口まで何でもやります ● 美味しいご飯を食べたり作ったりするのが好き ucchi-

Slide 3

Slide 3 text

会社紹介 3 SNS「pixiv」を中心に、 創作を楽しむサービスを複数展開

Slide 4

Slide 4 text

pixiv のユーザー数 4 出典：pixiv ユーザー情報自社調べ（ 2024年 6月） 2007 100,000,000 50,000,000 2024/6

Slide 5

Slide 5 text

5 pixivに広告を掲載できる 運用型広告配信プラットフォーム アニメ・マンガ・ゲームなどのコンテンツや創作活動を愛するpixivユーザーにリーチできる、 エンタメ系商材に適した広告ソリューション です。 入稿から掲載までオンラインで完結でき、 クリック課金&オークション形式でpixivや関連サービスの広告枠に出稿できます。 アカウントを 開設 アカウントを 開設 広告配信の 設定 審査完了後 配信開始 オークション形式で 掲載を最適化 pixiv Ads の紹介 ©pixiv Artwork by 逢編いあむ

Slide 6

Slide 6 text

発表の構成 ● pixiv Adsのデータ基盤 ● ディメンション ● ファクト ● まとめ 6

Slide 7

Slide 7 text

pixiv Ads のデータ基盤 7

Slide 8

Slide 8 text

pixiv Ads のデータ基盤アーキテクチャ 8 BigQuery Cloud SQL GKE Datastream Looker 利用者 Cloud Logging

Slide 9

Slide 9 text

pixiv Ads のデータモデリング 9

Slide 10

Slide 10 text

ディメンショナル モデリング 10 データを 「ディメンション」と 「ファクト」 に分けてモデリングする https://docs.getdbt.com/terms/dimensional-modeling

Slide 11

Slide 11 text

ディメンションとファクト ファクト: ビジネス活動の結果 ディメンション: 分析軸 例) ビジネス活動「製品の販売」 「8/1 に、営業員が、製品を販売した」 ● ファクト: 販売 (売上) ● ディメンション: 8/1 (日付)、営業員、製品 11

Slide 12

Slide 12 text

ディメンション 12

Slide 13

Slide 13 text

ディメンションのモデリング 13 数十個のテーブルを、 3つのディメンションに集約

Slide 14

Slide 14 text

1対多のモデリング 14 ディメンショナルモデリングで、ドメインや概念ごとに作るディメンションは 1つ。 ただし、ディメンションに1対多であるものはテーブルを分ける。

Slide 15

Slide 15 text

1対多の例：カルーセル広告 ユーザーがスワイプすると、複数の広告画像が表示される。 テーブルの粒度は「クリエイティブ ID × 広告画像ID」。 15

Slide 16

Slide 16 text

1対多の難しさ 「カルーセル広告」を「クリエイティブ」に JOINする と、レコード数が膨らむ。 その結果、集計ミスにつながる。 例えば、クリエイティブ数を計算するために COUNT(*)を使うと、過剰集計してしまう。 16

Slide 17

Slide 17 text

dbt では「クリエイティブ」と「カルーセル広告」を分けて持ち、 Lookerで繋ぐ。 「粒度はcreative_id」と指定することで、Lookerは自動で1対多を対処できる。 creative_idに対してユニークになるように指標を集計してくれる。 view: creatives { label: “クリエイティブ” dimension: creative_id { label: “クリエイティブID” primary_key: yes } } 「クリエイティブ」と「カルーセル広告」を分けて持つ 17

Slide 18

Slide 18 text

dbtとLooker の使い分け dbt Looker - ディメンションの作成ロジック - ファクトの作成ロジック - ディメンションやファクト同士の結合ロジッ ク - 指標の計算ロジック 例) クリック率 = クリック数 ÷ 閲覧数 18 ポイントは、Lookerを薄くすること。 ディメンションやファクトの作成ロジックは複雑になりがち。 ロジックをdbtに寄せることで、改修の見通しが良くなる。

Slide 19

Slide 19 text

ディメンションを最強にするために 19

Slide 20

Slide 20 text

最強のディメンション = 豊富な分析軸 ● データ基盤とは、利用者がデータ分析を行うために存在する ● ディメンションとは分析軸 → 最強のディメンションとは、より多くの洞察に繋がる、 豊富な分析軸の集まり 20

Slide 21

Slide 21 text

ファクトからディメンションを作る ● 基本的に、ディメンションはDBのデータを参照して作られる ● しかし、中には過去のユーザー行動履歴を分析軸として使うと便利なことがある ● ファクトから分析軸を作成する定期実行処理を組み、ディメンションに繋げる 21

Slide 22

Slide 22 text

ディメンションを使いやすくする 豊富な分析軸を提供しても、その意味を伝えられないと利用者目線で使いづらい。 分析軸の説明文が必要。 一方で、ディメンションの説明文をきちんと管理するのは大変。 そこで、説明文を楽に管理するための工夫を紹介する。 22

Slide 23

Slide 23 text

説明文の入力を楽にする 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: キャンペーンタイプ。外部/自社

Slide 24

Slide 24 text

説明文が存在することを楽に担保する dbtやLookerには、説明文の存在を担保する linterが充実している。 - dbt: dbt_project_evaluator - Looker: LAMS 説明文が無ければCIを落とす → 説明文が存在する状態を楽に担保できる。 24

Slide 25

Slide 25 text

ファクト 25

Slide 26

Slide 26 text

26 自己紹介 ● 本名: 三好 正剛 ● X: @mytk0u0 ● データエンジニア ● 2018年 ピクシブ株式会社に新卒入社 ● 普段は広告配信関連のシステム・ロジック・データ基 盤・BIを開発していることが多い ● 趣味はゲーム (VALORANT, Splatoon等) mytk まいたけ

Slide 27

Slide 27 text

27 pixiv Ads の fact ≒ 広告のリクエスト情報。 個々のイベントに対応するイベントログと、それらを統合した配信ログからなる。 ファクトテーブルの全体像

Slide 28

Slide 28 text

28 配信ログは個々の広告表示イベントごとに記録されている。 クリックやCVはほとんど NULL になる。 配信ログ

Slide 29

Slide 29 text

29 ① サービス定義上のクリックや CV は Imp と紐づけないと計測できないから。 例えばクリックイベントが有効であるためには「 0〜24時間前に同一イベントIDでのImpが発生し ている」必要がある。 ② 分析のほとんどがファクトを跨ぐから。 例えばクリックイベントの分析の際も「 n日に発生したクリック件数を知りたい」ではなく「 n日の Imp に対して発生したクリック件数を知りたい」ケースのほうが多い。 ③ ファクトが一箇所にまとまっているほうが Lookerで取り回しやすいから。 なぜ配信ログをつくるのか？

Slide 30

Slide 30 text

ファクトを最強にするために 30

Slide 31

Slide 31 text

31 速報値バッチ → 10分ごとに直近2hの配信ログを更新 (更新漏れあり) 確定値バッチ → 1日ごとに直近数日の配信ログを更新 (更新漏れなし) 精度と鮮度が両立されている

Slide 32

Slide 32 text

32 負荷や費用を抑えるために、タイムスタンプによるパーティションだけでなく、 - クリックフラグ - CVフラグ - サンプリング用ハッシュ値 によるクラスタリングを活用する。 BIツール (Looker) で直接利用できる abs(mod(farm_fingerprint(event_id), 100))

Slide 33

Slide 33 text

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) ;; } ... } ※ 実際のサンプリング割合は可変

Slide 34

Slide 34 text

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)

Slide 35

Slide 35 text

まとめ 35

Slide 36

Slide 36 text

36 基本的にはふつうの構成・ふつうのモデリングをていねいに。 - dbtを用いたBigQueryでのデータ管理 - ディメンショナルモデリング - BIツール (Looker) への接続 そこから、実際に分析する際の使いやすさを考えて工夫を加えている。 - 豊富な分析軸やその説明文 - 鮮度と精度の両立 - Lookerからの直接参照 → 最強の利便性を提供する pixiv Ads における”最強”のデータ基盤とは

Slide 37

Slide 37 text

一緒に働く仲間を募集中です！ 37