Zenn
dely Tech BlogPublicationへの投稿
📑

Snowflake GA4コネクタ x ディメンショナルモデリングで快適なデータ分析

taishi
2024/07/29に公開
Snowflake
GA4
dbt
データエンジニアリング
データモデリング
tech

こんにちは、クラシル比較でエンジニアをやっている福島です。

今回のテーマは、
　「SnowflakeのGA4コネクタ x ディメンショナルモデリングによる快適なデータ分析」
です！

GA4コネクタとは、GA4のデータを取り込むためのSnowflake公式のコネクタです。
このコネクタの導入により、低コストでRAWデータを取り込めるようになります。
さらにディメンショナルモデリングと合わせることで、分析しやすさとクエリパフォーマンスを担保しつつ、快適なデータ分析ライフを送れるようになります。

Snowflakeユーザーの方、これから導入を検討してる方の参考になれば幸いです🙌

データ基盤の全体像

本題の前に、クラシル比較のデータ基盤の全体像を紹介します。

ざっくりこんな感じの構成です

  • データレイク層 ... 上流データが格納されてる
  • ウェアハウス層 ... dbtを用いてデータ変換・モデリングしてる
  • データマート層 ... ウェアハウス層のデータを用途ごとに集計したデータが格納されてる

📝 基本的に、Snowflake外部（BIツール、クラシル比較のアプリケーション等）からはデータマート層のデータを参照するようにしています。個人的にはインターフェースをデータマート層に寄せておくことでdbtモデルに変更をかける時の影響範囲が限定されて保守しやすいと思います。

GA4コネクタによるデータ取り込み

ここからいよいよ本題です❄️

コネクタで取り込まれるのは2種類のテーブルです。

  • analytics_xxx ... バッチエクスポートされたデータ
  • analytics_intraday_xxx .... ストリーミングエクスポートされたデータ

analytics_xxxで完全なデータを参照しつつ、最新データはanalytics_intraday_xxx を参照しています

取り込まれたRAWデータ

では実際に、どのようなデータが取り込まれるのでしょうか？

コネクタで取り込まれたRAWデータ(analytics_xxxxx)は以下のようになります

さらに、これを構造化したView(analytics_xxxxx__view)まで自動で生成してくれます

もう少しデータを扱いやすく

構造化されたViewをさらに扱いやすくするため、EVENT_PARAMSをパースしたViewを作成します。

まずは、JSONパースするためのFunctionを作成します

CREATE OR REPLACE FUNCTION DATABASE.SCHEMA.PARSE_GA4_OBJARRAY("V" VARIANT)
RETURNS VARIANT
LANGUAGE JAVASCRIPT
AS '
var result = {};
for (const x of V) {
  if (x.value){
    for (const [key, value] of Object.entries(x.value)) {
      if ( value != null && key != ''set_timestamp_micros'' ) {
        result[x.key] = value;
      }
    }
  } else {
    result[x.key] = null;
  }
}
return result
';

次にこのFunctionを用いてJSONパースしたViewの作成します(SELECT文だけ抜粋)

select
    event_date,
    event_name,
    parse_ga4_objarray(event_params):"page_location"::varchar(65535) as page_location,
    parse_ga4_objarray(event_params):"page_referrer"::varchar(65535) as page_referrer,
    parse_ga4_objarray(event_params):"page_title"::varchar(65535) as page_title,
    parse_ga4_objarray(event_params):"engagement_time_msec"::number(38,0) as engagement_time_msec,
    cast(parse_ga4_objarray(event_params):"engaged_session_event"::integer as boolean) as engaged_session_event,
    user_pseudo_id
from database.schema.analytics_xxx

結果的に、このようなViewが取得できました！

コスト面について

GA4コネクタによるデータ取り込みにかかるコストは、

  • BigQuery: Storage Read API
  • Snowflake側: ウェアハウス料金

の2つくらいで、かなり安くつくと思います。

ウェアハウス料金に関しては、コネクタがData Syncを実行する頻度に依存します。
そこまでリアルタイムなデータ分析を必要としない場合は、ここを低頻度にしておけばかなりコスト節約できます

GA4データをディメンショナルモデリング

ここまでで、GA4から取り込んだRAWデータを扱いやすい状態に整えてきましたが、データ分析の観点ではまだ課題あります。

  • RAWデータはデータ量が膨大なので、クエリ実行に時間がかかる
  • PV数や滞在時間などをGROUP BYで集計する必要がある
  • 他テーブルをJOINして分析する場合、何をキーにすれば良いか分からない

これらの課題を解決するために、ディメンショナルモデリングを採用します。
データを ファクト(事実) と ディメンション(次元) に分けて管理する手法です。このモデリングをdbtで実装していきます。

スタースキーマの設計

まず、最低限の粒度で集計されたGA4データをファクトとします。
次に、このデータを記事別に分析したいので、記事データをディメンションとします。
商品別でも分析したいので、商品データもディメンションとして追加します。

これにより、GA4データに関するスタースキーマの構成が定まります（一部のみ）。
他にもユーザー軸で分析したり、デバイス軸で分析したければ、適宜ディメンションを追加していく感じですね。

モデリングの実装(dbt)

最低限の粒度で集計したデータを、intermediate層として定義します。

{{
  config(
    materialized='ephemeral'
  )
}}


with tmp as (
  select
    event_date,
    parse_url(page_location):"path" as page_path,
    page_referrer,
    count_if(event_name = 'page_view') as page_view,
    count_if(event_name = 'session_start') as sessions,
    count_if(event_name = 'session_start' and engaged_session_event = true) as engaged_sessions,
    count(distinct case when engaged_session_event = true then user_pseudo_id
                        when event_name = 'first_visit' then user_pseudo_id
                        when engagement_time_msec > 0 then user_pseudo_id end) as active_users,
    ceil(
      sum(case when engaged_session_event = true then engagement_time_msec end) / 1000 --- 秒単位に変換
    ) as user_engagement_duration
  from {{ ref('web_events') }}
  group by all
),
final as (
  select
    {{ dbt_utils.generate_surrogate_key(['event_date', 'page_path', 'page_referrer']) }} as surrogate_key,
    *
  from tmp
)
select * from final

これとdim_articles, dim_productsを合わせることで、fct_web_eventsを定義します。

{{
  config(
    snowflake_warehouse="MEDIUM",
    materialized='incremental',
    incremental_strategy='delete+insert',
    unique_key='surrogate_key',
    on_schema_change='append_new_columns',
  )
}}

with web_events as (
  select *
  from {{ ref('int_web_events') }}
  where 1 = 1
  {% if is_incremental() %}
    and event_date >= (select dateadd(day, -5, max(event_date)) from {{ this }})
  {% endif %}
),
articles as (
  select *
  from {{ ref('dim_articles') }}
),
products as (
  select *
  from {{ ref('dim_products') }}
),
final as (
  select
    -- PK
    surrogate_key,

    -- FK
    articles.article_key,
    products.product_key,
    
    -- fact
    page_view,
    sessions,
    ...
  from web_events
  left join articles
    on replace(page_path, 'articles/', '') = articles.article_id
  left join products
    on replace(page_path, 'items/', '') = products.product_id
)
select * from final

↑で作成したfct_web_eventsにはincrementalモデルを採用しており、一日に数百万レコードにもなるRAWデータを効率よく集計、保管することができます。

ディメンショナルモデリングの進め方

ちょっと脱線です

ディメンショナルモデリングを導入する上で、何をディメンションに、どの粒度でファクトを保持するのかを把握するのは結構大変だと思います。
私も最初はどこから手をつけたら良いか分かりませんでした。

なので私は、以下の手順でモデリングを進めました。

  1. 最初からモデリングし過ぎず、まずはステージング層から大福帳データを作成してみる
  2. そこから何のデータが、何を軸に分析されてるのか？どの粒度で集計されてるのか？をリストアップ
  3. それをもとにER図を書いてみる
  4. ある程度方針が見えてきたら、実際にデータを利用するメンバーとコミュニケーション取り、まだリストアップしてない分析軸などを潰していく

色々アプローチは試しましたが、個人的にはこのやり方が一番効率良かった気がします。
私と同じような状況の方、ぜひ参考にしていただければ🙌

まとめ

いかがでしたか？

これまでSnowflakeにGA4データを取り込むには、何かしらのELTツールを使ってました。
ツールの利用には結構なお金がかかり、取り込めるデータにも制約がありました。
今回の機能追加により、このようなデメリットは解消され、BigQueryと同等のレベルでGA4連携が可能になったと感じています。

​ここで紹介した以外にも、Storage IntegrationやPrivate Sharingなど、Snowflakeには便利な機能がたくさんあり、それらを使いこなすことでデータエンジニアリングの選択肢が広がりそうです。​
最近ではAI/LLM関連の機能も追加されてきており、誰でも簡単に、安全にAIをデータ分析に活用できるようになれば、データの価値をさらに高めることができそうですね。​

以上です、読んでいただきありがとうございました！​​​​​​​​​

dely Tech Blog
dely Tech BlogPublication

BE THE SUNを目指し、世界を照らす発明を続けるdelyのエンジニアが発信するPublicationです。

Discussion