Contents
導入（このSQLで解決できる課題）
検索流入を最適化するには「どの検索クエリで、どのURLがランディングページになっているか」を知ることが不可欠です。
Google Search Console（以下GSC）でもレポートは確認できますが、以下の課題があります。
GSCのUIでは日付やデバイス条件を細かく絞るのが手間
クエリ×URL単位での粒度が欲しいが、ダウンロードしてExcel処理するしかない
複数サイトや期間を比較する場合に手作業が膨大
そこでこのSQLを使うと、BigQueryにエクスポートされたGSCデータから直接「モバイル検索流入の全体像」を抽出できます。
クエリ×URL単位での表示回数（Impressions）、クリック数（Clicks）、CTR、平均掲載順位を自動算出
クエリパラメータ除去や小文字化によりURLを正規化
「匿名化された検索クエリ」も補足し、分析漏れを防止
これにより「どのページがモバイル検索で強いのか」「どのクエリがCTR改善余地を持っているか」を即座に把握できるようになります。SEO改善やコンテンツ最適化に直結する実務SQLです。
実行結果イメージ（抜粋）
👉 モバイル限定の検索パフォーマンスを、URL×クエリ粒度で俯瞰できる。
「CTRが低いクエリ」や「順位は良いのにクリックされていないURL」を簡単に発見可能。
背景・利用シーン
このSQLは以下のような実務に活用できます。
SEO記事の検索パフォーマンス評価
記事ごとに「どのクエリから来ているか」を可視化でき、狙ったキーワードでの順位とCTRをセットで評価可能。モバイル流入の特定
モバイル限定の集計なので「スマホユーザーに刺さっているか」を明確にできる。CTR改善余地の特定
平均掲載順位が良いのにCTRが低い場合は、タイトルやディスクリプションの改善対象とわかる。匿名化クエリの把握
(anonimized)として補足されるため、クエリ全体のトレンド分析に抜けが出ない。
抽出結果からできる議論（会話例）
SEO担当A: 「この記事、3位にいるのにCTRが5％しかないですね。」
分析担当B: 「meta descriptionを改善すればもっと流入増えそうです。」
SEO担当A: 「このLPはCTRが11％と高いです。」
分析担当B: 「広告コピーと一致しているのが効いてますね。類似キーワードも狙いましょう。」
分析担当B: 「匿名化クエリが2割以上の流入を占めてます。」
SEO担当A: 「カテゴリ単位での需要がありそうなので、包括的な記事を増やしましょう。」
SEO担当A: 「スマホ流入が多いけど、直帰率が高いページがありますね。」
分析担当B: 「モバイルUI改善の対象にすべきです。」
SEO担当B: 「平均掲載順位が7位以下のクエリは要改善です。」
SEO担当A: 「記事構成の見直しや内部リンク強化を検討しましょう。」
SQLコードと解説
WITH
params AS (
SELECT
-- 分析対象期間（例：7/25〜8/12）
'2024-07-25' AS startDate,
'2024-08-12' AS endDate
)
SELECT
-- URLを正規化（クエリパラメータ除去＋小文字化）
REGEXP_EXTRACT(LOWER(url), r'^([^\?]+)') AS landing_page_location,
-- 検索クエリ。NULLは匿名化して(anonimized)に
IFNULL(query, '(anonimized)') AS query,
-- 基本指標
SUM(impressions) AS imp, -- 表示回数
SUM(clicks) AS click, -- クリック数
IF(SUM(impressions) > 0,
SAFE_DIVIDE(SUM(clicks), SUM(impressions)),
0) AS ctr, -- CTR（クリック率）
((SUM(sum_position) / SUM(impressions)) + 1.0) AS avg_position -- 平均掲載順位
FROM
`***********.searchconsole.searchdata_url_impression` -- ★対象のSearch Consoleエクスポートテーブル
WHERE
data_date BETWEEN CAST((SELECT startDate FROM params) AS DATE)
AND CAST((SELECT endDate FROM params) AS DATE)
AND search_type = 'WEB' -- Web検索のみ
AND device = 'MOBILE' -- モバイル限定
GROUP BY
landing_page_location, query
ORDER BY
landing_page_location, click DESC;
params: 分析期間を柔軟に指定できる仕組み
REGEXP_EXTRACT(LOWER(url), …): URL正規化（パラメータ削除＋小文字化）
IFNULL(query, '(anonimized)'): GSCが匿名化する検索クエリを補完
ctr:
clicks / impressionsを安全に計算（ゼロ除算回避）avg_position: GSCの「sum_position」を平均化して順位を算出
確認すべきポイント
日付設定
params内の startDate / endDate を必ず対象期間に合わせること。デバイス条件
device = 'MOBILE'を PC/Tablet に切り替えれば他デバイス分析も可能。URL正規化
現状はクエリパラメータを除去しているが、REGEXP_REPLACEでドメイン削除まで行うとさらに集約しやすい。平均順位の解釈
計算式(sum_position / impressions) + 1.0は順位のオフセット補正。1位が0.0扱いになる場合の補正なので注意。
まとめ（SQLの効果と使いどころ）
CTRが低いクエリは meta 改善対象
平均順位が高いが流入が少ないクエリは流入強化候補
匿名化クエリも漏れなく把握可能
このSQLを使えば、モバイル検索におけるランディングURL×クエリごとの「表示回数・クリック数・CTR・平均順位」を一括で可視化できます。
SEOの改善対象を定量的に特定し、モバイル検索に強いサイト設計に役立ちます。
