取りこぼしクリックの計算と、リライトの優先順位をスプレッドシートで出す手順

取りこぼしクリックとは、そのページの掲載順位なら平均的に得られるはずのクリック数と、実際のクリック数の差のことです。式にすると 表示回数 ×(期待CTR − 実CTR) になります。リライトの優先順位は、この数が大きい順に並べれば決まります。

この計算は表計算ソフトだけで最後まで再現できます。必要なのは Search Console の「ページ」CSVと、順位ごとの期待CTRの表が1枚だけです。

この記事では、その手順を式ごと全部書きます。判定の基準そのもの(なぜ順位4.0位から20.5位なのか、なぜ表示回数100回で切るのか)はリライトする記事の見つけ方に根拠まで書いてあるので、こちらは操作手順だけに絞ります。

同じ計算を貼るだけで済ませる道具も置いてありますが、先に手順のほうを書きます。中で何をしているか分からないまま順番だけ渡されても、その順番を信じる理由がありません。

なぜ、CSVを開いたところで手が止まるのか

ページCSVに入っている列は5つだけです。日本語のUIなら「上位のページ / クリック数 / 表示回数 / CTR / 掲載順位」で、英語なら「Top pages / Clicks / Impressions / CTR / Position」です。

この5列を眺めても、どの記事から直せばいいかは出てきません。止まる理由は3つあります。

1. 5列のどこにも「良い・悪い」の基準が入っていない

CTRが0.9%という数字だけでは、高いのか低いのか決まりません。4位の0.9%と12位の0.9%は正反対の意味だからです。

つまり足りないのはデータではなく、比べる相手のほうです。順位ごとの期待CTRという外から持ち込む1枚の表を横に置いて初めて、CTR列が意味を持ちます。Search Console の画面に「直すべき順」の並べ替えが用意されていないのも、この表が製品の中に無いからです。

2. 掲載順位が小数で来るので、表引きが素直に効かない

掲載順位は8.4位のような小数で出ます。VLOOKUPの完全一致(第4引数 FALSE)は、8.4という行が表に無いのでエラーになります。

では近似一致(TRUE)にすればいいかというと、これも困ります。8.9位が8位の値に丸め込まれるので、9位に限りなく近いページを8位として扱うことになるからです。順位が小数になる仕組みは掲載順位が小数点で出るのはなぜかに書いています。ここでは前後2行を按分する式で解きます。

3. CTR列は「表示用に整えられた文字」で、計算用の数値ではない

CSVのCTR列は 7% や 0% のように%が付いた形で入っています。桁数も一定ではありません。私が落としたファイルには、小数第2位まで出ている行と、小数がまったく無い行が混ざっていました。

さらに厄介なのは、表計算ソフトに読み込ませた時点で数値に変換されているのか、文字のまま残っているのかが環境によって変わることです。前者なら100を掛ける必要があり、後者なら%を外してから数値化する必要があります。どちらか分からないまま式を書くと、100倍ずれた結果が静かに出てきます。

そこで、この記事ではCTR列を使いません。クリック数と表示回数はどちらも%も区切りも付かない整数なので、自分で割り算して実CTRを作り直します。丸めの入っていない値になるぶん、こちらのほうが正確です。

スプレッドシートでの手順(6ステップ)

Google スプレッドシートでも Excel でも同じです。全体はこの6つです。

1. ページCSVを読み込む — ZIPを解凍して「ページ.csv」だけを使う
2. 実CTRの列を作る — クリック数 ÷ 表示回数 × 100
3. 期待CTRの列を作る — 引き当て表から按分して引く
4. 差を出す — 期待CTR − 実CTR。マイナスは0にする
5. 表示回数を掛ける — ここで率が数に変わる
6. 降順に並べて、条件で絞る — 順位4.0〜20.5位・表示回数100回以上

1. 読み込む

Google スプレッドシートなら「ファイル」→「インポート」からCSVをアップロードします。Excel でCSVをそのまま開くと文字化けすることがあります(ファイルの文字コードがUTF-8のためで、ファイルが壊れているわけではありません)。直し方はCSVが文字化けする理由と直し方にまとめました。

以降は1行目がヘッダー、2行目からデータ、A列からE列にCSVの5列が入っている前提で式を書きます。

2. 実CTRの列(F列)

F2  =B2/C2*100

  B列: クリック数
  C列: 表示回数
  → CTRが 7% のページなら 7 という数値になる(%記号は付けない)

D列のCTRは使いません。単位を「%を外した数値」に揃えておくのが要点で、この先の式はすべてこの単位で書きます。

3. 引き当て表を貼る

期待CTRの表を、同じシートのK列とL列に貼ります。別シートにしてもいいのですが、シート名を式に書く手間が増えるだけなので、同じシートの空いた場所で十分です。

コピーしてそのまま貼れる形にしておきます。タブ区切りなので、K1に貼れば2列に分かれます。

4	3.15
5	2.49
6	2.05
7	1.75
8	1.52
9	1.24
10	1.19
11	0.93
12	0.90
13	0.90
14	0.90
15	0.90
16	0.90
17	0.90
18	0.90
19	0.90
20	0.90
21	0.90

21位まで作ってあるのは、20.5位のページを按分するときに21位の行を参照するからです。20位と同じ値を1行足してあるだけで、深い意味はありません。ここを忘れると、20位台のページだけエラーになります。

13位から先が同じ値なのは、実測値ではなく下限を置いているからです。1位から20位までの全順位の一覧と、この数字の出どころは掲載順位別のCTR平均一覧にまとめてあります。この記事の表は手作業に必要な範囲(4位から21位)だけを抜いたものです。

4. 期待CTRの列(G列)— 小数の順位を按分する

ここがいちばん詰まるところなので、式をそのまま置きます。

G2  =IFERROR(
        VLOOKUP(INT(E2),$K:$L,2,FALSE)
      +(VLOOKUP(INT(E2)+1,$K:$L,2,FALSE)-VLOOKUP(INT(E2),$K:$L,2,FALSE))
       *(E2-INT(E2))
      , "")

やっていることは単純です。8.4位なら、8位の値と9位の値の間を0.4のぶんだけ進んだ位置を取るだけです。8位が1.52%、9位が1.24%なので、8.4位は1.41%になります。

⚠️ IFERROR で包んでいるのは、表の範囲外の行があるからです。引き当て表は4〜21位ぶんしかないので、1〜3位や22位以降の行はそのままだとエラーになり、最後の並べ替えで表全体が出なくなります。空欄にしておけば、あとの条件で外れます。

この按分は、私のツールがやっている計算とわずかにやり方が違います。ツールは補正前の値と補正率をそれぞれ按分してから掛けていますが、この記事の表は補正後の値を直接按分します。4.0位から20.5位の範囲で両者を突き合わせたところ、差は0.01ポイント未満でした。小数第2位まで見ても同じ値になります。

5. 差と取りこぼし(H列・I列)

H2  =MAX(0, G2-F2)          … 期待CTR - 実CTR。マイナスは0にする
I2  =C2*H2/100              … 表示回数を掛けて「クリック数」に戻す

  1本にまとめるなら
I2  =MAX(0, C2*(G2-F2)/100)

マイナスを0にするのを飛ばさないでください。ブログ名や運営者名で検索して来る人が多いページは実CTRが期待を大きく上回るので、放っておくとマイナスの行が並び順を壊します。

そして5の「表示回数を掛ける」が、この手順でいちばん効きます。差は率なので、そのまま並べると表示回数の少ないページが上に来ます。直したいのはクリックの数なので、数に戻してから並べます。

6. 絞って、並べる

F2からI2までを最終行までコピーしたら、条件で絞って降順に並べます。手で並べ替えると式の参照がずれることがあるので、別の場所に結果だけを出す形が安全です。

Google スプレッドシート
  =SORT(FILTER(A2:I500, C2:C500>=100, E2:E500>=4, E2:E500<=20.5, I2:I500>0), 9, FALSE)

Excel(Microsoft 365)
  =SORT(FILTER(A2:I500,(C2:C500>=100)*(E2:E500>=4)*(E2:E500<=20.5)*(I2:I500>0)),9,-1)

Excel のバージョンによっては SORT と FILTER が使えません。その場合はオートフィルタで表示回数と掲載順位を絞り、I列で降順に並べ替えれば同じ結果になります。

6ステップを架空のデータで通してみる

※ 以下はサンプルデータの数字です。実際の計測結果ではありません。

I列の降順に並べ替えると記事A → 記事D → 記事B の順になります。取りこぼしは順に約15.9クリック / 約9.1クリック / 約2.9クリックです。これがそのままリライトの優先順位になります。

注目してほしいのは記事Dと記事Bの逆転です。CTRの差そのものは記事B(0.47%)のほうが記事D(0.18%)より大きいのに、表示回数を掛けると記事Dが上に来ます。率で並べるか数で並べるかで、直す順番が入れ替わります。ステップ5を飛ばせない理由がここです。

記事Cは取りこぼしが0なので候補から落ちます。期待CTR0.90%に対して実CTRが1.11%あり、順位のわりによくクリックされているページだからです。0が出るのは正常な結果で、触らないほうがいいという意味です。

つまずきやすい5箇所

手作業・テンプレ・ツールをどう使い分けるか

ここまで書いた手順を、毎回やる必要はありません。選択肢は3つあります。

正直に書くと、2番目が現実的な答えです。一度シートを作ってしまえば、翌月やることはCSVの貼り替えだけです。手作業だから毎回ゼロからやり直し、ということにはなりません。

Looker Studio の Search Console コネクタでも、順位・表示回数・CTRは同じように取れます。ただし順位ごとの期待CTRを出す既製の項目は、私が探した範囲では見つけられませんでした。計算フィールドを自分で書くことになり、小数の按分をそこで組むのは表計算より面倒です。Search Console API を使えば行数の制限は変わりますが、プログラムを書く話になるのでこの記事では扱いません。

コネクタで何が出て何が出ないのか(と、この製品が2026年4月にData Studioへ改名した件)はサーチコンソールをLooker Studioにつなぐと何が分かるかに分けて書きました。

なぜ手順を全部公開するのか

手順を隠して道具を売る形にしたくないからです。式が分からないまま「この順番で直してください」と言われても、その順番を信じる理由がありません。逆に式が分かっていれば、出てきた順番がおかしいときに自分で確かめられます。

道具の存在理由は、式が秘密であることではありません。記事が数十本あるサイトで、月に1回この作業を繰り返すことになる、というのが理由です。順位は毎月動くので、期待CTRの引き当ても毎月やり直しになります。

そして、その繰り返しはテンプレでも解けます。だからここで手順を出し惜しみする意味がありません。私が付け足せるのは、順番を出したあとの「このページのタイトルをどう直すか」のほうです。

よくある質問

ツールを使わずに手作業でも同じことができますか

できます。この記事の6ステップがその手順です。必要なのはページCSVと、順位ごとの期待CTRの表が1枚だけで、式は=MAX(0, C2*(G2-F2)/100) に集約されます。この記事の表を使う限り、私のツールの出力との差は0.01ポイント未満です。判定の基準そのものはリライトする記事の見つけ方にすべて公開しています。

掲載順位が8.4位のとき、8位と9位のどちらの期待CTRを使えばいいですか

どちらでもなく、間を按分してください。8位が1.52%、9位が1.24%なので、8.4位は1.41%です。VLOOKUPの近似一致(第4引数 TRUE)にすると8.9位まで8位として扱われるので、そこだけ注意してください。

CSVのCTR列をそのまま使ってはいけないのですか

使えないわけではありませんが、単位の確認が要ります。CSVの中では 7% のように%が付いた形で入っており、読み込んだ結果が数値になるか文字のまま残るかは環境で変わります。クリック数 ÷ 表示回数で作り直せば、この確認が丸ごと要らなくなります。丸めも入らないので、そちらをおすすめします。

月額のツールを増やしたくないのですが、買い切りで足りますか

買い切りで成立するかどうかは、そのデータを誰が取り続けるかで決まります。提供側が集め続けるクラウド型は料金も続く形になり、手元のデータを処理するものは実行のたびに完結します。同じ用途でも、利用者の端末が取りに行く形なら、料金の形は月額に固定されません。 買い切りのSEOツールはどこまで使えるか、月額と分かれる条件に、用途ごとの分かれ目を書きました。

改善余地スコアの分だけ、クリックは増えますか

増えるとは限りません。期待CTRは平均値なので、あなたのページ固有の事情(検索結果の構成、季節性、指名検索の割合)は入っていません。掲載順位もクエリをまたいだ平均です。出てくる数字は「直せば必ずこの分増える量」ではなく、「見に行く順番」として使ってください。

まとめ

• 取りこぼしクリック = 表示回数 ×(期待CTR − 実CTR)。この数の大きい順が、そのままリライトの優先順位になる
• 手順は①CSVを読み込む ②実CTRを クリック数÷表示回数 で作る ③期待CTRを引き当てる ④差を出す ⑤表示回数を掛ける ⑥降順に並べて絞るの6つ
• CSVのCTR列は使わない。%付きの文字で入っていて、読み込み後に数値になるか文字のまま残るかが環境で変わるため
• 詰まるのは小数の順位。VLOOKUPの完全一致は空振りするので、前後2行を按分する。21位の行まで作っておくのを忘れない
• マイナスは0にして、表示回数を掛けてから並べる。率のまま並べると表示回数の少ないページが上に来る
• 1度シートを組めば、翌月はCSVを貼り替えるだけ。手作業でも十分回るので、この手順を出し惜しみする理由が無い

ここから先は、私が作ったツールの話です。

上の6ステップをそのまま自動でやります。Search Console のエクスポートをZIPのまま落として渡すだけで、解凍もファイル選びも、実CTRの作り直しも按分も要りません。判定と候補3記事の改善余地スコアの確認、そのうち1記事分の診断カードまでは無料で、残りの診断カードが2,980円(税込)の買い切りです。月額課金や自動更新はありません。ログインも不要です。CSVファイルそのものはブラウザの外に出ません(診断カードを出すときに選んだ1ページのURL・数値・入力したキーワードを、「ページタイトルを取得する」を押したときに判定に出たページのURLを最大10件送ります)。

⚠️ 向かない人をはっきり書いておきます。この記事のとおりに1度シートを組んでしまった人には、ツールは要りません。翌月からはCSVを貼り替えるだけで同じ順番が出ます。記事が10本前後で、条件(4.0位から20.5位・表示回数100回以上)を満たすページがそもそも数本しか無い場合も同じで、手で数えたほうが早いです。計算の中身を自分で確かめたい人も、この記事の式で十分足ります。