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

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

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

Issues With The New Reporting API

Like many of you, I've been trying to sink my teeth into the new reporting functionality that was recently added to the AdWords scripts late last week.  Unfortunately, I've been getting errors even when I use the sample code given in the API. If anyone has been having the same issues, there is a thread on the forums where you can wait for the AdWords Scripts team to resolve the issue.  As soon as it is resolved, I'll start posting some sample code.  I have been working on an auto trafficking of user queries script that is pretty cool.

Thanks,
Russ