[Google Ads Script] Ad Template Report Script

It’s no secret that testing and learning is one of the key pillars of success within Google Ads. 

The advertisers who are winning in 2020 are not the ones who are the most creative, they are the ones who micro split test every aspect of their ads and continually improve.

To help advertisers create better ads, we’ve written a script to analyse adverts and aggregate performance for headlines, descriptions and paths, showing you which parts of your ads perform best. 

It then creates a report like the one below, showing the best-performing headline 1 in our account for the past seven 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 to program the script to do what you want. They are as follows:

  • Google Sheets URL: To use this script, you need to create a new blank Google Sheet where the report will be created, then enter the URL of the sheet into the script on line 20.
  • Date range: You need to set the date range that you want to analyse in the format year-month-day. This can be done on lines 23 and 24.
  • 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, we suggest changing 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: Choose your currency on line 40 using the appropriate 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;
}

About Wes

Wes is the Managing Director of DemandMore. He is columnist for several leading marketing publications including Campaign Magazine, Econsultancy and Search Engine Land where he shares his expertise in search marketing.