ECサイトの購買情報を使ってGA4でUAの「ページの価値」を再現する方法

暑い日が続きますね🌞　暑さを避けて部屋にこもりがちですが、運動不足を感じているこの頃です。

さて、今回はUA（Universal Analytics）からGA4（Google Analytics 4）に代わって無くなってしまった「ページの価値」をGA4でも算出できるようにするお話です。

UAの「ページの価値」について

UAの「ページの価値」使っていましたか？

私は使っていました。新たにアナリティクスの設定を行う際は、コンバージョンを定義すると同時にそのコンバージョンに（とりあえずでも）ページの価値のベースとなる金額を指定していました。

UAでは上記のように１回のコンバージョンに対してその価値を金額で指定するだけで、そのコンバージョンに寄与したページ（そのコンバージョンユーザが閲覧したページ）に価値を配分して「ページの価値」つまり、そのページがコンバージョンにどのくらい貢献したかを自動的に算出してくれました。

GA4ではなくなってしまった指標ですが、ページを評価する際には便利な指標だったのでGA4でもできるなら使いたいという方も多いのではないでしょうか？

GA4のコンバージョン金額について

GA4ではそもそもコンバージョンに対して金額を割り当てるということができません。

もちろん、カスタムでイベントを用意するなどして金額情報をGA4に送り付ければ実現可能ですが、すでに運用しているサイトだと過去にさかのぼって適用するのは難しいです。

しかし、今回タイトルに挙げたECサイトであれば自動的に購入情報として金額がGA4に渡るように設定されているケースがほとんどでしょうから、ページの価値の算出が比較的容易です。

今回はECサイトを前提として、ページの価値を求める方法を説明します。

（弊社では、本記事で対象とする「ECサイト」以外でも、従来のUAのようにコンバージョンに対して貢献額を割り当てページの価値を算出する機能を持った「GLASSダッシュボード」サービスを提供しています。サービス詳細は以下バナーからご確認ください。）

GA4とBigQueryの連携

今回はGA4のデータをBigQueryにエクスポートして、「ページの価値」を計算するSQLを記述する方法をとります。SQLで得られたビューをLooker Studioに連携して最後にページごとにその金額価値を可視化します。

今回の記事ではすでにGA4をBigQueryにエクスポートできていることを前提としています。GA4をBigQueryにエクスポートする方法は以下記事が参考になります。

【Googleアナリティクス4とBigQueryを連携する方法】
https://www.trans-plus.jp/blog/column/202210_ga4-bigquery

SQLの記述

最初にSQL全体を示します。

-- １．purchaseイベントから購買情報の取得
WITH purchase_events AS (
  SELECT
    CONCAT(user_pseudo_id, session_params.value.int_value) AS session_id,
    ecommerce.purchase_revenue AS purchase_revenue,
    event_timestamp AS purchase_timestamp
  FROM
    `<project_id>.<dataset_id>.events_*`,
    UNNEST(event_params) AS session_params
  WHERE
    event_name = "purchase"
    AND session_params.key = "ga_session_id"
),

-- ２．page_viewイベントを抽出
page_view_events AS (
  SELECT
    CONCAT(user_pseudo_id, session_params.value.int_value) AS session_id,
    event_timestamp AS page_view_timestamp,
    page_params.value.string_value AS page_location
  FROM
    `<project_id>.<dataset_id>.events_*`,
    UNNEST(event_params) AS page_params,
    UNNEST(event_params) AS session_params
  WHERE
    event_name = "page_view"
    AND page_params.key = "page_location"
    AND session_params.key = "ga_session_id"
),

-- ３．同一セッションの購買イベント以前のpage_viewイベントを抽出
page_views_before_purchase AS (
  SELECT
    pve.session_id,
    pve.page_view_timestamp,
    pve.page_location,
    pe.purchase_timestamp,
    pe.purchase_revenue,
    ROW_NUMBER() OVER (PARTITION BY pve.session_id, pe.purchase_timestamp ORDER BY pve.page_view_timestamp DESC) AS rn
  FROM
    page_view_events pve
  JOIN
    purchase_events pe
  ON
    pve.session_id= pe.session_id
  WHERE
    pve.page_view_timestamp < pe.purchase_timestamp
)

-- ４．同一セッション、同一購買イベントに紐づくページビュー数を合計して売上額で割る
SELECT
  session_id,
  page_view_timestamp,
  page_location,
  purchase_timestamp,
  purchase_revenue / COUNT(*) OVER (PARTITION BY session_id, purchase_timestamp) AS page_value
FROM
  page_views_before_purchase

以下、WITH句単位で内容を解説します

１．purchaseイベントから購買情報の取得

WITH purchase_events AS (
  SELECT
    CONCAT(user_pseudo_id, session_params.value.int_value) AS session_id,
    ecommerce.purchase_revenue AS purchase_revenue,
    event_timestamp AS purchase_timestamp
  FROM
    `lively-goal-354101.analytics_334611624.events_20241110`,
    UNNEST(event_params) AS session_params
  WHERE
    event_name = "purchase"
    AND session_params.key = "ga_session_id"
),

GA4で購買イベントはevent_name=”purchase”です。売上金額はecommerce.purchase_revenueに入っているので、これをSELECTしておきましょう。

購買のタイムスタンプ（event_timestamp）が必要になるのでこれもSELECTに入れておきます。

ユーザのサイト回遊を追跡するためにga_session_idをSELECTします。ga_session_idはevent_paramsに含まれていますが、ネストされています。BigQueryでpurchaseイベントのevent_paramsの中身をみると以下の通りです。

そのため、FROM句でevent_paramsをUNNESTしておき、params.key=”ga_session_id”に絞ってデータを取得、その中からparams.value.int_valueの値を抽出します。

ここで注意が必要です。ga_session_idはごく稀に同じものが発行されることがあります。つまり、そのままga_session_idを使った場合、わずかに別セッションを同一セッションとして扱ってしまうケースが出てきます。そこで、より正確に計測するため今回はuser_pseudo_id（ユーザ疑似ID）とCONCATし、重複を防いでいます。

２．page_viewイベントを抽出

page_view_events AS (
  SELECT
    session_params.value.int_value AS session_id,
    event_timestamp AS page_view_timestamp,
    page_params.value.string_value AS page_location
  FROM
    `<project_id>.<dataset_id>.events_*`,
    UNNEST(event_params) AS page_params,
    UNNEST(event_params) AS session_params
  WHERE
    event_name = "page_view"
    AND page_params.key = "page_location"
    AND session_params.key = "ga_session_id"
),

購買したユーザのpage_viewイベントを特定していきたいので、先にデータをpage_viewに絞っておきます。

価値を割り振る対象のページを特定する必要があるので、page_location（ページのURL）を取得しておきます。page_locationも1.のga_session_id同様、event_paramsに含まれます。BigQueryでpage_viewイベントのevent_paramsの中身を見ると以下の通りです。

こちらも、FROM句でevent_paramsをUNNESTし、params.key=”page_location”に絞ってデータを取得、その中からparams.value.string_valueの値を抽出します。

３．同一セッションの購買イベント以前のpage_viewイベントを抽出

page_views_before_purchase AS (
  SELECT
    pve.session_id,
    pve.page_view_timestamp,
    pve.page_location,
    pe.purchase_timestamp,
    pe.purchase_revenue,
    ROW_NUMBER() OVER (PARTITION BY pve.session_id, pe.purchase_timestamp ORDER BY pve.page_view_timestamp DESC) AS rn
  FROM
    page_view_events pve
  JOIN
    purchase_events pe
  ON
    pve.session_id= pe.session_id
  WHERE
    pve.page_view_timestamp < pe.purchase_timestamp
)

先ほど1,2で用意した結果を使って、セッションIDとタイムスタンプでJOINします。

JOINの条件は、同一ユーザのイベントであることと、購入より前のタイムスタンプのページビューであることです。

４．同一セッション、同一購買イベントに紐づくページビュー数を合計して売上額で割る

SELECT
  session_id,
  page_view_timestamp,
  page_location,
  purchase_timestamp,
  purchase_revenue / COUNT(*) OVER (PARTITION BY session_id, purchase_timestamp) AS page_value
FROM
  page_views_before_purchase

3までで購買したユーザのたどったページのページビューを抽出できたので、あとはページの価値を算出していきます。

ページの価値は「購買イベントの売上 / 購買までにたどったページビュー数」で計算できます。購買イベントの売上はpurchase_revenue、購買までにたどったページビュー数は同セッション（session_id）、同一購買のタイムスタンプ（purchase_timestamp）のレコードをカウントして計算できます。このカウントの集計にはPARTITION BYを使いましょう。

最後に、BigQueryにてこのビューを任意の名前で保存しておきましょう。