指数近似/対数近似/累乗近似（掲載順位とCTR）

公開日：2020-03-31　最終更新日：2020-03-31

指数近似/対数近似/累乗近似（掲載順位とCTR）

Google検索順位によってクリック率（CTR）が大きく違う事は皆さん知っている事でしょう。
「検索順位 CTR」で検索すると、1位から10位までのCTRが参考として出ています。

年度やデバイス別等で数値は違って来ますが、
大抵は、1位で21～32%位になっているはずです。

もちろん、これらの数値は正しいはずです。
しかし、CTRはキーワードによっても違ってくるはずです。
当サイトなら、「エクセル」「VBA」これらを含むキーワードが主になってきます。
そこで、当サイトのデータを基に掲載順位とCTRの関係を調査してみました。

Google Search Consoleから:掲載順位とCTRデータを取得

Google Search Consoleから:掲載順位とCTRをコピーしてシートに貼り付けて散布図を作成します。
Google Search Console「検索パフォーマンス」

「平均CTR」と「平均掲載順位」にチェックを付けます。

この数値は検索で表示されたキーワードでの順位の平均になります。
順位が低く表示回数の少ないキーワードは集計されていません。
したがって、どのサイトでも似たような数値になっているものと思います。

最初はクリック数順になっています。
そのままですと、当然掲載順位の高いものばかりのデータになってしまうので、
「表示回数」の降順にします。

期間は「過去3か月」「過去6か月」等から適宜選びます。

1ページあたりの行数は多い方が良いので500にします。

表示されたデータをコピーしてシートに貼り付けます。
CTRと掲載順位だけにしたものが以下になります。

このデータを散布図にします。

散布図と近似曲線

※以下のExcel画面はOffice365を使用しています。

散布図の作成

上記のA1:B501を選択し、
「おすすめグラフ」
散布図が出てきますので、そのまま「OK」

見づらいので、点の大きさを小さくしたり、タイトルは要らないので消したり等々、ある程度整形します。

ここは本題とは関係ないので、見やすければ良いので適当に調整します。

近似曲線の追加

グラフに近似曲線を追加します。

上記手順で、以下の3つの近似曲線を追加します。
・指数近似
・対数近似
・累乗近似
近似曲線の数式も見たいので、「グラフに数式を表示する」にチェックを入れます。
見やすいように実線にしたり、近似曲線ごとに色を変えたり、見栄えを整えます。

完成したグラフが以下になります。

緑が、累乗近似
黒が、対数近似
赤が、指数近似

近似曲線を見ると、どうも実体と一致しません。
順位1位だと、80%位になってしまったり、順位が1より小さくなったり・・・
どれも実際のデータと見比べると、近似と言える曲線になっていません。

そもそも、順位からCTRを求めたいのですから、XとYが逆になってしまっています。
そこで、グラフの軸を入れ替えてやり直しします。

X軸Y軸を入れ替えて散布図を作成

行/列の切り替え

グラフの「データ選択」→「行/列の切り替え」
普通はこれでX軸Y軸を入れ替えられますが、今回の場合はエラーが出てしまいます。

仕方ないので、元データの列（A列とB列）を入れ替えてグラフを作成します。

散布図の作成

上記のA1:B501を選択し、
「おすすめグラフ」
散布図が出てきますので、そのまま「OK」

先ほどと同様に見栄えを調整します。

近似曲線の追加

近似曲線も先ほどと同様に追加します。
完成したグラフが以下になります。

緑が、累乗近似
赤が、指数近似
黒が、対数近似

近似曲線を見ると、
赤の指数近似がかなり実態に近いように見えます。
これを使う事にしましょう。

適切な近似曲線の選択について

最適な曲線を選ぶことはとても難しいものです。
筆者はこれについて説明できるだけの専門知識を有していません。
最適な曲線を選ぶ要素としては、
・グラフの形状
・変化量
・収束するか
・どんなデータなのか
等々多岐にわたって検討する必要があります。
この辺りについて興味がある方は、別途調べてみると色々と面白いとは思います。
また、近似曲線の数式についても、その理論までは説明できません。
本稿では、エクセルの作成手順や関数の説明だけに徹しています。
近似曲線の数式について参考にしたページの中で一番分かりやすかったのは以下でした。

【Excel（エクセル）術】グラフの近似曲線の係数をセルに書き出す方法

指数近似を数式で求める

グラフに表示された指数近似の数式は、

y = 0.712e-0.451x

これらの係数値を定数値で計算しても良いのですが、せっかくなのでこれも求めてみましょう。
一般的な数式で書くと、

y = ae^bx

この係数aと係数bを求める数式は以下になります。

係数a = EXP(INTERCEPT(LN(既知のY), 既知のX))
係数b = SLOPE(LN(既知のY), 既知のX)

関数の説明

EXP関数

eを底とする数値のべき乗を返します。
定数eは自然対数の底で、2.71828182845904となります。

Exp(数値)

数値はeを底とするべき乗の指数を指定します。
他の数値を底としてべき乗の計算を行う場合は、指数演算子(^)を使用します。
EXP関数は数値の自然対数を返すLN関数の逆関数です。

LN関数

数値の自然対数を返します。
自然対数とは、定数e(2.71828182845904)を底とする対数のことです。

LN(数値)

数値は自然対数を求める正の実数を指定します。
LNは、EXP関数の逆関数です。

INTERCEPT関数

既知のxと既知のyを通過する線形回帰直線の切片を計算します。
切片は、既知のxと既知のyを通過する最適な回帰直線に基づいています。
独立変数が0(ゼロ)のときの従属変数の値を決める場合にINTERCEPT関数を使用します。

INTERCEPT(既知のy,既知のx)

既知のyは、観測またはデータの従属範囲を指定します。
既知のxは、観測またはデータの独立範囲を指定します。

SLOPE関数

既知のyと既知のxのデータ要素を通じて回帰直線の傾きを返します。
傾きとは、直線上の2点の垂直方向の距離を水平方向の距離で除算した値で、回帰直線の変化率に対応します。

SLOPE(既知のy,既知のx)

既知のyは、従属変数の値を含む数値配列またはセル範囲を指定します。
既知のxは、独立変数の値を含む数値配列またはセル範囲を指定します。

掲載順位に対するCTRを求める

係数a = EXP(INTERCEPT(LN(既知のY), 既知のX))
係数b = SLOPE(LN(既知のY), 既知のX)
Y = 係数a*EXP(係数b*X)

これらの数式を各セルに入れます。

E1=EXP(INTERCEPT(LN($B$2:$B$501), $A$2:$A$501))
E2=SLOPE(LN($B$2:$B$501), $A$2:$A$501)
E5= $E$1*EXP($E$2*D5)

最新のOffice365であればスピルするので、
D5を範囲指定してD5:D24のように指定すれば、スピルして20位まで一気に出力されます。
Office365以外の場合は、D5セルの数式をコピーしてください。
作成された掲載順位とCTR

順位	CTR
1	45.34%
2	28.87%
3	18.38%
4	11.71%
5	7.45%
6	4.75%
7	3.02%
8	1.92%
9	1.23%
10	0.78%
11	0.50%
12	0.32%
13	0.20%
14	0.13%
15	0.08%
16	0.05%
17	0.03%
18	0.02%
19	0.01%
20	0.01%

Google検索では、1ページ目の前半にランクされないと極端にクリック率が落ちてしまいます。
2ページ目の後半になってしまうと、もうほとんどクリックされないことになります。