Excel使用歴約20年の杉山貴隆です。
SEOコンサルタントのSEOおたくさんがYouTubeとブログで「CTRに課題のあるページを特定する方法」をレクチャーされています。
Googleサーチコンソールで入手したデータをもとに想定CTRを計算し、想定CTRを下回っているページに改善施策を打っていこうという内容です。私も見させていただき非常に勉強になりました。
さてレクチャー内ではGoogleスプレッドシートが使われていますが、スプレッドシートではなくExcelを使いたい場合もあると思います（たとえば会社によってはクラウドの利用が制限されていたりするので）。
そこでこの記事ではSEOおたくさんの「CTRに課題のあるページを特定する方法」をExcelで実施する手順について解説します。ぜひ参考にしてみてください。
「CTRに課題のあるページの特定」をExcelで実施する方法
次の手順で「CTRに課題のあるページを特定する方法」をExcelで実施できます。
Google Search Consoleの「検索パフォーマンス」で「エクスポート」をクリックし「Excelとしてダウンロード」をクリックします。データがダウンロードされます。
Excelのファイルを開きます。
D列とE列を入れ替えます。※上の画像は入れ替え後
（この作業は必須ではありませんが、Excelの場合はやっておくのがおすすめです。散布図の縦軸・横軸のデータ入れ替え作業がスプレッドシートほど簡単ではないので）
表示回数でデータを絞り込むためA列～E列にフィルタを設定します。
「▼」→「数値フィルター」→「指定の値以上」の順にクリックし、
「20」などの数値を指定して「OK」を押すと絞り込まれます。
D列・E列を選択した状態で「挿入」→「グラフ」グループの散布図のアイコン→散布図のパネルの順にクリックします。散布図が挿入されます。
散布図を選択した状態で「＋」をクリックし、「近似曲線」の右の三角をクリックし、「その他のオプション」をクリックします。ウィンドウ右側に「近似曲線の書式設定」が現れます。
「近似曲線の書式設定」にて「対数近似」を選択し、「グラフに数式を表示する」にチェックを入れます。散布図内に近似曲線と数式が表示されます。
必要であれば「塗りつぶしと線」をクリックして線の色や幅を変更します。
近似曲線の数式が下のほうに小さく表示されているので、見やすくするため上にもってきてフォントサイズを大きくします。
F列に想定CTRを求める数式を書き入れます。たとえば近似曲線の数式が
y=-0.145ln(x)+0.3611
であれば、セルには
=-0.145*LN(D2)+0.3611
と書けばOKです。
オートフィルで一番下の行まで埋めます。
F列を選択して右クリックして「セルの書式設定」をクリックします。
「表示形式」タブで「パーセンテージ」を選択し、小数点以下の桁数として2を指定してOKを押します。CTRがパーセント表示になります。
G列に改善要否を判定するif関数を書き入れます。たとえば、
=if(E2>=F2,"問題なし","改善余地あり")
という具合に入力すればOKです。
オートフィルで一番下の行まで埋めて、完了です。
上記の手順を実行すると、どのクエリでCTRに課題がありそうかが見えてきます。
CTR改善の具体的な方法はSEOおたくさんのCTRのブログ記事に戻って「5. クリック率（CTR）を改善する方法」以下を読みましょう。
そもそもこの散布図と近似曲線はどういう意味？
そもそも今回作った散布図は何なの？ 近似曲線はどう解釈すればいいの？と疑問を持つ人もいると思いますので解説します（ただし私は統計学やSEOの専門家ではないので「ざっくり言うとこのような意味合い」程度に受け止めてください）。
はじめに散布図がどうやって作られるのかというところから。たとえば「通信講座 おすすめ」というクエリの掲載順位が5位でCTRが30％だとすると次の位置に点を打つ、というのはわかると思います。
同様にデータ内のすべてのクエリについて点を打ち、できあがったのが散布図です。
たくさんの点が打たれていますがそれらはバラバラに散らばっているようには見えません。明らかに左下に偏っています。そこで特殊な計算をすると「たくさんの点の代表的な位置を示す線」を描くことができます。それが「近似曲線」です。
散布図内の近似曲線（橙色の点線）に注目すると、例えば横軸が5のところの縦軸の値は13くらいになっています。たくさん点があるけれど横軸が5のときは縦軸の代表的な値は13ということです。
これを平たく言い換えると、手持ちのデータでは掲載順位が5位のときCTRは平均で13％になるということであり、この平均的なCTRをここでは想定CTRと呼んでいます。要するに今見えている近似曲線は各順位における想定CTRを示しているのです。
以上を踏まえて、特定のクエリ（たとえば「通信講座 おすすめ」）に対応する点と近似曲線との関係は次のように解釈できます。
もし「通信講座 おすすめ」というクエリに対応する点が近似曲線にピッタリ重なっている位置にあれば、「通信講座 おすすめ」を検索することで検索結果に表示される自サイトのページが平均的なCTR（＝想定CTR）をとれていることを示しています。
この場合「通信講座 おすすめ」は特に問題視する必要がないクエリだと解釈できます。
もし「通信講座 おすすめ」というクエリに対応する点が近似曲線よりも上側にあれば、「通信講座 おすすめ」を検索することで検索結果に表示される自サイトのページが想定CTRより高いCTRをとれていることを示しています。
この場合も「通信講座 おすすめ」は問題のないクエリだと解釈できます。というかむしろ順位のわりによくクリックされているので、タイトルやメタディスクリプションが優秀なのだと考えられます。
もし「通信講座 おすすめ」というクエリに対応する点が近似曲線よりも下側にあれば、「通信講座 おすすめ」を検索することで検索結果に表示される自サイトのページが想定CTR未満のCTRしかとれていないことを示しています。
つまりせっかく検索結果に表示されているのに、その順位から期待されるほどにはクリックされていません。故にタイトルやメタディスクリプションに改善の余地がありそうだと解釈できます。
ごく簡単にまとめると「近似曲線より上にある点は優秀」「近似曲線より下にある点は問題がありそう」いうことです。
そして「近似曲線より上なのか下なのか」を全クエリについて判定したのが先のエクセルの「改善要否」の列だったのでした。
補足事項
今回お伝えしたExcelを使った方法について、いくつか補足します。
補足1：クエリをもとにページを特定する必要あり
今回示した手順でわかるのは「CTRに課題のあるページ」というよりも「CTRに課題のあるクエリ」です。
「CTRに課題のあるページ」にたどり着くには、クエリをもとに該当ページを特定する必要があります。
サイト管理者であればだいたいは「このクエリならこのページだ」とわかると思いますが、わからない場合は実際に検索して検索結果画面を確認するとか、Googleサーチコンソールに戻って調べるといったプロセスが必要になります。
補足2：サチコのデータは不完全
Googleサーチコンソールのボタンをクリックしてダウンロードできるデータは1,000行までしか出ません。何か他にも制約があったかも。とにかく不完全です。
なので今回お伝えした手順でできるのは不完全なデータに基づいた簡易な分析となっています。それで十分なケースも多いと思いますが、完全なデータでないという点は意識しておいたほうがよさそうです。
もっと精度の良いデータで分析したい場合は、Search Analytics for Sheetsを使えばSearch Consoleの全量データを引っこ抜けます。
（SEOおたくさんがブログ記事のほうで紹介しているSearch Analytics Sheets add-onとはリンク先と名称が一致しませんが、たぶん同じもの）
ただしSearch Analytics for SheetsはGoogleスプレッドシートで使うアドオンです。
そのためこのアドオンを利用する場合は「データ抽出の段階ではスプレッドシート＋アドオンを使う」→「取り出したデータをExcelに移して分析する」といった流れになります。スプレッドシートの使用を完全には回避できません。
ちなみにSearch Analytics for Sheetsを使って取り出したデータではクエリとページの組み合わせごとに掲載順位やCTRがわかります。
したがって補足1で述べたような「クエリをもとにページを特定する」作業が不要になります。
この利点を考えると、最終的にExcelを使うのだとしても、可能であればデータの取り出し作業はスプレッドシート＋Search Analytics for Sheetsを使うのが良いと思います。
この記事のまとめ
今回はSEOおたくさんの「CTRに課題のあるページを特定する方法」のExcelバージョンをお伝えしました。
何らかの理由でスプレッドシートが使えない・使いたくないといった場合に、この記事で紹介した手順を試してみてください。
以上、参考になれば嬉しいです。