AdWords scripts provide a way to programmatically control your AdWords data using simple JavaScript in a browser-based IDE. You can use scripts to automate common procedures or interact with external data – for one to many AdWords accounts.

Source: Google

I am very lazy person, I prefer to sit, reading books, drinking mohito while listening to the waves of sea. Unfortunately I haven’t won the lottery yet so from time to time I have to earn some money. That’s boring but my bank doesn’t understand that mortgage is obsolete invention.

Monday, in the company I work for, is a day to create reports, a lot of reports. Unfortunately most of the effort is completely useless because reports are printed but not analyzed. It has to be done so why not to automate every Monday morning?

I am responsible for AdWords reports – there are plenty accounts used in my company for different kind of campaigns (this is why I use AdWords client center – MCC) so creating reports for all of them is quite time-consuming.

Goal

To automate the reporting process for many AdWords accounts – create script which will automatically send us some information about our campaigns:

  • cross-account reporting
  • cost, clicks, CPC, impressions, CPM for all accounts
  • cost, clicks, CPC, impressions, CPM for all active campaigns

Tool kit

AdWords script for MCC writing in JavaScript

Scripts for MCC allow you to:

  • manage multiple accounts from a single script
  • proccess multiple accounts in parallel
  • changing / fixing scripts in one place without necessity to make changes in many places

Lets start – create your script

  • login to your MCC account
  • go to “Scripts” tab
  • create new script “+Script”
  • your first, empty function is waiting for you

[js]
function main() {

}
[/js]

  • rename your script
  • before first usage you have to authorise script with your Google credentials
First AdWords function
First AdWords function
  • now it’s high time for weekly report script

[js]
function main() {
var mailAddress = ’email@address.com’;

//—————– FUNCTIONS —————————————–
// ——- add thousands separator ————————————

function addThousandsSeparator(value) {
return value.toString().replace(/(\d)(?=(\d\d\d)+(?!\d))/g, "$1 ");
}

//———————————————————————
//get Monday and Sunday of the previous week

var beforeOneWeek = new Date(new Date().getTime() – 60 * 60 * 24 * 7 * 1000)
, day = beforeOneWeek.getDay()
, diffToMonday = beforeOneWeek.getDate() – day + (day === 0 ? -6 : 1)
, lastMonday = new Date(beforeOneWeek.setDate(diffToMonday))
, lastSunday = new Date(beforeOneWeek.setDate(diffToMonday + 6));

//———————————————————————
//get previous monday week number

Date.prototype.getWeek = function(lastMonday) {
var onejan = new Date(this.getFullYear(), 0, 1); //first day of January current year
return Math.ceil((((lastMonday – onejan) / 86400000) + onejan.getDay() + 1) / 7);
}

//———————————————————————-
// dd-mm-yyyy of previous Monday

var dFirstday = lastMonday.getDate();
var mFirstday = lastMonday.getMonth() + 1;
var yFirstday = lastMonday.getYear();
var dmyFirstday = dFirstday + ‘.’ + mFirstday + ‘.’ + yFirstday;

//———————————————————————-
// dd-mm-yyyy of previous Sunday

var dLastday = lastSunday.getDate();
var mLastday = lastSunday.getMonth() + 1;
var yLastday = lastSunday.getYear();
var dmyLastday = dLastday + ‘.’ + mLastday + ‘.’ + yLastday;

//———————————————————————-
// previous week number

var weekNumber = (new Date()).getWeek(lastMonday);

//——- function for every account from my MCC —————————

function previousMain() {
//select campaigns with impressions for last week for every account

var activeCampaignsInfo = [];
var i = 0;

var campaignSelector = AdWordsApp
.campaigns()
.withCondition("Impressions != 0")
.forDateRange("LAST_WEEK"); // TODAY, YESTERDAY, LAST_7_DAYS, THIS_WEEK_SUN_TODAY, LAST_WEEK, LAST_14_DAYS, LAST_30_DAYS, LAST_BUSINESS_WEEK, LAST_WEEK_SUN_SAT, THIS_MONTH, LAST_MONTH, ALL_TIME.

var campaignIterator = campaignSelector.get();

while(campaignIterator.hasNext()){
var campaign = campaignIterator.next();
var campaignName = campaign.getName();
var biddingStrategyType = campaign.getBiddingStrategyType(); //MANUAL_CPC, MANUAL_CPM, BUDGET_OPTIMIZER, CONVERSION_OPTIMIZER, PERCENT_CPA
var campaignStats = campaign.getStatsFor("LAST_WEEK");
var cost = campaignStats.getCost();
var clicks = campaignStats.getClicks();
var impressions = campaignStats.getImpressions();

activeCampaignsInfo[i] = [campaignName, biddingStrategyType, cost, clicks, impressions];
i++;
}
return activeCampaignsInfo;
}

//——- all account’s information function ——————–

var report = function getAllAccounts(mailAddress) {
var accountIterator = MccApp.accounts().get();

//array for stats
var stats = [];
var i = 0;
var activeCampaignsInfo = [];

//select accounts which were active LAST_WEEK
var accountSelector = MccApp
.accounts()
.withCondition("Impressions != 0")
.forDateRange("LAST_WEEK");

var accountIterator = accountSelector.get();

// ——- loop for accounts ——————–

var costTotal = clicksTotal = cpcTotal = impressionsTotal = cpmTotal = 0;
while (accountIterator.hasNext()) {

//———- accounts info ——————–

var account = accountIterator.next();

//———- account name / id ——————–

var accountName = account.getName();
var accountId = account.getCustomerId();
var currency = account.getCurrencyCode();

//———- run function for every account ——————–

MccApp.select(account);
activeCampaignsInfo[i] = previousMain();

//——- LAST_WEEK stats for account ——————–

var accountStats = account.getStatsFor(‘LAST_WEEK’);

//——- values ——————–

var cost = accountStats.getCost();
var costTotal = costTotal + cost;
var clicks = accountStats.getClicks();
var clicksTotal = clicksTotal + clicks;
var impressions = accountStats.getImpressions();
var impressionsTotal = impressionsTotal + impressions;
var cpc = cost / clicks;
var cpm = cost / impressions * 1000;

//——– volues + formating ——————–
var cost = addThousandsSeparator(cost.toFixed(2) + ‘ ‘ + currency);
var clicks = addThousandsSeparator(clicks);
var impressions = addThousandsSeparator(accountStats.getImpressions());
var cpc = cpc.toFixed(2) + ‘ ‘ + currency;
var cpm = cpm.toFixed(2) + ‘ ‘ + currency;

//——- array with stats ——————–
stats[i] = [accountId, accountName, cost, clicks, cpc, impressions, cpm];
i++;
}

// —– end of loop for accounts ——————–

var cpcTotal = costTotal / clicksTotal;
var cpcTotal = addThousandsSeparator(cpcTotal.toFixed(2) + ‘ ‘ + currency);
var cpmTotal = costTotal / impressionsTotal * 1000;
var cpmTotal = addThousandsSeparator(cpmTotal.toFixed(2) + ‘ ‘ + currency);
var impressionsTotal = addThousandsSeparator(impressionsTotal);
var costTotal = addThousandsSeparator(costTotal.toFixed(2) + ‘ ‘ + currency);
var clicksTotal = addThousandsSeparator(clicksTotal);

//——— creating mail elements (subject, body) ——————–

var subject = ‘WEEK-‘ + weekNumber + ‘ weekly stats for all active MCC / adwords accounts:’;

var body = ‘<table><tr><td rowspan="4"><img src="http://some-logo-for-report.jpg"></td>’;
var body = body + ‘<td><h2>Weekly stats for all active MCC / adwords accounts</h2></td></tr>’;
var body = body + ‘<tr><td><h3>WEEK-‘ + weekNumber + ‘</h3></td></tr>’;
var body = body + ‘<tr><td><h3>’ + dmyFirstday + ‘ – ‘ + dmyLastday + ‘<h3></td></tr></table>’;

var body = body + ‘<table border="1" style="border-collapse: collapse; padding: 3px;">’ +
‘<th align="center">Account</th>’ +
‘<th align="center">Cost</th>’ +
‘<th align="center">Clicks</th>’ +
‘<th align="center">CPC</th>’ +
‘<th align="center">Impressions</th>’ +
‘<th align="center">CPM</th>’;

for(i=0; i<stats.length; i++){
var body = body + ‘<tr>’;
var body = body +
‘<td align="left">’ + stats[i][1] + ‘</td>’ + //name
‘<td align="right">’ + stats[i][2] + ‘</td>’ + //cost
‘<td align="right">’ + stats[i][3] + ‘</td>’ + //clicks
‘<td align="right">’ + stats[i][4] + ‘</td>’ + //cpc
‘<td align="right">’ + stats[i][5] + ‘</td>’ + //impressions
‘<td align="right">’ + stats[i][6] + ‘</td>’; //cpm
var body = body + ‘</tr>’;
}

var body = body +
‘<th style = "align: center; padding: 15px" >TOTAL</th>’ +
‘<th style = "align: center; padding: 15px" >’ + costTotal + ‘</th>’ +
‘<th style = "align: center; padding: 15px" >’ + clicksTotal + ‘</th>’ +
‘<th style = "align: center; padding: 15px" >’ + cpcTotal + ‘</th>’ +
‘<th style = "align: center; padding: 15px" >’ + impressionsTotal + ‘</th>’ +
‘<th style = "align: center; padding: 15px" >’ + cpmTotal + ‘</th>’;
var body = body + ‘</tr>’;

var body = body + ‘</table>’;

var body = body + ‘<hr><h2>Active campaigns for all active MCC accounts:<h2>’;

//active campaigns for every account info ——————–

var activeCampaignsInfoBody = ”;
for(i=0; i<activeCampaignsInfo.length; i++){
var activeCampaignsInfoBody = activeCampaignsInfoBody + ‘<h3>’ + stats[i][1] + ‘</h3><table border="1" style="font-size: x-small; border-collapse: collapse;">’; //account name
var activeCampaignsInfoBody = activeCampaignsInfoBody +
‘<th align="center">Campaign</th>’ +
‘<th align="center">BiddingStrategyType</th>’ +
‘<th align="center">Cost</th>’ +
‘<th align="center">Clicks</th>’ +
‘<th align="center">CPC</th>’ +
‘<th align="center">Impressions</th>’ +
‘<th align="center">CPM</th>’;

for(j=0; j<activeCampaignsInfo[i].length; j++){
var activeCampaignsInfoBody = activeCampaignsInfoBody + ‘<tr>’;
var activeCampaignsInfoBody = activeCampaignsInfoBody +
‘<td style="align: left; padding: 3px">’ + activeCampaignsInfo[i][j][0] + ‘</td>’ + //Campaign
‘<td style="align: left; padding: 3px">’ + activeCampaignsInfo[i][j][1] + ‘</td>’ + //BiddingStrategyType
‘<td style="align: right; padding: 3px">’ + addThousandsSeparator((activeCampaignsInfo[i][j][2]).toFixed(2)) + ‘ ‘ + currency + ‘</td>’ + //Cost
‘<td style="align: right; padding: 3px">’ + addThousandsSeparator(activeCampaignsInfo[i][j][3]) + ‘</td>’ + //Clicks
‘<td style="align: right; padding: 3px">’ + (activeCampaignsInfo[i][j][2] / activeCampaignsInfo[i][j][3]).toFixed(2) + ‘ ‘ + currency + ‘</td>’ + //CPC
‘<td style="align: right; padding: 3px">’ + addThousandsSeparator(activeCampaignsInfo[i][j][4]) + ‘</td>’ + //Impressions
‘<td style="align: right; padding: 3px">’ + (activeCampaignsInfo[i][j][2] / activeCampaignsInfo[i][j][4] * 1000).toFixed(2) + ‘ ‘ + currency + ‘</td>’; //CPM

var activeCampaignsInfoBody = activeCampaignsInfoBody + ‘</tr>’;
var activeCampaignsInfoBody = activeCampaignsInfoBody + ‘<tr>’;
}

var activeCampaignsInfoBody = activeCampaignsInfoBody + ‘</table>’;
}

var body = body + activeCampaignsInfoBody;

//——- it’s hight time to send report ——————–

MailApp.sendEmail({
to: mailAddress,
subject: subject,
htmlBody: body,
});
}
report(mailAddress);
}
[/js]

  • Save your script

Add schedule for firing your script every week

  • “Change schedule”
Schedule
Schedule
  • Save

So, here we are, something about an hour weekly saved and you have more time for coffee…?

PS. I have no idea why SyntaxHighlighter plugin doesn’t work with that JS code and from line 122 there is something wrong with ‘ symbols 🙁

Leave a comment

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