GA4でeコマースの商品別カゴ落ち率を集計するSQLクエリ

本記事ではBigQueryにエクスポートしたGA4（Googleアナリティクス4）のデータを用いて、eコマースにおける商品別のカゴ落ち率を集計するためのSQLクエリについて解説をおこなう。全体のカゴ落ち率だけではなく商品ごとにカゴ落ち率を集計することで、特にカゴ落ち率が高い商品 = ボトルネックを特定できると共に、顧客の購買行動や商品選びの傾向を理解することができる。

SQLクエリの概要

カゴ落ちとは「ショッピングカートに商品を追加したが、購入までは至らなかった」という顧客の行動を表す。

カゴ落ちとは、ECサイトでカートに商品を入れたものの、購入まで至らず離脱してしまうことをいいます。

Baymard Institute社が2018年に行った調査※の結果、一般的にECサイトのカゴ落ち率は、平均して69.57％にもなることがわかっています。つまり、カートに商品を入れたお客さまのうち、10人中約7人は購入を完了せずページから離れてしまっているのです。

出典 : カゴ落ちとは？ECサイトのカゴ落ちの原因や対策・改善方法を解説

カゴ落ち率 = [ 1 – purchase イベントの発生数 / add_to_cart イベントの発生数 ] と定義する。

WITH句（サブクエリ）内の items カラムの取り扱いについては下記の記事を参照してほしい。

参考記事 : GA4でeコマースの商品別売上を集計するSQLクエリ | GA4QUERY

以下のSQLクエリサンプルでは、カート追加数（ add_to_cart イベントの発生数）が100回以上、購入数（ purchase イベントの発生数）が1回以上の商品に絞り込んで結果を表示している。

以下のSQLクエリのサンプルでは、カート落ち率が高い順に20個の商品名を抽出している。

割り算をおこなった際のエラーを回避するために、 SAFE_DIVIDE 関数を用いて計算をおこなっている。

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

出典 : SAFE_DIVIDE関数

今回は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クエリは、以下のようになる。

WITH prep AS (
  SELECT
    event_name,
    items.item_name,
    COUNT(items.quantity) AS item_count
  FROM
    `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202012*`, UNNEST(items) AS items

  GROUP BY
    event_name,
    item_name
)

SELECT
  item_name,
  SUM(CASE WHEN event_name = 'add_to_cart' THEN item_count ELSE 0 END) AS add_to_cart_count,
  SUM(CASE WHEN event_name = 'purchase' THEN item_count ELSE 0 END) AS purchase_count,
  ROUND(1 - (SAFE_DIVIDE(SUM(CASE WHEN event_name = 'purchase' THEN item_count ELSE 0 END), 
						             SUM(CASE WHEN event_name = 'add_to_cart' THEN item_count ELSE 0 END) )),2) AS cart_bandonment_rate
FROM
  prep
GROUP BY
  item_name
HAVING
  add_to_cart_count > 100
  AND purchase_count > 0
ORDER BY
  cart_bandonment_rate DESC
LIMIT
  20
;

ChatGPTによるSQLクエリの詳細解説

このSQLクエリは、Google Analytics 4のECサイトでの商品ごとのカゴ落ち率を計算し、上位20件を取得するものです。クエリは以下のようなステップで構成されています。

1.WITH句を用いて、prepという名前のサブクエリを作成します。このサブクエリでは、Google Analytics 4のデータセットから、イベント名（event_name）、商品名（item_name）、および商品の数量（items.quantity）を取得します。また、UNNEST関数を用いて、itemsカラムを展開し、イベントごとの商品情報を取得しています。最後に、イベント名と商品名でグループ化し、商品の数量をカウントしています。

2.SELECT句を使って、prepサブクエリから商品名（item_name）、カートに追加された商品の合計数量（add_to_cart_count）、購入された商品の合計数量（purchase_count）を取得します。この際、CASE文を用いてイベント名が「add_to_cart」または「purchase」である場合に限り、商品の数量をカウントしています。

3.カゴ落ち率（cart_abandonment_rate）を計算するために、購入された商品の数量をカートに追加された商品の数量で割り、その値を1から引いています。SAFE_DIVIDE関数を使っているため、0での除算が発生してもエラーにならず、NULLが返されます。また、ROUND関数を使って、カゴ落ち率を小数点第2位まで四捨五入しています。

4.商品名でグループ化し、カートに追加された商品数が100を超えるもので、購入された商品数が0より大きいものに限定しています。これにより、データ量が十分であり、実際に購入された商品に焦点を当てることができます。

5.最後に、カゴ落ち率が高い順にソートし、上位20件を取得します。

このクエリを実行することで、カゴ落ち率が高い商品のリストを取得し、ECサイト運営者が改善策を検討する際に役立てることができます。

BigQueryでの集計結果

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

今回は、eコマースにおける商品別のカゴ落ち率を集計するためのSQLクエリについて解説を行った。

商品ごとのカゴ落ち率を集計、特にカゴ落ち率が高い商品 = ボトルネックを特定、顧客の購買行動や商品選びの傾向を調査する際に活用されたい。

GA4でeコマースの商品別カゴ落ち率を集計するSQLクエリ | GA4QUERY

SQLクエリの概要

SQLクエリのサンプル

ChatGPTによるSQLクエリの詳細解説

BigQueryでの集計結果