BigQuery

クエリのスケジューリングで最新データをLooker Studioで可視化

2024.08.25 2024.09.05

GA4のレポーティングする際には常に最新データが反映されていなければならない。またそれは「自動化」されていることも重要な要件となる。そうでなければ、静的なある一時のデータを可視化することにBIツールを使うことにあまり意味を持たなくなるためだ。

ここではBigQueryの「クエリのスケジューリング」機能を用いて技術要件を解決する。この方法は最も単純な方法でもあり、テーブルを最新状態に保つために自動化させる上でイメージが付きやすく取り掛かりやすい。また、サイト規模が大きくなく複雑なデータパイプラインを組む必要がないのであれば、十分に実務で活用することができる。

GA4のデータマート構築のための「クエリのスケジューリング」

GA4のデータマート構築のための「クエリのスケジューリング」

BigQueryのデータ可視化のアンチパターン

BigQueryにエクスポートされたGA4のデータに対して、カスタムクエリの中でSQLを書いてデータ抽出をしている解説をよく見かけるが、この方法は非推奨となる。理由としては、クエリが複雑になればなるほどコストが大きく跳ね上がる可能性があり、また可視化スピードも大きく下がるためだ。なぜコストが不必要にかかってしまうのかというと、Looker Studioで何か期間を変えたり、1つでも何かを変更する度に、カスタムクエリ内で書いたコードが走ってしまうため。Looker Studioで何か操作するごとにカスタムクエリのSQLの処理が走り、その都度BigQueryからデータを抽出するコストがかかり、その度にクエリを走らせる処理が走るためクエリの複雑さやデータ抽出量によって処理スピードも大きく低下する。

クエリのスケジューリングでデータマートを構築する

「データマート」とは最終的な参照元となるテーブルのことであり、常にLooker StudioなどのBIツールと連携されるテーブルが「最新状態」であることを担保できていればよい。GA4のケースだと、最新データは前日分までとなる。この前日分までのテーブルをBigQueryの「クエリのスケジューリング」機能で作成する。基本的な手順は以下の通り。

抽出するSQLを作成
「１」のクエリをスケジューリング
「２」で作成されたテーブルをLooker Studioから連携

抽出するSQLを作成

例として、前日分までのデータを抽出するSQLをまず作成する。

WITH Session_Daily AS (
  SELECT
    ymd,
    COUNT(DISTINCT ssid) AS _ssuu
  FROM (
    SELECT
      ymd,
      CONCAT(user_pseudo_id, '-', ga_session_id) AS ssid
    FROM (
      SELECT
        PARSE_DATE("%Y%m%d", event_date) AS ymd,
        user_pseudo_id,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
      FROM
        `<project>.<dataset>.events_*`
      WHERE
      _TABLE_SUFFIX BETWEEN '20240401' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY))
    )
  )
  GROUP BY
    ymd
  ORDER BY
    ymd
)

SELECT
  *
FROM
  Session_Daily

クエリのスケジューリング

[スケジュール] ボタンをクリック

スケジュールされたクエリの名前
ここで設定した「クエリのスケジューリング」の名称を分かりやすく付ける
繰り返しの頻度と時刻
BigQueryにエクスポートされるGA4の時刻は定まっていないが、最近は午前中にエクスポートされるなど早まっているため、クエリのスケジューリングが午後に開始されれば十分だといえる。注意点としてここで設定する時刻はUTCなので日本時間ではプラス9時間後になるため、それを考慮する。
クエリ結果の書き込み先
予めデータセットは新規で作成しておく
Table ID
ここはデータセットの中のテーブル名称となる。名称ルールとしては「スケジュールされたクエリの名前」と同一にしておくと後から見ても分かりやすい。
宛先テーブルのパーティショニングフィールド
日付のDATE型のカラムがテーブルが存在すれば、そのカラム名を指定する
宛先テーブルの書き込み設定
テーブルを最新状態にすることが目的なので[テーブルを上書きする]を選択
ロケーションタイプ
普段選択しているリージョンを選択する。
通知オプション
処理が失敗したときに通知がくるのでチェックをする。