GA4でeコマースの平均注文商品数を日別で集計するSQLクエリ

本記事ではBigQueryにエクスポートしたGA4データを用いて、平均注文商品数（1回の注文あたりの購入商品数）を、日次で集計するSQLクエリについて解説をおこなう。ECサイトにおいて、合わせ買いを促すといったようなクロスセル施策を打つことで、顧客単価の上昇を目指している場合に、特に注視が必要なKPIとなる。

SQLクエリの概要

BigQueryにエクスポートしたGA4データのスキーマにおいて、eコマーストラッキングの売上関連の数値は ecommerce という RECORD タイプのフィールド（カラム）に格納されている。

また、ecommerce 内にネストされたフィールド（カラム）には、ecommerce.transaction_id や ecommerce.total_item_quantity という形で記述することでアクセスすることができる。

ここでは、平均注文商品数（1回の注文あたりの購入商品数） = 注文された商品数の合計数 / トランザクション（注文）発生数と定義する。

そして、注文された商品数の合計数は ecommerce.total_item_quantity の値を合計する。

さらに、トランザクション（注文）発生数は ecommerce.transaction_id を重複なしでカウントする。

また、割り算をおこなった際のエラーを回避するために、 SAFE_DIVIDE 関数を用いて計算をおこなっている（割り算の分母が0だとエラーが発生してしまうため）。

SAFE_DIVIDE関数

除算演算子（X / Y）と同じですが、0 で割った場合など、エラーがあると NULL を返します。

さらに続けて、購入された商品数のみをカウントするために、 WHERE句で event_name = ‘purchase’ と絞り込む。

今回はGoogleが用意しているGA4のサンプルデータセットを利用して集計をおこなっている。そのため、下記のSQLクエリサンプルをコピペしてそのまま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
  event_date,
  COUNT(DISTINCT ecommerce.transaction_id) AS transactions,
  SUM(ecommerce.total_item_quantity) AS item_quantity,
  ROUND(SAFE_DIVIDE(SUM(ecommerce.total_item_quantity), COUNT(DISTINCT ecommerce.transaction_id)), 2) AS items_per_transaction
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202012*`
WHERE
  event_name = 'purchase'
GROUP BY
  event_date
ORDER BY
  event_date
;

BigQueryでの集計結果

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

BigQueryにエクスポートしたデータを用いて、平均注文商品数を日次で集計する機会がある場合に、参考にしてほしい。