🔥

Google Analytics 4 & BigQuery でBIツールにつなげるための準備SQL(PVログデータ)

2023/04/27に公開

はじめに

Google Analytics 4がはじまってまもなくに導入した某サイトのエンジニアが、2年以上運用と改善をつづけて「このあたりが中規模サイトでよく使える構成はこれかな?」という感覚がみえてきたので公開してみます。

ターゲット

誰のために?

下記にあてはまるサイト管理者・運用者、アナリスト、マーケター、データエンジニア

  • 中小規模のwebサイト
  • Google Analytics 4およびWebストリームを使用している
  • gtag.jsのデフォルトおよび推奨イベントが有効化されている

逆に大規模サイトの場合はこの記事の内容だと、困る部分があるはずなので別の方法を推奨します。

何のために?

  • 分析用のSQLを作成する
  • BigQueryのコストを削減し、分析実行を高速化する
  • 非エンジニアやマーケティングの同僚にもLooker StudioのようなBIツールを活用してもらいやすくする

何が得られる?

下記をみたすSQL

  • webサイトの日別の記事のPVについて基本的な分析ログデータ
  • BigQueryのスロット・スキャン量の削減された低コスト・高パフォーマンスSQL

(*) 日付パーティションを使っています。

得られないもの

  • Google Analytics4, BigQuery, Looker Studioの使い方
  • 高トラフィックサイトでの期待の効用
  • 分析データの目的に応じた使い方

内容

ゴール構成図

説明

  • report: BIに読み込ませるためのデータセット

SQL

上記のスケジュール設定されたクエリを対象にします。

BigQueryにおけるスケジュール機能は下記にどうそ。
https://cloud.google.com/bigquery/docs/scheduling-queries?hl=ja

スケジュール用SQL
# page_viewイベント開始日
DECLARE start_date_of_page_view DATE DEFAULT DATE(2023, 01, 01);

# 保存済データの最新の日付
DECLARE registered_latest_date DATE DEFAULT start_date_of_page_view;
# データ追加の対象期間_開始
DECLARE insert_start_date DATE;
# データ追加の対象期間_終了。 レイトヒット72時間考慮
DECLARE available_latest_date DATE DEFAULT DATE_SUB(CURRENT_DATE("Asia/Tokyo"), interval 4 day);

# クエリからテーブル作成
CREATE TABLE IF NOT EXISTS
    report.page_view
(
    event_date      DATE,
    event_at        DATETIME,
    user_pseudo_id  STRING,
    user_id         STRING,
    event_name      STRING,
    page_location   STRING,
    page_title      STRING,
    page_referrer   STRING,
    device_category STRING,
    country         STRING,
    region          STRING,
    city            STRING,
    traffic_name    STRING,
    traffic_medium  STRING,
    traffic_source  STRING
) PARTITION BY event_date;

# テーブルの状態に合わせて変数を再設定
SET registered_latest_date = (select IFNULL(MAX(event_date), start_date_of_page_view)
                              from `project_id.report.page_view`);
SET insert_start_date = DATE_ADD(registered_latest_date, INTERVAL 1 DAY);

# データを追加
INSERT INTO `project_id.report.page_view`
( event_date
, event_at
, user_pseudo_id
, user_id
, event_name
, page_location
, page_title
, page_referrer
, device_category
, country
, region
, city
, traffic_name
, traffic_medium
, traffic_source)
SELECT event_date
     , event_at
     , user_pseudo_id
     , user_id
     , event_name
     , page_location
     , page_title
     , page_referrer
     , device_category
     , country
     , region
     , city
     , traffic_name
     , traffic_medium
     , traffic_source
FROM (
         with daily_T as (
             select DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Tokyo") as event_at
                  , DATE(TIMESTAMP_MICROS(event_timestamp), "Asia/Tokyo")     as event_date
                  , user_pseudo_id
                  , user_id
                  , event_name
                  , event_params
                  , device.category                                           as device_category
                  , geo.country                                               as country
                  , geo.region                                                as region
                  , geo.city                                                  as city
                  , traffic_source.name                                       as traffic_name
                  , traffic_source.medium                                     as traffic_medium
                  , traffic_source.source                                     as traffic_source
             from `project_id.analytics_xxxxxxxxx.events_*`
             where _table_suffix between
                 FORMAT_DATE("%Y%m%d", insert_start_date)
                 and
                 FORMAT_DATE("%Y%m%d", available_latest_date)
               and platform = 'WEB'
               and event_name IN ("page_view")
         )

            , page_view_T as (
             select distinct event_date
                           , DATETIME_TRUNC(event_at, second) as event_at # 秒単位での丸め込み。1秒未満の単位で同一ユーザー、同一イベントレコードが存在した場合の重複の除外
                           , user_pseudo_id
                           , user_id
                           , event_name
                           , event_params.key                 as event_key
                           , event_params.value.string_value  as event_value_string
                           , event_params.value.int_value     as event_value_int
                           , device_category
                           , country
                           , region
                           , city
                           , traffic_name
                           , traffic_medium
                           , traffic_source
             from daily_T
                , UNNEST(event_params) as event_params
         )
            , page_view_T2 as (
             select event_date
                  , event_at
                  , user_pseudo_id
                  , user_id
                  , event_name
                  , MAX(case when event_key = "page_location" then event_value_string end) as page_location
                  , MAX(case when event_key = "page_title" then event_value_string end)    as page_title
                  , MAX(case when event_key = "page_referrer" then event_value_string end) as page_referrer
                  , device_category
                  , country
                  , region
                  , city
                  , traffic_name
                  , traffic_medium
                  , traffic_source
             from page_view_T
             group by event_date, event_at, user_pseudo_id, user_id, event_name, device_category, country, region, city,
                      traffic_name, traffic_medium, traffic_source
         )
         select *
         from page_view_T2
     )

実行手順

  1. SQLをてきとうなファイルに保存 (tmp_scheduled_page_view_report.sql)
  2. プロジェクト名とアナリティクスの値を自分のGoogle Analytics 4 から取得されているBigQuery側のGCPのプロジェクトID, データセット(analytics_xxx)に置き換えたファイルを作成。(scheduled_page_view_report.sql)
  3. BigQuery側でプロジェクト直下にデータセット「report」を作成
  4. BigQueryのクエリワークスペースで「scheduled_page_view_report.sql」をコピペして実行
  5. スケジュールされたクエリに追加(毎日x時に実行とするといいです)

ちなみに、2の置換はこんな感じですればいい。 (Mac ユーザーはsed => gsedに。)

sed -e 's/project_id/(プロジェクトID)/g' tmp_scheduled_page_view_report.sql > scheduled_page_view_report.sql

さいごに

ほかにもLooker Studioでのデータ抽出とか、クラスタつかったりなど軽量化などする方法はあります。
が、このSQLから始めると最適化しつつ、拡張・対応しやすいんじゃないかとおもってます。

テーブル設計がかわっても、SQLの変更 & report内のpage_viewデータセットを削除して、再実行すれば対応できます。

Discussion