はじめに
こんにちは、私はデータ分析エンジニアとして色んなデータを触ってますが一番多く使うのはBigQueryです。GAで集めたデータをより自分が見やすい形態に整形する事も多いです。
クライアントからの多様な要望に応じる中で、このデータてどうクエリを書けば良いか悩んだりあまり使わない関数を検索するのに時間がかかることもあります。
また、クエリに不慣れな方々が自分の求めるデータをどうやって抽出すればよいのか悩むこともあるでしょう。そんな時に、生成AIを活用するのは非常に有効な方法だと思います。
GAのイベントデータを分析する際にBigQueryからデータを整形するのであれば同じGoogleの生成AI、Geminiを使うのもおすすめします。ここでの例のクエリはGeminiから生成されています。
1. 生成AIとは
生成AIとは、自然言語を入力として受け取り、プログラムコードやクエリを自動生成する技術です。特に、自然言語処理技術を活用することで、ユーザーの要求を理解し、適切な出力を提供することができます。この技術は、データ分析やデータベースクエリの作成において、特に有用です。
2. BigQueryの基本
BigQueryは、Google Cloudが提供するサーバーレスなデータウェアハウスです。大規模なデータセットを迅速に分析できる特性を持ち、SQLライクなクエリ言語を用いてデータにアクセスします。これにより、ユーザーは複雑なデータ分析を簡単に行うことができます。
3. 生成AIの活用例
1. ユーザー行動と属性を結合して分析する（JOIN）
シナリオ
Webサイトの分析担当者が、特定のマーケティングキャンペーン経由で訪れたユーザーの行動を詳細に分析したいと考えています。キャンペーンIDはイベントデータに含まれますが、ユーザーが何回目の訪問でコンバージョンに至ったかといった情報は、複数のセッションをまたいで分析する必要があります。
プロンプト
「BigQueryで、events_*テーブルを使い、過去30日間にキャンペーンsummer_saleからサイトを訪問したユーザーについて、初回訪問時と2回目以降の訪問時のページビュー数を比較するSQLクエリを生成してください。user_pseudo_idでユーザーを識別し、ga_session_numberで訪問回数を特定してください。」
生成されるクエリ
SELECT
user_pseudo_id,
SUM(CASE WHEN ga_session_number = 1 THEN 1 ELSE 0 END) AS first_session_pageviews,
SUM(CASE WHEN ga_session_number > 1 THEN 1 ELSE 0 END) AS subsequent_session_pageviews
FROM
`your_project.your_dataset.events_*`
WHERE
_table_suffix BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
AND event_name = 'page_view'
AND traffic_source.campaign = 'summer_sale'
GROUP BY
user_pseudo_id;
クエリの説明
このクエリは、events_* テーブルを自己結合することなく、CASE式と集計関数でユーザーの初回訪問とそれ以降の訪問を区別しています。
-
_table_suffix: 日付別のテーブルパーティションを指定することで、過去30日間のデータに絞り込み、クエリの処理量を削減しています。これはBigQueryのベストプラクティスです。 -
traffic_source.campaign: ネストされたフィールドにアクセスし、キャンペーン名でデータをフィルタリングしています。 -
CASE:ga_session_number（GAで自動的に付与されるセッション回数）が1かそうでないかを判定し、それぞれのページビュー数を集計します。 -
SUM:CASE式の結果を合計することで、ユーザーごとの初回訪問時と2回目以降の訪問時のページビュー数を算出します。
クエリの結果例
| user_pseudo_id | first_session_pageviews | subsequent_session_pageviews |
|---|---|---|
| 12345abc | 5 | 8 |
| 67890def | 3 | 0 |
| 13579ghi | 7 | 12 |
2. イベントを行から列に変換する（PIVOT）
シナリオ
Webサイトのコンバージョン率を分析する際、ユーザーが特定のページを訪れた後にどのようなコンバージョンイベント（例：add_to_cart、purchase）を発生させたかを知りたいと考えています。それぞれのイベントを別々の列に表示することで、一目で比較できるようにしたいです。
プロンプト
「BigQueryで、過去7日間のevents_*テーブルのデータを使って、event_nameを行から列にピボットするSQLクエリを生成してください。表示するイベントはpage_view、add_to_cart、purchaseの3つとし、ユーザーIDごとに各イベントの発生回数をカウントしてください。」
生成されるクエリ
SELECT
user_pseudo_id,
SUM(CASE WHEN event_name = 'page_view' THEN 1 ELSE 0 END) AS page_views,
SUM(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS add_to_carts,
SUM(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS purchases
FROM
`your_project.your_dataset.events_*`
WHERE
_table_suffix BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY
user_pseudo_id;
クエリの説明
このクエリは、CASE式とSUMを組み合わせてイベントのピボット（行から列への変換）を実現しています。
-
_table_suffix: 過去7日間のデータに絞り込みます。 -
CASE:event_nameが指定したイベント名と一致する場合、1を返し、そうでない場合は0を返します。 -
SUM:CASE式の結果を合計することで、ユーザーごとの各イベントの発生回数をカウントします。これにより、行にユーザー、列にイベント名が並んだレポートが作成できます。
クエリの結果例
| user_pseudo_id | page_views | add_to_carts | purchases |
|---|---|---|---|
| 12345abc | 15 | 2 | 1 |
| 67890def | 8 | 1 | 0 |
| 13579ghi | 20 | 5 | 2 |
3. ウィンドウ関数で順位付けする（ROW_NUMBER）
シナリオ
サイトを訪問したユーザーの中から、最もエンゲージメントの高いセッション（イベント数の多いセッション）を特定したいと考えています。各ユーザーのセッションをイベント数で順位付けすることで、トップセッションを抽出します。
プロンプト
「BigQueryで、events_*テーブルを使い、ユーザーごとに最もイベント数の多いセッションを抽出するSQLクエリを生成してください。user_pseudo_idとga_session_idでセッションを識別し、ウィンドウ関数を使ってセッションごとのイベント数をカウントして順位付けしてください。」
生成されるクエリ
SELECT
user_pseudo_id,
ga_session_id,
event_count
FROM
(
SELECT
user_pseudo_id,
ga_session_id,
COUNT(event_name) AS event_count,
ROW_NUMBER() OVER(PARTITION BY user_pseudo_id ORDER BY COUNT(event_name) DESC) AS rank
FROM
`your_project.your_dataset.events_*`
GROUP BY
user_pseudo_id,
ga_session_id
)
WHERE
rank = 1;
クエリの説明
このクエリは、ウィンドウ関数であるROW_NUMBER()を使って、ユーザーごとのセッションに順位を付けています。
-
内側の
SELECT: まず、GROUP BY user_pseudo_id, ga_session_idでユーザーとセッションごとにイベント数を集計します。 -
ROW_NUMBER():PARTITION BY user_pseudo_idでユーザーごとにデータを区切り、ORDER BY COUNT(event_name) DESCでイベント数が多い順に順位を付けます。 -
外側の
SELECT: サブクエリの結果に対してWHERE rank = 1という条件を適用し、各ユーザーのトップセッション（最もイベント数の多いセッション）のみを抽出します。
クエリの結果例
| user_pseudo_id | ga_session_id | event_count |
|---|---|---|
| 12345abc | 1699042500 | 45 |
| 67890def | 1699042800 | 25 |
| 13579ghi | 1699043100 | 62 |
4. 生成AIの利点
- 迅速なクエリ生成: 複雑なピボットクエリやJOINクエリを迅速に生成できるため、分析時間を短縮できます。
- エラーの軽減: 自動生成されたクエリは、文法エラーの可能性を減らします。
- 学習の助け: 生成されたクエリを参考にすることで、SQLの学習にも役立ちます。
5. 注意点
- 生成されたクエリは必ずしも最適とは限らないので、生成されたクエリをレビューすることが重要です。
- データの構造やビジネスロジックを理解した上でクエリを調整することが必要です。
- プロンプトの書き方に制限はないので望む結果になるまでいろいろ試す必要があります。
6. まとめ
生成AIを活用することで、BigQueryでのデータ分析がより効率的に行えます。クエリ作成の時間を短縮し、エラーを減らすことで、データ分析の質を向上させることが可能です。生成AIを活用して、ぜひ自分自身のプロジェクトに役立ててみてください。
生成AIはどれを使っても良いですが、BigQueryのクエリを作るならBigQueryの関数や機能を使うのにGeminiの方がより正確な結果が出るので参考してください。