Automatically label new products in Google Merchant Center

Requirements

For the implementation of this solution, you need:

• Read access to Google Merchant Center
• Google Merchant Center data in BigQuery
• Access to Google Ads Script / Google Apps Scripts

Objective

To supplement data in Google Merchant Center, there’s the option of “supplemental feeds.” These trigger product updates based on an item number and other attributes. You can create such a feed using Google Spreadsheets, BigQuery, and an AppScript that retrieves the data from BigQuery and writes it to a spreadsheet.

Query for new products in Google Merchant Center via BigQuery

First, you need to determine the latest products in BigQuery and create a table with an output in the format offer_id, customLabel0.

We achieve this in BigQuery with the following query:

SELECT
  offerId AS id,
  'New' 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

This query could now be executed directly in the apps script. However, to make the code more maintainable, it’s better to save the query directly in BigQuery as a view.

To specify the date range when products are to be classified as new, you must change the 30 in line 12 to any value.

Output new products in Google Spreadsheet

To make the products available in Google Spreadsheets we use a Google Apps Script .

The script executes a query based on the query just defined, formats the data for spreadsheets, and writes it to the spreadsheet. The script is a modified version of this Google Script .

Before executing, you should correctly fill in the variables in lines 12-17. In the created spreadsheet, the sheet name must be as shown in line 17.

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 = 'New Products';
  var labelName = 'New ';
  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.');
  }
}

New Labels in Spreadsheet.js hosted with ❤ by GitHub

Add spreadsheet as an additional feed in the Merchant Center

1. Open the customer’s Merchant Center that should receive the supplemental feed.
2. Navigate to Products > Feeds
3. Select the “Add subfeed” button at the bottom of the screen.
4. Follow the subsequent steps and select the corresponding spreadsheet.
5. It’s important to set which main feed these changes should affect.

Problem: Triggering the sequence time-efficiently

One problem with this solution is the different times at which data is created:

1. Data is transferred from Merchant Center to BigQuery
2. The Apps Script is triggered, and data is written to the sheet
3. The Google Sheet is retrieved by the Merchant Center.

If you start setting this up using time-based triggers, the risk increases that the data will only be updated when it’s out of date. Therefore, we recommend using a procedure like Gerald Maier’s in ” Google Analytics Raw Data Import as a Trigger for BigQuery Queries .”

In this case, the Apps Script can be triggered immediately after the data transfer from BigQuery is complete. This allows the time required for the Merchant Center data transfer to BigQuery plus 1 hour to be used for the update in Google Merchant Center.