GA4のeコマースで購入金額が大きいユーザーTOP10を抽出するSQLクエリの書き方

本記事ではBigQueryにエクスポートしたGA4（Googleアナリティクス4）のデータを用いて、対象期間において購入金額（売上）が大きい順にユーザーを並べて、そこから上位10ユーザーを抽出するSQLクエリについて解説をおこなう。ECサイトにおける価値の高いユーザー（ロイヤルカスタマー）を抽出し、その特徴や行動パターンなどを把握して施策を打つ上で重要なSQLクエリである。

SQLクエリの概要

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

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

続いて、eコマースの売上を集計するためには ecommerce.purchase_revenue に格納されている数値を利用する。

そして、ecommerce.purchase_revenue を user_pseudo_id でグループ化して合計し、金額が大きい順にソートをかけて上位10レコードのみを抽出する。

※ 厳密には user_pseudo_id は単なる「ブラウザID」であるため、各サービス固有の user_idを設定して集計に用いることで精度の高い分析が可能になる。

さらに、サンプルクエリでは期間を全期間に指定しているが、日次や月次、年次などタイムフレームを変更することで、特定範囲内における売上TOP10ユーザーを抽出することができる。

サンプルクエリのように全期間を指定すれば、各ユーザー（ user_pseudo_id）ごとのLTV（顧客生涯価値）を算出して、LTVが高い上位10ユーザーを抽出することができる。

今回は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
  user_pseudo_id,
  SUM(ecommerce.purchase_revenue) AS revenue
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
GROUP BY
  user_pseudo_id
ORDER BY
  revenue DESC
LIMIT
  10
;

BigQueryでの集計結果

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

ChatGPT（GPT-4モデル）によるSQL解説

ChatGPTによる解説をつけるので、学習の参考に役立ててほしい。

このSQLクエリは、指定されたデータセットからユーザーごとの合計購入金額（revenue）を計算し、購入金額が最も高い上位10人のユーザーを抽出しています。具体的なステップは以下のとおりです。

1.SELECT句で、user_pseudo_id（ユーザーの識別子）と、SUM(ecommerce.purchase_revenue)（購入金額の合計）を選択します。SUM関数を使って、各ユーザーの購入金額を合計しています。

2.FROM句で、データセットbigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*を指定します。ここでを使って、events_で始まるすべてのテーブルを対象にしています。

3.GROUP BY句を使って、結果をuser_pseudo_idでグループ化します。これにより、各ユーザーごとに購入金額の合計を計算できます。

4.ORDER BY句を使って、結果を購入金額（revenue）の降順で並べます。これにより、購入金額が最も高いユーザーが上位に表示されます。

5.最後に、LIMIT句を使って、結果を上位10人のユーザーに限定します。

このクエリを実行することで、売上が最も高い顧客を抽出し、その情報を使って戦略的な販売やマーケティング活動を行うことができます。また、顧客の購買行動や傾向を分析し、より効果的なプロモーションや製品開発に取り組むことができます。

今回の記事では、対象期間において購入金額（売上）が大きい順にユーザーを並べて、そこから上位10ユーザーを抽出するSQLクエリについて解説をおこなった。ECサイトにおける価値の高いユーザー（ロイヤルカスタマー）を抽出し、その特徴や行動パターンなどを把握して施策を打つ上で参考になるので、役立ててほしい。