【言語分析】BigqueryxSQL:GA4とGSCのLPを組み合わせて、クエリを算出する

このSQLは、Google Analytics 4 (GA4) のデータと Google Search Console (GSC) のデータを組み合わせて、ランディングページごとの集計を行うクエリです。以下は各セクションの詳細な説明です。

1. params テーブル
   期間 (startDate と endDate) とターゲットとなるイベント名 (eventNameComplete) を定義しています。
   今回の例では、2024-07-01 から 2024-08-20 までの期間で、イベント名は purchase に設定されています。

2. ga4 テーブル
   GA4のデータからランディングページごとの訪問数やコンバージョン数を集計しています。
   具体的には、以下の集計を行っています：
   ランディングページごとの全訪問数 (visit_all)
   SEO（オーガニック検索）からの訪問数 (visit_seo)
   CPC（有料広告）からの訪問数 (visit_cpc)
   全体のコンバージョン数 (cv_all)
   SEOからのコンバージョン数 (cv_seo)
   CPCからのコンバージョン数 (cv_cpc)
   セッションごとの最初のランディングページやソース・メディア情報を取得し、それに基づいて訪問とコンバージョンをカウントしています。

3. gsc テーブル
   Google Search Consoleのデータを元に、検索クエリごとの表示回数 (impressions)、クリック数 (click)、および平均順位 (avg_position) を集計しています。
   具体的には、URLごとにクエリごとのデータを集計し、表示回数やクリック数、平均順位を計算しています。

4. 最終結果の集計
   ga4 テーブルと gsc テーブルを landing_page_location 列で結合し、ランディングページごとにデータをまとめています。
   最終結果には以下の情報が含まれます：
   ランディングページ (landing_page_location)
   検索クエリ (query)
   表示回数 (imp)
   クリック数 (click)
   ページごとのクリック合計 (click_by_page)
   平均順位 (avg_position)
   訪問数 (visit_all, visit_seo, visit_cpc)
   コンバージョン数 (cv_all, cv_seo, cv_cpc)
   クリックシェア (click_share)
   調整済みコンバージョンシェア (cv_share_adjusted)
   これにより、検索クエリごとのパフォーマンスと、そのランディングページにおける訪問数やコンバージョン数の関係を分析することができます。
   このクエリの主な目的
   ランディングページごとの訪問数やコンバージョン数と、検索クエリごとのパフォーマンス（表示回数、クリック数、平均順位）の関係を可視化・分析することです。
   SEOとCPCの流入チャネルにおけるコンバージョン貢献度を比較することも目的の一つです。

WITH 
params AS (
  SELECT
    '2024-07-01' AS startDate,
    '2024-08-20' AS endDate,
    'purchase' AS eventNameComplete
),
-- GA4データの集計
ga4 AS (
  SELECT 
    REGEXP_EXTRACT(LOWER(landing_page_location), r'^([^\?]+)') AS 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 = (SELECT eventNameComplete FROM params) THEN ssid ELSE NULL END) AS cv_all,
    COUNT(DISTINCT CASE WHEN event_name = (SELECT eventNameComplete FROM params) AND landing_medium = 'organic' THEN ssid ELSE NULL END) AS cv_seo,
    COUNT(DISTINCT CASE WHEN event_name = (SELECT eventNameComplete FROM params) AND landing_medium = 'cpc' THEN ssid ELSE NULL END) AS cv_cpc
  FROM (
    SELECT 
      user_pseudo_id,
      LOWER(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(
          (SELECT value.string_value 
           FROM UNNEST(event_params) 
           WHERE key = 'page_location')
        ), r'^([^\?]+)') AS page_location,
        (SELECT value.int_value 
         FROM UNNEST(event_params) 
         WHERE key = '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 
        `***************.analytics_366268950.events_*`
      WHERE 
        _table_suffix BETWEEN (SELECT REPLACE(startDate, '-', '') FROM params) AND (SELECT REPLACE(endDate, '-', '') FROM params)
    )
  )
  GROUP BY 
    landing_page_location
),

-- GSCデータの集計
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 
    `**************.searchconsole.searchdata_url_impression`
  WHERE 
    data_date BETWEEN CAST((SELECT startDate FROM params) AS DATE) AND CAST((SELECT endDate FROM params) AS DATE)
    AND search_type = 'WEB'
  GROUP BY 
    landing_page_location, query
)

-- 最終結果の集計
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,
  ROUND(gsc.click / NULLIF(SUM(gsc.click) OVER(PARTITION BY gsc.landing_page_location), 0) * 100, 2) AS click_share,
  ROUND((gsc.click / NULLIF(SUM(gsc.click) OVER(PARTITION BY gsc.landing_page_location), 0)) * ga4.cv_all, 2) AS cv_share_adjusted
FROM 
  ga4
  LEFT JOIN gsc USING (landing_page_location)
ORDER BY 
  landing_page_location, 
  click DESC;