先代解析ツールUniversal Analyticsの次世代版としてリリースされたGoogle Analytics4。スマホアプリからの追跡や新たな指標エンゲージメントで、さらなる詳細なユーザー情報を収集でき、分析できるようになった。

しかし、そんなGA4には利用者を困らせる状況がある。それはデータを測定し、保存しておける期間が設定を変更しても14ヶ月、1年と２ヶ月しかないことだ。

BigQueryとSQL

アクセス解析の精度を上げるためには大前提として、できるだけ多くのデータを保存しておく必要がある。Big QueryはGA4の過去のGA4データを保存しておくデータベースだ。データベースであるからには、そこからデータを取り出す言語が必要になる。「SQL」だ。

データベースを用いるシステム開発で主に用いられるSQL。 GA4とBig Queryの連携で、デジタルマーケターにもSQLの波が押し寄せてきた。ここではそんなBig Queryで使うSQLを紹介していく。

初回のテーマは「日次のページビュー数を集計する」だ。

今回のSQLはこれだ。

日別のPV数をカウントする

最初のSQLは以下になる。

event_date をそのまま使ったクエリを見てみよう。

/* event_dateをdateとして、すべてのレコードの数をPVとして別名で取得する。
events_intraday_202207テーブルからの取得する。
event_nameと’page_view’ が一致することが条件とする。
event_dateでグループ化する。
event_dateで並び替え（昇順）を行う。 */

SELECT
  event_date AS date,
  COUNT(*) AS PV
FROM
  `technogram-354804.analytics_291973511.events_intraday_202207*`
WHERE
  event_name = 'page_view'
GROUP BY
  event_date
ORDER BY
  event_date
;

次は日付を復元してみよう。

event_timestamp から日付を復元したクエリを見てみよう。

（タイムゾーンが指定できる）

/* 整形された日付をdateとして、すべてのレコードの数をPVとして別名で取得する。
events_intraday_202207*のテーブルから取得する。
event_nameと’page_view’ が一致することが条件とする。
dateで並び替え（昇順）を行う */

SELECT
  FORMAT_DATE('%Y%m%d', DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo')) AS date,
  COUNT(*) AS PV
FROM
  `technogram-354804.analytics_291973511.events_intraday_202207*`
WHERE
  event_name = 'page_view'
GROUP BY
  date
ORDER BY
  date
;

最後にもう一つ紹介しよう。

WHERE 句ではなくCOUNTIF を使ったクエリ

/* event_dateをdateとして、event_name = 'page_view’に一致するデータの数をPVとして取得する。
events_intraday_202207*のテーブルから取得する。
event_dateでグループ化する。
event_dateで並び替え（昇順）する。 */

SELECT
  event_date AS date,
  COUNTIF(event_name = 'page_view') AS PV
FROM
  `technogram-354804.analytics_291973511.events_intraday_202207*`
GROUP BY
  event_date
ORDER BY
  event_date
;

実行結果

以下が実行結果となる。

これをGA4の探索画面から同じように集計をしてみよう。

若干数値がずれているが、概ね同じ結果の傾向を示している。

GA4の管理画面で表示されている数値とBigQueryで集計した数値に差異が発生してしまう事象に関しては公式のヘルプページにも記載がある。

「おい！管理画面の数値と違うぞ！集計ミスってるんじゃないのか！！」

と言われてしまう可能性があるため、注意が必要だ。

ここについてはGA4とBigQueryが本格的に活用され始めると必ず発生してくる問題だと考えられる。

BigQuery には効率的な計算方法が適用されないため、BigQuery の計算結果と、標準レポート、カスタム レポート、データ探索ツール、データポータルの計算結果にはわずかな差異が生じることがあります。 データを確認する方法を選ぶ際は、次のヒントを参考にしてください。

– 元データからより正確な結果を取得したい場合は、BigQuery で結果を確認してください。

– より効率的に結果を取得したい場合は、標準レポート、カスタム レポート、データ探索ツール、

データポータルで結果を確認してください

[出典]

https://support.google.com/firebase/answer/9191807