サインアップ

ログイン

パスワードをお忘れですか

パスワードを忘れましたか? メールアドレスを入力してください。 リンクを受け取り、メールで新しいパスワードを作成します。

質問するにはログインする必要があります

GA4の生データテーブルをBigqueryでUNNEST化する

皆さんこんにちは

GA4がスタートして暫く経過しているので改めて振り返ってみます。
既にGAユニバーサルアナリティクス(以下GAUA)を使っている人には
馴染みがあるかと思いますがGAUAの生データは扱いやすいようフラットな1行にして
別のデータテーブルとして保存しているところも多いかと思います。
今日は扱いやすい形にするところまでをやってみます。

 

【フラットにするには】

GA4のサンプルデータが下記の画像になります。
エクセルらしいデータになっていないので違和感があるかと思いますがこれらを
エクセルらしいデータに変換するクエリを書いてみます。

ここからクエリ

SELECT
PARSE_DATE(“%Y%m%d”, event_date) AS date,
FORMAT_TIMESTAMP(‘%Y-%m-%d %H:%M:%S’, TIMESTAMP_MICROS(CAST(event_timestamp AS INT64))) AS date_timestamp_utc,
FORMAT_TIMESTAMP(‘%Y-%m-%d %H:%M:%S’, TIMESTAMP_MICROS(CAST(event_timestamp AS INT64)), ‘Asia/Tokyo’) AS date_timestamp_jst,
CONCAT(CAST(user_pseudo_id AS STRING),“_”,CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = ‘ga_session_id’) AS STRING)) AS SS_KEY,
event_name,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = ‘engaged_session_event’) AS engaged_session_event,
(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.int_value FROM UNNEST(event_params) WHERE key = ‘ga_session_number’) AS ga_session_number,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = ‘page_referrer’) AS page_referrer,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = ‘ga_session_id’) AS ga_session_id,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = ‘medium’) AS medium,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = ‘term’) AS term,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = ‘campaign’) AS campaign,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = ‘source’) AS source,
(SELECT value.string_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.int_value FROM UNNEST(event_params) WHERE key = ‘percent_scrolled’) AS percent_scrolled,
user_id,
user_pseudo_id
FROM {GAの元データ} –GA4のRAWデータが入っているデータセット

他にもSTRUCT(構造体)にまとめてしまう方法があったかと思いますが
そちらはもう少し確認してから記すように致します。

 

できあがったものを見てみるとGAUAにはあるけどGA4では無いものが結構ありますね。
GoogleのGA4のスキーマに関するドキュメントを確認してみるとカスタムディメンションの記述が
見当たらないことやOptimizeに関連するスキーマ等確認できないものがちらほらあります

現状ではGA4とGAUAの併用が望ましい状態かと思われます。

次回は計算をしつつ、GAUAではあるけどGA4にはない情報を足していく事に
着目したいと思います。

 

関連記事

コメントを残す