GA4でeコマースのCVRを日別で集計するSQLクエリの書き方を解説

本記事ではBigQueryにエクスポートしたGA4データを用いて、商品購入におけるコンバージョン率（CVR）を集計するSQLクエリについて解説をおこなう。GA4のeコマーストラッキング機能を用いたデータ集計において利用頻度の高いSQLクエリであるため、ぜひマスターしてほしい。

SQLクエリの概要

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

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

ここでは、商品購入コンバージョン率（CVR） = トランザクション（商品購入）発生数 / セッション数と定義する（発生したセッションの中でどれくらいの割合で商品購入が発生したかという指標となる）。

また、トランザクション発生数は ecommerce.transaction_id を重複なしでカウントする。

トランザクション ID は、トランザクションごとに作成できる一意の識別子です（オーダー確認番号など）。各 e コマースイベントにトランザクション ID を追加することで、コンバージョンの重複カウントを避けることができます。

出典：[GA4] トランザクション ID を使用してコンバージョンの重複を抑える

セッション数は user_pseudo_id と ga_session_id を連結した値をセッションIDと定義して、重複なしでカウントする。

参考記事：GA4 + BigQueryで日次のセッション数を集計するクエリ

今回は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クエリサンプルは、以下になる。

WITH prep AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    event_date,
    ecommerce.transaction_id AS transaction_id,
  FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202012*`
)

SELECT
  event_date,
  ROUND(SAFE_DIVIDE(COUNT(DISTINCT transaction_id), COUNT(DISTINCT CONCAT(user_pseudo_id, ga_session_id))) * 100, 2) AS CVR_percent
FROM
  prep
GROUP BY
  event_date
ORDER BY
  event_date
;

BigQueryでの集計結果

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

続いて、この結果をLooker Studio(旧Data Portal)で簡易グラフにし、視覚化してみよう。

Looker Studioで簡易グラフを作成する

以下のようにBigQueryで集計した結果をLooker Studioを用いて簡単に視覚化することもできる。

データを探索 > Looker Studioで調べるをクリックするだけで自動的にLooker Studioに集計結果をエクスポートすることができる。

エクスポートしたデータを簡易グラフにした結果の参考例は、以下になる。

今回の記事では、BigQueryから集計したeコマースの日別CVRのデータをLooker Studioで視覚化するところまでを紹介した。

BigQueryと併用し、Looker Studioでデータの視覚化まで行う際の参考にしてほしい。