今回は、BigQueryにエクスポートしたGA4のデータを用いて、Googleアナリティクスを利用したアクセス解析において活用頻度の高い、ランディングページ別のセッション数を集計する際に必要となるSQLクエリに関して解説をおこなう。

SQLクエリ概要

まず、page_view イベントに絞り込み、 ROW_NUMBER 関数を利用して セッションID（sid）単位でパーティションを区切って時系列で連番を振る （ sid : user_pseudo_id と ga_session_id を連結した文字列のこと）。
続いて、 ROW_NUMBER 関数の値が1 = 各セッションにおける最初のページビュー = ランディングページとして is_entry フラグを立てる。
そして、ランディングページ（パス）ごとに is_entry の数を合計する。

ROW_NUMBER関数

https://cloud.google.com/bigquery/docs/reference/standard-sql/numbering_functions?hl=ja#row_number

☝ ROW_NUMBER 関数ではなく、 FIRST_VALUE 関数を用いてランディングページを特定することも可能。その際は下記のSQLクエリと内容が異なるので注意。（ ROW_NUMBER関数の部分をそのまま FIRST_VALUE 関数に置き換えてもうまくいかない）

☝ session_start イベントに格納されている page_location の値をランディングページと設定した集計も試みたが、GA4の探索画面での集計結果との乖離が上記の方法よりも大きくなった。

1. URLからパスを取り出す方法
2. イベントパラメータの取得方法
3. セッション数の集計方法

に関しては、以下の記事で詳しく解説している。

まず、1. URLからパスを取り出すSQLクエリについては、こちらで解説している。

また、2. UNNEST関数でevent_params内のパラメータを取り出すSQLクエリについては、こちらで解説している。

最後に、3. セッション数を集計するSQLクエリについては、こちらで解説している。

SQLクエリサンプル

クエリサンプルは、以下のようになる。

WITH prep1 AS (
  SELECT
    event_timestamp,
    REGEXP_REPLACE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), '[?].+', '') AS page_url, -- URLからパラメータ部分を取り除く
    CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS string)) AS sid

  FROM
    `technogram-354804.analytics_291973511.events_intraday_202209*`
  WHERE event_name = 'page_view'
),

prep2 AS (
  SELECT
    event_timestamp,
    REGEXP_REPLACE(page_url,'.+jp', '') AS url_path, -- URLからパス以外の部分を取り除く
    CASE WHEN ROW_NUMBER() OVER(PARTITION BY sid ORDER BY event_timestamp) = 1 THEN 1 ELSE 0 END AS is_entry
  FROM
    prep1
)

SELECT
  url_path,
  SUM(is_entry) AS landing_session_count,
FROM
  prep2
GROUP BY
  url_path
ORDER BY
  landing_session_count DESC;

BigQueryでの集計結果

BigQueryでの集計結果は、以下のようになる。

GA4の探索画面での集計結果

GA4での探索画面での集計結果は、以下のようになる。

ユニバーサルアナリティクス（旧Googleアナリティクス）の標準レポート（行動 > サイトコンテンツ > ランディングページ）での集計結果

ユニバーサルアナリティクス（旧Googleアナリティクス）の標準レポート（行動 > サイトコンテンツ > ランディングページ）での結果は、以下のようになる。

今回は、BigQuery、GA4の探索画面、UAの標準レポートでの集計結果にやや乖離が生じているため、引き続き調査を継続する。