Brianlabs is hiring

Label Your Keywords With Organic Rank Data

Label Your Keywords With Organic Rank Data

Fill out a Google Docs spreadsheet with the organic positions of your site and a few competitors, and this script will label up any matching keywords in your account with those positions. This means it’s easy to analyse how SEO is affecting your PPC performance!

If you’ve not run a script before please read our Introduction to AdWords Scripts. For more open-source fun check out our AdWords Scripts directory. And if you want to use some of our technology that’s arrived back from the future then sign up for one of our paid plans on the Brainlabs Tech Stack.

First set up a new Google Docs spreadsheet, and set up Sheet1 like this:
OrganicRankDataSheet
The first row is the headers – fill in your name and your competitors’ names. Then the first column is the keyword text, followed by the organic positions of the sites.

(Technically the first column is the search query, not the keyword text – but the script only looks at exact keywords, where the two are the same, so that the PPC data will be accurate.)

If given this sheet, the script will label the keyword ‘search query’ with ‘Us – 3’, ‘Competitor 1 – 1’ and ‘Competitor 2 – 6’. If a position is blank or over 9, the script will assume that means the site was not on the first page of results: the keyword ‘keyword’ would be labelled with ‘Us – Off First Page’, ‘Competitor 1 – 6’ and ‘Competitor 2 – Off First Page’.

If you want to do a before/after analysis, you could put in your current ranks and previous ranks, like this:
OrganicRankDataSheet2

Then go to your AdWords account and copy and paste in the script below. There are a few settings to change:

  • spreadsheetUrl is the URL of your spreadsheet.
  • Use campaignNameContains and campaignNameDoesNotContain if you only want to label keywords in some campaigns.
  • Set includePaused to true if you want to label paused keywords, in paused ad groups and campaigns.

Then authorise the script, hit Preview to make sure it runs correctly – and then you’re all set.

Where Does That Ranking Data Come From?

To get the data, we’ve been using SEMrush. To find which keywords’ positions have changed recently, type in your domain in the search bar, then go to ‘Position Changes’ in the Organic Research section. Then click Improved or Declined to see which keywords have better or worse position since the previous month. If you’re using a paid plan and want historic data, then change the month you’re looking at in the ‘Historic Data’ drop down at the top right.
semrush position changes

It’s not available in the free version, but if you’re paying you can use the API to download their data. There are handy instructions on how to do that in Google Sheets on the SEMrush blog. However we recommend that you only access their API from a temporary spreadsheet, and delete it afterwards – otherwise you may accidentally use up your API credits, as Google automatically redownloads imported data every so often.

If you want the first nine domains in the organic ranks for a particular keyword, put your keyword in A2 and this in B2:
TRANSPOSE(IMPORTDATA("http://api.semrush.com/?type=phrase_organic&key=YOUR-KEY-HERE&display_limit=9&export_columns=Dn&phrase="&A2&"&database=uk"))
You have to replace YOUR-KEY-HERE with your actual key, of course. And if you’re not in the UK you’ll want to change ‘database=uk’.
To get historic data use
TRANSPOSE(IMPORTDATA("http://api.semrush.com/?type=phrase_organic&key=YOUR-KEY-HERE&display_limit=9&export_columns=Dn&phrase="&A2&"&database=uk&display_date=20140815"))
Replacing ‘20140815’ with the wanted date (in ‘yyymmdd’ format).

You’ll get something like
semrush api1
For costing purposes each domain counts as a ‘line’: each keyword will cost you 90 API credits for current data or 450 credits for historic data.

To turn use that with the script, you can just add some extra columns to the side, headed with the sites you’re interested in, and use the MATCH function to find what the rank number is.
=iferror(match(N$1,$C2:$K2,0),11)
semrush api2
Then you can copy that and paste as values into the spreadsheet you’re using for the script, and delete the spreadsheet with the API calls in.

Follow us on Twitter and Facebook for more Brainlabs fun and updates.

The Script

/**
*
* Label Your Keywords With Organic Rank Data
*
* This script takes ranking data from a spreadsheet and uses it to label exact
* match keywords, for manual data analysis.
*
* Version: 1.0
*
* Google AdWords Script maintained on brainlabsdigital.com
*
**/

function main() {

  var spreadsheetUrl = "https://docs.google.com/YOUR-SPREADSHEET-URL-HERE";
  // The URL of the spreadsheet containing your ranking data

  var campaignNameContains = "";
  // Use this if you want to only label keywords in particular campaigns.
  // For example setting it to "Generic" would mean only keywords in campaigns
  // with 'Generic' in the name would be labelled.
  // Leave as "" if unwanted.

  var campaignNameDoesNotContain = "";
  // Use this if you want to ignore particular campaigns.
  // For example setting it to "Brand" then keywords in any campaigns with
  // 'brand' in the name would not be labelled.
  // Leave as "" if unwanted.

  var includePaused = false;
  // Set to true to include paused campaigns, ad groups and keywords
  // Set to false to ignore them and only label what is currently enabled.

  ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

  // Read the spreadsheet
  try {
    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  } catch (e) {
    Logger.log("Problem with the spreadsheet URL: '" + e + "'");
    Logger.log("Make sure you have correctly copied in your own spreadsheet URL.");
    return;
  }
  var sheet = spreadsheet.getSheets()[0];
  var spreadsheetData = sheet.getDataRange().getValues();

  var keywordTextArray = [];
  var keywordLabels = {};
  var sites = [];
  var neededLabels = [];

  for(var i=1; i<spreadsheetData[0].length; i++) {
    var siteName = spreadsheetData[0][i].trim();
    if (siteName != "") {
      sites.push(siteName);
    }
  }

  for(var i=1; i<spreadsheetData.length; i++) {
    var keyword = spreadsheetData[i][0].trim().toLowerCase();

    if (keyword == "") {
      continue;
    }

    keywordTextArray.push(keyword);
    keywordLabels[keyword] = [];

    for(var j=0; j<sites.length; j++) {
      var position = parseInt(spreadsheetData[i][j+1],10);

      if (isNaN(position) || position < 1 || position > 9) {
        var labelText = sites[j] + " - Off First Page";
      } else {
        var labelText = sites[j] + " - " + position;
      }

      keywordLabels[keyword].push(labelText);
      if (neededLabels.indexOf(labelText) < 0) {
        neededLabels.push(labelText);
      }
    }
  }

  Logger.log(keywordTextArray.length + " keywords found in spreadsheet.");

  var existingLabels = {};
  var labelIds = {};
  var labelIter = AdWordsApp.labels().get();

  while (labelIter.hasNext()) {
    var label = labelIter.next();
    existingLabels[ label.getName() ] = label;
  }

  // Remove existing position labels, to get rid of any outdated ones
  for (var i=0; i<sites.length; i++) {
    for (var j=0; j<10; j++) {
      labelText = sites[i] + " - " + j;
      if (existingLabels[labelText] != undefined) {
        existingLabels[labelText].remove();
      }
    }
    labelText = sites[i] + " - Off First Page";
    if (existingLabels[labelText] != undefined) {
      existingLabels[labelText].remove();
    }
  }

  // Create the necessary labels
  for (var i=0; i<neededLabels.length; i++) {
    AdWordsApp.createLabel(neededLabels[i]);
  }

  // Make the iterator to get the keywords
  var keywordIter = AdWordsApp.keywords()
  .withCondition("Criteria IN ['" + keywordTextArray.join("','") + "']")
  .withCondition("KeywordMatchType = EXACT");

  if (campaignNameContains != "") {
    keywordIter = keywordIter.withCondition("CampaignName CONTAINS_IGNORE_CASE '" + campaignNameContains + "'");
  }
  if (campaignNameDoesNotContain != "") {
    keywordIter = keywordIter.withCondition("CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '" + campaignNameDoesNotContain + "'");
  }

  if (includePaused) {
    keywordIter = keywordIter.withCondition("CampaignStatus IN [ENABLED, PAUSED]")
    .withCondition("AdGroupStatus IN [ENABLED, PAUSED]")
    .withCondition("Status IN [ENABLED, PAUSED]");
  } else {
    keywordIter = keywordIter.withCondition("CampaignStatus IN [ENABLED]")
    .withCondition("AdGroupStatus IN [ENABLED]")
    .withCondition("Status IN [ENABLED]");
  }

  keywordIter = keywordIter.get();

  // Apply the labels
  while (keywordIter.hasNext()) {
    var keyword = keywordIter.next();
    var keywordText = keyword.getText().replace("[","").replace("]","").trim().toLowerCase();

    if (keywordLabels[keywordText] == undefined) {
      Logger.log(keywordText + " not found");
      continue;
    }

    for (var i=0; i<keywordLabels[keywordText].length; i++) {
      var labelText = keywordLabels[keywordText][i];
      keyword.applyLabel(keywordLabels[keywordText][i]);
    }
  }

}

Share this post