SearchConsoleのデータをBigQueryに連携できるようになったことから、LookerStudioとSearchConsoleを直接連携しただけでは実現が不可能であったビジュアライズもSQLを活用することでビジュアライズの可視化の幅が広がった。反対に言えば、SearchConsoleと直接連携してビジュアライズが作成できるのであれば、BigQueryと連携する必要性はない。大きな理由としてはBigQueryの分析料金としてLookerStudioで生成させるSQLのデータ量によっては課金対象となるため。
ここではSearchConsoleとLookerStudioを直接連携するだけでは難しいビジュアライズ表現の1つである「ヒストグラム」を、BigQueryに蓄積されたSearchConsoleのデータをSQLで抽出した上で可視化を行う。
また、ここでいう「ヒストグラム」というのは横軸である階級の幅を自由に設定することで、1つ1つの棒グラフの独立性をグルーピングすることで分布の傾向を把握しやすくする意味合いを指す。
参考：一括データ エクスポート: Search Console のデータを活用するための優れた新機能 -Google検索セントラル
目次
LookerStudioの「指標」と「ディメンション」の限界
現時点ではSearchConsoleとLookerStudioを直接連携した場合、「ディメンション」を「指標」として扱うことはできるが、その逆の「指標」を「ディメンション」化することはできない。
参考：ディメンションと指標の改善
SearchConsoleのClicks（クリック数）を横軸とした階級を作成しページのクリック数の散らばり度合をグラフ化する
【ビジュアライズの概要】
クリック数をディメンションとして使うために、カスタムクエリでURL毎のクリック数を集計する。階級はLookerStudioでCASE文で作成し、それをディメンションとし指標をURLにする。
“] [/su_lightbox]
文章で説明しても分かりづらいと思うので、完成したグラフをまず提示する。通常のヒストグラムでは階級の幅は均一であるが、ここでは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 |
“] [/su_lightbox]
※[期間パラメーターを有効にする]にチェックをする
計算フィールドで階級（カスタムディメンション）を作成
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 |
縦棒グラフでビジュアライズ
ディメンション：10進数のカスタムディメンション
指標：url
今後WEBマーケティングにはSQLは必須になります。お勧め書籍はこちら
