August 7, 2017
Automated Whitelist in Google Shopping with AdWords Scripts
Lower your CPC by automatically segmenting search queries
If you have already dealt with regular text ads in AdWords you’re certainly used to steering ads based on keywords you explicitly target. As you probably know, in Google Shopping everything is different. Google determines for what type of queries your products are being displayed depending on what your feed provides and what Google’s algo magic makes out of it.
On the one hand, this comes in handy as Google essentially does the keyword research for your products. On the other hand, we know that not all keywords are created equal, especially with regard to the holy grail called conversion rate. This is incorporated in many funnel views, i.e. we regularly assume customers performing short-tail queries to be at the higher levels of the purchase funnel (general interest, product unawareness) and those making long-tail queries to be at the bottom (decision made, close to action). Consequently, marketers typically want to apply varying max cpcs to keywords in AdWords to achieve certain return goals. With only one Google Shopping campaign you can define max cpcs on a product level, however not per keyword or set of keywords. While shopping campaigns don’t allow you to explicitly set keywords, it is at least possible to add negative terms. Add to that the shopping campaign’s priority settings and we have a way of managing Google Shopping on a keyword level.
The guys of CPC Strategy did a great job in explaining the concept in 2016 under the term ISO campaign. In essence, you can set the order of campaign execution, and thus structure varying levels of max cpcs for sets of keywords by using negative keywords.
As the experts from crealytics demonstrated Google will play your products for a broad range of keywords depending on how far you increase your cpc. The trick is that while you want to raise cpcs for well performing queries to the maximum amount of traffic achievable, you want to be careful buying too much of expensive clicks from generic terms with comparatively lower conversion rates. That’s why it comes down to structuring and segmenting your shopping campaigns at the end of the day.
So, time to dive into some practical action. Although in many cases you don’t really need the help of scripts for managing your shopping negatives, it might make sense in certain scenarios resp. accounts.
Here we will write a script that will automagically control the search terms we get by excluding all search phrases that do not fit into our range of allowed terms as defined by regular expressions. Whether you want to use this to structure campaigns with different priority settings or you just want to fully exclude not matching terms from bidding is up to you.
In this theoretical case we have two identical shopping campaigns except for their respective priority settings. Our high-prioritized campaign serves as landing campaign for whatever keywords Google imagines for our products because Google executes it first. Then we have a medium-prioritized campaign where we will serve everything that we exclude from the high-prio campaign with other cpcs. We already know that certain terms are super-relevant to us, namely all
keywords including the element ‘key’, so we want to shift search queries like keyboard, keychain, and *key to the city *to our medium-prioritized campaign (This may not sound plausible to you as it is possibly more relevant in e.g. the German language where certain words are built differently)
keywords including a 3-digit size, say 200 gallon smart pot as well as 250 gallon smart pot
We’re lazy and tired of skimming through search terms reports, so we will hand the task of constantly monitoring and excluding search phrases in the high-prio campaign to our new AdWords script.
First, we will create a very simple Google Spreadsheet for easier management of our whitelist regular expressions. Consider this example. The first three columns define where our whitelist script should apply, meaning we specify adgroup names, campaign prios (in this simple case the prio setting has to be reflected in the campaign’s name: campaign name has to include PX or P-X with X being between 1 and 3), and a simple on/off state. In the following columns we can add all the terms that should be positively matched by regular expression testing which will result in the respective search terms not being excluded.
In AdGroup1 we want to keep everything that contains key, so specifying key is enough here. In AdGroup2, however, we already need to implement regexp syntax as we want to fetch dynamic size patterns. We achieve this by adding [0–9]{3} to our sheet. This keeps all search queries with 3-digit numbers from being excluded.
Now add this script to your account and let it run as often as you prefer. Feel free to make any modifications as per your requirements. You will notice that the code is neither beautiful nor extensive, but people might get some inspiration from it.
/*
* Specify the link to your spreadsheet and the name of the relevant sheet here. You can use sheets to manage multiple accounts.
*/
function getSpreadsheet() {
var spreadsheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1vBAiXAb38Fdhwz00B7-sE3HMQJvQnUtc3amgunsvQcM/");
var sheet = spreadsheet.getSheetByName( "AccountName" );
return sheet.getRange("A:Z").getValues();
}
function main() {
/*
* You can modify the timerange for the search query lookup here. Currently the previous 30 days.
*/
var today = new Date();
var end = new Date( new Date().setDate(today.getDate()-1) ).toISOString().slice(0,10).replace(/-/g,"");
var start = new Date( new Date().setDate(today.getDate()-30) ).toISOString().slice(0,10).replace(/-/g,"");
var whitelist = {};
var wl = [];
var exclude = {};
var sharedList = {};
var sharedListExcludes = {};
/*
* Load Whitelists from Spreadsheet
*/
var data = getSpreadsheet();
for( var r=1; r < data.length; r++ ) {
if( data[r][0].length == 0)
break;
if( data[r][3] != 'On')
continue;
whitelist[ data[r][0]+data[r][1] ] = [];
wl.push( data[r][0]+data[r][1] );
if( data[r][2].length > 0 ) {
sharedList[ data[r][0]+data[r][1] ] = data[r][2];
}
for( var key in data[r] ) {
if( key < 5 )
continue;
whitelist[ data[r][0]+data[r][1] ].push( data[r][key] );
}
}
/*
* Load Search Query Report
*/
var report = AdWordsApp.report(
'SELECT CampaignName, AdGroupName, AdGroupId, Query' +
' FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
' WHERE ' +
' Clicks >= 1 ' +
' AND CampaignStatus = ENABLED ' +
' AND AdGroupStatus = ENABLED ' +
' DURING ' + start + ',' + end );
var rows = report.rows();
while (rows.hasNext()) {
var r = rows.next();
var name = r.AdGroupName + r.CampaignName.replace(/.*(P\-?[0-9]).*/g,'$1').replace(/\-/g,'');
if( wl.indexOf( name ) == -1 )
continue;
if( !(new RegExp( '.*(' + whitelist[ name ].join('|') + ').*') ).test(r.Query) ) {
if(sharedList[name]) {
if( typeof( sharedListExcludes[name] ) == "undefined" ) {
sharedListExcludes[name] = [];
}
sharedListExcludes[name].push( r.Query );
} else {
if( typeof( exclude[r.AdGroupId] ) == "undefined" ) {
exclude[r.AdGroupId] = [];
}
exclude[r.AdGroupId].push( r.Query );
}
}
}
/*
* Create the collected negative keywords
*/
for( var aId in exclude ) {
var a = AdWordsApp.shoppingAdGroups().withIds([parseInt(aId)]).get().next();
for( var i = 0;i < exclude[aId].length; i++ ) {
a.createNegativeKeyword('['+ exclude[aId][i] +']');
}
}
for( var listName in sharedListExcludes ) {
var l = AdWordsApp.negativeKeywordLists().withCondition('Name = "' + listName + '"').get().next();
for( var i = 0;i < sharedListExcludes[listName].length; i++ ) {
l.addNegativeKeyword('['+ sharedListExcludes[listName][i] +']');
}
}
}
Now as this script runs, all search queries that do not pass our whitelist will be excluded as [exact negative] from our P1 campaign. Awesome.