本ブログで取っているGAの計測データをGA4に切り替えた(並行して数値は取得中)
APIをたたくスクリプトを書いて集計してBigQueryに入れてDataPortalから日ごろのアクセスなどを見ているがGA4に完全移行するには過去のPVもやはり見たいよなーと思っていた
GA4はBigQueryにエクスポートする機能があるので一度やってしまえばSQL頑張ってよしなにできる
過去GA3で取っていたデータもある程度の粒度でBigQueryにデータを置ければよしなにできるはず
良く見ていたのはpagePathで日ごとのpageViewだったのでいったんpagePath別日別のpageView数を出してGA4で取っているデータと付け合わせて可視化した
そのときの手順をまとめておく
- スプレッドシートのGAアドオンを使用してレポートの作成
- スプレッドシートをBigQueryの外部テーブルに設定
- SQLで整形してGA4のデータと付け合せてPV推移をDataPortalで可視化
スプレッドシートのGAアドオンを使用してレポートの作成
GA3のレポートをスプレッドシートに出力する
Create new report
下記のようにパラメータを記入しRun reports
をクリックしてレポート出力を待つ
export
シートに次のようにレポートデータが出力された
スプレッドシートをBigQueryの外部テーブルに設定
スプレッドシートを外部テーブルにしてBigQueryからクエリできるようにする
設定はこんな感じ
- ヘッダのスキップ
アドオンが自動生成するシートでスキップ対象なのは15行なので15に設定
- シートの範囲
アドオンが自動生成するシート名と範囲を指定する
対象範囲を確認して今回はexport!A:C
とした
- スキーマ自動検出
自動で設定してくれるようにする
BigQueryから確認する
export_ga3_pagepath_date
テーブルへクエリすることでスプレッドシートのデータを参照できるようになった
問題なさそう
SQLで整形してGA4のデータと付け合せてPV推移をDataPortalで可視化
整形
先の手順で作成したテーブルに対して次のようなSQLを発行してExport用のテーブルにデータを格納した
CREATE OR REPLACE TABLE sample.ga3_pagepath_date AS ( WITH query_trimed AS ( SELECT Date AS d, REGEXP_REPLACE( Page, r"\?.*", "" ) AS pagepath, ga_pageViews AS pageview FROM `project-111111.blog_data.export_ga3_pagepath_date` ) pagepath, SUM(pageview) AS pageview FROM query_trimed GROUP BY d, pagepath )
GA4のデータと付け合わせる
Dataformでクエリを書いた
今回はページごと日ごとのPV数を出すようにしている
この辺は参考になるかわからないが一応SQLXを載せておく
( SELECT DATE( timestamp_micros(event_timestamp), 'Asia/Tokyo' ) AS d, REGEXP_REPLACE( REGEXP_REPLACE( params.value.string_value, r"https?://swfz.hatenablog.com(.*)", "\\1" ), r"\?.*", "" ) AS pagepath, COUNT(params.key) AS pageview FROM ${ref("analytics")}, UNNEST(event_params) AS params WHERE event_name = 'page_view' AND params.key = 'page_location' AND DATE( timestamp_micros(event_timestamp), 'Asia/Tokyo' ) >= "2021-06-19" GROUP BY pagepath, d ) UNION ALL ( SELECT d, pagepath, pageview FROM ${ref("ga3_pagepath_date")} WHERE d <= "2021-06-18" )
やっていることはGA3のデータが入っているテーブルとGA4のデータが入っているテーブルをUNIONしているだけ
レコードが被って数値が二重にならないように両方のテーブルのWHERE句で日付を指定した
DataPortalで可視化する
途中の過程は省くがDataPortalで可視化してこんな感じ
まとめ
GA3のデータを集計してGA4と合わせて利用できるようにした
GAを移行するにあたって今までDataPortalで可視化してた部分の数値の過去データをどうしようかなーと思っていた
最初APIをたたいて直接BQに入れ込むなど考えたが単発での実行のためにスクリプト書くか?というところからそういえばと思いついてやってみたら割と簡単に実施できた
他のディメンジョンやメトリクスを増やして走査対象が大量になった場合は適さない気がするが個人ブログかつ今回くらいの規模であれば十分だった
というかスプレッドシートのアドオンでスケジュール実行もできそうだったのでGA3のデータを用いて何かするならこのパターンが楽で良さそう
わざわざスクリプト書かなくてもよかったなーと思いました