top of page
検索
執筆者の写真本間　充/マーケティングサイエンスラボ所長
7月29日読了時間: 6分

Google Analytics(GA4)のデータを、Gemini in BigQueryで分析するためのプロンプト・サンプル(2)

Google Analytcs(GA4)のGemini in BigQueryのAIプロンプトをさらに紹介します

前回の所長のBlogの「Google Analytics(GA4)のデータを、Gemini in BigQueryで分析するためのプロンプト・サンプル(1)」が、意外と好評だったので、今回もさらにサンプルのGemini in BigQueryのサンプルを紹介しますね。


アクセスの多いユーザーの閲覧環境を調べよう！

「Google Analytics(GA4)のデータを、Gemini in BigQueryで分析するためのプロンプト・サンプル(1)」で、「セッションの多いユーザーを探そう」で、セッションの多いユーザーを探しみてました。


ところで、このセッションの多いユーザーは、皆さんのWebサイトの味方でしょうか？敵でしょうか？え、と思う方もいると思いますが、同じサイトでセッションが多いユーザー（またが、ロボット）は、いくつかのタイプがあります。


1. ボットやクローラー:

  • 特徴: 短時間に大量のページをアクセスし、セッション時間が短い。アクセス元が特定のIPアドレスやUser-Agent文字列に集中する。

  • 予測: SEOツールやWebサイトの構造を解析するツール、価格比較サイトのクローラーなどが考えられる。

2. 自動化ツール:

  • 特徴: 特定のタスクを自動的に実行するツール（Webスクレイピングツール、価格監視ツールなど）が使用されている場合。アクセスパターンが規則的で、特定のページに集中する傾向がある。

  • 予測: 競合サイトの価格や商品情報を収集している可能性がある。

3. 不正アクセス:

  • 特徴: 攻撃目的で大量のリクエストを送信するDoS攻撃や、不正なログインを試みるケース。アクセス元が特定の国や地域に集中する可能性がある。

  • 予測: セキュリティ対策が不十分なWebサイトを狙った攻撃の可能性がある。

4. 内部ユーザー:

  • 特徴: 社員や開発者がWebサイトのテストやデバッグを行っている場合。アクセス元が社内ネットワークに集中する。

  • 予測: 新機能のテストやバグ修正を行っている可能性がある。

5. 特定のコンテンツへの強い関心を持つユーザー:

  • 特徴: 特定のコンテンツ（ブログ記事、動画、商品ページなど）に強い関心を持つユーザーが、繰り返しアクセスしている場合。セッション時間が長く、特定のページに集中する傾向がある。

  • 予測: コンテンツの内容に非常に興味があり、詳細に確認している可能性がある。


そこで、このユーザーに不自然なところがないのか、ユーザーの視聴環境を調べるこができます。

GA4データのBigQueryでのデータ
GA4データのBigQueryでのデータ

そのGemini in BigQueryのプロンプトを紹介しましょう。ここでは、evenets_**というデータではなく、users_**というデータを使います。


big-query-learning-429123.analytics_404904409.users_* （この部分は、各自書き換えてね）で、user_pseudo_idが、”1615079442.1721700002"（この部分は、各自書き換えてね）のユーザーに対して 
*セッション時間
*device.category
*geo.regionを表示してください。

ソートは、時間順で。
なお、セッション時間は、日本時間で、表示してください。

このようなプロンプトをGeimini in BigQueryに渡してみましょう。

Gemini in BigQueryの画面
Gemini in BigQueryの画面

すると、SQLが生成され、実行すると、以下のような表が出力されます。

ユーザーのWeb閲覧環境の調査データ
ユーザーのWeb閲覧環境の調査データ

今回から、Gemini in BigQueryだけだと、同じ結果を得られないという方もいたので、生成された、SQLも参考に載せておきますね。データベース名前や、user_idなどのパラメータは、皆さんの環境に合わせて、書き換えてくださいね。

SELECT
  event_timestamp,
  TIMESTAMP_MICROS(event_timestamp) AS session_time_JST,
  device.category AS device_category,
  geo.region AS geo_region
FROM
  `big-query-learning-429123.analytics_404904409.events_*` AS events
WHERE
  events.user_pseudo_id = '1615079442.1721700002'
ORDER BY
  session_time_JST;

少し、これでは情報が少ないと思う人もいるでしょう。そこで、Geimini in BigQueryに生成してもらった、SQLに２行だけ加えてみましょう。クエリ結果の上部に、SQLが表示されています。

Gemini in BigQueryが生成したGA4用のSQL
Gemini in BigQueryが生成したGA4用のSQL

このSQLの13行目の下に、下記の２行を加えてみましょう。

  device.operating_system,
  device.web_info.browser,

完成した、SQLは以下のようになります。

Gemini in BigQueryが生成したGA4用のSQLの修正
Gemini in BigQueryが生成したGA4用のSQLの修正

そして、実行すると、以下のように、Webアクセス時のパソコンのOSと、ブラウザーの情報が表示されますね。

ユーザーのWebアクセス環境の表示(2)
ユーザーのWebアクセス環境の表示(2)

このデータを見る限り、このアクセスが、一般的なブラウザからのアクセスでしたね。これが、botなどの場合は、このブラウザーの値が以下のようなものに変わることがあります。

一般的なボット/クローラー:

  • Googlebot

  • Bingbot

  • YandexBot

  • DuckDuckBot

  • AdsBot-Google

  • facebookexternalhit

  • Twitterbot

特定のツール/サービス:

  • Screaming Frog SEO Spider

  • SEMrush Bot

  • AhrefsBot

  • Mozbot

不明なボット/クローラー:

  • Unknown

  • Bot

  • Crawler


ユーザー単位のアクセスをもっと詳細にみたい

さて、今までのWebアクセス分析は、競合調査用のクローラーや、転売ヤーや、あるEC関係の価格調査用のロボットのアクセスを調べるときに有効です。


で、ボットではない「人」のアクセスの場合には、その行動をもっと詳細に調べたくなります。そこで、特定ユーザーのセッションを詳細に取り出すプロンプトを書いてみましょう。


プロンプトは以下のようなものです。

big-query-learning-429123.analytics_404904409.events*（この部分は、各自書き換えてね）に対して、user_pseudo_idが、”1615079442.1721700002"（この部分は、各自書き換えてね）のユーザーに対して、すべてのセッションについて、セッションの時間、ページのlocationを時間順に表示してください。なお、日時は、日本時間で表示してください。

今度は、events_**のデータを使います。AIは日本語の分析を厳密に行うので、「すべてのセッション」という言葉を厳密に理解します。これで、SQLを生成し、実行すると、以下のような結果が表示されます。

Web解析のユーザー動線、セッション単位
Web解析のユーザー動線、セッション単位

セッションの名前、アクセスのあったページも表示されています。なお、この場合も、SQLに手で、表示したい項目を増やすことができます。


実行SQLは以下のようになります。

SELECT
    user_pseudo_id,
    TIMESTAMP_MICROS(event_timestamp) AS event_time_jp,
    event_name,
    param1.value.string_value AS session_id,
    param2.value.int_value AS session_engaged,
    param3.value.string_value AS page_location
  FROM
    `big-query-learning-429123.analytics_404904409.events_*`,
    UNNEST(event_params) AS param1,
    UNNEST(event_params) AS param2,
    UNNEST(event_params) AS param3
  WHERE user_pseudo_id = '1615079442.1721700002'
   AND param1.key = 'ga_session_id'
   AND param2.key = 'engagement_time_msec'
   AND param3.key = 'page_location'
ORDER BY
  event_time_jp;

Gemini in BigQueryを使って、Webアクセス上位３ユーザーの動線を表示しよう

さて、今回の最後に紹介するプロンプトは、やや長文になりますが、以下を試してみましょう。


big-query-learning-429123.analytics_404904409.events*（この部分は、各自書き換えてね）に対して、ユーザーごとに、セッション数とPV数をカウントして、セッション数の多い順に、集計して、その上位三人のuser_psedo_idについて、教えてください。

次にその上位三人のユーザーについて、それぞれ、すべてのセッションについて、セッションの時間、ページのlocationを時間順に表示してください。なお、日時は、日本時間で表示してください。

すると、以下のような結果が出力されます。

Webアクセス上位３ユーザーの動線
Webアクセス上位３ユーザーの動線

SQLも参考に載せておきますね。

WITH UserSessionRank AS (
    SELECT
        user_pseudo_id,
        COUNT(DISTINCT (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) AS session_count,
        SUM(IF(event_name = 'page_view', 1, 0)) AS page_view_count
    FROM
        `big-query-learning-429123.analytics_404904409.events_*`
    GROUP BY
        user_pseudo_id
    ORDER BY
        session_count DESC
    LIMIT 3
),
-- 上記のユーザーのセッション情報を取得
UserSessionDetails AS (
    SELECT
        usr.user_pseudo_id,
        (SELECT value.int_value FROM UNNEST(evt.event_params) WHERE key = 'ga_session_id') AS session_id,
        TIMESTAMP_ADD(TIMESTAMP_MICROS(event_timestamp), INTERVAL 9 HOUR) AS event_time_jst,
        evt.event_name,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location
    FROM
        `big-query-learning-429123.analytics_404904409.events_*` AS evt
    INNER JOIN `UserSessionRank` AS usr ON evt.user_pseudo_id = usr.user_pseudo_id
)
-- セッションごとに情報を集約
SELECT
    user_pseudo_id,
    session_id,
    MIN(event_time_jst) AS session_start_time_jst,
    MAX(event_time_jst) AS session_end_time_jst,
    ARRAY_AGG(STRUCT(event_time_jst, event_name, page_location) ORDER BY event_time_jst ASC) AS session_events
FROM
    `UserSessionDetails`
GROUP BY
    user_pseudo_id,
    session_id
ORDER BY
    user_pseudo_id,
    session_id;

はい、今回は以上です。皆さんも、気軽な気持ちで、Gemini in BigQuery for GA4を使って、便利なWebアクセス分析手法を開発してくださいね。


閲覧数：277回0件のコメント

Comments

bottom of page