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

本記事は[BigQuery Advent Calendar 2024](https://qiita.com/advent-calendar/2024/bigquery )の15日目の記事になります。

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

# なぜDataform？
DataformはBigQueryでデータ変換を行う複雑なSQLワークフローを開発、テスト、バージョン管理、スケジュール設定するためのサービスです。類似サービスではdbtが有名ですが、BigQueryではDataformを無料なのと使用するための設定が容易なのでDataformを採用しました。
Dataformについては[G-gen](https://blog.g-gen.co.jp/entry/dataform-explained)や[BigQuery Advent Calendar 2023](https://qiita.com/corechika/items/1a889c4674b02f0d75da)などで丁寧に説明されていますので、当ページではDataformの説明や使い方については省略させていただきます。 

# プログラムの概要＆流れ
当プログラムは以下のような流れでデータマートやテーブルを作成しています。
1. GA4テーブルからデータを抽出
1. カラムやデータを加工、削除
1. テーブル化
1. 参照元やエンゲージメントなどセッションデータを構築
1. 構築済みの各データマートにデータを追加
1. 参照元データを別テーブルに作成
1. セッションデータからチャネルグループを各セッションごとに作成

## 処理の流れ
GA4テーブルからデータを取得してデータマートにデータが追加されるまでの流れになります。（文字が小さくて読みづらくてすみません）
```mermaid
erDiagram
		%% GA4のイベントデータを取得
    t-analytics_PROPERTY_ID-events_yyyymmdd ||--|| source-ga4_events_union : "GA4データを取得"
    t-analytics_PROPERTY_ID-events_intraday_yyyymmdd ||--|| source-ga4_events_union : "GA4データを取得"

	%% ソースデータをクレンジング
    source-ga4_events_union ||--|| cleanse-c_ga4_events_union : "不要なカラム等を削除"

	%% テーブル化
    cleanse-c_ga4_events_union ||--|| staging-s_ga4_events_union : "テーブル化"

		%% 内部アクセスを除外、イベント名を変更・削除・追加する場合の処理
    staging-s_ga4_events_union ||--|| staging-s_ga4_events_event_update : "debug_mode, traffic_type パラメータ, 不要ドメインを除外。イベント名を変更・削除・追加する場合の処理"

		%% 参照元やエンゲージメントなどセッションデータを構築
    staging-s_ga4_events_event_update ||--|| staging-s_ga4_events_add_session_item : "セッションデータを構築"

		%% 各データマートに追加する差分データを構築
    staging-s_ga4_events_add_session_item ||--|| staging-s_ga4_session : "セッションデータの差分データを作成"
    staging-s_ga4_events_add_session_item ||--|| staging-s_ga4_event : "イベントデータの差分データを作成"

    %% session_traffic_source_last_clickカラムから参照元データを別途作成
    staging-s_ga4_events_add_session_item ||--|| staging-s_ga4_session_traffic_source_last_click : "参照元データを構築。既存の参照元データと選択可能"

	%% 蓄積済みの各データマートにデータを投入
    staging-s_ga4_event ||..|| mart-m_ga4_event : "データマートに追加"
    staging-s_ga4_session ||..|| mart-m_ga4_session : "データマートに追加"
    mart-m_ga4_session_traffic_source_last_click ||--|| mart-m_ga4_session : "参照元データを追加" 
    staging-s_ga4_session_traffic_source_last_click ||--|| mart-m_ga4_session_traffic_source_last_click : "参照元データを追加"

    %% セッションデータからチャネルグループを各セッションごとに作成
    mart-m_ga4_session ||..|| mart-m_ga4_session_channel_group : "セッション情報を追加"
    GoogleSheet ||..|| ga4_channel_grouping_base : "チャネルグループをテーブル化"
    ga4_channel_grouping_base ||..|| mart-m_ga4_session_channel_group : "各セッションのチャネルグループを作成"
    mart-m_ga4_event||..|| report-r_ga4_analysis_event : "イベント分析ビューを作成"
     mart-m_ga4_session_channel_group||..|| report-r_ga4_analysis_event : ""
```
## 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. セッション用データマートにセッションがすでに存在していれば、その参照元を採用
1. session_startイベントのcollected_traffic_sourceカラム内のmanual_source
1. session_startイベントのevent_paramsカラム内のキーがsourceの値
1. 各セッションの最初のイベントのcollected_traffic_sourceカラム内のmanual_source
1. 各セッションの最初のイベントのevent_paramsカラム内のキーがsourceの値

### 参照元選定部分のコード
``` SQL
-- 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可能で、状況に応じて選択できるようにしています。

``` SQL
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カラムの中身が変わりました。そのため、変更の可能性があるカラムについては過去分と共通するものだけを抽出するようにしています。

``` SQL
-- 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のヘルプ](https://support.google.com/analytics/answer/11198161?hl=ja)によると、データの処理が最長で7日間遅延する場合があるそうです。
>** 一部のデータは遅延して到着することがあります（最長 7 日間）

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

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

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