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”で意味のあるグルーピングはできない。まとめると、「年月日」の粒度をグループ化することで「同じ日」同士がまとめられることになり、その日のページビュー数がカウントできることとなる。
このように”GROUP BY”で明示的に日付をグループ化しなければ、エラーとなり実行できない。”GROUP BY”で日付をグループ化した結果、SELECT文でそのグループ化した単位で行数をカウントすることが可能になる。
COUNT(*) AS PV
日次でまとめられた結果、そのグループ単位の行数をカウントするには、”COUNT(*)”を記載する。これにより日次でまとめられたグループの中で行数をカウントし、日次のページビュー数がカウントできることとなる。”AS”は別名と付けるときに使うもので、”AS”の省略も可能であるが明示的に付けることを推奨する。この別名が「列名」となるため、ここでは「PV」が列名となる。
PARSE_DATE(“%Y%m%d”, event_date)
BigQueryに格納されている”event_date”は日付ではあってもSTRING型の文字列であるため、日付に関する処理をする際に、日付として認識されない。そのため基本的にはGA4に格納されている日付は常に「日付型」に変換する必要がある。
_TABLE_SUFFIX
日付の指定に関して「*」で指定して部分をフィルタリングすることが可能になる。可読性が上がるため「_TABLE_SUFFIX」の使用を推奨する。
