URL別流入数ランキンググラフ！LookerStudioでバンプチャートを可視化【BigQueryデータ活用】

SearchConsoleをBigQueryを連携できるようになったことから、より詳細なデータ分析が可能になった。その一つに「バンプチャート」がある。バンプチャートとは「ランキング数値」を元にした推移グラフのことであるが、現状LookerStudioではランキングの数値計算機能が備わっていない。そのため、元のデータにランキングのカラム（列）を追加することでバンプチャートのビジュアライズが可能となる。

今回作成するビジュアライズはURL単位の流入数（クリック数）をサイトの中で相対ランキングを付け、月単位で推移を把握する。これを「積み上げ棒グラフ」でビジュアライズすることもできるが「積み上げ棒グラフ」では流入数のボリュームは把握することができるものの、相対的なランキング推移が分かりにくいことが挙げられる。反対に「バンプチャート」では流入数のボリュームは分からないが、相対的な流入数のランキング推移が明瞭になる。

このURL単位のバンプチャートを作成することで、他のどのグラフよりもURL単位の相対的な順位の可視化が可能になるため、SEOにおける重要なモニタリング指標として活用できる。文章で解説しても分かりにくいかと思うので、ひとまず完成形のバンプチャートのビジュアライズが以下となる。

まだSearchConsoleのデータが約3ヶ月分しか蓄積されていないため変動が分かりにくいが、期間が長くなるにつれバンプチャートの優れたビジュアライズ表現が分かるかと思う。

Contents

1 URL別流入数ランキンググラフ作成手順
- 1.1 SQLコード解説
- 1.2 LookerStudio設定解説

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列で降順に並べ替えます（クリック数の降順）。