notebook

都内でWEB系エンジニアやってます。

GA3のデータをBigQueryに移す

本ブログで取っている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

f:id:swfz:20210727051732p:plain

下記のようにパラメータを記入しRun reportsをクリックしてレポート出力を待つ

f:id:swfz:20210727051738p:plain

exportシートに次のようにレポートデータが出力された

f:id:swfz:20210727051744p:plain

スプレッドシートをBigQueryの外部テーブルに設定

スプレッドシートを外部テーブルにしてBigQueryからクエリできるようにする

f:id:swfz:20210727051751p:plain

設定はこんな感じ

  • ヘッダのスキップ

アドオンが自動生成するシートでスキップ対象なのは15行なので15に設定

  • シートの範囲

アドオンが自動生成するシート名と範囲を指定する

対象範囲を確認して今回はexport!A:Cとした

  • スキーマ自動検出

自動で設定してくれるようにする

BigQueryから確認する

export_ga3_pagepath_dateテーブルへクエリすることでスプレッドシートのデータを参照できるようになった

f:id:swfz:20210727051757p:plain

問題なさそう

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で可視化してこんな感じ

f:id:swfz:20210727051803p:plain

まとめ

GA3のデータを集計してGA4と合わせて利用できるようにした

GAを移行するにあたって今までDataPortalで可視化してた部分の数値の過去データをどうしようかなーと思っていた

最初APIをたたいて直接BQに入れ込むなど考えたが単発での実行のためにスクリプト書くか?というところからそういえばと思いついてやってみたら割と簡単に実施できた

他のディメンジョンやメトリクスを増やして走査対象が大量になった場合は適さない気がするが個人ブログかつ今回くらいの規模であれば十分だった

というかスプレッドシートのアドオンでスケジュール実行もできそうだったのでGA3のデータを用いて何かするならこのパターンが楽で良さそう

わざわざスクリプト書かなくてもよかったなーと思いました