SearchConsole指標(Clicks)をディメンション化しLookerStudioで「ヒストグラム」作成！BigQueryデータ活用

SearchConsoleのデータをBigQueryに連携できるようになったことから、LookerStudioとSearchConsoleを直接連携しただけでは実現が不可能であったビジュアライズもSQLを活用することでビジュアライズの可視化の幅が広がった。反対に言えば、SearchConsoleと直接連携してビジュアライズが作成できるのであれば、BigQueryと連携する必要性はない。大きな理由としてはBigQueryの分析料金としてLookerStudioで生成させるSQLのデータ量によっては課金対象となるため。

ここではSearchConsoleとLookerStudioを直接連携するだけでは難しいビジュアライズ表現の1つである「ヒストグラム」を、BigQueryに蓄積されたSearchConsoleのデータをSQLで抽出した上で可視化を行う。

また、ここでいう「ヒストグラム」というのは横軸である階級の幅を自由に設定することで、1つ1つの棒グラフの独立性をグルーピングすることで分布の傾向を把握しやすくする意味合いを指す。

参考：一括データエクスポート: Search Console のデータを活用するための優れた新機能 -Google検索セントラル

Contents

1 LookerStudioの「指標」と「ディメンション」の限界
2 SearchConsoleのClicks（クリック数）を横軸とした階級を作成しページのクリック数の散らばり度合をグラフ化する

LookerStudioの「指標」と「ディメンション」の限界

現時点ではSearchConsoleとLookerStudioを直接連携した場合、「ディメンション」を「指標」として扱うことはできるが、その逆の「指標」を「ディメンション」化することはできない。
参考：ディメンションと指標の改善

SearchConsoleのClicks（クリック数）を横軸とした階級を作成しページのクリック数の散らばり度合をグラフ化する

【ビジュアライズの概要】
クリック数をディメンションとして使うために、カスタムクエリでURL毎のクリック数を集計する。階級はLookerStudioでCASE文で作成し、それをディメンションとし指標をURLにする。

文章で説明しても分かりづらいと思うので、完成したグラフをまず提示する。通常のヒストグラムでは階級の幅は均一であるが、ここでは10進数の繰り上がりで階級を切っている。もちろん均一の幅で階級を分けてもよいが、一般的にSEOのページの集客力はページにより大きく偏っているため、10進数で分けた方がSEOの健全性を把握しやすい。例えば月間のPVが10,000あるとして、1記事だけで10,000PVあるのと、100記事で100PVあるのでは、1記事だけに頼っているという意味でリスクが高く、100記事で100PVの方がリスクが分散されているため、同じ10,000PVでもサイトの価値が全く異なる。

この観点は自身のサイト分析でも重要になるが、サイトを購入する場合にも確認しておいた方が良いだろう。集客力がある記事が数記事では、もしその記事がSEOで順位が下落した場合、サイト全体のPVが一気に下がることを意味する。

カスタムクエリでURL毎のクリック数を集計

[データを追加] > [BigQuery] > [カスタムクエリ] > [該当課金プロジェクト]選択 > [カスタムクエリを入力]

URL毎のクリック数を集計する。その際、データ分析量を節約するためクリック数が0回は除外する。抽出期間はLookerStudioの期間コントロールと連動させたいため、パラメーター（@DS_START_DATE・@DS_END_DATE）を使用する。
参考：カスタムクエリでパラメータを使用する -LookerStudioのヘルプ

1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT
Url,SUM(clicks) AS clicks

FROM
`データセット名.searchconsole.searchdata_url_impression*`

WHERE
clicks > 0
AND
data_date BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE) AND PARSE_DATE('%Y%m%d', @DS_END_DATE)

GROUP BY
Url

※[期間パラメーターを有効にする]にチェックをする

計算フィールドで階級（カスタムディメンション）を作成

SEOのクリック数の分布をより把握しやすくするため、10進数の区切りでディメンションを作成する。

1
2
3
4
5
6

CASE
WHEN clicks > 0 AND clicks <= 9 THEN "A 1-9"
WHEN clicks > 9 AND clicks <= 99 THEN "B 10-99"
WHEN clicks > 99 AND clicks <= 999 THEN "C 100-999"
WHEN clicks > 999 AND clicks <= 9999 THEN "D 1000-9999"
END