Looking for a Script to export High-Performing Query from Keyword

[Anshul Jain]

Hi All,

I have recently come across a script to add high performing keywords into campaign/adgroup level. The code is shared below.  But I want this script to analyze high performing search terms and export them on a spreadsheet from time to time where I can manually analyze them first and then can add in the campaigns/adgroup within a give CPA

Here is the code I am Using:

*******************************************************************************/
var maxCPA = 100;
// Enter the maximum all-time CPA the search term requires above.
// To only consider search terms with a £10 CPA, enter 10. To only consider
// search terms with a £1.50 CPA, enter 1.50.
// Example: var maxCPA = 10;
// Example: var maxCPA = 1.50;

var minROAS = 0;
// Enter the minimum all-time ROAS the search term requires above.
// To only consider search terms with at least a 3.5:1 all-time ROAS, enter 3.5.
// To only consider search terms with at least a 5 to 1 all-time ROAS, enter 5.
// Example: var minROAS = 3.5;
// Example: var minROAS = 5;

var minImpressions = 0;
// Enter the minimum number of all-time impressions the search term requires
// above as an integer. To only consider search terms with 2 or more
// all-time impressions, enter 2.
// Example: var minImpressions = 2;

var minConversions = 0.01;
// Enter the minimum number of all-time conversions the search term requires
// above. To only consider search terms with 2 or more all-time conversions,
// enter 2. To only consider search terms with 0.5 or more all-time
// conversions, enter 0.5.
// Example: var minConversions = 2;
// Example: var minConversions = 0.5;

var yourEmail = "";
// Enter your email above if you'd like to be emailed the log when the script
// has finished running. If you'd rather not receive the email log, leave this
// blank. You can add multiple emails using commas, if you want.

/******************************************************************************//*                       DON'T TOUCH THE STUFF BELOW                          *//******************************************************************************/
minImpressions--;
minConversions -= 0.01;
minROAS -= 0.01;

var fullCleanLog = ""; // initialise fullCleanLog
var keywordCount = 0; // intialise keyword count

function main() {

  // Campaign Broad
  var broadCampaignReport = gimmeMyReport("Campaign", "Broad");
  cpaCheck(broadCampaignReport, "broad");
  cleanLog(" ");

  var bmmCampaignReport = gimmeMyReport("Campaign", "BMM");
  cpaCheck(bmmCampaignReport, "broad");
  cleanLog(" ");

  // Ad Group Broad
  var broadAdGroupReport = gimmeMyReport("Ad Group", "Broad");
  cpaCheck(broadAdGroupReport, "broad");
  cleanLog(" ");

  var bmmAdGroupReport = gimmeMyReport("Ad Group", "BMM");
  cpaCheck(bmmAdGroupReport, "broad");
  cleanLog(" ");

  // Campaign Phrase
  var phraseCampaignReport = gimmeMyReport("Campaign", "Phrase");
  cpaCheck(phraseCampaignReport, "phrase");
  cleanLog(" ");

  // Ad Group Phrase
  var phraseAdGroupReport = gimmeMyReport("Ad Group", "Phrase");
  cpaCheck(phraseAdGroupReport, "phrase");
  cleanLog(" ");

  // Campaign Exact
  var exactCampaignReport = gimmeMyReport("Campaign", "Exact");
  cpaCheck(exactCampaignReport, "exact");
  cleanLog(" ");

  // Ad Group Exact
  var exactAdGroupReport = gimmeMyReport("Ad Group", "Exact");
  cpaCheck(exactAdGroupReport, "exact");
  cleanLog(" ");

  if (keywordCount > 0) {
    try {
      var subject = "[DAVE] Keyword Report for " + AdsApp.currentAccount().getName();
      var body = fullCleanLog;
      MailApp.sendEmail(yourEmail, subject, body);
    } catch (e) {
      cleanLog("Unable to send keyword report email. Please check the email "
      + "addresses provided are valid.");
    }
  }

  Logger.log("Added " + keywordCount + " keyword(s).");
}

// Gets the report at either campaign or ad group level of search queries which:
// - have at least the minimum threshold of conversions
// - are not currently being targeted in the account
// - are in enabled ad groups and campaigns
// - have at least the minimum threshold of impressions
function gimmeMyReport(level, matchType) {
  if (level.toLowerCase() == "campaign") {
    reportLevel = "CampaignName";
  } else if (level.toLowerCase() == "ad group") {
    reportLevel = "AdGroupName";
  }
  cleanLog("Level: " + level + ". Match Type: " + matchType);

  var report = AdWordsApp.report(
    "SELECT Query, Conversions, Cost, AdGroupId, QueryTargetingStatus,"
    + " CampaignStatus, AdGroupStatus, CampaignName, AdGroupName, Impressions,"
    + " Clicks, Ctr, ConversionRate, CostPerConversion, ConversionValue,"
    + " QueryMatchTypeWithVariant, KeywordTextMatchingQuery, AverageCpc"
    + " FROM SEARCH_QUERY_PERFORMANCE_REPORT"
    + " WHERE Conversions > "  + minConversions
    + " AND QueryTargetingStatus = 'NONE' AND"
    + " CampaignStatus = 'ENABLED' AND AdGroupStatus = 'ENABLED'"
    + " AND Impressions > " + minImpressions
    + " AND KeywordTextMatchingQuery DOES_NOT_CONTAIN 'URL=='"
    + " AND KeywordTextMatchingQuery DOES_NOT_CONTAIN 'CATEGORY=='"
    + " AND KeywordTextMatchingQuery != '*'"
    + " AND " + reportLevel + " CONTAINS_IGNORE_CASE '" + matchType + "'"
  );

  return report;
}

// Checks whether CPA is below target. If so, it checks if checks whether query
// is eligible to be added as a keyword and then adds it if so.
function cpaCheck(report, criterionType) {
  var rows = report.rows();

  while (rows.hasNext()) {

    var row = rows.next();
    var adGroupId = row["AdGroupId"];
    var searchQuery = row["Query"];
    var campaignName = row["CampaignName"];
    var adGroupName = row["AdGroupName"];
    var impr = row["Impressions"];
    var clicks = row["Clicks"];
    var ctr = row["Ctr"];
    var avgCpc = row["AverageCpc"];
    var cost = row["Cost"];
    var conv = row["Conversions"];
    var convRate = row["ConversionRate"];
    var CPA = row["CostPerConversion"]; // can this use CostPerConversion ?
    var revenue = row["ConversionValue"];
    var roas = revenue / cost;
    var kwText = row["KeywordTextMatchingQuery"];

    var stats = ["Search Query: " + searchQuery,
    "Campaign: " + campaignName,
    "Ad Group: " + adGroupName,
    "Keyword: " + kwText,
    "Impressions: " + impr,
    "Clicks: " + clicks,
    "CTR: " + ctr,
    "Avg. CPC: " + avgCpc,
    "Cost: " + cost,
    "Revenue: " + revenue,
    "ROAS: " + roas.toFixed(2),
    "Conversions: " + conv,
    "Conv. rate: " + convRate,
    "CPA: " + CPA];

    var adGroupSelector = AdsApp.adGroups().withIds([adGroupId]);

    var adGroupIterator = adGroupSelector.get();
    while (adGroupIterator.hasNext()) {
      var adGroup = adGroupIterator.next();

      if (CPA <= maxCPA) {
        if (roas >= minROAS) {
          try {
            if (canWeEvenDealWithThisTing(searchQuery)) {
              addThatTing(searchQuery, adGroup, criterionType, CPA, avgCpc, conv, stats);
              keywordCount++;
            }
          } catch (e) {
            cleanLog("Unable to add " + searchQuery + " as keyword.");
          }
        }
      }
    }
  }
}

// Adds the search term as a targeted keyword with either a broad match
// modifier, phrase or exact match type
function addThatTing(thatQuery, thatAdGroup, thatCriterionType,
  thatCPA, bid, thatConv, thatStats) {
    if (thatCriterionType == "broad") {
      var addedQuery = "+" + thatQuery.replace(/ /g," +");
    } else if (thatCriterionType == "phrase") {
      var addedQuery = '"' + thatQuery + '"';
    } else {
      var addedQuery = "[" + thatQuery + "]";
    }

    var keywordOperation = thatAdGroup.newKeywordBuilder()
    .withText(addedQuery)
    .withCpc(bid)
    .build();

    cleanLog(" ");

    // Logs statistics and details of the search query to the Logger
    thatStats.forEach(function(stat) {
      cleanLog("- " + stat);
    });
  }

// Logs a message and returns false if query is too long (more than 10 words or
// more than 80 characters) to be added as a targeted keyword
function canWeEvenDealWithThisTing(longQuery) {
  if ((longQuery.match(/\s/gi) || []).length > 10) {
    cleanLog(">> '" + longQuery
    + "' is more than 10 words so can't be added.");
    return false;
  } else if (longQuery.length > 80) {
    cleanLog(">> '" + longQuery
    + "' is longer than 80 characters so can't be " +
    "added.");
    return false;
  } else {
    return true;
  }
}

function cleanLog(input) {
  Logger.log(input);
  fullCleanLog += "\n" + input;
}

[Google Ads Scripts Forum Advisor]

Hi Anshul,

Thanks for posting your concern.

I am afraid that there is currently no script that meets your requirement. However, you may try to utilize the Search Query Performance Report to get the performance of the search terms. After examining the result, you can use KeywordBuilder to convert the high performing search terms into keywords.

Furthermore, you may refer to this code as it illustrates how to import report data into spreadsheet.

Let me know if you have further questions.

Regards,
Ejay
Google Ads Scripts Team

ref:_00D1U1174p._5004Q21kg7D:ref