📘

ChatGPTを使ってGA4のデータをBigQueryにExportし、Dataformで変換してLooker Studioで可視化する

2023/04/11に公開

Google Analytics

みなさんこんにちは。株式会社INVOXの佐藤です。

LPの検証やマーケティング施策の効果検証など、GAのデータを使って細かいデータ分析を実施したいケースは数多くあると思います。
GA内のレポート機能や、Looker Studio（旧Google Data Portal）にGAを直で繋ぐだけでは実現できなかったり、運用が煩雑になるので、最近ではDataform等で変換処理を行うことが多いと思います。

しかし、データエンジニアリングに精通していないメンバーが、都度データエンジニアに分析依頼を行ってしまうとエンジニア側に負担がかかり、分析のスピード感も出せません。

そこで今回は、データエンジニアリングに関する知識がそこまでない場合を想定して、GA4のデータを使ってDataformで変換処理を行い、Looker Studioで可視化することができるのか、ChatGPT（Model：GPT-4）を使って検証してみたいと思います。

検証のゴールと前提

ゴール
- URL（パラメータを除く）ごとのPV数を日次で可視化する
前提
- ChatGPTに指示された行動のみを行う（コーディングはコピペのみ）
- エラーが出た場合はエラー内容を貼り付け、ChatGPTに修正してもらう

GA4のデータをBigQueryにエクスポート

まずは以下のプロンプトを投げてみました。

GA4のデータをBigQueryにExportする方法をできるだけ具体的に教えてください。

Chat-GPTの返答

Google Analytics 4（GA4）のデータをBigQueryにエクスポートする方法を具体的に説明します。BigQueryとのリンクが完了すれば、GA4データをリアルタイムでBigQueryに送信できます。これにより、カスタムクエリを作成し、より詳細な分析が可能になります。

Google Cloudプロジェクトを作成または選択する
まず、Google Cloudプロジェクトを作成または選択する必要があります。このプロジェクトには、GA4データがエクスポートされるBigQueryデータセットが含まれます。

BigQuery APIを有効化する
プロジェクトでBigQuery APIを有効にしてください。以下の手順で進めます。
a. Google Cloud Console にアクセスし、プロジェクトを選択します。
b. 左上のメニューバーで、[ナビゲーションメニュー] > [APIとサービス] > [ダッシュボード] を選択します。
c. [APIとサービスを有効にする] をクリックし、[BigQuery API] を検索して有効にします。

Google Analytics 4プロパティを設定する
GA4プロパティを設定し、BigQueryエクスポートを有効にします。
a. Google Analytics にアクセスし、GA4プロパティを選択します。
b. 左下のメニューバーで、[管理] をクリックします。
c. [プロパティ設定] > [BigQueryリンク] を選択し、[リンクの設定] をクリックします。
d. Google Cloudプロジェクトを選択し、[続行]をクリックします。

データストリームを選択し、エクスポートを設定する
a. データストリームを選択し、[続行] をクリックします。
b. BigQueryエクスポートの設定をカスタマイズし、[リンク] をクリックします。

データセットを確認する
BigQueryコンソールにアクセスし、新しく作成されたデータセットを確認します。このデータセットには、GA4データがリアルタイムでエクスポートされます。

1.2.はすでに終わっていたので、3.から作業を始めました。

c. [プロパティ設定] > [BigQueryリンク]

ではなく、プロパティ列の下の方に[BigQueryリンク]がありました。（昔はそうだったのかも？）
それ以外の説明もそこまで丁寧ではありませんが、概ね問題なく設定が完了しました。

Dataformで変換

無事にBigQueryへExportできたので、次はDataformで変換処理を行います。

BigQueryにExportされたGA4のデータをGCP上のDataformで変換し、URL（パラメータを除く）ごとのPV数を日次で可視化する方法をできるだけ丁寧に教えてください。

Chat-GPTの返答

Dataformは、Google Cloud上でデータ変換や分析を行うためのデータエンジニアリングプラットフォームです。BigQueryにエクスポートされたGA4データをDataformで変換し、URL（パラメータを除く）ごとのPV数を日次で可視化する方法を以下に説明します。

Dataformプロジェクトを作成
最初に、DataformのWebアプリにアクセスして、新しいプロジェクトを作成します。Googleアカウントでサインインし、「Create new project」をクリックしてプロジェクトを作成してください。

BigQueryをデータソースとして設定
プロジェクトのデータソースとしてBigQueryを設定する必要があります。プロジェクトの「Settings」ページに移動し、「Data warehouse」タブを選択します。次に、「Warehouse type」で「BigQuery」を選択し、必要な認証情報を入力して接続を完了させます。

Dataformでデータ変換用のSQLを作成
以下のクエリを入力し、URL（パラメータを除く）ごとのPV数を日次で集計します。GA4データはyour_project_id.your_dataset_id.your_table_idに置き換えてください。

データ変換を実行する
...

Data Studioを使って可視化する
...

んーやはり情報が古いのか、GCP上で提供されているDataformの回答は得られませんでした。
この後もプロンプトを修正していくつか投げてみましたが、適切な回答は得られず...

ただ書いてあるSQLは使えそうなので、dataformプロジェクトを作成し、daily_pageviews.sqlを作成しました。

daily_pageviews.sql

config {
  type: "table",
  schema: "your_output_schema",
  name: "daily_pageviews"
}

WITH pageviews AS (
  SELECT
    DATE(event_timestamp) as date,
    event_bundle_sequence_id as user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') as page_location
  FROM
    `your_project_id.your_dataset_id.your_table_id`
  WHERE
    event_name = 'page_view'
)

, parsed_pageviews AS (
  SELECT
    date,
    user_pseudo_id,
    REGEXP_EXTRACT(page_location, r'^(https?:\/\/[^\/]+\/[^?]*)(\?|$)') as base_url
  FROM
    pageviews
)

SELECT
  date,
  base_url,
  COUNT(DISTINCT user_pseudo_id) as pageviews
FROM
  parsed_pageviews
GROUP BY
  date,
  base_url
ORDER BY
  date,
  pageviews DESC

DATE(event_timestamp) の箇所でコンパイルエラーが出ていたので、エラー内容をそのまま貼り付け修正を依頼しました。

プロンプト

以下のエラーが発生したので修正してください。

エラー内容
No matching signature for function DATE for argument types: INT64. Supported signatures: DATE(TIMESTAMP, [STRING]); DATE(DATETIME); DATE(INT64, INT64, INT64); DATE(DATE); DATE(STRING) at [3:5].

Chat-GPTの返答

エラーの内容から、event_timestamp を直接 DATE 関数に渡すことができないことがわかります。event_timestamp はマイクロ秒単位の整数で表されるため、まず TIMESTAMP_MICROS 関数でタイムスタンプに変換し、その後 DATE 関数で日付に変換する必要があります。
修正後のコードは以下の通りです。
この修正により、event_timestamp を日付に変換する際のエラーが解消されるはずです。

修正後のコード

daily_pageviews.sql

config {
  type: "table",
  schema: "your_output_schema",
  name: "daily_pageviews"
}

WITH pageviews AS (
  SELECT
    DATE(TIMESTAMP_MICROS(event_timestamp)) as date,
    ...

無事コンパイルは通り、実行できたのですが、正規表現 REGEXP_EXTRACT(page_location, r'^(https?:\/\/[^\/]+\/[^?]*)(\?|$)') as base_url がルートパスをNULL判定してしまうのでこれも修正してもらいました。

プロンプト

この正規表現だと、ルートパスがnullになってしまいます。

Chat-GPTの返答

すみません、その通りです。ルートパスがnullにならないように正規表現を修正します。
修正後のコードは以下の通りです。
この修正では、REGEXP_REPLACE 関数を使って、クエリパラメータ（? から始まる部分）を空文字列に置き換えるようにしています。この変更により、ルートパスがnullになる問題を解消できます。

修正された正規表現

daily_pageviews.sql

config {
  type: "table",
  schema: "your_output_schema",
  name: "daily_pageviews"
}

WITH pageviews AS (
...
)

, parsed_pageviews AS (
  SELECT
    date,
    user_pseudo_id,
    REGEXP_REPLACE(page_location, r'(\?.*)$', '') as base_url
  FROM
    pageviews
)
...

これで想定通りのアウトプットができましたが、SQLの書き方を好みのものに変更しました。

プロンプト

上記のSQLファイルを以下のルールに沿って書き換えてください。

・予約語、関数名は小文字
・カンマは行頭
・インデントは4文字

修正されたファイル

daily_pageviews.sql

config {
    type: "table",
    schema: "your_output_schema",
    name: "daily_pageviews"
}

with pageviews as (
    select
        date(timestamp_micros(event_timestamp)) as date
        , event_bundle_sequence_id as user_pseudo_id
        , (select value.string_value from unnest(event_params) where key = 'page_location') as page_location
    from
        `your_project_id.your_dataset_id.your_table_id`
    where
        event_name = 'page_view'
)

, parsed_pageviews as (
    select
        date
        , user_pseudo_id
        , regexp_replace(page_location, r'(\?.*)$', '') as base_url
    from
        pageviews
)

select
    date
    , base_url
    , count(distinct user_pseudo_id) as pageviews
from
    parsed_pageviews
group by
    date
    , base_url
order by
    date
    , pageviews desc

良さそうです。このコードでも問題なく実行することができました。

Looker Studioで可視化する

最後に、Looker Studioで日次ページビューのデータを可視化してみます。

作成されたdaily_pageviewsのテーブルを以下の条件に沿って可視化する方法をできるだけ丁寧に教えてください。

・日付ごとの縦棒グラフ
・直近1週間のみのデータを表示

Chat-GPTの返答

データ可視化のために、Google Data Studioを使用します。Google Data Studioは、BigQuery上のデータを簡単に可視化することができるツールです。以下の手順で、daily_pageviewsテーブルを元に日付ごとの縦棒グラフを作成し、直近1週間のみのデータを表示します。

Google Data Studioにアクセス
Google Data Studioのウェブサイトにアクセスし、Googleアカウントでログインします。

新しいレポートの作成
Data Studioのホーム画面で、［新しいレポート］ボタンをクリックします。新しいレポートが作成されます。

BigQueryデータソースの追加
新しいレポート画面で、左上にある［データを追加］アイコンをクリックします。データソースの一覧が表示されるので、ここでBigQueryを選択してください。

データソースとしてdaily_pageviewsテーブルを選択
BigQueryの接続画面で、対象のプロジェクト、データセット、そしてdaily_pageviewsテーブルを選択し、［接続］ボタンをクリックします。

データソースをレポートに追加
データソースが選択されると、フィールドのリストが表示されます。ここで［レポートに追加］ボタンをクリックして、データソースをレポートに追加します。

縦棒グラフの作成
レポート画面に戻り、上部のツールバーから縦棒グラフアイコンを選択し、グラフをレポートにドラッグ＆ドロップします。グラフがレポートに追加されるので、適切なサイズにリサイズしてください。

グラフのデータ設定
グラフをクリックして選択し、右側の設定パネルで以下の設定を行います。
ディメンション: dateフィールドを選択します。
指標: pageviewsフィールドを選択します。

直近1週間のデータを表示
グラフを選択した状態で、右側の設定パネルで［フィルタ］タブを選択し、［フィルタを追加］ボタンをクリックします。新しいフィルタを設定するウィンドウが表示されるので、以下の設定を行います。
・フィルタ名: 適切な名前を付けてください（例: "Last 7 days"）
・フィルタのタイプ: 「含む」を選択します。
・フィールド: dateフィールドを選択します。
・演算子: 「相対日付」を選択します。
・相対日付: 「直近N日間」を選択し、Nに「7」を入力します。
設定が完了したら、［完了］ボタンをクリックして、フィルタを適用します。これで、縦棒グラフに直近1週間のデータのみが表示されるようになります。

グラフの見た目を調整（オプション）
必要に応じて、右側の設定パネルで、グラフの見た目を調整できます。例えば、軸のラベル、グリッド線、色、フォントなどを変更して、グラフをより見やすくすることができます。

レポートの保存と共有
レポートが完成したら、左上の［すべての変更を保存］ボタンをクリックして、レポートを保存します。さらに、［共有］ボタンをクリックして、他のユーザーとレポートを共有することができます。
これで、daily_pageviewsテーブルを元にした日付ごとの縦棒グラフを作成し、直近1週間のデータを表示する方法が完了しました。これにより、Google Data Studioを使ってBigQuery上のデータを効果的に可視化できます。

まずLooker StudioのURLが古いのですが、これはうまくリダイレクトされました。
その他、用語が若干違っていたりすることもありましたが、概ね指示通りに作成できました。

一点、8.のフィルタは全くデタラメ（？）を言っており設定ができず、デフォルトの日付範囲で設定を行いました。

まとめ

今回はGA4のデータをBigQueryにExportし、Dataformで変換してLooker Studioで可視化するまでをChtaGPTの指示に従い実行してみました。

所感としては、何の知識もない状態で指示通りに動くだけで完遂するのは難しいですが、他のELTやBIツールを使ったことがある人が入門するのには丁度いいのかなと思います。（Dataformはモデリング等まだまだやるべきことはありますが...）

現時点（2023/04/11）ではこの程度の精度ですが、これからさらに実用的になっていくと思います。

弊社では、GA4以外にも複数ソースを組み合わせたデータ分析基盤の構築や、データ活用に関する支援を行っていますので、興味がある方は以下より気軽にお問い合わせください。

https://in-vox.com/contact

検証のゴールと前提

GA4のデータをBigQueryにエクスポート

Dataformで変換

Looker Studioで可視化する

まとめ

Discussion