📝

dbt単体でBigQuery上のGoogle Analyticsのデータを成型して永続化する

2024/05/01に公開

Google Analytics

BigQuery

dbt

tech

はじめに

Google Analytics4(以下GA4)単体では十分なデータ分析を行うことはできない。もっと細かくデータを分析したいときに、GA4のコンソールは十分な精度を提供してはくれない。また、データの保持期間も最大14か月であり、保持期間を過ぎたデータは消えてしまう。

GA4のコンソールより詳細な分析をしたい、あるいは14か月以上の長期間にわたりデータを保持したいユースケースに備えて、GA4にはBigQueryへのデータのエキスポート機能が備わっている。このあたりに関しては別の文献に詳しいので、この記事では省かせてもらう。

ただ、BigQueryにエキスポートするだけで即座に分析がしやすくなるわけではない。BigQueryへエキスポートされた直後の生データには以下のような弱点があり、そのままでは分析しづらいのが正直なところである。

タイムスタンプがマイクロ秒のUNIXTIME形式であり、非直感的
- datetime形式の方が扱いやすい
- タイムゾーン情報も明示したい
event_paramsが正規化されていない
- UNNESTを多用するのは認知負荷が高い
余分なカラムが多すぎる
- GA4は多機能で取得できるデータも多く、結果としてカラムの数が膨大になる
- 使わないカラムが多すぎて邪魔になる
  etc...（ほかにもたくさん）

そこで、この記事ではBigQueryにエキスポートされたGA4のデータを成型し、分析しやすい形に成型する処理について詳細を書こうと思う。dbt Cloudでの実行の検証を行ったが、dbt core単体のみで実行は可能である。また、GA4のBigQueryへのエキスポート設定は済んでいるものとし、この記事では説明しない。

ためしたこと

以下のパッケージを利用してみたが、利用しないテーブルが多く作成されすぎてしまって面倒なことになってしまった。

TL; DR

まずはdbt_project.yml に以下のようにvarsを設定する

dbt_project.yml

vars:
  last_3_days : [
        current_date('Asia/Tokyo')
        , date_sub(current_date('Asia/Tokyo'), interval 1 day)
        , date_sub(current_date('Asia/Tokyo'), interval 2 day)
    ]

次に、schema.ymlでソースの設定を行う。idの部分はBigQueryのデータセット名として現れる。

schema.yml

version: 2

sources:
  - name: analytics_${your_id}
    tables:
      - name: events_*

最後に、設定した変数を用いてinsert_overwriteを用いてインクリメンタルに実行するSQLを書く。細かい部分はお好みで。

stg_ga4.sql

{{
    config(
        materialized='incremental',
        partition_by = {
            "field": "event_date",
            "data_type": "date",
            "granularity": "day"
        },
        incremental_strategy='insert_overwrite',
        partitons=var('last_3_days')
    )
}}

SELECT
    parse_date('%Y%m%d', event_date) as event_date,
    datetime(timestamp_micros(event_timestamp), 'UTC') as event_datetime_utc,
    datetime(timestamp_micros(event_timestamp), 'Asia/Tokyo') as  event_datetime_jst,
    event_name,
    (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,
    (select value.int_value from unnest(event_params) where key = 'session_engaged') as session_engaged,
    (select value.int_value from unnest(event_params) where key = 'entrances') as entrances,
    (select value.int_value from unnest(event_params) where key = 'engagement_time_msec') as engagement_time_msec,
    (select value.string_value from unnest(event_params) where key = 'page_location') as page_location,
    (select value.string_value from unnest(event_params) where key = 'page_title') as page_title,
    (select value.string_value from unnest(event_params) where key = 'page_referrer') as page_referrer,
    user_pseudo_id,
    datetime(timestamp_micros(user_first_touch_timestamp), 'UTC') as user_first_touch_datetime_utc,
    datetime(timestamp_micros(user_first_touch_timestamp), 'Asia/Tokyo' ) as user_first_touch_datetime_jst,
    device.category as device_category,
    device.mobile_brand_name as device_mobile_brand_name,
    device.mobile_model_name as device_mobile_model_name,
    device.operating_system as device_operating_system,
    device.operating_system_version as device_operating_system_version,
    device.language as device_language,
    device.web_info.browser as device_web_info_browser,
    device.web_info.browser_version as device_web_info_browser_version,
    geo.country as geo_country,
    geo.continent as geo_continent,
    geo.region as geo_region,
    geo.metro as geo_metro,
    traffic_source.name as traffic_source_name,
    traffic_source.medium as traffic_source_medium,
    traffic_source.source as traffic_source_source,
    platform
FROM
    {{ source("analytics_353281755", "events_*")}}
WHERE
    _TABLE_SUFFIX in (
        format_date('%Y%m%d', date_sub(current_date(), interval 3 day)),
        format_date('%Y%m%d', date_sub(current_date(), interval 2 day)),
        format_date('%Y%m%d', date_sub(current_date(), interval 1 day))
    )

説明

GA4のBigQueryへのエキスポートデータは以下のような形式で取得できる
- _TABLE_SUFFIXで日時を指定する
- events_テーブルへの反映は2日ほどラグがあるため、3日のインターバルを取っている。

select * from `project_id.analytics_353281756.events_*`
where _TABLE_SUFFIX = '20240430'

insert overwriteについて
- 公式ドキュメントに詳細があるため省略

はじめに

ためしたこと

TL; DR

説明

Discussion