データ整備

[GA4+BigQuery] 日別にページ別のpvを見たかったのでクエリを書いてついでに自動更新のデータマートを作ってデータポータルで可視化した

GA4でできないからBigQueryでやろう

GA4で日別にページ別のpvを見たかったがやり方がわからない。データポータルにGAを連携すればできそうな気はするが、GA4のデータをBigQueryに連携していることだしクエリを書いてみることにした。

ついでに、スケジュールされたクエリを設定して毎朝テーブルを更新するようにする。あとデータポータルで簡単なグラフを作る。

クエリを作っていく

目標は日別+ページ別を抽出するクエリだ。BigQueryに連携されているデータをきちんと触るのは初めてなのでいつもより丁寧に進めて行く。

PVのイベント

BigQueryにはイベント単位でレコードが存在する。ページビュー以外にもいろいろあるので絞る。

#ページビューのイベントだけとる
event_name='page_view'

日付

「event_timestamp」から作る。日付型にする時に日本時間にしておく。

#タイムスタンプを日本時間の日付型に変換
DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo')

ページタイトル

event_paramsの中にネストされている。

#ページタイトルの取得
(select value.string_value from unnest(event_params) p where p.key = 'page_title')

Google Analytics 4 + BigQueryでよく使う基本的なSQL例の「1. ページビュー数」を参考にさせていただきました。

実行した日の前日のデータを取得する

スケジュールされたクエリを設定する際に必要。

#実行日前日のデータを対象にする
_table_suffix= format_date('%Y%m%d',date_add(current_date("Asia/Tokyo"),interval -1 day))

date_addの代わりにdate_subを使うこともできる。その場合intervalは1 dayになる。

日別ページ別のPVのクエリ

ここまでで日別とページ別でpv数を集計するクエリができる。対象とする日付の範囲はリニューアルした5月16日以降前日まで。するとクエリは以下になる。

#実行日前日までの日別・ページ別のpv数
SELECT 
DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS pv_date
, (select value.string_value from unnest(event_params) p where p.key = 'page_title') page_title
,count(1) cnt_pv
FROM `xxxxxxxxxx.xxxxxxxxxx.events_*` #プロジェクトIDとデータセット名
where event_name='page_view'
and _table_suffix<= format_date('%Y%m%d',date_add(current_date("Asia/Tokyo"),interval -1 day))
and _table_suffix>=  '20210516' #リニューアルした日以降
group by 1,2

スケジュールされたクエリの設定

毎日自動更新させたいので、スケジュールされたクエリを使う。通常ならば前日までの分を先に作り、そこに翌日以降追加していくようにするだろう。今回は大した量ではないので毎日上書きにしておいた。

累積pv数を追加する

これで翌日に追加がきちんとされれば終わり、と思ったけど累積pv数も欲しくなった。最初に書いたクエリをテーブル名をcntとしてwith句に入れる。その後window関数を使えばいい。

#cnt_pvの累積を取る
sum(cnt.cnt_pv) over(partition by page_title order by page_title,date)

閲覧が無いと歯抜けになるので日付のリストを作る

累積pv数のグラフを作ったら何かおかしい。調べてみると閲覧がない日はレコードができないので歯抜けになるのが原因だった。そこで日付×ページのマスタを作る。マスタを作るついでにnullも削除しておく。

#日付×ページのマスタ
select * from unnest(GENERATE_DATE_ARRAY('2021-05-16',date_add(current_date("Asia/Tokyo"),interval -1 day))) as date
,(select distinct page_title from cnt where page_title is not null)  

閲覧が無い日は0にする

閲覧が無い日はpv数のカラムがnullになるので0に変換。累積pv数も最初にpv数が1以上になる日まではやはりnullになるのでこちらも0に変換。ifでもいいけど変数が3つ以上でも使えるcoalesceを基本的に使っている。

#nullは0にする
,coalesce(cnt.cnt_pv,0) as cnt_pv
,coalesce(sum(cnt.cnt_pv) over(partition by page_title order by page_title,date),0) as cum_pv

最終的に作ったクエリ

あとは全部組み合わせて以下のクエリになる。基本的な指標を取るだけのわりには様々な関数を使っており、少々ややこしいクエリになった。スケジュールされたクエリに入れてあるのでトラブルがなければ毎朝5時にデータが前日分まで更新される。

#実行日前日までの日別・ページ別のpv数と累積pv数
with cnt as (
SELECT 
DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS date
, (select value.string_value from unnest(event_params) p where p.key = 'page_title') page_title
,count(1) cnt_pv
FROM `xxxxxxxxxx.xxxxxxxxxx.events_*` #プロジェクトIDとデータセット名
where event_name='page_view'
and _table_suffix<= format_date('%Y%m%d',date_add(current_date("Asia/Tokyo"),interval -1 day))
and _table_suffix>=  '20210516'
group by 1,2
)

,date_page_list as (
select * from unnest(GENERATE_DATE_ARRAY('2021-05-16',date_add(current_date("Asia/Tokyo"),interval -1 day))) as date
,(select distinct page_title from cnt where page_title is not null)  
)

select 
date_page_list.date
,date_page_list.page_title
,coalesce(cnt.cnt_pv,0) as cnt_pv
,coalesce(sum(cnt.cnt_pv) over(partition by page_title order by page_title,date),0) as cum_pv
from date_page_list
    
left join cnt
using(date,page_title)

と思ったら更新されていなかった

翌朝確認したけれどまだデータが入っていなかった。とりあえず9時に設定を変更しておく。上書きなので 更新されていなかったら管理画面を見ればいいのだけど・・・それだと意味が無いな。

テーブルが出来ているのを確認したら更新するようにしたいが、スケジュールされたクエリだけではできない。いろいろ仕組みを導入するモチベーションは今のところないのでひとまずこれで完了にする。

データポータルで可視化

ついでにデータポータルでダッシュボードも作っておいた。上がPV数、下が累積PV数。左が日別ページ別のグラフで右が前日のページ別PV数。

データマートに集計済みのデータが格納してあるのでデータポータルで行っているのは可視化だけ。スタイルを選ぶ、指標を選ぶ、並び替えるで終わり。データ量が少ないのはあると思うが2-3秒で表示される。

BIを作るならデータマートを作っておいた方がいい、という話はBIではデータを加工しないでデータマートを作るにまとめてある。

おまけ:使えそうなカラム

今回使わなかったがメモ代わりに書いておく。

  • ユーザーid:user_pseudo_id
  • 時間だけ:EXTRACT(hour FROM 時間)。分とか秒でもできる
  • ページurl:page_location。page_urlではない

クエリで書くとこんな感じ

#使えそうなデータ
select
user_pseudo_id
,EXTRACT(HOUR FROM (DATETIME(timestamp_micros(event_timestamp), 'Asia/Tokyo'))) 
,(select value.string_value from unnest(event_params) p where p.key = 'page_location') 

GA4+BigQueryにどう向き合おうか

ひとまずやってみた感想としては、GA4+BigQueryはSQLに慣れていない人には結構大変そう。特にネストの扱いがややこしい。自分もそれなりにSQLを書いてはいるが自力で書くのは厳しかった。

今回はpv数を取るだけだったのでこれで済んだ。しかし、この後はユーザーやセッションなど他のイベントも使ってサイト内での動きを詳しく追うことになる。その後はGA以外のデータと結び付けていくことになるだろう。

とはいえGA4+BigQueryを使うことに時間やコストを取られてしまうと本末転倒だ。データポータルだけで比較的簡単できることに絞る方がいいことも多いだろう。

残念ながら手元にデータがないので検証できないのが残念だ。やってみたら簡単にできるのかもしれない。データはあって色々知りたいことがあるけれども手が回ってない会社さんとかいたら一緒に取り組んでみたい。

7/3追記:後でいろいろ追加したくなりそうなので日付×ページのテーブルを間に挟むように変更した。あわせてスクロールしたかのフラグも追加。

#実行日前日までの日別・ページ別のpv数と累積pv数+スクロール数
with pv as (
SELECT 
DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS date
,user_pseudo_id
,(select value.string_value from unnest(event_params) p where p.key = 'page_title') page_title
,(select value.int_value from unnest(event_params) p where p.key = 'ga_session_id') ga_session_id

FROM `xxxxxxxxxx.xxxxxxxxxx.events_*` #プロジェクトIDとデータセット名
where event_name='page_view'
and _table_suffix<= format_date('%Y%m%d',date_add(current_date("Asia/Tokyo"),interval -1 day))
and _table_suffix>=  '20210516'


)

, sc as (
SELECT 
DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS date
,user_pseudo_id
,(select value.string_value from unnest(event_params) p where p.key = 'page_title') page_title
,(select value.int_value from unnest(event_params) p where p.key = 'ga_session_id') ga_session_id
,1 as flag_scroll

FROM `xxxxxxxxxx.xxxxxxxxxx.events_*` #プロジェクトIDとデータセット名
where event_name='scroll'
and _table_suffix<= format_date('%Y%m%d',date_add(current_date("Asia/Tokyo"),interval -1 day))
and _table_suffix>=  '20210516'
)

,base as (
select
pv.*
,coalesce(flag_scroll,0) as flag_scroll
from pv
left join sc 
using(user_pseudo_id,page_title,ga_session_id)
)


,date_page_list as (
select * from unnest(GENERATE_DATE_ARRAY('2021-05-16',date_add(current_date("Asia/Tokyo"),interval -1 day))) as date
,(select distinct page_title from base where page_title is not null)  
)

, cnt as (
select 
date
,page_title
,count(1) as cnt_pv
,sum(base.flag_scroll) as cnt_scroll
from base
group by 1,2
)


select 
date_page_list.date
,date_page_list.page_title
,coalesce(cnt.cnt_pv,0) as cnt_pv
,coalesce(sum(cnt.cnt_pv) over(partition by page_title order by page_title,date),0) as cum_pv
,coalesce(cnt.cnt_scroll,0) as cnt_scroll
from date_page_list
    
left join cnt
using(date,page_title)

人気のある記事

HOME > データ整備 > [GA4+BigQuery] 日別にページ別のpvを見たかったのでクエリを書いてついでに自動更新のデータマートを作ってデータポータルで可視化した