本記事では、BigQueryにエクスポートしたGA4（Googleアナリティクス4）のデータを用いて、eコマースにおける商品別の売上を集計するためのSQLクエリについて解説をおこなう。ECサイトの運営において、日次や月次での売れ筋商品を確認するために必要な集計となるため、利用頻度が高いSQLクエリである。

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

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

そして、items.item_name で商品名を抽出し、 items.item_revenue を合計することで売上を集計する（※ ecommerce.purchase_revenue ではないので要注意）。

今回のサンプルクエリでは、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
  items.item_name AS item_name,
  SUM(items.item_revenue) AS revenue
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20201201`, UNNEST(items) AS items
WHERE
  event_name = 'purchase'
GROUP BY
  item_name
HAVING
  revenue IS NOT NULL
ORDER BY
  revenue DESC
;

BigQueryでの集計結果

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

今回の記事では、eコマースにおける商品別の売上を集計するためのSQLクエリについて解説を行った。日次や月次での売れ筋商品を集計する際に、ぜひ活用してほしい。