Adwords Script to track Quality Score on Account, campaign & Ad Group Level

by Joel on April 9, 2013 · 80 comments

in PPC

Warning: this post is a bit more technical than most of our posts as we go into some depth on Adwords scripts. BUT – do not worry – if you follow the post through you will hopefully be able to just follow the instructions and achieve what you need to achieve with no coding at all. It is aimed at getting ALL PPC marketers using a basic Adwords script to track quality score.

About 6 weeks ago I read this brilliant post by Frederick Vallaeys where he gave up a simple Google Adwords script that allows you to track the Quality Score of an Adwords account every day in an automated fashion by keeping a daily log in a Google Document.

This was my introduction to Google scripts as to be honest I am not a coder and apart from basic HTML any kind of code scares me a little. However – Frederick gave the instructions and the code so all I had to do was copy and paste it into my clients Adwords accounts – the screen shot below shows you where this needs to go in your Adwords account. On the left hand menu go to:

Bulk Operations >> Scripts:

Google Adwords scripts


I started collecting the data and within 1 day I had a strong desire to track the quality score of campaigns and ad groups. I did a lot of digging around the Google Adwords developer forum for scripts and eventually found another blinding post on quality score tracking by Martin Roettgerding of Bloofusion.

In this post Martin explains how to track the quality score of your most important keywords over time – up to around 100 of them.

This was a step in the right direction BUT I really felt that what we needed for our clients was the ability to track campaigns and ad groups as that way we can prioritise time and effort efficiently. For example if a specific campaign makes up 20% of the total Adwords spend and it’s average quality score is just 5 then it is one that needs attention as there are large cost savings to be made.

Martin kindly gave me some advice after I left this query on his post and directed me to some Adwords scripts. I spent a frustrating morning trying to hack together scripts into a working format and unfortunately got nowhere. Luckily one of our team is far more adept with scripts than me and took the reigns. Within a few hours he had a working script that did what we wanted – it tracks quality score at account, campaign and ad group level.

Today we will be sharing that script with you.

Caution: Adwords scripts time out and break if they do not complete within a few minutes so if you have hundreds of campaigns and thousands of ad groups you will not be able to track everything with this script. The way we have been using it is to track the campaigns with the most spend and to then dig into those that have the lowest quality score on an ad group level so that specific actions can be taken – improving ad copy relevance and developing negative keywords, splitting out keywords into separate ad groups etc.

Step by step guide to tracking Adwords quality score by account, campaign and ad group

Step 1 – prepare a Google Spreadsheet

Copy a Google SpreadsheetHere is the spreadsheet template – click on this link and then click on “Make a copy” to copy it: https://docs.google.com/spreadsheet/ccc?key=0At6gVZSGVZXTdC1FdFNHcFZyWlQ2cUxUOWxVQjRCZVE&usp=sharing.

Make sure that the Account tabs contains Account name in 1st column, Campaign tabs must contain Campaign names in 1st column and Adgroup tabs must contain Campaign and Adgroup names in 1st and 2nd columns. This way you can decide which Campaign and Adgroups you want to track. This is important as if the account size is too big the script might time out as Google allows script run time to be only 30 minutes. In the sheet, I’ve included names such as Campaign-01 but they need to be changed with actual Account, Campaign and Ad group names.

2. Go to Google Scripts

In your Adwords account you should see on the left hand menu an option called “bulk operations” – click on that. There are 3 options; under scripts, click on “create and manage scripts”

There will be a green button that says +create script – click on that.

You should see a screen like this:

Create an Adwords script

3. Paste in the script

Paste in the following script ensuring that you replace any other script in the box in the Google interface;

do not copy the opening and closing code tags in square brackets:

[code language="text"]

var spreadsheet_url = "https://docs.google.com/spreadsheet/ccc?key=0At6gVZSGVZXTdC1FdFNHcFZyWlQ2cUxUOWxVQjRCZVE&usp=sharing";
var email_address = "myemail@mycompany.com";

function main() {
var matches = new RegExp('key=([^&#]*)').exec(spreadsheet_url);
if (!matches || !matches[1]) throw 'Invalid spreadsheet URL: ' + spreadsheetUrl;
var spreadsheetId = matches[1];
var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
var account_sheet = spreadsheet.getSheetByName('Account');
var campaign_sheet = spreadsheet.getSheetByName('Campaigns');
var adgroup_sheet = spreadsheet.getSheetByName('Adgroups');
var account_sheet_values = account_sheet.getDataRange().getValues();
var campaign_sheet_values = campaign_sheet.getDataRange().getValues();
var adgroup_sheet_values = adgroup_sheet.getDataRange().getValues();
var result_range = new Array();
var adgroup_result_range = new Array();
var account_alert_text = new Array();
var campaign_alert_text = new Array();
var adgroup_alert_text = new Array();
var campaign_history = new Array();
var account_history = new Array();
var adgroup_history = new Array();
var currentTime = new Date();
var today = (currentTime.getMonth() + 1) + "/" + currentTime.getDate() + "/" + currentTime.getFullYear();

//Account QS Starts

for(i = 1; i < account_sheet_values.length; i++){
if(account_sheet_values[i][0] == "") continue;
result_range[i] = [today, 0];
var account_name = account_sheet_values[i][0];
var latest_check = account_sheet_values[i][1];
var old_quality_score = account_sheet_values[i][2];
var totalImpressionsAnalyzed = 0;
var totalQualityScoreAnalyzed = 0;
var keywordIterator = AdWordsApp.keywords()
.withCondition("Status = ACTIVE")
.forDateRange("LAST_30_DAYS")
.withCondition("Impressions > 0")
.orderBy("Impressions DESC")
.withLimit(50000)
.get();

while (keywordIterator.hasNext()) {
var keyword = keywordIterator.next();
var qualityScore = keyword.getQualityScore();
var keywordStats = keyword.getStatsFor("LAST_30_DAYS");
var impressions = keywordStats.getImpressions();
var qualityScoreContribution = qualityScore * impressions;
totalQualityScoreAnalyzed = totalQualityScoreAnalyzed + qualityScoreContribution;
totalImpressionsAnalyzed = totalImpressionsAnalyzed + impressions;
}

var accountQualityScore = totalQualityScoreAnalyzed / totalImpressionsAnalyzed;
var FinalAccountQualityScore = accountQualityScore.toFixed(2);

// Save account quality score for results
result_range[i][1] = FinalAccountQualityScore;
// for the history we note the change
if(old_quality_score > 0) var change = (FinalAccountQualityScore - old_quality_score).toFixed(1);
else var change = "NEW";
var column = [FinalAccountQualityScore];
account_history.push(column);
account_alert_text.push(FinalAccountQualityScore + "\t" + old_quality_score + "\t" + change + "\t" + account_name);
}

// write results to spreadsheet
result_range.splice(0,1);
account_sheet.getRange(2, 2, result_range.length, 2).setValues(result_range);
// write history to spreadsheet
var history_sheet = spreadsheet.getSheetByName('Account QS history');
history_sheet.getRange(2,history_sheet.getLastColumn()+1, account_history.length, 1).setValues(account_history);
history_sheet.getRange(1,history_sheet.getLastColumn(), 1, 1).setValue(today);

//Account QS Ends

//Campaign QS Starts

for(i = 1; i < campaign_sheet_values.length; i++){
// make sure there is actually some data here
if(campaign_sheet_values[i][0] == "") continue;
result_range[i] = [today, 0];
var campaign_name = campaign_sheet_values[i][0];
var latest_check = campaign_sheet_values[i][1];
var old_quality_score = campaign_sheet_values[i][2];
var totalImpressionsAnalyzed = 0;
var totalQualityScoreAnalyzed = 0;
var keywordIterator = AdWordsApp.keywords()
.withCondition("CampaignName = '" + campaign_name + "'")
.withCondition("CampaignStatus = ENABLED")
.withCondition("AdGroupStatus = ENABLED")
.orderBy("Impressions")
.forDateRange("LAST_30_DAYS")
.withLimit(50000)
.get();

while(keywordIterator.hasNext()){
var keyword = keywordIterator.next();
var current_quality_score = keyword.getQualityScore();
var keywordStats = keyword.getStatsFor("LAST_30_DAYS");
var impressions = keywordStats.getImpressions();
var qualityScoreContribution = current_quality_score * impressions;
totalQualityScoreAnalyzed = totalQualityScoreAnalyzed + qualityScoreContribution;
totalImpressionsAnalyzed = totalImpressionsAnalyzed + impressions;
}

var CampaignQualityScore = totalQualityScoreAnalyzed / totalImpressionsAnalyzed;
var FinalCampaignQualityScore=CampaignQualityScore.toFixed(2);

// save quality score for results
result_range[i][1] = FinalCampaignQualityScore;
// for the history we note the change
if(old_quality_score > 0) var change = (FinalCampaignQualityScore - old_quality_score).toFixed(1);
else var change = "NEW";

var column = [FinalCampaignQualityScore];
campaign_history.push(column);
// if we have a previously tracked quality score and it's different from the current one, we make a note to log it and send it via email later
if(old_quality_score > 0 && CampaignQualityScore != old_quality_score){
campaign_alert_text.push(FinalCampaignQualityScore + "\t" + old_quality_score + "\t" + change + "\t" + campaign_name);
}
}

// write results to spreadsheet
result_range.splice(0,1);
campaign_sheet.getRange(2, 2, result_range.length, 2).setValues(result_range);
// write history to spreadsheet
var history_sheet = spreadsheet.getSheetByName('Campaigns QS history');
history_sheet.getRange(2,history_sheet.getLastColumn()+1, campaign_history.length, 1).setValues(campaign_history);
history_sheet.getRange(1,history_sheet.getLastColumn(), 1, 1).setValue(today);

//Campaign QS Ends

//Adgroup QS Starts

for(i = 1; i < adgroup_sheet_values.length; i++){
// make sure there is actually some data here
if(adgroup_sheet_values[i][0] == "") continue;
adgroup_result_range[i] = [today, 0];
var campaign_name = adgroup_sheet_values[i][0];
var adgroup_name = adgroup_sheet_values[i][1];
var latest_check = adgroup_sheet_values[i][2];
var old_quality_score = adgroup_sheet_values[i][3];
var totalImpressionsAnalyzed = 0;
var totalQualityScoreAnalyzed = 0;
var keywordIterator = AdWordsApp.keywords()
.withCondition("CampaignName = '" + campaign_name + "'")
.withCondition("AdGroupName = '" + adgroup_name + "'")
.withCondition("CampaignStatus = ENABLED")
.withCondition("AdGroupStatus = ENABLED")
.orderBy("Impressions")
.forDateRange("LAST_30_DAYS")
.withLimit(50000)
.get();

while(keywordIterator.hasNext()){
var keyword = keywordIterator.next();
var current_quality_score = keyword.getQualityScore();
var keywordStats = keyword.getStatsFor("LAST_30_DAYS");
var impressions = keywordStats.getImpressions();
var qualityScoreContribution = current_quality_score * impressions;
totalQualityScoreAnalyzed = totalQualityScoreAnalyzed + qualityScoreContribution;
totalImpressionsAnalyzed = totalImpressionsAnalyzed + impressions;
}

var AdgroupQualityScore = totalQualityScoreAnalyzed / totalImpressionsAnalyzed;
var FinalAdgroupQualityScore=AdgroupQualityScore.toFixed(2);

// save quality score for results
adgroup_result_range[i][1] = FinalAdgroupQualityScore;
// Note the change for the history
if(old_quality_score > 0) var change = (FinalAdgroupQualityScore - old_quality_score).toFixed(1);
else var change = "NEW";
var column = [FinalAdgroupQualityScore];
adgroup_history.push(column);
// if we have a previously tracked quality score and it's different from the current one, we make a note to log it and send it via email later
if(old_quality_score > 0 && CampaignQualityScore != old_quality_score){
adgroup_alert_text.push(FinalAdgroupQualityScore + "\t" + old_quality_score + "\t" + change + "\t" + campaign_name + "\t" + adgroup_name);
}
}

// write results to spreadsheet
adgroup_result_range.splice(0,1);
adgroup_sheet.getRange(2, 3, adgroup_result_range.length, 2).setValues(adgroup_result_range);
// write history to spreadsheet
var history_sheet = spreadsheet.getSheetByName('Adgroups QS history');
history_sheet.getRange(2,history_sheet.getLastColumn()+1, adgroup_history.length, 1).setValues(adgroup_history);
history_sheet.getRange(1,history_sheet.getLastColumn(), 1, 1).setValue(today);
//Adgroup QS Ends

// Send Quality Score Changes through Email

var message = "The following Account Quality Score changes were discovered:\nNew\tOld\tChange\tAccount\n";
for(i = 0; i < account_alert_text.length; i++) message += account_alert_text[i] + "\n";
message += "\n" + "The following Campaign Quality Score changes were discovered:\nNew\tOld\tChange\tCampaign\n";
for(i = 0; i < campaign_alert_text.length; i++) message += campaign_alert_text[i] + "\n";
message += "\n" + "The following Adgroup Quality Score changes were discovered:\nNew\tOld\tChange\tCampaign Name\Adgroup Name\n";
for(i = 0; i < adgroup_alert_text.length; i++) message += adgroup_alert_text[i] + "\n";
// Include a link to the spreadsheet
message += "\n" + "Settings and complete history are available at " + spreadsheet_url;
MailApp.sendEmail(email_address, "AdWords quality score changes detected", message);
}

[/code]

4. Change email address and spreadsheet URL

In the first few lines of the script you will see a spreadsheet URL and a dummy email address. Replace the spreadsheet URL with the URL of the spreadsheet that you created (after copying ours). Then also put in your email address (you can use multiple addresses if you want to send reports to multiple people – just separate with a comma).

The script is divided in 4 parts – Account QS calculation, Campaign QS calculation, Adgroup QS calculation and Email sending. You can see in the script where each part starts and finishes.

The logic of calculating account, campaign and ad group QS is based on http://searchengineland.com/how-account-quality-score-can-guide-adwords-optimization-148595. As you know the original script for tracking keyword QS was written on PPC Epiphany. http://www.ppc-epiphany.com/2012/08/14/an-adwords-script-to-track-quality-scores/

5. Authorise the script

Before the script will run you will need to hit “Authorize now” button in the Authorize bar.

6. Run the script

Click run and the script will run. If it times out then check how many campaigns and ad groups you pasted into the spreadsheet to track and consider reducing a little.

Check your spreadsheet and you should see the data there and it should then be possible to set a schedule for the script to run every day or week so that you can monitor changes based on your account management as well as to prioritise your time effectively.

Having a high quality score can save you loads of cash – read this post to find out more on quality score.

We hope that you get a lot of benefit from this script. Much appreciation has to go to the guys mentioned above who really helped out us and the rest of the Adwords community by pioneering some early scripts to track Adwords quality score.

{ 80 comments… read them below or add one }

Katie April 11, 2013 at 2:02 pm

Thanks for this wonderful post! I can’t wait to use it, however I’m currently getting an “illegal character” error. Any thoughts on why? I’ve googled the error and can’t find any information. Thanks!

Reply

Joel April 11, 2013 at 2:23 pm

Hi Katie

This is strange. I tested this on 3 accounts before publishing the post with no issues. I am now getting the same issue as you.

I am going to have to get someone in the team to look at this and get back with the detail. As soon as I have it then i will update this post and email you.

Reply

Stephen April 11, 2013 at 3:13 pm

Same issue: “illegal character”. A quick fix would be appreciated. Thanks.

Reply

Joel April 12, 2013 at 1:27 am

Hi Stephen

We are working on it. We have got as far as knowing that the script itself is OK and that the problem is triggered by some characters/spaces in the names of accounts and/or campaigns/ad groups in the spreadsheet.

Could you clarify if your account name that you put in the first tab of the spreadsheet has spaces in it?

Katie – would be appreciated if you could let me know too as it will help with trouble-shooting.

Thanks

Joel

Reply

Joel April 12, 2013 at 4:58 am

Right – we found it.
As we thought it was the account name in the spreadsheet that was the culprit.

If you have an account name that contains spaces e.g.

My Account Name

rather than

MyAccountName

then you will need to simply add single quotes around the account name in the spreadsheet.

e.g.

‘My Account Name’

Then you should have it working.

Also – I have just updated the script so that it records the history in columns rather than rows to make charting easier. Please use the new spreadsheet and script form above and you should be fine. Any continuing issues then please just let me know.

Reply

Vicky April 12, 2013 at 11:14 am

I’m afraid I’m still getting the illegal character message, even with the single quotes around the account name. Any ideas?

Reply

Joel April 12, 2013 at 1:02 pm

Hi Vicky

Bugger – is my first thought.

Second thought:

Do you use Skype? I think that it is worth going through everything step by step to work out what is going wrong as i may have miscommunicated something in the post.

Everything working fine for me but if I can see what you are doing then I may be able to diagnose.

Let me know if you have the time?

Cheers

Joel

Reply

Birgitta April 15, 2013 at 2:39 pm

Seems like an incredibly useful script, thanks for posting this! Though just like others I keep getting the “illegal character” error.

Reply

Joel April 15, 2013 at 3:14 pm

I am going to end up crying in the corner soon ;-)

Sorry about this Birgitta – I will help you get it working if I can!

The issue is that we cannot replicate any issues anymore and have tested on numerous accounts. I think that I may have explained something poorly relating to the spreadsheet URL or the spreadsheet set up as that seems to be where both us and others have mainly had issues.

Did you ensure to copy and paste you own account, campaign and ad group names into the spreadsheet on both tabs for each level? i.e. both the “Account” and “Account QS History” with account name and likewise for campaigns and also ad groups?

Are you sure that you copied the URL back into the script correctly and did not accidentally copy over any quotation marks around the URL or anything like that?

I am happy to spend some time on Skype taking through you or any of the other guys systematically to see where the issue lies. Just let me know?

Reply

Patrick April 16, 2013 at 9:49 am

Yep, I get the same “illegal character” error message.

Let me know if anyone found what the problem is, I could really use this script well :-)

Thanks anyway for your post!

Reply

Joel April 16, 2013 at 9:54 am

Hi Patrick
Would it be possible to do a quick Skype call where you share your screen with me?
I can help diagnose any issues that way.
Thanks
Joel

Reply

Patrick April 16, 2013 at 3:24 pm

Thanks to Andree, it works perfect now!

Thanks again for your post Joel.

Reply

Andree April 16, 2013 at 12:27 pm

Hi everyone…

To fix the “illegal character” just remove the double quotes arount the spreadsheed-url and the E-Mail in the first lines with valid ones. The ones you copy aren’t the right ones…

now i have another error:
Parsing error. Please check your selector. (line 88)

Reply

Andree April 16, 2013 at 12:32 pm

My fault: removing the single-quotes around the campain-name did fix it…

works pretty good! Many thanks for this neat script, I was looking for something like this for years!

have a nice day,
Andree

Reply

Joel April 16, 2013 at 12:35 pm

Andree
Very nice spot – thank you.

I knew it had to be something simple. I have now updated the post with valid quotes so if people use the script above from now on they should not have any issues.

Apologies all and thanks for your patience.

Reply

Dominika April 17, 2013 at 2:08 pm

Hey,

I also have a problem with opening that script. It says to try again later. I have some questions, that maybe will help to guess what is wrong:
1. Which URL addresse should I put in the script? I mean, every sheet in a Spreadsheet has different URL, should I take the first one?
2. Do we need to change anything else in the script despite URL and e-mail addresse?

Thank you very much for the answer.
Greetings,
Dominika

Reply

Joel April 17, 2013 at 2:15 pm

Hi Dominika

Thanks for getting in contact.

1) Any is fine as the tabs only affect the URL after the hash (#) which should not make a difference. But just use the one from the 1st tab to be sure
2) No, nothing else to change.

So assuming that you are fine on those; here are a couple of other things to check:
1) You did not add too many campaigns and/or ad groups causing the script to time out – use a max of 20 of each just to make sure that you can get it working and then experiment adding more afterwards.
2) That you did not accidentally copy in the [code] [/code] tags at the beginning or end of the script?

If those are also OK then it may be a temporary issue with Google as I guess try again later may suggest that they are having some temporary issues?

If all this fails then please let me know.

Reply

Dominika April 24, 2013 at 3:00 pm

Hey,

thank you for the answer.
I have done everything from scratch and now the only mistake is Line 88 – Parsing Error. What can it mean?

I observed that Google automatically adds another single-quote at the beginning of all the names in the Spreadsheet, so it looks like ”Campaign Name’ instead of ‘Campaign Name’. Can it be the problem?

Have a nice day,
Dominika

Reply

Joel April 25, 2013 at 1:16 am

Hi Dominika

Please see Andee’s comment above. he got it working again by removing the single quotes around the account and campaign names in the spreadsheet.

Thanks
Joel

Reply

bristolking April 17, 2013 at 6:35 pm

Hello – when I am running the script I get the following error

Not completed: Script runtime error

Any idea why I am getting this error.

Reply

Joel April 17, 2013 at 11:44 pm

Hi Bristolking
It suggests that you have entered too many campaigns and ad groups. Try first with just 10 or 20 campaigns and also max of 20 ad groups and it should work. Also; do not bother previewing first as the preview runs for less time than the actual script.

Reply

bristolking April 18, 2013 at 3:47 pm

Thanks Joel for responding.

I have only one campaign and 3 ad groups – u can check my google spreadsheet as below:-
https://docs.google.com/spreadsheet/ccc?key=0Aj9cKs6fDlMudHlPaENZWkZObU9QZUJ0Y1dxODNaS1E&usp=sharing

Any help is very much appreciated

Reply

Joel April 19, 2013 at 1:21 am

Hi Bristolking

Your spreadsheet is set up correctly therefore I see a reason for the runtime error.

I am going to copy the script from a working adwords account and insert your email address and this spreadsheet URL and email to you.

Please try that and let me know how you get on.

Reply

Adrian May 6, 2013 at 11:31 am

My script runs but for the Campaigns and AdGroups tabs, the Quality Score Value shows as “NaN”?

What does NaN mean?

Thanks

Adrian

Reply

Joel May 6, 2013 at 12:20 pm

Hi Adrian

It stands for “not a number”

This means that for some reason there is no data for the campaigns and ad groups that you selected to pull in the data for.

It could be because they have no impressions over that day – i.e. are they paused for the weekend/bank holiday?

Thanks

Joel

Reply

Adrian May 7, 2013 at 5:02 pm

Thanks for the reply Joel, that explains it!

Reply

Joel May 8, 2013 at 1:14 am

No worries – I hope that you find the script helpful.

Reply

Adrian May 10, 2013 at 10:04 pm

I’ve continued to receive NaN this week, despite my campaign receiving clicks. As a test, I renamed one of AdGroups to have no spaces e.g. “My AdGroup 1″ to “MyAdGroup1″ and changing the corresponding entry in the Google Docs spreadsheet but this didn’t fix it.

I’ll set up the script again from scratch but any ideas what might be causing this?

Adrian

Adrian May 10, 2013 at 10:28 pm

I solved it! In fact it was my mistake – I had the wrong name in the Account name field. I changed this to to be the campaign name and all is fine!

Thanks for ths wonderful script.

Adrian

JR May 9, 2013 at 10:10 pm

Thanks for the info! This will be so helpful! When I try running the scrips in adwords it gives me a message “The script must define exactly one main function.” What does that mean? Also, where you say to input your account name, what exactly is the account name? The login name, the cust ID number?

Reply

JR May 9, 2013 at 10:26 pm

Well, I fixed the main function error but now it’s giving me a syntax error. And it won’t identify where the error is. Anyone have recommendations on how to figure out where the error is. Thanks! I’m not as good with scripts lol.

Reply

Joel May 10, 2013 at 12:12 am

Hi Jodi
If you could email me a text file with the exact script that you are using and also give me a link to the spreadsheet in the same email then i will look into it for you.
Syntax error sounds like you have accidentally deleted a bit of the script?!?
You can email me on joel@deepfootprints.co.uk

Reply

Kathrin May 30, 2013 at 10:20 am

Dear Joel

Wow, this is great, thank you very much! I just tried to create the script as described above, but I get the error message: TypeError: Cannot call method “getRange” of null. (line 72)

Do you know what the reason for this error could be?

Thanks again, also for the great support!

Kathrin

Reply

Joel May 30, 2013 at 10:38 am

Hi Kathrin
That part of the code is about writing the results to the spreadsheet. Therefore it is most likely that you have made an error in the set up of the spreadsheet. Check that you have put in the account name correctly in both of the account tabs as well as the campaign names and ad group names in both of the tabs that relate to those levels.

If that doesn’t highlight anything you can email me a link to the spreadsheet if you like and I will take a look – joel@deepfootprints.co.uk

Reply

Robert Bridgens June 4, 2013 at 12:37 pm

Great script with awesome potential – thanks for sharing.
Im getting a “Not completed: Script runtime error” which is a “Parsing error. Please check your selector. (line 146)”

This refers to
var keywordIterator = AdWordsApp.keywords()

Any idea what i might be doing wrong?

Thanks for any help,

Rob

Reply

Robert Bridgens June 4, 2013 at 12:52 pm

My bad, I was being greedy and using too many adgroups.

I have another question though. WEhat date range does this script look at? If its looking at the same day as its run, is it better to run in the morning or at the end of the day?

Thanks again for your help,

Rob

Reply

Joel June 4, 2013 at 1:52 pm

Hi Rob

No worries – it takes a while to get the hang of the max number of campaigns and ad groups – seems that 100 in total or less is a vague guideline.

It just looks at the QS at the time that you run the report so time of day makes no difference. Google could update your QS at anytime.

Thanks

Joel

Reply

Fabian June 11, 2013 at 1:24 pm

Hey Joel,

Great script! Was looking for this a few weekS back but didn’t find a complete one. This one is :)
Everythings works great but some campaigns/adgroups just don’t show up and give the NaN as return.
Could the length of the campaign name have anything to do with this?

The campaigns with a name of max 20 characters return fine but the longer ones return NaN.

Is there something I can do about this?
Thnx!

Reply

Joel June 11, 2013 at 1:38 pm

Hi Fabian

I only get the NaN issue where there has not been at least 5 impressions over the past 24 hours for a campaign and have run some with more than 20 characters in campaign and ad group names.

I have had that and then simple misspellings of names which have caused this issue but not name length.

Let me know if this helps?

Reply

Axel June 13, 2013 at 4:09 am

Hi Joel,

I have the same “NaN” Results for the account Name. I’ve tried to remove the space from the account name but it didn’t change anything.
Yet, I have some specials characters in my account name like[ – and _
Same Characters in my campagne name. Yet I’ve try to remove them from my account name, NaN results again…
Moreover, I removed all campaigns which didn’t have enough impressions, NaN again…
Any Idea?

Thanks a lot

Reply

Joel June 13, 2013 at 4:29 am

Hi Axel

I am a bit out of my depth when it comes to javascript and Google Doc errors. I would not adjust your account or campaign names at all as that will definitely break the script.

One possibility is that you are copying in the names with an accidental space before/after the name?

If it is not this and it is breaking due to the characters in your account name and campaign name then I am afraid I am unsure how to troubleshoot and fix this.

Does anyone have any ideas?

Thanks

Joel

Reply

Axel June 13, 2013 at 4:44 am

Thanks Joel,

I’ve tried with a different account same result…
However, I’ve noticed something:

As my Account Name has spaces, I put it under ‘ ‘. Yet on the spreadsheet, the opening ‘ isn’t displayed (but it is present in the cell value though).

But in the email I received, the Account name is : Account Name’ with no opening ‘…

I don’t know if it is the same with you.

Thanks

Reply

Joel June 13, 2013 at 5:04 am

Yes that is the same for me – I would just remove the ‘ as in most cases it is not needed.

Thanks

Joel

Reply

Giovanni June 21, 2013 at 3:21 pm

Hi
I tried your script that I find absolutely great…
The first time, I had the Account QS back, but in the other tabs there were the NaN errors…

Then I realized that I had put all the names within ” and not ‘, (also in the account tab) .
So I changed every ” with a ‘, and now adwords when I run the script says something like “Script ended with runtime error”

And the log says “Parsing error. Please check your selector. (line 88)” , that is the var keywordIterator = AdWordsApp.keywords() line….

Getting back to the ” in the sheet, I don ‘t have the runtime error, but I have the same NaN results.

Thank you for any help

Giovanni

Reply

Joel June 22, 2013 at 8:09 am

Hi Giovanni
This sounds like you have too many campaigns and/or ad groups in the spreadsheet. That is the first thing to check – try with just one or two of each.
Secondly – remove the ‘ around the names so that there is nothing at all.
Let me know if neither of these work.
Joel

Reply

Giovanni June 24, 2013 at 9:40 am

Hi,
I left the ‘ just on the name of the account, and now it works perfectly..
Thank you!!

Reply

Joel June 24, 2013 at 10:21 am

Great – glad you got it sorted. Thanks Giovanni

Reply

Chip Oglesby June 24, 2013 at 9:03 pm

Joel,

Thanks for this script! It has been working like a charm. One question: If I’m running this daily, do I need to change the date range from ‘last 30 days’ to ‘yesterday’? Thanks for any advice you can provide.

Reply

Joel June 25, 2013 at 1:06 am

Hi Chip
It will run on a daily basis and give you the quality score only at the time that it runs. The 30 days refers to how many days of data is checked to ensure that the keywords each have 5 impressions or more in the date range otherwise it is not really worth monitoring them. You do not need to change anything.

Reply

Chip Oglesby June 25, 2013 at 9:06 pm

Thank you so much Joel, I appreciate it.

Chip

Reply

Roberta June 24, 2013 at 10:08 pm

I get the following error (quotes are mine)
Parsing error. Please check your selector. (line 88)

(The Account and Account History QS writes to the Googgledocs spreadsheet, though; Campaign and Adgroups sections remain blank

Reply

Roberta June 24, 2013 at 10:25 pm

Ok, removed the quotes around Campaign Name as a prior poster mentioned… now I get:

Parsing error. Please check your selector. (line 146)

I only have 27 ad groups to track, by the way.

Reply

Joel June 25, 2013 at 1:08 am

Hi Roberta
Do you have any quotes around the ad group names?
This sounds like most likely issue. If that is not it just try running with 5 campaigns and 5 ad groups as that will confirm whether or not it is a timeout issue.

Reply

Roberta June 25, 2013 at 9:12 pm

I removed all but 5 ad groups & removed quotes from ad groups. Still get:

Parsing error. Please check your selector. (line 146)

Reply

Joel July 4, 2013 at 12:18 am

Hi Roberta
Really sorry for the delayed reply – I have been incredibly busy for the past few weeks. Could you possibly email me a link to your spreadsheet and also attach the exact script that you are using in a text file so that i can diagnose what the issue is?
joel @ deepfootprints.co.uk

paulo rossini July 4, 2013 at 11:40 pm

Hi,
I have just tested the script (campaigns only) and it works perfectly – Thank you for sharing
Do you know how many adgroups this script can run ?

Reply

Joel July 4, 2013 at 11:56 pm

Hi Paulo
It’s really a matter of trying but as a rough guideline keep combined campaigns and ad groups under 80 and it should run.
Glad you like the script.
Thanks

Reply

Jenny July 23, 2013 at 2:30 am

I tried running this scrip but got an error:
TypeError: Cannot call method “getDataRange” of null. (line 12)

my line 12 is:
var account_sheet_values = account_sheet.getDataRange().getValues();

I followed the instructions. Any idea why I’m getting the error?

Reply

Joel July 23, 2013 at 3:24 am

Hi Jenny
That suggests there is something wrong with the spreadsheet set up on the Account sheets – could you send me a link to it and I will check?

First thing to check is that you have the account name exactly as it is in the account – same capitalisation is important.

Thanks

Reply

Jenny July 23, 2013 at 5:46 pm

I figured it out. Thanks!

Reply

Joel July 23, 2013 at 11:54 pm

Great – what was it?

Reply

Jenny July 25, 2013 at 12:24 am

stupid me, I didn’t put the account name in the spreadsheet.

Reply

Jenny July 24, 2013 at 5:36 pm

How can I make it so that the script does not write over the previous day’s QS? I test ran the script yesterday and it was successful. I scheduled it to run every day at 8am. When I came in today the QS from yesterday was overwritten with today’s date and QS.

Thanks

Reply

Jenny July 25, 2013 at 12:23 am

Nevermind, I got it

Reply

Jo July 26, 2013 at 3:15 pm

Hi,

I am getting the same error of
‘Parsing error. Please check your selector. (line 146)’
Line 146 =
var keywordIterator = AdWordsApp.keywords()

total of 8 campaigns, 30 ad groups

getting results into account and account history, campaigns and campaign history, but not the adgroups OR adgroups history tab – so it’s working until it gets to the adgroups……

Reply

Jo July 26, 2013 at 3:55 pm

Tried takling out all but 5 ad groups, it works then.
Tried putting the removed ones back, fails again, keeping the results that it previously generated for the 5 that were left in…..and not adding another column to the adgroups history tab

Reply

Chip Oglesby August 2, 2013 at 3:15 pm

Hi Joel,

I’ve been running this script daily and I’m starting to rack up a lot of columns. Is there a way to change the script so that it will record data in rows instead?

Reply

Mike September 26, 2013 at 8:42 pm

Hi thanks for this script, Is there a way to remove the account level QS and just pull the campaign and Adgroup level. What would i need to modify for this thanks?

Reply

Joel September 27, 2013 at 2:18 am

Hi Mike

Yes it is possible; we will take a look at it for you but are pretty busy right now. I will comment back here once we have worked out what you need to do. You could always just ignore those sheets in the spreadsheet….Can I ask what you are tring to achieve through removing the account level detail as it will help us come up with the right solution for you?

Reply

Joel September 27, 2013 at 9:42 am

Hi Mike

Here you go:

The original script has a section //Account QS Starts and then after few lines it ends with //Account QS Ends. You just need to delete all these lines. Instead of deleting you can also put // (which means comment and all these lines will be ignored upon code execution) ahead of all these lines. I would suggest to put // instead of deleting these lines because in case if he wants account level QS part back then it’s easier.

A small change needs to be made in the last section //Send Quality Score Changes through Email. First 2 lines in this section is about account level QS so they need to be removed and the third line needs to start with var message instead of message. See below the last section coding.

Original Script

// Send Quality Score Changes through Email

var message = “The following Account Quality Score changes were discovered:\nNew\tOld\tChange\tAccount\n”;
for(i = 0; i < account_alert_text.length; i++) message += account_alert_text[i] + “\n”;
message += “\n” + “The following Campaign Quality Score changes were discovered:\nNew\tOld\tChange\tCampaign\n”;
for(i = 0; i < campaign_alert_text.length; i++) message += campaign_alert_text[i] + “\n”;
message += “\n” + “The following Adgroup Quality Score changes were discovered:\nNew\tOld\tChange\tCampaign Name\Adgroup Name\n”;
for(i = 0; i < adgroup_alert_text.length; i++) message += adgroup_alert_text[i] + “\n”;
// Include a link to the spreadsheet
message += “\n” + “Settings and complete history are available at ” + spreadsheet_url;
MailApp.sendEmail(email_address, “AdWords quality score changes detected”, message);

Modifed Script

// Send Quality Score Changes through Email

//var message = “The following Account Quality Score changes were discovered:\nNew\tOld\tChange\tAccount\n”;
//for(i = 0; i < account_alert_text.length; i++) message += account_alert_text[i] + “\n”;
var message = “The following Campaign Quality Score changes were discovered:\nNew\tOld\tChange\tCampaign\n”;
for(i = 0; i < campaign_alert_text.length; i++) message += campaign_alert_text[i] + “\n”;
message += “\n” + “The following Adgroup Quality Score changes were discovered:\nNew\tOld\tChange\tCampaign Name\Adgroup Name\n”;
for(i = 0; i < adgroup_alert_text.length; i++) message += adgroup_alert_text[i] + “\n”;
// Include a link to the spreadsheet
message += “\n” + “Settings and complete history are available at ” + spreadsheet_url;
MailApp.sendEmail(email_address, “AdWords quality score changes detected”, message);

No need to change anything in the doc file. You can just ignore first 2 tabs for account QS there. I’ve tested this modified script and it works fine.

Reply

Mike September 27, 2013 at 2:13 pm

Thanks a bunch will give this a try. Its just to avoid timing out because our accounts are so large. If I can just get adgroup level QS and maybe ill incorporate campaign level this should be plenty to optimize off of. Improving adgroup level QS will improve campaign and account anyways. Thanks again for your help.

Reply

Mike September 27, 2013 at 2:35 pm

I keep getting an error when i switch out the campaign and adgroup names.

Missing ) after argument list.

Here is the only section i am trying to test without sending output anywhere.

function main() {

var totalImpressionsAnalyzed = 0;
var totalQualityScoreAnalyzed = 0;
var keywordIterator = AdWordsApp.keywords()
.withCondition(“CampaignName = ‘” + game-career + “‘”)
.withCondition(“AdGroupName = ‘” + game-game design + “‘”)
.withCondition(“CampaignStatus = ENABLED”)
.withCondition(“AdGroupStatus = ENABLED”)
.orderBy(“Impressions”)
.forDateRange(“LAST_30_DAYS”)
.withLimit(50000)
.get();

while(keywordIterator.hasNext()){
var keyword = keywordIterator.next();
var current_quality_score = keyword.getQualityScore();
var keywordStats = keyword.getStatsFor(“LAST_30_DAYS”);
var impressions = keywordStats.getImpressions();
var qualityScoreContribution = current_quality_score * impressions;
totalQualityScoreAnalyzed = totalQualityScoreAnalyzed + qualityScoreContribution;
totalImpressionsAnalyzed = totalImpressionsAnalyzed + impressions;
}
}

Reply

Anuj Ranka October 20, 2013 at 10:21 am

Hey I am getting this error ” missing ; before statement” what can be possible reasons. I double checked on having ; at end of my google spreadsheet address and my e-mail id.

Thanks

Reply

Tyler Jensen November 14, 2013 at 10:08 pm

I ran the script and I got the Account Quality score. However everything else returns Nan. Any thoughts?

Reply

Joel November 15, 2013 at 2:34 am

Hi Tyler
That usually means that there was no data for that day due to the campaigns, ad groups being paused or else have less than 5 impressions.

Reply

clive spenser November 18, 2013 at 7:32 pm

Very nice and useful script – the hardest bit was finding out what the account name is!

Reply

Joel November 18, 2013 at 11:12 pm

Thanks Clive – glad that you did not have any stumbling blocks.

Reply

Sarah January 22, 2014 at 12:29 pm

absolutely cracking script, and thank you for sharing, so helpful!

Reply

Joel January 22, 2014 at 12:38 pm

I am glad you found it useful Sarah.

Reply

Joel October 4, 2013 at 12:17 am

Thanks Ian
Alternatively you could just hit the download button on an ad group report in Adwords to get it into a spreadsheet.
Either way most account managers are going to need to trim their list of ad groups to add to the script output spreadsheet as it can only handle so many before it times out.

Reply

Leave a Comment

Previous post:

Next post:

Privacy policy and cookie use