こんにちは。CCIのアナリティクス担当の寺田です。

さっそくですが、GA4とBigQueryを連携するとBigQueryに日次で「events_YYYYMMDD」テーブルがexportされ、「日」単位に分割されたテーブルが作成・追加されていきます。

この「events_YYYYMMDD」テーブルをいわゆる「フラット化」し、SQL文で扱いやすく加工するわけですが、こんな悩みを抱えたことはないでしょうか？

• 新しくexportされてきた「events_YYYYMMDD」テーブル(増分データ)だけを「フラット化」して、既存のフラット化テーブルに追加(INSERT)したい。
  
  

新しくexportされてきた「events_YYYYMMDD」テーブルデータ(増分データ)を既存のフラット化テーブルに追加する際、毎回「events_YYYYMMDD」テーブルを全期間フルスキャン&フラット化していては、なかなかクエリ料金がかさんでしまいますよね😱

例えばこんなクエリで最新のフラット化テーブルを毎回作成していると、

日を追うごとに指定期間が増加→スキャン量が増加するため、クエリ料金は高くなっていきます。

--TABLE_SUFFIX定義 --YYYYMMDDの形式
WITH ts AS (
  --startは2024-01-01～
   SELECT FORMAT_DATE("%Y%m%d",DATE('2024-01-01')) AS start_date 
  --endは昨日まで
   ,FORMAT_DATE("%Y%m%d",DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 1 DAY)) AS end_date 
)

--期間指定
,period AS (
  SELECT *
  FROM `cci-analytics-sgtm.analytics_270527238.events_*`
  WHERE _TABLE_SUFFIX BETWEEN (SELECT start_date FROM ts) AND (SELECT end_date FROM ts)
)

--フラット化
SELECT
DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS date
--ユーザー
,user_pseudo_id
,DATE(timestamp_micros(user_first_touch_timestamp), 'Asia/Tokyo') AS user_first_touch_date
,traffic_source.medium AS first_touch_medium

--イベント
,event_name
--以下よりevent_params.valueを展開
,(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
,(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS ga_session_number

FROM period
;

今回は「Dataform」を用いて、増分データのみを対象にフラット化を行い、既存のフラット化テーブルに追加する(Insertする)方法をご紹介したいと思います。

やりたいことのイメージ↓

※ただし、昨日時点でexportされてきたeventsテーブルを最新とみなします。

start_date～昨日時点(最新)の期間のeventsテーブルをフルスキャンしてフラット化クエリを実行→start_date～昨日時点(最新)のeventsテーブルを含んだフラット化テーブルを新たに作成するのではなく、

「昨日時点(最新)のeventsテーブルだけをスキャンしてフラット化クエリを実行→既存のフラット化テーブルに追加(Insert)し、start_date～昨日時点(最新)のeventsテーブルを含んだ増分フラット化テーブルを作成しよう！ということです。

例えば

start_date:2024/01/01

昨日(eventsテーブルの最新):2024/01/18とすると、具体的には下記の図のイメージになります。

Dataformで増分テーブルを作成する

想定する条件を以下に示します。

・既存のフラット化テーブルが存在する

・増分データは新たにエクスポートされた「昨日」分のeventsテーブル

手順1.Dataformの設定を終わらせる

下記の記事を参考にDataformを使用可能な状態にしてください！

※(参考)Dataformって何？便利そうだし調べてみた！（前編）

手順2.昨日のeventsテーブルをフラット化するビューをBigQueryで定義

「昨日」分のeventsテーブルをフラット化するようなビューをBigQueryにて定義しておく。今回は以下の条件で定義しました。

• プロジェクト名：cci-analytics-sgtm
• データセット名：test
• ビュー名：to_flat_V

--昨日のeventsテーブルをフラット化するビュー

--TABLE_SUFFIX定義 --YYYYMMDDの形式
WITH ts AS (
  --startは昨日～
  SELECT FORMAT_DATE("%Y%m%d",DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 1 DAY)) AS start_date
  --endは昨日まで
  ,FORMAT_DATE("%Y%m%d",DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 1 DAY)) AS end_date
)

--期間指定
--昨日分のeventsテーブルだけ指定する
,period AS (
  SELECT *
  FROM `cci-analytics-sgtm.analytics_270527238.events_*`
  WHERE _TABLE_SUFFIX BETWEEN (SELECT start_date FROM ts) AND (SELECT end_date FROM ts)
)

--フラット化
SELECT
DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS date
--ユーザー
,user_pseudo_id
,DATE(timestamp_micros(user_first_touch_timestamp), 'Asia/Tokyo') AS user_first_touch_date
,traffic_source.medium AS first_touch_medium

--イベント
,event_name
--以下よりevent_params.valueを展開
,(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
,(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS ga_session_number

FROM period
;

手順3.Dataformでdefinitions配下にフォルダを作成

definitions配下にフォルダを2つ作成する。

1. データソース定義用(declareフォルダ)
2. 増分テーブル定義用(tablesフォルダ)

手順4.それぞれのフォルダにSQLXで作成したコードをファイルとして格納

definitions/declare/flat_V.sqlx

config {
  type: "declaration",
  database: "cci-analytics-sgtm",
  schema: "test",
  name: "flat_V",
}

config {
    type: 'incremental',
    bigquery: {
        partitionBy: 'date'
    }
}
SELECT *
FROM ${ref("test", "flat_V")}
${when(incremental(), `WHERE date > (SELECT MAX(date) FROM ${self()})`) }

あとはDataform側で、「昨日」分のeventsテーブルがexportされる時間以降にこのワークフローを毎日実行するように設定すれば、自動で昨日時点(最新)のeventsテーブルを含んだ増分フラット化テーブルを作成してくれます。

(※ただし最初の1回だけは、手動でDataformを用いて、対象の全期間をフルスキャンをした【type: 'incremental'】のフラット化テーブル「flat_T」を作成しておいてください。)

いかがだったでしょうか。今回は増分データだけを「フラット化」して、既存のフラット化テーブルに追加(INSERT)する方法を解説しました。

また、ほかのGoogle Cloudプロダクト、「Cloud functions」や「Pub/Sub」を用いれば、「昨日」分のeventsテーブルがexportされた時点で、このワークフローを回すことなどもできます。

ダッシュボードに使うデータソースの更新などには非常に便利だと思います。

また、

• 社内の1st Partyデータを利用したダッシュボード
• 広告効果検証用ダッシュボード

など、ダッシュボード作成のご相談も承っております。

ここまで読んでいただきありがとうございました！😊

📣CCIではGA4の導入や計測カスタマイズ、運用サポートなどの支援を随時行っております。ご興味のある方はお問い合わせください。

• CCIによるGA4導入/運用支援サービスの詳細はこちらから

• GA4活用支援資料のダウンロードはこちらから

• 問い合わせはこちらから

📣なお、私も含め、CCI Analyticsを担当するアナリストは現在は「Data Dig（データディグ）」というCookieに依存しないデジタルマーケティング展開の支援も行っています。そのサービスの詳細も「データマーケティング」という形でご案内しており、Data Dig関連サービスとしてどのような資料が存在するか、またそれらの資料ダウンロードも行えます。

Data Dig（データディグ）

📣CCIが提供する統合デジタルマーケティングサービスの全容、また現状のデジタルマーケティング施策のノウハウを認識されたい場合は、ぜひ近日オープンした「KnowHowNow」をご覧ください。

KnowHowNow（ノウハウナウ）はこちらから