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
- 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”
- 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 🙁