Google Ads Campaign Experiment Reporting

Overview

Report on Campaign Experiments on an ongoing basis. Add this script to your Google Ads account, make a copy of the Spreadsheet, adjust your settings, and use the sheet as a reference to monitor performance and check the statistical significance of your results.

1. Copy the sheet above

2. Go to the Settings tab and adjust your Start and End Dates as needed.

3. Enter a comma-separated list of the Campaign Names that you want to include in your reporting. Make sure they match exactly what's in the platform and make sure that you don't use spaces after the commas

4. Copy the script below and paste it into your Google Ads account as a new script.

5. Change MY_GOOGLE_SHEET_URL to the URL of your new Google Sheet that you created above. Leave the quotations in place around your URL.

6. Save, run, authorize, and run!

7. Check to make sure data is filtering in on the Data Import tab of your sheet correctly.

   • Note: you can use the reference link at the top of the script to adjust the WHERE line of the script to just CONTAIN a certain string or whatever makes the most sense.

8. Save and close the script. Schedule it to run daily (or more frequently if you want!)

9. Go back to your Google Sheet and update the Campaign Names you want to compare data for on the Campaign A Statistical Significance tab (you can rename the tab and cells B6 and B7 for the test you're running). And you can also update the Campaign Names in cells B5 and B6 of the Results tab.

10. All set! Use this sheet to monitor results more easily and centralize ongoing experiments. You can duplicate the Statistical Significance tab and change Campaign Names to use this for multiple tests at once. You could also configure the sheet (see some of the monitoring Google Sheets for conditional emailing scripts) to email you when Statistical Significance is reached for any given test.

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

//*** CUSTOM VARIABLES START ***//

var SheetURL = "MY_GOOGLE_SHEET_URL";

//*** CUSTOM VARIABLES END ***//

//sheet tab name

var CampaignTabName = "Data Import";

var SettingsTabName = "Settings";

//get date range

////start date from sheet

var start = SpreadsheetApp.openByUrl(SheetURL).getSheetByName(SettingsTabName).getRange('C1').getValue();

Logger.log(start);

var startDate = new Date(start.getTime());

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

Logger.log(from);

////end date from sheet

var end = SpreadsheetApp.openByUrl(SheetURL).getSheetByName(SettingsTabName).getRange('C2').getValue();

Logger.log(end);

var endDate = new Date(end.getTime());

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

Logger.log(to);

////combine into date variable

var DateRange = from+','+to;

//campaign name contains filter

var campaignNameFilter = SpreadsheetApp.openByUrl(SheetURL).getSheetByName(SettingsTabName).getRange('B3').getValue().split(',').map(i => '"' + i + '"');

Logger.log(campaignNameFilter);

var QUERIES = [

                {'query' : 'SELECT CampaignName, Impressions, Clicks, Cost, Conversions, SearchImpressionShare, SearchAbsoluteTopImpressionShare, SearchTopImpressionShare, SearchBudgetLostImpressionShare, SearchRankLostImpressionShare ' +

                   'FROM   CAMPAIGN_PERFORMANCE_REPORT ' +

                   'WHERE  CampaignStatus = "ENABLED" AND CampaignName IN [' + campaignNameFilter + ']' +

                   'DURING ' + DateRange,

                   'spreadsheetUrl' : SheetURL,

                   'tabName' : CampaignTabName,

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

  }

}