2023年2月末に、Google サーチコンソール（以降、GSC）のデータががBigQueryにエクスポートできるようになりました。その手順については、動画で解説 Googleサーチコンソールの「一括データエクスポート」のBigQuery側の設定という記事にまとめていますので、まだエクスポート設定をしていない方は、ぜひ、ご覧ください。
この記事では、GSCからエクスポートされたBigQuery上のデータを使って「GSCに新しく登場した検索クエリ」を可視化する方法を解説します。自社サイトをオウンドメディアとして利用し、ブログを掲載している企業や個人は多いと思います。記事は普通、どんどん増えいきますので、理論上は、自社サイトが検索エンジン結果ページ（Search Engine Result Pages、SERP）に表示される検索クエリの種類数も増えていくと思います。
すると、「新規に（表示を、あるいはクリックを）獲得できた検索クエリ」を知りたくなるのが人情ですし、ブログの著者に「あなたの記事のお陰で、こんな新しいクエリで表示が、あるいはクリックが獲得できました」とフィードバックできれば、ブログを執筆するモチベーションにもなるでしょう。
ところが、GSCのオリジナルの検索パフォーマンスのレポートでは、そうした「一定の期間に新規に獲得した検索クエリ」を取得する機能はありません。そこで、BigQuery上のGSCデータを利用してそれらを可視化する手法を紹介します。
「新規獲得検索クエリ」ダッシュボード
せっかくBigQueryで新規に獲得した検索クエリのリストを取得するのですから、それをLooker Studioでダッシュボード化してみました。こんなダッシュボードを作りたいな。と思ったら、本記事を読み進めてSQL文も確認してみてください。
このダッシュボードでは、「ターゲット日」に知りたい日を入力すると、過去30日間には一度もGSCに登場しておらず、ターゲット日に初めて登場した検索クエリがリストされます。また、「日の遡及」に30よりも大きな数字を入力すると、過去30日間というデフォルトの期間をもっと伸ばすことができます。以下は、本ブログサイトについて、2024年1月1日から1月30日には登場しておらず、2024年1月31日初めて登場したクエリ群です。（本当の本当に初出。を調べたければ、GSCデータがBigQueryにエクスポートされ始めた日までの遡及が必要になります。BigQueryのコンピューティング費用がかかるので、日和って、過去30日に・・としてます 汗）
なかなか面白いですね。
上記のダッシュボードのデータソース（SQL文）
上記のダッシュボードは、GSCからBigQueryにエクスポートされたテーブルを対象として、以下のカスタムSQLを記述したものをデータソースとしています。
with gsc_full as (
select distinct query
from
`kazkida-com-stats.searchconsole.searchdata_url_impression`
where
data_date between date_add(date(@target_date), interval -1*@retroaction_days day)
and @target_date
and search_type = 'WEB'
and query is not null
) ,gsc_sub as (
select distinct query
from
`kazkida-com-stats.searchconsole.searchdata_url_impression`
where
data_date between date_add(date(@target_date), interval -1*@retroaction_days day)
and date_add(date(@target_date), interval -1 day)
and search_type = 'WEB'
and query is not null
)
select
query
, url
, sum(impressions) as imp
, sum(clicks) as click
, ((sum(sum_position) / sum(impressions)) + 1.0) as avg_position
from
`kazkida-com-stats.searchconsole.searchdata_url_impression`
where
data_date between date_add(date(@target_date), interval -1*@retroaction_days day)
and @target_date
and search_type = 'WEB'
and query in (select query from gsc_full except distinct select query from gsc_sub)
group by query, url
ただし、上記のSQL文をBigQueryのコンソールにあるクエリエディタに投入しても、そのままでは動かないです。Looker Studioで作成した「パラメータ」を差し込んだ、パラメタイズドクエリになっているからです。パラメタイズドクエリについてもう少し深く知りたい方は、こちらのブログ記事もご参照ください。BigQueryのパラメタイズドクエリとTableauでGA4の任意のユーザーセグメントを対比して可視化する
パラメータを使ってLooker Studioで可視化する前に、まず、（パラメータを使わず）BigQuery上でどんな結果が帰ってくるか、確認したい場合には、上記のSQL文に以下の3箇所の修正を加えてください。1月31日に、過去30日間で初めて登場した検索クエリをリストする例です。
- date_add(date(@target_date), interval -1*@retroaction_days day) → ‘2024-01-01’
- @target_day → ’2024-01-31′
- date_add(date(@target_date), interval -1 day) → ‘2024-01-30’
SQL文は何をしているのか？
SQLを勉強中の方に、上記のSQL文で何をしているのかを簡単に解説します。
WITH句で作成している最初の仮想テーブル、gsc_fullは、対象とする期間の(例えば、1月1日～1月31日）重複のない検索クエリのリストを取得しています。二番目の仮想テーブル、gsc_subは、対象とする期間のうち、最後の1日(1月31日）だけを含まない(1月1日～1月30日の期間の）重複のない検索クエリのリストを取得しています。
本体のSQL文では、検索クエリと、URLごとに、impの合計、clickの合計、sum_positionの合計をimpの合計で割った、avg_positionを求めていますが、対象となるクエリを、gsc_fullのリストに含まれているものから、gsc_subのリストに含まれているものを差し引いて、それでも残ったもの。に絞り込んでいます。
この太字の部分は、SQLの言葉で言うと「テーブルの集合演算」をしている。ということになります。SQLというと、数値の集計や文字列の加工などをする言語だという認識が一般的と思いますが、テーブルを集合と見立て、それらのテーブル同士の、足し算(UNION)、掛け算(INTERSECT)、引き算(EXCEPT)などをすることもできるんですね。そのうち、今回は引き算(EXCEPT)を利用しています。
テーブルの集合演算の機能はGA4のユーザー分析や、CRM分析で、「●●はしたけれど、▲▲はしていないユーザー」のリストなどを取得することにも利用できる、非常に強力なものです。
宣伝
（ここから宣伝です）この記事をここまで読んでいただいた方は、もれなく、BigQuery(SQL)って、やっぱりできた方がいいな！！と思っていただいたんじゃないかと思います。
そういえば、SQLについて、学習用の良い動画講座があるんですよ。たくさんのレビューを頂き、5点満点中4.6点と、お陰様で評判も良いようです。よろしければどうぞ。（画像をクリックするとUdemy.comにジャンプします。）
え？SQLは本で勉強したい？なるほど、では、こちらをどうぞ。
