8月19日読了時間: 7分

GA4とサチコンをBigQueryに取り込んで、Page Viewを計測しよう！

Google Analytics4の高速データ分析と詳細分析は、BigQueryで決まり

この「所長のBlog」で、「Google Analyticsのデータ(GA4)をBigQueryで分析してみよう」で、Google Analytics(GA4)の分析方法の整理を行いました。Google Analytics(GA4)のデータ計測・分析方法には、大きく３種類あります。

Google Analytics(GA4)のダッシュボードを使う
Google Looker Studioを使う
BigQueryを使う

それぞれの特徴は、「Google Analyticsのデータ(GA4)をBigQueryで分析してみよう」に整理しましたが、以下に該当する人は、BigQueryでの分析が最適です。

GA4データのBigQuery分析：適したサイト特徴

大規模トラフィック
1. 月間数百万PV以上のサイト
2. データ量が膨大で、標準的なGA4インターフェースでは処理が困難
複雑なユーザーフロー
1. Eコマース、SaaS、多段階のコンバージョンプロセスを持つサイト
2. ユーザーの行動パターンが複雑で、詳細な分析が必要
カスタムデータの多用
1. 多数のカスタムイベントやユーザープロパティを使用
2. 標準的なGA4レポートでは十分にカバーできない独自の指標が多い
複数のデータソース統合
1. CRM、広告プラットフォーム、その他のマーケティングツールとのデータ統合が必要
2. クロスプラットフォーム分析が求められる
長期的なデータ保持と分析
1. 数年にわたるデータ分析や傾向把握が必要
2. GA4の標準的なデータ保持期間（最大14ヶ月）では不十分

GA4データのBigQuery分析：適した分析者

データ分析スキル
1. SQL言語に精通
2. データモデリングの知識
3. 統計学の基本的な理解
テクニカルスキル
1. BigQueryの操作に慣れている
2. データパイプラインの構築経験
3. プログラミング言語（Python, R等）の知識
ビジネス理解
1. マーケティング戦略の理解
2. KPIの設定と評価の経験
3. データドリブンな意思決定プロセスへの理解
可視化スキル
1. データの効果的な視覚化能力
2. Tableau, Power BIなどのBIツールの使用経験
問題解決能力
1. 複雑なビジネス課題をデータ分析で解決する能力
2. 洞察を実践可能な施策に変換する能力

GA4データのBigQuery分析:マーケティングに使うべき理由

セグメンテーションの深化
1. BigQueryを使用して、より詳細で複雑なユーザーセグメントを作成
2. 行動パターン、購買履歴、デモグラフィックデータを組み合わせた多次元セグメント分析
アトリビューションモデリング
1. カスタムアトリビューションモデルの構築
2. マルチタッチポイント分析による、各マーケティングチャネルの貢献度評価
予測分析の活用
1. 機械学習モデルを使用した顧客生涯価値（LTV）の予測
2. チャーン予測モデルの構築と早期介入戦略の策定
リアルタイムパーソナライゼーション
1. ユーザーの行動データをリアルタイムで分析し、パーソナライズされたコンテンツや商品推奨を実現
クロスチャネル分析
1. オンラインとオフラインのデータを統合し、全体的な顧客体験を把握
2. オムニチャネルマーケティング戦略の最適化
ROI最適化
1. 詳細なコスト分析と収益データの統合による、各マーケティング施策のROI計算
2. 予算配分の最適化と効率的なマーケティング支出の実現
コンテンツ効果測定
1. コンテンツの詳細な効果分析（閲覧時間、スクロール深度、エンゲージメント率など）
2. コンテンツマーケティング戦略の改善
カスタムファネル分析
1. 複雑な購買プロセスや顧客旅行のステップごとの詳細分析
2. コンバージョン率最適化のための具体的な改善ポイントの特定

私の場合は、「セグメンテーションの深化」と、新しい分析手法の開発のために、BigQueryの活用が必須です。何しろ、BigQueryの分析は、「Google Analytics(GA4)のダッシュボード」や「Google Looker Studio」より高速なのです。

GA4とサチコンをBigQueryに取り込んで、Page Viewを表示

まず今回は、前回の「サチコンのデータもBigQueryに取り込もう」で予告したように、

そして、さらに丁寧に、Google Analytics(GA4)の計測データと、サチコンGoogle Search Consoleの計測データを調べると、サチコンGoogle Search Consoleの方に、私たちに馴染みのある、Page Viewが存在しているのです。つまり、「GA4でページビューは計測されなくなった」は、ある意味正しいのですが、「サチコンGoogle Search Consoleのデータからは、ページビューが計測できる」が、さらに正しいことがわかったのです。

Page Viewの表示を行ってみましょう。Gemini in BigQueryというAI機能を使って、SQLを作成しても良いのですが、このようにやることが明確な場合には、SQLをそのまま自分の環境にコピーして実行するのが便利です。

SQLを、自分の環境にコピペする前に、皆さんの環境で、調べておく必要があることが、２つあります。それは、Google Analytics(GA4)のデータセットの名前と、サチコンGoogle Search Consoleのデータセットの名前の確認です。

まずは、Google Analytics(GA4)のデータセットの名前の確認方法です。皆さんの、BigQueryのデータの中に、「analytics_***」と「searchconsole_**」というフォルダーがあるはずです。まずは、この「analytics_***」を展開しましょう。

そして、その中の、「evenets_**」の横の「３点マーク」を押して、「クエリ」を選択します。

すると、左のウィンドウに、SQLが表示されます。

この赤い枠で囲まれた、「緑」の文字が、データセットの名前のサンプルになります。

一方、サチコンGoogle Search Consoleのデータセットも同様に確認します。

「searchconsole_**」というフォルダーには、[_site_]と[_url_]と、ExportLogの3種類のファイルがあります。

ExportLog
- 他の2つのテーブルのデータがいつ、どのようにエクスポートされたかを追跡します。
search_site_impressions
- サイト全体のパフォーマンスを把握するのに適しています。全体的なトレンドや、デバイス別、国別のパフォーマンスを分析するのに役立ちます。
search_url_impressions
- 個別のURLやクエリレベルでの詳細な分析に使用します。特定のページのパフォーマンスや、特定の検索クエリに対するサイトの表示状況を分析するのに適しています。

この中で、search_site_impressionsとsearch_url_impressionsが分析用のデータです。

search_site_impressionsには、以下のデータが格納されています。

date
- データの日付
country
- ユーザーの国
device
- デバイスタイプ（desktop, mobile, tablet）
search_type
- 検索タイプ（web, image, video等）
impressions
- 検索結果ページでの表示回数
clicks
- クリック数
ctr
- クリック率（Click-Through Rate）
position
- 平均掲載順位

一方、search_url_impressionsには、以下のデータです。

date
- データの日付
country
- ユーザーの国
device
- デバイスタイプ（desktop, mobile, tablet）
search_type
- 検索タイプ（web, image, video等）
page
- 特定のURLのパス
query
- 検索クエリ
impressions
- 検索結果ページでの表示回数
clicks
- クリック数
ctr
- クリック率（Click-Through Rate）
position
- 平均掲載順位

間違い探しのようですが、検索クエリは、「search_url_impressions」に格納されているので、このファイルを使って分析します。

先ほど同様に、search_url_impressions横の３点マークを教えて、「クエリ」を選びます。すると、以下のようなSQLクエリが表示されます。

この赤い枠で囲まれた、「緑」の文字が、サチコンのデータセットの名前のサンプルになります。

SQLをコピペする

次に、無題のクエリを開きます。開き方は、青い[+]マークを押すだけです。

そこに、以下のSQLをコピペして、データセットの名前を、皆さんのデータセットの名前に変更するだけです。変える場所を、以下では、赤い文字にしてあります。

WITH ga4_data AS (
  SELECT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_url,
    COUNT(DISTINCT CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'))) AS sessions,
    COUNT(DISTINCT user_pseudo_id) AS users
  FROM
    `big-query-learning-429123.analytics_404904409.events_*` -- replace here 
  WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)) AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    # 過去30日間のデータを出力
    AND NOT REGEXP_CONTAINS((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), r'#')
  GROUP BY
    page_url
),
gsc_data AS (
  SELECT
    url AS page_url,
    SUM(clicks) AS clicks,
    SUM(impressions) AS impressions
  FROM
    `big-query-learning-429123.searchconsole_MSL.searchdata_url_impression` -- replace w/ GSC url table name 
  WHERE
    data_date BETWEEN DATE_TRUNC(CURRENT_DATE(), YEAR) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
    AND search_type = 'WEB'
    AND NOT CONTAINS_SUBSTR(url, '#')
  GROUP BY
    page_url
)
SELECT
  gsc.page_url,
  IFNULL(ga4.sessions, 0) AS `セッション数`,
  IFNULL(ga4.users, 0) AS `ユーザー数`,
#  gsc.clicks,
  gsc.impressions AS `Page Views`
  
FROM
  gsc_data gsc
LEFT JOIN
  ga4_data ga4
ON
  gsc.page_url = ga4.page_url
ORDER BY
#  gsc.clicks DESC
  `Page Views` DESC

実際に実行すると、

このような表示がされるはずです。

せっかくなので、ページ単位の検索単語も表示しよう

では、せっかくなので、ページ単位の検索単語も分析してみましょう。SQLは以下のようになります。同じように、２か所のデータセットの名前は、皆さんのデータセットの名前に変更してくださいね。

WITH ga4_data AS (
  SELECT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_url,
    COUNT(DISTINCT CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'))) AS sessions,
    COUNT(DISTINCT user_pseudo_id) AS users
FROM
    `big-query-learning-429123.analytics_404904409.events_*`
    # Replace the Above
  WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 day)) AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    # 過去30日間のデータを出力

  GROUP BY
    page_url
),
gsc_data AS (
  SELECT
    url AS page_url,
    ARRAY_AGG(STRUCT(query, clicks, impressions)) AS query_data
  FROM (
    SELECT
      url,
      query,
      SUM(clicks) AS clicks,
      SUM(impressions) AS impressions
    FROM
      `big-query-learning-429123.searchconsole_MSL.searchdata_url_impression` 
      # Replace the above
    WHERE
      data_date BETWEEN DATE_TRUNC(CURRENT_DATE(), YEAR) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
      AND search_type = 'WEB'
      AND NOT CONTAINS_SUBSTR(url, '#')
    GROUP BY
      url, query
  )
  GROUP BY
    page_url
)
SELECT
  gsc.page_url,
  query_data,
  IFNULL(ga4.users, 0) AS users,
  IFNULL(ga4.sessions, 0) AS sessions
FROM
  gsc_data gsc
LEFT JOIN
  ga4_data ga4
ON
  gsc.page_url = ga4.page_url
ORDER BY
  gsc.page_url

すると、以下のように、ページ単位の検索単語の分析もできます。

結構簡単なのと、何より分析速度が速いですよね。これからも、BigQueryを使っての分析を行っていこうと思います。

もちろん、企業のWebを使ったマーケターの方で、

Google Analytics(GA4)のダッシュボードを使う
Google Looker Studioを使う
BigQueryを使う

の分析を、社内研修や、導入支援を行って欲しい方は、「MSLへのお問い合わせ」から、

お気軽にお問い合わせください。

マーケティングサイエンスラボ