【GA4】page.A -> page.B -> purchase の遷移セッション数を求める by BigQuery & SQL

はじめに

前回の記事

【GA4】[セッション] スコープ《シーケンス（ページ遷移）》by BigQuery & SQL

では、セッションスコープのページ遷移（page.A -> page.B）のSQL文を書いてみました。今回は、

page.A -> page.B -> purchase

という遷移があったセッション数を集計するSQL文を解説します。

今回使用するデータ

今回はGoogleが用意してくれている、GA4のサンプルデータを使用します。

bigquery-public-data 内にある、ga4_obfuscated_sample_ecommerceです。

このデータは、2020年11月1日から2021年1月31日までの3ケ月分が公開されています。

このデータは、SQLで呼び出す際は、下記のようになります。

    FROM
      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

今回計測する経路

ページA：

https://shop.googlemerchandisestore.com/

▼

ページB：

https://shop.googlemerchandisestore.com/Google+Redesign/Apparel

▼

イベント：purchase（＝購入完了）

と進んだ、セッション数を集計して見ましょう。

上記の▼の部分には、他のページの閲覧やイベントがあってもよいものとします。

いったん、ページA → ページBの遷移セッション数を出してみる

冒頭で紹介した、前回のページ遷移があったセッション数を計測するSQLを使って、いったん、ページA → ページBの遷移セッション数を出してみます。

WITH PageAVisits AS (
    SELECT DISTINCT
        user_pseudo_id,
        event_timestamp AS A_timestamp,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session
    FROM 
        `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
        UNNEST(event_params) AS params
    WHERE 
        _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
        AND event_name = 'page_view'
        AND params.key = 'page_location'
        AND params.value.string_value = 'https://shop.googlemerchandisestore.com/'
),

PageBVisits AS (
    SELECT DISTINCT
        user_pseudo_id,
        event_timestamp AS B_timestamp,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session
    FROM 
        `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
        UNNEST(event_params) AS params
    WHERE 
        _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
        AND event_name = 'page_view'
        AND params.key = 'page_location'
        AND params.value.string_value = 'https://shop.googlemerchandisestore.com/Google+Redesign/Apparel'
)

SELECT 
    COUNT(DISTINCT CONCAT(A.user_pseudo_id, '_', A.ga_session)) AS transitioned_sessions
FROM 
    PageAVisits A
JOIN 
    PageBVisits B
ON 
    A.user_pseudo_id = B.user_pseudo_id
    AND A.ga_session = B.ga_session
    AND A.A_timestamp < B.B_timestamp;

集計結果は、

となりました。

page.A -> page.B -> purchase の遷移セッション数を求める

上記で見た、ページA → ページBの遷移セッション数を出すSQLに加えて、新しくPurchaseEventsというサブクエリを追加して、purchaseイベントを発火するセッションをフィルタリングし、その後でpage.A -> page.Bの遷移と結合して適切な順序でイベントが発火されたセッションをカウントします。

WITH 
PageAVisits AS (
    SELECT DISTINCT
        user_pseudo_id,
        event_timestamp AS A_timestamp,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session
    FROM 
        `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
        UNNEST(event_params) AS params
    WHERE 
        _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
        AND event_name = 'page_view'
        AND params.key = 'page_location'
        AND params.value.string_value = 'https://shop.googlemerchandisestore.com/'
),

PageBVisits AS (
    SELECT DISTINCT
        user_pseudo_id,
        event_timestamp AS B_timestamp,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session
    FROM 
        `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
        UNNEST(event_params) AS params
    WHERE 
        _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
        AND event_name = 'page_view'
        AND params.key = 'page_location'
        AND params.value.string_value = 'https://shop.googlemerchandisestore.com/Google+Redesign/Apparel'
),

PurchaseEvents AS (
    SELECT DISTINCT
        user_pseudo_id,
        event_timestamp AS P_timestamp,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session
    FROM 
        `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    WHERE 
        _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
        AND event_name = 'purchase'
)

SELECT 
    COUNT(DISTINCT CONCAT(A.user_pseudo_id, '_', A.ga_session)) AS transitioned_purchase_sessions
FROM 
    PageAVisits A
JOIN 
    PageBVisits B
ON 
    A.user_pseudo_id = B.user_pseudo_id
    AND A.ga_session = B.ga_session
    AND A.A_timestamp < B.B_timestamp
JOIN
    PurchaseEvents P
ON 
    B.user_pseudo_id = P.user_pseudo_id
    AND B.ga_session = P.ga_session
    AND B.B_timestamp < P.P_timestamp;

上記のクエリは、page.Aからpage.Bへの遷移の後に、purchaseイベントが発火するセッションをカウントします。

集計結果は、

となりました。

今回の記事は、ここまでとなります。

前回の記事、

【GA4】[セッション] スコープ《シーケンス（ページ遷移）》by BigQuery & SQL

では、SQLの中味について、より詳しく解説しておりますので、よろしかったら、前回の記事も是非ご覧ください。