Keep track of your AdWords changes with labels and Slack

October 1, 2018

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).

The slack output (very plain by default)

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;
  }

}
Neugierig geworden?
Skalieren auch Sie mit der richtigen Marketing Technology. Sprechen Sie uns an.