Go to Qiita Advent Calendar 2024 Top
LoginSignup
4
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

BigQueryAdvent Calendar 2024

Day 15
@ricardinho(メガネ)

GA4テーブルからDataformでデータマートを作成

Last updated at Posted at 2024-12-03

本記事はBigQuery Advent Calendar 2024の15日目の記事になります。

背景と目的

こんにちは。
私は月曜日のトラで、Webやアプリの分析やダッシュボードの作成、GA4やGTM、BigQueryなどの導入＆活用を行っています。
GA4では無料版でもBigQueryへのエクスポートが可能になったので、GA4を単なるWeb分析ツールとして使うだけでなく、勘定系・ユーザーデータなどと連携してより深い分析を行ったり、外部サービスとのデータ連携など、データ活用の機会が増えています。
しかし、分析や他システムと連携するためのテーブルをその都度作成するのが大変で管理も面倒です。また、社員によってスキルレベルも異なるので、テーブル設計やクエリの作成が統一されないことも課題となっていました。これらの課題を解決するためにDataformの活用を考えました。

なぜDataform？

DataformはBigQueryでデータ変換を行う複雑なSQLワークフローを開発、テスト、バージョン管理、スケジュール設定するためのサービスです。類似サービスではdbtが有名ですが、BigQueryではDataformを無料なのと使用するための設定が容易なのでDataformを採用しました。
DataformについてはG-genやBigQuery Advent Calendar 2023などで丁寧に説明されていますので、当ページではDataformの説明や使い方については省略させていただきます。

プログラムの概要＆流れ

当プログラムは以下のような流れでデータマートやテーブルを作成しています。

  1. GA4テーブルからデータを抽出
  2. カラムやデータを加工、削除
  3. テーブル化
  4. 参照元やエンゲージメントなどセッションデータを構築
  5. 構築済みの各データマートにデータを追加
  6. 参照元データを別テーブルに作成
  7. セッションデータからチャネルグループを各セッションごとに作成

処理の流れ

GA4テーブルからデータを取得してデータマートにデータが追加されるまでの流れになります。（文字が小さくて読みづらくてすみません）

Dataform 内のファイル構成

definitions/ga4/

GA4関連のテーブルと処理を定義するフォルダになります。ファイルの記載順の上から順に実際の処理が行われています。
クライアントによって独自のイベントパラメータやユーザープロパティを追加するケースがありますが、それ以外についてはこれらのソースを修正することはほとんどありません。

フォルダ ファイル名 説明
source ga4_events_union.sqlx GA4テーブルからデータを取得。
cleanse c_ga4_events_union.sqlx カラムやデータを削除。
staging s_ga4_events_union.sqlx ソースデータをテーブル化。
s_ga4_events_event_update.sqlx イベントの変更・削除・追加。
s_ga4_events_add_session_item.sqlx セッションデータを作成。
s_ga4_session_traffic_source_last_click.sqlx 参照元データを作成。
s_ga4_event.sqlx イベントデータを生成。
s_ga4_session.sqlx セッションデータを生成。
mart m_ga4_session_channel_group.sqlx セッションチャネルグループデータを作成。
m_ga4_session_traffic_source_last_click_delete_unfixed.sqlx 直近の参照元情報データの削除。
m_ga4_session_traffic_source_last_click.sqlx 各セッションの参照元情報データをデータマートに追加。
m_ga4_event_delete_unfixed.sqlx 直近のイベントデータを削除。
m_ga4_event.sqlx イベントデータをデータマートに追加。
m_ga4_session_delete_unfixed.sqlx 直近のセッションデータを削除。
m_ga4_session.sqlx セッションデータをデータマートに追加。
report r_ga4_analysis_event_purchase.sqlx 購入イベント分析ビュー。
r_ga4_analysis_event.sqlx 全体的なイベント分析ビュー。

includes/

共通で使用される定数や関数を管理するフォルダになります。クライアントのGoogle CloudのプロジェクトIDやGA4プロパティIDなどをすべてconstants.jsにまとめることで導入を容易にしています。

フォルダ ファイル名 説明
includes constants.js GCPプロジェクト名や対象ホスト名などの定数をまとめたファイル。
helpers.js SQLXの記述を簡略化するための関数が定義されたファイル。

ポイント

テーブル化

source、cleanse、stagingデータセット内のSQLXファイルでテーブルではなくビューを生成していますが、GA4のイベント数が多い場合、データマートを作成する前にエラーが発生する可能性があります。これを防ぐためにs_ga4_events_union.sqlxで1度テーブルを生成しています。

参照元定義

GA4のセッションの参照元の抽出方法はこれまでに何度も変わっています。

  1. 各セッションの最初のイベントのevent_paramsカラム内のキーがsourceの値
  2. session_startイベントのevent_paramsカラム内のキーがsourceの値
  3. 各セッションの最初のイベントのcollected_traffic_sourceカラム内のmanual_source
  4. session_startイベントのcollected_traffic_sourceカラム内のmanual_source
  5. session_traffic_source_last_clickカラムのmanual_campaign.source

そのためイベント用データマート（mart.m_ga4_event）とセッション用データマート（mart.m_ga4_session）には下記のように定義しています。

  1. セッション用データマートにセッションがすでに存在していれば、その参照元を採用
  2. session_startイベントのcollected_traffic_sourceカラム内のmanual_source
  3. session_startイベントのevent_paramsカラム内のキーがsourceの値
  4. 各セッションの最初のイベントのcollected_traffic_sourceカラム内のmanual_source
  5. 各セッションの最初のイベントのevent_paramsカラム内のキーがsourceの値

参照元選定部分のコード

-- session_startイベントのcollected_traffic_sourceかevent_paramsカラムから参照元などを取得
session_start AS(
    SELECT *
    FROM(
        SELECT 
            user_pseudo_id,
            ga_session_id,
            ARRAY_AGG(STRUCT(
                COALESCE(g.collected_traffic_source.manual_source, g.event_traffic_source) AS event_traffic_source,
                COALESCE(g.collected_traffic_source.manual_medium, g.event_traffic_medium) AS event_traffic_medium,
                COALESCE(g.collected_traffic_source.manual_campaign_name, g.event_traffic_campaign) AS event_traffic_campaign,
                COALESCE(g.collected_traffic_source.manual_content, g.event_traffic_content) AS event_traffic_content,
                COALESCE(g.collected_traffic_source.manual_term, g.event_traffic_term) AS event_traffic_term,
                COALESCE(g.collected_traffic_source.manual_source_platform, g.event_traffic_source_platform) AS event_traffic_source_platform,
                COALESCE(g.collected_traffic_source.manual_creative_format, g.event_traffic_creative_format) AS event_traffic_creative_format,
                COALESCE(g.collected_traffic_source.manual_marketing_tactic, g.event_traffic_marketing_tactic) AS event_traffic_marketing_tactic,
                COALESCE(g.collected_traffic_source.manual_campaign_id, g.event_traffic_campaign_id) AS event_traffic_campaign_id,
                COALESCE(g.collected_traffic_source.gclid, g.event_traffic_gclid) AS event_traffic_gclid
            ) ORDER BY event_timestamp ASC LIMIT 1)[OFFSET(0)].*
        FROM ga g
        WHERE event_name ="session_start"
        GROUP BY ALL
    ) 
    WHERE event_traffic_source IS NOT NULL AND event_traffic_source NOT IN("(not set)","(direct)")  -- 対象となったsession_startイベントのevent_traffic_sourceがNULLや (not set), (direct)の場合は値を返さない ※(not set)や(direct)はないはずですが念のため
),
-- 参照元などが入っているイベントのうち一番古いものを取得
agg_campaign AS(
    SELECT 
        user_pseudo_id,
        ga_session_id,
        ARRAY_AGG(STRUCT(
            COALESCE(g.collected_traffic_source.manual_source, g.event_traffic_source) AS event_traffic_source,
            COALESCE(g.collected_traffic_source.manual_medium, g.event_traffic_medium) AS event_traffic_medium,
            COALESCE(g.collected_traffic_source.manual_campaign_name, g.event_traffic_campaign) AS event_traffic_campaign,
            COALESCE(g.collected_traffic_source.manual_content, g.event_traffic_content) AS event_traffic_content,
            COALESCE(g.collected_traffic_source.manual_term, g.event_traffic_term) AS event_traffic_term,
            COALESCE(g.collected_traffic_source.manual_source_platform, g.event_traffic_source_platform) AS event_traffic_source_platform,
            COALESCE(g.collected_traffic_source.manual_creative_format, g.event_traffic_creative_format) AS event_traffic_creative_format,
            COALESCE(g.collected_traffic_source.manual_marketing_tactic, g.event_traffic_marketing_tactic) AS event_traffic_marketing_tactic,
            COALESCE(g.collected_traffic_source.manual_campaign_id, g.event_traffic_campaign_id) AS event_traffic_campaign_id,
            COALESCE(g.collected_traffic_source.gclid, g.event_traffic_gclid) AS event_traffic_gclid
        ) ORDER BY event_timestamp ASC LIMIT 1)[OFFSET(0)].*
    FROM ga g
    WHERE (
        g.event_traffic_source IS NOT NULL AND g.event_traffic_source NOT IN("(not set)","(direct)","(none)") 
    )OR (
        g.event_traffic_medium IS NOT NULL AND g.event_traffic_medium NOT IN("(not set)","(direct)","(none)") 
    )OR (
        g.event_traffic_campaign IS NOT NULL AND g.event_traffic_campaign NOT IN("(not set)","(direct)","(none)") 
    )
    GROUP BY ALL
),
-- session_startに参照元(event_traffic_source）が入っていればそれを採用し、ない場合はイベントから取得。session_traffic_mediumなどでもIF(s.event_traffic_source IS NOT NULLとしているのは、event_traffic_mediumとしてしまうと、sourceはsession_startから取得しmediumはイベントから取得というミスを防ぐため
agg_campaign_first_2 AS(
    SELECT 
        user_pseudo_id,
        ga_session_id,
        IF(s.event_traffic_source IS NOT NULL, s.event_traffic_source, a.event_traffic_source) AS session_traffic_source,
        IF(s.event_traffic_source IS NOT NULL, s.event_traffic_medium, a.event_traffic_medium) AS session_traffic_medium,
        IF(s.event_traffic_source IS NOT NULL, s.event_traffic_campaign, a.event_traffic_campaign) AS session_traffic_campaign,
        IF(s.event_traffic_source IS NOT NULL, s.event_traffic_content, a.event_traffic_content) AS session_traffic_content,
        IF(s.event_traffic_source IS NOT NULL, s.event_traffic_term, a.event_traffic_term) AS session_traffic_term,
        IF(s.event_traffic_source IS NOT NULL, s.event_traffic_source_platform, a.event_traffic_source_platform) AS session_traffic_source_platform,
        IF(s.event_traffic_source IS NOT NULL, s.event_traffic_creative_format , a.event_traffic_creative_format ) AS session_traffic_creative_format,
        IF(s.event_traffic_source IS NOT NULL, s.event_traffic_marketing_tactic, a.event_traffic_marketing_tactic) AS session_traffic_marketing_tactic,
        IF(s.event_traffic_source IS NOT NULL, s.event_traffic_campaign_id, a.event_traffic_campaign_id) AS session_traffic_campaign_id,
        IF(s.event_traffic_source IS NOT NULL, s.event_traffic_gclid, a.event_traffic_gclid) AS session_traffic_gclid
    FROM agg_campaign a FULL JOIN session_start s USING(user_pseudo_id, ga_session_id)
),
-- 過去にセッション情報が存在する場合はそれを採用
mart_session AS(
    SELECT 
        user_pseudo_id,
        ga_session_id,
        ARRAY_AGG(STRUCT(
            session_traffic_source,
            session_traffic_medium,
            session_traffic_campaign,
            session_traffic_content,
            session_traffic_term,
            session_traffic_source_platform,
            session_traffic_creative_format,
            session_traffic_marketing_tactic,
            session_traffic_campaign_id,
            session_traffic_gclid
        ) ORDER BY event_date, entrance_timestamp,exit_timestamp ASC LIMIT 1)[OFFSET(0)].*
    FROM `project_id.mart.m_ga4_session` 
    WHERE event_date >= DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 10 DAY)
    GROUP BY ALL
),
agg_campaign_first_3 AS(
    SELECT 
        user_pseudo_id,
        ga_session_id,
        ARRAY_AGG(STRUCT(
            COALESCE(m.session_traffic_source, a.session_traffic_source) AS session_traffic_source,
            COALESCE(m.session_traffic_medium, a.session_traffic_medium) AS session_traffic_medium,
            COALESCE(m.session_traffic_campaign, a.session_traffic_campaign) AS session_traffic_campaign,
            COALESCE(m.session_traffic_content, a.session_traffic_content) AS session_traffic_content,
            COALESCE(m.session_traffic_term, a.session_traffic_term) AS session_traffic_term,
            COALESCE(m.session_traffic_source_platform, a.session_traffic_source_platform) AS session_traffic_source_platform,
            COALESCE(m.session_traffic_creative_format, a.session_traffic_creative_format) AS session_traffic_creative_format,
            COALESCE(m.session_traffic_marketing_tactic, a.session_traffic_marketing_tactic) AS session_traffic_marketing_tactic,
            COALESCE(m.session_traffic_campaign_id, a.session_traffic_campaign_id) AS session_traffic_campaign_id,
            COALESCE(m.session_traffic_gclid, a.session_traffic_gclid) AS session_traffic_gclid
        ) LIMIT 1)[OFFSET(0)].*
    FROM agg_campaign_first_2 a LEFT JOIN mart_session m 
    USING(user_pseudo_id, ga_session_id)
    GROUP BY ALL
)

session_traffic_source_last_clickカラムのmanual_campaign.sourceに関しては、古いGA4テーブルには存在しないため、上記のデータマートとは別にmart.m_ga4_session_traffic_source_last_clickというテーブルを作成しています。user_pseudo_id、ga_session_id、event_dateをキーにしてデータマートとこのテーブルをJOIN可能で、状況に応じて選択できるようにしています。

カスタムチャネルグループ

2024年10月にsession_traffic_source_last_click.cross_channel_campaign.primary_channel_groupカラムが追加されましたが、それ以前はカスタムチャネルグループがなかったので、上記のsession_traffic_source_last_clickと同様にカスタムチャネルグループも別テーブルとして作成しており、こちらもuser_pseudo_id、ga_session_idをキーにしてデータマートとこのテーブルをJOIN可能で、状況に応じて選択できるようにしています。

WITH
  channel_group_table AS (
  SELECT
    user_pseudo_id,
    ga_session_id,
    :
    NET.HOST(g.entrance_page) AS entrance_host,
    REGEXP_EXTRACT(g.entrance_page, r'^https?://[^/]+(/.*)$') AS entrance_path,
    CASE -- 独自チャネル（最優先）を以下に記載 -- Google ヘルプに即した分類条件 https://support.google.com/analytics/answer/9756891?hl=ja
      WHEN g.session_traffic_source = '(direct)' AND g.session_traffic_medium IN ('(none)', '(not set)') THEN 'Direct'
      WHEN g.session_traffic_campaign LIKE '%cross-network%' THEN 'Cross-network'
      WHEN REGEXP_CONTAINS(g.session_traffic_campaign, r'.*(shop|shopping).*$') AND REGEXP_CONTAINS(g.session_traffic_medium, r'^(.*cp.*|ppc|retargeting|paid.*)$') THEN 'Paid Shopping'
      :
      WHEN g.session_traffic_medium = 'sms' THEN 'SMS'
      WHEN g.session_traffic_source = 'firebase' THEN 'Mobile Push Notifications' 
      -- Google ヘルプ内の参照元変換表リストに即した分類条件 https://support.google.com/analytics/answer/9756891?hl=ja > アナリティクスの参照元とカテゴリのリスト
      WHEN c.category = 'SOURCE_CATEGORY_SHOPPING'
        AND REGEXP_CONTAINS(g.session_traffic_medium, r'^(.*cp.*|ppc|retargeting|paid.*)$') THEN 'Paid Shopping'
      :
      WHEN c.category = 'SOURCE_CATEGORY_VIDEO' THEN 'Organic Video'
      WHEN REGEXP_CONTAINS(g.session_traffic_medium, r'^(.*cp.*|ppc|retargeting|paid.*)$') THEN 'Paid Other'
      ELSE 'Unassigned'
  END
    AS channel_group
  FROM
    `project-id.mart.m_ga4_session` AS g -- アナリティクスの参照元とカテゴリのリストをテーブル化(https://storage.googleapis.com/support-kms-prod/qn1xhBu8MVcZPIZ2WZMNdI40FtZXFPGYxj2K)
  LEFT JOIN
    `project-id.master.ga4_channel_grouping_base` AS c
  ON
    g.session_traffic_source = c.source )
SELECT
  * EXCEPT(entrance_timestamp),
  -- カスタムチャネルグループの設定
  CASE
    WHEN channel_group = 'Cross-network' THEN 'Cross-network'
    :
    WHEN session_traffic_source = '(direct)'
  OR REGEXP_CONTAINS(session_traffic_medium, '(not set|none)') THEN 'ノーリファラー'
    ELSE 'その他'
END
  AS custom_channel_group,
  -- 以下は各サイトのLPにあわせて変更
  CASE
    WHEN REGEXP_CONTAINS(entrance_path, r'^/sale.*') THEN 'sale'
    WHEN REGEXP_CONTAINS(entrance_host, r'^www\.abcde\.jp.*') THEN '公式'
    WHEN REGEXP_CONTAINS(entrance_host, r'^www\.abcde\.com.*') THEN 'その他公式'
    ELSE 'その他'
END
  AS lp_group,
  -- 以下は各サイトごとのドメインやサブドメインのグループ分けの定義にあわせて変更
  CASE
    WHEN REGEXP_CONTAINS(entrance_host, r'lp\.abcde\.com') THEN '新規LP'
    WHEN REGEXP_CONTAINS(entrance_host, r'www\.abcde\.com|www\.abcde\.jp|abcde\.jp') THEN '公式'
    WHEN REGEXP_CONTAINS(entrance_host, r'www\.abcde\.co\.jp') THEN 'その他'
    ELSE '定義外'
END
  AS domain
FROM
  channel_group_table
QUALIFY
  ROW_NUMBER()OVER(PARTITION BY user_pseudo_id, ga_session_id ORDER BY entrance_timestamp) =1

注意点

GA4定義テーブルのスキーマが頻繁に更新

当プログラムでは、source/ga4_events_union.sqlxにて2種類のGA4テーブル（events_YYYYMMDD、events_intraday_YYYYMMDD）を取り込んでいますが、この2つのテーブルはスキーマが異なる場合があります。2024年ではsession_traffic_source_last_clickカラムの中身が変わりました。そのため、変更の可能性があるカラムについては過去分と共通するものだけを抽出するようにしています。

-- source/ga4_events_union.sqlx の一部
WITH
  events AS(
  SELECT
    event_date,
    event_timestamp,
    : 
    STRUCT(collected_traffic_source.manual_campaign_id,
      collected_traffic_source.manual_campaign_name,
      collected_traffic_source.manual_source,
      collected_traffic_source.manual_medium,
      collected_traffic_source.manual_term,
      collected_traffic_source.manual_content,
      collected_traffic_source.manual_source_platform,
      collected_traffic_source.manual_creative_format,
      collected_traffic_source.manual_marketing_tactic,
      collected_traffic_source.gclid,
      collected_traffic_source.dclid,
      collected_traffic_source.srsltid) AS collected_traffic_source,
    STRUCT(session_traffic_source_last_click.manual_campaign,
      session_traffic_source_last_click.google_ads_campaign) AS session_traffic_source_last_click,
    :
  FROM
    `project-id.analytics_xxxxxxxx.events_*`
  WHERE
    :
)

データエクスポート遅延（最大７日間）とクエリコストの折り合い

GA4のヘルプによると、データの処理が最長で7日間遅延する場合があるそうです。

** 一部のデータは遅延して到着することがあります（最長 7 日間）

そのため、当プログラムでは9日前までのGA4データを取り込み、データマートを更新しています。ですが、9日前までのデータを毎日更新すると料金増が懸念されます。日次更新の対象期間を何日前までにするかはご検討ください。

さいごに

このような作業でスケジュールされたクエリを使う方も多いと思います。スケジュールされたクエリは設定が簡単ですが、複数のクエリを連続させて実行したり、ソース管理などが悩ましいと思います。
Dataformは参考資料が少ないので始めるハードルが少々高いですが、わからない部分はSQLをそのままSQLXに記述しても動きますので、Dataformをまだ使ったことがなくて敬遠されている方は、まずは${ref(SQLXファイル名)}だけでも理解して、使いながら理解していくことをおすすめします。

おまけ

当プログラムの企画＆第1フェーズの開発は月曜日のトラの西さんが行い、以降の開発は私が引き継いでいます。私はそれまでdbtは使ったことがありましたがDataformはあまり触ったことがなかったので、機能を理解するのが大変でした。今でも当プログラムのバージョンアップを頻繁に行っていますが、Dataformの機能を新たに理解することもあるので日々勉強です。

4
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?