October 1, 2018
Keep track of your AdWords changes with labels and Slack
Stay up to date after changes even without experiments - here's how!
Despite of Google having released the new AdWords UI that includes the then highly anticipated notes function, it’s still not easy to keep an eye on all the small changes you make in the bunch of accounts you probably manage.
Of course, if you plan to test a different campaign setup with a lot of traffic you just set up an experiment and there you go.
But what about all this small stuff you do along the way? I mean, especially in well-established accounts that are “just running”. I’m talking about adding another keyword here, setting a device modifier there, and throwing in new ads from time to time.
The new notes function is not suitable to help you monitor such changes. And manual spreadsheet note-taking etc. isn’t the solution either. Wait. But why?
Because, depending on what entity you touch, i.e. high or low-volume keywords, there’s a big difference in when it makes sense to have a look into how the entity has performed after your changes. You change keyword X, and X generates 100 clicks per day → You better control that keyword on the same day. You change keyword Y, and Y generates 30 clicks per month → Relax for a while.
So, who really wants to jump into accounts and control all these small changes on a daily basis, each time first having to check for a sufficient amount of clicks to have happened after the changes for each single modification.
To the rescue, I introduce to you the power of AdWords Scripts, Google Firebase Database, and Slack, your favorite communication tool that even Google suggests to be combined with AdWords reporting.
You will get the script and short setup instructions in a second, let me just quickly explain how it works.
You will install the script and let it run hourly in your AdWords account. You will have a label in your account which you use to flag all entities (by default, keywords, adgroups, and ads are supported) that you want to monitor. Let’s name this label CONTROL for now.
So you change a keyword in some way and then you put the label CONTROL on that keyword. When the script runs the next time, it will notice that you labelled a new keyword with CONTROL and it will write the hour and some other details into a database. Now each time the script runs, it will have a look into your CONTROL entities to see whether they trigger a slack reporting condition. By default, the script knows two types of triggers, time and stats-based.
A stats-based trigger by default is clicks|30, which means you will get performance stats to your slack channel as soon as the entity passes 30 clicks after having received the CONTROL label.
The default time-based trigger is weeks|1–4, that is, starting from 1 week after the entity has received the label, performance stats will be sent to your slack channel on a weekly basis until 4 weeks will have passed.
The stats in slack will compare the period that passed after setting the label to the period of the same length prior to setting the label. For the stats-based trigger this can be very dynamic with one day being the smallest interval, though. The default time-based trigger will always compare full weeks (7 days).
The following screenshot shows a slack output for a keyword that was newly created and received the CONTROL label (that’s why all comparison stats show 0).
Once you want to stop monitor an entity you just remove the label.
Setup instructions
Login to Google Firebase (it’s free), create a project and a realtime database within that project. Edit the database rules to only allow authenticated access:
{
“rules”: {
“.read”: “auth != null”,
“.write”: “auth != null”
}
}
Note the database URL and the database secret:
Create a Slack App for your Slack workspace and activate incoming webhooks. Note the Hook URL that you can find under Apps Management → Custom Integrations → Incoming WebHooks
Copy the script at the end of this story and add it to your AdWords account, put in the needed Firebase and Slack config data.
Configure the stats-based trigger and the time-based trigger according to your preference.
Activate hourly running for the script.
You can now enjoy labeling entities in your account and automatically receiving stats in Slack. For the best experience you should style the Slack output a bit :) All needed information is provided by Slack.
Script source
/**
* AdWords Performance Monitoring in Slack via Labels
* @author: Dustin Recko
*
*/
// Config Section //>
var DB_URL = 'https://...'; // The Firebase Database URL
var DB_AUTH = 'xxx'; // The Firebase Database Secret
var SLACK_HOOK = 'https://...'; // The Slack Hook URL
var SLACK_CHANNEL = '#adwords'; // The Slack Channel
var SLACK_EMOJI = ':smile:'; // The Slack Emoji
var TRIGGER = {
clicks: 30, //Stats as a trigger with a specified threshold
weeks: [1,4] //Time as a trigger with a start and end threshold
};
var LABEL_NAME = "CONTROL"; // The name of the label used in AdWords to activate monitoring for Keywords, AdGroups, and Ads
var NOW = new Date();
// End of Config <//
function main() {
init();
var ACC = AdWordsApp.currentAccount().getName().split(" ")[0];
var myDb = new firebase(DB_URL,DB_AUTH);
var myDbJson = myDb.getJson(LABEL_NAME+'/'+ACC) || nest({},[LABEL_NAME,ACC]);
var mySlack = new slack(SLACK_HOOK,SLACK_CHANNEL,SLACK_EMOJI);
var myLabel = AdWordsApp.labels().withCondition("Name = '"+LABEL_NAME+"'").get().next();
var process = {
"keywords": myLabel.keywords().get(),
"adGroups": myLabel.adGroups().get(),
"ads": myLabel.ads().get()
};
/// Main process
for(var handler in process) {
/// Check items with the label
while(process[handler].hasNext()) {
var h = process[handler].next();
if(!myDbJson[handler] || !myDbJson[handler][h.getId()]) {
var obj = {
"name" : (h.getText instanceof Function) ? h.getText() : ((h.getDescription1 instanceof Function) ? h.getDescription1() : h.getName()),
"campaign": h.getCampaign().getName(),
"qsStart" : (h.getQualityScore instanceof Function) ? h.getQualityScore() : 0,
"started" : NOW.getTime(),
"trigger": initTrigger(TRIGGER)
};
myDb.patch(obj,LABEL_NAME+'/'+ACC+'/'+handler+'/'+h.getId());
myDbJson = nest(myDbJson,[handler,h.getId()]);
} else {
for(var i in TRIGGER) {
switch(typeof(myDbJson[handler][h.getId()].trigger[i])) {
case "boolean":
if(!myDbJson[handler][h.getId()].trigger[i]) {
if(statsBasedCheck(handler,h,i,myDbJson[handler][h.getId()])) {
var status = {};
status[i] = true;
myDb.patch(status,LABEL_NAME+'/'+ACC+'/'+handler+'/'+h.getId()+'/trigger');
}
}
break;
case "number":
var weeksPassed = Math.round((NOW.getTime() - myDbJson[handler][h.getId()].started)/(1000*60*60*24)) / 7;
if(weeksPassed%1 === 0 && TRIGGER[i][0] <= weeksPassed && weeksPassed <= TRIGGER[i][1] && weeksPassed > myDbJson[handler][h.getId()].trigger[i]) {
timeBasedCheck(handler,h,i,myDbJson[handler][h.getId()]);
var status = {};
status[i] = weeksPassed;
myDb.patch(status,LABEL_NAME+'/'+ACC+'/'+handler+'/'+h.getId()+'/trigger');
}
break;
}
}
}
/// Flag processed items
myDbJson[handler][h.getId()].flag = true;
}
/// Cleanup no longer labelled items, i.e., non-flagged
for(var i in myDbJson[handler]) {
if(myDbJson[handler][i].flag == undefined)
myDb.purge(LABEL_NAME+'/'+ACC+'/'+handler+'/'+i);
}
}
/// Some functions
function init() {
Date.prototype.yyyymmdd = function(days) {
if(days) {
this.setDate(this.getDate() + days);
}
return Utilities.formatDate(this, AdWordsApp.currentAccount().getTimeZone(),'yyyyMMdd');
}
}
function firebase(db,auth) {
this.db = db;
this.auth = auth;
this.patch = function(payload,path) {
path = path+'/.json?auth=';
var options = {
"method" : "patch",
"payload" : JSON.stringify( payload )
};
UrlFetchApp.fetch(this.db+path+this.auth,options);
}
this.purge = function(path) {
path = path+'/.json?auth=';
var options = {
"method": "delete"
};
UrlFetchApp.fetch(this.db+path+this.auth,options);
}
this.getJson = function(path) {
path = path+'/.json?auth=';
return JSON.parse(
UrlFetchApp
.fetch(this.db+path+this.auth)
.getContentText()
);
}
}
function slack(hook,channel,emoji) {
this.hook = hook;
this.channel = channel;
this.emoji = emoji;
this.msg = function(payload) {
payload.channel = this.channel;
payload.icon_emoji = this.emoji;
var options = {
method: "POST",
contentType: 'application/json',
payload: JSON.stringify(payload)
};
UrlFetchApp.fetch(this.hook,options);
}
}
function initTrigger() {
var obj = {};
for(var i in TRIGGER) {
if(typeof(TRIGGER[i]) == "number")
obj[i] = false
else
obj[i] = 0
}
return obj;
}
function statsBasedCheck(type,handler,trigger,dbData) {
var sh = handler.getStatsFor(new Date(dbData.started).yyyymmdd(),NOW.yyyymmdd());
var stats = {
avgCpc: sh.getAverageCpc().toFixed(2),
avgPos: sh.getAveragePosition(),
clicks: sh.getClicks(),
conversions: sh.getConversions(),
cost: sh.getCost(),
qs: (type == 'keywords') ? handler.getQualityScore() : 0
};
if(stats[trigger] >= TRIGGER[trigger]) {
var daysPassed = Math.round((NOW.getTime() - dbData.started)/(1000*60*60*24));
var sh = handler.getStatsFor(new Date(dbData.started).yyyymmdd(daysPassed*-1),new Date(dbData.started).yyyymmdd());
var beforeStats = {
avgCpc: sh.getAverageCpc().toFixed(2),
avgPos: sh.getAveragePosition(),
clicks: sh.getClicks(),
conversions: sh.getConversions(),
cost: sh.getCost(),
qs: dbData.qsStart
};
var attachments = [];
for(var s in stats) {
attachments.push({
title: s,
text: 'is '+stats[s]+ (stats[s]>=beforeStats[s] ? ' (up' : '(down') +' from '+beforeStats[s]+')'
});
}
mySlack.msg({
text: LABEL_NAME+" > "+ACC+" > "+type+" > "+dbData.name+" in "+dbData.campaign+" passed "+stats[trigger]+" "+trigger+" in "+daysPassed+" days (was "+beforeStats[trigger]+" in the previous period)",
attachments: attachments
});
return true;
}
return false;
}
function timeBasedCheck(type,handler,trigger,dbData) {
var sh = handler.getStatsFor(new Date(dbData.started).yyyymmdd(),NOW.yyyymmdd());
var stats = {
avgCpc: sh.getAverageCpc().toFixed(2),
avgPos: sh.getAveragePosition(),
clicks: sh.getClicks(),
conversions: sh.getConversions(),
cost: sh.getCost(),
qs: (type == 'keywords') ? handler.getQualityScore() : 0
};
var sh = handler.getStatsFor(new Date(dbData.started).yyyymmdd((dbData.trigger[trigger]+1)*-7),new Date(dbData.started).yyyymmdd());
var beforeStats = {
avgCpc: sh.getAverageCpc().toFixed(2),
avgPos: sh.getAveragePosition(),
clicks: sh.getClicks(),
conversions: sh.getConversions(),
cost: sh.getCost(),
qs: dbData.qsStart
};
var attachments = [];
for(var s in stats) {
attachments.push({
title: s,
text: 'is '+stats[s]+ (stats[s]>=beforeStats[s] ? ' (up' : '(down') +' from '+beforeStats[s]+')'
});
}
mySlack.msg({
text: LABEL_NAME+" > "+ACC+" > "+type+" > "+dbData.name+" in "+dbData.campaign+" passed "+(dbData.trigger[trigger]+1)+" "+trigger,
attachments: attachments
});
}
// by Jason Knight
function nest(base,arr) {
for (var obj = base, ptr = obj, i = 0, j = arr.length; i < j; i++)
ptr = (ptr[arr[i]] = {});
return obj;
}
}