本記事では、BigQueryにエクスポートしたGA4データを用いてページ別訪問数を集計するSQLクエリに関して解説をおこなう。旧Googleアナリティクス（ユニバーサルアナリティクス）では標準レポート内の「行動 > サイトコンテンツ > すべてのページ」部分で提供されていたが、（執筆時点では）GA4の標準レポートからは姿を消しているため、BigQueryにエクスポートしたGA4データでSQLクエリを書いて集計をおこなう際にはぜひ参考にしてほしい。

SQLクエリ概要

ページ別訪問数とは、ページ別のセッション数のことであり、1セッション中に何度も同じページを閲覧しても、ページ別訪問数のカウントは1となることである。

今回は上記の定義に従い、各URL（パス）ごとにセッション数を集計する。

セッション数の集計方法に関する記事はこちらを参照されたい。

また、正規表現を使ってURLからパスを取り出す方法に関する記事はこちらを参照されたい。

最後に、event_params内のイベントパラメータを取り出す方法に関する記事はこちらを参照されたい。

SQLクエリサンプル

今回のSQLのサンプルクエリは、以下になる。

WITH prep1 AS (
  SELECT
    REGEXP_REPLACE((SELECT value.string_value FROM UNNEST(event_params)
      WHERE key = 'page_location'), '[?].+', '') AS page_url, -- URLからパラメータ部分を取り除く
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title,
    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_202211*`
    WHERE _TABLE_SUFFIX BETWEEN '01' AND '20' -- 集計期間を11/1~11/20に設定
),

prep2 AS (
  SELECT
    REGEXP_REPLACE(page_url,'.+jp', '') AS url_path, -- URLからパス以外の部分を取り除く
    page_title, 
    sid
  FROM
    prep1
)

SELECT
  url_path,
  page_title,
  COUNT(DISTINCT sid) AS unique_page_view_count
FROM
  prep2
GROUP BY
  url_path,
  page_title
ORDER BY
  unique_page_view_count DESC
;

※上記のクエリを引用して使用する際は、 technogram-354804.analytics_291973511.events_intraday_202211* の部分をご自身のテーブル名に変更して使用してください。

集計結果（BigQuery＋GA4）

以下がBigQueryとGA4での、上記クエリの集計結果である。

■ BigQueryでの集計結果

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

BigQueryでの集計結果とGA4探索画面での集計結果が一致していることが確認できた。