こんにちは。CCIのアナリティクス担当の寺田です。
さっそくですが、GA4とBigQueryを連携するとBigQueryに日次で「events_YYYYMMDD」テーブルがexportされ、「日」単位に分割されたテーブルが作成・追加されていきます。
この「events_YYYYMMDD」テーブルをいわゆる「フラット化」し、SQL文で扱いやすく加工するわけですが、こんな悩みを抱えたことはないでしょうか？
新しく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とすると、具体的には下記の図のイメージになります。
想定する条件を以下に示します。
・既存のフラット化テーブルが存在する
・増分データは新たにエクスポートされた「昨日」分のeventsテーブル
下記の記事を参考にDataformを使用可能な状態にしてください！
※(参考)Dataformって何？便利そうだし調べてみた！（前編）
「昨日」分のeventsテーブルをフラット化するようなビューをBigQueryにて定義しておく。今回は以下の条件で定義しました。
--昨日の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
;
definitions配下にフォルダを2つ作成する。
definitions/declare/flat_V.sqlx
config {
type: "declaration",
database: "cci-analytics-sgtm",
schema: "test",
name: "flat_V",
}
definitions/tables/flat_T.sqlx
config {
type: 'incremental',
bigquery: {
partitionBy: 'date'
}
}
SELECT *
FROM ${ref("test", "flat_V")}
${when(incremental(), `WHERE date > (SELECT MAX(date) FROM ${self()})`) }
設定が終わるとCOMPILED GRAPHに以下のような依存関係が表示されます。
あとはDataform側で、「昨日」分のeventsテーブルがexportされる時間以降にこのワークフローを毎日実行するように設定すれば、自動で昨日時点(最新)のeventsテーブルを含んだ増分フラット化テーブルを作成してくれます。
(※ただし最初の1回だけは、手動でDataformを用いて、対象の全期間をフルスキャンをした【type: 'incremental'】のフラット化テーブル「flat_T」を作成しておいてください。)
いかがだったでしょうか。今回は増分データだけを「フラット化」して、既存のフラット化テーブルに追加(INSERT)する方法を解説しました。
また、ほかのGoogle Cloudプロダクト、「Cloud functions」や「Pub/Sub」を用いれば、「昨日」分のeventsテーブルがexportされた時点で、このワークフローを回すことなどもできます。
ダッシュボードに使うデータソースの更新などには非常に便利だと思います。
また、
など、ダッシュボード作成のご相談も承っております。
ここまで読んでいただきありがとうございました！😊
📣CCIではGA4の導入や計測カスタマイズ、運用サポートなどの支援を随時行っております。ご興味のある方はお問い合わせください。
📣なお、私も含め、CCI Analyticsを担当するアナリストは現在は「Data Dig（データディグ）」というCookieに依存しないデジタルマーケティング展開の支援も行っています。そのサービスの詳細も「データマーケティング」という形でご案内しており、Data Dig関連サービスとしてどのような資料が存在するか、またそれらの資料ダウンロードも行えます。
📣CCIが提供する統合デジタルマーケティングサービスの全容、また現状のデジタルマーケティング施策のノウハウを認識されたい場合は、ぜひ近日オープンした「KnowHowNow」をご覧ください。