Cheap VPS & Xen Server

Business & Earning Opportunity

Dynamic Ad Extensions: don’t let your sitelinks and callouts get out of date


Even with expanded text ads, there’s still always more information you want to show searchers. That’s why we use ad extensions: callouts add extra info, sitelinks add extra info and links.

But the extra info — and the extra links — may change over time. Maybe you want a callout saying your minimum price, or how big your range is. That’s not going to stay the same over time. Or maybe you want sitelinks to be topical. There’s not much point giving a link to Valentine Gift Ideas when it’s April, or Barbecue Accessories when the weather’s bad, or Freshly Baked Hot Cakes when you’ve just sold out.

AdWords has ad customizers and ad params for constantly updating ad text, but nothing for updating extensions. So Brainlabs (my employer) came up with a two-part solution that we like to call dynamic ad extensions: a Google Sheet to define the sitelinks and callouts with variable placeholders, and the variables to insert into their text; and an AdWords Script to turn those definitions into real extensions.

dynamic-ad-extension-sheet

But why bother with the script? Why not just write out the extensions with a spreadsheet and copy them into AdWords Editor manually? Because you don’t just run the script this once — you can schedule this to run as often as you want. When new campaigns and ad groups come along, the script will give them all the right extensions, and you don’t have to lift a finger.

And you’re not just writing one set of extensions — the variables can be changed. They’re in their own cells separate from the ad text, so you can update them however you like. Here are some ideas:

  • Use basic functions and the current time from NOW() (just make sure the spreadsheet has the right time zone!):
    • Change sitelinks by time of day — for example, show the most appropriate menu using =if(hour(now())<11,"Breakfast Menu",if(hour(now())<17,"Lunch Menu","Dinner Menu"))
    • Get seasonal text and final URLs based on the date. Automatically change sitelinks from Summer Dresses to Autumn Coats to Christmas Sweaters.
    • Set up countdowns.
  • Be a bit more fancy, and use Google Sheet’s functions to import data:
    • IMPORTFEED can access RSS and ATOM feeds.
    • IMPORTHTML, IMPORTDATA or IMPORTXML can use any URL that has data in the right format.
    • GOOGLEFINANCE can get currency conversions and stock information (and if you want to see it in action, see our currency converter dashboard).
  • Fancier still, write a Google Apps Script within the spreadsheet to fill in the variable cells:
    • Use UrlFetchApp to get data from your site. For example, rather than a sitelink saying, “Wide Range Available,” you could check the actual number available and put that into the sitelink.
    • The Shopping Content Service lets you get data from your Merchant Center’s shopping feed — for example, you could show your cheapest prices or make sure sitelinks point to things that are in stock.
    • Use the Drive service to get data from your files stored in Google Drive. This could get you any information you can think of.
  • Or, simplest of all, you could just manually update the cells! You still have benefit of knowing all extensions are updated and are applied to all applicable campaigns or groups.

You can also add new sitelinks and callouts to the sheet whenever you feel like it. The script checks which of the extensions already exist, and it creates them if they don’t. You can also change which campaigns or ad groups the extensions get attached to (but the script won’t detach extensions from campaigns that aren’t covered any more — you’ll have to do that yourself).

Once an extension is created, the script will update it, rather than create a new one (There’s a hidden column that will automagically fill with extension IDs). So if there’s a campaign that’s not covered by the settings in the spreadsheet but uses the same sitelinks, it will still be updated.

So, you want to try this out for yourself? First go to the template sheet — in the File menu, click “Make a copy” to get a version for yourself. In your version, replace the example extensions with the details for your extensions:

  • The Sitelinks tab has columns for Dynamic Headline, Dynamic DL1 (for description line 1), Dynamic DL2 (for description line 2) and Dynamic URL. The Callouts tab has a column for Dynamic Callout (for the callout text). You can fill these in with the text you want for your extensions. Where you’d like to have variable text, you use {variable column name:default text}.
    • For sitelinks, you can leave the description lines blank, but not the headline or URL.
  • The next columns give the variables. Callouts only have two variables, var1 and var2. As sitelinks have more text to fill in, they can have up to five variables: the columns var1 to var5.
    • For example, if the Dynamic Headline said {var1:Many} {var2:Seasonal} Dresses, var1 said “302” and var2 said “Summer”, then the script would create a sitelink with the headline 302 Summer Dresses. If var1 is “300000002” — which would be too long to fit — the script would use the default text instead, making the headline Many Summer Dresses.
  • In the Sitelinks tab, the next columns are Headline, DL1, DL2 and URL; in the Callouts tab, the next column is Callout. You don’t fill in these columns — every time it runs, the script will fill them in with the actual values currently being used.
  • Mobile Preferred can be Yes or No, depending on whether you want the extension to be mobile-preferred or not.
  • Entity level is either Campaign or Ad Group, depending on which level you want your extensions.
  • The next columns are used to pick which campaign/ad group the extensions will be attached to:
    • If you want the extensions in just one campaign/ad group, then fill its exact name into Entity name is.
    • If you’d like the extensions in all campaigns/ad groups with a certain word or phrase in the name, then put it under Entity name contains.
    • If you’d like the extensions in all campaigns/ad groups except those with a certain word or phrase in the name, then put it under Entity name excludes.
    • If you’d like the extensions in all campaigns/ad groups with a certain label, then put the label name in Entity has label. Make sure you type it precisely correctly — the capitalization does matter for labels.
  • If the Entity level is Ad Group, then you can also fill in Parent is, Parent contains and Parent excludes — these are like Entity name is, Entity name contains and Entity name excludes, except they are used to filter the campaign name rather than the ad group name.

Once you have the spreadsheet set up, go to your AdWords account and copy and paste the script. Then there are a couple of settings at the top of the script:

  • Change spreadsheetUrl to the URL of your version of the spreadsheet.
  • If there’s a problem with running the script — for example, a campaign label has disappeared, or some part of the extension text was too long — then an email will be sent to all of the addresses in emailRecipients. These addresses should be surrounded by quotes and comma-separated. Alternatively, if you don’t want errors to be emailed, you can leave the array empty.

The first run will create the extensions. Then you can set up a schedule, so it can run as frequently as you think is necessary: every time the script runs, it will update the extensions and make sure they’re attached to everything that needs them.

(Note that if you’ve not run the script before, you can do a preview run to see what the extensions will look like. But it won’t show which campaigns the extensions will be attached to — to do that, the extensions need to actually be created, and they won’t be created in a preview run.)

/**
*
* Dynamic Ad Extensions
*
* Script to dynamically add and/or update sitelinks and callouts and apply them to
* all campaigns or ad groups, based on definitions and variables in a Google Sheet.
*
* Version: 1.0
* Google AdWords Script maintained on brainlabsdigital.com
*
**/
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//Options
var spreadsheetUrl = https://docs.google.com/YOUR-SPREADSHEET-URL-HERE;
// The URL of the Google Sheet with the extension definitions
// Should be a copy of https://docs.google.com/spreadsheets/d/1ROGMwhpIaZXuIsThZ6yaVKkO0Txa5aaYgNG0oM5qYgk/edit#gid=0
var emailRecipients = [];
// If set, these addresses will be emailed if there are errors when the script runs.
// Enter like [“a@b.com”] or [“a@b.com”,”c@d.com”,”e@g.co.uk”]
// Leave as [] to skip.
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
function main() {
// This array is used to store any errors to be emailed
var problems = {};
problems.general = [];
problems.sitelink = [];
problems.callout = [];
// Check the spreadsheet URL works
try {
var spreadsheet = checkSpreadsheet(spreadsheetUrl, the spreadsheet);
} catch (e) {
problems.general.push(e);
var subject = Dynamic Ad Extensions – Could Not Open Spreadsheet;
notify(problems, emailRecipients, subject);
throw(e);
}
// Process sitelinks
try {
var charLimits = {hl:25, dl1:35, dl2:35, url:2048};
var slSheet = spreadsheet.getSheetByName(Sitelinks);
generateSitelinks(slSheet, charLimits, problems.sitelink);
refreshSitelinks(slSheet, charLimits, problems.sitelink);
applyExtensions(slSheet, sitelink, problems.sitelink);
} catch (e) {
Logger.log(e);
problems.general.push(e);
}
// Process callouts
try {
charLimits = {callout:25}
var coSheet = spreadsheet.getSheetByName(Callouts);
generateCallouts(coSheet, charLimits, problems.callout);
refreshCallouts(coSheet, charLimits, problems.callout);
applyExtensions(coSheet, callout, problems.callout);
} catch (e) {
Logger.log(e);
problems.general.push(e);
}
// Send error emails
var subject = Dynamic Ad Extensions – problems encountered;
notify(problems, emailRecipients, subject);
Logger.log(Finished.);
}
/**
* Checks the spreadsheet URL has been entered, and that it works
*
* @param String spreadsheetUrl URL of a Google Sheet
* @param String spreadsheetName Name of the sheet, for use in error messages
*
* @return void
*/
function checkSpreadsheet(spreadsheetUrl, spreadsheetName) {
if (spreadsheetUrl.replace(/[AEIOU]/g,X) == https://docs.google.com/YXXR-SPRXXDSHXXT-XRL-HXRX) {
throw(Problem with + spreadsheetName + URL: make sure you’ve replaced the default with a valid spreadsheet URL.);
}
try {
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
return spreadsheet;
} catch (e) {
throw(Problem with + spreadsheetName + URL: ‘ + e + );
}
}
/**
* Searches sheet for any Sitelink entries without an ID and creates them
* Inputs their IDs into Column A for future reference
*
* @param Sheet sheet Sheet object containing Sitelink details
* @param Object charLimits Character limits of each entity
*
* @return void
*/
function generateSitelinks(sheet, charLimits, problems)
{
var lastRow = sheet.getLastRow();
var headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0];
var idIndex = headers.indexOf(ID)+1;
var ids = sheet.getRange(2,idIndex,lastRow1, 1).getValues();
// Array of row numbers missing ids
var missingIds = [];
for(var i = 0; i < ids.length; i++) if(ids[i][0] == ) missingIds.push(i+2);
Logger.log(Creating + missingIds.length + new sitelinks);
for(var i = 0; i < missingIds.length; i++)
{
var rowNum = missingIds[i];
var details = getSitelinkDetails(sheet, rowNum, headers, charLimits);
// Check char limits
var legit = true;
var entities = [hl,dl1, dl2, url];
for(var e in entities) if(details[entities[e]].length > charLimits[entities[e]]) legit = false;
if(legit)
{
var builder = AdWordsApp.extensions().newSitelinkBuilder();
var sl = builder
.withLinkText(details.hl)
.withDescription1(details.dl1)
.withDescription2(details.dl2)
.withFinalUrl(details.url)
.withMobilePreferred(details.mobPref);
sl = sl.build().getResult();
// If the script is being previewed, the sitelink won’t be made,
// so we don’t write the ID into the sheet
if(AdWordsApp.getExecutionInfo().isPreview() || sl == null) ;
else sheet.getRange(rowNum, 1).setValue(sl.getId());
}
else
{
Logger.log(Sitelink text too long in row +rowNum+ – extension not created.);
Logger.log(details);
problems.push(Sitelink text too long in row +rowNum+ – extension not created.);
}
}
}
/**
* Searches sheet for any Callout entries without an ID and creates them
* Inputs their IDs into Column A for future reference
*
* @param Sheet sheet Sheet object containing Callout details
* @param Object charLimits Character limits of each entity
*
* @return void
*/
function generateCallouts(sheet, charLimits, problems)
{
var lastRow = sheet.getLastRow();
var headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0];
var idIndex = headers.indexOf(ID)+1;
var ids = sheet.getRange(2,idIndex,lastRow1, 1).getValues();
// Array of row numbers missing ids
var missingIds = [];
for(var i = 0; i < ids.length; i++) if(ids[i][0] == ) missingIds.push(i+2);
Logger.log(Creating + missingIds.length + new callouts);
for(var i = 0; i < missingIds.length; i++)
{
var rowNum = missingIds[i];
var details = getCalloutDetails(sheet, rowNum, headers, charLimits);
// Check char limits
var legit = details.callout.length <= charLimits.callout;
if(legit)
{
var builder = AdWordsApp.extensions().newCalloutBuilder();
var co = builder
.withText(details.callout)
.withMobilePreferred(details.mobPref);
var callout = co.build().getResult();
// If the script is being previewed, the callout won’t be made,
// so we don’t write the ID into the sheet
if(AdWordsApp.getExecutionInfo().isPreview() || callout == null) ;
else sheet.getRange(rowNum, 1).setValue(callout.getId());
}
else
{
Logger.log(Callout text too long in row +rowNum+ – extension not created.);
problems.push(Callout text too long in row +rowNum+ – extension not created.);
}
}
}
/**
* Pulls the existing sitelinks, substitutes vars
* If it can update existing sitelinks it does
*
* @param Sheet sheet Sheet object containing Sitelink details
* @param Object charLimits Character limits of each entity
*
* @return void
*/
function refreshSitelinks(sheet, charLimits, problems)
{
var lr = sheet.getLastRow()
var idsArray = sheet.getRange(2,1,lr1).getValues();
// Flatten ids
var ids = [];
for(var i = 0; i < idsArray.length; i++)
{
if(idsArray[i][0] !== undefined) ids.push(idsArray[i][0]);
else ids.push();
}
var headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0];
var startIndex = headers.indexOf(Headline)+1;
Logger.log(Updating + ids.length + sitelinks);
for(var i = 0; i< ids.length; i++)
{
var idString = ids[i].toString();
var idArray = idString.split(^);
for(var j = 0; j<idArray.length; j++)
{
var id = idArray[j];
if(id == ) continue;
var rowNum = i+2;
var details = getSitelinkDetails(sheet, rowNum, headers, charLimits);
var sl = AdWordsApp.extensions().sitelinks().withIds([id]).get();
// Check char limits
var legit = true;
var entities = [hl,dl1, dl2, url];
for(var e in entities) if(details[entities[e]].length > charLimits[entities[e]]) legit = false;
if (!legit)
{
// Can’t update the sitelink as the new text is invalid
Logger.log(Sitelink text too long in row + rowNum + – extension not updated.);
problems.push(Sitelink text too long in row + rowNum + – extension not updated.);
}
else if(sl.hasNext())
{
sl = sl.next();
if(sl.getLinkText() !== details.hl) sl.setLinkText(details.hl);
if(details.dl1 == ) sl.clearDescription1();
else if(sl.getDescription1() !== details.dl1) sl.setDescription1(details.dl1);
if(details.dl2 == ) sl.clearDescription2();
else if(sl.getDescription2() !== details.dl2) sl.setDescription2(details.dl2);
if(sl.urls().getFinalUrl() !== details.url) sl.urls().setFinalUrl(details.url);
sl.setMobilePreferred(details.mobPref);
var vals = [sl.getLinkText(), sl.getDescription1(), sl.getDescription2(), sl.urls().getFinalUrl()];
sheet.getRange(rowNum, startIndex, 1, 4).setValues([vals]);
}
else
{
// Can’t find sitelink, so clear the ID and preview from the sheet
sheet.getRange(rowNum, 1).clear();
sheet.getRange(rowNum, startIndex, 1, 4).clear()
}
}
}
}
/**
* Pulls the existing callouts, substitutes vars
* If it can update existing callout it does
*
* @param Sheet sheet Sheet object containing callout details
* @param Object charLimits Character limits of each entity
*
* @return void
*/
function refreshCallouts(sheet, charLimits, problems)
{
var lr = sheet.getLastRow()
var idsArray = sheet.getRange(2,1,lr1).getValues();
// Flatten ids
var ids = [];
for(var i = 0; i < idsArray.length; i++)
{
if(idsArray[i][0] !== undefined) ids.push(idsArray[i][0]);
else ids.push();
}
var headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0];
var startIndex = headers.indexOf(Callout)+1;
Logger.log(Updating + ids.length + callouts);
for(var i = 0; i< ids.length; i++)
{
var idString = ids[i].toString();
var idArray = idString.split(^);
for(var j = 0; j<idArray.length; j++)
{
var id = idArray[j];
if(id == ) continue;
var rowNum = i+2;
var details = getCalloutDetails(sheet, rowNum, headers, charLimits);
var callout = AdWordsApp.extensions().callouts().withIds([id]).get();
// Check char limits
var legit = details.callout.length <= charLimits.callout;
if(!legit)
{
// Can’t update the callout as the new text is invalid
Logger.log(Callout text too long in row + rowNum + – extension not updated.);
problems.push(Callout text too long in row + rowNum + – extension not updated.);
}
else if(callout.hasNext())
{
callout = callout.next();
if(callout.getText() !== details.callout) callout.setText(details.callout);
callout.setMobilePreferred(details.mobPref);
var vals = [callout.getText()];
sheet.getRange(rowNum, startIndex).setValues([vals]);
}
else
{
// Can’t find the callout so clear ID and preview from the sheet
sheet.getRange(rowNum, 1).clear();
sheet.getRange(rowNum, startIndex).clear()
}
}
}
}
/**
* Reads the sitelink info on a row and returns it as an object
*
* @param Sheet sheet Sheet object containing Sitelink details
* @param Int rowNum The row number to fetch
* @param Array headers The header row loaded as array, used to get correct columns
* @param Object charLimits Character limits of each entity
*
* @return Object Object keyed by sitelink properties
*/
function getSitelinkDetails(sheet, rowNum, headers, charLimits)
{
var startIndex = headers.indexOf(Dynamic Headline);
var row = sheet.getRange(rowNum,1,1,sheet.getLastColumn()).getValues()[0];
var hl = row[startIndex].toString(),
dl1 = row[++startIndex].toString(),
dl2 = row[++startIndex].toString(),
url = row[++startIndex].toString(),
mobPref = row[++startIndex];
hl = subParamValues(hl, row, headers, charLimits.hl);
dl1 = subParamValues(dl1, row, headers, charLimits.dl1);
dl2 = subParamValues(dl2, row, headers, charLimits.dl2);
url = subParamValues(url, row, headers, charLimits.url);
mobPref = !(mobPref == No);
return {
hl:hl,
dl1:dl1,
dl2:dl2,
url:url,
mobPref:mobPref
};
}
/**
* Reads the callout info on a row and returns it as an object
*
* @param Sheet sheet Sheet object containing callout details
* @param Int rowNum The row number to fetch
* @param Array headers The header row loaded as array, used to get correct columns
* @param Object charLimits Character limits of each entity
*
* @return Object Object keyed by callout properties
*/
function getCalloutDetails(sheet, rowNum, headers, charLimits)
{
var startIndex = headers.indexOf(Dynamic Callout);
var row = sheet.getRange(rowNum,1,1,sheet.getLastColumn()).getValues()[0];
var callout = row[startIndex].toString(),
mobPref = row[++startIndex]
callout = subParamValues(callout, row, headers, charLimits.callout);
mobPref = !(mobPref == No);
return {callout:callout, mobPref:mobPref};
}
/**
* Searches the string for what vars to lookup
* Puts in var value if exists, default value otherwise
* If subbed value is too long, reverts to default
*
* @param String search String to search
* @param Int row The row the extension is on
* @param Array headers The header row loaded as array, used to get correct columns
* @param Int limit The character limit for the field
*
* @return String The final string value
*/
function subParamValues(search, row, headers, limit)
{
var regex = /{var[1-9]+:[^}]*}/g
var matches = search.match(regex);
var ans = search;
for(var i = 0; matches !== null && i<matches.length; i++)
{
var match = matches[i].split({)[1].split(})[0].split(:);
var varx = match[0];
var def = match[1];
var val = row[headers.indexOf(varx)];
var ans = search.replace(matches[i], def);
if(val != )
{
var ans = search.replace(matches[i], val);
if(ans.length <= limit) search = ans;
else
{
ans = search.replace(matches[i], def);
search = ans.trim();
}
}
}
return ans;
}
/**
* Creates a selector, reads sheet to apply appropriate conditions
* Applies extension to appropriate entities
* Does row by row, so new selector for each extension
*
* @param Sheet sheet Sheet object containing extension details
* @param Strin type Type of extension to add
*
* @return void
*/
function applyExtensions(sheet, type, problems)
{
var lr = sheet.getLastRow();
var lc = sheet.getLastColumn();
var headers = sheet.getRange(1,1,1,lc).getValues()[0];
var levelIndex = headers.indexOf(Entity level);
var includesIndex = headers.indexOf(Entity name contains);
var excludesIndex = headers.indexOf(Entity name excludes);
var parentIncludesIndex = headers.indexOf(Parent contains);
var parentExcludesIndex = headers.indexOf(Parent excludes);
var parentIsIndex = headers.indexOf(Parent is);
var isIndex = headers.indexOf(Entity name is);
var labelIndex = headers.indexOf(Entity has label);
var vals = sheet.getRange(1,1,lr, lc).getValues();
Logger.log(Applying +type+s to campaigns / ad groups);
rows:
for(var i = 1; i < vals.length; i++)
{
var row = vals[i];
if(row[0] === && !AdWordsApp.getExecutionInfo().isPreview())
{
Logger.log(Failed to generate +type+ from row +i);
problems.push(Failed to generate +type+ from row +i);
continue;
}
// Make the campaign/ad group selector
var selector;
var level = row[levelIndex];
switch(level) {
case Campaign :
selector = AdWordsApp.campaigns();
break;
case Ad Group :
selector = AdWordsApp.adGroups()
break;
default :
Logger.log(Invalid level for +type+ on row +(i+1));
problems.push(Invalid level for +type+ on row +(i+1));
continue rows;
}
var is = row[isIndex].toString().split().join(\’);
if(is !== )
{
selector.withCondition(Name = ‘+is+);
}
else
{
var includes = row[includesIndex].toString().split().join(\’);
var excludes = row[excludesIndex].toString().split().join(\’);
var label = row[labelIndex].toString().split().join(\’);
if(includes !== ) selector.withCondition(Name CONTAINS ‘+includes+);
if(excludes !== ) selector.withCondition(Name DOES_NOT_CONTAIN ‘+excludes+);
if(label !== ) selector.withCondition(LabelNames CONTAINS_ANY [‘+label+‘]);
}
if(level == Ad Group)
{
var parentIs = row[parentIsIndex].toString().split().join(\’);
if(parentIs !== )
{
selector.withCondition(CampaignName = ‘+parentIs+);
}
else
{
var parentInclude = row[parentIncludesIndex].toString().split().join(\’);
var parentExclude = row[parentExcludesIndex].toString().split().join(\’);
if(parentInclude !== ) selector.withCondition(CampaignName CONTAINS ‘+parentInclude+);
if(parentExclude !== ) selector.withCondition(CampaignName DOES_NOT_CONTAIN ‘+parentInclude+);
}
}
// Get the extension to apply
var ids = row[0].toString().split(^);
for(var j = 0; j<ids.length; j++)
{
var id = ids[j];
if(id == ) continue;
switch(type) {
case sitelink:
var extension = AdWordsApp.extensions().sitelinks().withIds([id]).get().next();
break;
case callout:
var extension = AdWordsApp.extensions().callouts().withIds([id]).get().next();
break;
default:
Logger.log(Invalid type +type+ inputed in applyExtensions);
return;
}
// Apply the extension to the campaigns/ad groups in the selector
var entities = selector.get();
while(entities.hasNext())
{
var entity = entities.next();
if(type == sitelink) entity.addSitelink(extension);
else entity.addCallout(extension);
}
}
}
}
/**
* Emails the problems that occurred during the script to the relevent recipients
*
* @param Object problems {type : [problems]}
* @param Array email Array of email addresses
* @param String subject Email subject line
*
* @return void
*/
function notify(problems, recipients, subject)
{
if (recipients.length == 0) {
// No one wants an email
return;
}
var message = ;
for(var type in problems)
{
var list = problems[type];
for(var i=0; i<list.length;i++)
{
message += type + :\t+list[i]+\n;
}
}
if (message == ) {
Logger.log(No problems to report.);
return;
}
message = The following problems were encountered during the script:\n + message;
MailApp.sendEmail(recipients.join(,), subject, message);
Logger.log(Error email sent to + recipients.join(, ));

}

Comments

comments