Data Studio Report: Analyzing Products in Merchant Center with BigQuery

Who doesn’t know this in the agency routine? A client’s online shop publishes new products, but no one in the ads agency noticed because those responsible for product maintenance don’t regularly communicate with the various agency partners. In the end, such product innovations are then casually mentioned at the next performance review meeting. Because we experience this case more as the rule rather than the exception, we have created a data-based solution that conveniently shows us new releases in the shop.

The article shows how to make new products from the Google Merchant Center available to a group of people via Google Data Studio.

For the implementation of this solution, you need:

• Read access to Google Merchant Center
• Google BigQuery
• Access to Google Ads Script / Google Apps Scripts
• Google Data Studio

The report shows all products that have newly appeared in the Merchant Center during the selected time period. For this, the range of the Data Studio time range control is compared with the previous period.

In addition, the report offers the possibility to distinguish between products that have been newly listed or have disappeared from the Merchant Center via the “new listed” / “unlisted” diagram.

As a result, you get a real overview of the product movements in the Google Merchant Center, which can be equally valuable for agency employees and the client in product analysis.

To work with Merchant Center data in BigQuery, it must first be imported there on a daily basis. There are 2 approaches for this.

1. With the predefined Merchant Center Data Center transfer, as detailed by Google in this article.
2. With our Ads Script solution, the script addresses the current Shopping Content API and is created based on the v2.1 API.

Option 1 is the simpler one, but it also has the disadvantage that the newest fields are not available here. With the presented Ads Script, you have more influence possibilities and can easily rework missing fields. For users with Ads Scripts experience, certainly the preferred way.

The query first retrieves all products for the set time period. Then the same happens for the previous time period.

Subsequently, the data is merged with Join and products that were present in both time periods are excluded. The data is then labeled as “unlisted” or “new listed” according to their affiliation.

Finally, the tables for the query are merged with UNION ALL.

To operate the query in Data Studio with dynamic dates, @DS_START_DATE and @DS_END_DATE need to be added to the code.

For the period of the time range control, this is simple:

For the previous period, it’s a bit more complex.

We want to determine the period before @DS_START_DATE that is exactly as long as the period between @DS_START_DATE and @DS_END_DATE

To be able to use the data correctly later, I built this test function:

If you add this in Google Data Studio, you can conveniently check if the time periods are set correctly.

Here’s the finished result for the Data Studio Report:

Now this query can be used in Data Studio with the help of the BigQuery Data Studio Connector. How this works is shown later in the article.

This BigQuery query shows products from the Google Merchant Center that had a problem yesterday.

For this, we only need to load the products with yesterday’s date. Then we unnest the itemLevelIssues so that we can retrieve them individually.

If the query in Data Studio is set up with meaningful cross-filtering for the problems, it serves as an efficient tool to identify issues and solve them systematically.

For the Data Studio report, we use the BigQuery query shown above and set it up in Google Data Studio as a data source for the report.

1. Open Data Studio report
2. Select ‘Add data’
3. Choose BigQuery as data source
4. Select custom query
5. Choose billing account
6. Insert query
7. Set Enable Date Range Parameter = true
8. Select ‘Add’

The data source is now available under “Resources > Data sources”. The luxury of giving the data source a name in advance does not exist here. This must now be done afterwards. The newly created data source is called “BigQuery”.

The data source is now rudimentarily ready, and you can start creating corresponding reporting tables.

The SEA team can now see which products are new, but still has to call up each number individually in the Merchant Center and use the shop search. Somehow… uncool – especially since the Merchant Center has its own product pages and the products have their own websites, and while we’re at it…. can we also have product images? So you don’t have to read whether it’s a jacket or a sweater?

Yes. It’s possible.

One of my new favorites has become the HYPERLINK() function in Google Data Studio.

Google’s short help is:

HYPERLINK(URL, Link Label)

Examples
HYPERLINK(CONCAT(‘https://www.youtube.com/watch?v=’, External Video Id), Video Title)

Back in the just created data source via Resources > Data sources > Edit

You can now create a new field and use the Hyperlink function to create a linked title.

And because that works so well, we’ll do the same for the Merchant Center page. This way, we have a field that says “View in Merchant Center” and links directly to the product detail page.

Cross-filtering in Google Data Studio offers even more value. It makes the tables interactive. If we activate this option for the brand list, clicking on a brand in the first table will cause the article table to display only articles of that brand.

Displaying images in Google Data Studio tables is very simple.

In the data source, you can define what type of value it is. For URL values, “URL” or “Image” are available here. We configure the imageLink for the reports as an image, and immediately have a quick perception of what the product is.