本記事では、BigQueryにエクスポートしたGA4（Googleアナリティクス4）のデータを用いて、eコマースにおける購入までのファネル分析をおこなうためのSQLクエリについて解説をおこなう。ECサイトにおいて最も重要なKPIの1つであるCVR（コンバージョン率）の改善をおこなう上で必須となる集計・分析である。

SQLクエリの概要

eコマースサイトにおける「ファネル分析」とは、顧客が商品購入に至るまでの各プロセスを定量化し、顧客の離脱ポイントやボトルネックを特定することで、CVR（コンバージョン率）の改善につなげる分析手法のことである。

「ファネル分析」とは、マーケティングにおける分析手法のひとつです。「商品を購入する」「サービスの申し込みをする」「資料の請求をする」など、各企業がそれぞれ設定したコンバージョン（CV、最終的な成果）に至るまでの顧客の行動プロセスを分解し、CVにつながる顧客の購買行動、およびCVに至らなかった顧客が離脱するプロセスとその離脱率を測定して改善を図ります。

出典 : ファネル分析とは？基礎知識や活用の注意点、成功事例を紹介

今回のサンプルクエリでは、購入プロセスとして以下の5つのポイントを設定して、それぞれのイベントが発生したセッション数を集計している。

• all_session = すべてのセッション
• view_item = 各商品ページの閲覧
• add_to_cart = 商品をカートに追加
• begin_checkout = 決済情報の入力
• purchase = 購入完了

※GA4では上記以外にも、細分化した購入プロセスをイベントとして設定することができる。詳細に関しては、以下の公式ドキュメントに記載されている。

参考記事① : [GA4] 推奨イベント

参考記事② : e コマースを測定する

またセッション数に関しては、user_pseudo_idと ga_session_idを連結した文字列をユニークセッションID = sid として、重複を除いてsidの数をカウントしている。

参考記事 : 日次のセッション数を集計するSQLクエリ | GA4QUERY

SQLクエリのサンプル

今回のSQLクエリは、以下のようになる。

WITH prep AS (
SELECT
  event_name,
  CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) AS sid
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20201201`
WHERE
  event_name
    IN ('view_item', 'add_to_cart', 'begin_checkout','purchase')
)

SELECT
  'all_session' AS event_name,
  COUNT(DISTINCT CONCAT(user_pseudo_id, 
								(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'))) AS session_count
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20201201`
UNION ALL
SELECT
  event_name,
  COUNT(DISTINCT sid) AS session_count
FROM
  prep
GROUP BY
  event_name
ORDER BY
  session_count DESC
;

※今回のSQLクエリでは、「すべてのセッション数」をファネルに追加したかったため、 UNION ALL を用いてやや強引な形で連結してしまっているので、もっとエレガントな集計ロジックがあれば、ぜひご指摘いただきたい。

BigQueryでの集計結果

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

今回の記事では、eコマースにおける購入までのファネル分析をおこなうためのSQLクエリについて解説をおこなった。ECサイトにおいて最も重要なKPIの1つであるCVR（コンバージョン率）の改善をおこなう上で必須となる集計・分析であるので、ぜひ活用されたい。