3 Scripts For More Effective Google Shopping Reporting

By , Account Manager at Hanapin Marketing

107 SHARES

A few month’s ago I wrote about the power of AdWords scripts reporting. The reporting functionality in scripts allows you to access the API data tables. Not only is reporting faster and more efficient but allows access to additional metrics. Reporting is less technically demanding so even if you don’t have a lot of experience coding, this method is very accessible to anyone.

 

While Google Shopping is officially released there are still shortcomings and tasks that require too much time. Reporting speeds this up by exporting the data you need to make the decisions that matter. Now that AdWords Editor support has arrived, you can even find clever ways to output the data to make it easier to directly upload and post.

 

We’ll cover three use cases today. You will find each script is different in content but written very similarly. By the end you should be able to modify these to fit your own needs.

 

Getting Started

 

Rather than iterate through all the data using the standard scripting functionality, reports allow you to pull directly from data tables. For this reason the script looks a bit different and reads differently. If you are interested in all the data you can access in reports, browse through the API support material here. Alternatively I have a text post and a video post on the topic.

 

To pull data you must select a report, the metrics, the date range, and apply any limits. That is all you need! There are no requirements to create extra variables, loops, functions, or iterators. You can even use the same template since each report pull looks very similar in structure and content.

 

The last step is simply create a spreadsheet in Google Drive. Once you have created a blank sheet, go ahead and copy the link. Paste it in the relevant line of your script, making sure to enclose it in brackets, and close it with a semicolon.

 

Screen Shot 2014-08-13 at 10.12.25 AM

 

Item Performance

 

The first example is simple. Let’s say your product groups are not very well segmented but you want to know which individual products are doing well. This could be for general curiosity or because you want to segment a new product group for your top performers.

 

To do this, take the basic template and change the report type to “Shopping Performance” and select the relevant metrics. For this example we are going to search for any product with more than $1,000 in revenue over the last 30 days. Insert the script, run it, and you will have an output of every product exceeding your revenue threshold.

 


function main() {

//insert revenue in dollars below
var revenueThreshold = 1000 ;

var spreadsheet = SpreadsheetApp.create("Top Products");
var report = AdWordsApp.report(
'SELECT OfferId, ConversionsManyPerClick, ConversionValue ' +
'FROM   SHOPPING_PERFORMANCE_REPORT ' +
'WHERE  ConversionValue > ' + revenueThreshold +
' DURING LAST_30_DAYS');

report.exportToSheet(spreadsheet.getActiveSheet());

}

 

Competitive Metrics by Product Group

 

Google Shopping added competitive metrics to product listings. Benchmark CPC and impression share are now available in each product group in your account. While these metrics should never dictate your complete strategy, these metrics make it easier to see which direction you should be moving.

 

By utilizing these metrics you have the opportunity to see how your products compare to similar products you are competing against. Have a strong performer with a low impression share? You may consider bidding up. Alternatively if you are struggling to meet goals, you may compare your max CPC compares to the benchmark.

 

For this script we will simply pull the product groups and insert the data into a Google Spreadsheet. By pulling the campaign and ad group as well, we can easily track issues to the source and make the appropriate changes. This may not seem that great now, but trust me, you will thank me after you try and do this by hand in the interface.

 


function main() {

var spreadsheet = SpreadsheetApp.create("Product Group Competitive Metrics");
var report = AdWordsApp.report(
'SELECT CampaignName, AdGroupName, Id, BenchmarkAverageMaxCpc, BenchmarkCtr ' +
'FROM   PRODUCT_PARTITION_REPORT ' +
' DURING LAST_30_DAYS');

report.exportToSheet(spreadsheet.getActiveSheet());

}

 

Finding Poor Performing Products

 

What if you want to pull any product with high impressions and low clicks? Rather than indicate a strict AdWords issue, this script indicates an issue with the feed. This list could be extremely helpful since your benchmarks and other metrics could be decent, all while failing to produce much in the way of sales.

 

A list of these products allows you to consult your feed and make changes to the titles and descriptions as necessary. There is no exact science to it but by changing these product titles to focus on key terms, you will often see a nice CTR jump with more relevant traffic.

 

This script is very similar to the first example in that it looks for products surpassing a certain threshold and records them in a spreadsheet. Of course if you want to expand on this you could switch the products to look at the product groups, brands, or other divisions in the feed.

 


function main() {

//Only consider products with impressions greater than.
var impressionsThreshold = 500 ;
//Pull any products with lower CTRs than the threshold
var ctrThreshold = 0.002 ;

var spreadsheet = SpreadsheetApp.create("Poor Performing Products");
var report = AdWordsApp.report(
'SELECT OfferId, Impressions, Ctr ' +
'FROM   SHOPPING_PERFORMANCE_REPORT ' +
'WHERE  Ctr < ' + ctrThreshold +
' AND Impressions > ' + impressionsThreshold +
' DURING LAST_30_DAYS');

report.exportToSheet(spreadsheet.getActiveSheet());

}

 

Where To Go From Here

 

You’ll probably need to change a few aspects to make these scripts useful in your own accounts, but it should be pretty easy. Once you grasp the basics, taking these scripts even further isn’t an issue.

 

If you were feeling industrious, you could even work these into one script or multiple scheduled scripts. Now, instead of working through your account with multiple filters, you could run a weekly report that sends you poor preforming product IDs, high CPA product groups, and the difference between your Max CPC and benchmark CPC for your top product groups.

 

Feel free to chime in with your solutions! Not only do I like to see them, other readers will most likely benefit from them as well.

 

Get more weekly links with our Fast Five newsletter! Five Fast Links in Your Email Every Friday.

Also send me a daily RSS digest

Automation Toolkit

Twitter Facebook LinkedIn Google+ Email Print More