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

かつてUAではセッションスコープ「シーケンスのセグメント」が作成できた

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

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

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

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

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

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

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

bit.ly/44cG80O

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

早速ですが、どのような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;

上記SQLの詳細解説

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）を取得する点が異なります。

最後に、PageAVisitsとPageBVisitsを結合して、ユーザーがページ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が正しいかどうかの検証　一般的なアプローチ方法

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

サンプルデータでのテスト:
- 既知のサンプルデータセットを作成し、そのデータセットに対してクエリを実行してみることで、期待される結果が得られるかどうかを確認します。
セグメンテーション:
- すべてのデータを一度に処理するのではなく、データをセグメント（例：日付ごと、ユーザーグループごと）に分けて分析することで、特定のセグメントで予期しない結果や誤差が生じていないかをチェックします。
クエリの分解:
- クエリを複数の部分に分解して、それぞれの部分の結果を個別に確認します。このステップバイステップのアプローチにより、問題が発生している具体的な場所を特定しやすくなります。
予期せぬ結果のチェック:
- ある範囲外の値や予期せぬ結果をフィルタリングするための追加の条件をクエリに組み込むことで、エラーや誤解を防ぐことができます。
コードのレビュー:
- 他のデータ専門家やチームメンバーにクエリのコードレビューを依頼することで、潜在的な問題や改善点を特定できます。
ドキュメントと説明:
- クエリが何を行っているのか、どのような前提条件や制限があるのかを文書化することで、クライアントやチームメンバーが理解しやすくなります。
ツールやプラットフォームの確認:
- 使用しているツールやプラットフォームが最新であり、知られているバグや制限がないことを確認します。
元のデータの検証:
- SQLクエリが正しい結果を返しているかどうかを検証する前に、元となるデータが正確であることを確認します。ソースデータに問題がある場合、クエリの結果も正確ではなくなる可能性があります。
結果の検証:
- 別の方法やツールを使用して、同じ結果を得られるかどうかを確認します。これにより、SQLクエリの結果に信頼性を持つことができます。

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

SQLが正しいかどうかの検証　異なる切り口で、もう一つSQLを書いてみる

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

1. 上記のSQLのアプローチ

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

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

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

2. 新たに書くもう一つのSQLのアプローチ

ステップ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と全く同じ結果でしたので、

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

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