今回は、各URLごとのPV数を集計するSQLクエリを紹介する。

SQLクエリ概要

まず、event_name を page_view に絞り込み、event_params から page_location パラメータを取り出してグループ化した上で、集計をおこなう。

UNNEST 関数とスカラーサブクエリを使用したイベントパラメータの取得方法に関しては、こちらの記事で解説している。

SQLクエリサンプル

クエリサンプルは、以下のようになる。

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
  COUNT(*) AS PV

FROM
  `technogram-354804.analytics_291973511.events_intraday_202208*`
WHERE
  event_name = 'page_view'
GROUP BY
  page_location
ORDER BY
  PV DESC
;

BigQueryでの集計結果

集計結果は、以下のようになる。

また、URLからパスを取り出す方法に関しては、こちらの記事で解説している。