【言語分析】BigqueryxSQL:GSCによる対象のURLとクエリを一挙に抽出

セイさん


このSQLクエリは、Google Search Console (GSC) のデータをBigQueryで集計し、特定の条件に基づいてウェブページのパフォーマンスデータ（インプレッション、クリック数、CTR、平均掲載順位）を抽出するためのものです。具体的には、デバイスが「MOBILE」に限定されたデータを集計しています。


具体的な説明

  1. WITH params AS (...)

    • クエリの冒頭で params という共通テーブル式（CTE）を定義しています。このセクションでは、開始日（startDate）と終了日（endDate）を設定しています。

    • これはクエリ内で一貫して使用される日付フィルタリングの基準となります。これにより、柔軟に期間を変更することが可能です。

  2. SELECT ...

    • SELECT 文では、抽出したいデータの列を定義しています。

    • REGEXP_EXTRACT(LOWER(url), r'^([^\?]+)') AS landing_page_location

      • url列からパラメータを除いた純粋なページURLを抽出しています。この正規表現は、?より前の部分のみを抽出するために使用されます。

    • IFNULL(query, '(anonimized)') AS query

      • query 列が NULL の場合、'(anonimized)' というプレースホルダー文字列を表示します。これは、クエリが匿名化されている場合に役立ちます。

    • SUM(impressions) AS imp

      • ページに対するインプレッション（表示回数）の合計を算出します。

    • SUM(clicks) AS click

      • ページに対するクリック数の合計を算出します。

    • IF(SUM(impressions) > 0, SAFE_DIVIDE(SUM(clicks), SUM(impressions)), 0) AS ctr

      • CTR（Click-Through Rate: クリック率）を計算します。SAFE_DIVIDE 関数を使用してゼロ除算を防いでいます。もしインプレッション数が0の場合は、CTRとして0を返します。

    • ((SUM(sum_position) / SUM(impressions)) + 1.0) AS avg_position

      • ページの平均掲載順位を計算しています。この値は sum_position の合計をインプレッション数で割った結果に1を加えたもので、GSCの掲載順位の平均を求めます。

  3. FROM ...

    • FROM句では、ai-wood.searchconsole.searchdata_url_impression テーブルからデータを取得します。このテーブルには、Google Search Console から取得された検索データが格納されていると仮定しています。

  4. WHERE ...

    • data_date BETWEEN CAST((SELECT startDate FROM params) AS DATE) AND CAST((SELECT endDate FROM params) AS DATE)

      • paramsで定義された日付範囲に基づき、データの日付をフィルタリングします。この部分で指定された期間のデータのみが対象となります。

    • AND search_type = 'WEB'

      • 検索の種類を「WEB」に限定しています。これにより、ウェブ検索に関連するデータのみが対象となります。

    • AND device = 'MOBILE'

      • デバイスを「MOBILE」にフィルタリングしています。この条件により、モバイルデバイスに関連するデータのみを抽出します。

  5. GROUP BY ...

    • データを landing_page_location と query 列でグループ化し、ページURLと検索クエリごとの集計データを取得します。

  6. ORDER BY ...

    • 最後に、クリック数（click）の降順で結果をソートし、クリック数が多いページから順に表示します。

このクエリが提供する結果

このクエリの実行結果として、以下のデータが返されます。

  • landing_page_location: URLパラメータを除いた純粋なページのURL

  • query: 検索クエリ（匿名化されたクエリは'(anonimized)'として表示）

  • imp: ページが検索結果に表示された回数（インプレッション）

  • click: ページがクリックされた回数

  • ctr: クリック率 (CTR)

  • avg_position: ページの平均掲載順位

これらのデータはすべて「モバイル」デバイスからのアクセスに限定されています。

WITH 
params AS (
  SELECT
    -- ここに開始日を入力してください (例: 'YYYY-MM-DD')
    '2024-07-25' AS startDate,
    -- ここに終了日を入力してください (例: 'YYYY-MM-DD')
    '2024-08-12' AS endDate
)

SELECT 
  -- URLを抽出するための正規表現処理。URLが別のフィールドで保持されている場合は、そのフィールドに変更してください。
  REGEXP_EXTRACT(LOWER(url), r'^([^\?]+)') AS landing_page_location,
  
  -- クエリがNULLの場合に匿名化する処理。queryが異なるフィールド名である場合は変更してください。
  IFNULL(query, '(anonimized)') AS query,
  
  -- インプレッション数の合計
  SUM(impressions) AS imp,
  
  -- クリック数の合計
  SUM(clicks) AS click,
  
  -- CTR (クリック率) の計算。インプレッションが0の場合は0を返します。
  IF(SUM(impressions) > 0, SAFE_DIVIDE(SUM(clicks), SUM(impressions)), 0) AS ctr,
  
  -- 平均掲載順位の計算
  ((SUM(sum_position) / SUM(impressions)) + 1.0) AS avg_position
FROM 
  -- ここに正しいテーブル名を入力してください (例: `project.dataset.table`)
  `***********.searchconsole.searchdata_url_impression`
WHERE 
  -- 日付フィルターの適用
  data_date BETWEEN CAST((SELECT startDate FROM params) AS DATE) AND CAST((SELECT endDate FROM params) AS DATE)
  
  -- 検索タイプがWEBの場合にフィルタリング (必要に応じて変更)
  AND search_type = 'WEB'
  
  -- デバイスをMOBILEにフィルタリング (デバイス名が異なる場合は変更)
  AND device = 'MOBILE'
GROUP BY 
  landing_page_location, query
ORDER BY 
  landing_page_location, 
  click DESC;

いいなと思ったら応援しよう！