徳丸 貴嗣 フィード株式会社 マーケティング戦略本部 データマーケティング部 dbt-ga4パッケージを実業務に導入してみた話

2 自己紹介 徳丸 貴嗣 フィード株式会社 マーケティング戦略本部 データマーケティング部 ＜経歴＞ • 前職マーケティング会社で 小売業やメーカーなどのデータ分析やデータ基盤の構築運用などを手掛ける • 2022年9月から現職 • データ基盤の構築・運用 • データ収集・整備 • データ分析 • 「データ」にまつわる色々なことを手広くやらせてもらってます

3 会社紹介 • BtoB（主に歯科医院）向けのECサ イトを運営しています • 医院・病院で使用する消耗品や器具 機械などを販売 • データマーケティング部 • マーケティング領域に軸足を置 いてデータ活用の支援

4 概要 dbt-ga4パッケージを実業務に導入してみた • dbt-ga4とはなにものか • 導入方法 • 処理の中身がどうなっているか • 検証してみたいけどどこから手を付けたらいいかな？というのが最低限わかる程度に • 実際使ってみてどうだったか

5 動機 • 自社ECを複数運営→EC行動履歴の分析からの改善活動 • GA4のWEB UIとLooker Studioでレポート • レポートの自動化や他のデータと紐づけて施策を実施したいといった需要 →BigQuery連携

6 動機 ・STRUCT, ARRAYで畳まれたデータ ・日付データ ・よくわからんカラムだらけ →お決まりの処理は済ませてあるいい感じのデータマートが欲しい！ しかしリソースが足りない GA4の生データは扱いづらい

7 動機 dbt-ga4パッケージ • dbt package hub https://hub.getdbt.com/Velir/ga4/latest/ • GitHub https://github.com/Velir/dbt-ga4 • 各種イベントパラメータのフラット化 • 日付時刻関連の変換（文字列→DATE型） • 日付ごとのシャーディング→日付でパーティション分割した1テーブル • 複数サイトのイベントデータを1テーブルに集約 • ディメンショナルモデリング • など →前処理のつらいところをいい感じに解決してくれる ただ日本語の文献が非常に少ない https://speakerdeck.com/hiro_koba_jp/dbtdega4woxi-iyasukusuruhua

8 導入手順 1．packages.ymlに以下を追加 2．dbt_project.ymlに以下を追加（必須項目のみ） GA4データが連携されているプロジェクトID GA4データが連携されているデータセット名 GA4データが連携されているプロパティIDのリスト GA4データの連携開始日 直近何日分のデータを再集計するか 加工後のモデルを格納するデータセット名

9 導入手順 TROCCOのdbt連携機能を使って日次処理

10 中身はどうなっているか？ 実行するとこんなモデルが生えてきます よく使っているモデル ・stg_ga4__events: 加工済みのイベントテーブル ・stg_ga4__event_*: イベントテーブルをイベントごとに分割 ・stg_ga4__event_items: eコマースイベントのアイテム ・dim_ga4__sessions: セッションの流入元・デバイスなど

11 中身はどうなっているか？ データリネージを見るとこうなっています GA4 生データ イベントごとに 分割したビュー base_ga4__events ディメンション・ ファクトテーブル を作るための 前処理 stg_ga4__events ディメンション・ ファクトテーブル

12 どういう処理をやっているかざっくり説明  base_ga4__events • 複数のWEBサイトのデータを統合する（dbt_project.ymlで設定可） • 主要なイベントパラメータのフラット化 • ga_session_id • page_location • session_engaged • engagement_time_msec • page_title • page_referrer • 流入元（source,medium,campaign） • event_date -> 日付型（event_date_dt）への変換 • 直近○日のデータを持ってきて追記＋更新(dbt_project.ymlで設定可) • パーティション分割：event_date_dt • クラスタ化：event_name

13 中身はどうなっているか？ データリネージを見るとこうなっています GA4 生データ イベントごとに 分割したビュー base_ga4__events ディメンション・ ファクトテーブル を作るための 前処理 stg_ga4__events ディメンション・ ファクトテーブル

14 どういう処理をやっているかざっくり説明  stg_ga4__events • 各種ユニークキーを定義 • イベントパラメータの調整 • gclid（GoogleクリックID）を検出、イベントパラメータを書き換えてGoogle広告経由とする • URLの解析・調整 • クエリパラメータの抽出 • page_locationをクエリパラメータを除去したURLに差し替え • page_hostname, page_query_stringを追加

15 どういう処理をやっているかざっくり説明 • stg_ga4__eventsで以下のユニークキーを生成 • これらキーでディメンション・ファクトテーブルを結合して集計・分析を進める 名前 定義 概要 client_key user_pseudo_id + stream_id GA4上のユーザーの単位 session_key client_key + session_id セッションの単位。 session_idは重複することがある session_partition_key session_key + event_date_dt 日跨ぎで別セッションとカウント したいときに使う event_key session_key + event_name + event_timestamp + event_params GA4のイベントのユニークなキー event_params=event_params構 造体をJSON文字列化したもの page_key event_date_dt + page_location 日別・ページ別のキー page_engagement_key イベント名がpage_view なら session_key + page_referrerer, それ以外の場合はsession_key + page_location エンゲージメント時間を算出する ときのキー

16 分析クエリの例 • こうやって生成されたモデルを使って一通りの分析ができるようになります • dbt-ga4を利用する前後でクエリがどのように変わるか見てみます 例）あるサイトのページ別のユニークユーザー数、ページビュー数 before after ・SQLがシンプルに！ （クエリが複雑になればなるほど効果が高い） ・データのスキャン量も減る→料金節約

17 実務で困った事例 サイト内検索キーワードの集計 • 集計結果がおかしい… • 商品コードでの検索が拾えていない • 商品コードでの検索がそれなりのボリュームあることは確認済み

18 実務で困った事例 サイト内検索キーワードの集計 生データを探してみると… 検索キーワードが数字のみの場合、int_valueやdouble_valueに格納されていたが dbt-ga4ではstring_valueしか拾わない仕様 ココ！？

19 実務で困った事例 サイト内検索キーワードの集計 どうやって解決したか？ →自社製マクロを特定条件下で検索順に割り込ませてオーバーライド 本家のunnest_key()マクロ 自社リポジトリにあるunnest_key()マクロ string_valueだけ抽出 string→int→doubleの検索順で抽出 search_termを抽出するときだけ置き換える

20 まとめ • dbt-ga4パッケージを実業務に導入してみた • いいところ • dbtにある程度習熟していれば導入は容易 • GA4生データの使いづらいところが改善されて便利 • パーティション分割、クラスタ化によりクエリコスト削減 • 注意すべき点 • GA4-BigQueryの連携済み、データ基盤をBQで構築している前提 • よくわからんテーブルがいっぱい生えてくる • 違和感があったら生データまで戻る、ソースコードまで戻る • GA4標準レポートと値が違うことがある • 現場側とのすり合わせが必要