AdWords
3.6K members online now
3.6K members online now
Use AdWords conversion tracking and reporting to measure your results. Have a question about Google Analytics? Post it here, on the Google Analytics Community!
Guide Me
star_border
Reply

Help with Adwords Script for Total Stats

Follower ✭ ☆ ☆
# 1
Follower ✭ ☆ ☆

Hi,

 

I am currently manually updating an excel spreadsheet on a weekly basis for all of my clients with high level adwords data (total clicks, impressions, costs, ctr, avg position etc.)

 

I have been trawling through the help files for some guidance on how to go about setting up a script that would update a single sheet with weekly stats of the adwords acocunt.

 

I have attached screen shot of what i have been doing manually. 

 

Can someone help me find a script that would automate this into a Google Spreadsheet?

 

spreadsheet.jpg

Re: Help with Adwords Script for Total Stats

[ Edited ]
Follower ✭ ✭ ✭
# 2
Follower ✭ ✭ ✭

I'm not a huge Adwords Script buff but I have looked at Russell Savage's Adwords Script blog and I'd be very surprised if you couldn't find your answer there.

 

http://www.freeadwordsscripts.com/

 

Also, you could try this post by PPCHero as well.

 

http://www.ppchero.com/3-easy-adwords-scripts-to-analyze-your-account-part-1/

Re: Help with Adwords Script for Total Stats

Follower ✭ ☆ ☆
# 3
Follower ✭ ☆ ☆
Hi Mark,

I researched both these sites before posting. But thanks for your input. I have sent a message to Russell to see if he can point me in the right direction.

Once I have the script working, I will share with you guys.

Re: Help with Adwords Script for Total Stats

Follower ✭ ✭ ✭
# 4
Follower ✭ ✭ ✭

Sounds Great, very interested in that.

Re: Help with Adwords Script for Total Stats

[ Edited ]
Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

Hi Guys, Thanks for mentioning my blog.  I think this post might be what you are looking for:

Store Account Level Quality Score in a Google Spreadsheet

 

Check out the comments, specifically the question and answer I provided to Marco.  I think that is what you are looking for.  If you have any questions or comments, please feel free to post a comment and I will get back to you.

 

Thanks,

Russ

 

Re: Help with Adwords Script for Total Stats

Follower ✭ ✭ ✭
# 6
Follower ✭ ✭ ✭

Russell, classy move.

Re: Help with Adwords Script for Total Stats

Follower ✭ ☆ ☆
# 7
Follower ✭ ☆ ☆

Hey Guys,

 

Got the script working exactly how i want to use it. 

 

I am pulling account level data into a spreadsheet and scheduled it to run every week!!

 

Here is the script that I am using: -

=========================================

function main() {
var SPREADSHEET_URL = "SPREADSHEET URL";
var ACCOUNT_NAME = 'YOUR SHEET TAB NAME HERE';
var today = new Date();
var date_str = [today.getDate(),(today.getMonth() + 1),today.getFullYear()].join("/");

var spreadsheet = getSpreadsheet(SPREADSHEET_URL);
var qs_sheet = spreadsheet.getSheetByName(ACCOUNT_NAME);

var kw_iter = AdWordsApp.keywords()
.forDateRange("LAST_7_DAYS")
.get();

var tot_imps_weighted_qs = 0;
var tot_imps = 0;
var tot_clicks = 0;
var tot_impr = 0;
var tot_cost = 0;
var tot_conv = 0;

while(kw_iter.hasNext()) {
var kw = kw_iter.next();
var kw_stats = kw.getStatsFor("LAST_7_DAYS");
var imps = kw_stats.getImpressions();
var qs = kw.getQualityScore();
tot_imps_weighted_qs += (qs * imps);
tot_imps += imps;
tot_clicks += kw_stats.getClicks();
tot_impr += kw_stats.getImpressions();
tot_cost += kw_stats.getCost();
tot_conv += kw_stats.getConversions();
}

var acct_qs = tot_imps_weighted_qs / tot_imps;
var ctr = (tot_clicks / tot_impr) * 100;
var avg_cpc = tot_cost / tot_clicks;
var cost_conv = tot_cost / tot_conv;
var conv_rate = (tot_conv / tot_clicks) * 100;

qs_sheet.appendRow([date_str,acct_qs,tot_clicks,tot_impr,ctr,avg_cpc,tot_cost,tot_conv,cost_conv,conv_rate]);

function getSpreadsheet(spreadsheetUrl) {
var matches = new RegExp('key=([^&#]*)').exec(spreadsheetUrl);
if (!matches || !matches[1]) {
throw 'Invalid spreadsheet URL: ' + spreadsheetUrl;
}
var spreadsheetId = matches[1];
return SpreadsheetApp.openById(spreadsheetId);
}
}

=====================================================

 

It is built of Russell's script.

 

I have noticed that Russell has added a new script to his blog for Account Level stats, which is obviously more in depth than my script so worth looking at that too. As I will be revisiting mine to include Many Per Click conversions, especially for my e-commerce clients.

 

:-)

 

Once again, thanks to Mark & Russell for your input.