Automate Weekly Reports Using AdWords Scripts

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 an AdWords script that will save you an hour every week. Either you are on the agency side or the client side, creating Adwords weekly reports isn’t that much fun. You would rather spend that time (to collect and visualize the data) on optimizing your campaigns.

With our script, you can automate your weekly report and use great-looking graphs and data that is 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);
}

 

mm
Ahmed launched his first digital campaign in 2013 through participating in Google Online Marketing Challenge. He competed with over 12,000 students from 80 countries to win the first place in Africa & MENA region and one of the best five campaigns in the world. Today, he works as an Associate Digital Director at Keyade Middle East.

4 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *