Zenn
Open11

【dbt】dbt-ga4

GA4
dbt
YuichiYuichi

これで同じデータセットに作成される

models:
  ga4:
    +schema: dwh_ga4
    + tags:
      - "dwh"
      - "ga4"

seeds: # dbt seed
  ga4:
    +schema: dwh_ga4
    +tags:
      - "dwh"
      - "ga4"

実行

dbt build --select package:ga4
YuichiYuichi

モデル

モデル名 説明
stg_ga4__events 便利なイベントやセッションキーで拡張された、クリーンなイベントデータを含む。
stg_ga4__event_* 各イベント（例: page_view、purchase など）ごとのモデルで、そのイベント固有のイベントパラメータをフラット化する。
stg_ga4__event_items 購入やカート追加など、eコマースイベントに関連するアイテムデータを含む。
stg_ga4__event_to_query_string_params 各イベントと、page_locationフィールドに含まれるクエリパラメータおよび値のマッピングを行う。
stg_ga4__user_properties 各ユーザーに対して指定されたuser_propertiesの最も最近の発生を取得する。
stg_ga4__derived_user_properties 特定のevent_params値の最も最近の発生を見つけ、それをclient_keyに割り当てる。導出されたユーザー属性は変数として指定される（詳細は下記ドキュメント参照）。
stg_ga4__derived_session_properties 特定のevent_paramsまたはuser_properties値の最も最近の発生を見つけ、それをセッションのsession_keyに割り当てる。導出されたセッション属性は変数として指定される（詳細は下記ドキュメント参照）。
stg_ga4__session_conversions_daily セッションごとの日別コンバージョン数を生成する。含めるコンバージョンイベントのリストは設定可能（詳細は下記ドキュメント参照）。
stg_ga4__sessions_traffic_sources 各セッションに対して、最初のsource、medium、campaign、content、paid search term（UTMトラッキングから）およびデフォルトチャネルグルーピングを見つける。
stg_ga4__sessions_traffic_sources_daily stg_ga4__sessions_traffic_sourcesと同じデータだが、日ごとにパーティション分割され、データの効率的なロードおよびクエリを可能にする。
stg_ga4__sessions_traffic_sources_last_non_direct_daily 各セッションに対して、30日間のルックバックウィンドウ内の最後の非ダイレクトソースを見つける。各セッションが1日内に含まれていることを前提としている。
dim_ga4__client_keys client_keysによって示されるユーザーデバイスのディメンションテーブル。最初および最後に表示されたページなどの属性を含む。
dim_ga4__sessions 地理情報、デバイス情報、取得データなどの有用な属性を含むセッションのディメンションテーブル。大規模インストールではコストがかかることがある（dim_ga4__sessions_daily参照）。
dim_ga4__sessions_daily 日付でパーティション分割されたインクリメンタルなセッションディメンションテーブル。各パーティションが1日の範囲内にあることを前提としている。
fct_ga4__pages stream_idおよびpage_locationごとに、日別の一般的なページメトリクスを集計したファクトテーブル。
fct_ga4__sessions_daily 日付でパーティション分割されたセッションメトリクスのファクトテーブル。セッションは複数日にわたる可能性があり、1つのセッションが複数行にまたがる場合がある。
fct_ga4__sessions 日をまたいでセッションメトリクスを集計したファクトテーブル。このテーブルはパーティション分割されていないため、クエリ時のパフォーマンスやコストに注意が必要。

シード

シードファイル名 説明
ga4_source_categories.csv Googleのsourceとsource_categoryのマッピング。https://support.google.com/analytics/answer/9756891?hl=en からダウンロードされたもの。
YuichiYuichi
dbt_project.yml
vars:
  ga4:
    source_project: "Project_id" # Project that contains raw GA4 data
    property_ids: [*******] # Array of properties to process
    start_date: "20240401" # Earliest date to load
    static_incremental_days: 5
    query_parameter_extraction: ["gclid","fbclid","keyword"] 
    conversion_events: ['*******']
    user_properties:
      - user_property_name: "*******"
        value_type: "int_value"
YuichiYuichi

https://support.google.com/analytics/answer/9191807
[GA4] アナリティクスのセッションについて

セッションとしてカウントされる操作
アナリティクスでは、ユーザーがアプリをフォアグラウンドで開くか、現在アクティブなセッションがないとき（以前のセッションがタイムアウトした場合など）にページまたはスクリーンを表示すると、セッションが開始されます。
デフォルトでは、30 分間操作がなければセッションは終了（タイムアウト）します。セッションの継続時間に制限はありません。
BigQuery の検討事項

  • BigQuery では十分な時間とリソースを使って正確なセッション数を計算するため、前述の効率的なセッション指標の計算方法（HyperLogLog++ アルゴリズム）は適用されません。

https://developers.google.com/analytics/blog/2022/hll?hl=ja
Google アナリティクスにおけるユニーク カウントの近似値

  • APPROX_COUNT_DISTINCT:HLL++ を使用して近似値を返します
  • HLL_COUNT
    いずれもdbt-ga4では使われてない
YuichiYuichi

このスキーマを使用して GA4 レポートを再作成するにはどうすればよいですか? ＃337
https://github.com/Velir/dbt-ga4/issues/337

いずれにしても、数字が完全に一致することを期待しないでください。
GA4 から意図的に逸脱することを決定した箇所がいくつかあります。
Google は Hyperlog++ を使用して数値を推定します。dbt-GA4 パッケージは数値を数えます。
Google はソース、メディア、キャンペーンを個別に計算するため、ソースとメディアの組み合わせに次の利用可能なキャンペーンが割り当てられるべきではありません。dbt-GA4 パッケージは、有効な最初のイベントにすべてのソースアトリビューションパラメータを割り当てます。

dbt-GA4 パッケージの使用方法に関する無料コースを作成しました。
https://caretjuice.com/courses/dbt-ga4-setup/

最初のレポートでは、fct_ga4__sessions_daily と dim_ga4__sessions_daily テーブルを session_partition_key で結合（使用するツールによって「ブレンド」と呼ばれる場合もあります）する必要があります。

次に、主要なディメンションとして session_default_channel_grouping または last_non_direct_default_channel_grouping を使用し、日付範囲として session_partition_date を指定します。

fct_ga4__sessions_daily から取得する項目は以下の通りです。

  • 新規ユーザー: sumif(session_number = 1)
  • エンゲージされたセッション数: sum(session_partition_max_session_engaged)
  • エンゲージメント率: sum(session_partition_max_session_engaged) / count(*)
  • ユーザーあたりのエンゲージされたセッション数: sum(session_partition_max_session_engaged) / count(distinct client_key)
  • 平均エンゲージメント時間: sum(session_partition_sum_engagement_time_msec) / count(*)
  • イベント数: カスタマイズが必要ですが、session_partition_count_page_views を代替として使用できるか確認します。
  • 主要イベント数: コンバージョンイベントを設定すると、event_name_count というカラム（例えば、purchase_count など）が追加されます。
  • 総収益: データ収集の設定方法によりますが、sum(session_partition_sum_event_value_in_usd) を使用できる可能性があります。それ以外の場合はカスタマイズが必要です。

上記のクエリは擬似コードとみなしてください。実際のクエリは使用しているレポートツールに依存します。

YuichiYuichi

core.yml

  • name: dim_ga4__sessions
    description: セッションのコンテキスト（取得元、媒体、キャンペーンなど）を含むフィルタリングに役立つディメンションテーブル。各行はセッションを表す。session_key でユニーク。
    columns:

    • name: session_key
      tests:
      • unique

  • name: dim_ga4__client_keys
    description: ユーザーのデバイス（client_key）に関するデータを含むディメンションテーブル。最初と最後のイベントデータが含まれる。client_key でユニーク。
    columns:

    • name: client_key
      description: user_pseudo_id と stream_id をハッシュ化した組み合わせ
      tests:
      • unique

  • name: fct_ga4__client_keys
    description: ユーザーのデバイス（client_key）レベルでの集計メトリクスを持つファクトテーブル。メトリクスは fct_ga4__sessions から集計される。
    columns:

    • name: client_key
      description: user_pseudo_id と stream_id をハッシュ化した組み合わせ
      tests:
      • unique

dim_ga4__sessions_daily.yml

  • name: dim_ga4__sessions_daily
    description: >
    セッションパーティション用の増分更新、パーティション化されたディメンションテーブル。session_partition_date でパーティション分けされ、日付でフィルタリングする際にクエリ最適化が向上。
    取得元、媒体、キャンペーンなど、セッションのフィルタリングに役立つコンテキストを含む。
    各行は1日のセッションパーティションを表す（セッションそのものではなく）。session_partition_key でユニーク。
    columns:
    • name: session_partition_key
      description: >
      セッションの1日分のパーティションに割り当てられたユニークキー。GA4では、セッションは複数の日にまたがることがある。
      クエリパフォーマンスを向上させるため、日付でフィルタリング/パーティション分け可能な「セッションパーティション」を使用する方が簡単。
      tests:
      • unique
    • name: session_key
      description: >
      セッションに割り当てられたユニークキー。セッションは複数の日付/パーティションにまたがることがある。
    • name: session_partition_date
      description: >
      session_partition_key に関連する日付。テーブルのパーティションに使用される。この列でフィルタリングすると、クエリコストとパフォーマンスが最適化される。
    • name: session_source
      description: この特定のセッション内のイベントに基づいたセッションの取得元。非ダイレクト取得元を確認するには、last_non_direct_source を参照。
    • name: last_non_direct_source
      description: 30日間の遡及ウィンドウに基づいて、このセッションに帰属する最後の非ダイレクト取得元。

fct_ga4__pages.yml

  • name: fct_ga4__pages
    description:
    stream_id と page_location でグループ化された訪問数、ユーザー数、新規ユーザー数、入り口数、出口数、設定可能なコンバージョン数などのページメトリクスを持つ増分モデル。
    tests:
    • unique:
      column_name: "(event_date_dt || stream_id || page_location)"
      columns:
    • name: total_engagement_time_msec
      description: page_location に対する総エンゲージメント時間。
    • name: avg_engagement_time_denominator
      description:
      avg_engagement_time_denominator を使用して平均エンゲージメント時間を計算する。これは、総エンゲージメント時間の合計を分母の合計と1000の積で割ることで導き出され、秒単位の平均エンゲージメント時間を得る
      （average_engagement_time = sum(total_engagement_time_msec)/(sum(avg_engagement_time_denominator) *1000 )）。
      分母には、セッション内で page_location にエンゲージメント時間が記録されていないページビューイベントを除外するが、同じセッション内で以前にページビューイベントが記録された
      page_location への後続のページビューイベントが含まれる（たとえ後続イベントにエンゲージメント時間が記録されていなくても含まれる）。

fct_ga4__sessions_daily.yml

  • name: fct_ga4__sessions_daily
    description: >
    日次セッションパーティションに関連するメトリクスを持つ増分ファクトテーブル。
    columns:
    • name: session_partition_key
      description: >
      セッションの1日分のパーティションに割り当てられたユニークキー。GA4では、セッションは複数の日にまたがることがある。
      クエリパフォーマンスを向上させるため、日付でフィルタリング/パーティション分け可能な「セッションパーティション」を使用する方が簡単。
      tests:
      • unique
    • name: session_key
      description: >
      セッションに割り当てられたユニークキー。セッションは複数の日付/パーティションにまたがることがある。
    • name: session_partition_date
      description: >
      session_partition_key に関連する日付。テーブルのパーティションに使用される。この列でフィルタリングすると、クエリコストとパフォーマンスが最適化される。

fct_ga4__sessions.yml

  • name: fct_ga4__sessions
    description: >
    セッションに関連するメトリクスを含むファクトテーブル。このモデルは fct_ga4__sessions_daily で生成された日次パーティションメトリクスを使用して、セッション全体のメトリクスを計算する。session_key でユニーク。
    columns:
    • name: session_key
      description: >
      セッションに割り当てられたユニークキー。セッションは複数の日付/パーティションにまたがることがある。
      tests:
      • unique
    • name: user_id
      description: >
      クライアントに関連付けられたユーザーID。GA4の実装で明示的に割り当てられない限り、nullに設定される。この値はセッション中に変わる可能性があるため、max 値を取得する。
    • name: session_start_date
      description: >
      最初のセッションパーティションに関連付けられた日付。

fct_ga4__user_ids.yml

  • name: fct_ga4__user_ids
    description: >
    user_id が存在する場合はユーザー単位で、存在しない場合はデバイス単位（client_key による）で集計されたメトリクスを持つファクトテーブル。メトリクスは fct_ga4__client_keys から集計される。
    columns:
    • name: user_id_or_client_key
      tests:
    • unique:
      column_name: "md5(user_id_or_client_key || stream_id)"
YuichiYuichi

https://caretjuice.com/comparing-ga4-session-attribution-with-dbt-ga4/

  • GA4 は訪問の最初のページのすべてのイベントには、該当する場合はソース、メディア、キャンペーンが含まれ、外部リファラーまたは別の UTM パラメータ セットがない限り、後続のページのイベントにはこれらのパラメータは含まれません。
  • dbt-GA4 パッケージは GA4 と同じ方法でアトリビューションを計算しません。
  • Google ではオーガニック検索結果にパラメータをタグ付けすることを許可していないためutm_campaign