Google Analytics4の高速データ分析と詳細分析は、BigQueryで決まり
この「所長のBlog」で、「Google Analyticsのデータ(GA4)をBigQueryで分析してみよう」で、Google Analytics(GA4)の分析方法の整理を行いました。Google Analytics(GA4)のデータ計測・分析方法には、大きく3種類あります。
Google Analytics(GA4)のダッシュボードを使う
Google Looker Studioを使う
BigQueryを使う
それぞれの特徴は、「Google Analyticsのデータ(GA4)をBigQueryで分析してみよう」に整理しましたが、以下に該当する人は、BigQueryでの分析が最適です。
GA4データのBigQuery分析:適したサイト特徴
大規模トラフィック
月間数百万PV以上のサイト
データ量が膨大で、標準的なGA4インターフェースでは処理が困難
複雑なユーザーフロー
Eコマース、SaaS、多段階のコンバージョンプロセスを持つサイト
ユーザーの行動パターンが複雑で、詳細な分析が必要
カスタムデータの多用
多数のカスタムイベントやユーザープロパティを使用
標準的なGA4レポートでは十分にカバーできない独自の指標が多い
複数のデータソース統合
CRM、広告プラットフォーム、その他のマーケティングツールとのデータ統合が必要
クロスプラットフォーム分析が求められる
長期的なデータ保持と分析
数年にわたるデータ分析や傾向把握が必要
GA4の標準的なデータ保持期間(最大14ヶ月)では不十分
GA4データのBigQuery分析:適した分析者
データ分析スキル
SQL言語に精通
データモデリングの知識
統計学の基本的な理解
テクニカルスキル
BigQueryの操作に慣れている
データパイプラインの構築経験
プログラミング言語(Python, R等)の知識
ビジネス理解
マーケティング戦略の理解
KPIの設定と評価の経験
データドリブンな意思決定プロセスへの理解
可視化スキル
データの効果的な視覚化能力
Tableau, Power BIなどのBIツールの使用経験
問題解決能力
複雑なビジネス課題をデータ分析で解決する能力
洞察を実践可能な施策に変換する能力
GA4データのBigQuery分析:マーケティングに使うべき理由
セグメンテーションの深化
BigQueryを使用して、より詳細で複雑なユーザーセグメントを作成
行動パターン、購買履歴、デモグラフィックデータを組み合わせた多次元セグメント分析
アトリビューションモデリング
カスタムアトリビューションモデルの構築
マルチタッチポイント分析による、各マーケティングチャネルの貢献度評価
予測分析の活用
機械学習モデルを使用した顧客生涯価値(LTV)の予測
チャーン予測モデルの構築と早期介入戦略の策定
リアルタイムパーソナライゼーション
ユーザーの行動データをリアルタイムで分析し、パーソナライズされたコンテンツや商品推奨を実現
クロスチャネル分析
オンラインとオフラインのデータを統合し、全体的な顧客体験を把握
オムニチャネルマーケティング戦略の最適化
ROI最適化
詳細なコスト分析と収益データの統合による、各マーケティング施策のROI計算
予算配分の最適化と効率的なマーケティング支出の実現
コンテンツ効果測定
コンテンツの詳細な効果分析(閲覧時間、スクロール深度、エンゲージメント率など)
コンテンツマーケティング戦略の改善
カスタムファネル分析
複雑な購買プロセスや顧客旅行のステップごとの詳細分析
コンバージョン率最適化のための具体的な改善ポイントの特定
私の場合は、「セグメンテーションの深化」と、新しい分析手法の開発のために、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を、自分の環境にコピペする前に、皆さんの環境で、調べておく必要があることが、2つあります。それは、Google Analytics(GA4)のデータセットの名前と、サチコンGoogle Search Consoleのデータセットの名前の確認です。
まずは、Google Analytics(GA4)のデータセットの名前の確認方法です。皆さんの、BigQueryのデータの中に、「analytics_***」と「searchconsole_**」というフォルダーがあるはずです。まずは、この「analytics_***」を展開しましょう。
![BiogQueryの中のGA4とサチコンのデータ](https://static.wixstatic.com/media/82864e_b5e236efc2ed4cb88d89e195356a80f6~mv2.jpg/v1/fill/w_720,h_211,al_c,q_80,enc_auto/82864e_b5e236efc2ed4cb88d89e195356a80f6~mv2.jpg)
そして、その中の、「evenets_**」の横の「3点マーク」を押して、「クエリ」を選択します。
![Google Analytics(GA4)のデータセットの名前の確認方法](https://static.wixstatic.com/media/82864e_e9a254f746014c80a4a45dbbcf25f621~mv2.jpg/v1/fill/w_716,h_720,al_c,q_85,enc_auto/82864e_e9a254f746014c80a4a45dbbcf25f621~mv2.jpg)
すると、左のウィンドウに、SQLが表示されます。
![BigQueryのGA4データセットの名前](https://static.wixstatic.com/media/82864e_53e03c60c85c46b3b09fcf6796096908~mv2.jpg/v1/fill/w_720,h_148,al_c,q_80,enc_auto/82864e_53e03c60c85c46b3b09fcf6796096908~mv2.jpg)
この赤い枠で囲まれた、「緑」の文字が、データセットの名前のサンプルになります。
一方、サチコンGoogle Search Consoleのデータセットも同様に確認します。
![BigQueryのサチコンのデータセット](https://static.wixstatic.com/media/82864e_7a8aa99a5123457a83a15ae0b16b9a90~mv2.jpg/v1/fill/w_720,h_335,al_c,q_80,enc_auto/82864e_7a8aa99a5123457a83a15ae0b16b9a90~mv2.jpg)
「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」に格納されているので、このファイルを使って分析します。
![サチコンのデータセットの名前の確認方法](https://static.wixstatic.com/media/82864e_ed5b6660169f456bb202e1dc01bcb100~mv2.jpg/v1/fill/w_702,h_720,al_c,q_85,enc_auto/82864e_ed5b6660169f456bb202e1dc01bcb100~mv2.jpg)
先ほど同様に、search_url_impressions横の3点マークを教えて、「クエリ」を選びます。すると、以下のようなSQLクエリが表示されます。
![サチコン・データセットの名前](https://static.wixstatic.com/media/82864e_d4c4cd9eb80e4256a82166f162ca2825~mv2.jpg/v1/fill/w_720,h_132,al_c,q_80,enc_auto/82864e_d4c4cd9eb80e4256a82166f162ca2825~mv2.jpg)
この赤い枠で囲まれた、「緑」の文字が、サチコンのデータセットの名前のサンプルになります。
SQLをコピペする
次に、無題のクエリを開きます。開き方は、青い[+]マークを押すだけです。
![無題のクエリを開く](https://static.wixstatic.com/media/82864e_b688a64955584e0f817ceaadf6782cf7~mv2.jpg/v1/fill/w_720,h_176,al_c,q_80,enc_auto/82864e_b688a64955584e0f817ceaadf6782cf7~mv2.jpg)
そこに、以下の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
実際に実行すると、
![GA4とサチコンでPage Viewの計測・分析](https://static.wixstatic.com/media/82864e_b8da2d81d08a49f8b3a70bb1a3076550~mv2.jpg/v1/fill/w_720,h_433,al_c,q_80,enc_auto/82864e_b8da2d81d08a49f8b3a70bb1a3076550~mv2.jpg)
このような表示がされるはずです。
せっかくなので、ページ単位の検索単語も表示しよう
では、せっかくなので、ページ単位の検索単語も分析してみましょう。SQLは以下のようになります。同じように、2か所のデータセットの名前は、皆さんのデータセットの名前に変更してくださいね。
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
すると、以下のように、ページ単位の検索単語の分析もできます。
![ページ単位の検索単語分析](https://static.wixstatic.com/media/82864e_37bd77b9ba9c4e348d68e1ec84b94e53~mv2.jpg/v1/fill/w_720,h_413,al_c,q_80,enc_auto/82864e_37bd77b9ba9c4e348d68e1ec84b94e53~mv2.jpg)
結構簡単なのと、何より分析速度が速いですよね。これからも、BigQueryを使っての分析を行っていこうと思います。
もちろん、企業のWebを使ったマーケターの方で、
Google Analytics(GA4)のダッシュボードを使う
Google Looker Studioを使う
BigQueryを使う
の分析を、社内研修や、導入支援を行って欲しい方は、「MSLへのお問い合わせ」から、
お気軽にお問い合わせください。
Comments