スプレッドシートとサーチコンソールAPIで作るキーワード順位調査ツール

キーワード順位の遷移を確認したい場合、有料のSEO対策ツールに頼ることが多いと思います。

検索順位はGoogleサーチコンソールでも確認はできますが、サーチコンソールの画面は使いづらく、複数のキーワードを横断して調べたりするのはとても面倒です。

Googleスプレッドシート(Apps Script)とサーチコンソールAPIを活用することで、簡易ではありますが、キーワード順位の過去遷移を無料で確認できるツールを作ることができます。

この記事では、Googleサーチコンソールが公開しているAPIを使って、過去のキーワード順位遷移を確認する方法を紹介します。

目次

スプレッドシートとサーチコンソールAPIで作るキーワード順位ツール

作ろうとしているツールの完成イメージは以下の通りです。

スプレッドシートの表示構成のサンプルを用意しました。(これにはApps Scriptは組み込んでありません)

スプレッドシートのサンプル

このツールを作るためには、以下の環境が揃っていることが条件となります。

キーワード順位遷移確認ツールを作るための条件
  • 確認対象となるサイトについて、サーチコンソールの「所有者」か「フルユーザー」の権限を持っている
  • 上記のGoogleアカウントにログインした状態で Google Developer Consoleにアクセスできる
  • Googleスプレッドシートの拡張機能からAppsScriptにアクセスできる

色々書いてはいますが、ブロガーさんが自分でサーチコンソールの設定を完了している状態であれば、問題はありません。

キーワード順位の遷移確認ツール の作成手順

作成の大まかな手順は以下の通りです。

Search Console URL Inspection API を使用するまでの手順(概要)
  1. Google Developer Console で新規プロジェクトを作成する
  2. Search Console APIを有効化する
  3. OAuthの認証画面を作成
  4. スプレッドシート上のApps Scriptの設定を変更する
  5. スプレッドシートに検索条件、実行ボタン、結果表示エリアを設置する
  6. スクリプトエディタにApps Scriptのコードを記載する

GDCで新規プロジェクト作成~Apps Script設定

1~4の GDCで新規プロジェクト作成からApps Script設定までは、以下の記事で詳しく解説していますので、参照してください。

すでにサーチコンソールAPIを使える状態になっている方は、1~4の手順は不要です。

スプレッドシートに検索条件、実行ボタン、結果表示エリアを設置する

新規のスプレッドシートを開いて、以下の画面と同じものを用意します。

スプレッドシートの表示構成のサンプルを用意しましたので、このシートをコピペしてもOKです。

スプレッドシートのサンプル

スクリプトエディタにApps Scriptのコードを記載する

最後の手順は、Apps Scriptの記載です。

まずは、コードで使用しているライブラリ(便利な追加機能みたいなもの)を追加します。

Apps Scriptの編集画面の左メニューにある「ライブラリ」の「+」ボタンを押します。

続いて、表示されるフォームに以下のスクリプトIDを入力します。
(これはDay.jsというライブラリで、日付の操作をしやすくするものです)

Day.jsのスクリプトID

1ShsRhHc8tgPy5wGOzUvgEhOedJUQD53m-gd8lG2MOgs-dXC_aCZn9lFB

実際の入力画面

最後に サンプル と同じ構成のスプレッドシートから、スクリプトエディタを開いて、以下のコードを記載します。

function getKeywordData() {
  //アクティブなシートを読み込む
  let mySheet = SpreadsheetApp.getActiveSheet();

  //サーチコンソールの取得する開始日・終了日を定義※取得したい期間を設定
  let startDate = dayjs.dayjs(mySheet.getRange(2,4).getDisplayValue());
  let endDate = dayjs.dayjs(mySheet.getRange(2,5).getDisplayValue());

  //順位遷移を取得したいキーワード
  let keyword = mySheet.getRange(2,3).getDisplayValue();

  //サーチコンソールに登録しているサイトドメイン
  let siteDomain = mySheet.getRange(2,2).getDisplayValue();

  //リクエストするAPIのURLを設定
  let apiURL = 'https://www.googleapis.com/webmasters/v3/sites/sc-domain%3A' + siteDomain + '/searchAnalytics/query';
  //サーチコンソールから取得するキーワードの最大数を設定する
  let maxRecord = 20000;
  
  //出力されたデータを一時保管する入れ物(配列)
  let keywordData = [];
  
  //リクエスト送る時のペイロード指定(=出力条件)
  let payload = {
    'startDate' : startDate.format("YYYY-MM-DD"),
    'endDate': endDate.format("YYYY-MM-DD"),
    "dimensions" : ["date","query","page"],
    "rowLimit" : maxRecord,
    "dimensionFilterGroups": [
    {
      "filters": [
        {
          "dimension": "query",
          "operator": "contains",
          "expression": keyword
        }
      ]
    }
  ]
  };

  //ヘッダーのオプション
  let options = {
    'payload' : JSON.stringify(payload),
    'myamethod'  : 'POST',
    'muteHttpExceptions' : true,
    'headers' : {"Authorization" : "Bearer " + ScriptApp.getOAuthToken()},
    'contentType' : 'application/json'
  };

  //APIリクエス送信とJSONの分解
  let response = UrlFetchApp.fetch(apiURL, options);
  let json = JSON.parse(response);
  if (!(typeof json["rows"] === "undefined" || json["rows"].length === 0)){ 
    //分解したデータを配列化して、入れ物の配列にpushでぶちこんでいく
    for(i=0;i<json["rows"].length;i++){
      keywordData.push([
                      json["rows"][i]["keys"][0],
                      json["rows"][i]["keys"][1],
                      json["rows"][i]["keys"][2],
                      json["rows"][i]["clicks"],
                      json["rows"][i]["impressions"],
                      json["rows"][i]["position"],
                      json["rows"][i]["ctr"]
                      ])
    }; 
    
    //前のデータを削除する
    mySheet.getRange(6, 1,mySheet.getLastRow(),7).clearContent();
    //スプレッドシートに出力
    mySheet.getRange(6, 1, keywordData.length, keywordData[0].length).setValues(keywordData);
    //CTRの表示形式変更
    mySheet.getRange(6, 7, keywordData.length).setNumberFormat('0.00%');
  }else{
    Browser.msgBox("該当するデータがありませんでした。");
  }
}

キーワード順位の遷移確認ツール の実行

実際の実行動画を見てみます。

キーワード順位の遷移確認ツール の応用

ここまでの手順では、

  • 日付
  • 検索クエリ(キーワード)
  • 記事URL
  • クリック数
  • 検索数
  • 平均順位
  • 平均CTR

のデータを表示させるまでとなっています。

このデータを元にピボットテーブルやQuery関数を使って、自分の見たいレポートを設計することで、よりSEO改善に役立ていることもできるかと思います。

レポートの作り方やQuery関数は以下の記事にもまとめていますので参考にしてみてください。

Apps Scriptを体系的に学べるUdemyの講座を集めてみましたので、深く学びたい方は見てみてください。

  • udemy-appsscript-001
    【新IDE対応】Google Apps Script(GAS)の基礎を完全習得

この記事が気に入ったら
フォローしてね!

よかったらシェアしてね!
目次
閉じる