Automatice los informes multisitio con Google Sheets y la API de GSC

Optimice su flujo de trabajo de SEO con GSC API Aprenda a gestionar datos de más de 100 sitios y realizar un seguimiento de su rendimiento a escala global.

Trabajar en SEO conlleva desafíos interesantes que estoy seguro de que todos habéis enfrentado en algún momento.

Eres un maestro de la flexibilidad y de la gestión de tareas tediosas. Recientemente me encontré tratando con más de 100 sitios de primer nivel.

Al trabajar con empresas globales, resulta todo un enigma:

• Recopila datos para más de 100 sitios.
• Controle el rendimiento de cada sitio.

Y, dado que algunos de estos sitios compiten entre sí en la primera página de Google, es muy posible que el tráfico del Sitio 1 disminuya pero el Sitio 2 capture la pérdida.

Verificar la Consola de búsqueda de Google (GSC) de un sitio es fácil, pero es intenso con cientos de sitios a escala global.

¿Qué puedes hacer?

Ideé un script de aplicaciones de Google Sheets que se conecta a la API de GSC para transformar los informes globales de una tarea ardua que puede llevar días (o semanas) a una que lleva unos minutos.

Después de crear el script, puedo poner fácilmente un rango de fechas y extraer el de cada sitio:

• Clics e impresiones.
• Palabras clave.
• Clasificaciones promedio.
• Etc.

Dado que administramos cientos de sitios, no es raro que los usuarios terminen en uno de nuestros sitios para realizar su compra, como se mencionó anteriormente.

En el gran esquema de las cosas, el panorama general es más importante que el rendimiento de un sitio individual.

Lo que les voy a mostrar es mi proceso de 10 pasos para crear un script que obtenga clics e impresiones y luego los compare año tras año (YoY).

Proceso de 10 pasos para crear un script de aplicaciones de Google Sheets para generar informes en cientos de sitios

Paso 1: crear tus hojas de cálculo de Google

Su primer paso es crear su archivo original de Google Sheets Puedes hacerlo siguiendo estos pasos:

• Vaya a GoogleDrive.
• Navegue hasta la carpeta donde desea colocar los archivos.
• Haga clic derecho en el fondo
• Seleccione > Google Sheets > Hoja de cálculo en blanco.

Querrás cambiar el nombre del archivo Llamé al mío «Informes de Global Search Console».

Su archivo ahora está configurado y está listo para el siguiente paso.

Paso 2: configurar su hoja de Google

Una hoja en blanco no es útil y no tendrá sentido para los usuarios hasta que agregue algunos encabezados en la fila 1. Los encabezados que recomiendo agregar, en este orden y en negrita, son:

• Sitio web.
• Nicho.
• Clics.
• Impresiones.
• Clics interanuales.
• Impresiones interanuales.
• % de diferencia de clics.
• Impresiones % Diferencia.

Su archivo ahora debería verse así:

El siguiente paso es crear un proyecto de Google Cloud, que también es bastante sencillo y directo.

Paso 3: cree un proyecto de datos de Google Cloud Console

Crear tu proyecto debería ser gratis porque Google ofrece un crédito de 300 dólares para probar su plataforma. Si no ha utilizado Google Cloud, puede encontrarlo en https://console.cloud.google.com/. https://console.cloud.google.com/

Ahora puedes seguir estos pasos:

• Toque Seleccionar proyecto > Nuevo proyecto.
• Ingrese el nombre del proyecto (ejemplo: «Mi proyecto de datos GSC»).
• Toca Crear.

• Haga clic en Seleccionar proyecto.
• Selecciona tu Proyecto.

• Haga clic en la barra de búsqueda superior.
• Escriba «API de Google Search Console».
• Seleccione «API de Google Search Console».
• Haga clic en Habilitar.

Paso 4: cree secuencias de comandos de aplicaciones en Google Sheets

En este paso, trabajaremos para integrar Apps Script en la hoja de Google que creó anteriormente. Deberá abrir la Hoja y seguir estos pasos:

• Toca Extensiones > Apps Script.

No voy a entrar en detalles sobre cómo funciona el script, pero puedes copiar este código:

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 = 'https://www.googleapis.com/webmasters/v3/sites/' + 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;
  }
}

Y luego regrese a su proyecto de Apps Script y haga lo siguiente:

• Presione CTRL + A para seleccionar todo.
• Presione CTRL + V para pegar el código que copió.
• Pulsa Aceptar.
• Haga clic en Guardar proyecto.
• Toca Ejecutar.

*Nota: Si recibe un error de Solicitud incorrecta de Google con demasiadas redirecciones, esto se debe a que tiene varias cuentas iniciadas. Pruebe en un navegador con una sola cuenta de Google iniciada.

Se le pedirá que revise los permisos y deberá seleccionar la cuenta de Google asociada con su Google Search Console.

Google le dará una advertencia porque la aplicación no está verificada, así que simplemente toque la configuración «Avanzada» y luego «Ir a proyecto sin título (inseguro)».

Finalmente, puede completar este paso tocando o haciendo clic en el botón Permitir.

Paso 5: configurar las credenciales de acceso

Sé que hay muchos intercambios entre Sheets y Google Cloud Console, pero es una necesidad desafortunada en este momento. Ahora configuraremos las Credenciales de acceso, lo que requerirá que regrese a Google Cloud Console.

Nota: Debes haber habilitado la API de Google Search Console en el paso anterior.

Su pantalla debería verse así:

Necesitará:

• Toque Credenciales > Crear credenciales.
• Toque ID de cliente de OAuth > Configurar pantalla de consentimiento.

• Haga clic en Externo.
• Toca Crear.
• Ingrese «Mis datos de GSC» como nombre de la aplicación.
• Agregue su correo electrónico de soporte (su correo electrónico utilizado para GSC).
• Agregue su información de contacto de desarrollador (el correo electrónico que utilizó para GSC).
• Toca Guardar y continuar.
• Toque AGREGAR O QUITAR VISORES.
• Marque 2 de los alcances de la API de Google Search Console (puede estar en la página 2).

• Haz clic en Actualizar.
• Haga clic en Guardar y continuar.
• Ahora haga clic en Agregar usuarios.

• Puede agregar varios usuarios, preferiblemente aquellos que tengan acceso a GSC.
• Guardar y continuar.

Paso 6: configurar el proyecto Google Cloud para datos de GSC

Mientras todavía estamos en Google Cloud Project, querrás hacer clic en el ícono de hamburguesa e ir a Descripción general de la nube > Panel de control:

Notarás que dice «Número de proyecto», que debes seleccionar y copiar presionando CTRL + C.

Vuelva a la pestaña Apps Script y toque Configuración del proyecto:

Vaya a la sección titulada Proyecto de Google Cloud Platform (GCP), pegue el número del proyecto (CTRL + V) en el cuadro de texto y haga clic en Establecer proyecto.

Paso 7: cambie el nombre de su secuencia de comandos de Google Apps

Ahora querrás cambiar el nombre de tu Apps Script yendo al Historial del proyecto de esta manera:

Entonces podrás:

• Haga clic en Proyecto sin título en la parte superior de la pantalla.
• Ingrese «Mi script de proyecto de datos GSC».
• Haga clic en Cambiar nombre.

Paso 8: Edite el archivo de manifiesto de Google Apps para el script Code.gs

Aún permanece dentro de su secuencia de comandos y volveremos a la Configuración del proyecto tal como lo hicimos antes.

Esta vez, querrás hacer clic en Mostrar archivo de manifiesto «appsscript.json» en el editor para asegurarte de que haya una marca de verificación junto a él.

A continuación, haga clic en Editor y navegue hasta appsscript.json, que puede ver a continuación:

Querrá eliminar todo el contenido del archivo appsscript.json y pegar el siguiente script:

{
  "timeZone": "America/New_York",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes": [
    "https://www.googleapis.com/auth/webmasters",
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/spreadsheets.currentonly"
  ]
}

Una vez que haya agregado el código, puede hacer clic en su archivo Code.gs y tocar Guardar y luego Ejecutar. Se le pedirá que revise los permisos y deberá seleccionar su cuenta adecuada para continuar usándola.

Después de algunas indicaciones, se le pedirá que permita su aplicación «Mis datos GSC» y comenzará la ejecución.

Paso 9: ajuste las fechas para el análisis de datos del sitio web

En el archivo de Google Sheets, querrás agregar lo siguiente en:

• L1: Fecha de inicio.
• L2: Fecha de finalización.

Nota: Las fechas de inicio y finalización deben especificarse en M1 y M2. Por ejemplo, puede ingresar:

• 01/03/2024
• 31/03/2024

Nota: El formato de fecha puede diferir según la configuración y la ubicación de su sistema.

Paso 10: establezca el formato condicional para celdas no vacías menores que cero

Todo está configurado, pero debes agregar algún formato condicional para que se vea mejor. Nos centraremos en las columnas «% de diferencia de clics» y «% de diferencia de impresiones»:

Seleccione las filas debajo de los encabezados “Clics % de diferencia” y “Impresiones % de diferencia” y haga clic en Formato > Formato condicional. En Reglas de formato, querrás seleccionar Menos que.

En el área de texto «Valor o fórmula», puede agregar 0.

Lo que esto hace es que si es menor que 0, cambiaremos el color a rojo ya que está en negativo y se ha perdido tráfico. Puedes hacer esto haciendo clic en la lata de pintura y cambiándola a roja antes de hacer clic en Listo.

Si desea cambiar un aumento positivo en el tráfico a verde, agregará otra regla para Mayor que y agregará el valor 0.

Aquí están las fórmulas para usar en G2 y H2 (puede replicarlas para cada fila; simplemente haga clic y arrastre hacia abajo para las otras filas):

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

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

Ahora tiene una forma sencilla de ejecutar informes en varios sitios a la vez.

Eso es todo, ya tienes tu informe global

En la columna A, ingrese sus propiedades de Google Search Console;

Para ejecutar o actualizar el informe, utilice el menú especial Search Console > Obtener datos:

*Nota: Este script admite alrededor de 150 dominios, pero si necesita más, puede ajustar la fila n.° 14 en su archivo AppScripts:

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

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

Con este mismo tutorial, le resultará fácil convertir días de recopilación de datos y ejecución de informes en unos pocos minutos. Incluso puedes expandir los scripts para realizar otros cálculos o recopilar más datos para tu informe.

Consulte mi otro tutorial sobre la integración de ChatGPT con Google Sheets.

Automatizar sus informes es una excelente manera de agilizar tareas tediosas y espero que facilite un poco su trabajo.

Más recursos:

Imagen de portada: 200dgr /Shutterstock

Leer el articulo original en Search Engine Journal.