Saturday, March 30, 2013

Automate Your Ads Based on Your Baseball Team's Schedule

I know everyone is focused on March Madness right now, but opening day for Baseball season is right around the corner. I wondered if it were possible to enable and disable specific ads based on the schedules of your local MLB team.

It turns out, the MLB has every team's full schedule in an easy to parse CSV format. All you need to do is search Google for " downloadable schedule" and look for the page that ends in "downloadable.jsp". It will look something like this:


The link you need is circled in the image above. You can see from the url what the team_id value should be in the code below. And if you right-click and download that .csv file, you will be able to see what value to use for the home_field as well. In the example below, I used my hometown teams of the Cubs and the White Sox, but you can use any teams you want.

The script itself will run through and look for a home game for your team. If the team is playing today and it is a home game, it will enable any ads that are tagged with the label you configured in the TEAM_INFO. If the team isn't playing a home game today, it will pause those same ads.

In the example below, I only used a portion of the data in the csv files. It would also be possible to enable ads only during the actual game, or enable ads for all game days, not just home games.

I encourage you to play around with the data and see what you can come up with. If you find anything useful, please leave a comment.

Thanks,
Russ


//-----------------------------------
// Enable/Disable Ads Based on the MLB Schedule
// Created By: Russ Savage
// FreeAdWordsScripts.com
//-----------------------------------
function main() {
  var TEAM_INFO = [
    { team_id : 112, home_field : 'Wrigley Field', label : 'cubs' }, // Cubs
    { team_id : 145, home_field : 'U.S. Cellular Field', label : 'whitesox' } // White Sox
  ];
  //hopefully you've already created and tagged some ads with these labels
  //but just in case...
  createLabelsIfNeeded(TEAM_INFO); 
  
  var SEASON = (new Date()).getFullYear();
  var is_home = false, is_game_day = false;
  for(var t in TEAM_INFO) {
    var team = TEAM_INFO[t];
    var url = "http://mlb.mlb.com/soa/ical/schedule.csv?team_id="+team.team_id+"&season="+SEASON;
    var html = UrlFetchApp.fetch(url).getContentText();
    var date_list = html.split("\r\n");
    for(var i in date_list) {
      if(i == 0) {continue;}
      var [start_date,start_time,start_time_et,
           subject,location,description,
           end_date,end_date_et,end_time,end_time_et] = date_list[i].split(",");
      
      var today = new Date();
      var game_day = new Date();
      game_day.setFullYear(SEASON,parseInt(start_date.split("/")[0])-1,parseInt(start_date.split("/")[1]));
      
      is_home = (location == team.home_field);
      is_game_day = (diffDays(game_day,today) == 0);
      
      if(is_home && is_game_day) {
        enableBaseballAds(team.label);
        break;
      }
    }
    if(!(is_home && is_game_day)) {
      disableBaseballAds(team.label); 
    }
  }
  
}

function enableBaseballAds(label) {
  Logger.log("Enabling all ads with the "+label+" label.");
  var ads = AdWordsApp.ads().withCondition("LabelNames CONTAINS_ALL ['"+label+"']").get();
  while(ads.hasNext()) {
    ads.next().enable(); 
  }
}

function disableBaseballAds(label) {
  Logger.log("Disabling all ads with the "+label+" label.");
  var ads = AdWordsApp.ads().withCondition("LabelNames CONTAINS_ALL ['"+label+"']").get();
  while(ads.hasNext()) {
    ads.next().pause(); 
  }
}

function createLabelsIfNeeded(team_info) {
  var label_iter = AdWordsApp.labels().get();
  var label_list = [];
  while(label_iter.hasNext()) {
    label_list.push(label_iter.next().getName());
  }
  for(var i in team_info) {
    if(label_list.indexOf(team_info[i].label) == -1) {
      AdWordsApp.createLabel(team_info[i].label);
      label_list.push(team_info[i].label);
    }
  }
}

//A helper function to compare dates.
//Copied from: http://goo.gl/uW48a
function diffDays(firstDate,secondDate) {
  var oneDay = 24*60*60*1000; // hours*minutes*seconds*milliseconds
  return Math.round(Math.abs((firstDate.getTime() - secondDate.getTime())/(oneDay))); 
}

Wednesday, March 20, 2013

Store Account Performance Report in a Google Doc

Day 2 and I'm still playing around with the new reporting API. I figured I'd post something that people started asking a little about from my previous post about Storing Account Level Quality Score which is storing all account level performance in a spreadsheet.

All you need to do is create a brand new Google Doc to store your data and paste that url into the script. The first time the script runs, it will create column headings. You can add, remove, or rearrange columns as you like by moving the column names around. You should reset your spreadsheet after doing this though.

Thanks,
Russ

/**************************************
* Store Account Performance Report in a Google Doc
* Version 1.1
* Changelog v1.1 - Removed apiVersion, Removed get spreadsheet
* Created By: Russ Savage
* FreeAdWordsScripts.com
**************************************/
function main() {
  var spreadsheet_url = "Your Spreadsheet Url Goes Here";
  var date_range = 'YESTERDAY';
  var columns = ['Date',
                 'AccountCurrencyCode',
                 'AccountDescriptiveName',
                 'AccountId',
                 'AccountTimeZoneId',
                 'CustomerDescriptiveName',
                 'ExternalCustomerId',
                 'PrimaryCompanyName',
                 'PrimaryUserLogin',
                 'Device',
                 'AverageCpc',
                 'AverageCpm',
                 'AveragePosition',
                 'Clicks',
                 'ConversionRate',
                 'ConversionRateManyPerClick',
                 'Conversions',
                 'ConversionsManyPerClick',
                 'ConversionValue',
                 'Cost',
                 'CostPerConversion',
                 'CostPerConversionManyPerClick',
                 'Ctr',
                 'Impressions',
                 'SearchBudgetLostImpressionShare',
                 'SearchExactMatchImpressionShare',
                 'SearchImpressionShare',
                 'SearchRankLostImpressionShare',
                 'ValuePerConversion',
                 'ValuePerConversionManyPerClick',
                 'ViewThroughConversions'];
  var columns_str = columns.join(',') + " ";
  
  var sheet = SpreadsheetApp.openByUrl(spreadsheet_url).getActiveSheet();
  if(sheet.getRange('A1:A1').getValues()[0][0] == "") {
    sheet.clear();
    sheet.appendRow(columns);
  }
  
  var report_iter = AdWordsApp.report(
    'SELECT ' + columns_str +
    'FROM ACCOUNT_PERFORMANCE_REPORT ' +
    'DURING ' +date_range).rows();
  
  while(report_iter.hasNext()) {
    var row = report_iter.next();
    var row_array = [];
    for(var i in columns) {
       row_array.push(row[columns[i]]);
    }
    sheet.appendRow(row_array); 
  }
}

Tuesday, March 19, 2013

Store Search Query Performance Report in a Google Doc

Well, I promised you that once the AdWords Scripts team had fixed the issue with the reporting API that I would start adding some examples. Thankfully they fixed it today because I wanted to start playing with the Search Query Performance Report.

This report will give you all the user search queries for your broad match keywords. Normally, you have to log in and manually generate the report each day. But with AdWords scripts, you can start storing all of that data into a Google Spreadsheet so that it is easier to work with.

So today I have a simple script that will store the search query report and send you an email reminding you that it's ready. All you need to do is fill in the url from an existing Google Doc that you have access to with the same log in as your AdWords account, and set the email addresses you want to send a notice to, and you should be all set. There is also a flag to determine if you want to ignore exact and exact (close variant) matched keywords from the results, which I recommend.

Thanks,
Russ

/***************************************************
* Store Search Query Perf Report in Google Doc
* Version 1.1
* CHangelog v1.1 - Removed apiVersion, Updated formatting
* Created By: Russ Savage
* FreeAdWordsScripts.com
****************************************************/
var DATE_RANGE = 'LAST_7_DAYS';
var IGNORE_EXACT = true;
var TO = ["email_1@my_company.com","email_2@my_company.com"];
var SPREADSHEET_URL = "your spreadsheet url goes here";  

function main() {
  var columns = ['AccountDescriptiveName',
                 'CampaignName',
                 'AdGroupName',
                 'KeywordTextMatchingQuery',
                 'MatchType',
                 'Query',
                 'Device',
                 'Impressions',
                 'Clicks',
                 'Cost',
                 'Conversions',
                 'AverageCpc',
                 'CostPerConversion',
                 'ConversionRate',
                 'Ctr'];
  var columnsStr = columns.join(',') + " ";
   
  var sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getActiveSheet();
  sheet.clear();
  sheet.appendRow(columns);
   
  var reportIter = AdWordsApp.report(
    'SELECT ' + columnsStr +
    'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
    'DURING ' + DATE_RANGE, {
      includeZeroImpressions: false
    }).rows();
   
  while(reportIter.hasNext()) {
    var row = reportIter.next();
    if(IGNORE_EXACT && row['MatchType'].indexOf('exact') >= 0) { continue; }
    var rowArray = [];
    for(var i in columns) {
      rowArray.push(row[columns[i]]);
    }
    sheet.appendRow(rowArray); 
  }
   
  for(var i in TO) {
    MailApp.sendEmail(TO[i], "Search Query Report Ready", SPREADSHEET_URL);
  }
}

Monday, March 18, 2013

Dynamically Adjust Campaign Budgets

UPDATE 2013-04-05: There is an updated version of this script. Check it out. Dynamically Adjust Campaign Budgets v2.0.

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

Here is a request from a reader:
I manage many small business PPC accounts, and some of these accounts have multiple campaigns, and they usually have a relatively small monthly click budget. I'm looking for a way to pause ALL campaigns if the entire account has spent over a certain amount month-to-date.

This is actually a pretty easy script to put together. Below is a script that will do just that. You can set the MONTHLY_BUDGET at the beginning of the script and run this script daily on your account. Once the campaigns have a total cost greater than the budget specified, it will pause all the campaigns in the account.

Then, on the first of the next month, it will enable those campaigns once again. If you make no changes to the script below, it should do just that. But let's go one step further.

You actually have the power to get and set your campaign budgets using scripts. So let's say your monthly budget is $100, but you want to make sure your ads are spaced out through the month. I have added a function below called _adjust_campaign_budget() which can be enabled through the flag ADJUST_BUDGETS at the top of the script.

The script will then attempt to calculate a run rate for your campaign to figure out if you are going to meet your budget or not. If you are going to go over, it will lower the budget of each campaign (weighted by campaign cost) so that you come in at your target. If you are going to under-spend, it will also attempt to increase your campaign budget to try to allow you to hit your goal. I have also added a _reset_budgets() function to the end that gets called on the first of the month. If you run this script more frequently, you should enable the code for checking if it is the first hour of the first day of the month.

Now understandably, this script comes with a few cautions. THIS SCRIPT MAY CAUSE YOU TO SPEND A LOT OF MONEY. I'm sure the campaigns I was testing this on were quite a bit smaller than your campaigns, with budgets to match.

Thanks,
Russ

/******************************************
* Keep Your Campaigns In Budget
* Version 1.1
* ChangeLog v1.1 
*   - cleaned up code
*   - added ability for any dates
* Created By: Russ Savage
* FreeAdWordsScripts.com
******************************************/
// Let's set some constants
var MONTHLY_BUDGET = 5000.00;

//If you want to work with a monthly budget, leave START_DATE and END_DATE blank.
var TIMEFRAME = "THIS_MONTH";
//But if you want to work with a specific timeframe, fill these in.
//Use the format yyyyMMdd, so for Jan 12th, 2014, you would put 20140112.
var START_DATE = '';
var END_DATE = '';
 
//Set this to true if you want to adjust budgets or
//keep set to false if you want to just pause all the campaigns
//when you hit your budget
var ADJUST_BUDGETS = false;
var DECIMAL_PLACES = 3;
 
function main() {
  var totalCostMTD = getTotalCost();
  var isFirstOfTheMonth = ((new Date()).getDate() == 1);
  if(START_DATE && END_DATE) {
    var today = new Date();
    today.setHours(0,0,0,0);
    var startDate = new Date(START_DATE.substring(0,4),
                             parseFloat(START_DATE.substring(4,6))-1,
                             START_DATE.substring(6,8));
    isFirstOfTheMonth = (startDate.getTime() == today.getTime());
  }
  //if you run this script more than once per day, uncomment the next line
  //isFirstOfTheMonth = (isFirstOfTheMonth && ((new Date()).getHour() == 0));
  Logger.log("Total cost: " + totalCostMTD + 
           ", Monthly budget:" + MONTHLY_BUDGET +
           ", isFirstOfTheMonth: "+isFirstOfTheMonth);
   
  if(ADJUST_BUDGETS) {
    if(isFirstOfTheMonth) {
      resetBudgets();
    } else {
      adjustCampaignBudget(totalCostMTD);
    }
  } else {
    if(totalCostMTD >= MONTHLY_BUDGET) {
      //If we have hit the limit, pause all ads
      enableOrDisableCampaigns(true);
    } else {
      // let's check if it's the first day of the month
      if((new Date()).getDate() == 1) {
        //enable all the campaigns
        enableOrDisableCampaigns(false);
      }
    }
  }
}
 
// Returns the total cost for the set TIMEFRAME
function getTotalCost() {
  var campIter = AdWordsApp.campaigns().get();
   
  var totalCost = 0;
  while(campIter.hasNext()) {
    if(START_DATE && END_DATE) {
      totalCost += campIter.next().getStatsFor(START_DATE,END_DATE).getCost();
    } else {
      totalCost += campIter.next().getStatsFor(TIMEFRAME).getCost();
    }
  }
  return totalCost;
}
 
// Enables or Disables All Campaigns In Account
function enableOrDisableCampaigns(shouldDisable) {
  var campIter = AdWordsApp.campaigns().get();
  while(campIter.hasNext()) { 
    if(shouldDisable) { 
      campIter.next().pause(); 
    } else { 
      campIter.next().enable(); 
    }
  }
}
 
// Calculates run rate and adjusts campaign bids as needed.
function adjustCampaignBudget(myTotalCost) {
  var today = new Date();
  // Accounting for December
  var eom;
  if(START_DATE && END_DATE) {
    eom = new Date(END_DATE.substring(0,4),
                   parseFloat(END_DATE.substring(4,2))-1,
                   END_DATE.substring(6,2));
  } else {
    eom = (today.getMonth() == 11) ? new Date(today.getFullYear()+1,0,1) : 
                                     new Date(today.getFullYear(),today.getMonth()+1,1);
  }
  var daysLeft = Math.round((eom-today)/1000/60/60/24);
  var daysSpent;
  if(START_DATE && END_DATE) {
    var startDate = new Date(START_DATE.substring(0,4),
                             parseFloat(START_DATE.substring(4,2))-1,
                             START_DATE.substring(6,2));
    daysSpent = Math.round((today-startDate)/1000/60/60/24);
  } else {
    daysSpent = today.getDate();
  }
  var runRate = round(myTotalCost/daysSpent);
  var projectedTotal = myTotalCost + (runRate * daysLeft);
  var percOver = round((MONTHLY_BUDGET-projectedTotal)/projectedTotal);
   
  changeSpend(percOver,myTotalCost);
}
 
//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 changeSpend(percToChange,myTotalCost) {
  var campIter = AdWordsApp.campaigns().withCondition("Status = ENABLED").get();
   
  while(campIter.hasNext()) {
    var camp = campIter.next();
    var campCost = (START_DATE && END_DATE) ? camp.getStatsFor(START_DATE,END_DATE).getCost()
                                            : camp.getStatsFor(TIMEFRAME).getCost();
    var percOfTotal = round(campCost/myTotalCost);
    //If there is no cost for the campaign, let's not change it.
    var toChange = (percOfTotal) ? (percOfTotal*percToChange) : 0;
    camp.setBudget(camp.getBudget()*(1+toChange));
  }
}
 
// Resets the budget evenly across all campaigns
function resetBudgets() {
  Logger.log('Resetting budgets at the first of the period.');
  var campIter = AdWordsApp.campaigns().withCondition("Status = ENABLED").get();
  var campCount = 0;
  while(campIter.hasNext()) {
    campCount++;
    campIter.next();
  }
  campIter = AdWordsApp.campaigns().withCondition("Status = ENABLED").get();
  while(campIter.hasNext()) {
    campIter.next().setBudget(MONTHLY_BUDGET/campCount);
  }
}

// A helper function to make rounding a little easier
function round(value) {
  var decimals = Math.pow(10,DECIMAL_PLACES);
  return Math.round(value*decimals)/decimals;
}