Optimization Up

Searching for a tool or a service that can easily enable you to report and create dashboards of your Google Adwords campaigns. No Need, we have written a Google Ads script that will save you an hour every week. Either you are on the agency side or the client side, creating Google Ads weekly reports isn’t that much fun. You would rather spend that time (to collect and visualize the data) optimizing your campaigns.

With our script, you can automate your weekly report and use great-looking graphs and data that are presented in a professional look and feel. Through the interactive dashboard, your clients will have access to the KPIs that matter, segmented by network and device type as seen below.

The script fetches statistics in the past five weeks (Monday to Sunday). If you would like to change the number of weeks or even the week type (e.g. Sunday to Saturday), please email us and will modify and send the script source code to you.

How to you use the script?

1- Open the template spreadsheet to make a copy of it.
# In the spreadsheet, menu, select “File -> Make a copy”.
# Enter the new spreadsheet name, click “OK”.

2- In AdWords, navigate to Scripts.

# Create a new AdWords script with the source code below.
# Don’t forget to update INSERT_SPREADSHEET_URL_HERE in your code.
# Schedule the script Weekly (Monday).

Source Code

/***************************************************
* AdWords Weekly Report Script
* Version 1.0
* Created By: Ahmed Ali
*https://optimizationup.com/
****************************************************/

var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
var SHEET_NAME = 'Source'; 
var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var sheet = spreadsheet.getSheetByName(SHEET_NAME);


function main() { 
 
 
 sheet.getRange("A:H").clearContent(); 

var last5WeeksMonday = new Date();
var lastweekSunday = new Date();

// set to Monday of this week

last5WeeksMonday.setDate(last5WeeksMonday.getDate() - (last5WeeksMonday.getDay() + 6) % 7);
lastweekSunday.setDate(lastweekSunday.getDate() - (lastweekSunday.getDay() + 6) % 7);



// set to previous Monday

last5WeeksMonday.setDate(last5WeeksMonday.getDate() - 35);

lastweekSunday.setDate(lastweekSunday.getDate() - 1);

var Last5WeeksMonday = Utilities.formatDate(last5WeeksMonday, 'PST', 'yyyyMMdd');
var LastweekSunday = Utilities.formatDate(lastweekSunday, 'PST', 'yyyyMMdd');

var d = new Date(); d.getDate(); var today = Utilities.formatDate(d, 'PST', 'yyyyMMdd');

sheet.getRange("A1").setValue("Start Date");
sheet.getRange("A2").setValue("End Date");
sheet.getRange("B1").setValue(Last5WeeksMonday);
sheet.getRange("B2").setValue(LastweekSunday);
sheet.getRange("A3").setValue("Frequency");
sheet.getRange("B3").setValue("Last Five Weeks");
sheet.getRange("A4").setValue("Last Execution");
sheet.getRange("A5").setValue("Copyright");
sheet.getRange("B5").setValue("https://optimizationup.com/");
sheet.getRange("B4").setValue(today);
sheet.getRange("A8").setValue("Week");
sheet.getRange("B8").setValue("Impressions");
sheet.getRange("C8").setValue("Clicks");
sheet.getRange("D8").setValue("Cost");
sheet.getRange("E8").setValue("Conversions");
sheet.getRange("F8").setValue("Revenue");
sheet.getRange("G8").setValue("Network");
sheet.getRange("H8").setValue("Device");
sheet.getRange("A8").setValue("Week");
 

var report = AdWordsApp.report("SELECT Week, Clicks, Impressions, Ctr, AverageCpc, Cost, Conversions, AdNetworkType1, ConversionValue " +
 "FROM ACCOUNT_PERFORMANCE_REPORT " +
 "WHERE Impressions > 0 " +
 "DURING " + Last5WeeksMonday + ", " + LastweekSunday);

 
 var rows = report.rows();
 while (rows.hasNext()) {
 var row = rows.next();
 
 
sheet.appendRow([ row['Week'], row['Impressions'], row['Clicks'] , row['Cost'] , row['Conversions'],row['ConversionValue'], row['AdNetworkType1'], "All" ]);
 
 }

 var report = AdWordsApp.report("SELECT Week, Clicks, Impressions, Ctr, AverageCpc, Cost, Conversions, ConversionValue " +
 "FROM ACCOUNT_PERFORMANCE_REPORT " +
 "WHERE Impressions > 0 " +
 "DURING " + Last5WeeksMonday + ", " + LastweekSunday);

 var rows = report.rows();
 while (rows.hasNext()) {
 var row = rows.next();
 
 sheet.appendRow([ row['Week'], row['Impressions'], row['Clicks'] , row['Cost'] , row['Conversions'],row['ConversionValue'], "All" , "All" ]);
 
}

 var report = AdWordsApp.report("SELECT Week, Clicks, Impressions, Ctr, AverageCpc, Cost, Conversions, AdNetworkType1, Device, ConversionValue " +
 "FROM ACCOUNT_PERFORMANCE_REPORT " +
 "WHERE Impressions > 0 " +
 "DURING " + Last5WeeksMonday + ", " + LastweekSunday);

 var rows = report.rows();
 while (rows.hasNext()) {
 var row = rows.next();
 
 sheet.appendRow([ row['Week'], row['Impressions'], row['Clicks'] , row['Cost'] , row['Conversions'],row['ConversionValue'], row['AdNetworkType1'] , row['Device'] ]);
 
}
var report = AdWordsApp.report("SELECT Week, Clicks, Impressions, Ctr, AverageCpc, Cost, Conversions, Device, ConversionValue " +
 "FROM ACCOUNT_PERFORMANCE_REPORT " +
 "WHERE Impressions > 0 " +
 "DURING " + Last5WeeksMonday + ", " + LastweekSunday);

 var rows = report.rows();
 while (rows.hasNext()) {
 var row = rows.next();
 
 sheet.appendRow([ row['Week'], row['Impressions'], row['Clicks'] , row['Cost'] , row['Conversions'],row['ConversionValue'], "All" , row['Device'] ]);
 
}

var report = AdWordsApp.report("SELECT Week, Clicks " +
 "FROM ACCOUNT_PERFORMANCE_REPORT " +
 "WHERE Impressions > 0 " +
 "DURING " + Last5WeeksMonday + ", " + LastweekSunday);

var rows = report.rows()
for (var row = 1; rows.hasNext(); row ++) {
 var rowx = rows.next();
 
 sheet.getRange("D1").setValue("The 5 Weeks Sorted ASC"); 
 sheet.getRange("E" + row).setValue(rowx['Week']);
 
 
}


var range = sheet.getRange("E1:F6");
range.sort(5);
}

Ahmed Ali

Entrepreneur focused on building MarTech products that bridge the gap between marketing and technology. Check out my latest products Marketing Auditor & GA4 Auditor – discover all my products here.