Neue Produkte im Google Merchant Center automatisch labeln

Voraussetzungen

Für die Implementierung dieser Lösung benötigt man:

• Leserechte für das Google Merchant Center
• Google Merchant Center Daten in BigQuery
• Zugang zu Google Ads Skript / Google Apps Skripts

Zielsetzung

Um Daten im Google Merchant Center zu ergänzen, gibt es im Merchant Center die Möglichkeit von „Supplemental Feeds“. Die aufgrund einer Artikelnummer und weiterer Merkmale ein Update der Produkte auslösen. Einen solchen Feed erstellen via mithilfe von Google Spreadsheets, BigQuery und einem AppScript welches die Daten aus BigQuery holt und in einen Spreadsheet schreibt.

Abfrage von neuen Produkte im Google Merchant Centers via BigQuery

Zunächst gilt es die neusten Produkte inBigQuery zu ermitteln und eine Tabelle eine Ausgabe im Format offer_id, customLabel0 zu erzeugen.

Dies erreichen wir in BigQuery mit folgender Abfrage:

SELECT
  offerId AS id,
  'Neu' AS custom_label_0
FROM (
  SELECT
    *
  FROM
    `mnyproject.gmc_data.SHOPPING_CONTENT`
  WHERE
    DATE(_PARTITIONTIME) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
WHERE
  creationDate > DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
ORDER BY
  creationDate

Diese Abfrage könnte nun direkt so im Apps Skript ausgeführt werden. Um den Code aber besser pflegbar zu halten, ist es besser die Abfrage direkt in BigQuery als Ansicht zu speichern.

Um den Datumsbereich festzulegen, wann Produkte als Neu einzustufen sind, muss man in Zeile 12 die 30 in einen beliebigen Wert ändern.

Neue Produkte in Google Spreadsheet ausgeben

Um die Produkte in Google Spreadsheets verfügbar zu machen nutzen wir ein Google Apps Scripts.

Das Skript führt eine Query auf die eben definierte Abfrage aus, formatiert die Daten für Spreadsheets vor und schreibt sie in den Spreadsheet. Bei dem Script handelt es sich um eine modifizierte Variante dieses Google Scripts.

Vor der Ausführung sollte man die Variablen in Zeilen 12-17 korrekt ausfüllen. Im Erstellten Spreadsheet muss der Sheet Name wie in Zeile 17 lauten.

function doPost(e){
    runQuery()
  return HtmlService.createHtmlOutput("script complete");
}

/**
 * This Script collects all Products in Merchant Center that have been created within the lookback window.
 */
function runQuery() {
  // Replace this value with the project ID listed in the Google
  // Cloud Platform project.
  var projectId = 'MYPROJECT';
  var dataset = 'gmc_data';
  var tableid = 'SHOPPING_CONTENT';
  var tablepath = projectId + '.' + dataset + '.' + tableid;
  var spreadsheet = 'MYSHEETURL';
  var sheetname = 'Neue Produkte';
  var labelName = 'Neu	';
  var lookback = 60

  var request = {
    query: 'SELECT offerId as id, "' + labelName + '" as custom_label_2 FROM (SELECT * FROM '+tablepath+' WHERE DATE(_PARTITIONTIME) = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)) WHERE creationDate > DATE_ADD(CURRENT_DATE(), INTERVAL -'+lookback+' DAY) ORDER BY  creationDate',
    useLegacySql: false
  };
  var queryResults = BigQuery.Jobs.query(request, projectId);
  var jobId = queryResults.jobReference.jobId;

  // Check on status of the Query Job.
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }

  // Get all the rows of results.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  if (rows) {
    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheet);
    var sheet = spreadsheet.getSheetByName(sheetname);
    sheet.clear();
    // Append the headers.
    var headers = queryResults.schema.fields.map(function(field) {
      return field.name;
    });
    sheet.appendRow(headers);

    // Append the results.
    var data = new Array(rows.length);
    for (var i = 0; i < rows.length; i++) {
      var cols = rows[i].f;
      data[i] = new Array(cols.length);
      for (var j = 0; j < cols.length; j++) {
        data[i][j] = cols[j].v;
      }
    }
    sheet.getRange(2, 1, rows.length, headers.length).setValues(data);

    Logger.log('Results spreadsheet created: %s',
        spreadsheet.getUrl());
  } else {
    Logger.log('No rows returned.');
  }
}

Neu Labels in Spreadsheet.js hosted with ❤ by GitHub

Spreadsheet als zusätzlichen Feed im Merchant Center hinzufügen

1. Das Merchant Center des Kunden öffnen, der den Supplemental Feed erhalten soll.
2. Navigieren nach Produkte > Feeds
3. Im unteren Bildschirmbereich den Button „Subfeed hinzufügen“ wählen.
4. Den weiteren Schritten folgen und das entsprechende Spreadsheet auswählen.
5. Wichtig sind die Einstellungen, auf welchen Hauptfeed diese Änderungen wirken sollen.

Problem: Abfolge Zeiteffizient triggern

Ein Problem was sich bei dieser Lösung stellt, sind die verschiedenen Zeiten, zu denen Daten erstellt werden:

1. Daten werden vom Merchant Center nach BigQuery übertragen
2. Das Apps Skript wird getriggert die Daten werden in das Sheet geschrieben
3. Das Google Sheet wird durch das Merchant Center abgerufen.

Fängt man dies nun an über Zeittrigger einzurichten, wächst die Gefahr, dass die Daten nur veraltet aktualisiert werden. Empfehlenswert ist daher ein Vorgehen wie von Gerald Maier in „Google Analytics Rohdaten Import als Trigger für BigQuery Abfragen“ zu nutzen.

Damit kann in diesem Fall das Apps Script direkt getriggert werden, nachdem die Datenübertragung von BigQuery fertig gestellt ist. Es kann also für die Aktualisierung im Google Merchant Center bequem die Zeit der Merchant Center Datenübertragung nach BigQuery + 1h genutzt werden.