BigQuery

[BigQuery] カゴ落ち分析SQL：GA4のEコマース

2026.09.07 2026.09.10

ここではGoogle Merchandise Storeのデータセットを使って、GA4のEコマースのカゴ落ち分析の一例について解説します。クエリの書き方はどこまで細部のロジックを考慮すべきかや、セッションベースで計測するのかなど、何を基準にするかでクエリが異なってきます。また、背景となる要件はそれぞれ異なりますのでクエリロジックに唯一の正解はありません。ここで解説するクエリがそのまま使えるケースは少ないでしょう。そのため、当記事のクエリをベースとしカスタマイズして使うということを前提として参考にしてください。

カゴ落ち分析クエリ
- 商品別カート落ち率：ユーザースコープ
- 日付別セッションごとのカート落ち率

カゴ落ち分析クエリ

商品別カート落ち率：ユーザースコープ

本クエリは、GA4のイベントログからユーザースコープで商品単位のカゴ落ち率（カート投入後7日以内の未購入率）を算出する処理となります。一般的に商品が購入されるまでのフローを考慮してユーザースコープでの集計を採用しておりますが、理由としては、通常、商品がカートに入れられたあと、セッションが切れ後日に購入するといったパターンは一般的だからです。

CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20201101');
CREATE TEMP FUNCTION date_to()   RETURNS STRING AS ('20210131');
CREATE TEMP FUNCTION lookback_days() RETURNS INT64 AS (7);

WITH 
  raw AS (
    SELECT
      user_pseudo_id,
      event_name,
      event_timestamp,
      items.item_id,
      items.item_name
    FROM
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
      UNNEST(items) AS items
    WHERE 1 = 1
      AND REGEXP_EXTRACT(_TABLE_SUFFIX, '[0-9]+') BETWEEN date_from() AND date_to()
      AND event_name IN ('add_to_cart', 'purchase')
      AND user_pseudo_id IS NOT NULL
      AND items.quantity IS NOT NULL
      AND items.quantity > 0
  ),

  first_cart_events AS (
    SELECT
      user_pseudo_id,
      item_id,
      MAX_BY(item_name, event_timestamp) AS item_name,
      MIN(event_timestamp) AS first_cart_ts
    FROM
      raw
    WHERE
      event_name = 'add_to_cart'
    GROUP BY
      user_pseudo_id,
      item_id
  ),

  cart_with_window AS (
    SELECT
      user_pseudo_id,
      item_id,
      item_name,
      first_cart_ts,
      EXISTS(
        SELECT 1 
        FROM raw AS p
        WHERE 1 = 1
          AND p.user_pseudo_id = f.user_pseudo_id
          AND p.item_id = f.item_id
          AND p.event_name = 'purchase'
          AND p.event_timestamp >= f.first_cart_ts
          AND p.event_timestamp <= f.first_cart_ts + (lookback_days() * 86400 * 1000000)
      ) AS is_purchased_in_window
    FROM
      first_cart_events f
    WHERE 1 = 1
      AND first_cart_ts + (lookback_days() * 86400 * 1000000) <= UNIX_MICROS(TIMESTAMP(PARSE_DATE('%Y%m%d', date_to())) + INTERVAL 1 DAY - INTERVAL 1 MICROSECOND)
  ),

  calc AS (
    SELECT
      item_id,
      MAX_BY(item_name, first_cart_ts) AS item_name,
      COUNT(DISTINCT user_pseudo_id) AS users_added,
      COUNT(DISTINCT CASE WHEN is_purchased_in_window THEN user_pseudo_id END) AS users_purchased,
      COUNT(DISTINCT CASE WHEN NOT is_purchased_in_window THEN user_pseudo_id END) AS users_abandoned,
      ROUND(
        COUNT(DISTINCT CASE WHEN NOT is_purchased_in_window THEN user_pseudo_id END) * 100.0 / 
        NULLIF(COUNT(DISTINCT user_pseudo_id), 0),
        2
      ) AS abandonment_rate
    FROM
      cart_with_window
    GROUP BY
      item_id
  )

SELECT
  *
FROM
  calc

[20201101 〜 20210131 の events_* テーブル]
  |
  ▼ UNNEST(items) & フィルタ: add_to_cart / purchase のみ、quantity > 0（ゴースト行排除）
[raw: 必要なイベント・商品ログの抽出]
  |
  ▼ フィルタ: event_name = 'add_to_cart' & GROUP BY user_pseudo_id, item_id
  ▼ MAX_BY(item_name) & MIN(event_timestamp)
[first_cart_events: ユーザー×商品ごとの初回カート投入日時を特定]
  |
  ▼ フィルタ: 期間末尾7日間のカート投入を除外（右側打ち切り防止）
  ▼ EXISTS: 同一ユーザー・商品の purchase（カート投入後 7日以内）の有無を判定
[cart_with_window: 7日以内購入フラグ（is_purchased_in_window）の付与]
  |
  ▼ GROUP BY item_id & MAX_BY(item_name)
  ▼ COUNT(DISTINCT user_pseudo_id) & カゴ落ち率（%）の算出
[calc: 商品別カゴ落ち集計テーブル]
  |
  ▼ SELECT *
[商品別の投入数・購入数・カゴ落ち数・カゴ落ち率を出力]

日付別セッションごとのカート落ち率

CREATE TEMP FUNCTION date_from()  RETURNS STRING AS ('20201101');
CREATE TEMP FUNCTION date_to()    RETURNS STRING AS ('20201130');

WITH
  raw AS (
    SELECT
      event_date,
      user_pseudo_id,
      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
      event_name,
      event_timestamp
    FROM
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE 1 = 1
      AND REGEXP_EXTRACT(_TABLE_SUFFIX, '[0-9]+') BETWEEN date_from() AND date_to()
      AND event_name IN ('session_start', 'add_to_cart', 'purchase')
  ),

  session_flags AS (
    SELECT
      PARSE_DATE('%Y%m%d', MIN(event_date)) AS ymd,
      CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING)) AS ssid,
      LOGICAL_OR(event_name = 'add_to_cart') AS has_add_to_cart,
      LOGICAL_OR(event_name = 'purchase') AS has_purchase,
      MIN(IF(event_name = 'add_to_cart', event_timestamp, NULL))
        <= MAX(IF(event_name = 'purchase', event_timestamp, NULL)) AS is_cart_converted
    FROM
      raw
    WHERE
      ga_session_id IS NOT NULL
    GROUP BY
      user_pseudo_id,
      ga_session_id
  ),

  calc AS (
    SELECT
      ymd,
      COUNT(ssid) AS _ss,                     
      COUNTIF(has_add_to_cart) AS _cart_add,         
      COUNTIF(has_purchase) AS _total_purchase,      
      COUNTIF(is_cart_converted) AS _cart_to_purchase, 
      COUNTIF(has_add_to_cart AND NOT COALESCE(is_cart_converted, FALSE)) AS _cart_abandoned,
      SAFE_DIVIDE(
        COUNTIF(has_add_to_cart AND NOT COALESCE(is_cart_converted, FALSE)),
        COUNTIF(has_add_to_cart)
      ) AS _cart_abandonment_rate
    FROM
      session_flags
    GROUP BY
      ymd
  )

SELECT
  *
FROM
  calc
ORDER BY
  ymd ASC

【元データ：GA4 events_* テーブル（1行＝1イベントログ）】
      │
      ▼ STEP 1：ログの抽出とセッションID展開（raw）
      │  ・対象期間（20201101〜20201130）のテーブルに絞り込み
      │  ・event_params から「ga_session_id」を取り出して展開
      │  ・分析対象のイベント（session_start / add_to_cart / purchase）のみ抽出
      │
      ▼ STEP 2：セッション単位への集約と行動判定（session_flags）
      │  ・ユーザーID × セッションID で1セッション1行にまとめる
      │  ・セッション開始日（最小の event_date）を代表日（ymd）として定義
      │  ・「カート追加」と「購入」それぞれの発生有無をフラグ化
      │  ・【時系列判定】「カート追加時刻 <= 購入時刻」で購入成功フラグを作成
      │
      ▼ STEP 3：日別メトリクスの集約とカゴ落ち率の算出（calc）
      │  ・日付（ymd）ごとに各セッションをカウント
      │  ・「カート追加あり」かつ「購入成功フラグなし」をカゴ落ちセッションとして算出
      │  ・SAFE_DIVIDE でゼロ除算を防ぎつつ、カゴ落ち率を計算
      │
【出力：日別の推移レポート（ORDER BY ymd ASC）】
  [ ymd | セッション数 | カート投入数 | 全購入数 | カート経由購入数 | カゴ落ち数 | カゴ落ち率 ]