GA4のデータをBigQueryを経由して15分刻みでLooker Studioに表示する方法

GA4で取得したデータは、探索レポートやLooker Studioで閲覧できるようになるまでに半日〜1日のタイムラグがあります。

収益が大きく動くリスティング施策では、このタイムラグはなかなか厄介です。
(なお、UAではもっと早く表示されていました。）

この記事では、GA4で取得したデータを、15分刻みでLooker Studioに表示する方法について解説します。

本記事は本職のデータベースエンジニアが作成した記事ではありません。
余計な手順が含まれている場合や、誤った記載がある可能性があります。
誤りに気がついた方は、お手数ですが問い合わせフォームよりご指摘いただけますと大変助かります。

データの流れの概要

GA4データを15分刻みでLooker Studioで表示するために、以下の図のようなGA4→BIgQuery→Looker Studioのデータパイプラインを作成します。

Google Cloudプロジェクトの作成

こちらの記事に従って、プロジェクトを作成します。

GA4とビッククエリを連携

以降は、GoogleAnalytics4での設定になります。

GA4の左サイドバーの「管理」→「BigQueryのリンク」をクリック

右上の「リンク」をクリック

先ほど作成したプロジェクトを選択し、確認をクリックします。

ここで注意点なのですが、
「デフォルトのデータセット作成先ロケーション」
は「US(米国)」のままにしてください。

東京 (asia-northeast1)
を選択することもできるのですが、東京にすると、後の設定でなぜかエラーが出ます。

次へをクリックします。

エクスポート タイプにて、「ストリーミング」を選択します。
（選択できない状態になっている場合、こちらの記事で解説したクレジットカードの登録がされていません。GoogleCloudの「お支払い」タブから、クレジットカードの登録を確認してください。）

ユーザーデータについては、デフォルトの設定（チェックなし）のままでOKです。

最後に「送信」をクリックして、GA4での設定は終了です。

※なお、ストリーミングでエクスポートする場合、データが欠損することがあります。
正確なデータを取得したい場合は「毎日」の方法でエクスポートすべきなので、今回お伝えするする方法には向いていません。

クエリの定期実行環境を構築

GA4の設定から、最長で24時間すると
プロジェクト名_analytics_XXXXXXXXXというテーブルが自動的に作成されています。

例：bluegoat.analytics_276440588

更新用データセットとテーブルの作成

このGA4から取得したデータセットから、15分間隔で特定のデータを取得し、更新用のテーブルに書き込む処理を作成します。

データセットの作成

まずは、書き込み先のテーブルを作成します。

プロジェクト名の横にある3点リーダーから、「データセットの作成」をクリック

 

データセット IDに
frequenty_update
などのデータセット名を入力し、「データセットを作成」をクリックします。

テーブルの作成

作成したデータセット名の横にある3点リーダーから、「テーブルの作成」をクリックします。

テーブル名に
15min_update
などのわかりやすいテーブル名を入力し、テーブルを作成をクリックします。

スケジュールされたクエリの作成

左サイドバーから、スケジュールされたクエリを選択します。

クエリの新規作成画面で、以下のSQL文を入力して、テスト実行します。

EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE bluegoat.frequenty_update.15min_update AS SELECT event_timestamp,event_name, event_params FROM `bluegoat.analytics_276440588.' || CONCAT('events_intraday_', FORMAT_DATE('%Y%m%d', CURRENT_DATE())) || '` WHERE event_name = "内部リンククリック" LIMIT 100';

 

状況に応じて、2箇所変更する必要があります。

テーブルIDの修正

bluegoat.analytics_276440588
の部分ですが、

1. bluegoat → プロジェクト名
2. analytics_276440588 → analytics_GA4によって作成されているデータセットID

に変更してください。
データセットIDは、GA4からストリーミング送信が成功していれば、自動的に作成されています。

イベント名の修正

WHERE event_name = "内部リンククリック" LIMIT 100';

こちらの"内部リンククリック"についてですが、こちらも取得したいイベント名（GA4で集計されている）を入力してください。
今回は日本語になっていますが、本来イベント名は半角英数字で記載するのが通例です。
（outbound_linkやpage_viewなど）

クエリの意味

それぞれのクエリの意味をざっくりと解説します。

EXECUTE IMMEDIATE

EXECUTE IMMEDIATEは、動的SQLを実行するために使用されるコマンドです。
動的SQLとは、SQL文が実行時に生成または変更されるものを指します。
これは、静的SQL（コードに固定されたSQL文）と対比されます。

また、EXECUTE IMMEDIATEの中で生成されたSQL文の連結には||を使用します。

CREATE OR REPLACE TABLE bluegoat.frequenty_update.15min_update

書き込み（上書き）先のテーブルを

bluegoat
>frequenty_update
>15min_update

に指定しています。

SELECT event_timestamp,event_name, event_params

指定したテーブルから、

• event_timestamp
• event_name
• event_params

のデータを取得することを指定しています。

`bluegoat.analytics_276440588.' || CONCAT('events_intraday_', FORMAT_DATE('%Y%m%d', CURRENT_DATE())

BigQueryにおいて、GA4のデータは

プロジェクト名
>analytics_ID
>events_intraday_YYYYMMDD
という名称のテーブル(events_intraday_YYYYMMDD)に保存されています。

最新のデータを取得するためには、YYYYMMDDが最新のテーブルから、データを取得する必要があります。

FORMAT_DATE('%Y%m%d', CURRENT_DATE())

の部分で、クエリが実行された時点の日付を取得し、SQLに組み込んでいます。

WHERE event_name = "内部リンククリック" LIMIT 100';

イベント名(event_name)が「内部リンククリック」となっているデータのみ抽出し、上限は100行としています。

クエリ画面から、スケジュールボタンをクリック

以下のポップアップが表示されるので「APIを有効にする」をクリック

「スケジュールされたクエリの名前」に
15分ごとにクリックデータを取得
など、わかやすい名前を入力します。

スケジュールオプションにて、

• 繰り返しの頻度＝分
• 繰り返しの間隔＝15

を入力します。

クエリ結果の書き込み先

保存をクリックします。
※「クエリ結果の書き込み先」は、設定する必要はありません。

以上で、定期実行されるクエリの設定は終了です。
15分ほど待ってみて、実行時にエラーが出ないか確認しておきましょう。

Looker Studioでデータを取得

データリソースの追加

ツールバーの「リソース」
→「追加済みデータソースの管理」
→「データソースを追加」をクリック

「BigQuery」を選択し、今回作成したテーブルを選択します。

データの更新頻度の変更

先ほどと同じく
ツールバーの「リソース」
→「追加済みデータソースの管理」
→「データソースを追加」をクリック
より、該当のBigQueryデータの「編集」をクリック

「データの更新頻度」をクリックして

更新頻度を15分に設定します。

データ整理と表示

BigQueryのデータリソースには

• event_name（イベント名）
• event_params.key（パラメーター名）
• event_params.value.string_value（パラメーターの文字列の中身）
• event_timestamp（発生時刻）

などの7つディメンションと

• Record Count（個数）

の1つの指標で構成されています。

これらのデータから、欲しいデータによってデータをフィルタリングし、目的とするテーブルを作成します。

 

たとえば「リンククリックの発生時刻、発生ページ、クリックテキストが早く知りたい」という目的で作成する場合、以下のような表が理想的です。

出力したい表

このテーブルを出力するためには

• 「event_params.key」が「timestamp」のデータ
• 「event_params.key」が「page_location」のデータ
• 「event_params.key」が「click_text_click_url」のデータ

これらのデータをフィルタリングしつつ、テーブル結合する必要があります。

具体的には、以下のようなテーブル結合を行います。

• 2つ目のテーブルのフィルター：「event_params.key」が「timestamp」と一致
• 2つ目のテーブルのフィルター：「event_params.key」が「page_location」と一致
• 3つ目のテーブルのフィルター：「event_params.key」が「click_text_click_url」と一致

※timestampやclick_text_click_urlは、私がよくタグマネージャーを使って取得しているパラメータです。取得には事前に設定しておく必要があります。

以上で、設定は終了です。

だいぶ雑な解説となり恐縮ですが、設定のハードルが高いと感じつつも、どうしても設定したい方は、弊社で設定の代行も行っております。

問い合わせフォームよりご相談ください。