[GA4+BigQuery] 整理するとクエリにどんな違いが出るかを見る

そのまま使うか整理してから使うか

BigQueryに連携されたデータをそのまま使うといろいろ大変なので、[GA4+BigQuery] 連携されたデータを使いやすく整理することが大切になる。GA4+BigQueryだけの話ではないのでそんな話を聞いたことがある人も多いだろう。

しかし今まで見たことない人だとイメージがつかないかもしれないので「日別・URL別のPV数」というよく使われていそうなデータを出すクエリを例として見てみよう。

連携されたデータをそのまま使う

整理を全くしないでevents_のテーブルからそのまま2022年7月のURL別pv数を集計するクエリを書いてみよう。

-- 整理していない状態での2022年7月のURL別pv数
select
split((select value.string_value from unnest(event_params) params where params.key = 'page_location'),'?')[OFFSET(0)] as page_location
,count(1)
from  `xxxxxxxxxx.xxxxxxxxxx.events_*`
where event_name='page_view'
and _table_suffix >= format_date('%Y%m%d',date('2022-07-01'))
and _table_suffix <= format_date('%Y%m%d',date('2022-07-31'))
group by 1

もしこのクエリを使う場合は下記のことに注意。

• プロジェクトとデータセットは書き換える
• 全期間使うとデータの課金で大変なことになるかもしれない
• page_locationはパラメータを外した値にしている（"?"でsplitしてできる配列の最初の要素をとっている）

整理されたデータで集計する

もしイベントごとにばらしたテーブルを作って整理していればかなり見やすくなる。

-- 整理した状態での2022年7月のURL別pv数
page_location
,count(1)
from from  `xxxxxxxxxx.xxxxxxxxxx.event_page_view`
where event_month='2022-07-01'
group by 1

ちょっとの違いでも詰みあがると大変なことになる

パラメータを外すために書いているこの1行だけでも「このカラムにはパラメータを外したURLが入っている」ことが保証されているのであれば「page_location」だけで済む。コピペしようにもどこに書いてあったか探すところから始まるのもよくある話。

-- パラメータを外す（?以降を消す）
,split((select value.string_value from unnest(event_params) params where params.key = 'page_location'),'?')[OFFSET(0)] as page_location

数行の違いでしかないがこの程度の単純なクエリでも違いがでる。これがもっと長く複雑なクエリになってくるとその負荷は何倍どころではなくなる。整理することでこの負荷を大きく下げることができる。

整理はしなくてもデータは使えるが、大量、多種類、大人数であればあるほど整理することで使いやすくなる、ということが少しでも伝わってくれれば幸いだ。

GA4+BigQueryのまとめ