GA4 のデータを BigQuery に パーティション分割テーブル としてエクスポートして、 dbt で利用
"GA4 のデータを BigQuery に パーティション分割テーブル としてエクスポートして、 dbt で利用
GA4 のデータを BigQuery に パーティション分割テーブル としてエクスポートして、 dbt で利用できるようにしました。
dbt 設定
dbt_project.yml
name: my_project_name
config-version: 2
version: 1.1.0
profile: bigquery
model-paths: ["models"]
seed-paths: ["seeds"]
test-paths: ["tests"]
analysis-paths: ["analyses"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]
docs-paths: ["analyses", "macros", "models", "seeds"]
asset-paths: ["assets"]
target-path: target
log-path: logs
packages-install-path: dbt_packages
clean-targets:
- target
- dbt_packages
require-dbt-version: ">=1.1.0"
models:
my_project_name:
+hours_to_expiration: 168
+persist_docs:
relation: true
columns: true
marts:
dataset: marts
+materialized: table
staging:
dataset: staging
+materialized: view
analytics_site_name:
+tags: big_model
ドキュメント(dbt docs)
models/staging/analytics_site_name/analytics_site_name.md
GA4 からエクスポート
{% docs ga4_events_description %}
# GA4 から 1 日に 1 回、エクスポートされるすべてのイベント
> 1 日に 1 回、すべてのイベントがエクスポートされます。
>
> [[GA4] BigQuery Export - アナリティクス ヘルプ](https://support.google.com/analytics/answer/9358801?hl=ja&ref_topic=9359001)
> 毎日のエクスポート オプションが有効になっている場合、各データセット内に events_YYYYMMDD という名前のテーブルが毎日作成されます。
>
> [[GA4] BigQuery Export スキーマ - Firebase ヘルプ](https://support.google.com/firebase/answer/7029846?hl=ja)
{% enddocs %}
{% docs ga4_events_intraday_description %}
# GA4 から 1 日を通して継続的にエクスポートされるイベント
> events_intraday_YYYYMMDD: 内部ステージング テーブルです。このテーブルには、その日に発生したセッション アクティビティのレコードが保持されます。ストリーミング エクスポートはベスト エフォート型の処理であり、イベントの遅れやアップロードの失敗などにより、データに漏れが生じる場合もあります。データは 1 日を通して継続的にエクスポートされます。セッションが複数のエクスポート周期にまたがっていると、テーブルにはそのセッションのレコードが複数保存されることがあります。このテーブルは、events_YYYYMMDD の作成が完了すると削除されます。
>
> [[GA4] BigQuery Export - アナリティクス ヘルプ](https://support.google.com/analytics/answer/9358801?hl=ja&ref_topic=9359001)
> ストリーミング エクスポート オプションが有効になっている場合、events_intraday_YYYYMMDD という名前のテーブルが作成されます。イベントは 1 日中記録されるため、このテーブルには継続的にデータが入力されます。このテーブルは、events_YYYYMMDD の作成が完了すると 1 日の終わりに削除されます。
>
> [[GA4] BigQuery Export スキーマ - Firebase ヘルプ](https://support.google.com/firebase/answer/7029846?hl=ja)
{% enddocs %}
models/staging/analytics_site_name/analytics_site_name.md
{% docs analytics_site_name_description %}
# <site_name> の GA4 データ
<site_name> についての説明を記載
{% enddocs %}
モデル
models/staging/analytics_site_name/src_analytics_site_name.yml
<property_id> には、アナリティクスのプロパティ ID を設定します。
Google アナリティクス 4 プロパティおよび Firebase プロジェクトごとに、「analytics_<property_id>」という名前の 1 つのデータセットが BigQuery プロジェクトに追加されます。プロパティ ID は、アナリティクスのプロパティ ID を指します。これは、Google アナリティクス 4 プロパティのプロパティ設定、および Firebase のアプリ分析の設定で確認できます。
version: 2
sources:
- name: analytics_site_name
dataset: analytics_<property_id>
description: >
{{ doc("analytics_site_name_description") }}
tables:
- name: events
identifier: events_*
description: >
{{ doc("ga4_events_description") }}
- name: events_intraday
identifier: events_intraday_*
description: >
{{ doc("ga4_events_intraday_description") }}
models/staging/analytics_site_name/stg_analytics_site_name__hogehoge.sql
_table_suffix を使用して、1 週間と 1 日前(8 日前) ~ 昨日(1 日前) といった期間に絞り込んで クエリの実行料金 を削減することが多いです。
昨日(1 日前) までに絞り込んでいるのは、events_<今日> のテーブルがまだ作成されていないためです。
_TABLE_SUFFIX を使用するとスキャンされるバイト数が大幅に少なくなり、
クエリの実行料金を削減できる可能性があります。
#standardSQL
with events as (
select
* -- 必要なカラムに絞り込んで利用しています
from {{ source('analytics_site_name', 'events') }}
where
-- 1週間と1日前 <= _table_suffix <= 昨日
_table_suffix between
format_date(
'%Y%m%d'
, date_sub(
date_sub(current_date('Asia/Tokyo'), interval 1 week)
, interval 1 day
)
)
and format_date(
'%Y%m%d'
, date_sub(current_date('Asia/Tokyo'), interval 1 day)
)
)
-- 略
注意点
テスト
GitHub Actions で、テスト(dbt test)を定期的に実行することがあります。
GA4 関連のデータに対して、テストを実行するとデータ量が多いため クエリの実行料金 が高くなります。
そのため、モニタリング対象ではないモデルに対しては、big_model という tagをつけて、テストを実行しないようにしています。
poetry run dbt test --exclude +tag:big_model
全モデルに対して、テストを実行している期間があったのですが、モニタリング対象のモデルのテストのみに絞り込むことで、1 日の クエリの実行料金 を 1/30 程度まで抑えることができました。
GCP 請求書の確認とプロジェクト毎の明細確認 | DevelopersIO などを参考に、定期的に クエリの実行料金 を確認するようにしましょう。
