日次のページごとのページビュー数をBigQueryから集計するクエリについて解説する。ページビュー数はそれ自体を単体でモニタリングすることもあるが、ページへとドリルダウンしページビュー数の推移を追うことで個々のページの閲覧数の推移に大きな変動が起きていないかをチェックできるため、改善アクションにつなげるための示唆が得やすくなる。GA4の探索ではページビュー数は「表示回数」と表示されているが同じ意味である。

ページごとのページビュー数を日別で取得するクエリ

WITH
  Pagelocation_PV AS (
    SELECT
      ymd,
      page_location,
      COUNT(*) AS _pv
    FROM (
      SELECT
        PARSE_DATE("%Y%m%d", event_date) AS ymd,
        REGEXP_REPLACE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), r"\?.*", "") AS page_location
      FROM
        `<project>.<dataset>.events_*`
      WHERE
          _TABLE_SUFFIX BETWEEN '20240401' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
        AND
          event_name = 'page_view'
    )
    GROUP BY
      ymd,
      page_location
    ORDER BY
      ymd ASC,
      _pv DESC
)

SELECT
  *
FROM
  Pagelocation_PV

ChatGPTによるクエリ解説

ステップ1: データの選択
FROM句で、Google Analytics 4のイベントデータが保存されているBigQueryのテーブルを指定します。この例では`<project>.<dataset>.events_*`というテーブルからデータを取得しています。テーブル名の末尾の*は、特定の期間にまたがる複数のテーブルからデータを取得するためのワイルドカードです。

ステップ2: データのフィルタリング
WHERE句で、データの期間を指定しています。また、イベント名が’page_view’であるレコードのみを対象にしています。これはウェブページが表示されたときに記録されるイベントです。

ステップ3: データの加工
SELECT句内のPARSE_DATE関数でevent_date（イベントが発生した日付の文字列）を日付型に変換しています。ページのURL（page_location）からクエリパラメータ（URLの?以降の部分）を取り除いています。これにより、ページのアドレスをクリーンな形で取得し、正確なページビュー数を計算できるようにしています。

ステップ4: データの集計
GROUP BY句で、日付（ymd）と加工後のページロケーション（page_location）ごとにデータをグループ化しています。
COUNT(*)関数で、各グループのレコード数を数えています。これはそのページが特定の日に何回表示されたかを示します。

ステップ5: 結果の整理
ORDER BY句で、結果を日付順に並べ替えています。さらにページビュー数で降順に並べ替え、最もアクセスの多いページから表示しています。