本記事では、BigQueryにエクスポートしたGA4（Googleアナリティクス4）のデータを用いて、eコマースにおいて注文された商品履歴を一覧で表示するSQLクエリについて解説をおこなう。これはデイリーで注文状況を確認する際や、注文状況の分析をする際の前処理としても活用できるSQLクエリである。また、 UNNEST と CROSS JOIN でフラットニングが必要となるため、初心者がつまづきやすいポイントでもある。

SQLクエリの概要

まず初めに、商品データが格納されている items というカラムは、 RECORD タイプかつ REPEATED モードに設定されているため、 UNNEST と CROSS JOIN を用いてフラット化する必要がある（ UNNEST の前にあるカンマが CROSS JOIN を表している）。

FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20201201`, UNNEST(items) AS items

（参考記事）

UNNESTとCROSS JOINを使ってevent_paramsをフラット化するSQLクエリ | GA4QUERY

この items カラムを、UNNEST と CROSS JOIN でフラット化することで、 items カラム内にネストされている各種フィールドに対して、 items.item_id や items.item_name といったような形でアクセスできるようになる。

そして、items にネストされたフィールドから、必要な情報を適宜 SELECT 内で記述していく。また、用意されているフィールドの種類が多いため、各種フィールドの定義に関しては公式ドキュメントを参照してほしい。

（参考記事）

[GA4] BigQuery Export スキーマ

今回のサンプルクエリでは、 event_timestamp と ecommerce.transaction_id も追加している。

さらにサンプルクエリでは、2020/12/01の注文履歴を抽出しているが、日付を変更したい場合は bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20201201 の最後の数字を変更すればよい。また、 202012* のような形で指定してあげると、2020年12月の注文履歴を一括で抽出することが可能である。

（参考記事）

ワイルドカードテーブルと _TABLE_SUFFIX の使い方 | GA4QUERY

今回はGoogleが用意しているGA4のサンプルデータセットを利用して集計をおこなっている。そのため、下記のサンプルクエリをコピペして、そのままBigQueryで実行していただければ、同じ結果を得ることができるようになっている。

Google Merchandise Storeは Google ブランドの商品を販売するオンライン ストアです。このサイトでは、Google アナリティクス 4 の標準のウェブ e コマースの実装と拡張計測機能が使用されます。BigQuery 一般公開データセット プログラムを通じて利用可能な ga4_obfuscated_sample_ecommerce データセットには、2020 年 11 月 1 日から 2021 年 1 月 31 日までの 3 か月間の難読化した BigQuery イベント エクスポート データのサンプルが含まれています。

出典 : Google アナリティクス 4 e コマースウェブ実装向けの BigQuery サンプル データセット

SQLクエリのサンプル

今回のSQLサンプルは、以下のようになる。

SELECT
  FORMAT_TIMESTAMP('%F %H:%M:%S', TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo')  AS event_timestamp,
  ecommerce.transaction_id AS transaction_id,
  items.item_id AS item_id,
  items.item_name AS item_name,
  items.item_brand AS item_brand,
  items.item_category AS item_category,
  items.quantity AS item_quantity,
  items.price AS item_price,
  items.item_revenue AS revenue,
  items.coupon AS coupon,

FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20201201`, UNNEST(items) AS items
WHERE
  event_name = 'purchase'
ORDER BY
  event_timestamp
;

BigQueryでの集計結果

SQLクエリの結果は、以下のようになった。

今回は、BigQueryにエクスポートしたGA4（Googleアナリティクス4）のデータを用いて、eコマースにおいて注文された商品履歴を一覧で表示する方法を紹介した。

デイリーで注文状況を確認する際や、注文状況の分析をする際の前処理としても活用できるので、自身のデータ抽出時に活かしてほしい。