AdWords Script to Export a Monthly Spend Report vs PO Number

Many media agencies struggle every month to generate a spend report for each purchase order number, so the finance team can settle accounts’ invoices and payments. The manual process can be time-consuming and mistakes could happen, especially if the number of the accounts is large.

To automate the process, we’ve written an MCC AdWords script that you can schedule to run every month, then it exports a report to a Google sheet that contains Account name, Account ID, Spend, PO Number, Month, and Currency.

 

po number script vs spend

Script Source Code

Here’s the source code for the MCC version of the script. All you need to do is to insert your spreadsheet URL in the relevant variable, but please note the max number of accounts the script can process at one time is 50 accounts.

 

/*********************************************
* PO Number vs Spend Script
* Version 1.0
* Created By: Ahmed Ali
*https://optimizationup.com/
**********************************************/

var SPREADSHEET_URL = 'Insert your spreadsheet URL Here';
var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var sheet = spreadsheet.getActiveSheet();


function main() {
 
sheet.getRange("A:H").clearContent(); 
sheet.getRange("c1").setValue("PO Number");
sheet.getRange("a1").setValue("Account ID");
sheet.getRange("b1").setValue("Account Name");
sheet.getRange("e1").setValue("Currency");
sheet.getRange("f1").setValue("Month");
sheet.getRange("d1").setValue("Spend");
var accountIterator = MccApp.accounts().withLimit(50).withCondition("Impressions > 0")
 .forDateRange("LAST_MONTH").executeInParallel('ponumber');
}

function ponumber() {
 
 var budgetOrderIterator = AdWordsApp.budgetOrders().get();
 while (budgetOrderIterator.hasNext()) {
 var budgetOrder = budgetOrderIterator.next();
 var poNumber = budgetOrder.getPoNumber();
 var startDate= formatDate(budgetOrder.getStartDateTime());
 var endDate= formatDate(budgetOrder.getEndDateTime());
 var report = AdWordsApp.report("SELECT Month, Cost " +
 "FROM ACCOUNT_PERFORMANCE_REPORT " +
 "WHERE Impressions > 0 " +
 "DURING " + startDate + ", " + endDate);
var rows = report.rows();
 while (rows.hasNext()) {
 var row = rows.next();
 
 Logger.log([row['Cost'], row['Month'], poNumber]);
 sheet.appendRow([AdWordsApp.currentAccount().getCustomerId(), AdWordsApp.currentAccount().getName(), poNumber, row['Cost'], AdWordsApp.currentAccount().getCurrencyCode(), row['Month']]);

}
 }
}

function formatDate(date) {
 function zeroPad(number) { return Utilities.formatString('%02d', number); }
 return (date == null) ? 'None' : zeroPad(date.year) + zeroPad(date.month) +
 zeroPad(date.day);
}
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.

Leave a Reply

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