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

    ウェブ解析レポートを作成する際に「同一セッション内で、ページAからページBにセッションした遷移数は?」という数値を取り上げたい場面が多々あります。

    UAの時は、セッションスコープのシーケンスのセグメントを作ることが出来たのですが、GA4では、この原稿を書いている時点ではできません。

    探索でセグメントを作る際に、「ユーザー セグメント」を選択した場合だけ、

    「シーケンスのセグメント」を作ることができます。

    では、UAで解析をしていた時のように、セッションスコープで「シーケンスのセグメント」を作りたい時はどうすれば、よいのでしょうか?

    この原稿を書いている現在、解決策は、GA4のデータをBigQueryにエクスポートして、SQLで集計することです。

    (もし、BigQueryでGA4のデータを処理したいので、入門編から入りたいという方には、下記のテキスト(PDF)をご用意しています。登録不要で直接閲覧していただけます。)

    bit.ly/44cG80O

    早速ですが、どのようなSQLを書けば、セッションスコープで「シーケンス」を集計できるのかを解説します。

    ページAからページBに遷移したセッション数を集計したい。
    ただし、ページAとページBの間に、別のページに遷移した場合も含む。
    ・ページA:https://nakata.click/
    ・ページB:https://nakata.click/ga4/2023/07/09/725/
    ・集計期間を2023年7月1日から、2023年7月31日までとする。
    ・BigQueryのSQL中のFROMによるデータベースへのアクセスは、下記の記述を使用する。(ここは、皆様の環境に合わせて書き替えてください。)
    FROM
      nakata-click.analytics_373572981.events_*

    実際のコードは、下記のようになります。

    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 
            `nakata-click.analytics_373572981.events_*`,
            UNNEST(event_params) AS params
        WHERE 
            _TABLE_SUFFIX BETWEEN '20230701' AND '20230731'
            AND event_name = 'page_view'
            AND params.key = 'page_location'
            AND params.value.string_value = 'https://nakata.click/'
    ),
    
    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 
            `nakata-click.analytics_373572981.events_*`,
            UNNEST(event_params) AS params
        WHERE 
            _TABLE_SUFFIX BETWEEN '20230701' AND '20230731'
            AND event_name = 'page_view'
            AND params.key = 'page_location'
            AND params.value.string_value = 'https://nakata.click/ga4/2023/07/09/725/'
    )
    
    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;
    WITH PageAVisits AS (

    WITH句は、クエリ内の一時的なテーブルやサブクエリを定義するためのものです。ここでは、PageAVisitsという名前のサブクエリを定義しています。

        SELECT DISTINCT

    SELECT文でデータを取得します。DISTINCTキーワードを使うことで、重複した行を取り除きます。

            user_pseudo_id,

    ユーザーの識別子user_pseudo_idを取得します。

            event_timestamp AS A_timestamp,

    イベントが発生したタイムスタンプをA_timestampという名前で取得します。

            (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session
    

    event_paramsは配列であり、UNNEST関数を使用して配列内の要素を展開します。その中からga_session_idというキーを持つ項目の値をga_sessionという名前で取得します。

        FROM 
            `nakata-click.analytics_373572981.events_*`,

    データソースとしてnakata-click.analytics_373572981.events_*を指定します。*はワイルドカードとして、複数のテーブルからデータを取得する際に使われます。

    .eventよりも前の文字列は、読者の皆様の環境に合わせて書き替えてください。

            UNNEST(event_params) AS params

    event_params配列を再度展開して、それをparamsという名前で参照できるようにします。

        WHERE 

    以下の条件節で、取得するデータの条件を絞り込みます。

            _TABLE_SUFFIX BETWEEN '20230701' AND '20230731'

    テーブルのサフィックス(接尾辞)を使用して、2023年7月1日から2023年7月31日までのデータだけを取得します。(必要に応じて、集計期間を書き替えてください。)

            AND event_name = 'page_view'

    イベント名がpage_viewであるデータのみを取得します。

            AND params.key = 'page_location'

    展開したevent_paramsから、キーがpage_locationのデータを取得します。

            AND params.value.string_value = 'https://nakata.click/'

    解説: page_locationの値が'https://nakata.click/'であるデータのみを取得します。

    ここから下の部分(PageBVisitsの定義)も同様のロジックで動作しますが、異なるURL(ページBのURL)を取得する点が異なります。

    最後に、PageAVisitsPageBVisitsを結合して、ユーザーがページAを訪れた後にページ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が先に見られた」ということを判定しています。

    SQLの実行結果は、「6」となりました。

    「このSQLは本当に正しいのですか」という質問に対する検証方法には以下のアプローチがあります:

    1. サンプルデータでのテスト:

      • 既知のサンプルデータセットを作成し、そのデータセットに対してクエリを実行してみることで、期待される結果が得られるかどうかを確認します。

    2. セグメンテーション:

      • すべてのデータを一度に処理するのではなく、データをセグメント(例:日付ごと、ユーザーグループごと)に分けて分析することで、特定のセグメントで予期しない結果や誤差が生じていないかをチェックします。

    3. クエリの分解:

      • クエリを複数の部分に分解して、それぞれの部分の結果を個別に確認します。このステップバイステップのアプローチにより、問題が発生している具体的な場所を特定しやすくなります。

    4. 予期せぬ結果のチェック:

      • ある範囲外の値や予期せぬ結果をフィルタリングするための追加の条件をクエリに組み込むことで、エラーや誤解を防ぐことができます。

    5. コードのレビュー:

      • 他のデータ専門家やチームメンバーにクエリのコードレビューを依頼することで、潜在的な問題や改善点を特定できます。

    6. ドキュメントと説明:

      • クエリが何を行っているのか、どのような前提条件や制限があるのかを文書化することで、クライアントやチームメンバーが理解しやすくなります。

    7. ツールやプラットフォームの確認:

      • 使用しているツールやプラットフォームが最新であり、知られているバグや制限がないことを確認します。

    8. 元のデータの検証:

      • SQLクエリが正しい結果を返しているかどうかを検証する前に、元となるデータが正確であることを確認します。ソースデータに問題がある場合、クエリの結果も正確ではなくなる可能性があります。

    9. 結果の検証:

      • 別の方法やツールを使用して、同じ結果を得られるかどうかを確認します。これにより、SQLクエリの結果に信頼性を持つことができます。

    これらの検証手法を適切に組み合わせることで、クエリの正確性や信頼性を高めることができます。

    異なる切り口でSQLを書いて、結果を比較することで、検証を行う方法をご紹介します。

    ステップ1: まず、指定された期間内のページAとページBのビューイベントをそれぞれのCTE(Common Table Expression: 共通テーブル式)として分離して取得します。

    ステップ2: その後、これら2つのCTEを結合することで、同一のセッション内でページAの後にページBが表示されたセッションを特定します。

    この方法は、まず各ページのビューを個別にフィルタリングし、その後で結合という2ステップのプロセスを使用しています。


    ステップ1: 各セッションのページビューイベントを取得し、それらのイベントを時系列順に配列として保存します。

    ステップ2: この配列の中で、ページAの後にページBが表示されるイベントペアが存在するかどうかを確認します。

    この方法は、セッション全体のページビューイベントを一度に取得し、その後でページAからBへの遷移を探索するというアプローチをとっています。


    最初のSQL: これは、2つの異なる箱(ページAのビュー、ページBのビュー)を用意し、それぞれの箱にアイテム(ビューイベント)を分けて入れる方法に似ています。その後、2つの箱を一緒に取り出して、どちらのアイテムが同じセッションに属しているかを確認します。

    新しく書くSQL: これは、1つの大きな箱にすべてのアイテム(ページビューイベント)を時系列順に入れる方法に似ています。その後、この箱の中で、ページAのアイテムの後にページBのアイテムが存在するかどうかを確認します。

    ということで、新しく書いたSQLがこちらです。

    WITH SessionEvents AS (
        SELECT
            user_pseudo_id,
            (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session,
            ARRAY_AGG(STRUCT(event_timestamp AS timestamp, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_url) ORDER BY event_timestamp ASC) AS events
        FROM 
            `nakata-click.analytics_373572981.events_*`,
            UNNEST(event_params) AS params
        WHERE 
            _TABLE_SUFFIX BETWEEN '20230701' AND '20230731'
            AND event_name = 'page_view'
        GROUP BY 
            user_pseudo_id, ga_session
    )
    
    SELECT
        COUNT(DISTINCT CONCAT(user_pseudo_id, '_', ga_session)) AS transitioned_sessions
    FROM
        SessionEvents
    WHERE
        ARRAY_LENGTH(ARRAY(
            SELECT AS STRUCT event
            FROM UNNEST(events) AS event, UNNEST(events) AS next_event
            WHERE
                event.page_url = 'https://nakata.click/'
                AND next_event.page_url = 'https://nakata.click/ga4/2023/07/09/725/'
                AND event.timestamp < next_event.timestamp
        )) > 0;
    

    上記の実行結果は、最初に書いたSQLと全く同じ結果でしたので、

    これをもって、検証結果は問題なかったということとなります。

    アンド,エー株式会社
    facebookTwitterDiscord

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

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

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