Zenn
Closed7

BigQuery エクスポートされた Analytics のデータを、生成 AI を活用して簡単に分析したい

Firebase
BigQuery
Gemini
高田 晴彦高田 晴彦

背景

Google のアプリ計測ソリューション「Google Analytics for Firebase」(以下、Analytics と省略します)には、アプリから送信されたイベントを BigQuery にエクスポートする機能がある。それにより、アプリの画面遷移や操作内容について、自由に様々な分析ができる。

課題

BigQuery の SQL は難しい

しかし私は BigQuery エクスポートされた Analytics のデータに対する SQL をスムーズに書くことができない。多くの列があるが、すべてを暗記していない。また時刻の取り扱いも覚えていないので、毎回調べている。また WITH 句などのサブクエリを使う SQL が必要な時は頑張って考えている。

多くのメンバーにデータ分析に参加してほしい

アプリサービスを運用するにあたり、多くのメンバーが思いついた仮説をデータで裏付けた上で、アイディアを提案してほしい。SQL に抵抗がない人、得意な人だけがそれを行うと、その人が退職や異動になってしまうと、データに基づく意思決定をする文化があっても失われてしまう懸念がある。よって Analytics や SQL の基本的な知識のレクチャーだけで、だれでもデータ分析できるチーム体制を作りたい。

関連する発表

DroidKaigi 2024 で、こちらの発表を行いました。
https://2024.droidkaigi.jp/timetable/678050/

このスクラップの予備知識としては
https://www.docswell.com/s/tfandkusu/KG11N8-DroidKaigi2024#p28
の28〜50ページになります。

高田 晴彦高田 晴彦

初期設定を行う

https://cloud.google.com/gemini/docs/bigquery/set-up-gemini

管理者は Gemini in BigQuery の pre-GA 登録フォームに記入する必要があります。

注: SQL コードの生成とデータキャンバスは、すべてのプロジェクトで使用できます。これらの機能を利用するのにアクセスをリクエストする必要はありません。

と書いてあるので、登録フォームには記入しない。

Google Cloud コンソールで [BigQuery] ページに移動します。

鉛筆っぽいマークから「続行」をクリック

Gemini for Google Cloud API の「有効にする」をクリック

権限は（省略可）と書いていたので、いったんそのまま進んでみる。

Gemini で SQL を生成できるようになった。

高田 晴彦高田 晴彦

生成された SQL は間違えている

SELECT
    DATE(TIMESTAMP_MILLIS(event_timestamp)) AS event_date,
    user_pseudo_id,
    device.mobile_model_name
  FROM
    `tfandkusu-ga913.analytics_454428616.events_20240914`
  WHERE DATE(TIMESTAMP_MILLIS(event_timestamp)) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE();

実行するとエラーになる

TIMESTAMP value is out of allowed range: from 0001-01-01 00:00:00.000000+00 to 9999-12-31 23:59:59.999999+00.; while executing the filter on column 'event_timestamp'; Column 'event_timestamp'

日時に関する処理の間違い

  • 欲しいのは日付で無く日時
  • event_timestamp 列はミリ秒でなくマイクロ秒の UNIX 時間

テーブル指定の間違い

BigQuery エクスポートされた Analytics のデータは1日1テーブルなので、テーブル名の 20240914 の部分を * にして、WHERE 句で _TABLE_SUFFIX を使う必要がある。

高田 晴彦高田 晴彦

ChatGPT に SQL を作らせる

使用モデルは GPT-4o

クエリは「Analytics のデータがエクスポートされた BigQuery から「直近1週間の日時、 user_pseudo_id 、機種名の一覧」を見るSQLを作ってください。Google Cloud プロジェクトは tfandkusu-ga913、データセット名は analytics_454428616 です。」

SELECT
  TIMESTAMP_MICROS(event_timestamp) AS event_datetime,
  user_pseudo_id,
  device.mobile_device_info AS device_name
FROM
  `tfandkusu-ga913.analytics_454428616.events_*`
WHERE
  event_date BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
ORDER BY
  event_datetime DESC;

間違いがひとつだけあり、device.mobile_device_info のところを mobile_model_name に変更する必要がある。

あとは正しい。結果も正しく出た。

高田 晴彦高田 晴彦

結論

冒頭に記載した課題は現状の Gemini in BigQuery では解決できない。Google Analytics for Firebase, BigQuery, Gemini はすべて Google の製品なので、正しい SQL を生成してほしかった。

今後の予定

サードパーティー製品または自作で自然言語から BigQuery の SQL を作成して、結果を表示および可視化する手段を探す。Analytics や SQL の基本的な知識のレクチャーだけで、だれでも利用できる提供形態にしたい。

このスクラップは8日前にクローズされました