[Adwords Script] Ad Template Report Script

It’s no secret that ad testing is one of the key pillars to success within Adwords. The advertisers that are winning in 2018 are not the ones that are the most creative, it is the ones that micro split test every aspect of their advert and continually improve.

To help advertisers create better ads we have created this script that analyses adverts and aggregates performance for headlines, descriptions, and paths, showing you which parts of your ads perform best. It then creates a report like the one below which shows the best performing headline 1 in our account for the last 7 days.

It also creates several tabs at the bottom of the sheet, each one containing the report for different elements of the ad copy. So you can analyse headline 1, headline 2, the description, path 1 and path 2 separately.

Within the script, there are several variables that you can set that allow you to program the script to do what you want to do, they are as follows:

  • Google Sheets URL. To use this script you need to create a new blank Google Sheet and then enter the URL of that sheet into the Script on line 20.
  • Date range. You need to set the date range that you want to analyze in the format year-month-day. This can be done on lines 23 aand 24.
  • Do you want to include paused ads? You can select whether or not to include data from paused ads within the script. You can either set this to True if you want to or False if not on line 27.
  • Include Ads with no conversions? On large accounts with tens of thousands of ads it is suggested that you change this to False otherwise leave this as True on line 30.
  • Define campaigns to include or exclude. In lines 32 to 37 you have the option to define which campaigns you want to include or exclude. By default, all campaigns are included. To include only certain campaigns define these within the brackets on line 33. To exclude certain campaigns define these within the brackets on line 37.
  • Select your currency. Select the currency on line 40 using the symbol, E.g. “£” or “$”.

The script can be found below, you can add it to your account within the bulk operations section of your account.

/**********************
 *
 * AdWords script to pull ad data into a Google Sheet.
 *
 * Parameters include:
 * - Sheet URL
 * - Start and end dates
 * - Include/exclude paused ads
 * - Include/exclude ads with no conversions
 * - Lists of campaigns to include/exclude (or shows all)
 * - V1. Clicteq Limited
 */


function main() {

  ///////////////////////// BEGIN USER EDIT AREA /////////////////////////////

  //Enter the url of the sheet where you want the data to be added
  var sheetURL = "Enter Google Sheets URL here";

  //Enter start and end dates in this format:  2018-04-10
  var startDate = "2018-04-01";
  var endDate = "2018-04-14"

  //Include paused ads?
  var includePaused = false;

  //Include ads with no conversions?
  var includeNoConversions = true;

  //List of campaign names to include (leave blank for all)
  var includeCampaigns = [];// e.g. ["My campaign 1", "My campaign 2"];

  //List of campaign names to exclude (leave blank for all,
  //and do not exclude campaigns you have explicitly included)
  var excludeCampaigns = [];// e.g. ["My campaign 1", "My campaign 2"];

  //Enter here the currency used for the cost
  var currency = "£";

  //List of outputs to display by
  var pivots = ["HeadlinePart1","HeadlinePart2","Description","Path1","Path2"];

  ///////////////////////// END USER EDIT AREA ///////////////////////////////


  generateReports(sheetURL,pivots,startDate,endDate,includePaused,includeCampaigns,excludeCampaigns,includeNoConversions,currency)


}

//Put it all together and generate reports
function generateReports(sheetURL,pivots,startDate,endDate,includePaused,includeCampaigns,excludeCampaigns,includeNoConversions,currency){
  //Open the sheet provided by the user
  var ss = SpreadsheetApp.openByUrl(sheetURL);

  var start = convertPrettyDateToAQL(startDate);
  var end = convertPrettyDateToAQL(endDate);

  //Continue only if valid dates
  if (start != undefined && end != undefined){
    //Pivot by each of the five categories
    for (var i=0;i<pivots.length;i++){
      var pivot = pivots[i];
      Logger.log("Retrieving Adwords data for " + pivot + "...");
      addReportToSheetWithName(ss,pivot,start,end,includePaused,includeCampaigns,excludeCampaigns,includeNoConversions,currency);
    }
  }
  else{
    Logger.log("Dates entered not in correct format! Please make sure you enter them in the format specified: 2018-04-10 (YYYY-MM-DD)");
  }
}

//Retrieve AdWords reporting data
function generateAdWordsReport(pivot,start,end,paused,include,exclude,includeNoConversions){
  var includes = "";

  //If paused ads need to be shown, add it to the query, otherwise keep it as it is
  var includePausedText = (paused ? "" : " Status = ENABLED ");
  includes = addToQuery(includes,includePausedText);

  //If include/exclude campaigns contain names, add them to the query, otherwise keep it as it is
  var includeCampaignsText = include.length>0 ? (" CampaignName IN " + formatTextForQuerySet(include)) : "";
  includes = addToQuery(includes,includeCampaignsText);
  var excludeCampaignsText = exclude.length>0 ? (" CampaignName NOT_IN " + formatTextForQuerySet(exclude)) : "";
  includes = addToQuery(includes,excludeCampaignsText);

  if (!includeNoConversions){
  	includes = addToQuery(includes," Conversions > 0 ");
  }

  var query =   "SELECT " + pivot + ", Clicks, Impressions, Cost, Conversions, Ctr, ConversionRate, CostPerConversion " +
    			"FROM AD_PERFORMANCE_REPORT " +
    			includes +
    			"DURING " + start + "," + end;

  var report = AdWordsApp.report(query);

  return report;
}

//Generate report and fill sheet
function addReportToSheetWithName(ss,pivot,start,end,paused,include,exclude,conversions,currency){
  //Get or create the sheet with the pivot specified
  var sheet = ss.getSheetByName(pivot);
  if (sheet == undefined){
      sheet = ss.insertSheet(pivot);
  }

  //Create the report
  var report = generateAdWordsReport(pivot,start,end,paused,include,exclude,conversions);

  //Add the data to the sheet. If the data exceeds the max sheet size,
  //log it so the user knows
  try{
    Logger.log("Pivotting the data for " + pivot);
    var data = getRowsAndAggregateToUnique(report,pivot);
    var asArray = getObjectAsArray(data,pivot);
    Logger.log("Writing data to the sheet\n");
    if (asArray.length>0){
      var range = sheet.getRange(1,1,asArray.length,asArray[0].length);
      sheet.clear();
      range.setValues(asArray);
      sheet.setFrozenRows(1);

      // Pct format
      var column = sheet.getRange("F2:G");
      column.setNumberFormat("#.##%")
      // Currency format
      column = sheet.getRange("H2:H");
      column.setNumberFormat(currency + "#,##0.00");
    }
  }
  catch(e){
    Logger.log(e);
  }
}

//Format array as AQL set
function formatTextForQuerySet(array){
  var awq = "[";
  Logger.log(array);
  for (var i=0;i<array.length;i++){
    var item = "'" + array[i] + "'";
    var isLast = (i==array.length-1);
    awq += item + (isLast?"":",");
    Logger.log(awq);
  }
  awq += "] ";
  return awq;
}

//Add to query filter
function addToQuery(queryBuilder,newQueryItem){
  //no new queries to add
  if (newQueryItem == ""){
    return queryBuilder;
  }

  //add 'WHERE' or 'AND' depending on blank statement or existing statement
  if (queryBuilder == ""){
    queryBuilder +=  " WHERE " + newQueryItem;
  }
  else{
    queryBuilder += " AND " + newQueryItem;
  }
  return queryBuilder;
}

//Retrieve rows for further processing and aggregation of data
function getRowsAndAggregateToUnique(report,pivot){
  var rows = report.rows();
  var data = {};
  while (rows.hasNext()){
    var row = rows.next();
    var dataRow = [];
    var keys = Object.keys(row);

    if (data[row[pivot]] == undefined){
      data[row[pivot]] = {};
    }
    for (var i=0;i<keys.length;i++){
      if (keys[i]!="Ctr" && keys[i]!="ConversionRate" && keys[i]!="CostPerConversion" && keys[i]!=pivot){
        if (data[row[pivot]][keys[i]] == undefined){
          data[row[pivot]][keys[i]] = 0;
        }
        var n = readNumber(row[keys[i]]);
        var value = isNaN(n) ? 0 : n;

        data[row[pivot]][keys[i]] += value*1;
      }
    }


  }

  //Now based on clicks, implessions, conversions and cost, calculare Ctr, ConversionRate and CostPerConversion
  var pivotKeys = Object.keys(data);
  for (var i=0;i<pivotKeys.length;i++){
    var pivotKey = pivotKeys[i];
    var value = ( isNaN(data[pivotKey]["Clicks"])
                 || (data[pivotKey]["Impressions"] == 0)
                 || isNaN(data[pivotKey]["Impressions"])
                 || isNaN(data[pivotKey]["Clicks"] / data[pivotKey]["Impressions"])
                )? 0 : (data[pivotKey]["Clicks"] / data[pivotKey]["Impressions"]);
    data[pivotKey]["Ctr"] = value;
    value =     ( isNaN(data[pivotKey]["Conversions"])
                 || (data[pivotKey]["Clicks"] == 0)
                 || isNaN(data[pivotKey]["Clicks"])
                 || isNaN(data[pivotKey]["Conversions"] / data[pivotKey]["Clicks"])
                )? 0 : (data[pivotKey]["Conversions"] / data[pivotKey]["Clicks"]);
    data[pivotKey]["ConversionRate"] =  value;
    value =     ( isNaN(data[pivotKey]["Cost"])
                 || (data[pivotKey]["Conversions"] == 0)
                 || isNaN(data[pivotKey]["Conversions"])
                 || isNaN(data[pivotKey]["Cost"] / data[pivotKey]["Conversions"])
                )? 0 : (data[pivotKey]["Cost"]/ data[pivotKey]["Conversions"]);
    data[pivotKey]["CostPerConversion"] = value;
  }

  return data;
}

//Convert the object to an array so it can be written to sheets
function getObjectAsArray(obj,pivot){
  var keys = Object.keys(obj);

  //first add the headers
  var rows = [[pivot,"Clicks","Impressions","Cost","Conversions","Ctr","ConversionRate","CostPerConversion"]];

  //now add the data
  for (var i=0;i<keys.length;i++){
    var row = [keys[i] , obj[keys[i]]["Clicks"] , obj[keys[i]]["Impressions"] , obj[keys[i]]["Cost"]
               , obj[keys[i]]["Conversions"] , obj[keys[i]]["Ctr"] , obj[keys[i]]["ConversionRate"]
               ,obj[keys[i]]["CostPerConversion"]];
    rows.push(row);

  }

  return rows;
}

//Fix the raw format returned by the report
function readNumber(textNumber){
  if (typeof textNumber == 'string'){
    textNumber = textNumber.replace(',', '');
    return parseFloat(textNumber);
  }
  return textNumber;
}

//Convert user input date into AQL format
function convertPrettyDateToAQL(date){
  var split = date.toString().split("-");
  if (split.length == 3){
    var year = split[0];
    var month = split[1];
    var day = split[2];
    var checks = (year.length == 4) && (month <12) && (day <32);
    if (checks){
      var monthString = (month.toString().length == 1)?("0" + month.toString()):month.toString();
      var dayString = (day.toString().length == 1)?("0" + day.toString()):day.toString();
      var dateOut = year.toString() + monthString + dayString;
      return dateOut;
    }
  }
  //fallback
  return undefined;
}

 

wesley parker
About wesley parker

Wesley is Founder and CEO at Clicteq. He currently manages a £6 Mil Adwords portfolio across a range of different sectors. He regulally features in leading search publications such as Search Engine Journal, Econsultancy and Certified Knowledge. You can follow him on Twitter or connect with him on Linkedin

5 thoughts on “[Adwords Script] Ad Template Report Script

  • Hi,

    Thanks for this.

    I wanted to test it out but it gives an error in line 108:

    Retrieving Adwords data for HeadlinePart1…
    Pivotting the data for HeadlinePart1
    Writing data to the sheet

    Retrieving Adwords data for HeadlinePart2…
    Service error: Spreadsheets (line 81)

    Thanks.

  • Aggregating statistics from ads in different ad groups is a flawed concept, because different ad groups & keywords have different CTRs. CTR is not entirely dependent on the ad copy, the keyword also matters. In order to compare the aggregate CTRs of phrases used in different ad groups accurately, all the ad groups compared would have to have the same average CTR, or the ad being tested would have to get shown equally in all ad groups you’re aggregating statistics from. Neither of these things happens in real life so a script that tries to draw conclusions about which ad text was better could not possibly have enough information to do so. The aggregate results don’t tell you which text is best they simply tell you which ad group the ad appeared most in.

    A simple example would be: if you have 10 ads in 2 different ad groups, 5 ads in each ad group. Ad group A has an average CTR of 5% and has ads in it ranging from 4% to 6% CTR. Ad group B has an average CTR of 3% with ads in it ranging in CTR from 2% to 3%. If we measure the aggregate CTR of a piece of ad text that appears in both ads all we are doing is measuring whether that text was shown more in the high CTR ad group than in the low CTR ad group.

    A more useful measurement would be to ask: out of the total number of ad groups this text was tried in, how many did it win in? To know that a given piece of text was the CTR winner in 7 out of 10 ad groups it was tried in would actually be valuable.

    • Hey Rob, see where you are coming from here, however, I would disagree on the basis that if you were to follow your method of looking at which ad groups ads won in, this is likely to lack statistical significance. If you had enough data to make a call at ad group level you would simply a/b test two ads against each other and test different headlines, desc and paths.

      The reason for aggregating performance is to allow you to split test where you have low volume keywords, where you will have to aggregate data across groups to reach statistical significance. Though you are correct that this is not perfect and will work better where all of the CTRs are the same.

  • Hi
    Thanks for this script.
    It’s possibile include a specific adgroup ?
    Fore example have a var to specific campaign end another one to specific adgroup.
    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *