はじめに
この記事について
スプレッドシートのHowTo記事です。
「何かをカテゴリわけして、色分けした散布図でカラフルに表示したい」ときに使うグラフの作り方を説明します。
ググってもやり方が出てこなかったため、ここにメモがてら記載しておきます。
完成図
今回は検索ワードをカテゴリわけして、横に表示順位、縦にCTRを置いた散布図を作ってみます。
作業
①ローデータをエクスポート
今回はSearch Analytics For Sheetsを使ってSearchConsoleのデータをエクスポートしましょう。
ここでは後から使うつもりでGroupBy句にQuery（検索ワード）とPage（ランディングページ）を入れています。
エクスポートされた結果はこんな感じになるでしょうか。
（ここはまとめ方に応じます、飛ばしていただいて結構です）
検索ワードをグルーピングするため、ワードを目視でチェックして分類しました。今回はCT数上位100ワードに分類を振ったとします。
あまりにも数が多い場合はランディングページの第一階層を分類として利用するとかでもよいと思います。
②ピボットテーブルでデータを整列
ピボットテーブルを開き、行ラベルに「Position（検索順位）」、列ラベルに「ワード分類」、値に「CTR」を入れてみます。
行・列ともに「合計を表示」のチェックを外すようにしてください。
※追記：今回値にCTRをAVERAGEで入れてしまいましたが、加重平均しないと厳密な結果ではないですね。素直にSUMできるものを例に使うべきでした。
データが、こんな感じに整列されたでしょうか（ぼかしの奥には%に丸めていない少数が並んでいます。）
③あとはグラフを挿入するだけ
②で整列されたデータ全てを選択して、挿入>グラフをクリックして下さい。
始め棒グラフが出るかと思いますが、グラフエディタのデータ>グラフの種類で「散布図」を選択します。
すると、こんなカラフルな散布図が出来上がります。
今回はダミーデータのためまったく傾向は出ていません。
実務において、こんなグラフと一緒に「ブランドワードはCTRが高いですがビッグワードは検索上位でもCTRが低いです！Organic流入を増やすために、ビッグワードのLPになっているページのTDKを見直しましょう！」とかビシっと言えると、それっぽい感じがします。
おわりに
やっぱりスプレッドシートはすごかった
ちょっとややこしいグラフはエクセルでしか作れないのかと思っていましたが、スプレッドシートでも作れるようです。
データ抽出や集計、操作に関してはスプレッドシートの方が長けていると思いますので、今後も細かいデータはできるだけスプレッドシートで扱い、サラッとグラフにまとめていこうと思います。