BigqueryでのGoogle Analytics4のログデータ加工

Google Cloud

2023.05.282023.01.18

はじめに
前提
やりたいこと
アーキテクチャ
SQLコードサンプル
補足

はじめに

GA4からBigQueryにデータを連携して顧客分析に活用していました。GA4から流れてくるデータはネストされた構造となっており、SQLで通常のSELECTでは使用できないデータ構造となっております。その際に記述していたSQLが参考になればと思いコード掲載してみます。

前提

Google Cloudの初期環境構築ができている
GA4のデータがBigqueryに取り込みできている

やりたいこと

Bigqueryに流したGA4のログデータからview_itemイベントのuser_id,item_name,page_urlを取得する
- ※今回は日曜に実行して先週の日曜～土曜のデータを読みに行く
- 閲覧した（view_item タグ）けど商品お買い上げ頂いていない方へリコメンドする、の様なシナリオ書きたかった

アーキテクチャ

参考までにアーキテクチャですが、GA4のデータは以下のようにBigQueryに直接取込をしていました。

SQLコードサンプル

SELECT
   event_date,
   user_id,
   (select item_name from unnest(items)) as item_name,
   (select value.string_value from unnest(event_params) where key="page_location") as page_url,
   count(*) as pageviews

# 読みに行くテーブルを日付動的指定したいのでテーブル名の yyyymmdd を * に置き換え
る
FROM
   `プロジェクト名.データセット名.テーブル名*` 

WHERE
   _TABLE_SUFFIX BETWEEN
   FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL EXTRACT(DAYOFWEEK FROM CURRENT_DATE('Asia/Tokyo'))+6 DAY))
   AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL EXTRACT(DAYOFWEEK FROM CURRENT_DATE('Asia/Tokyo')) DAY))
AND 
   event_name = "view_item"
AND
   user_id is not null
AND
   (select item_variant from unnest(items)) != '(not set)'
GROUP BY
   event_date,
   user_id,
   item_name,
   page_url

補足

データ分析基盤の全体像はこちらでした。

参考になれば幸いです☕