AdWords is now Google Ads. Our new name reflects the full range of advertising options we offer across Search, Display, YouTube, and more. Learn more

Ads
2.5K members online now
2.5K members online now
Dive into advanced features like Remarketing, Flexible Bid Strategies, AdWords Editor, and AdWords Scripts
Guide Me
star_border
Reply

Breakdown quality score issues with a simple script.

Explorer ✭ ☆ ☆
# 1
Explorer ✭ ☆ ☆

Hi all.

I thought of sharing a nice script that some of you may find useful, regarding the keywords quality score breakdown.
As we know, the QS is combined of 3 factors: Expected CTR, Ad Relevance, Landing Page Experience.

When we want to improve a keyword's QS we need first to hover over the little bubble next to the keyword, to see which of the 3 factors requires our attention:

bubble-hover.jpg

By itself, its not too difficult task.
But what if we want to make a massive work, fixing hundreds of keywords?
hovering over all those bubbles may sound not too fascinating, especially when we hover away the info is gone and we either have to remember it or write it down so we'll know which keyword has below average expected CTR, or an average ad relevance.

Here is where this script (on the bottom) comes in to make work easier.

The way the script works is:
It will populate the spreadsheet with all enabled keywords on the SEARCH network that got more than 1 impression in the last 7 days. Each keyword is in a line.
The script will find the 3 factors of the keyword, populate the values so we can see them as a table, and the spreadsheet template has conditional formatting to paint below average in red for convenience. at the end of the line, the script will populate the campaign and ad group so we can find the keyword easily in our account.
Finally the script will sort the keywords by impressions (descending).

This is how it would look after the script runs:
QS-Breakdown.jpg

With the table, it will be easy to see which keywords require our most immediate attention.
You may of course change the script if you want other time period, minimum impressions, etc.

First, start by creating a copy of a template spreadsheet. It also contains all conditional formatting for colors.
Second copy the address of the newly created spreadsheet on your drive, and paste it in the script where it says "PLACE YOUR SPREADSHEET URL HERE".
Then let the script run, and that's it.

This is the script:

function main() {
  
  // GET THE SPREADSHEET
  var ssURL = "PLACE YOUR SPREADSHEET URL HERE";
  var spreadsheet = SpreadsheetApp.openByUrl(ssURL);

  // CLEAN THE SPREADSHEET FROM PREVIOUS DATA
  var lastRow = spreadsheet.getLastRow();
  var lastColumn = spreadsheet.getLastColumn();
  var lastColumnLetter = String.fromCharCode(64 + lastColumn);
  var lastCell;
  if (lastRow == 1) {
    lastCell = lastColumnLetter + 2;
  }
  else {
    lastCell = lastColumnLetter + lastRow;
  }

  spreadsheet.getRange('A2:'+ lastCell).clearContent();

  var currentSheetRow = 2;


  // FIND ALL KEYWORDS FROM SEARCH NETWORK WITH MORE THAN 1 IMPRESSION IN LAST 7 DAYS
  var keywordIterator = AdWordsApp.report("Select Criteria, Impressions, SearchPredictedCtr, CreativeQualityScore, PostClickQualityScore, QualityScore, CampaignName, AdGroupName " +
                                          "from KEYWORDS_PERFORMANCE_REPORT WHERE Impressions > 1 and CampaignStatus = ENABLED and AdGroupStatus = ENABLED and Status = ENABLED and AdNetworkType1 = SEARCH " +
                                          "DURING LAST_7_DAYS");

  var keywordRowIterator = keywordIterator.rows();

  // POPULATE THE SPREADSHEET
  while (keywordRowIterator.hasNext()) {
    var keywordRow = keywordRowIterator.next();
    spreadsheet.getRange("A"+currentSheetRow).setValue(keywordRow["Criteria"]);
    spreadsheet.getRange("B"+currentSheetRow).setValue(keywordRow["Impressions"]);
    spreadsheet.getRange("C"+currentSheetRow).setValue(keywordRow["SearchPredictedCtr"]);
    spreadsheet.getRange("D"+currentSheetRow).setValue(keywordRow["CreativeQualityScore"]);
    spreadsheet.getRange("E"+currentSheetRow).setValue(keywordRow["PostClickQualityScore"]);
    spreadsheet.getRange("F"+currentSheetRow).setValue(keywordRow["QualityScore"]);
    spreadsheet.getRange("G"+currentSheetRow).setValue(keywordRow["CampaignName"]);
    spreadsheet.getRange("H"+currentSheetRow).setValue(keywordRow["AdGroupName"]);
    currentSheetRow++;
  }
  
  // SORT
  spreadsheet.sort(2, false);
  
}



Last note:
Since LIMIT is not supported for the Adwords Reports, If you have too many active keywords you may change the minimum impressions on the "Select" statement. Where it says "WHERE Impressions > 1", you can change it to anything else: "WHERE Impressions > X".

I've tested it and used it several times. If anyone encounters any issues, feel free to reply and I'll check it.

 
Good Luck!
Arik.

 

2 Expert replyverified_user

Re: Breakdown quality score issues with a simple script.

Top Contributor
# 2
Top Contributor
Hi Arik,

Thanks for sharing. Am I correct in assuming that this works like a snapshot of the account meaning there is no updating of the existing sheet and no recording of changes week to week for instance.

Is that right?

-Tommy

Tommy Sands, AdWords Top Contributor | Community Profile | Twitter | Philly Marketing Labs
Did you find any helpful responses or answers to your query? If yes, please mark it as the ‘Best Answer.’

Re: Breakdown quality score issues with a simple script.

[ Edited ]
Explorer ✭ ☆ ☆
# 3
Explorer ✭ ☆ ☆

That's right Tommy.

This script clears all the data first and then re-populates the spreadsheet.
The script can be modified, for example to create new sheet every week inside the spreadsheet.
However, when talking about "updating", it is a problem to use this spreadsheet to compare and update previous data, when relating to keyword's stats. The reason is that a Keyword ID is not unique in an Adwords account (unlike Adgroup ID or Campaign ID), so if there are 2 keywords with the same text and match type but different adgroups, the comparison to previous data is not injective (one-to-one).

There is a bypass that can be done by combining the Adgroup ID with Keyword ID, and then know for sure for every keyword it's previous stats, but this is actually using the spreadsheet as a database, and working with a database with client side javascript is not too wise (time-consuming).

of course, if someone is working with Adwords API, they can keep the figures in a database like mySql, and have these updates with history comparison quite fast and easy.

Re: Breakdown quality score issues with a simple script.

Follower ✭ ☆ ☆
# 4
Follower ✭ ☆ ☆
Thanks for sharing this useful script Arik!

Breakdown quality score issues with a simple script.

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

Hi Arik, 

thank you for the great script.

Just a little suggestion, that might help a lot of people:

How about adding a row which shows the final URL of the keyword (we always set up final URLs on keyword level)? Then you would be able to quickly see what landing page is performing poor or great.

 

Let me know, what you think.

Florian

Breakdown quality score issues with a simple script.

Rising Star
# 6
Rising Star

That was a great script you built, Arik!

 

As an FYI for anybody else reading this thread, Google has recently added new Quality Score statistics to the AdWords interface. You can see the component statuses for Expected CTR, Ad Relevance, and Landing Page Experience directly within the AdWords interface.

 

See more info here: https://support.google.com/adwords/answer/2454010

 

Best,

 

John