Ad Copy Analysis Report

Overview

This sheet will take the data provided by the script below to assemble five reports on ad performance: Headline Performance, Description Performance, Final URL Performance, Ad Copy Performance (compiles insights based on unique copy combinations), and Unique Ad Performance (based on Ad ID).

Getting Started

• Make a copy of the Google Sheet using the link below

• Rename the sheet & store it somewhere in your Drive!

• Right now the sheet has some sample data, so we'll need to add more data in from Google Ads using the script below.

• Go to Google Ads. Then Tools > Bulk Actions > Scripts

• Create a new script named "Ad Copy Testing" and paste in the code below.

• Once you've pasted in the code, just change MY_SPREADSHEET_URL to the link of the new sheet you created earlier.

• After that, you can adjust the date range that you want to pull data from.

  • • Currently, the report is set to pull data from 1/1/19 to today. You'll notice that in lines 12-15 there are 2 sets of from and to variables. For each, the first version is dynamic (i.e. today or X number of days ago), and the second one is just a preset date with format YYYYMMDD. You can switch between these by just cutting and pasting the // in front of the commented out variable in front of the version you don't want to use.

• Only other thing to do is to adjust the filters on the data. See line 23 for this. Currently, the script filters out any ads without Impressions and any Ad Types other than ETAs and RSAs. This sheet is only set up to handle these two ad types. 

  • • To add any other filters, you can reference the set of available variables here.

• Save, Authorize, and Run The Script and you should be good to go!

// Reference: https://developers.google.com/adwords/api/docs/guides/awql

var SheetURL = "MY_SPREADSHEET_URL";

var AdTabName = "Ad Import";

// GET LAST 365 Days

  var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;

  var startDate = new Date() - 365 * MILLIS_PER_DAY;

//get today's date

  var now = new Date();

  var endDate = new Date(now.getTime() - 0 * MILLIS_PER_DAY);

//set variables. select which format you want to use – the preformatted start & end dates of custom set dates. simply comment out the option you don't want to use.

  //var from = Utilities.formatDate(startDate, "GMT-5", "yyyyMMdd");

  var from = "20190101";

  var to = Utilities.formatDate(endDate, "GMT-5", "yyyyMMdd");

  //var to = "20191231";

//set final variable

var DateRange = from+','+to;

var QUERIES = [

                {'query' : 'SELECT CampaignName, AdGroupName, Id, Status, AdType, HeadlinePart1, HeadlinePart2, ExpandedTextAdHeadlinePart3, ResponsiveSearchAdHeadlines, Description, ExpandedTextAdDescription2, ResponsiveSearchAdDescriptions, CreativeFinalUrls, Impressions, Clicks, Ctr, AverageCpc, Cost, Conversions, ConversionRate, CostPerConversion, ConversionValue ' +

                   'FROM   AD_PERFORMANCE_REPORT ' +

                   'WHERE  AdType IN ["EXPANDED_TEXT_AD", "RESPONSIVE_SEARCH_AD"] AND Impressions > 0 ' +

                   'DURING ' + DateRange,

                   'spreadsheetUrl' : SheetURL,

                   'tabName' : AdTabName,

                   'reportVersion' : 'v201809'

                }

              ];

function main() {

  for(var i in QUERIES) {

    var queryObject = QUERIES[i];

    var query = queryObject.query;

    var spreadsheetUrl = queryObject.spreadsheetUrl;

    var tabName = queryObject.tabName;

    var reportVersion = queryObject.reportVersion;

    //Logger.log(spreadsheetUrl + " " + query);

    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);

    var sheet = spreadsheet.getSheetByName(tabName);

    var report = AdWordsApp.report(query, {apiVersion: reportVersion});

    report.exportToSheet(sheet);

  }

  var dateSheet = spreadsheet.getSheetByName("Date Range Confirmation");

  var dateSheetRangeStart = dateSheet.getRange("A2");

  var dateSheetRangeEnd = dateSheet.getRange("B2");

  dateSheetRangeStart.setValue(from);

  dateSheetRangeEnd.setValue(to);

}