Pull performance data to spreadsheet using keyword labels

[tpre...@expedia.com]

Hi there,

I've amended an existing adwords script below; essentially i want to pull key metrics by their keyword labels to a google spreadsheet. I want for each line in a spreadsheet: label name, total clicks, impressions, cost, average position, conversions etc.

The script runs but apparently reaches entity limits, which doesn't make sense as it's a small account. Could someone pls help bug check?

var RECIPIENT_EMAIL = 'EXA...@EXAMPLE.COM';

var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/1Zl7DMGBebAmcNopmu5A0UFbSOiLWKw1LtuNKAp153kE/edit?usp=sharing';

var LABEL_MAP_SIZE = 2;

function main() {

  validateEmail(RECIPIENT_EMAIL);

  Logger.log('Using template spreadsheet - %s.', SPREADSHEET_URL);

  var spreadsheet = copySpreadsheet(SPREADSHEET_URL);

  Logger.log(

      'Generated new reporting spreadsheet %s based on the template ' +

          'spreadsheet.The reporting data will be populated here.',

      spreadsheet.getUrl());

  spreadsheet.getRangeByName('date_label').setValue('Date'); //need to define date_label in Googlesheet

  spreadsheet.getRangeByName('date_value').setValue(new Date()); //need to define date_value in Googlesheet

  spreadsheet.getRangeByName('account_id') //need to define account_id in Googlesheet

      .setValue(AdWordsApp.currentAccount().getCustomerId());

  outputLabelData(spreadsheet);

  Logger.log(

      'Keyword performance report available at\n' + spreadsheet.getUrl());

  if (RECIPIENT_EMAIL) {

    MailApp.sendEmail(

        RECIPIENT_EMAIL, 'Keyword Performance Report is ready',

        spreadsheet.getUrl());

  }

}

function getKeywordsWithSelectedLabels() {

    return AdWordsApp.keywords()

      .forDateRange('LAST_WEEK')

      .withCondition('Impressions > 0')

      .withCondition("LabelNames CONTAINS_ANY ['LABEL_1' , 'LABEL_2,]")

    .get();

}

 

function outputLabelData(spreadsheet) {

  // Output header row

  var header = [

    'Label Name', 'Num Keywords', 'Impressions', 'Clicks', 'Cost'

  ];

  spreadsheet.getRangeByName('label_data_headings').setValues([header]);//need to define range in GoogleSheet

  // Initialize LabelDataMap

  var LabelDataMap = getEmptyStatMap(LABEL_MAP_SIZE);

  

  // Output data to spreadsheet//might need attention

  var rows = [];

    for (var key in LabelDataMap) {

     var row = [

      key, LabelDataMap[key].numKeywords,

      LabelDataMap[key].totalImpressions, LabelDataMap[key].totalClicks,

      LabelDataMap[key].totalCost

    ]

    for (var key; key<=100; LabelDataMap) 

    {

      rows.push(row)

    };

  }

  Logger.log(rows)

  

  var spreadsheetrange = spreadsheet.getRangeByName('label_data_body').setValues(rows);

  

}

function getEmptyStatMap(size) {

      var LabelNameMap = []; //aka qualityScoreMap

  for (i = 1; i <= size; i++) {

      LabelNameMap[i] =

      {numKeywords: 0, totalImpressions: 0, totalClicks: 0, totalCost: 0.0};

  }

  return LabelNameMap;

}

  

function computeLabelData(keywordIterator, LabelNameMap) {

  while (keywordIterator.hasNext()) {

      var keyword = keywordIterator.next();

      var stats = keyword.getStatsFor('LAST_WEEK');

      var data = LabelNameMap[keyword.getId()];

  if (data) {

      data.numKeywords++;

      data.totalImpressions +=stats.getImpressions();

      data.totalClicks += stats.getClicks();

      data.totalCost += stats.getCost();

    }

  }

}

function copySpreadsheet(spreadsheetUrl) {

  var spreadsheet = validateAndGetSpreadsheet(spreadsheetUrl)

                        .copy(

                            'Keyword Performance Report - ' +

                            getDateStringInTimeZone('MMM dd, yyyy HH:mm:ss z'));

  // Make sure the spreadsheet is using the account's timezone.

  spreadsheet.setSpreadsheetTimeZone(AdWordsApp.currentAccount().getTimeZone());

  return spreadsheet;

}

function getDateStringInTimeZone(format, date, timeZone) {

  date = date || new Date();

  timeZone = timeZone || AdWordsApp.currentAccount().getTimeZone();

  return Utilities.formatDate(date, timeZone, format);

}

  

function validateEmail(email) {

  if (email == 'em...@example.com') {

    throw new Error('Please use a valid email address.');

  }

}

function validateAndGetSpreadsheet(spreadsheeturl) {

  if (spreadsheeturl == 'YOUR_SPREADSHEET_URL') {

    throw new Error(

        'Please specify a valid Spreadsheet URL. You can find' +

        ' a link to a template in the associated guide for this script.');

  }

  return SpreadsheetApp.openByUrl(spreadsheeturl);

}

[Adrian Catambay (AdWords Scripts Team)]

Hello,

I will delete your original post since it contains private information. In the future, please refrain from posting private information such as your spreadsheet URL.

Reposting this with masked information for reference.

 

Hi there,
I've amended an existing adwords script below; essentially i want to pull key metrics by their keyword labels to a google spreadsheet. I want for each line in a spreadsheet: label name, total clicks, impressions, cost, average position, conversions etc.
The script runs but apparently reaches entity limits, which doesn't make sense as it's a small account. Could someone pls help bug check?

var RECIPIENT_EMAIL = 'YOUR_RECIPIENT_EMAIL';
var SPREADSHEET_URL = 'YOUR_SPREADSHEET_URL;

Thanks,

Adrian

AdWords Scripts Team

[Adrian Catambay (AdWords Scripts Team)]

Hello,

Since you said your code is running fine but is reaching entity limits, you might want to check some of your iterators and selectors if they are fetching entities above the specified limit of AdWords Scripts. You may refer to the Entity Limits documentation to help sort this out. I would also suggest that you consider using Reports instead since it is not subject to any entity limits when running scripts as indicated here, and is considered best practice when retrieving large amounts of entities.

If you have verified that the number of entities you are retrieving is still below the specified entity limits, kindly Reply privately to author with your CID, script name, and spreadsheet access so I can further investigate on this issue.