GA4×BigQueryでページ別の表示回数・ランディングページを集計する方法
「どのページがよく見られているか」「ユーザーはどのページからサイトに入ってきているか」は、サイト改善の基本になる数字です。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で取り出す
ページ別の集計ができると、「どの記事が集客に効いているか」「どのページで離脱しているか」といった、サイト改善に直結する分析ができるようになります。
NEWSLETTER
Data Schoolの新着記事をメールで受け取る
新しい記事が公開されたら、メールでお知らせします。配信はいつでも停止できます。
