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

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

Работая с глобальными компаниями, сложно:

• Обработка данных для более чем 100 сайтов.
• Следите за производительностью каждого сайта.

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

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

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

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

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

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

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

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

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

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

Шаг 1. Создание таблиц Google

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

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

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

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

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

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

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

• Веб-сайт.
• Ниша.
• Щелчки.
• Впечатления.
• Кликов по сравнению с прошлым годом.
• Годовые впечатления.
• Клики % разницы.
• Разница в процентах показов.

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

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

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

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

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

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

• Кран Выберите Проект > Новый проект..
• Введите название проекта (пример: «Мой проект данных GSC»).
• Кран Создавать.

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

• Нажмите Выберите проект.
• Выберите свой проект.

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

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

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

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

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

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.

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

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

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

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

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

Шаг 5. Настройте учетные данные доступа

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

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

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

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

Вам нужно будет:

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

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

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

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

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

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

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

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

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

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

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

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

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

Затем вы:

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

Шаг 8. Отредактируйте файл манифеста Google Apps для сценария Code.gs

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

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

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

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

Вам нужно удалить все из файла appsscript.json и вставить следующий скрипт:

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

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

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

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

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

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

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

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

Шаг 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 как

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

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

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

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

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

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

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

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