🔥
Google Analytics 4 & BigQuery でBIツールにつなげるための準備SQL(PVログデータ)
はじめに
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におけるスケジュール機能は下記にどうそ。
スケジュール用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
)
実行手順
- SQLをてきとうなファイルに保存 (tmp_scheduled_page_view_report.sql)
- プロジェクト名とアナリティクスの値を自分のGoogle Analytics 4 から取得されているBigQuery側のGCPのプロジェクトID, データセット(analytics_xxx)に置き換えたファイルを作成。(scheduled_page_view_report.sql)
- BigQuery側でプロジェクト直下にデータセット「report」を作成
- BigQueryのクエリワークスペースで「scheduled_page_view_report.sql」をコピペして実行
- スケジュールされたクエリに追加(毎日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