Keyword Performance Report

[Katrin Munich]

Hi guys! 

I have a general question; Can I pull performance Data for individual Keywords with a Script for MCC Level? 

For example, export the Impression Share of a certain Keyword for every Account of the MCC using the accountSelector = AdsManagerApp.accounts and then select from the Data from the Keyword Performance Report? 

Best Regards, 

Katrin

[Google Ads Scripts Forum Advisor]

Hi Katrin,

Assuming you want to pull metrics for a certain keyword phrase from all accounts, you could use the code below. Please see my comments in bold:
 

function main() {
  var keywordPhrase = 'my phrase'; //Keyword text that you want to analyze across accounts
  var accounts = AdsManagerApp.accounts().get();
  
  //Loop through accounts
  while(accounts.hasNext()) {
    var account = accounts.next();
    AdsManagerApp.select(account);
    var keyword = AdsApp.keywords().withCondition("Text = '" + keywordPhrase + "'").get();

    //If the keyword phrase exists in the account, pull stats over a given date range
    if(keyword.hasNext()) {
      var keyword = keyword.next();
      var keywordStats = keyword.getStatsFor("YOUR_DATE_RANGE");
      
      /** Further processing of keyword stats **/

    }
  }
}

You can also find more keyword selector filters here.

Hope this helps.

Regards,
Matt
Google Ads Scripts Team
 

ref:_00D1U1174p._5004Q22b6s9:ref

[Katrin Munich]

Hi Matt, 

thank you so much :) I have a few followup questions: If I wanted to get stats like Impression Share, would that be possible too? What stats would be exported in your script? And can I see which Account I got the stats from?

For the further processing of the Keywords, I tried to have the stats exported to a spreadsheet, but that didn't work: 

var SPREADSHEET_URL = 'My URL';

var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

var sheet = spreadsheet.getActiveSheet();

function main() {

  var keywordPhrase = 'cloud data warehouse'; 

  var accounts = AdsManagerApp.accounts().get();

  

  while(accounts.hasNext()) {

    var account = accounts.next();

    AdsManagerApp.select(account);

    var keyword = AdsApp.keywords().withCondition(keywordPhrase).get();

    if(keyword.hasNext()) {

      var keyword = keyword.next();

      var keywordStats = keyword.getStatsFor("LAST_MONTH");

report.exportToSheet(sheet);

 }

  }

}

Looking forward to hearing from you! 

Best regards, 

Katrin

[Google Ads Scripts Forum Advisor]

Hi Katrin,

I work with Matt and allow me to assist you.

You can update your condition similar to the one below :

var keyword = AdsApp.keywords().withCondition("Text = '" + keywordPhrase + "'").get();

You will need to ensure that the field (ex. Text) is included in the condition. In addition, you can generate the Keywords Performance Report in order to be able to retrieve the SerachImpressionShare metrics of your keywords.

When going to the reporting route, the field you will need to use inside the query should be the Criteria field, for filtering using the exact keyword string. I hope this helps.

Let me know if this helps.

Best regards,
Peter

[Katrin Munich]

Hi Peter, 

thank you so much for your reply and apologies for the late reply on my part. :) I updated the part you pointed out. Also I tried to include the command for the Keyword Performance Report. 

I'm however unsure if this has been done correctly, could you tell me if I'm on the right way?

var SPREADSHEET_URL = 'My URL';

var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

var sheet = spreadsheet.getActiveSheet();

function main() {

  var keywordPhrase = 'cloud data warehouse'; 

  var accounts = AdsManagerApp.accounts().get();

  

  while(accounts.hasNext()) {

    var account = accounts.next();

    AdsManagerApp.select(account);

    var keyword = AdsApp.keywords().withCondition("Text = '" + keywordPhrase + "'").get();

    if(keyword.hasNext()) {

      var keyword = keyword.next();

      var keywordStats = keyword.getStatsFor("LAST_MONTH");

var report = AdsApp.report(

    'SELECT Impressions, SearchTopImpressionShare, SearchImpressionShare ' +

    'FROM KEYWORD_PERFORMANCE_REPORT ' +

  );

report.exportToSheet(sheet);

 }

  }

}

Have a good weekend, 

Katrin

[Google Ads Scripts Forum Advisor]

Hi Katrin,

If you are going to use the keywords performance report, the keyword selector and if-block can be removed.

The exportToSheet method overwrites the spreadsheet; it does not append data. With the current setup, the spreadsheet would loop through all accounts overwriting the sheet each time. It would be best to use the appendRow method instead. 

Please see a revised version of the script which contains some syntax cleanup and the criteria field added to the query:
 

var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/19khRvoIA4jagSlXqjmZnCP9e3YRG4iZNwCDPrlErzpk/edit#gid=0';
var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var sheet = spreadsheet.getActiveSheet();

function main() {
  var keywordPhrase = 'cloud data warehouse'; 
  var accounts = AdsManagerApp.accounts().get();
  
  while(accounts.hasNext()) {
    var account = accounts.next();
    AdsManagerApp.select(account);

    var report = AdsApp.report(
      'SELECT Impressions, SearchTopImpressionShare, SearchImpressionShare ' +
      'FROM KEYWORDS_PERFORMANCE_REPORT ' +
      'WHERE Criteria = "' + keywordPhrase + '" ' + 
      'DURING TODAY '
    );
    
    var rows = report.rows();
    
    while(rows.hasNext()) {
      var row = rows.next();
      sheet.appendRow([row['Impressions'], row['SearchTopImpressionShare'], row['SearchImpressionShare']]);
    }
  }
}

Please note that this is not necessarily a complete version of the script. For example, you may want to add:

• Account ID/name column to identity which account the keyword is in
• A header to identify the columns 

Regards,
Matt

[Katrin Munich]

Hi Matt, 

thanks for the revised version. I've added a header and the Account name, but now I get a very weird output in my spreadsheet. The Account name fills out normally in the first column but in the second column there is content like this: [Ljava.lang.Object;@10de33d6

var SPREADSHEET_URL = 'My URL'; var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL); var sheet = spreadsheet.getActiveSheet(); function main() { var keywordPhrase = 'cloud data warehouse'; var accounts = AdsManagerApp.accounts().get(); while(accounts.hasNext()) { var account = accounts.next(); AdsManagerApp.select(account); var header = [ 'AccountDescriptiveName', 'KeywordMatchType', 'Impressions', 'SearchTopImpressionShare', 'SearchImpressionShare', ]; var report = AdsApp.report( 'SELECT AccountDescriptiveName, KeywordMatchType, Impressions, SearchTopImpressionShare, SearchImpressionShare ' + 'FROM KEYWORDS_PERFORMANCE_REPORT ' + 'WHERE Criteria = "' + keywordPhrase + '" ' + 'DURING LAST_MONTH ' ); var rows = report.rows(); while(rows.hasNext()) { var row = rows.next(); sheet.appendRow([row['AccountDescriptiveName'],[row['KeywordMatchType'],[row['Impressions'], row['SearchTopImpressionShare'], row['SearchImpressionShare']]]]); } } }

Also is it possible to add a column with the Keyword?

Kind regards,

Katrin

[Google Ads Scripts Forum Advisor]

Hi Katrin,

Looking at the array that you pass to 'appendRow', there are four square brackets at the end where there should only be two. It's possible the cryptic value set was the Spreadsheet service's way of handling the error. 

You can include the Criteria field (also referenced in the where clause) in your select statement.

Thanks,

[Katrin Munich]

Hi Matt, 

I tried to remove the two brackets as suggested, but got the error message Missing ] after element list.  for that line.

Could the mistake be somewhere else?

Thanks, 

Katrin

[Katrin Munich]

Hi Matt, 

Please disregard the last email, I found the mistake with the brackets, there was an extra one at the beginning of the array that should not have been there, causing the error message when I tried to remove it at the end. 

The Script exports the metrics now, but does not include the header. Do you see a mistake with the code I included for the header? Also, if I wanted to add a second Keyword to this report (or third), what would be the best way to include this?

Thanks, 

Katrin

[Google Ads Scripts Forum Advisor]

Hi Katrin,

I am happy to know that the previous issue has been fixed.

Moving forward, you may refer to the modified script below wherein necessary updates are applied. First, the array variable header will be passed to the sheet.appendRow() so that the script will add the column header to spreadsheet. Then, as for adding more keywords, I converted the keywordPhrase variable into an array and used it in a for loop statement. You may set 3 or more keywords in the keywordPhrase array.
 

var SPREADSHEET_URL = 'My URL';
var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var sheet = spreadsheet.getActiveSheet();

function main() {
  var keywordPhrase = ['keyword phrase 1', 'keyword phrase 2', 'keyword phrase 3']; 
  
  var header = [
    'AccountDescriptiveName',
    'KeywordMatchType',
    'Impressions',
    'SearchTopImpressionShare',
    'SearchImpressionShare',
  ]; 
    
  sheet.appendRow(header);
  
  var accounts = AdsManagerApp.accounts().get();
  
  while(accounts.hasNext()) {
    var account = accounts.next();
    AdsManagerApp.select(account);
    
    
    for(var x=0 ; x<keywordPhrase.length ; x++)
    
    var report = AdsApp.report(
      'SELECT AccountDescriptiveName, KeywordMatchType, Impressions, SearchTopImpressionShare, SearchImpressionShare ' +
      'FROM KEYWORDS_PERFORMANCE_REPORT ' +
      'WHERE Criteria = "' + keywordPhrase[x] + '" ' + 
      'DURING LAST_MONTH '
    );
    
    var rows = report.rows();
    
    while(rows.hasNext()) {
      var row = rows.next();
      sheet.appendRow([row['AccountDescriptiveName'],row['KeywordMatchType'],row['Impressions'], row['SearchTopImpressionShare'], row['SearchImpressionShare']]);
    }
  }
}

Let me know if you have further questions.

Regards,
Ejay

[Katrin Munich]

Hi Ejay, 

you're the best! One small issue though, the script only pulls the data for the last Keyword phrase of the array and disregards the first two. If there's a solution for this, I'd be very grateful for it.

Thanks a lot :)

Katrin

[Google Ads Scripts Forum Advisor]

Hi Katrin,

Thank you for the reply.

Make sure that the keywords that will be set on keywordPhrase array is existing with exact case as the this condition WHERE Criteria = "' + keywordPhrase[x] + '" is case sensitive.

[Katrin Munich]

Hi Ejay, 

I double checked the Keywords, they match exactly. I also switched up the order of the Keywords, the script always only exports the data for the last Keyword in the list and not the other two. 

It would be really great if you could check this. 

Kind regards, 

Katrin

[Google Ads Scripts Forum Advisor]

Hi Katrin,

The for-loop requires curly braces if multiple statements need to be executed within the loop. Without the braces it just executes the first statement after the for ( ... ). As it is now, it runs the report for using the first two keywords, but then just continues to the next iteration. It exits the loop and continues execution using the last value of the keyword phrase array. 

Hope this helps.

Regards,
Matt

[Katrin Munich]

Hi Matt, 

thank you so much! With this explanation I was able to fix it and it works perfectly now :) 

Have a good week

Katrin