【BigQuery】増分フラット化テーブルを作成する

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

 

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

【DA】dataform_1

 

この「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とすると、具体的には下記の図のイメージになります。

 

【DA】240119_colum_1

 

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フォルダ)
【DA】240131_colum_1

 

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

 

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に以下のような依存関係が表示されます。

【DA】240131_colum_2

 

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

 

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

 

 

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

 

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

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

また、

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

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

 

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

 


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

 


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

 

Data Dig(データディグ)


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

 

KnowHowNow(ノウハウナウ)はこちらから

BigQuery

  • Dataform
  • GA4連携