Advanced Reporting & Automation for Google Shopping

Using the Google Merchant Center API, Supermetrics & More

By Nico Brooks & Maggie Castle

It doesn’t take much to upend a Google Merchant Center (GMC) account.  A single recalled product in a feed of thousands could suspend an entire feed.  Discrepancies in shipping costs, lost site verification, or expired feeds are also common culprits. GMC requirements are always in flux and manual attention is typically required to monitor feeds.  If a single person manages multiple GMC accounts, or conversely, if multiple people manage a single account, this can become overwhelming. GMC’s email alerts don’t always reach the right person and don’t always flag small issues. We created several safeguards to make GMC account management more programmatic and GMC suspensions and product disapprovals more foreseeable. Below, we have outlined two ways to stay in front of GMC issues in order to keep your shopping campaigns running unencumbered.

Use the Content API for Shopping to track key GMC attributes

This process involves configuring a Google Apps Script (GAS) to run in a Google Sheet. We have provided sample code, but you should have some familiarity with GAS and Javascript before trying this on your own. See our post on advanced optimizations that don’t require a developer for some tips on getting up to speed with GAS.

With Google Sheets, Google Scripts, and the Content API for Shopping, we can track the feed status, its last upload date, the number of total products, the number of valid products, and more in one place from any number of GMC accounts.  These types of values, pulled on a daily and rolling basis, can quickly answer questions like “when will my feed next expire” or “I wonder if my drop in impressions is related to a decrease in products in the feed.”  While this information is accessible in GMC, pulling it into Google Sheets opens opportunities for enhanced reporting, alerts, and cross-account data consolidation.  It also saves time.

To replicate this system, follow the steps below.  Here is Google’s guide to the Content API for Shopping for additional context.  To get you started, we created a Google Sheet with the correct script for easy replication.

1. Log into your GMC account.  (I have run into issues doing this in Chrome when I have been logged in to other Google accounts at the same time, so I do this in an incognito Firefox browser.)
2. Open this Google Sheet – note that the sheet needs to be logged in to the same Google account as your GMC account for the script to work.
   1. Go to File > Make a Copy.  Do not change the ‘status’ tab name.
   2. In the copy, open Script Editor from the Tools dropdown.
   3. In Script Editor, go to Resources > Advanced Google Services > Content API for Shopping > Turn On.  Click the link at the bottom of the Advanced Google Services box: “Google Developers Console”.
   4. In Google Developer’s Console, go to Library. Double check that you’re still logged in as the same user as the google sheet. If you’re not logged into the right one you won’t be able to do this step. Search for and click on Content API for Shopping.  Press Enable button at the top.  Back in Script Editor, press “ok” on the Advanced Google Services box.
   5. In Script Editor, update the FEED variable with your Merchant Center ID(s) and feed ID(s). The script includes comments that describe how to do this.  Save the script.
   6. Select function > getDatafeedStatus > Click run (play button).  The first time you do this, you will need to grant permissions for the script to run. Go back to the Google Sheet, and voilà!
   7. To run this function every day and accrue rolling data, go to Resources > Current Project’s Triggers and set up the trigger featured below.  Email notifications can be set here as well.

If you work with multiple GMC logins, you will need to create a separate sheet for each login to collect data, since the script needs to execute under the same login as the GMC. We have multiple sheets collecting data, then aggregate the data into a single sheet using Google Apps Script. You could use the IMPORTRANGE spreadsheet function to do this as well, if you are not comfortable doing it in script.

There is other feed data you can fetch via the API. For other data feed status properties, see Google’s API documentation on Datafeedstatuses. You will need to customize the getDatafeedStatus() function in the script if you want to change the properties it is fetching.

Use Supermetrics to track trends over time and send automated alerts

While the Content API for Shopping extracts valuable information from your GMC account, we also recommend a second report that monitors your Shopping metrics based on AdWords and Bing Ads data.  We use Supermetrics to aggregate campaign-level data and send an alert if day-over-day spending has changed significantly.  This report and its accompanying alerts, in most instances, can inform you the next morning of suspicious campaign trends.  Then, after referencing the report you set up in Google Sheets with the Content API for Shopping, you will have a comprehensive understanding of the issue at hand.

We produce this report using the paid version of the Supermetrics add-in for Google Drive.  With the free version, you can pull up to 100 rows of data from Google Analytics and schedule it to refresh daily, but you won’t be able to fetch data directly from Bing and AdWords.

1. Initial requirements: Google AdWords & Google Analytics must be linked, and auto-tagging must be enabled
2. Pulling the raw data:  After adding the Supermetrics login in Google Sheets, pull the following data into a table:
   1. Data Source: Choose the Google Analytics (free version) or AdWords or Bing (paid version)
   2. Views: The view that hosts the data for the shopping campaign you want to track
   3. Select dates:  This will depend on how long of a trend you want to follow; we typically do 90 days.  However, keep in mind the free version only allows 100 rows
   4. Select metrics:  Ad impressions, Ad clicks, Ad cost, Transactions, Transaction revenue (or whatever else you’d like to monitor)
   5. Split by: View name, Date, Campaign
   6. Filter: Narrow in on your shopping campaigns only to keep the data set smaller.  We typically do: “Campaign contains Shopping” OR “Campaign contains PLA”
3. Creating a trend report:  Here is an example of the polished template we use to track the shopping campaign trends.  After you set up your raw data to populate on a daily basis, you can use the “example report” as a template for monitoring trends.  We typically look at cost and revenue as these the best red flag indicators
4. Setting up alerts:  With the free version of Supermetrics, you are limited to simply scheduling the report to email to you.  With the paid version, you can set up a trigger to send an email based on conditional factors.  Supermetrics conditional rules are limited to: “will send email if X cell is not empty.”  We found that some carefully constructed =IF formulas, coupled with a =COUNTBLANK formula, can meet this conditional rule.  To mirror this, follow the formulas in B11, then B4, and then B5 in the example report.  See the trigger rule below.

We hope you find these tactics helpful for keeping your shopping campaigns running strong. We’d love to hear your feedback in the comments. And if you’d like help optimizing a shopping campaign, drop us a line!

Maggie Castle manages a variety of shopping campaigns at Two Octobers and provides guidance and best practices for ecommerce marketing to our team. Prior to joining Two Octobers, Maggie managed shopping campaigns for IT giant CDW.

Nico Brooks oversees tools and analytics at Two Octobers, and loves nothing more than solving a real business problem with a few lines of code.