本記事では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クエリについて解説を行った。
商品ごとのカゴ落ち率を集計、特にカゴ落ち率が高い商品 = ボトルネックを特定、顧客の購買行動や商品選びの傾向を調査する際に活用されたい。