Get an analysis related to the impact of Google Ads merging phrase match and broad match modified keywords in 2021

Phrase and BMM Analysis

/*************************************************
* 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'; 
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 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(' ');
  
  Logger.log (query);
  
  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, '');
    //Logger.log (cleanText);
    
    // 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);
      //Logger.log(row.BiddingStrategyType);
    }
    
    
    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(' +');
      //Logger.log(bmmText);
      
      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,''));
  }
  
  var accId = AdsApp.currentAccount().getCustomerId();
  var accName = AdsApp.currentAccount().getName();
  
  // Output 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();
  //Logger.log("here");
  var dataMap = map;
  var col = [[accName + ' (' + 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]);
  //Logger.log(pctSmartBiddingClicks);
  
  // Output the data for account in next available column
  tab.getRange(1,tab.getLastColumn()+1, col.length, 1).setValues(col);

  Logger.log("Check the results at " + URL);
}