Автоматизируйте создание отчетов с нескольких сайтов с помощью Google Sheets и GSC API

Работа с SEO приводит к интересным проблемам, с которыми, я уверен, вы все сталкивались в тот или иной момент.

Вы мастер гибкости и можете справляться с скучными задачами. Недавно я обнаружил, что имею дело с более чем 100 сайтами высшего уровня.

Работа с глобальными компаниями — это большая загадка для:

  • Измените данные для более чем 100 сайтов.
  • Контролируйте производительность каждого сайта.

А поскольку некоторые из этих сайтов конкурируют друг с другом на первой странице Google, вполне возможно, что трафик сайта 1 упадет, а сайт 2 возьмет на себя потери.

Проверить консоль поиска Google (GSC) одного сайта легко, но на сотнях сайтов по всему миру это трудоемкий процесс.

Что ты можешь сделать?

Я разработал скрипт Google Sheets Apps, который подключается к API GSC и превращает глобальную отчетность из громоздкой задачи, которая может занять дни (или недели), в задачу, занимающую несколько минут.

После создания сценария я могу легко вставить диапазон дат и получить информацию о каждом сайте:

  • Клики и просмотры.
  • Ключевое слово.
  • Средний рейтинг.
  • И т. д.

Поскольку мы управляем сотнями сайтов, пользователи нередко попадают на один из наших сайтов, чтобы совершить покупку, как упоминалось выше.

По большому счету, общая картина важнее, чем производительность отдельного сайта.

То, что я должен показать тебе, мое 10-шаговый процесс создать скрипт, который тянет клики и просмотры а затем сравнивает его по годам (г/г).

10-шаговый процесс создания сценария Google Sheets Apps для создания отчетов по сотням сайтов

Шаг 1. Создайте свои Google Таблицы

Скриншот автора, апрель 2024 г.

Ваш первый шаг — создать исходный файл Google Sheets. Вы можете сделать это, выполнив следующие действия:

  • Идти к Гугл Диск.
  • Перейдите в папку, в которую вы хотите поместить файлы.
  • Щелкните правой кнопкой мыши на заднем плане
  • Выберите > Google Таблицы > Пустой лист.

Вы хотите переименовать файл. Я назвал свой «Отчеты глобальной консоли поиска».

Шаг 1. Назовите файл Google Таблиц.Скриншот автора, апрель 2024 г.

Теперь ваш файл настроен, и вы готовы к следующему шагу.

Шаг 2. Настройка таблицы Google

Пустой лист бесполезен и не имеет смысла для пользователей, пока вы не добавите несколько заголовков в строку 1. Я рекомендую добавлять заголовки в следующем порядке и жирным шрифтом:

  • Веб-сайт.
  • Ниша.
  • Нажмите.
  • Впечатление.
  • Год-год-щелкни.
  • Впечатление из года в год.
  • Нажмите % разницы.
  • Разница в процентах просмотров.
ЧИТАТЬ   7 простых шагов для создания качественного списка контактов

Теперь ваш файл должен выглядеть так:

шаг 2: добавьте заголовки столбцовСкриншот автора, апрель 2024 г.

Ваш следующий шаг — создать проект Google Cloud, который также довольно прост и понятен.

Шаг 3. Создайте проект данных Google Cloud Console.

Создание вашего проекта должно быть бесплатным, поскольку Google предоставляет кредит в размере 300 долларов США для опробования своей платформы. Если вы еще не использовали Google Cloud, вы можете найти его. https://console.cloud.google.com/.

Теперь вы можете выполнить следующие шаги:

  • нажмите на Выберите Проект > Новый проект..
  • Введите название проекта (пример: «Мой проект данных GSC»).
  • нажмите на кабинет.
Шаг 3. Проект консоли GoogleСкриншот автора, апрель 2024 г.
  • щелкнуть Выберите проект.
  • Выберите свой проект.

шаг 3: выберите проект

  • Нажмите наверху Поиск бар.
  • Введите «API консоли поиска Google.«
  • выбирать «API консоли поиска Google».
  • щелкнуть Активировать.

Шаг 3 API консоли поиска

Шаг 4. Создайте сценарии приложений в Google Sheets

На этом этапе мы будем работать над встраиванием сценария приложений в Google Sheet, который вы создали ранее. Вам необходимо открыть лист и выполнить следующие действия:

  • нажмите на Расширения > Скрипт приложений.

шаг 4: создание сценария приложений

Я не буду вдаваться в подробности работы скрипта, но вы можете скопировать этот код:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Search Console')
    .addItem('Fetch Data', 'menuItem1')
    .addToUi();
}

function menuItem1() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow(); // Find the last row with data in column A

  // Clear cells C2:F151 before processing data
  sheet.getRange("C2:F151").clearContent();

  for (var i = 2; i <= lastRow; i++) { var siteProperty = sheet.getRange(i, 1).getValue(); var startDateValue = sheet.getRange('M1').getValue(); var endDateValue = sheet.getRange('M2').getValue(); var timeZone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(); var format = "yyyy-MM-dd"; // Calculate dates for last year var lastYearStartDate = new Date(startDateValue); lastYearStartDate.setFullYear(lastYearStartDate.getFullYear() - 1); var lastYearEndDate = new Date(endDateValue); lastYearEndDate.setFullYear(lastYearEndDate.getFullYear() - 1); var startDate = Utilities.formatDate(lastYearStartDate, timeZone, format); var endDate = Utilities.formatDate(lastYearEndDate, timeZone, format); // Fetch data for the previous year var previousYearResponse = requestSearchConsoleAPI(siteProperty, startDate, endDate); // Fetch data for the current year (unchanged) startDate = Utilities.formatDate(new Date(startDateValue), timeZone, format); endDate = Utilities.formatDate(new Date(endDateValue), timeZone, format); var currentYearResponse = requestSearchConsoleAPI(siteProperty, startDate, endDate); // Process and write data for both years processAndWriteData(sheet, i, previousYearResponse, currentYearResponse); } } function processAndWriteData(sheet, row, previousYearResponse, currentYearResponse) { // Check if response is not defined or null and has at least one row if (previousYearResponse && previousYearResponse.length > 0) {
    var previousYearClicks = 0;
    var previousYearImpressions = 0;

    previousYearResponse.forEach(function(row) {
      previousYearClicks += row.clicks;
      previousYearImpressions += row.impressions;
    });

    sheet.getRange(row, 5).setValue(previousYearClicks); // Write to column D (index 5)
    sheet.getRange(row, 6).setValue(previousYearImpressions); // Write to column E (index 6)
  } else {
    Logger.log('No data found for previous year in row: ' + row);
  }

// Process and write data for the current year
  if (currentYearResponse && currentYearResponse.length > 0) {
    var currentYearClicks = 0;
    var currentYearImpressions = 0;

    currentYearResponse.forEach(function(row) {
      currentYearClicks += row.clicks;
      currentYearImpressions += row.impressions;
    });

    sheet.getRange(row, 3).setValue(currentYearClicks); // Write to column C (index 3)
    sheet.getRange(row, 4).setValue(currentYearImpressions); // Write to column D (index 4)
  } else {
    Logger.log('No data found for current year in row: ' + row);
  }
}



function requestSearchConsoleAPI(siteProperty, startDate, endDate) {

  try {
    const oauthToken = ScriptApp.getOAuthToken(); // Correctly call the method
    const siteUrl = siteProperty;
    const url=" + encodeURIComponent(siteUrl) + '/searchAnalytics/query';
    const payload = {
      startDate: startDate,
      endDate: endDate,
      type: 'web'
    };

    const headers = {
      'Authorization': 'Bearer ' + oauthToken,
      'Content-Type': 'application/json'
    };
    const options = {
      'method': 'post',
      'contentType': 'application/json', // Consistent content type
      'headers': headers,
      'payload': JSON.stringify(payload),
      'muteHttpExceptions': true
    };

    const response = UrlFetchApp.fetch(url, options);
    const responseCode = response.getResponseCode();
    const contentText = response.getContentText(); // Get response text for logging

  Logger.log('Response Code: ${responseCode}'); // Use backticks
  Logger.log('Response Content: ${contentText}'); // Use backticks


  if (responseCode === 200) {
    const json = JSON.parse(contentText);
    Logger.log(json); // This will log the actual JSON response
    return json.rows; // Adjust this line based on the actual structure of your API response
  } else {
    // Correctly use backticks here for template literals
    const errorMessage="Error fetching data: ${responseCode} - ${contentText}";
    Logger.log(errorMessage);
    throw new Error(errorMessage);
  }

  } catch (e) {
    Logger.log('Error: ${e.toString()}');
    return null;
  }
}

Затем вернитесь к проекту Apps Script и выполните следующие действия:

  • Нажимать CTRL + А чтобы выбрать все.
  • Нажимать CTRL + В чтобы вставить скопированный код.
  • нажмите на Хорошо.
  • щелкнуть Сохранить проект.
  • нажмите на раса.

*Примечание: Если вы получаете сообщение об ошибке «Неверный запрос» от Google со слишком большим количеством перенаправлений, это связано с тем, что у вас зарегистрировано несколько учетных записей. Попробуйте в браузере, войдя в систему только с одной учетной записью Google.

Шаг 4: сохраните сценарий запуска приложенийСкриншот автора, апрель 2024 г.

Вам будет предложено Просмотр разрешений и должен выбрать учетную запись Google, связанную с вашей консолью поиска Google.

ЧИТАТЬ   Рукописная реклама Apple 1 Стива Джобса на аукционе за большие деньги | Цифровые тенденции

Google выдаст вам предупреждение, поскольку приложение не проверено, поэтому просто нажмите «Передовой» настройка, а затем «Перейти к проекту без названия (не уверен)».

Шаг 4: небезопасное приложениеСкриншот автора, апрель 2024 г.

Наконец, вы можете завершить этот шаг, нажав или нажав Позволять кнопка.

Шаг 5. Настройте информацию для доступа

Я знаю, что между Таблицами и Google Cloud Console происходит много взаимодействий, но на данном этапе это досадная необходимость. Теперь мы настроим учетные данные для доступа, для чего вам потребуется вернуться в Google Cloud Console.

Примечание: Вы должны иметь активирован API консоли поиска Google из предыдущего шага.

Ваш экран должен выглядеть так:

Шаг 5: экран концентрации OAuthСкриншот автора, апрель 2024 г.

Вы должны:

  • нажмите на Учетные данные > Создать учетные данные.
  • нажмите на Идентификатор клиента OAuth > Настройка экрана согласия.

шаг 5: создайте учетные данные oauth

  • щелкнуть Внешний.
  • нажмите на Кабинет.
  • Входить «Мои данные GSC» в качестве названия приложения.
  • Добавьте свой Поддержка по электронной почте (ваш адрес электронной почты используется для GSC).
  • Добавьте свой Дразработать контактную информацию (адрес электронной почты, который вы использовали для GSC).
  • нажмите на Сохранить и продолжить.
  • нажмите на ДОБАВЬТЕ ИЛИ УДАЛИТЕ ОБЪЕМ.
  • Отметьте 2 выкл. API консоли поиска Google области действия (может быть на странице 2).

Шаг 5: добавьте область действия API GSC

  • щелкнуть Обновлять.
  • щелкнуть Сохранить и продолжить.
  • Нажмите сейчас Добавьте пользователей.

шаг 5 добавить пользователей

  • Вы можете добавить больше пользователей, желательно тех, у кого есть доступ к GSC.
  • Сохранить и продолжить.

Шаг 6. Настройте проект Google Cloud для данных GSC

Пока мы все еще работаем над проектом Google Cloud, нажмите значок гамбургера и пойти в Обзор облака > Панель мониторинга:

Шаг 6. Облачная панель управленияСкриншот автора, апрель 2024 г.

Вы заметите, что там написано «Номер проекта», который вам нужно выбрать и Копировать нажав CTRL+С.

Вернитесь на вкладку «Скрипт приложений» и нажмите Настройки проекта:

шаг 6 настройки приложенияСкриншот автора, апрель 2024 г.

Перейти в раздел с названием Проект Google Cloud Platform (GCP)вставьте номер проекта (CTRL + V) в текстовое поле и нажмите Установить проект.

Шаг 7. Переименуйте скрипт Google Apps.

Теперь вы переименуете свой скрипт приложений, перейдя в История проекта так:

ЧИТАТЬ   Google Pixel 8 и Google Photos: 7 главных новых трюков с камерой

название проекта шага 7

Тогда вы:

  • щелкнуть Безымянный проект в верхней части экрана.
  • Входить «Мой сценарий проекта данных GSC».
  • нажмите на Переименовать.

Шаг 8. Измените файл манифеста Google Apps на скрипт Code.gs.

Вы все еще остаетесь в своем сценарии, и мы вернемся к Настройки проекта точно так же, как мы делали раньше.

На этот раз вы хотите нажать Отобразите файл манифеста «appsscript.json» в редакторе. чтобы убедиться, что рядом с ним стоит галочка.

Затем нажмите редактор и перейдите к appsscript.jsonкак вы можете видеть ниже:

Шаг 8: редактируем Appscript JSONСкриншот автора, апрель 2024 г.

Вы хотите удалить все в файле appsscript.json и вставить следующий скрипт:

{
  "timeZone": "America/New_York",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes": [
    "
    "
    "
    "
  ]
}

Добавив код, вы можете нажать на свой Код.gs файл и распечатать Сохранение, также раса. Вам будет предложено просмотреть разрешения и выбрать соответствующую учетную запись для продолжения использования.

После нескольких сообщений вам будет предложено авторизовать ваше приложение «Мои данные GSC», и выполнение начнется.

Шаг 9. Настройте даты анализа данных веб-сайта.

В файл Google Таблиц добавьте следующее:

  • Л1: Дата начала.
  • Л2: Дата окончания.

Примечание: Даты начала и окончания должны быть указаны в М1 и М2. Например, вы можете ввести:

Примечание: формат даты может различаться в зависимости от настроек вашей системы и местоположения.

Шаг 10. Установите условное форматирование для непустых ячеек меньше нуля.

Все настроено, но вам следует добавить условное форматирование, чтобы оно выглядело лучше. Мы сосредоточимся на столбцах «Разница по кликам» и «Разница по показам в %»:

шаг 10: количество кликовСкриншот автора, апрель 2024 г.

Выберите строки под заголовками «Разница по кликам, %» и «Разница по показам, %» и нажмите Формат > Условное форматирование. Ниже Правила формата, ты выберешь Меньше, чем.

В текстовую область «Значение или формула» можно добавить 0.

Если оно меньше 0, мы меняем цвет на красный, поскольку он отрицательный и трафик теряется. Вы можете сделать это, нажав на банку с краской и изменив ее на красный прежде чем нажать «Готово».

Если вы хотите изменить положительное увеличение трафика на зеленый, добавьте еще одно правило для Лучше чем и добавить 0 ценить.

Вот формулы, которые можно использовать в G2 и H2 (вы можете воспроизвести их для каждой строки; просто щелкните и перетащите вниз для других строк):

=IFERROR(IF(AND(C2<>"",E2<>""), (C2-E2)/E2, ""),"")
=IFERROR(IF(AND(D2<>"",F2<>""), (D2-F2)/F2, ""),"")

Теперь у вас есть простой способ запускать отчеты на нескольких сайтах одновременно.

Вот и все, у вас есть глобальный отчет.

В столбце A введите свойства консоли поиска Google; если это свойство домена, добавьте его как sc-domain:example.com или свойство URL как

Используйте специальное меню для запуска или обновления отчета Search Console > Получить данные:

последний этап езды

*Примечание: Этот скрипт поддерживает около 150 доменов, но если вам нужно больше, вы можете изменить строку № 14 в файле AppScripts:

sheet.getRange("C2:F151").clearContent();

Используя это руководство, вы легко сможете превратить дни сбора данных и создания отчетов в несколько минут. Вы даже можете расширить сценарии для выполнения других вычислений или сбора дополнительных данных для вашего отчета.

Ознакомьтесь с другим моим руководством по интеграции ChatGPT с Google Sheets.

Автоматизация отчетов — отличный способ упростить утомительные задачи, и я надеюсь, что это немного облегчит вашу работу.

Дополнительные ресурсы:


Рекомендованное изображение: 200dgr /Shutterstock

Source