Find out how much your account may be impacted by Google's match type changes in Feb 2021 - MCC version

BMM and Phrase Match Change Analysis - MCC version

/*************************************************
* Analysis of the impact of Phrase Match and Broad Match Modified merging in Google Ads in 2021
* @author Naman Jindal <nj.itprof@gmail.com>
* For Optmyzr.com - Award Winning PPC Management Suite - Try it free for 2 weeks at www.optmyzr.com
* @version 1.0
***************************************************/

// Make your own copy of this spreadsheet
// then add the URL to the line below: https://swiy.io/phrase-bmm-template-spreadsheet
var URL = 'ENTER YOUR GOOGLE SPREADSHEET URL HERE'; 
var ACCOUNT_LABEL = ""; // an optional account label that must be present for an account in the MCC to be included in this analysis

if(URL=='ENTER YOUR GOOGLE SPREADSHEET URL HERE') Logger.log("Please make a copy of this spreadsheet (https://swiy.io/phrase-bmm-template-spreadsheet) in your own Google account and then add the URL of your spreadsheet on the next line.");


// Execution Begins here
function main() {
  var iter = AdsManagerApp.accounts();
  
  if(ACCOUNT_LABEL) {
    iter.withCondition("LabelNames CONTAINS '"+ACCOUNT_LABEL+"'")
  }
  
  iter.withCondition('Clicks > 0')
    .forDateRange('LAST_30_DAYS')
    .orderBy('Impressions DESC')
    //.withLimit(5)
    .executeInParallel('run', 'compile');
  
  Logger.log("Your analysis is ready at " + URL);
}

// Runs Parallel in multiple accounts under the mcc and gathers the data for output
function run() {
  
  Logger.log("Starting analysis of account " + AdWordsApp.currentAccount().getName());
  var map = {
    'Total': { 'Count': 0, 'Impressions': 0, 'Clicks': 0, 'Cost': 0, 'Conversions': 0, 'DuplicatedKeywords': 0, 'SBClicks': 0 },
    'Exact': { 'Count': 0, 'Impressions': 0, 'Clicks': 0, 'Cost': 0, 'Conversions': 0 },
    'Phrase': { 'Count': 0, 'Impressions': 0, 'Clicks': 0, 'Cost': 0, 'Conversions': 0 },
    'Broad': { 'Count': 0, 'Impressions': 0, 'Clicks': 0, 'Cost': 0, 'Conversions': 0 },
    'BMM': { 'Count': 0, 'Impressions': 0, 'Clicks': 0, 'Cost': 0, 'Conversions': 0 },
    'Partial BMM': { 'Count': 0, 'Impressions': 0, 'Clicks': 0, 'Cost': 0, 'Conversions': 0 }
  };
  
  var duplicatedKeywordsMap = {}, alreadyMarkedDuplicate = {};
  
  var manualBiddingStrategies = ['cpv', 'cpc', 'cpm', 'None', 'unknown']
  var query = [
    'SELECT CampaignId, AdGroupId, Id, KeywordMatchType, Criteria, BiddingStrategyType, EnhancedCpcEnabled,',
    'Clicks, Cost, Impressions, Conversions',
    'FROM KEYWORDS_PERFORMANCE_REPORT',
    'WHERE Status = ENABLED and AdGroupStatus = ENABLED and CampaignStatus = ENABLED',
    'DURING LAST_30_DAYS'
  ].join(' ');
  
  var rows = AdsApp.report(query, {'includeZeroImpressions': true}).rows();
  while(rows.hasNext()) {
    var row = rows.next();
    var mt = row.KeywordMatchType;
    var text = row.Criteria;
    var cleanText = text.replace(/[+]/g, '');
    
    // Collect Clicks for Smart Bidding Keywords
    if(manualBiddingStrategies.indexOf(row.BiddingStrategyType) < 0 || 
       (row.BiddingStrategyType == 'cpc' && row.EnhancedCpcEnabled == true)) {
      map['Total']['SBClicks'] += parseInt(row.Clicks,10);
    }
    
    
    if(!duplicatedKeywordsMap[row.CampaignId]) {
      duplicatedKeywordsMap[row.CampaignId] = {}; 
      alreadyMarkedDuplicate[row.CampaignId] = {};
    }
    
    // Check if the keyword exists as duplicate in the same campaign
    if(duplicatedKeywordsMap[row.CampaignId][cleanText]) {
      if(!alreadyMarkedDuplicate[row.CampaignId][cleanText]) {
        map['Total']['DuplicatedKeywords']++;
        alreadyMarkedDuplicate[row.CampaignId][cleanText] = 1;
      }
    }
    
    duplicatedKeywordsMap[row.CampaignId][cleanText] = 1;
      
    
    // Clean up Matchtype
    if(mt == 'Broad' && text.indexOf('+') > -1) {
      var bmmText = '+' + cleanText.split(' ').join(' +');
      
      if(text == bmmText) {
        mt = 'BMM'; 
      } else {
        mt = 'Partial BMM'; 
      }
    }
    
    map[mt]['Count']++;
    map[mt]['Impressions'] += parseInt(row.Impressions,10);
    map[mt]['Clicks'] += parseInt(row.Clicks,10);
    map[mt]['Conversions'] += parseFloat(row.Conversions);
    map[mt]['Cost'] += parseFloat(row.Cost.toString().replace(/,/g,''));
    
    map['Total']['Count']++;
    map['Total']['Impressions'] += parseInt(row.Impressions,10);
    map['Total']['Clicks'] += parseInt(row.Clicks,10);
    map['Total']['Conversions'] += parseFloat(row.Conversions);
    map['Total']['Cost'] += parseFloat(row.Cost.toString().replace(/,/g,''));
  }
  
  return JSON.stringify({
    'accId': AdsApp.currentAccount().getCustomerId(),
    'accName': AdsApp.currentAccount().getName(),
    'dataMap': map
  })
}

// Compile Results from Multiple accounts
function compile(results) {
  var tab = SpreadsheetApp.openByUrl(URL).getSheets()[0];
  
  // Clear the accounts section for fresh data entry
  tab.getRange(1,2,tab.getLastRow(),tab.getLastColumn()).clearContent();
  
  for(var i in results) {
    if(!results[i].getReturnValue()) { continue; }
    var resp = JSON.parse(results[i].getReturnValue());
    
    var dataMap = resp.dataMap;
    var col = [[resp.accName + ' (' +resp.accId + ')'], ['']];
    
    // Totals
    for(var mt in dataMap) {
     col.push([dataMap[mt].Count]);
    }
    
    
    var totalsRow = dataMap['Total'];
    delete dataMap['Total'];
    
    col.push([''], ['']);
    
    // %
    for(var mt in dataMap) {
     col.push([totalsRow.Count == 0 ? 0 : dataMap[mt].Count / totalsRow.Count]);
    }
    
    col.push([''], ['']);
    
    // Cost
    for(var mt in dataMap) {
     col.push([dataMap[mt].Cost]);
    }
    
    col.push([''], ['']);
    
    // Impressions
    for(var mt in dataMap) {
     col.push([dataMap[mt].Impressions]);
    }
    
    col.push([''], ['']);
    
    // Clicks
    for(var mt in dataMap) {
     col.push([dataMap[mt].Clicks]);
    }
    
    col.push([''], ['']);
    
    // Conversions
    for(var mt in dataMap) {
     col.push([dataMap[mt].Conversions]);
    }
    
    col.push([''], [''], [totalsRow.DuplicatedKeywords], ['']);
    
    var pctSmartBiddingClicks = totalsRow.Clicks == 0 ? 0 : totalsRow.SBClicks / totalsRow.Clicks;
    col.push([pctSmartBiddingClicks]);
    
    // Output the data for account in next available column
    tab.getRange(1,tab.getLastColumn()+1, col.length, 1).setValues(col);
  }
  
}