【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(=購入完了)

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

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

    冒頭で紹介した、前回のページ遷移があったセッション数を計測する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;

    集計結果は、

    となりました。

    上記で見た、ページ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の中味について、より詳しく解説しておりますので、よろしかったら、前回の記事も是非ご覧ください。

    アンド,エー株式会社
    facebookTwitterDiscord

    and,a株式会社(and,a Inc. アンド,エー株式会社)

    〒153-0063 東京都目黒区目黒1-4-16 目黒Gビル7階

    Copyright © 2017-2024 and,a Inc. All Rights Reserved.