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

本記事ではBigQueryにエクスポートしたGA4データを用いて、eコマースの平均注文単価（※AOV）を集計するSQLクエリについて解説をおこなう。ECサイトにおいて最重要KPIの1つであり、集計に利用する機会が多いSQLクエリである。

※ AOV = Average Order Value

SQLクエリの概要

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

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

・平均注文金額（AOV）

平均注文金額（AOV）= 売上金額の合計 / トランザクション（商品購入）発生数と定義する。

・売上金額の合計

売上金額の合計は ecommerce.purchase_revenue の値を合計する。

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

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

SAFE_DIVIDE関数

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

今回は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.purchase_revenue) AS revenue,
  ROUND(SAFE_DIVIDE(SUM(ecommerce.purchase_revenue), COUNT(DISTINCT ecommerce.transaction_id)), 2) AS AOV
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202012*`
GROUP BY
  1
ORDER BY
  1
;

BigQueryでの集計結果

BigQueryでの集計結果は、以下のようになる。

Looker Studioで簡易グラフを作成する

さらに、以下のようにBigQueryで集計した結果をLooker Studioを用いて、簡単に視覚化することができる。

データを探索 > Looker Studioで調べるをクリックするだけで、自動的にLooker Studioに集計結果をエクスポートすることができる。

今回の記事では、BigQueryにエクスポートしたGA4データを用いて、eコマースの平均注文単価を集計するSQLクエリについて解説をおこなった。さらにLooker Studioでデータを可視化するための方法も併せて紹介した。

eコマースのデータを集計する際に参考にしてほしい。