Search Console の「平均掲載順位」を SQL や Excel で平均してはいけない理由
Search Console からエクスポートしたクエリ一覧を、ページ単位やサイト単位にまとめるとき、掲載順位の列を「平均」で集計していませんか。 平均掲載順位は「各表示ごとの順位の平均」なので、行をまとめるときは表示回数で重み付けしないと数値が狂います。 この記事は、なぜ単純平均が誤りになるのかと、Excel・SQL・スプレッドシート・Looker Studio での正しい書き方をまとめたものです。
1. 「平均掲載順位」は何の平均か
Search Console ヘルプ「検索パフォーマンス レポート」は、掲載順位を「検索結果に表示されたときの、最上位の位置」と定義し、平均掲載順位はそれを表示（インプレッション）ごとに平均したものだと説明しています。 つまり、レポートに出ている「12.5 位」は、そのクエリ（またはページ）が表示された回数ぶんの順位を足して、表示回数で割った値です。
ここが出発点です。平均の分母は「行数」ではなく「表示回数」。この前提を忘れると、行をまとめた瞬間に計算が崩れます。
2. 単純平均が誤りになる理由：15.0 と 12.5
同じページに 2 つのクエリが流入しているとします。片方は表示 300 回で 10 位、もう片方は表示 100 回で 20 位です。ページ全体の掲載順位は何位でしょうか。
| クエリ | 表示回数 | 掲載順位 | 順位 × 表示回数 |
|---|---|---|---|
| クエリ A | 300 | 10.0 | 3,000 |
| クエリ B | 100 | 20.0 | 2,000 |
| 合計 | 400 | — | 5,000 |
- 単純平均: (10 + 20) ÷ 2 = 15.0
- 表示回数で重み付けした平均: 5,000 ÷ 400 = 12.5
正しいのは 12.5 です。400 回の表示のうち 300 回は 10 位に出ていたのだから、ページ全体の順位は 10 位寄りになるはずで、15.0 は実態から 2.5 位も悪く見えています。 この 2 行の例は、私たちが集計ロジックの自己検証に使っているテストと同じ数値で、重み付けを外すと必ず 15.0 が出て失敗するようにしています。
実際のデータでは、表示回数が 10 の行と 10,000 の行が同じ表に並びます。単純平均は、表示 10 回のロングテールを表示 10,000 回の主要クエリと同じ重さで扱うため、ズレは 2.5 位どころではなくなります。 ロングテールほど順位が低い傾向があるので、単純平均はほぼ常に実態より悪い数値を出す、と考えてよいと思います。
3. 正しい式と、各ツールでの書き方
順位 = Σ(順位 × 表示回数) ÷ Σ(表示回数)
式は 1 つだけです。複数クエリをページにまとめるとき、複数ページをサイトにまとめるとき、複数期間を 1 つにまとめるとき、すべてこの式を使います。 各行の順位はすでに「その行の表示回数で平均された値」なので、もう一度表示回数を掛けて戻し、合計してから割り直す、という考え方です。
| ツール | 書き方（B 列 = 表示回数、C 列 = 掲載順位） |
|---|---|
| Excel | =SUMPRODUCT(C2:C1000, B2:B1000) / SUM(B2:B1000) |
| Google スプレッドシート | =SUMPRODUCT(C2:C1000, B2:B1000) / SUM(B2:B1000)（Excel と同じ） |
| SQL（BigQuery、PostgreSQL など） | SUM(position * impressions) / SUM(impressions) |
| Looker Studio（計算フィールド） | SUM(Average Position * Impressions) / SUM(Impressions) |
Excel やスプレッドシートでページ単位にまとめるなら、ピボットテーブルの「平均」は使わず、SUMPRODUCT で「順位 × 表示回数」の合計を出してから表示回数の合計で割ります。 SQL では AVG(position) と書いた時点で誤りです。GROUP BY page の中で必ず上の形にしてください。
注意点として、表示回数が 0 の行が混ざると分母が 0 になるので、除外するか NULLIF(SUM(impressions), 0) のようにガードします。Search Console のデータでは表示 0 の行は通常返ってきませんが、自前で結合した表では起こり得ます。
4. 期間をまたぐときの注意
日次で取得したデータを月にまとめる場合も、上の式で足せます。各日の順位はその日の表示回数で平均されているので、「順位 × 表示回数」を日ごとに出して合計し、表示回数の合計で割れば、月の重み付き順位になります。
ただし、期間が重なっているデータは足してはいけません。私たちは毎週「直近 28 日」の集計を保存していますが、隣り合う週のスナップショットは 21 日分が重複しています。 これを足すと重複した 21 日が二重に数えられ、順位もクリックも意味のない値になります。重なりのある期間は、足さずに「各時点の 28 日集計」として並べて見るのが正しい扱いです。
もう 1 つ、API で長期間を 1 回で取ると 50,000 行の上限で下位クエリが黙って欠けます。欠けた状態のデータを重み付き平均しても、上位に偏った順位が出るだけです。 これは 50,000 行制限の記事 に実測をまとめています。
5. Looker Studio で起きがちな誤り
Looker Studio で Search Console のデータを表にすると、掲載順位のフィールドの集計方法が「平均」になっていることがあります。 その状態でページやサイトの単位にディメンションを変えると、行を束ねるときに単純平均が使われ、上の 15.0 と同じ誤りが起きます。
対処は、掲載順位を表示するのに既定のフィールドを使わず、計算フィールドで重み付き平均を定義することです（3 章の表の式）。 ディメンションをどう変えても、常に「順位 × 表示回数の合計 ÷ 表示回数の合計」で計算されるので、ズレません。 既定のフィールドのままで使う場合は、少なくとも「この順位はどの粒度で平均されたものか」を確認してから読んでください。
6. 「小さいほど良い」指標の見せ方
掲載順位は、クリックや表示回数と違って数値が小さいほど良い指標です。前回比で「+3.0」と表示されたら、それは 3 位悪化した、という意味になります。 クリック数の「+3」と同じ色・同じ向きの矢印で並べると、読む人は必ず取り違えます。
- 改善・悪化の表示は、数値の増減と逆にする（順位が下がった＝数値が減った＝改善）
- 色だけに頼らず、「改善」「悪化」の文字か、矢印の向きを併記する
- 差分を出すときは、順位の差も表示回数で重み付けした順位どうしで取る（単純平均どうしの差は、差そのものが信用できない）
7. RocketConsole での扱い
RocketConsole では、画面と API 応答に出るすべての順位を、データベースのビュー側で 3 章の式に固定しています。 生の順位の列は position_raw と名付けて、SQL を書く人が AVG(position_raw) と書いた瞬間に「これは生の値だ」と気づけるようにしています。 クエリ・ページ・サイト・期間のどの粒度でも、順位の計算式は 1 つだけです。定義の一覧は 指標の定義 にあります。
参考
- Google 公式: Search Console ヘルプ「検索パフォーマンス レポート」（掲載順位・平均掲載順位の定義）— support.google.com/webmasters/answer/7042828
- Google 公式: 検索パフォーマンスデータの詳細（集計方法、匿名化クエリ）— developers.google.com/search/blog/2022/10/performance-data-deep-dive