Automatisieren Sie Multi-Site-Reporting mit Google Sheets und der GSC-API

Die Arbeit im SEO-Bereich bringt interessante Herausforderungen mit sich, mit denen Sie sicher alle schon einmal konfrontiert waren.

Sie sind ein Meister der Flexibilität und der Bewältigung langwieriger Aufgaben. Ich habe kürzlich mit mehr als 100 erstklassigen Websites zu tun gehabt.

Bei der Zusammenarbeit mit globalen Unternehmen ist es eine ziemliche Herausforderung:

• Wrangle-Daten für über 100 Websites.
• Behalten Sie die Leistung jeder Website im Auge.

Und da einige dieser Websites auf der ersten Seite von Google miteinander konkurrieren, ist es durchaus möglich, dass der Traffic von Site 1 sinkt, Site 2 jedoch den Verlust auffängt.

Die Überprüfung der Google Search Console (GSC) einer Website ist einfach, bei Hunderten von Websites auf globaler Ebene jedoch mühsam.

Was kannst du tun?

Ich habe ein Google Sheets Apps-Skript entwickelt, das eine Verbindung zur GSC-API herstellt, um die globale Berichterstellung von einer mühsamen Aufgabe, die Tage – oder Wochen – dauern kann, in eine Aufgabe zu verwandeln, die nur wenige Minuten dauert.

Nachdem ich das Skript erstellt habe, kann ich ganz einfach einen Datumsbereich eingeben und Folgendes abrufen:

• Klicks und Impressionen.
• Schlüsselwörter.
• Durchschnittliche Platzierungen.
• Usw.

Da wir Hunderte von Websites verwalten, ist es nicht ungewöhnlich, dass Benutzer, wie oben erwähnt, auf einer unserer Websites landen, um ihren Kauf zu tätigen.

Im Großen und Ganzen ist das Gesamtbild wichtiger als die Leistung einer einzelnen Website.

Was ich dir zeige, gehört mir 10-stufiger Prozess um ein Skript zu erstellen, das zieht Klicks und Impressionen und vergleicht es dann im Jahresvergleich (im Jahresvergleich).

10-Schritte-Prozess zum Erstellen eines Google Sheets Apps-Skripts für die Berichterstellung auf Hunderten von Websites

Schritt 1: Erstellen Sie Ihre Google Sheets

Ihr erster Schritt besteht darin, Ihre ursprüngliche Google Sheets-Datei zu erstellen. Sie können dies tun, indem Sie die folgenden Schritte ausführen:

• Gehen Sie zu Google Drive.
• Navigieren Sie zu dem Ordner, in dem Sie die Dateien ablegen möchten.
• Rechtsklick Im Hintergrund
• Wählen Sie > Google Sheets > Leere Tabelle.

Sie möchten die Datei umbenennen. Ich habe meine „Global Search Console Reporting“ genannt.

Screenshot vom Autor, April 2024

Ihre Datei ist nun eingerichtet und Sie sind bereit für den nächsten Schritt.

Schritt 2: Einrichten Ihres Google Sheets

Ein leeres Blatt ist nutzlos und wird für Benutzer erst dann sinnvoll, wenn Sie in Zeile 1 einige Überschriften hinzufügen. Ich empfehle, in dieser Reihenfolge und in Fettschrift folgende Überschriften hinzuzufügen:

• Webseite.
• Nische.
• Klicks.
• Eindrücke.
• Klicks im Jahresvergleich.
• Eindrücke im Jahresvergleich.
• Klicks % Differenz.
• Impressionen % Unterschied.

Ihre Datei sollte nun etwa so aussehen:

Screenshot vom Autor, April 2024

Ihr nächster Schritt besteht darin, ein Google Cloud-Projekt zu erstellen, was ebenfalls recht einfach und unkompliziert ist.

Schritt 3: Erstellen Sie ein Google Cloud Console-Datenprojekt

Die Erstellung Ihres Projekts sollte kostenlos sein, da Google ein Guthaben von 300 US-Dollar zum Ausprobieren seiner Plattform bereitstellt. Wenn Sie Google Cloud noch nicht verwendet haben, finden Sie es unter https://console.cloud.google.com/.

Sie können nun diesen Schritten folgen:

• Klopfen Wählen Sie Projekt > Neues Projekt.
• Geben Sie den Projektnamen ein (Beispiel: „Mein GSC-Datenprojekt“).
• Klopfen Erstellen.

Screenshot vom Autor, April 2024

• Klicken Wählen Sie Projekt aus.
• Wählen Sie Ihr Projekt aus.

• Klicken Sie oben Suchen Bar.
• Geben Sie „Google Search Console API“ ein.”
• Wählen „Google Search Console API.“
• Klicken Aktivieren.

Schritt 4: Erstellen Sie Apps-Skripte in Google Sheets

In diesem Schritt arbeiten wir daran, das Apps-Skript in das zuvor erstellte Google Sheet zu integrieren. Sie müssen das Blatt öffnen und die folgenden Schritte ausführen:

• Klopfen Erweiterungen > Apps-Skript.

Ich werde nicht näher auf die Funktionsweise des Skripts eingehen, aber Sie können diesen Code kopieren:

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;
  }
}

Kehren Sie dann zu Ihrem Apps Script-Projekt zurück und gehen Sie wie folgt vor:

• Drücken Sie STRG + A um alle auszuwählen.
• Drücken Sie STRG + V um den kopierten Code einzufügen.
• Klopfen OK.
• Klicken Projekt speichern.
• Klopfen Laufen.

*Notiz: Wenn Sie von Google die Fehlermeldung „Bad Request“ mit zu vielen Weiterleitungen erhalten, liegt das daran, dass Sie über mehrere Konten angemeldet sind. Versuchen Sie es in einem Browser mit nur einem angemeldeten Google-Konto.

Screenshot vom Autor, April 2024

Sie werden dazu aufgefordert Überprüfen Sie die Berechtigungen und müssen das Google-Konto auswählen, das mit Ihrer Google Search Console verknüpft ist.

Google gibt Ihnen eine Warnung aus, da die App nicht verifiziert ist. Tippen Sie also einfach auf “Fortschrittlich” Einstellung und dann „Gehe zu Projekt ohne Titel (unsicher).“

Screenshot vom Autor, April 2024

Abschließend können Sie diesen Schritt abschließen, indem Sie auf tippen oder klicken Erlauben Taste.

Schritt 5: Richten Sie die Zugangsdaten ein

Ich weiß, dass es zwischen Sheets und der Google Cloud Console viel Hin und Her gibt, aber das ist zum jetzigen Zeitpunkt eine bedauerliche Notwendigkeit. Jetzt richten wir die Zugangsdaten ein, wofür Sie zur Google Cloud Console zurückkehren müssen.

Notiz: Du musst haben ermöglicht die Google Search Console API aus dem vorherigen Schritt.

Ihr Bildschirm sollte etwa so aussehen:

Screenshot vom Autor, April 2024

Sie müssen Folgendes tun:

• Klopfen Anmeldeinformationen > Anmeldeinformationen erstellen.
• Klopfen OAuth-Client-ID > Einwilligungsbildschirm konfigurieren.

• Klicken Extern.
• Klopfen Erstellen.
• Eingeben „Meine GSC-Daten“ als App-Name.
• Fügen Sie Ihre Support-E-Mail (Ihre E-Mail-Adresse, die für GSC verwendet wird).
• Fügen Sie Ihre DKontaktinformationen des Entwicklers (die E-Mail-Adresse, die Sie für GSC verwendet haben).
• Klopfen Speichern und fortfahren.
• Klopfen SCOPES HINZUFÜGEN ODER ENTFERNEN.
• Überprüfen Sie 2 davon Google Search Console-API Bereiche (möglicherweise auf Seite 2).

• Klicken Aktualisieren.
• Klicken Speichern und fortfahren.
• Klicken Sie nun Benutzer hinzufügen.

• Sie können mehrere Benutzer hinzufügen, vorzugsweise diejenigen, die Zugriff auf GSC haben.
• Speichern und fortfahren.

Schritt 6: Richten Sie das Google Cloud-Projekt für GSC-Daten ein

Während wir uns noch mit dem Google Cloud-Projekt befassen, möchten Sie vielleicht auf klicken Hamburger-Symbol und geh zu Cloud-Übersicht > Dashboard:

Screenshot vom Autor, April 2024

Sie werden feststellen, dass dort „Projektnummer“ steht, die Sie auswählen sollten und Kopieren durch Drücken STRG + C.

Wechseln Sie zurück zur Registerkarte „Apps Script“ und tippen Sie auf Projekt Einstellungen:

Screenshot vom Autor, April 2024

Gehen Sie zum Abschnitt mit dem Titel Google Cloud Platform (GCP)-Projektfügen Sie die Projektnummer (STRG + V) in das Textfeld ein und klicken Sie Projekt festlegen.

Schritt 7: Benennen Sie Ihr Google Apps-Skript um

Sie möchten nun Ihr Apps-Skript umbenennen, indem Sie zu gehen Projektgeschichte so was:

Sie werden dann:

• Klicken Projekt ohne Titel oben auf dem Bildschirm.
• Eingeben „Mein GSC-Datenprojektskript.“
• Klicke auf Umbenennen.

Schritt 8: Bearbeiten Sie die Google Apps-Manifestdatei für das Code.gs-Skript

Sie bleiben immer noch in Ihrem Drehbuch und wir werden darauf zurückkommen Projekt Einstellungen genau wie wir es zuvor getan haben.

Dieses Mal möchten Sie klicken Zeigen Sie die Manifestdatei „appsscript.json“ im Editor an um sicherzustellen, dass daneben ein Häkchen steht.

Klicken Sie anschließend auf Editor und navigieren Sie zu appsscript.jsonwas Sie unten sehen können:

Screenshot vom Autor, April 2024

Sie möchten alles in der Datei appsscript.json löschen und das folgende Skript einfügen:

{
  "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"
  ]
}

Sobald Sie den Code hinzugefügt haben, können Sie auf Ihren klicken Code.gs Datei und tippen Sie auf Speichern, und dann Laufen. Sie werden aufgefordert, die Berechtigungen zu überprüfen, und müssen Ihr entsprechendes Konto auswählen, um es weiterhin verwenden zu können.

Nach einigen Eingabeaufforderungen werden Sie aufgefordert, Ihrer App „Meine GSC-Daten“ zu erlauben, und die Ausführung beginnt.

Schritt 9: Passen Sie die Daten für die Website-Datenanalyse an

In der Google Sheets-Datei möchten Sie Folgendes hinzufügen:

• L1: Startdatum.
• L2: Endtermin.

Notiz: Das Start- und Enddatum sollte in angegeben werden M1 Und M2. Sie können beispielsweise Folgendes eingeben:

Notiz: Das Datumsformat kann je nach Ihren Systemeinstellungen und Ihrem Standort unterschiedlich sein.

Schritt 10: Legen Sie die bedingte Formatierung für nicht leere Zellen fest, die kleiner als Null sind

Alles ist eingerichtet, aber Sie sollten eine bedingte Formatierung hinzufügen, damit es besser aussieht. Wir konzentrieren uns auf die Spalten „Klicks % Differenz“ und „Impressionen % Differenz“:

Screenshot vom Autor, April 2024

Wählen Sie die Zeilen unter den Überschriften „Klicks % Differenz“ und „Impressionen % Differenz“ aus und klicken Sie auf Format > Bedingte Formatierung. Unter Formatregeln, Sie möchten auswählen Weniger als.

Im Textbereich „Wert oder Formel“ können Sie hinzufügen 0.

Wenn der Wert kleiner als 0 ist, ändern wir die Farbe in Rot, da der Wert negativ ist und der Datenverkehr verloren gegangen ist. Sie können dies tun, indem Sie auf die Farbdose klicken und sie in ändern Rot bevor Sie auf „Fertig“ klicken.

Wenn Sie einen positiven Anstieg des Datenverkehrs in einen grünen Wert umwandeln möchten, fügen Sie eine weitere Regel für hinzu Größer als und füge hinzu 0 Wert.

Hier sind die Formeln, die in G2 und H2 verwendet werden können (Sie können sie für jede Zeile replizieren; klicken Sie einfach darauf und ziehen Sie sie nach unten, um die anderen Zeilen anzuzeigen):

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

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

Jetzt haben Sie eine einfache Möglichkeit, Berichte für mehrere Websites gleichzeitig auszuführen.

Das ist es, Sie haben Ihren globalen Bericht

Geben Sie in Spalte A Ihre Google Search Console-Eigenschaften ein; Wenn es sich um eine Domäneneigenschaft handelt, fügen Sie sie als sc-domain:example.com oder eine URL-Eigenschaft als https://example.com hinzu

Um den Bericht auszuführen oder zu aktualisieren, verwenden Sie das Spezialmenü Search Console > Daten abrufen:

*Notiz: Dieses Skript unterstützt etwa 150 Domänen. Wenn Sie jedoch mehr benötigen, können Sie die Zeile Nr. 14 in Ihrer AppScripts-Datei anpassen:

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

Mit diesem Tutorial wird es Ihnen leicht fallen, tagelanges Sammeln von Daten und Erstellen von Berichten in ein paar Minuten zu verkürzen. Sie können die Skripte sogar erweitern, um andere Berechnungen durchzuführen oder mehr Daten für Ihren Bericht zu sammeln.

Schauen Sie sich mein anderes Tutorial zur Integration von ChatGPT mit Google Sheets an.

Die Automatisierung Ihrer Berichte ist eine großartige Möglichkeit, mühsame Aufgaben zu rationalisieren, und ich hoffe, dass sie Ihre Arbeit ein wenig erleichtert.

Mehr Ressourcen:

Ausgewähltes Bild: 200dgr /Shutterstock