Data Studio Google Ads Experiment Reporting

Overview

Templated Google Data Studio page for displaying the results of an Experiment with statistical significance metrics included. Intended to be added to the client's primary Data Studio report for sharing on an ongoing basis.

Instructions

1. Follow the instructions on the Data Studio Report Template page to create a new report, if you haven't already.

2. Make a copy of the Google Sheet below.

Make A Copy Of The Sheet

3. Rename the file accordingly.

4. Open the client's Google Ads account and go to Tools & Settings > Bulk Actions > Scripts

5. Create a new script. Paste in the code below

6. Change the MY_GOOGLE_SHEET_URL to the URL of the Sheet you made in Step 2. Leave the double quotes in place.

7. Customize the startDate, endDate, and WHERE filter to parse data appropriately.

8. Save, Authorize, and Run the script. Check the data in the Sheet. If accurate, Schedule the script to run (at least) daily.

Google Ads Reporting Script

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

//edit the variables below

var SheetURL = "MY_GOOGLE_SHEET_URL";

var startDate = "20220628";

// Un-comment (remove // from) the third line below and comment (add // to the front of) the first two lines to set a fixed date or use the two lines below to set the end date to today's date

var now = new Date();

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

//var endDate = "20220718";

//do not edit below

var DataTabName = "Data Import";

var DateRange = startDate+','+endDate;

var QUERIES = [

{'query' : 'SELECT CampaignName, Device, Impressions, Clicks, Cost, Conversions, ConversionValue ' +

'FROM CAMPAIGN_PERFORMANCE_REPORT ' +

'WHERE CampaignName IN ["Campaign 1", "Campaign 2", "Campaign 3", "Campaign 4"] AND Impressions > 1 ' +

'DURING ' + DateRange,

'spreadsheetUrl' : SheetURL,

'tabName' : DataTabName,

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

}

9. Once the data has been loaded in the Google Sheet that you created in Step 2, go to the first tab: Experiment Key.

10. Use the dropdown in Cell C1 to select the dimension that you want to "key" the Experiment values off of.

11. The unique values of the selected dimension will appear in Cell B4. Manually assign labels to each of these values to label the "A" and "B" values of the A/B test. Check the data output on the Dataset tab.

12. Make a copy of the Data Studio data source below.

Data Source Template

13. Update the data connection to use the Dataset tab of your new Google Sheet from Step 2.

14. Refresh your page and make a copy of the Data Studio report below. Swap out the Data Source for the new Data Source you created in Step 12.

Data Studio Report Template

15. Update charts and tables accordingly. You'll most likely want to just Copy & Paste all of the elements (while in Edit mode) from this report onto the client's primary report once it's ready for sharing. If so, you can delete this report after that's been done.