SearchConsoleをBigQueryを連携できるようになったことから、より詳細なデータ分析が可能になった。その一つに「バンプチャート」がある。バンプチャートとは「ランキング数値」を元にした推移グラフのことであるが、現状LookerStudioではランキングの数値計算機能が備わっていない。そのため、元のデータにランキングのカラム（列）を追加することでバンプチャートのビジュアライズが可能となる。
今回作成するビジュアライズはURL単位の流入数（クリック数）をサイトの中で相対ランキングを付け、月単位で推移を把握する。これを「積み上げ棒グラフ」でビジュアライズすることもできるが「積み上げ棒グラフ」では流入数のボリュームは把握することができるものの、相対的なランキング推移が分かりにくいことが挙げられる。反対に「バンプチャート」では流入数のボリュームは分からないが、相対的な流入数のランキング推移が明瞭になる。
このURL単位のバンプチャートを作成することで、他のどのグラフよりもURL単位の相対的な順位の可視化が可能になるため、SEOにおける重要なモニタリング指標として活用できる。文章で解説しても分かりにくいかと思うので、ひとまず完成形のバンプチャートのビジュアライズが以下となる。
[su_lightbox type=”image” src=”
“] [/su_lightbox]
まだSearchConsoleのデータが約3ヶ月分しか蓄積されていないため変動が分かりにくいが、期間が長くなるにつれバンプチャートの優れたビジュアライズ表現が分かるかと思う。
目次
URL別流入数ランキンググラフ作成手順
前提としてSearchConsoleとBigQueryの連携が済んでいること。SQLでやることは年月とURLの組み合わせで「ランキング順位」カラムを追加する。このような分析用のカラムを追加する処理は「ウィンドウ関数」と呼ばれる。ウィンドウ関数を理解するには、テーブルに新しい分析用の列を追加するというイメージをまず持つと、ウィンドウ関数とは何かがすんなりと理解できる。今回の例でいえば、ROW_NUMBER関数を使ってランキング順位を付与し、そのランキングをグラフ化するという流れになる。
SQLコード解説
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | WITH prep1 AS( SELECT FORMAT_TIMESTAMP("%Y%m", data_date) AS date_ym, url, SUM(clicks) AS clicks FROM `データセットID.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 FORMAT_TIMESTAMP("%Y%m", data_date), url ) SELECT parse_date('%Y%m', date_ym) AS date_ym, url, clicks, ROW_NUMBER() OVER ( PARTITION BY date_ym ORDER BY clicks DESC ) AS click_rank FROM prep1 ORDER BY date_ym ASC, clicks DESC |
パラメーター（@DS_START_DATE・@DS_END_DATE）を使用することで、LookerStudioの期間コントロールと連動させる。この行は、LookerStudioのカスタムクエリの中で動作するものなので、BigQueryで動かす際はこの行を削除するか、書き換えること。
参考：カスタムクエリでパラメータを使用する -LookerStudioのヘルプ
ChatGPTによる解説
このSQLクエリは、Google Cloud PlatformのBigQueryを使用してウェブサイトの検索エンジン結果ページ(SERP)のデータからクリック数のランキングを取得するものです。
prep1という一時的な結果セットを定義します。
1．元のデータから、年と月を”%Y%m”形式でフォーマットし、date_ym列として取得します。
・clicksが0より大きい行のみを選択します。
・data_date列が@DS_START_DATE（開始日）と@DS_END_DATE（終了日）の範囲内にある行だけを抽出する条件です。
・date_ymとurlでグループ化し、同じ年月とURLを持つ行をまとめ、そのグループ内のclicksの合計値を計算します。
2．prep1をベースに、結果を計算します。
・date_ym列を”%Y%m”形式の日付に変換し、新しいdate_ym列として取得します。
・url列とclicks列はそのまま取得します。
・同じdate_ym内でclicks列を降順に並べ替え、ランキングを計算します。ランキングはclick_rank列として取得します。
3．最終結果を表示します。
・date_ym列で昇順に並べ替えます（年と月の昇順）。
・同じdate_ym内ではclicks列で降順に並べ替えます（クリック数の降順）。
カスタムクエリからBigQueryのデータに接続
SQLはLookerStudioのカスタムクエリに張り付けてBigQueryからデータの抽出を行う。
[データを追加] > [BigQuery] > [カスタムクエリ] > [該当課金プロジェクト]選択 > [カスタムクエリを入力]
LookerStudio設定解説
メインとなる設定は以下の通り。
設定
・グラフ：時系列グラフ
・ディメンション：[date_ym] （’20230501’形式となっているので、タイプを「年月」に変換）
・内訳ディメンション：url（視認性を上げるために計算フィールドのCASE文で日本語タイトル名に変換する）
1 2 3 | CASE WHEN url = "" THEN "タイトル" END |
・指標：click_rank
・内訳ディメンションの並び替え：click_rank（昇順）
スタイル
・系列
折れ線・ポイントを表示・データラベルを表示
・軸
軸を表示する・Y軸を逆方向にする
・左Y軸
軸の最小値：1
軸の最大値：10
カスタム目盛り間隔：1
SQLが分かると今回のような一歩進んだ分析が可能になります。習得したいよと思った方は以下の書籍がお勧め⇩
作成したSQLをLookerStudioで可視化するスキルを身に着けたい方は⇩
