本記事ではBigQueryにエクスポートしたGA4データを用いて、平均注文商品数(1回の注文あたりの購入商品数)を、日次で集計するSQLクエリについて解説をおこなう。ECサイトにおいて、合わせ買いを促すといったようなクロスセル施策を打つことで、顧客単価の上昇を目指している場合に、特に注視が必要なKPIとなる。
SQLクエリの概要
BigQueryにエクスポートしたGA4データのスキーマにおいて、eコマーストラッキングの売上関連の数値は ecommerce という RECORD タイプのフィールド(カラム)に格納されている。
![](https://www.ga4query.com/wp-content/uploads/2023/02/img_id00034-01-1024x962.png)
また、ecommerce 内にネストされたフィールド(カラム)には、ecommerce.transaction_id や ecommerce.total_item_quantity という形で記述することでアクセスすることができる。
ここでは、平均注文商品数(1回の注文あたりの購入商品数) = 注文された商品数の合計数 / トランザクション(注文)発生数と定義する。
そして、注文された商品数の合計数は ecommerce.total_item_quantity の値を合計する。
![](https://www.ga4query.com/wp-content/uploads/2023/02/img_id00034-02-1024x777.png)
さらに、トランザクション(注文)発生数は ecommerce.transaction_id を重複なしでカウントする。
![](https://www.ga4query.com/wp-content/uploads/2023/02/img_id00034-03.png)
また、割り算をおこなった際のエラーを回避するために、 SAFE_DIVIDE 関数を用いて計算をおこなっている(割り算の分母が0だとエラーが発生してしまうため)。
除算演算子(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では以下のような結果になった。
![](https://www.ga4query.com/wp-content/uploads/2023/02/img_id00034-04-1024x838.png)
BigQueryにエクスポートしたデータを用いて、平均注文商品数を日次で集計する機会がある場合に、参考にしてほしい。