Modifying the Search Query Performance Report script

[Vy Nguyen]

Hi,

I'm trying to implement the search query report script to scan my campaign's search terms on a daily basis & add negative/positive keywords. I want to modify the script to only apply to campaigns with the label "fetchSearchTerms_test". Can someone take a look at my script to see if I'm doing it correctly? 

(Note: this is only a small part of the whole script)

function main() {
  var label = AdWordsApp.labels()
    .withCondition("Name = 'fetchSearchQuery_test'")
    .get().next();
  var report = AdWordsApp.report(
      'SELECT Query, Clicks, Cost, Ctr, ConversionRate,' +
      ' CostPerConversion, Conversions, CampaignId, AdGroupId ' +
      ' FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
      ' WHERE' + ' Labels CONTAINS_ANY ' +
      "[" + label.getId() + "] DURING LAST_14_DAYS" +
          ' Conversions > 0' +
          ' AND Impressions > ' + IMPRESSIONS_THRESHOLD +
          ' AND AverageCpc > ' +
           (AVERAGE_CPC_THRESHOLD * MICRO_AMOUNT_MULTIPLIER), REPORTING_OPTIONS);
  var rows = report.rows();

[Anthony Madrigal]

Hi Vy,

Labels are not supported in the Search Query Performance Report. You can first filter your campaigns by labels, then use those campaigns in your predicate in the Search Query Performance Report. 

var campaignIds = []
var campaignIt = AdWordsApp.labels()

   .withCondition("Name = 'fetchSearchQuery_test'")

   .get().next().campaigns().get();
while(campaignIt.hasNext()){
   var campaign = campaignIt.next();
   campaignIds.push(campaign.getId());
}

Cheers,

Anthony

AdWords Scripts Team

[Vy Nguyen]

Thanks, Anthony, for the script. This is my modified version. Basically, I want to add negative keywords from the search query to the campaign's Negative Keywords list. For the positive keywords, I want to add them to applicable ad groups. I've highlighted the parts that I added/modified. I just want to check with the experts if I'm doing this right.

  // Minimum number of impressions to consider "enough data"
  var IMPRESSIONS_THRESHOLD = 50;
  // Cost-per-click (in account currency) we consider an expensive keyword.
  var AVERAGE_CPC_THRESHOLD = 1; // $1
  // Threshold we use to decide if a keyword is a good performer or bad.
  var CTR_THRESHOLD = 0.5; // 0.5%
  // If ctr is above threshold AND our conversion cost isn’t too high,
  // it’ll become a positive keyword.
  var COST_PER_CONVERSION_THRESHOLD = 10; // $10
  // One currency unit is one million micro amount.
  var MICRO_AMOUNT_MULTIPLIER = 1000000;


  /**
   * Configuration to be used for running reports.
   */
   var REPORTING_OPTIONS = {
    // Comment out the following line to default to the latest reporting version.
   // apiVersion: 'v201609'
 };


 function main() {

 var report = AdWordsApp.report(
  'SELECT Query, Clicks, Cost, Ctr, ConversionRate,' +
  ' CostPerConversion, Conversions, CampaignId, AdGroupId ' +
  ' FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +

  ' WHERE Conversions > 0' +

  ' AND Impressions > ' + IMPRESSIONS_THRESHOLD +
  ' AND AverageCpc > ' +
  (AVERAGE_CPC_THRESHOLD * MICRO_AMOUNT_MULTIPLIER), REPORTING_OPTIONS);
 var rows = report.rows();

 var negativeKeywords = {};
 var positiveKeywords = {};
 var allAdGroupIds = {};
 // var allCampaignIds = {};

    // Iterate through search query and decide whether to
    // add them as positive or negative keywords (or ignore).
    while (rows.hasNext()) {
      var row = rows.next();
      if (parseFloat(row['Ctr']) < CTR_THRESHOLD) {
        addToMultiMap(negativeKeywords, row['AdGroupId'], row['Query']);
        allAdGroupIds[row['AdGroupId']] = true;
      } else if (parseFloat(row['CostPerConversion']) <
        COST_PER_CONVERSION_THRESHOLD) {
        addToMultiMap(positiveKeywords, row['AdGroupId'], row['Query']);
        allAdGroupIds[row['AdGroupId']] = true;
      }
    }

    // Copy all the adGroupIds from the object into an array.
    var campaignIds = []

    var campaignIt = AdWordsApp.labels()
    .withCondition("Name = 'fetchSearchQuery_test'")
    .get().next().campaigns().get();
    while(campaignIt.hasNext()){
     var campaign = campaignIt.next();
     campaignIds.push(campaign.getId());

   };

    // Add negative keywords to campaign negative keywords list
    var campaigns = AdWordsApp.campaigns().withIds(campaignIds).get();
    while (campaigns.hasNext()) {
      var campaign = campaigns.next();
      if (negativeKeywords[campaign.getId()]) {
        for (var i = 0; i < negativeKeywords[campaign.getId()].length; i++) {
          campaign.createNegativeKeyword(
            '[' + negativeKeywords[campaign.getId()][i] + ']');
        }
      }
    }

  // Add the keywords as negative or positive to the applicable ad groups.
    var adGroups = AdWordsApp.adGroups().withIds(adGroupIdList).get();
    while (adGroups.hasNext()) {
      var adGroup = adGroups.next();
      if (positiveKeywords[adGroup.getId()]) {
        for (var i = 0; i < positiveKeywords[adGroup.getId()].length; i++) {
          var keywordOperation = adGroup.newKeywordBuilder()
          .withText('[' + positiveKeywords[adGroup.getId()][i] + ']')
          .build();
        }
      }
    }
  }


  function addToMultiMap(map, key, value) {
    if (!map[key]) {
      map[key] = [];
    }
    map[key].push(value);
  }

[Anthony Madrigal]

Hi Vy,

If you want to add the negative keywords to your campaigns, rather than ad groups, you will need to modify the section if (parseFloat(row['Ctr']) < CTR_THRESHOLD) since that adds a row of negative keyword, ad group Id and query.

To address you initial question, you will need to put the campaign selector before the search query report as such: 

var campaignIds = []
var campaignIt = AdWordsApp.labels()
   .withCondition("Name = 'fetchSearchQuery_test'")
   .get().next().campaigns().get();
while(campaignIt.hasNext()){
   var campaign = campaignIt.next();
   campaignIds.push(campaign.getId());
}

var report = AdWordsApp.report(
'SELECT Query, Clicks, Cost, Ctr, ConversionRate,' +
' CostPerConversion, Conversions, CampaignId, AdGroupId ' +
' FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +

' WHERE CampaignId IN [' + campaignIds ']' +
' Conversions > 0' +

' AND Impressions > ' + IMPRESSIONS_THRESHOLD +
' AND AverageCpc > ' +
(AVERAGE_CPC_THRESHOLD * MICRO_AMOUNT_MULTIPLIER), REPORTING_OPTIONS);
var rows = report.rows();

If you are facing issues, please reply privately to author your CID and script name so I can take a closer look.

Regards,

Anthony

AdWords Scripts Team

[Vy Nguyen]

Hi Anthony,

Is this the correct way to modify the section? I replaced 'adGroup' with 'Campaign':

On Thursday, March 16, 2017 at 7:08:49 AM UTC-7, Anthony Madrigal wrote:

Hi Vy,

    while (rows.hasNext()) {
      var row = rows.next();

      if (parseFloat(row['Ctr']) < CTR_THRESHOLD) {
        addToMultiMap(negativeKeywords, row['CampaignId'], row['Query']);
        allCampaignIds[row['CampaignId']] = true;

      } else if (parseFloat(row['CostPerConversion']) < COST_PER_CONVERSION_THRESHOLD) {

        addToMultiMap(positiveKeywords, row['CampaignId'], row['Query']);
        allCampaignIds[row['CampaignId']] = true;
      }
    }

[Anthony Madrigal]

Hi Vy,

Since you will want the positive keywords to be added to ad groups, you can leave the else if (parseFloat(row['CostPerConversion']) < COST_PER_CONVERSION_THRESHOLD) part the same.

Please let me know if you face issues.

[mohit.me...@tcs.com]

Hi 

Nguyen
Can you please give me script for same.If possible m looking for same.

[Anthony Madrigal]

Hello,

If you are facing issues, can you please create a new thread detailing the issues you are facing, since this is an old thread?

Thanks,

Anthony

AdWords Scripts Team