Automatically label new products in Google Merchant Center

When there’s a lot of activity in the customer’s Merchant Center, new products sometimes get overlooked. However, these could potentially have high potential and be seasonally important. For some customers, it can be relevant to group all products that are new in the Merchant Center into their own campaign, so they can better develop their performance there with a separate budget. To avoid relying on a static checkbox in the customer’s inventory management system for communication, we have developed a solution that labels products as ‘new’ if their Merchant Center ‘creationDate’ is not older than, for example, 30 days.

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

To supplement data in Google Merchant Center, there is an option for ‘Supplemental Feeds’ in the Merchant Center. These trigger an update of products based on an item number and other characteristics. We create such a feed using Google Spreadsheets, BigQuery, and an AppScript that retrieves data from BigQuery and writes it to a spreadsheet.

First, we need to identify the newest products in BigQuery and generate a table output in the format offer_id, customLabel0.

We achieve this in BigQuery with the following query:

This query could now be executed directly in the Apps Script. However, to keep the code more maintainable, it’s better to save the query directly in BigQuery as a view. To set the date range for when products are to be classified as new, you must change the 30 in line 12 to any desired value.

To make the products available in Google Spreadsheets, we use a Google Apps Script. The script runs a query on the previously defined query, formats the data for spreadsheets, and writes it to the spreadsheet. This script is a modified version of this Google Script. Before execution, you should correctly fill in the variables in lines 12-17. In the created spreadsheet, the sheet name must be as specified in line 17.

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

One problem that arises 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 triggers, there’s an increasing risk that the data will only be updated in an outdated manner. Therefore, it’s recommended to use an approach as described by Gerald Maier in “Google Analytics Raw Data Import as a Trigger for BigQuery Queries“. This way, in this case, the Apps Script can be triggered directly after the data transfer from BigQuery is completed. So, for the update in Google Merchant Center, the time of Merchant Center data transfer to BigQuery + 1h can be conveniently used.