Saturday, April 27, 2013

Put Current Crime Statistics in Your Creatives

I live in Chicago and one of the things this city is known for is it's colorful history of Organized Crime. So I thought it might be interesting to see if I could use that history to my advantage.

Chicago runs a website that collects and aggregates the statistics of the city: City of Chicago Data Portal. Here you can find all sorts of data sets, but the one I was interested in was the Crime Stats.

After a little exploration, I found that the data is available in JSON (an easy to consume format for software). So I put together a script to read that data and then automatically update the keyword AdParams with the total count of the different types of crime.

This might come in handy for any companies that sell insurance or security systems, but I'm sure there's other companies that might be able to use this info.

Other major cities in the US keep similar sets of data as well:
What other public records data could your business use to target your customers? Let me know in the comments and I'll take a look.

Thanks,
Russ

//-----------------------------------
// Put Chicago Crime Stats in Your Creatives
// Created By: Russ Savage
// FreeAdWordsScripts.com
//-----------------------------------
function main() {
  // You can get this link by going here: http://goo.gl/tfNgM
  // Apply some filters and then click export > api
  // This end point is good for all of 2013
  var DATA_ENDPOINT = "http://data.cityofchicago.org/resource/pga9-zdiw.json";
  var CAMPAIGN_PREFIX = 'Crime_Data_Chicago_'; //All your campaigns start with this
  var AD_PARAM = 1; // 1 or 2
  
  try {
    var json = Utilities.jsonParse(UrlFetchApp.fetch(DATA_ENDPOINT).getContentText());
    var summary = summarizeCrimeStats(json);
    //logCrimeSummary(summary);
    
    for(var i in summary) {
      var total = totalPrimaryDescription(i,summary);
      var kw_iter = AdWordsApp.keywords()
                      .withCondition("CampaignName CONTAINS_IGNORE_CASE '"+CAMPAIGN_PREFIX+i+"'")
                      .get();
      while(kw_iter.hasNext()) {
        var kw = kw_iter.next();
        kw.setAdParam(AD_PARAM, total);
      }
    }
  }catch(e) {
  }
}

// A helper function to aggregate the data by primary description
function totalPrimaryDescription(key,summary) {
  var tot = 0;
  for(var i in summary[key]) {
    tot += summary[key][i];
  }
  return tot;
}

//This function takes in a json formatted object and stores the count of instances
//in a 2 dimentional hash of [Primary Description][Secondary Description]
function summarizeCrimeStats(json) {
  var crime_summary = {};
  for(var i in json) {
    var crime = json[i];
    if(crime_summary[crime._primary_decsription]) {
      if(crime_summary[crime._primary_decsription][crime._secondary_description]) {
        crime_summary[crime._primary_decsription][crime._secondary_description]++;
      }else{
        crime_summary[crime._primary_decsription][crime._secondary_description] = 1;
      }
    }else{
      crime_summary[crime._primary_decsription] = {};
      crime_summary[crime._primary_decsription][crime._secondary_description] = 1;
    }
  }
  return crime_summary;
}

//Just a helper function to print out the summary info so that
//I can find the data I'm interested in.
function logCrimeSummary(crime_summary) {
  for(var i in crime_summary) {
    for(var x in crime_summary[i]) {
      Logger.log([i,x,crime_summary[i][x]].join(', '));
    }
  }
}

Monday, April 22, 2013

Store Account, Campaign, AdGroup, and Keyword Level Quality Score

UPDATE: 2014-02-15 - I updated the script to v2.0 which speeds the script up dramatically, includes the ability to store data into a CSV and use whatever date range you like for the stats.

I've had a lot of good feedback on my previous script: Store Account Level Quality Score. To that end, I've been asked a few times about storing Campaign and/or AdGroup level quality scores as well so I figured it would be a good time for an update.

Below is a script that will attempt to store Account, Campaign, and AdGroup level quality scores for the top 50000 keywords in your account. It will store the results in a google spreadsheet. In order for this to work, you will need to set up a new google spreadsheet. with three sheets named Account, Campaign, and AdGroup. You can simply make a copy of my spreadsheet found here (File > Make a copy...) : Account, Campaign, AdGroup Quality Score Spreadsheet (No longer needed as of v1.1)

Thanks,
Russ

Thursday, April 18, 2013

Pause or Enable Campaigns, Keywords or Ads on a Specific Date

UPDATE 2013-12-13: Added the ability to work on Ads as well.

I've seen some questions posted around the forums asking about enabling or pausing keywords on a given day. It is pretty simple to do using labels and scripts. The following script will run through your account and look for keywords labeled with "Pause on " or "Enable on " and perform that action. If you would like to just use dates, you can set the two prefix values to be empty strings (""). The format of the date is YYYY-MM-DD (2013-05-01).

Thanks,
Russ

/**************************************************
* Pause or Enable Campaigns, Keywords or Ads on a Given Date
* Version 1.2
* Changelog v1.2 - Added ability to pause Campaigns
* Changelog v1.1 - Added ability to run on Ads
* Created By: Russ Savage
* FreeAdWordsScripts.com
**************************************************/
var ENTITY = 'Keyword'; //or Ad or Campaign
var PAUSE_PREFIX = "Pause on "; //look for labels "Pause on 2013-04-11"
var ENABLE_PREFIX = "Enable on "; //look for labels "Enable on 2013-04-11"

  
function main() {
  var todayStr = Utilities.formatDate(new Date(), AdWordsApp.currentAccount().getTimeZone(), "yyyy-MM-dd");
  var pauseStr = PAUSE_PREFIX+todayStr;
  var enableStr = ENABLE_PREFIX+todayStr;
  Logger.log("Looking for labels: " + [pauseStr,enableStr].join(' and '));
   
  var labelsArray = buildLabelArray(pauseStr,enableStr);
   
  if(labelsArray.length > 0) { 
    var labelsStr = "['" + labelsArray.join("','") + "']";
    var entityIter;
    if(ENTITY === 'Keyword') {
      entityIter = AdWordsApp.keywords().withCondition("LabelNames CONTAINS_ANY "+labelsStr).get();
    } else if(ENTITY === 'Ad') {
      entityIter = AdWordsApp.ads().withCondition("LabelNames CONTAINS_ANY "+labelsStr).get();
    } else if(ENTITY === 'Campaign') {
      entityIter = AdWordsApp.campaigns().withCondition("LabelNames CONTAINS_ANY "+labelsStr).get();
    } else {
      throw 'Invaid ENTITY type. Should be Campaign, Keyword or Ad. ENTITY:'+ENTITY;
    }
     
    while(entityIter.hasNext()) {
      var entity = entityIter.next();
      pauseEntity(entity, pauseStr);
      enableEntity(entity, enableStr);
    }
  }
}
 
//Helper function to build a list of labels in the account
function buildLabelArray(pauseStr,enableStr) {
  var labelsArray = [];
  try {
    var labelIter = AdWordsApp.labels().withCondition("Name IN ['"+pauseStr+"','"+enableStr+"']").get();
    while(labelIter.hasNext()) {
      labelsArray.push(labelIter.next().getName());
    }
    return labelsArray;
  } catch(e) {
    Logger.log(e);
  }
  return [];
}
 
//Helper function to pause entities
function pauseEntity(entity, pauseStr) {
  var labelIter = entity.labels().withCondition("Name = '"+pauseStr+"'").get();
  if(labelIter.hasNext()) {
    entity.pause();
    entity.removeLabel(pauseStr);
  }
}
 
//Helper function to enable entities
function enableEntity(entity, enableStr) {
  var labelIter = entity.labels().withCondition("Name = '"+enableStr+"'").get();
  if(labelIter.hasNext()) {
    entity.enable();
    entity.removeLabel(enableStr);
  }
}

Sunday, April 14, 2013

Merge Labels from Multiple Campaigns

This script is a follow up to one of my previous scripts: Merge Multiple Campaigns Together For Enhanced Campaigns Migration. This one is meant to run after you have merged your campaigns together and it will copy all the labels from the campaigns, adgroups, ads, and keywords from the ORIGIN_CAMAPIGN_NAMES into the DESTINATION_CAMPAIGN_NAME.

Thanks,
Russ

//-----------------------------------
// Merge Labels from Multiple Campaigns
// Created By: Russ Savage
// FreeAdWordsScripts.com
//-----------------------------------
var DESTINATION_CAMPAIGN_NAME = "Destination Campaign Name";
var ORIGIN_CAMPAIGN_NAMES = ["Origin Campaign Name 1","Origin Campaign Name 2"];

function main() {
  var label_iter = AdWordsApp.labels().get();
  while(label_iter.hasNext()) {
    var label = label_iter.next();
    //Pre-build all the iterators
    var iters = [
      label.campaigns().withCondition("Name IN ['"+ORIGIN_CAMPAIGN_NAMES.join("','")+"']").get(),
      label.adGroups().withCondition("CampaignName IN ['"+ORIGIN_CAMPAIGN_NAMES.join("','")+"']").get(),
      label.ads().withCondition("CampaignName IN ['"+ORIGIN_CAMPAIGN_NAMES.join("','")+"']").get(),
      label.keywords().withCondition("CampaignName IN ['"+ORIGIN_CAMPAIGN_NAMES.join("','")+"']").get()
    ];
    for(var i in iters) {
      var iter = iters[i];
      while(iter.hasNext()) {
        _copyLabels(iter.next()); 
      }
    }
  }
}

//Copies the labels from entity in Origin campaign 
//to entity with the same name in dest campaign
function _copyLabels(entity) {
  var iter;
  if(_getEntityType(entity) == "Campaign") {
    // it's a campaign
    iter = AdWordsApp.campaigns()
             .withCondition("Name = '"+DESTINATION_CAMPAIGN_NAME+"'")
             .get();
  } else if(_getEntityType(entity) == "AdGroup") {
    // it's an adgroup
    iter = AdWordsApp.adGroups()
             .withCondition("CampaignName = '"+DESTINATION_CAMPAIGN_NAME+"'")
             .withCondition("Name = '"+entity.getName()+"'")
             .get();
  } else if(_getEntityType(entity) == "Ad") {
    // it's an ad
    iter = AdWordsApp.ads()
             .withCondition("CampaignName = '"+DESTINATION_CAMPAIGN_NAME+"'")
             .withCondition("AdGroupName = '"+entity.getAdGroup().getName()+"'")
             .withCondition("Headline = '"+entity.getHeadline()+"'")
             .withCondition("Description1 = '"+entity.getDescription1()+"'")
             .withCondition("Description2 = '"+entity.getDescription2()+"'")
             .withCondition("DisplayUrl = '"+entity.getDisplayUrl()+"'")
             .get();
  } else if(_getEntityType(entity) == "Keyword") {
    // it's a keyword
    iter = AdWordsApp.keywords()
             .withCondition("CampaignName = '"+DESTINATION_CAMPAIGN_NAME+"'")
             .withCondition("AdGroupName = '"+entity.getAdGroup().getName()+"'")
             .withCondition("Text = '"+entity.getText()+"'")
             .withCondition("KeywordMatchType = '"+entity.getMatchType()+"'")
             .get();
  }
  
  while(iter.hasNext()) {
    _copyLabelsHelper(entity,iter.next());
  }
  
}

//Copy the labels form orig entity to dest entity
function _copyLabelsHelper(orig,dest) {
  var label_iter = orig.labels().get();
  while(label_iter.hasNext()) {
    dest.applyLabel(label_iter.next().getName());
  }
}

//Returns a text representation of an entity
//For a better way, check: http://goo.gl/kZL3X
function _getEntityType(obj) {
  if(typeof(obj['getCampaign']) == "undefined") {
    return 'Campaign';
  }
  if(typeof(obj['getAdGroup']) == "undefined") {
    return 'AdGroup';
  }
  if(typeof(obj['getHeadline']) != "undefined") {
    return "Ad";
  }
  if(typeof(obj['getText']) != "undefined") {
    return "Keyword";
  }
  return null;
}

Wednesday, April 10, 2013

Report on Broken Urls In Your Account

Note: If you are looking for a version of this script to run at the MCC level, check out Monitor Broken Links Using MCC Level Scripts.

UPDATED: 2013-05-20: Based on a comment from a reader, the script now only checks active campaigns and adgroups and it also only checks each url once.

UPDATED: 2013-04-28: Based on a comment from a reader, I made some updates to this script which include adding the response code to the email and formatting the results as an attachment.

It happens to the best of us. Sometimes, we remove pages on our site or update links and forget to make the corresponding change our SEM accounts. So tonight I put together a quick script to run through all your ads and keywords and create an email report with any of them that return a 404 Not Found or a 500 Server Error response code. You can easily add more error codes to check for by adding them to the BAD_CODES array at the beginning of the script.

Thanks,
Russ

/****************************
* Find Broken Urls In Your Account
* Version 1.1
* ChangeLog v1.1
*  - Updated to only see Text Ads
* Created By: Russ Savage
* FreeAdWordsScripts.com
****************************/
function main() {
  // You can add more if you want: http://goo.gl/VhIX
  var BAD_CODES = [404,500];
  var TO = ['email@example.com'/*,'email_address_2@example.com'*/];
  var SUBJECT = 'Broken Url Report - ' + _getDateString();
  var HTTP_OPTIONS = {
    muteHttpExceptions:true
  };
   
  //Let's look at ads and keywords for urls
  var iters = [
    //For Ad Level Urls
    AdWordsApp.ads()
      .withCondition("Status = 'ENABLED'")
      .withCondition("AdGroupStatus = 'ENABLED'")
      .withCondition("CampaignStatus = 'ENABLED'")
      .withCondition("Type = 'TEXT_AD'")
      .get(),
    //For Keyword Level Urls
    AdWordsApp.keywords()
      .withCondition("Status = 'ENABLED'")
      .withCondition("DestinationUrl != ''")
      .withCondition("AdGroupStatus = 'ENABLED'")
      .withCondition("CampaignStatus = 'ENABLED'")
      .get()
    ];
  
  var already_checked = {}; 
  var bad_entities = [];
  for(var x in iters) {
    var iter = iters[x];
    while(iter.hasNext()) {
      var entity = iter.next();
      if(entity.getDestinationUrl() == null) { continue; }
      var url = entity.getDestinationUrl();
      if(url.indexOf('{') >= 0) {
        //Let's remove the value track parameters
        url = url.replace(/\{[0-9a-zA-Z]+\}/g,'');
      }
      if(already_checked[url]) { continue; }
      var response_code;
      try {
        Logger.log("Testing url: "+url);
        response_code = UrlFetchApp.fetch(url, HTTP_OPTIONS).getResponseCode();
      } catch(e) {
        //Something is wrong here, we should know about it.
        bad_entities.push({e : entity, code : -1});
      }
      if(BAD_CODES.indexOf(response_code) >= 0) {
        //This entity has an issue.  Save it for later. 
        bad_entities.push({e : entity, code : response_code});
      }
      already_checked[url] = true;
    }
  }
  var column_names = ['Type','CampaignName','AdGroupName','Id','Headline/KeywordText','ResponseCode','DestUrl'];
  var attachment = column_names.join(",")+"\n";
  for(var i in bad_entities) {
    attachment += _formatResults(bad_entities[i],",");
  }
  if(bad_entities.length > 0) {
    var options = { attachments: [Utilities.newBlob(attachment, 'text/csv', 'bad_urls_'+_getDateString()+'.csv')] };
    var email_body = "There are " + bad_entities.length + " urls that are broken. See attachment for details.";
     
    for(var i in TO) {
      MailApp.sendEmail(TO[i], SUBJECT, email_body, options);
    }
  }  
}
 
//Formats a row of results separated by SEP
function _formatResults(entity,SEP) {
  var e = entity.e;
  if(typeof(e['getHeadline']) != "undefined") {
    //this is an ad entity
    return ["Ad",
            e.getCampaign().getName(),
            e.getAdGroup().getName(),
            e.getId(),
            e.getHeadline(),
            entity.code,
            e.getDestinationUrl()
           ].join(SEP)+"\n";
  } else {
    // and this is a keyword
    return ["Keyword",
            e.getCampaign().getName(),
            e.getAdGroup().getName(),
            e.getId(),
            e.getText(),
            entity.code,
            e.getDestinationUrl()
           ].join(SEP)+"\n";
  }
}
 
//Helper function to format todays date
function _getDateString() {
  return Utilities.formatDate((new Date()), AdWordsApp.currentAccount().getTimeZone(), "yyyy-MM-dd");
}

Friday, April 5, 2013

Dynamically Adjust Campaign Budgets v2.0

UPDATE 2013-04-06: Made sure daily budgets are multiplied by 30.5 to turn them into monthly budgets (line 150).

UPDATE 2013-04-06: Made sure that when the budgets are reset, they are divided by 30.5 to turn them into daily budgets (line 95).

UPDATE 2013-04-07: A big thank you to FoxSUP for helping me track down an issue with updating the budgets. Fixed line 78 to multiply the current budget by 1+to_change instead of just to_change. Also fixed a bug in calculating the change(line 56).


This is an update to a previous script I put together to dynamically adjust campaign budgets. There have been a few asks from some of the comments and I figured it would be easier to combine them into a new post.

The script will now keep track of the budgets you set at the beginning of the month via a Google Spreadsheet. You can also fill in the LABEL value if you only want this to operate on campaigns with that label name. Leaving it blank operates on all campaigns.

Thanks,
Russ

/********************************
* Dynamically Adjust Campaign Budgets v2.1
* Changelog v2.1 - Fixed opening of spreadsheet
* Created By: Russ Savage
* FreeAdWordsScripts.com
********************************/
// Let's set some constants
var TIMEFRAME = "THIS_MONTH";
//if the campaign is not in the spreadsheet, the budget is reset
//to this value at the beginning of the month.
var DEFAULT_BUDGET = 100; 
var SPREADSHEET_URL = "PLACE EMPYT SPREADSHEET URL HERE";
var LABEL = ""; //Fill in if you only want to operate on campaigns with this label
 
var SIG_FIGS = 1000; //this means round all calculations to 3 decimal places
var MONTHLY_BUDGET = 0; // we will set this later
 
function main() {
  MONTHLY_BUDGET = _pull_budget_data_from_spreadsheet();
  var tot_cost_mtd = _get_total_cost();
  var is_first_of_the_month = ((new Date()).getDate() == 1);
  is_first_of_the_month = (is_first_of_the_month && ((new Date()).getHours() == 0));
  Logger.log("Total cost: " + tot_cost_mtd + ", Monthly budget:" + MONTHLY_BUDGET);
   
  if(is_first_of_the_month) {
    _reset_budgets();
  } else {
    _adjust_campaign_budget(tot_cost_mtd);
  }
   
}
 
// Returns the total cost for the set TIMEFRAME
function _get_total_cost() {
  var camp_iter = (LABEL == "") ? AdWordsApp.campaigns().get() :
                                  AdWordsApp.campaigns()
                                    .withCondition("LabelNames CONTAINS_ANY ['"+LABEL+"']")
                                    .get();
   
  var tot_cost = 0;
  while(camp_iter.hasNext()) {
    tot_cost += camp_iter.next().getStatsFor(TIMEFRAME).getCost();
  }
  return tot_cost;
}
 
// Calculates run rate and adjusts campaign bids as needed.
function _adjust_campaign_budget(my_tot_cost) {
  var today = new Date();
  // Accounting for December
  var eom = (today.getMonth() == 11) ? new Date(today.getFullYear()+1,0,1) : 
                                       new Date(today.getFullYear(),today.getMonth()+1,1);
  var days_left = Math.round((eom-today)/1000/60/60/24);
  var days_spent = today.getDate();
  var run_rate = Math.round(my_tot_cost/days_spent*SIG_FIGS)/SIG_FIGS;
  var projected_total = my_tot_cost + (run_rate * days_left);
  var perc_over = Math.round(((MONTHLY_BUDGET-projected_total)/projected_total)*SIG_FIGS)/SIG_FIGS; 
  _change_spend(perc_over,my_tot_cost);
}
 
//Adjusts the budget for a given campaign based on percentage of total spend
//Note: if the cost of a campaign is $0 mtd, the budget is not changed.
function _change_spend(perc_to_change,my_tot_cost) {
  var camp_iter = (LABEL == '') ? AdWordsApp.campaigns()
                                    .withCondition("Status = ENABLED")
                                    .get() :
                                  AdWordsApp.campaigns()
                                    .withCondition("Status = ENABLED")
                                    .withCondition("LabelNames CONTAINS_ANY ['"+LABEL+"']")
                                    .get();
   
  while(camp_iter.hasNext()) {
    var camp = camp_iter.next();
    var camp_cost = camp.getStatsFor(TIMEFRAME).getCost();
    var perc_of_total = Math.round(camp_cost/my_tot_cost*SIG_FIGS)/SIG_FIGS;
    //If there is no cost for the campaign, let's not change it.
    var to_change = (perc_of_total) ? (perc_of_total*perc_to_change) : 0;
    camp.setBudget(camp.getBudget()*(1+to_change));
  }
}
 
// Resets the budget unevenly
function _reset_budgets() {
  var camp_budget_map = _pull_campaign_data_from_spreadsheet();
  var camp_iter = (LABEL == '') ? AdWordsApp.campaigns()
                                    .withCondition("Status = ENABLED")
                                    .get() :
                                  AdWordsApp.campaigns()
                                    .withCondition("Status = ENABLED")
                                    .withCondition("LabelNames CONTAINS_ANY ['"+LABEL+"']")
                                    .get();
  while(camp_iter.hasNext()) {
    var camp = camp_iter.next();
    if(camp_budget_map[camp.getName()]) {
      camp.setBudget(camp_budget_map[camp.getName()]/30.5);
    } else {
      camp.setBudget(DEFAULT_BUDGET);
    }
  }
}
 
function _pull_campaign_data_from_spreadsheet() {
  var spreadsheet = getSpreadsheet(SPREADSHEET_URL);
  var sheet = spreadsheet.getActiveSheet();
  var data = sheet.getRange("A:B").getValues();
  if(data[0][0] == "") {
    //This means this is the first run and we should populate the data.
    _populate_spreadsheet(sheet);
    data = sheet.getRange("A:B").getValues();
  }
  var campaign_budget_map = {};
  for(var i in data) {
    if(i == 0) { continue; } //ignore the header
    if(data[i][0] == "") { break; } //stop when there is no more data
    campaign_budget_map[data[i][0]] = parseFloat(data[i][1]);
  }
  return campaign_budget_map;
}
 
function _pull_budget_data_from_spreadsheet() {
  var spreadsheet = getSpreadsheet(SPREADSHEET_URL);
  var sheet = spreadsheet.getActiveSheet();
  var data = sheet.getRange("A:B").getValues();
  if(data[0][0] == "") {
    //This means this is the first run and we should populate the data.
    _populate_spreadsheet(sheet);
    data = sheet.getRange("A:B").getValues();
  }
  var tot_budget = 0;
  for(var i in data) {
    if(i == 0) { continue; } //ignore the header
    if(data[i][1] == "") { break; } //stop when there is no more data
    tot_budget += parseFloat(data[i][1]);
  }
  return tot_budget;
}
 
function _populate_spreadsheet(sheet) {
  sheet.clear();
  sheet.appendRow(['Campaign Name','Monthly Budget']);
  var camp_iter = (LABEL == '') ? AdWordsApp.campaigns()
                                    .withCondition("Status = ENABLED")
                                    .get() :
                                  AdWordsApp.campaigns()
                                    .withCondition("Status = ENABLED")
                                    .withCondition("LabelNames CONTAINS_ANY ['"+LABEL+"']")
                                    .get();
  while(camp_iter.hasNext()) {
    var camp = camp_iter.next();
    sheet.appendRow([camp.getName(),(camp.getBudget()*30.5)]);
  }
}
 
function getSpreadsheet(spreadsheetUrl) {
  return SpreadsheetApp.openByUrl(spreadsheetUrl);
}