こんにちは！カスタマーエンジニアの神谷です。この前の3連休最終日は、一晩漬けたフレンチトーストを朝食に作ってみました。生クリームを入れた卵液はザルで濾すと口当たりが滑らか、厚切りの食パンを使うと仕上がりがフワフワになります。個人的には、バニラエッセンスを入れるのも好きです。たまにはこうして、優雅な朝を過ごしたいものですね。

さて今回は、Craft FunctionsからSearch Console APIを利用して、Google Search Console（以下、サーチコンソール）における検索キーワードとページURLのクロス集計結果を、Googleスプレッドシート（以下、スプレッドシート）に出力する方法をご紹介します。まずそもそもサーチコンソールってなんぞやという方は、以前書いたこちらの記事の冒頭をご覧ください。既にサーチコンソールについてある程度ご存知の方は、このまま下にお進みください。

サーチコンソールのクロス集計について

サーチコンソールの概要についてご理解いただけたら、今回のソリューションに関して話を進めていきます。サーチコンソールでは、どんなキーワードに対してどのページが検索結果に表示されるか確認できる、クロス集計機能があります。

スクリーンショット2024-02-1315.03.31.png
スクリーンショット2024-02-1315.05.41.png

上の画像は、弊社が運営するCX Clipにある記事の中から、それぞれ「n1分析」と「0 party data」というユーザーの検索に対して、どの記事が検索結果にヒットしたかを表しています。前者は5つ、後者は2つと、同じキーワードに対して複数の記事がヒットしている、つまりコンテンツのカニバリゼーション（以下、カニバリ）が起きていることがわかります。SEOやコンテンツマーケの世界でカニバリと言われた場合、1つのサイト内に似たような記事などが複数あって、同一キーワードに対して内部で競合している状態とお考えください。各記事の内容や状況にもよりますが、内部で競合している状態なので、コンテンツのカニバリは基本的にあまりいいことではありません。

カニバリが起きることによる問題やデメリット

一旦サーチコンソールから離れて、コンテンツのカニバリが起きた場合の問題やデメリットについて、もう少し具体的に見てみましょう。私の経験や一般論から、カニバリが起きることによる問題やデメリットには、主に以下の3つがあると考えています。

検索エンジンからの評価が分散する可能性がある

ユーザーの検索意図に対して適切なコンテンツを作成していると検索エンジンに判断されれば、上位表示される可能性が高まります。しかし1つのサイト内に似たような内容の記事が複数ある場合、その中でどれが最も適切なのか、検索エンジンが判断に迷ってしまい、結果としてどの記事も中途半端な順位になることがあります。せっかく頑張って記事を作っても、こうなってしまっては悲しいです。

意図したものとは違うコンテンツが上位に出る可能性がある

例えば「東京ラーメン」というキーワードを狙った記事を公開しても、既に似たような記事か、東京のラーメンに関するカテゴリやタグページがサイト内にあったとします。すると、そちらの方が先に検索エンジンにインデックスされており、後から出した記事はなかなか上位に出なかったりします。記事のクオリティに物凄い差があり、後発の記事の方が圧倒的に良ければそのうち逆転ってこともありますが、それはなかなか大変です。それに、意図していない記事や関連性の薄いページが上位に出ることで、ユーザーを混乱させてしまい、ユーザーからの信頼を失ってしまう可能性もあります。

同じようなコンテンツを作っていることに気づかずリソースが無駄になる

記事を作るには、内製にしろ外注にしろ、当然コストがかかります。それゆえ、気づかずに同じような記事を複数作ってしまうのは、金銭面でも人的リソースの面でも勿体無いです。資金が死ぬほど潤沢にあれば話は別ですが、そんなケースは極めて稀でしょう。ただでさえ効果が出るのに時間のかかるSEOやコンテンツマーケなので、どこの会社や組織もなるべく効率良く進めたいと考えているはずです。

このように、コンテンツのカニバリによって様々な問題が発生する可能性があるので、なるべくならそれを起こさず、起きてしまっている場合は順次対応していくべきでしょう。ただ、同一キーワードにおけるコンテンツのカニバリ状況を1つ1つサーチコンソールで見ていくのは、正直かなり大変で骨の折れる作業です。

クロス集計結果をスプレッドシートに一覧で出す意義

そこで今回の、クロス集計結果をスプレッドシートに一覧でまとめて出力することの意義が出てきます。既にサーチコンソールにある程度慣れている方や、ここまでご覧いただいて勘のいい方ならお気づきかもしれませんが、その意義は以下の4つだと考えています。

全てのキーワードと記事ページのURLをまとめて出力できる

スクリーンショット2024-02-1315.03.31.png

冒頭のスクショを改めてご覧いただくとわかりやすいですが、サーチコンソールのGUIでは、クロス集計の結果をまとめて確認できません。キーワードを1つ1つ切り替えながら順番に見ていく必要があるので、非常に効率が悪いです。しかしAPIを利用すれば、スプレッドシートに複数のキーワードとそれらに紐づくページのURLを全部まとめて出力できます。そのため、1つ1つキーワードごとに切り替えることなくカニバリ状況を確認できます。

出力する行数を自由に調節できる

スクリーンショット2024-02-1316.36.13.png

サーチコンソールのGUIでは、通常のキーワードもしくはページごと、あるいは今回のクロス集計にしろ、最大で1000行までしか出力できず、しかも500行を超える場合はページ分割が発生します。これもAPIを使えば、自分で自由に出力行数を決められるので便利です。5000行でも10000行でも、問題ありません。特に大規模なサイトの場合は、キーワード数もページ数も非常に多くなるため、その便利さを実感できるはずです。ただし、あまりにも出力するデータが多いと処理を実行しきれず、途中で終了する可能性はあります。

サーチコンソールの権限がない人でも確認できる

そもそもサーチコンソールの権限がないと、ユーザーがどんなキーワードで検索していて、どのページにどれぐらい流入しているのかを知る由もありません。全員に権限付与できればいいですが、人数が多くなってくると漏れが生じたり、正社員以外には付与できないなどの事情もあるでしょう。そんな場合でも、スプレッドシートに出力しておいて会社のメールアドレスを持つユーザーに共有できれば、皆でパッと確認できて便利です。

定期的に自動で実行できる

今回のソリューションに限った話ではないものの、GUIではなくAPIを利用することのメリットの1つに、自動実行が挙げられます。やはり手動で1つ1つGUIで見ていくのは面倒なのと、その作業自体を忘れがちになってしまったりするので、自動で実行できれば便利です。今回は、成果を見るのにある程度時間のかかるSEOやコンテンツマーケに関する話なので、1〜2ヶ月おきに実行して、その度に前回との比較も含めて変化を確認していくといいでしょう。

処理の流れと目指すアウトプット

だいぶ前置きが長くなりましたが、サーチコンソールのクロス集計やそれをスプレッドシートに出力することの意義についてご理解いただけたと思うので、話を前に進めます。今回の処理の流れは、以下の図のようになります。

スクリーンショット2024-02-1317.19.13.png

ここまでの話の内容の割に、処理自体は極めてシンプルです。Craft Functionsを定期的に実行し、Google Search Console API経由で検索キーワードとページURLのクロス集計結果を取得し、それを返します。そしてそれを成形して、スプレッドシートに出力するだけです。

スクリーンショット2024-02-1414.38.05.png

最終的なスプレッドシートでのアウトプットとしては、上記のようなものを目指してみます。キーワードごとに並び替えたりするのは、Craft Functionsよりもスプレッドシートでやる方が簡単なので、そちらを利用します。

手順

具体的な手順は、以下の通りです。

GCPプロジェクトでGoogle Search Console APIとGoogle Sheets APIを有効化する
Googleサービスアカウントを作成してjsonキーを取得する
サービスアカウントにサーチコンソールの権限を付与する
サービスアカウントにスプレッドシートの権限を付与する
サービスアカウントのjsonキーをCraft Secret Managerに登録する
クロス集計結果を取得してスプレッドシートに出力するためのCraft Functionsを作成する

1つずつ順番に見ていきましょう。

1. GCPプロジェクトでGoogle Search Console APIとGoogle Sheets APIを有効化する

まず、Google Search Console APIとGoogle Sheets APIを有効化します。その利用にあたっては、GCPプロジェクトを利用する必要があります。

スクリーンショット2024-01-3013.30.52.png
スクリーンショット2024-01-3013.31.24.png
スクリーンショット2023-08-1413.10.10.png

適当なGCPプロジェクトを作成または選択し、[APIとサービス > 有効なAPIとサービス > APIとサービスの有効化またはライブラリ] からGoogle Search Console APIとGoogle Sheets APIをそれぞれ検索し、有効化します。

2. Googleサービスアカウントを作成してjsonキーを取得する

次に、同一GCPプロジェクトでGoogleサービスアカウントを作成します。後にそのアカウントを使ってサーチコンソールとスプレッドシートの認証をします。

[IAMと管理 > サービスアカウント > サービスアカウントを作成] から適当な名称のアカウントを作成します。

スクリーンショット2023-08-1413.15.34.png

作成したサービスアカウントを選択し、[キー > 鍵を追加 > 新しい鍵を作成] からJSON形式のキーファイルをダウンロードします。

スクリーンショット2023-08-1413.16.12.png

このキーファイルの中身を、後でCraft Secret Managerに登録する必要があるので、取っておいてください。

3. サービスアカウントにサーチコンソールの権限を付与する

Google Search Console APIを有効化して、サービスアカウントのjsonキーを取得しても、それだけではGoogle Search Console APIは使えるようになりません。通常のGoogleアカウントにサーチコンソールの権限を付与するのと同様に、サービスアカウントにもサーチコンソールの権限付与が必要です。

スクリーンショット2024-01-3014.48.54.png

サーチコンソール左側のメニューから、設定を開きます
そこのユーザーと権限をクリックし、サービスアカウントのメールアドレスを入力し、フル権限を選択して追加します

もし皆さんが、サーチコンソールの管理者権限を持っている場合は、ご自身でサービスアカウントへの権限付与が可能なはずです。しかし上記の画面のようにユーザーと権限が設定の中になく、他者への権限付与ができない場合は、管理者に権限付与を依頼してください。

4. サービスアカウントにスプレッドシートの権限を付与する

サーチコンソールと同様、Google Sheets APIを有効化してサービスアカウントのjsonキーを取得しても、個別のスプレッドシートに対して自由にアクセスできるわけではありません。スプレッドシートごとに、閲覧権限や編集権限を付与する必要があります。

スクリーンショット2024-02-1317.57.41.png

右上の共有をクリックします
Googleアカウントに権限を付与するのと同様に、サービスアカウントのメールアドレスを入力します
権限には、編集者を指定してください

ここまで来れば、サーチコンソール及びスプレッドシートを利用するための準備は完了です。

5. サービスアカウントのjsonキーをCraft Secret Managerに登録する

Craft Functions作成の前に、ここまでで取得したサービスアカウントのjsonキーを、Craft Secret Managerに登録しておきましょう。これをCraft Secret Managerに登録しておくことで、中身を直接Craft Functionsに埋め込まなくても、使用できるようになります。

スクリーンショット2024-01-2219.11.47.png

[Craft > シークレット] からシークレットを新規作成します
サービスアカウントのjsonキーは、中身を丸ごとコピペして適当な名前をつけて保存してください

ここでつけた名前を、Craft Functions作成時に変数として設定します。

6. クロス集計結果を取得してスプレッドシートに出力するためのCraft Functionsを作成する

いよいよ本題となる、Craft Functionsの作成に入っていきます。サンプルコードは、下記URLで公開しています。

https://github.com/plaidev/craft-codes/tree/main/craft-functions/google-search-console-crosstab-spreadsheet

ソースコードの取得手順は、以下の通りです。

[Craft > ファンクション > 作成 > テンプレートから作成] を選択
Google Search Console APIを利用して、検索キーワードとページURLのクロス集計結果を、Googleスプレッドシートにまとめて出力する方法というテンプレートを検索し「取得」ボタンをクリック
「反映」ボタンをクリック
Functionを有効化して保存

まずは、5で設定したシークレットやその他必要なものを、変数として指定しておきましょう。

スクリーンショット2024-02-1413.24.31.png

SERVICE_ACCOUNT_KEY_SECRET
- 利用するGoogleサービスアカウントのJSONキーを登録したシークレットの名前です
SITE_URL
- 集計対象となるWebサイトのURLです
DAY_BEFORE_END_DATE
- 集計の終了日から何日前の日付を開始日として使用するかを指定します
SPREADSHEET_ID
- 出力先となるスプレッドシートのシートIDです
- URLがhttps://docs.google.com/spreadsheets/d/123456789/editの場合、123456789がそれに該当します
SHEET_NAME
- 出力先となるスプレッドシートのシート名です
ROW_LIMIT
- スプレッドシートに出力する集計結果の最大行数です

これらの値は直接Craft Functionsのコード内に埋め込むこともできますが、こうして変数として指定することで、コードをいじらずとも変更できます。特に、DAY_BEFORE_END_DATEを変えれば集計期間を伸ばしたり、ROW_LIMITを変えれば出力行数を増やしたりできて便利です。

ここから、Craft Functionsのコードで重要な部分について、かいつまんでご説明します。

// キーワードとURLの組み合わせを取得し、それらのメトリクスを取得する
async function getKeywordPageMetrics(searchConsole, siteUrl, startDate, endDate, logger) {
  try {
    const res = await searchConsole.searchanalytics.query({
      siteUrl,
      requestBody: {
        startDate,
        endDate,
        dimensions: ['query', 'page'],
        rowLimit: ROW_LIMIT // 必要に応じて出力行数を調整してください
      }
    });

    return res.data.rows.map(row => ({
      query: row.keys[0],
      page: row.keys[1],
      impressions: row.impressions,
      clicks: row.clicks,
      ctr: row.ctr,
      position: row.position
    }));
  } catch (error) {
    logger.error('キーワードとURLのメトリクス取得に失敗しました:', error.message);
    //logger.error(error.stack);
    //throw new Error(`キーワードとURLのメトリクス取得に失敗しました:` ,error.message);
  }
}

ここでは、キーワードとページのURLの2者を使ってユニークな組み合わせを作り、それぞれに対応するクリック数や掲載順位などのデータを取得しています。

// スプレッドシートにヘッダーとデータを書き込む関数
async function writeDataToSheet(sheets, spreadsheetId, sheetName, startDate, data, logger) {
  try {
    // ヘッダー行を設定
    const headers = [['集計開始日', 'キーワード', 'URL', '表示回数', 'クリック数', 'CTR', '掲載順位']];
    
    // 数値を四捨五入し、フォーマットを調整してデータ行を作成
    const formattedData = data.map(row => ([
      startDate, // A列に固定でstartDateを入れる
      `'${row.query}`, // キーワードの値を文字列として扱うためにアポストロフィを追加
      row.page,
      row.impressions,
      row.clicks,
      `${(Math.round(row.ctr * 1000) / 10).toFixed(1)}%`,
      Math.round(row.position * 10) / 10  
    ]));

    // データを書き込む範囲を指定
    const range = `${sheetName}!A1:G${1 + formattedData.length}`; // ヘッダー + データを書き込む範囲
    const values = [...headers, ...formattedData]; // ヘッダーとデータを結合して書き込む

    // ヘッダーとデータを書き込む
    await sheets.spreadsheets.values.update({
      spreadsheetId,
      range,
      valueInputOption: 'USER_ENTERED',
      requestBody: { values } 
    });
  } catch (error) {
    logger.error('スプレッドシートへの書き込みに失敗しました:', error.message);
  }
}

ここでは、ヘッダー及び取得したクロス集計結果を、スプレッドシートに出力する処理を書いています。見やすくするためにまず固定のヘッダーを作成し、2行目以下に実際の結果が出力されます。CTRや掲載順位については、サーチコンソールのGUIと表記を合わせるため、適当なところで四捨五入しています。

あとは、Craft Functionsを実行するための設定も必要になります。今回は、KARTEで閲覧や購入など何かイベントが発生した際に発動するのではなく、1ヶ月か2ヶ月程度に1回の定期実行が一般的かと思われるので、その設定方法をご紹介します。

スクリーンショット2024-02-1318.25.41.png

Craft Functionsの設定タブを開きます
スケジュールを有効化し、cron形式（JST）で実行時間を指定します

cron形式（JST）についてここで詳しくは説明しませんが、例えば毎月1日の11時に実行したい場合は、0 11 1 * *と入力して保存してください。とりあえずすぐ動かして実行結果を確認したい場合は、1分ごとに毎回実行される* * * * *を入力してみてください。入力欄の下に例があるので、そんなに難しくはないと思います。

ここまでできたら、Craft Functionsを有効化して保存してください。

検証

実際にCraft Functionsを実行して、Search Console API経由で取得したクロス集計結果が、スプレッドシートに出力されるか確認してみましょう。特定の条件の場合は出力しないといった条件分岐は設けていないので、途中でエラーが出ない限りは出力されるはずです。

スクリーンショット2024-02-1414.38.05.png

もしキーワードごとにまとめて並び替えたい場合は、出力結果のB列からG列を選択し、ページ上部のメニューから、データ→範囲を並び替えをクリックして昇順もしくは降順を選んで並び替えてください。

スクリーンショット2024-02-1414.37.20.png

あとは今回の場合、1行目を固定表示にすると、より見やすいのではないかと思います。ちなみに1ヶ月に一度などの定期実行にした場合、そのままだと同じシートで項目が上書きされてしまうので、出力したものはコピーして別シートとして保管しておくといいでしょう。

おまけ

今回のソリューションを使って、サイトにおけるコンテンツのカニバリ状況が分かったところで、具体的にそこからどうすればいいのかと思われる方もいるかと思うので、ケースごとに対応策を簡単にいくつか書いておきます。

SEO記事とLPがカニバっていた場合

この場合の対策は、割と簡単です。そもそもLPは、SEOによる検索流入を狙うものではなく、主に広告流入させて一気にコンバージョン獲得まで狙うものです。そのため、SEO記事とLPがカニバっていた場合は、LPにnoindex処理をして、サーチコンソールのインデックス対象からも削除しておくといいでしょう。

SEO記事同士がカニバっていて、どれか1つの成果がいい場合

似たようなSEO記事がサイト内に複数ありつつも、そのうちどれか1つで成果がしっかり出ていれば、これもそこまで対処は難しくありません。その成果がいいSEO記事にアクセスを集中させるため、他の記事はインデックス対象から削除し、1番いい記事へのリダイレクト処理をかけておきましょう。そこから数ヶ月経ち、1番いい記事への評価が更に高まったりすれば、他の記事は非公開にしてリダイレクトも止めて問題ないと思います。

SEO記事同士がカニバっていて、どんぐりの背比べな状態の場合

これは最も厄介且つよくあるケースなのですが、似たようなSEO記事がサイト内に複数あり、尚且つ検索エンジンからの評価もどっこいどっこいな状態です。どれもそれなりに評価されつつ、かといって検索1位や2位にもなっていない、まさに評価が分散している状態で、担当者の皆さんは頭を悩ませることでしょう。これはもう人力で見ていくしかないものの、各記事で元々どんなキーワードを狙っていたのか、あるいは中身を見比べてそれぞれの記事ごとに差別化していくなどの対応が必要です。

最後に

今回は、Craft FunctionsからSearch Console APIを利用して、キーワードとURLのクロス集計結果をGoogleスプレッドシートにまとめて出力する方法をご紹介しました！SEO対策やコンテンツマーケは、かなり根気のいる大変な施策ですが、諦めずにやるべきことをしっかりやれば、効果を実感しやすいものでもあります。今回のソリューションが、皆さんのSEO対策やコンテンツマーケを推進するのに、少しでもお役に立てれば幸いです。

プレイドでは、世の中にインパクトを与える新しいソリューションを一緒につくる仲間を募集しています！
ぜひ気軽にお問い合わせください。

↓ パートナー企業になりたい

CustomerData-Driven Solution を一緒に創りませんか？ | PLAID Ecosystem

↓ PLAIDで働きたい

Craft FunctionsからSearch Console APIを利用し、キーワードとURLのクロス集計結果をGoogleスプレッドシートに出力する方法