ブログ裏側マーケティング

BigQuery×GA4×Search ConsoleでCVまで分析｜Looker Studioで一括可視化してみた

By odawara-blog コメントはまだありません

Webサイトを分析していると、こんなことがあります。

  • Search Consoleでは検索順位やクリック数は分かる
  • GA4ではページビューやユーザー行動が分かる
  • でも、SEOで集客しているページと、CVに関わっているページを一つの表で見たい
  • 毎回GA4やSearch Consoleを行き来するのは面倒

そこで今回は、自分のブログを使って、

Search Console＋GA4 → BigQuery → Looker Studio

という形でデータをまとめてみました。

最終的には、ページURLを軸に、

  • Search Consoleの表示回数
  • Search Consoleのクリック数
  • CTR
  • 平均掲載順位
  • GA4のユーザー数
  • GA4のページビュー
  • CVセッションにおけるページの接点

などを一つの画面で確認できるようにします。

この記事は、自分自身の構築記録も兼ねた備忘録です。

まず、今回できること・できないこと

最初に、ここは整理しておきます。

今回の仕組みでは、「Google検索で『○○』と検索したユーザーが、その後どのページを見てCVしたか」までを直接特定することはできません。

Search ConsoleとGA4では、取得できるデータの粒度や仕組みが異なるためです。

今回できるのは、あくまでページURLを軸にしたデータの統合です。

例えば、

ページGSCクリックGSC表示回数GA4 PVCVセッションでの接点
A記事50010,0001,2005
B記事2005,00060012
C記事502,0003000

といった形で、

「検索流入を獲得しているページ」と「CVに至ったセッションで接点になったページ」

を同じページ軸で比較できます。

つまり、検索クエリ単位のCV分析ではなく、ページ単位でSEOとCVの関係を見るための仕組みです。

なぜBigQueryを使うのか？

GA4やSearch Consoleには、それぞれ便利なレポートがあります。

ただ、それぞれのデータを別々に見るだけでは、

「検索から評価されているページが、サイト内でどのような役割を果たしているのか？」

をまとめて確認しづらいことがあります。

そこでBigQueryを使います。

今回のポイントは、ページURLを共通キーとしてデータを統合することです。

Search Console

  • 検索表示回数
  • クリック数
  • CTR
  • 平均掲載順位

GA4

  • ユーザー数
  • ページビュー
  • CV発生セッション
  • CVセッション内でのページ接点

これらをBigQueryで集計し、Looker Studioに渡します。

今回の構成

全体像はシンプルです。

Search Console
      │
      │ 検索パフォーマンス
      ▼
   BigQuery
      ▲
      │ GA4イベント
      │
      │
     GA4
      │
      ▼
Looker Studio

BigQueryで、

Search Consoleのページデータ

と

GA4のページデータ

をURL単位で結合します。

フェーズ1：まずGSC＋GA4を統合する

最初からCVまで含めた複雑なSQLを作ると、どこで問題が発生しているのか分かりにくくなります。

そこで、まずは基本的なデータ統合から始めます。

ステップ1：GA4のデータを確認

まず、BigQueryにGA4のデータが届いているか確認します。

SELECT 
  event_date,
  event_name
FROM 
  `your-project-id.analytics_xxxxxxxx.events_YYYYMMDD`
LIMIT 100;

ここでは、

  • event_date
  • event_name

など、GA4のイベントデータを確認します。

ステップ2：Search Consoleのデータを確認

次にSearch Consoleのデータを確認します。

SELECT 
  data_date,
  url,
  clicks,
  impressions
FROM 
  `your-project-id.searchconsole.searchdata_url_impression`
LIMIT 100;

今回はURL単位の検索パフォーマンスデータを利用します。

ステップ3：GA4からページURLを取得する

GA4のイベントパラメータには、ページURLなどが入っています。

event_paramsはネストされた構造になっているため、UNNESTを使って取り出します。

SELECT 
  event_date,
  event_name,
  (
    SELECT value.string_value 
    FROM UNNEST(event_params) 
    WHERE key = 'page_location'
  ) AS page_location
FROM 
  `your-project-id.analytics_xxxxxxxx.events_YYYYMMDD`
WHERE 
  event_name = 'page_view'
LIMIT 100;

ここで、GA4からページURLを取得できることを確認します。

ステップ4：URLを正規化する

GA4では、

https://example.com/article/

だけでなく、

https://example.com/article/?utm_source=xxx

のようなURLが記録されることがあります。

このままではSearch Console側のURLと一致しない場合があるため、今回は?以降を削除してURLを正規化します。

REGEXP_REPLACE(
  page_location,
  r'\?.*$',
  ''
)

これによって、

https://example.com/article/?utm_source=xxx

を

https://example.com/article/

として扱います。

※実際のサイトでは、末尾スラッシュ、http/https、wwwの有無なども必要に応じて統一する必要があります。

ステップ5：GSC＋GA4をページ単位で統合する

ここまで確認できたら、実際にデータを統合します。

WITH ga4_data AS (
  SELECT
    REGEXP_REPLACE(
      (
        SELECT value.string_value
        FROM UNNEST(event_params)
        WHERE key = 'page_location'
      ),
      r'\?.*$',
      ''
    ) AS page_url,

    COUNTIF(event_name = 'page_view') AS ga4_pageviews,
    COUNT(DISTINCT user_pseudo_id) AS ga4_users

  FROM
    `your-project-id.analytics_xxxxxxxx.events_*`

  WHERE
    event_name = 'page_view'

  GROUP BY
    page_url
),

gsc_data AS (
  SELECT
    REGEXP_REPLACE(
      url,
      r'\?.*$',
      ''
    ) AS page_url,

    SUM(clicks) AS gsc_clicks,
    SUM(impressions) AS gsc_impressions,
    SAFE_DIVIDE(
      SUM(clicks),
      SUM(impressions)
    ) AS gsc_ctr,
    SAFE_DIVIDE(
      SUM(sum_position),
      SUM(impressions)
    ) AS gsc_avg_position

  FROM
    `your-project-id.searchconsole.searchdata_url_impression`

  GROUP BY
    page_url
)

SELECT
  COALESCE(
    gsc_data.page_url,
    ga4_data.page_url
  ) AS page_url,

  gsc_data.gsc_impressions,
  gsc_data.gsc_clicks,
  gsc_data.gsc_ctr,
  gsc_data.gsc_avg_position,

  ga4_data.ga4_users,
  ga4_data.ga4_pageviews

FROM
  gsc_data

FULL OUTER JOIN
  ga4_data

ON
  gsc_data.page_url = ga4_data.page_url

ORDER BY
  gsc_data.gsc_impressions DESC;

これで、

1ページ＝1行

という形で、GSCとGA4のデータをまとめられます。

ステップ6：BigQueryのVIEWとして保存する

このクエリをBigQueryのVIEWとして保存しておくと、Looker Studioから参照しやすくなります。

例えば、

marketing_bi
└── v_ga4_gsc_page

のような構成です。

Looker Studio側では、このVIEWをデータソースとして利用します。

ステップ7：Looker Studioで可視化する

あとはLooker StudioからVIEWを接続します。

例えば、こんな表にできます。

ページURLGSC表示GSCクリックCTR平均順位GA4ユーザーGA4 PV
/article-a/10,0005005.0%8.24201,200
/article-b/5,0002004.0%11.3180600

これだけでも、

  • 検索表示は多いけどクリックされていないページ
  • 検索から多く流入しているページ
  • PVは多いけど検索流入が少ないページ

などを見つけやすくなります。

フェーズ2：CVデータを追加する

ここからが今回やりたかった部分です。

ページ単位のGSC＋GA4統合ができたら、次にGA4のCVデータを追加します。

ただし、ここでも重要なのが、

「検索クエリとCVを直接結び付ける」のではない

ということです。

見るのは、

CVが発生したセッションの中で、そのページがどのような接点になっていたか

です。

今回は、

  • セッションの入口になったページ
  • セッション内の最後のページ
  • セッション中に閲覧されたページ

という3つの観点から見ていきます。

ステップ8：まずCVイベントを確認する

実際にGA4で利用しているイベント名を確認します。

SELECT 
  event_name,
  COUNT(1) AS event_count
FROM 
  `your-project-id.analytics_xxxxxxxx.events_*`
WHERE 
  event_name LIKE '%CV%'
  OR event_name LIKE '%mcv%'
  OR event_name LIKE '%download%'
  OR event_name LIKE '%seminar%'
GROUP BY 
  event_name
ORDER BY 
  event_count DESC;

ここで確認した実際のイベント名を、後続のSQLに設定します。

ステップ9：CVセッションのページ接点を集計する

ここでは、GA4のセッション情報を利用して、

  • CVが発生したセッション
  • そのセッションの入口ページ
  • 最後に閲覧されたページ
  • セッション中に閲覧されたページ

を整理します。

例えば、あるセッションが、

Google検索
   ↓
記事A
   ↓
記事B
   ↓
サービスページ
   ↓
お問い合わせ

という流れだった場合、

  • 記事A → 入口
  • 記事B → 接点
  • サービスページ → 接点
  • お問い合わせページ → ラスト

という形でページの役割を整理できます。

ここで分かるのは、

「CVセッションにおいて、そのページがどのような接点だったか」

です。

一方で、

「Google検索で『○○』と検索した人が、記事Aを経由してCVした」

という検索クエリ単位の経路までは分かりません。

どんな分析ができるようになる？

今回の仕組みを作ることで、例えば、

① SEOで集客できているページ

Search Consoleの

  • 表示回数
  • クリック数
  • CTR
  • 平均掲載順位

から確認できます。

② 実際に閲覧されているページ

GA4の

  • ユーザー数
  • ページビュー

から確認できます。

③ CVセッションで接点になっているページ

GA4のイベントデータから、

  • 入口ページ
  • ラストページ
  • セッション中の接点ページ

として確認できます。

つまり、

「検索で評価されているページ」

と

「サイト内でCVに関わっているページ」

をページ単位で比較できるようになります。

例えば、こんなページが見つかるかもしれない

例えば、

ページGSCクリックGA4 PVCV接点
A1,0002,0000
B30080015
C5020010

という結果だったとします。

AはSEOによる集客には強い一方で、CVセッションでの接点は少ない。

一方、BやCは検索流入自体は少なくても、CVセッションで閲覧されている。

こうした違いを同じ表で確認できれば、

「アクセスを集める記事」

と

「CVにつながるサイト内導線として機能するページ」

を分けて考えられます。

もちろん、これはページ単位の関連を見るもので、そのページがCVの原因だったことを証明するものではありません。

Looker Studioでダッシュボード化

最終的には、このVIEWをLooker Studioにつないで、

┌──────────────────────────────┐
│        Webサイト分析          │
├──────────────────────────────┤
│ GSCクリック │ GA4 PV │ CV接点 │
├──────────────────────────────┤
│ ページURL                    │
│                              │
│ 表示回数 │ CTR │ 平均順位     │
│ ユーザー │ PV  │ CV接点       │
└──────────────────────────────┘

のように、ページ単位で確認できるようにします。

毎回GA4とSearch Consoleを開いて数字を拾うのではなく、BigQueryでデータを整えて、Looker Studioで見るという形です。

まとめ

今回やったことは、かなりシンプルです。

Search Console
    ↓
検索パフォーマンス
    ↓
       BigQuery
       ↓
  URLをキーに統合
       ↑
       │
      GA4
       ↓
ページ閲覧・CVセッション
       ↓
  Looker Studio

ポイントは、Search Consoleの検索クエリとGA4のCVを直接結び付けたわけではないこと。

今回できるのは、

「どのページが検索流入を獲得しているか」

と

「どのページがCVセッションで接点になっているか」

を、ページURLを軸に一つのダッシュボードで確認することです。

GA4やSearch Consoleを個別に見るだけでは分かりにくかった情報を、BigQueryで一度まとめてしまう。

さらにLooker Studioにつなげれば、毎月のレポート作成やサイトの状態確認にも使いやすくなります。

今回はまず基本的な構成を作りましたが、今後は、

  • CV・MCVの分類
  • 流入チャネル別の分析
  • 新規・リピーター別の分析
  • ページカテゴリー別の分析
  • 月次推移

などにも広げていきたいと思います。

自分のブログを使って、実際にどこまでWebサイト分析を自動化できるか。

これからも少しずつ試していきます。

By odawara-blog

Related Post

ブログ裏側日常

【実例】ブログのアクセスが急増した原因はテレビ放送だった｜サーチコンソールの数値で解説

ブログ裏側小田原移住

「小田原 何がある」で検索1位を狙う！トピッククラスターに挑戦する記録

ブログ裏側

GA4で地域別のアクセスを確認する方法！都道府県・市区町村を調べてみた

odawara-blog

コメントはまだありません

コメントを残す

コメントは管理者の承認後に公開されます。メールアドレスは公開されません。