BigQueryにエクスポートしたGA4のデータをSQLでPV（ページビュー）を取得するための基本的な書き方を解説する。ちなみにここで解説するのはGA4のSQLの中でも最も単純なSQLを例にするが、実務で使えないほどに単純化すると、それはそれで使えないサンプルになるため、実務レベルで使えかつ必要最低限な構文に絞ったSQLを提示する。また最低限の構文であるが、基本的な構文は汎用的なものでもあるため、基本を理解することが応用的なクエリを書く上で大切になってくる。そのため、かなり基本的なことから解説を行う。

日次のページビュー数カウントするクエリ

SELECT
  PARSE_DATE("%Y%m%d", event_date) AS ymd,
  COUNT(*) AS _pv
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
ORDER BY
  ymd ASC

ページビューをカウントできる仕組み

BigQueryにエクスポートされたGA4のテーブルは「イベント」単位で行が追加されていく。つまりデータ処理の最小単位がイベントであり、イベントの中にイベントが集計されることはない。このテーブルの格納の決まりを利用すれば、イベントが「ページビュー」である”page_view”で絞り込んだ上で、その行数をカウントすればページビュー数が計算できるというイメージはつくだろう。SQLではこれらの条件式は「WHERE」文の中に記載する。よって、ページビューを求めるときは「event_name = ‘page_view’」という条件を記載する”

日次のページビュー数の算出

最終的に求めたいのは、その日に何PVあったのか？であるので、「その日」を1つにグループ化する必要がある。このような何かの基準を元に1つにまとめてくれるSQLの機能が”GROUP BY”に該当する。グループ化の基準となるのは”GROUP BY”に記載した列の粒度そのものとなる。ここでは”ymd”を指定していており年月日の粒度であるため、この粒度で同じもの同士がまとめられることになる。仮にGA4のさらに粒度が細かい”event_timestamp”でグルーピングした場合、単位のミリ秒でグルーピングされることになるため、実務的に”event_timestamp”で意味のあるグルーピングはできない。まとめると、「年月日」の粒度をグループ化することで「同じ日」同士がまとめられることになり、その日のページビュー数がカウントできることとなる。

COUNT(*) AS _pv

日次でまとめられた結果、そのグループ単位の行数をカウントするには、”COUNT(*)”を記載する。これにより日次でまとめられたグループの中で行数をカウントし、日次のページビュー数がカウントできることとなる。”AS”は別名と付けるときに使うもので、”AS”の省略も可能であるが明示的に付けることを推奨する。この別名が「列名」となるため、ここでは「PV」が列名となる。

PARSE_DATE(“%Y%m%d”, event_date)

BigQueryに格納されている”event_date”は日付ではあってもSTRING型の文字列であるため、そのままでは日付として認識されない。そのため基本的にはGA4に格納されている日付は常に「日付型」に変換する必要がある。

参考：PARSE_DATE – Google Cloud BigQuery > 日付関数

_TABLE_SUFFIX

日付の指定に関して「*」で指定して部分をフィルタリングすることが可能になる。可読性が上がるため「_TABLE_SUFFIX」の使用を推奨する。

参考：ワイルドカード テーブルを使用した複数テーブルに対するクエリ – Google Cloud BigQuery

COUNTIFを活用したページビュー集計

WHEREで条件を指定せずSELECT文の中で直接’page_view’イベントをカウントする方法でもページビューの集計が可能である。このクエリのメリットはWHEREでイベントを’page_view’に指定すると集計可能なスコープが’page_view’に限られてしまうが、この方法と取れば特定のイベントに限定されないため柔軟性が増す。

SELECT
  PARSE_DATE('%Y%m%d', event_date) AS ymd,
  COUNTIF(event_name = 'page_view') AS _pv
FROM
  `<project>.<dataset>.events_*`
WHERE
    _TABLE_SUFFIX BETWEEN '20240401' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY))
GROUP BY
  ymd
ORDER BY
  ymd ASC