How to Automate Google AdWords Performance Reports with Google Sheets

Posted: April 3rd, 2018
Categories: Advice, Google, Google Ads, How to, PPC

When monitoring the performance of your AdWords account, it can be difficult to get the information you need together to be able to make decisions and changes. Below is a script that can be implemented on any account to make that data easier to read and action, without the high cost of external platforms, all by using free resources like AdWords Scripts and Google Sheets.

The script will take all of the data in your account from the last 30 days and set it up into two sheets labelled “Segmentation Performance” and “Account Performance” which are explained further below with tips on how to make use of the data.

Segmentation Performance

The segmentation performance sheet breaks down the overall performance over the last 30 days into categories that can be used for bid adjustments and targeting setting.

The top row of graphs focuses on total revenue and the bottom row represents the return on ad spend, or ROAS, to allow for optimisation decisions to be made.

Looking at the device segmentations you can see the percentage split of revenue generated by device category to allow for bid adjustments to be set. It’s important to look at the ROAS and not focus purely on total revenue generated as this will give you an idea of how profitable the device segment is relative to the total revenue generated.

The “Day Of Week” and “Hour Of Day” graph for revenue and ROAS will display the performance segmented by day and hour which can then be used to refine ad schedules and also place bid adjustments on the best/worst performing hours of day to optimise spend.

Account Performance

The account performance sheet contains actual figures and performance data from the account and then breaks down the 5 top spending campaigns to provide more granular data on the campaigns which are having the greatest effect on the overall performance.

Script Setup

To set the audit script up:

Copy the code below into the account you want to audit
Sign in to your google account and make a copy of the below example spreadsheet by clicking File > Make Copy and save it in your own Google Drive

https://docs.google.com/spreadsheets/d/17ayBrKTQiO_fhmCdfsYKsKplkAluo2t8YKU2-UBqIo0/edit?usp=sharing

Create a shared URL of the sheet you’ve just created with edit (“Anyone with the link can edit“)access and copy it in between the speech marks on line 20 of the script
The script is now setup, so hit run and authorise the script to audit your account, once the script has finished running just open or refresh the Google Sheet and your audit output will be there

Script Code


/*-------------------------------------------------------------------------------------------------------------------*/
/* AdWords Audit Script  - Account Level - */
/* A Script to audit an account and output data with performance metrics to a Google Spreadsheet for evaluation */
/* Version 1.0 */
/*-------------------------------------------------------------------------------------------------------------------*/

//INSTALLATION INSTRUCTIONS --
// Copy this script into the account that you want to audit
// Take a Copy of the sheet located here - 
// Change the variable below labelled outputspreadsheet to include a link to your copy of the spreadsheet, inside the speech marks
// Click "Run Script Now" 
// Approve the script to access your AdWords Account & Output to Google Sheets
// View the results in the output sheet you've setup 



/*-------------------------------------------------------------------------------------------------------------------*/

// edit the below to include the link to your output sheet
var outputspreadsheet = "https://docs.google.com/spreadsheets/d/17ayBrKTQiO_fhmCdfsYKsKplkAluo2t8YKU2-UBqIo0/edit?usp=sharing";
/*-------------------------------------------------------------------------------------------------------------------*/


/*--------------------------------------------------------------------------------------------------------------------------------------*/
var sheetarray = ["rawdevstats","rawdaystats","rawhourstats","campaignstats","impsharestats"];
/*--------------------------------------------------------------------------------------------------------------------------------------*/
function main() {
  for(i=0;i<sheetarray.length;i++){clearsheetdata(outputspreadsheet,sheetarray[i]);}
  Logger.log("Old Stats Cleared"); 
  devstats();
  Logger.log("Device Stats Updated");
  daystats();
  Logger.log("Day of Week Stats Updated");
  hourstats();
  Logger.log("Hour of Day Stats Updated");
  campaignstats();
  Logger.log("Campaign Stats Updated");
  impsharestats();
  Logger.log("Imp Share Stats Updated");

   shoppingstats();

}
/*--------------------------------------------------------------------------------------------------------------------------------------*/
function devstats(){
  var segmentarray = [];
  var clickarray = [];
  var costarray = [];
  var convarray = [];
  var revarray = [];

  var report = AdWordsApp.report("".concat(
    "SELECT Device, Clicks, Cost, Conversions, ConversionValue ",
    "FROM ACCOUNT_PERFORMANCE_REPORT ",
    "DURING LAST_30_DAYS"));

var rows = report.rows();
while (rows.hasNext()) {
    var row = rows.next();
    var segment = row["Device"];
    var clicks = row["Clicks"];
    var cost = row["Cost"];
    var conv = row["Conversions"];
    var rev = row["ConversionValue"];

  segmentarray.push("".concat(segment));
  clickarray.push("".concat(clicks));
  costarray.push("".concat(cost));
  convarray.push("".concat(conv));
  revarray.push("".concat(rev));
}
  addtosheetfivevar("Device","Clicks","Cost","Conversions","Revenue","rawdevstats");
  for(i=0;i<segmentarray.length;i++){
 //  Logger.log("".concat(segmentarray[i],clickarray[i],costarray[i],convarray[i],revarray[i]));
   addtosheetfivevar(segmentarray[i],clickarray[i],costarray[i],convarray[i],revarray[i],"rawdevstats");
  }
}
/*--------------------------------------------------------------------------------------------------------------------------------------*/
function daystats(){
  var segmentarray = [];
  var clickarray = [];
  var costarray = [];
  var convarray = [];
  var revarray = [];

  var report = AdWordsApp.report("".concat(
    "SELECT DayOfWeek, Clicks, Cost, Conversions, ConversionValue ",
    "FROM ACCOUNT_PERFORMANCE_REPORT ",
    "DURING LAST_30_DAYS"));

var rows = report.rows();
while (rows.hasNext()) {
    var row = rows.next();
    var segment = row["DayOfWeek"];
    var clicks = row["Clicks"];
    var cost = row["Cost"];
    var conv = row["Conversions"];
    var rev = row["ConversionValue"];

  segmentarray.push("".concat(segment));
  clickarray.push("".concat(clicks));
  costarray.push("".concat(cost));
  convarray.push("".concat(conv));
  revarray.push("".concat(rev));
} 
  addtosheetfivevar("Day Of Week","Clicks","Cost","Conversions","Revenue","rawdaystats");
  for(i=0;i<segmentarray.length;i++){
 //  Logger.log("".concat(segmentarray[i],clickarray[i],costarray[i],convarray[i],revarray[i]));
   addtosheetfivevar(segmentarray[i],clickarray[i],costarray[i],convarray[i],revarray[i],"rawdaystats");
  }
}
/*--------------------------------------------------------------------------------------------------------------------------------------*/
function hourstats(){
  var segmentarray = [];
  var clickarray = [];
  var costarray = [];
  var convarray = [];
  var revarray = [];

  var report = AdWordsApp.report("".concat(
    "SELECT HourOfDay, Clicks, Cost, Conversions, ConversionValue ",
    "FROM ACCOUNT_PERFORMANCE_REPORT ",
    "DURING LAST_30_DAYS"));

var rows = report.rows();
while (rows.hasNext()) {
    var row = rows.next();
    var segment = row["HourOfDay"];
    var clicks = row["Clicks"];
    var cost = row["Cost"];
    var conv = row["Conversions"];
    var rev = row["ConversionValue"];

  segmentarray.push("".concat(segment));
  clickarray.push("".concat(clicks));
  costarray.push("".concat(cost));
  convarray.push("".concat(conv));
  revarray.push("".concat(rev));
} 
    addtosheetfivevar("Hour Of Day","Clicks","Cost","Conversions","Revenue","rawhourstats");
  for(i=0;i<segmentarray.length;i++){
 //  Logger.log("".concat(segmentarray[i],clickarray[i],costarray[i],convarray[i],revarray[i]));
   addtosheetfivevar(segmentarray[i],clickarray[i],costarray[i],convarray[i],revarray[i],"rawhourstats");
  }
}
/*--------------------------------------------------------------------------------------------------------------------------------------*/
function impsharestats(){
  var segmentarray = [];
  var clickarray = [];
  var costarray = [];
  var convarray = [];
  var revarray = [];

  var report = AdWordsApp.report("".concat(
    "SELECT CampaignName, Amount, Cost, Conversions, SearchImpressionShare, CampaignStatus ",
    "FROM CAMPAIGN_PERFORMANCE_REPORT ",
    "DURING LAST_30_DAYS"));

var rows = report.rows();
while (rows.hasNext()) {
    var row = rows.next();
    var segment = row["CampaignName"];
    var amount = row["Amount"];
    var cost = row["Cost"];
    var conv = row["Conversions"];
    var rev = row["SearchImpressionShare"];
  var status = row["CampaignStatus"];

  if(cost > 0){ 
    if(status == "enabled"){
  segmentarray.push("".concat(segment));
  clickarray.push("".concat(amount));
  costarray.push("".concat(cost));
  convarray.push("".concat(conv));
  revarray.push("".concat(rev));
    }
  }
} 

    addtosheetfivevar("Campaign","Daily Budget","Cost","Conversions","Impression Share","impsharestats");
  for(i=0;i<segmentarray.length;i++){
 //  Logger.log("".concat(segmentarray[i],clickarray[i],costarray[i],convarray[i],revarray[i]));
   addtosheetfivevar(segmentarray[i],clickarray[i],costarray[i],convarray[i],revarray[i],"impsharestats");
  }
}
/*--------------------------------------------------------------------------------------------------------------------------------------*/
function shoppingstats(){
  var segmentarray = [];
  var clickarray = [];
  var costarray = [];
  var convarray = [];
  var revarray = [];
  
  var report = AdWordsApp.report("".concat(
    "SELECT  OfferId, Clicks, Cost, Conversions, ConversionValue ",
    "FROM SHOPPING_PERFORMANCE_REPORT ",
    "DURING LAST_30_DAYS"));

var rows = report.rows();
while (rows.hasNext()) {
    var row = rows.next();
    var segment = row["OfferId"];
    var clicks = row["Clicks"];
    var cost = row["Cost"];
    var conv = row["Conversions"];
    var rev = row["ConversionValue"];

  segmentarray.push("".concat(segment));
  clickarray.push("".concat(clicks));
  costarray.push("".concat(cost));
  convarray.push("".concat(conv));
  revarray.push("".concat(rev));
}
  addtosheetfivevar("Product ID","Clicks","Cost","Conversions","Revenue","rawshoppingstats");
  for(i=0;i<segmentarray.length;i++){
 //  Logger.log("".concat(segmentarray[i],clickarray[i],costarray[i],convarray[i],revarray[i]));
   addtosheetfivevar(segmentarray[i],clickarray[i],costarray[i],convarray[i],revarray[i],"rawshoppingstats");
  }
}
/*--------------------------------------------------------------------------------------------------------------------------------------*/
function campaignstats(){
  var segmentarray = [];
  var clickarray = [];
  var costarray = [];
  var convarray = [];
  var revarray = [];

  var report = AdWordsApp.report("".concat(
    "SELECT CampaignName, Clicks, Cost, Conversions, ConversionValue, CampaignStatus ",
    "FROM CAMPAIGN_PERFORMANCE_REPORT ",
    "DURING LAST_30_DAYS"));

var rows = report.rows();
while (rows.hasNext()) {
    var row = rows.next();
    var segment = row["CampaignName"];
    var clicks = row["Clicks"];
    var cost = row["Cost"];
    var conv = row["Conversions"];
    var rev = row["ConversionValue"];
  var status = row["CampaignStatus"];

  if(clicks > 0){ 
    if(status == "enabled"){
  segmentarray.push("".concat(segment));
  clickarray.push("".concat(clicks));
  costarray.push("".concat(cost));
  convarray.push("".concat(conv));
  revarray.push("".concat(rev));
    }
  }
} 

    addtosheetfivevar("Campaign","Clicks","Cost","Conversions","Revenue","campaignstats");
  for(i=0;i<segmentarray.length;i++){
 //  Logger.log("".concat(segmentarray[i],clickarray[i],costarray[i],convarray[i],revarray[i]));
   addtosheetfivevar(segmentarray[i],clickarray[i],costarray[i],convarray[i],revarray[i],"campaignstats");
  }
}
/*--------------------------------------------------------------------------------------------------------------------------------------*/

/*--------------------------------------------------------------------------------------------------------------------------------------*/

/*--------------------------------------------------------------------------------------------------------------------------------------*/
function clearsheetdata(URL,SHEET) {
  var SPREADSHEET_URL = "".concat(URL);
  // Name of the specific sheet in the spreadsheet.
  var SHEET_NAME = "".concat(SHEET);

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);
  sheet.clearContents();
}
/*--------------------------------------------------------------------------------------------------------------------------------------*/
    function addtosheet(var1,var2,sheettouse) {
      
  var SPREADSHEET_URL = outputspreadsheet;
  var SHEET_NAME = sheettouse;

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);

  sheet.appendRow([var1,var2]);
}
/*-------------------------------------------------------------------------------------------------------------------------------*/
 function addtosheetfivevar(var1,var2,var3,var4,var5,sheettouse) {
      
  var SPREADSHEET_URL = outputspreadsheet;
  var SHEET_NAME = sheettouse;

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);

  sheet.appendRow([var1,var2,var3,var4,var5]);
}
/*-------------------------------------------------------------------------------------------------------------------------------*/

Share

Author Biography

Jack

Company Number: 07865143 | Company VAT: 177073296