見出し画像

GA4 と Google Search Consoleデータをつなげた分析

Dentsu Digital Tech Blog

1.概要

この記事では、Google Analytics 4（GA4）とGoogle Search Console（GSC）のデータを組み合わせて分析する方法や分析に必要なSQLについて記載します。
GA4のデータ、GSCのデータの結合の仕方や可視化、解釈の仕方の一例としてご覧ください。
なお、分析に際してBigQueryを使用しています。
GA4、GSCデータのBigQuery連携方法は以下ページを参考にしてください


2.GA4 と GSC のデータをつなげてわかること

GA4のデータは自社サイト内のパフォーマンス、ユーザー行動を収集しています。
GSCのデータは、自社サイトがGoogleの検索結果として表示されたページ、検索キーワードを収集しています。

大前提として、GA4とGSCのデータは同じユーザーを識別できるデータを持っていません（2024年1月時点）。
そのため、特定の検索キーワードでユーザーがサイトに来訪しコンバージョンしたか、という分析まではできません。

GA4のレポート画面にGSCを連携する方法もありますが、現時点で確認できるデータは限定的です。

ランディングページ単位でのGoogle検索上の表示回数、CTRはわかりますが、ランディングページと検索キーワードを組み合わせた分析はできません。

BigQueryにGA4、GSCのデータを連携することで、ランディングページと検索キーワードを組み合わせた分析が可能です。

分析できる内容の例としては以下のとおりです。

  • 自社サイト内で検索エンジン経由での流入が多い/少ないページと検索エンジン経由のコンバージョンやCVRはどうか

  • 検索エンジン経由での流入が多い/少ないページが、よく検索されているキーワードはなにか

上記分析結果を踏まえると、以下のような施策の検討ができます。

  • 検索エンジン経由の流入が少ない　かつ　CVRが高いページ

    • リスティング広告を活用することで流入を増やし、コンバージョンを増やすことができる

    • リスティング広告に入稿するキーワードは、そのページで実際に流入しているキーワードを参考にする

  • 検索エンジン経由の流入が多い　かつ　CVRが低いページ

    • CVRを改善した際のインパクトが大きい

    • 流入時のキーワードを確認し、キーワードに合わせたコンテンツ改修をすることでCVRを改善できる

分析結果としてはあくまで一例ですが、検索エンジン上での情報とサイト上の情報を組み合わせることで、より解像度が高い施策検討や分析が可能です。

3.集計データの内容について

GA4とGSCのデータをつなげて分析する際のデータの定義は以下のとおりです。
ポイントは、GA4とGSCのデータをページURL（GA4ではランディングページ）で結合する点です。

  • landing_page_location

    • ページのURLを正規化し、小文字化したランディングページのURL。

  • query

    • GSCデータにおけるクエリ。nullの場合は (anonimized) に統一。

  • imp

    • GSCデータにおける表示回数（impressions）の合計。

  • click

    • GSCデータにおけるクリック数（clicks）の合計。

  • click_by_page

    • GSCデータで算出した、ページ単位でのクリック数（clicks）の合計。

  • avg_position

    • 平均表示位置の計算。平均表示位置が小さいほど、検索結果の上位に表示されていることを示す。

  • visit_all

    • GA4データにおける総訪問数。

  • visit_seo

    • GA4データにおける自然検索からの訪問数。

  • visit_cpc

    • GA4データにおける有料クリックからの訪問数。

  • cv_all

    • GA4データにおける全てのコンバージョン数。

  • cv_seo

    • GA4データにおける自然検索からのコンバージョン数。

  • cv_cpc

    • GA4データにおける有料クリックからのコンバージョン数。

query / imp / click / avg_position は GSCデータを元に集計しているため、ページ×キーワード単位での集計結果です。

visit_XXX / cv_xxx / click_by_page はページ（ランディングページ）単位での集計結果です。
そのため、実際の集計結果は以下のようになります。

ページ単位のデータは同じ数字が並び、さらに細かいデータとしてGSC由来のキーワードデータが並びます。

抽出結果

4.実際に作成したダッシュボードとSQL

バブルチャートで可視化をすることで、施策実施重要度の高いページを識別しやすくなります。

  • 横軸

    • 検索エンジン経由での訪問割合

  • 縦軸

    • 検索エンジン経由でのCVR

  • バブルの大きさ

    • ページの訪問回数

バブルはランディングページ単位でプロットし、クリックすることで下に表示されている表データを絞り込めるようにしています。
表データ側ではページ×キーワード単位でのクリックシェアから推定CV数を算出しています。

ダッシュボードイメージ

SQLは以下のとおりです。

with ga4 as ( 
  select 
    landing_page_location 
    , count(distinct ssid) as visit_all 
    , count(distinct case when landing_medium = 'organic' then ssid else null end) as visit_seo 
    , count(distinct case when landing_medium = 'cpc' then ssid else null end) as visit_cpc 
    , count(distinct case when event_name = 'セミナー申し込み完了' then ssid else null end) as cv_all 
    , count(distinct case when event_name = 'セミナー申し込み完了' and landing_medium = 'organic' then ssid else null end) as cv_seo 
    , count(distinct case when event_name = 'セミナー申し込み完了' and landing_medium = 'cpc' then ssid else null end) as cv_cpc 
  from 
  ( 
    select 
      user_pseudo_id 
      , first_value(page_location ignore nulls) over(partition by user_pseudo_id, ga_session_id order by event_timestamp asc) as landing_page_location 
      , first_value(source ignore nulls) over(partition by user_pseudo_id, ga_session_id order by event_timestamp asc) as landing_source 
      , first_value(medium ignore nulls) over(partition by user_pseudo_id, ga_session_id order by event_timestamp asc) as landing_medium 
      , event_name 
      , event_timestamp 
      , concat(user_pseudo_id, '-', ga_session_id) as ssid 
    from 
      ( 
        select 
          event_date 
          , user_pseudo_id 
          , regexp_extract(lower(util.get_value(event_params, 'page_location')), r'^([^\?]+)') as page_location 
          , util.get_value(event_params, 'ga_session_id') as ga_session_id 
          , collected_traffic_source.manual_source as source 
          , collected_traffic_source.manual_medium as medium 
          , event_name 
          , event_timestamp 
        from 
          `project名.analytics_251045496.events_*` 
        where 
          _table_suffix between '20231201' and '20231207' 
      ) 
  ) 
  group by 
    1 
) 
  
, gsc as ( 
  select 
    regexp_extract(lower(url), r'^([^\?]+)') as landing_page_location 
    , ifnull(query, '(anonimized)') as query 
    , sum(impressions) as imp 
    , sum(clicks) as click 
    , ((sum(sum_position) / sum(impressions)) + 1.0) as avg_position 
  from 
    `project名.searchconsole.searchdata_url_impression` 
  where 
    data_date between '2023-12-01' and '2023-12-07' 
    and search_type = 'WEB' 
  group by 
    1, 2 
) 
  
  
select 
  ga4.landing_page_location 
  , gsc.query 
  , gsc.imp 
  , gsc.click 
  , sum(gsc.click) over(partition by gsc.landing_page_location) as click_by_page
  , gsc.avg_position 
  , ga4.visit_all 
  , ga4.visit_seo 
  , ga4.visit_cpc 
  , ga4.cv_all 
  , ga4.cv_seo
  , ga4.cv_cpc 
from 
  ga4 
  left join 
  gsc 
  using(landing_page_location) 
order by 
  landing_page_location 
  , click desc

※util.get_value は自社GCP Project 内で定義しているUDFです。event_paramsから指定された値を取得します。

電通デジタルにはGA4の計測設計からDWHの構築、BI構築まで幅広いスキルを持ったチームがあります。
お困りのことがあれば、お問い合わせください。