Web Analytics

GA4×BigQueryでページ別の表示回数・ランディングページを集計する方法

酒井 裕麻株式会社Bistro 代表取締役社長

「どのページがよく見られているか」「ユーザーはどのページからサイトに入ってきているか」は、サイト改善の基本になる数字です。GA4の画面でも確認できますが、BigQueryでSQLを書けば、URLのまとめ方や期間を自由に変えて集計できます。

この記事では、GA4のエクスポートデータから、ページ別の表示回数・ユーザー数・平均エンゲージメント時間と、ランディングページ別のセッション数を集計する方法を解説します。

参考記事

GA4×BigQueryのevent_paramsをUNNESTで取り出す方法【初心者向けSQL】
GA4×BigQueryでユーザー数・セッション数を集計する方法【GA4の画面と合わない理由も解説】
SQLウィンドウ関数の使い方【ROW_NUMBER・RANK・LAG・累計・移動平均】

※この記事の実行結果は、GA4のエクスポートと同じ構造で作ったサンプルデータ(架空のサイト、2026年3月1日〜7日)で集計したものです。

page_locationとページパス

GA4のエクスポートデータでは、ページのURLは event_params の page_location に、https://www.example.com/blog/r-intro/?utm_source=newsletter のような完全なURLで入っています。

このままページ別に集計すると、同じページでもパラメータ(? 以降)の違いで別々の行に分かれてしまいます。そこで、ドメインとパラメータを取り除いた「ページパス」(/blog/r-intro/)を作ってから集計します。

REGEXP_EXTRACT(page_location, r'https?://[^/]+([^?#]*)')
部分 意味
https?:// http:// または https://
[^/]+ ドメイン(次の / までの文字)
([^?#]*) ? や # が出てくるまでの文字 → ここを取り出す

文字列の前の r は、\ などの記号をそのまま正規表現として扱うための書き方です。

ページ別の表示回数・ユーザー数・エンゲージメント時間

WITH ev AS (
  SELECT
    event_name
    , user_pseudo_id
    , REGEXP_EXTRACT(
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'),
        r'https?://[^/]+([^?#]*)') AS page_path
    , (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') AS engagement_time_msec
  FROM
    `プロジェクトID.analytics_123456789.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20260301' AND '20260307'
)
SELECT
  page_path
  , COUNTIF(event_name = 'page_view') AS page_views
  , COUNT(DISTINCT IF(event_name = 'page_view', user_pseudo_id, NULL)) AS users
  , ROUND(SUM(engagement_time_msec) / 1000
      / COUNT(DISTINCT IF(event_name = 'page_view', user_pseudo_id, NULL)), 1) AS avg_engagement_sec
FROM
  ev
GROUP BY
  page_path
HAVING
  page_views > 0
ORDER BY
  page_views DESC

実行結果

page_path page_views users avg_engagement_sec
/ 311 224 65.2
/blog/r-intro/ 292 215 63
/blog/sql-intro/ 197 160 59.7
/service/ 182 135 63
/contact/ 120 99 56.1
/thanks/ 15 15 51.2
列 計算のしかた
page_views そのページの page_view イベントの数
users そのページを見たユーザーの数
avg_engagement_sec そのページでのエンゲージメント時間の合計(秒)÷ ユーザー数

平均エンゲージメント時間は、GA4の画面の「ユーザーあたりの平均エンゲージメント時間」と同じ考え方で、ユーザー数で割っています。

※実際のGA4では、エンゲージメント時間は page_view だけでなく、ページを離れるときの user_engagement イベントなどにも記録されます。そのため、この記事のSQLでは event_name を page_view に絞らずに、そのページで起きたすべてのイベントの engagement_time_msec を合計しています。

ブログ記事だけを集計する

WHERE に条件を足せば、特定のディレクトリのページだけを集計できます。ブログ記事の日別の表示回数を出してみましょう。

SELECT
  event_date
  , COUNT(*) AS page_views
FROM
  `プロジェクトID.analytics_123456789.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20260301' AND '20260307'
  AND event_name = 'page_view'
  AND REGEXP_CONTAINS(
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'),
        r'/blog/')
GROUP BY
  event_date
ORDER BY
  event_date

実行結果

event_date page_views
20260301 82
20260302 79
20260303 71
20260304 71
20260305 71
20260306 64
20260307 51

REGEXP_CONTAINS(文字列, 正規表現) は、文字列が正規表現のパターンを含むかどうかを調べる関数です。LIKE '%/blog/%' と書いても同じ結果になります。

ランディングページ別のセッション数

ランディングページは、セッションの中で最初に表示されたページです。セッションごとに page_view を時刻の順に並べ、1番目のページを取り出して数えます。ここでウィンドウ関数の ROW_NUMBER を使います。

WITH pv AS (
  SELECT
    user_pseudo_id
    , (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
    , event_timestamp
    , REGEXP_EXTRACT(
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'),
        r'https?://[^/]+([^?#]*)') AS page_path
  FROM
    `プロジェクトID.analytics_123456789.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20260301' AND '20260307'
    AND event_name = 'page_view'
),
landing AS (
  SELECT
    *
  FROM
    pv
  QUALIFY
    ROW_NUMBER() OVER (PARTITION BY user_pseudo_id, ga_session_id ORDER BY event_timestamp) = 1
)
SELECT
  page_path AS landing_page
  , COUNT(*) AS sessions
FROM
  landing
GROUP BY
  landing_page
ORDER BY
  sessions DESC

実行結果

landing_page sessions
/ 209
/blog/r-intro/ 182
/blog/sql-intro/ 108
/service/ 79

トップページに次いで、「Rの基本」の記事から入ってくるセッションが多いことがわかります。ブログ記事が、検索から人を呼び込む入り口になっている、と読み取れます。

※実際のGA4のエクスポートデータでは、セッションの最初の page_view に entrances(値は1)というパラメータが付いています。(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'entrances') = 1 で絞り込んでも、ランディングページを集計できます。

ページ別の数字を見るときのポイント

  • 表示回数とユーザー数の両方を見る:表示回数だけ多いページは、同じ人が何度も見ているだけかもしれません
  • 入り口のページと、途中で見られるページを分けて考える:ランディングページとして多いページは、検索や広告からの集客の成果を表します
  • コンバージョンにつながるページを探す:お問い合わせなどのコンバージョンに至ったセッションで、どのページが見られていたかを集計すると、成果に貢献しているページがわかります

まとめ

  • page_location から REGEXP_EXTRACT でページパスを取り出してから集計する
  • 表示回数は COUNTIF(event_name = 'page_view')、ユーザー数は COUNT(DISTINCT …)
  • 平均エンゲージメント時間は、エンゲージメント時間の合計 ÷ ユーザー数
  • ランディングページは、セッションごとに最初の page_view を ROW_NUMBER と QUALIFY で取り出す

ページ別の集計ができると、「どの記事が集客に効いているか」「どのページで離脱しているか」といった、サイト改善に直結する分析ができるようになります。

CONTACT

データ活用のお悩み、
直接相談しませんか。

「何から始めればいいかわからない」段階でも大丈夫です。課題の整理からお手伝いします。