Search Analytics for Sheetsのロゴ

当記事では、Search Analytics for Sheetsを使って、サーチコンソールの検索アナリティクスデータをGoogleスプレッドシート上に読み込み、SEO分析をする方法を解説していきます。

上手くデータを加工すれば、記事のリライト判断と方針を自動で判定することができるため、作業効率の向上が見込めます。

前提として、当記事はサイト運営者であり既にサーチコンソールを使っている人向けの記事となります。

Search Analytics for Sheetsとは?

Search Analytics for Sheetsとは、サーチコンソールの検索アナリティクスデータを、Googleスプレッドシート上で取得するアドオンです。

スプレッドシート上でデータを取得できれば、様々なデータの加工が可能になります。

例えば、検索上位なのにCTR(クリック率)が悪い記事や検索下位なのにCTRが高い記事などを簡単に見つけ出したりすることができます。

ここではSearch Analytics for Sheetsの導入方法と、私が実際にやっているSEOの分析方法をご紹介します。

Search Analytics for Sheetsの導入方法

実際にGoogleスプレッドシートにSearch Analytics for Sheetsを導入してみましょう。

まずはGoogleスプレッドシートのページに飛び、空白の新しいスプレッドシートを作成します。

Googleスプレッドシートのファイル名変更

適当に「検索アナリティクス分析」とでも名前を付けておきます。

Googleスプレッドシートのアドオン取得ボタン

アドオン→アドオン取得を選択します。

Search Analyltics for Sheetsの導入ボタン

Search Analyltics for Sheetsを検索し、該当アドオンの「+無料」をクリックします。

「アカウントの選択」という画面が出るので、使用しているアカウント名をクリックします。

Search Analyltics for Sheets導入前の許可ボタン

「Search Analytics for Google Sheets が Google アカウントへのアクセスをリクエストしています」と出るので、「許可」をクリックします。

これで導入完了です。

Search Analytics for Sheetsを使った検索アナリティクスデータの取得方法

次はSearch Analytics for Sheetsを使って、サーチコンソールの検索アナリティクスデータを取得してみます。

Search Analyltics for Sheetsのサイドバー表示ボタン

アドオン→Search Analytics for Sheets→Open Sidebarをクリックします。すると、右側にSearch Analytics for Sheetsという項目が出てきます。

Search Analyltics for Sheetsのサイドバー入力例

一番左上のRequestを選択します。

Verified Siteにて、解析したいサイトを選択します。

Date Rangeには、解析したい期間を取得します。今回は間近1週間分のデータの解析をしてみたいと思います。

Group Byでは、Query(検索クエリ)とPage(記事URL)を選んでおきます。

Rows returnedは1000 rowsを選択しておきます。

この状態で、Request Dataをクリックします。

すると、検索クエリごとの検索アナリティクスデータがスプレッドシート上に表示されます。

あとはデータを自由に加工するなりして、SEOの分析に役立てると良いと思います。

私がやっているSEO分析方法

ここからは私が実際にやっているSEOの分析方法を書きます。

Googleスプレッドシートでページごとに並び替え

まず、先程取得したシートのB列のURLを揃えておきます。B列の一番上のセルを右クリックし、「A→Zでシートを並べ替え」をクリックします。

これで記事ごとの検索クエリに統一されました。

このシートは後ほど使います。

次に、もう一つ新規のシートを作成します。

Search Analyltics for Sheetsで、Pageのみで取得したシート作成

今度はGroup Byの項目の「Query」を外して、「Page」のみで作ります。

すると、ページごとの検索アナリティクスデータが取得できます。

Googleスプレッドシートで、列を右に挿入

E列の一番上を右クリックして、1列を右に挿入をクリックします。

Fの列ができるので、ここに記事改善判断の条件式を埋め込みます。

記事改善の目標としては、以下に設定してみます。

  • サイトに多くの流入をもたらす検索順位1~4位の記事のタイトルと記事テーマの点検
  • 流入のない記事のコンテンツを改善し、検索10位以内に底上げする

2017年のGoogleの検索順位別クリック率は、

  • 1位: 21.12%
  • 2位: 10.65%
  • 3位: 7.57%
  • 4位: 4.66%
  • 5位: 3.42%
  • 6位: 2.56%
  • 7位: 2.69%
  • 8位: 1.74%
  • 9位: 1.74%
  • 10位: 1.64%

とありますので、この表に沿って条件式を作成してみます。

  • 検索順位が1位台でCTRが20%未満=検索需要ミスマッチ
  • 検索順位が2位台でCTRが10%未満=検索需要ミスマッチ
  • 検索順位が3位台でCTRが7.5%未満=検索需要ミスマッチ
  • 検索順位が4位台でCTRが4.5%未満=検索需要ミスマッチ
  • 検索順位が10位以下=コンテンツ不足

※検索需要ミスマッチ=記事タイトルもしくは記事テーマが悪くクリック率が悪い

また、検索順位が5位以下でCTRが5%よりも高いものは、検索上位サイトの検索需要ミスマッチ、またはコンテンツ不足を引き起こしている可能性があり、更に上位に食い込める余地があるため「注目」としておきます。

これらをスプレッドシートの計算式に書き直すと、

  • =IF(AND(D2<20%,E2<2,E2>0),“検索需要ミスマッチ”,IF(AND(D2<10%,E2>=2,E2<3),“検索需要ミスマッチ”,IF(AND(D2<7.5%,E2>=3,E2<4),“検索需要ミスマッチ”,IF(AND(D2<4.5%,E2>=4,E2<5),“検索需要ミスマッチ”,IF(AND(E2>=5,D2>=5%),“注目”,IF(E2>=10,“コンテンツ不足”,“”))))))

となります。

記事リライト判定の計算式

新しく作ったFのマスの一番上のセルを「チェック判定」とでもしておき、その下のセルに上記の式を入れます。

そのセルをCtrl+Cでコピーし、Ctrl+Shift+↓を押して列をすべて選択し、Ctrl+Vで貼り付けると一括で反映できます。

これで改善すべき記事と改善項目が一目瞭然になります。

基本的には、クリック数の高い順に並べた上で、

  • 検索需要ミスマッチ>コンテンツ不足>注目

の順に改善していくと良いと思います。

検索需要ミスマッチの改善例

検索需要ミスマッチが表示された場合は、

  • 記事タイトルの付け方に魅力がない
  • 記事テーマが検索クエリに対する大多数の検索需要に合致していない

のどちらかが考えられます。

記事URLを覚えておき、一番最初に作った検索クエリが表示された別シートを参照してみます。

該当記事URLを探し、主にクリックされている検索クエリで実際にGoogleで検索してみます。

検索上位に表示されている他の記事のタイトルやテーマを参照して、大多数の検索需要を探ってみると良いと思います。

コンテンツ不足の改善例

コンテンツ不足が表示された場合は、記事が拾っている検索クエリが全体的に上位表示されていないことが考えられます。

同じように記事URLを覚えておき、一番最初に作った検索クエリが表示された別シートを参照してみます。

該当記事に流入している検索クエリを見つつ、記事タイトルと関係ないキーワードが上位表示ができていない場合は、そのキーワードでSEO対策をした別記事を作り、関連記事として誘導していきます。

それでも上位表示できない場合は、関連記事を沢山書いて専門性を高めていきます。

細かな検索需要を満たすように記事を手直ししていけば、全体的な検索順位の底上げができるはずです。

おわりに

私は今までサーチコンソールのWeb画面でSEOの分析をしていましたが、Search Analytics for SheetsとGoogleスプレッドシートを使うことでより細かく、かつ素早く改善点を拾えるようになりました。

結構おすすめです。

当記事に書いた分析方法はあくまで私の個人的なやり方なので、是非参考にしていただき、オリジナルの分析、改善方法を作って取り組んでいただければと思います。