BigQuery
GA4×BigQuery

【BigQuery×GA4】ページごとのPV数・ユニーク訪問数・閲覧開始数を出すSQLクエリ

【BigQuery×GA4】ページごとのPV数・ユニーク訪問数・閲覧開始数を出すSQLクエリ BigQuery
2026.02.05

本記事では、BigQueryを利用してページごとのPV数・ユニーク訪問数・閲覧開始数を出すSQLクエリを解説する。

各指標の定義とカウント方法

PV数・ユニーク訪問数・閲覧開始数の定義と、どのようにカウントされているのかを紹介する。

PV数

PV数はGA4の指標でいう「表示回数」で、「page_view」イベントを数えることで算出できる。

ユニーク訪問数

ここでいうユニーク訪問数は、旧GA（ユニバーサルアナリティクス）では「ページ別訪問数」と言われていた指標で、1訪問につき1カウントまでのユニークページビューとも言える。

具体的には、ページごとに「セッション数」を数えることで算出でき、「user_pseudo_id」と「ga_session_id」を組み合わせた固有識別子を数える。

Google アナリティクス以外の異なるセッションを分析するには、user_id または user_pseudo_id と session_id を結び付けて、各セッションの固有識別子を取得することをご検討ください。

出所：イベントがセッション ID とセッション番号に関連付けられる仕組み – アナリティクスのセッションについて

閲覧開始数

閲覧開始数は、サイト訪問の1ページ目に付与されるイベントパラメータ「entrances」の数を数える。
「entrances」は、サイトに訪問した一番初めの「page_view」イベントのみに紐づいており、「1」の値が格納されている。

ページごとのPV数・ユニーク訪問数・閲覧開始数を出すSQLクエリ

SQL
SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = "page_location") AS pagelocation,
  COUNTIF(event_name = "page_view") AS pageviews,
  COUNT(DISTINCT CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = "ga_session_id"))) AS sessions,
  COUNT((SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = "entrances")) AS entrances
FROM
  `analytics_280494569.events_*` -- ご自身のプロジェクト名・データセット名に応じて変更する（`<project>.<datASet>.events_*`）
WHERE
  _table_suffix BETWEEN "20241101" AND "20241105" -- ご自身で抽出したい期間に変更する
GROUP BY
  pagelocation
ORDER BY
  pageviews DESC;

クエリ結果は以下のようになる。

ページごとのPV数・ユニーク訪問数・閲覧開始数を出すSQLクエリ結果テーブル

下はGA4で集計したときのデータだが、同じ数値となっており、上のクエリが正しいことがわかる。

クエリ解説

(SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = "page_location") AS pagelocation

上は、ページURLを抽出するクエリ。page_locationは、BigQueryでは「event_params」というフィールドのなかの「value」配下にある「string_value（文字列型）」のイベントパラメータであるから、上のような書き方になる。

COUNTIF(event_name = "page_view") AS pageviews

上は、先述したPV数を抽出するクエリ。「event_name」が“page_view”である場合にカウントしている。

COUNT(DISTINCT CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = "ga_session_id"))) AS sessions

上は、先述したユニーク訪問数を抽出するクエリ。ga_session_idは、「event_params」フィールドのなかの「value」配下にある「int_value（数値型）」のイベントパラメータであるから、(SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = “ga_session_id”)のようになる。

それをuser_pseudo_idと連結したときの固有の数をカウントしている。

COUNT((SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = "entrances")) AS entrances

上は、先述した閲覧開始数を抽出するクエリ。entrancesは、「event_params」フィールドのなかの「value」配下にある「int_value（数値型）」のイベントパラメータであるから、上のようになる。

おわりに

今回は、ページごとのPV数・ユニーク訪問数・閲覧開始数を抽出するSQLクエリを紹介した。かなり基礎的な集計内容なので、わざわざBigQueryを使わなくともGA4で可視化できるが、より複雑なSQLを書くときにも今回のクエリの書き方は役に立つと思う。
基礎的なものほど使用頻度が多いので、興味があればぜひトライしてみていただきたい。

タイトルとURLをコピーしました