OptiScore Tracker - MCC

// Copyright 2022 - until eternity
// Free to use or alter for everyone. A mention would be nice ;-)
//
// Created by: Tibbe van Asten
// for Adsscripts.com
//
// Created : 22-07-2021
// Last update: 17-08-2022 - Added an overview sheet with account scores
//
// ABOUT THE SCRIPT
// Create an overview of the Optimization Score for each campaign
// in your account and the score on account-level. The OptiScore-tracker 
// will show the score per month in a spreadsheet. For use in MCC.
//
////////////////////////////////////////////////////////////////////

var config = {
  
  LOG : true,     
  
  // Set a home folder for this script to put the data in.
  // Within the homefolder, a folder for this account will be created.
  // The homefolder doesn't need to be in your Drive already, the script
  // will check and create the folder when needed
  DRIVE_FOLDER : "OptiScore Tracker",  
  
  // Report campaigns in the Q-report for you to take action
  OPTISCORE_THRESHOLD : "75",

  // Only select campaigns with this label. Leave empty to ignore. You can process up to 50 accounts.
  ACCOUNT_LABEL: "Active account",
  
  // To receive an email with campaigns that score below the threshold, set to 'true'
  EMAIL_REPORT : true,
  EMAIL_RECIPIENTS : “email@example.com”,
  EMAIL_SUBJECT : "OptiScore Alert"
  
}

////////////////////////////////////////////////////////////////////

function main() {
  
  // Select accounts. Use label if set in config
  var accountIterator = AdsManagerApp.accounts();
    if(config.ACCOUNT_LABEL != ""){
      accountIterator = accountIterator.withCondition("LabelNames CONTAINS '"+config.ACCOUNT_LABEL+"'");
    }
  accountIterator = accountIterator.withLimit(50).get();
  
  var emailContent = "";
  var rootFolder = checkFolder(DriveApp.getRootFolder(), config.DRIVE_FOLDER);
  var accountOverviewSheet = checkSheet(rootFolder, "Account Overview");
  var accountOverviewTab = checkTab(accountOverviewSheet, "Account Overview", ["Date","Account","OptiScore"]);

  for (const account of accountIterator) {
    AdsManagerApp.select(account);
  
    // Select the folder for this account. The script will create folder(s) when not in Drive yet.
    var accountFolder = checkFolder(rootFolder, AdsApp.currentAccount().getName() + " (" + AdsApp.currentAccount().getCustomerId() + ")");

    // We use yesterday for data collecting and reporting
    var today = new Date();
    var yesterday = new Date(today);
    yesterday.setDate(yesterday.getDate() - 1);
    var year = yesterday.getFullYear();
    var quarter = Math.floor((yesterday.getMonth() + 3) / 3);

    // Check for existing spreadsheets or create them
    var dataSheet = checkSheet(accountFolder, "OptiScore Tracker Data - " + year + " - Q" + quarter);
    var summarySheet = checkSheet(accountFolder, "OptiScore Tracker - Summary")

    // Check for existing sheets or create them
    var campaignAlertTab = checkTab(dataSheet, "Campaign Alerts", ["Campaign","CampaignId","OptiScore"]);
    var campaignDataTab = checkTab(dataSheet, "Campaign Data", ["Date","CampaignId","Campaign","OptiScore","Impressions","Clicks"]);
    var accountSummaryTab = checkTab(summarySheet, "Summary Data", ["Date", "OptiScore", "OptiScore Weight"]);

    // Collect campaign data
    var campaignQuery = "SELECT campaign.name, campaign.id, campaign.optimization_score, metrics.impressions, metrics.clicks FROM campaign WHERE campaign.status = 'ENABLED' AND campaign.serving_status = 'SERVING' AND segments.date DURING YESTERDAY";
    var campaignReport = AdsApp.report(campaignQuery);

    // Clear alert tab, to only reflect actual scores
    campaignAlertTab.deleteRows(2,campaignAlertTab.getLastRow())

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

      campaignDataTab.appendRow([yesterday.toLocaleDateString("nl-NL"),row["campaign.id"],row["campaign.name"],(row["campaign.optimization_score"] * 100).toFixed(2),row["metrics.impressions"],row["metrics.clicks"]])

      if(row["campaign.optimization_score"] < (config.OPTISCORE_THRESHOLD/100)){
        campaignAlertTab.appendRow([row["campaign.name"],row["campaign.id"],(row["campaign.optimization_score"] * 100).toFixed(2)]);
      }

    } // campaignIterator

    // Collect account data for the summary spreadsheet
    var accountQuery = "SELECT customer.optimization_score, customer.optimization_score_weight, customer.descriptive_name FROM customer";
    var accountReport = AdsApp.report(accountQuery);

    var accountRows = accountReport.rows();
    while(accountRows.hasNext()){
      var accountRow = accountRows.next();

      accountSummaryTab.appendRow([today.toLocaleDateString("nl-NL"),(accountRow["customer.optimization_score"] * 100).toFixed(2),accountRow["customer.optimization_score_weight"].toFixed(2)]);
      accountOverviewTab.appendRow([today.toLocaleDateString("nl-NL"),accountRow["customer.descriptive_name"] ,(accountRow["customer.optimization_score"] * 100).toFixed(2)]);
      emailContent += accountRow["customer.descriptive_name"] + ": " + (accountRow["customer.optimization_score"] * 100).toFixed(2) + "%<br />";
    } // accountIterator

    // Last fix of sheets
    campaignAlertTab.autoResizeColumns(1,campaignAlertTab.getLastColumn());  
    campaignDataTab.autoResizeColumns(1,campaignDataTab.getLastColumn()); 
    accountSummaryTab.autoResizeColumns(1,accountSummaryTab.getLastColumn()); 
    
  }
  
  if(config.EMAIL_REPORT === true){
    sendEmail(emailContent);
  }
  
} // function main()

////////////////////////////////////////////////////////////////////

function checkFolder(root, folderName){
  
  if(root.getFoldersByName(folderName).hasNext()){    
    var driveFolder = root.getFoldersByName(folderName).next();
    
      if(config.LOG === true){
       Logger.log("Selected folder " + folderName);      
      }   
  } else {    
    var driveFolder = root.createFolder(folderName);
    
      if(config.LOG === true){
       Logger.log("Created folder " + folderName);      
      }
  }
  
  return driveFolder;   
  
} // function checkFolder

////////////////////////////////////////////////////////////////////

function checkSheet(folder, sheetName){
  
  if(folder.getFilesByName(sheetName).hasNext()){
    var sheet = folder.getFilesByName(sheetName).next();
    
    if(config.LOG === true){
     Logger.log("Selected file " + sheetName);      
    }
  } else {
    var sheet = SpreadsheetApp.create(sheetName);
    var file = DriveApp.getFileById(sheet.getId());
    var destFolder = DriveApp.getFolderById(folder.getId());
    file.moveTo(destFolder);

      if(config.LOG === true){
       Logger.log("Created file " + sheetName);      
      }
  }
  
  return sheet;
  
} // function checkSheet

////////////////////////////////////////////////////////////////////

function checkTab(file, tabName, headers){
  
  if(SpreadsheetApp.openById(file.getId()).getSheetByName(tabName)){
    var tab = SpreadsheetApp.openById(file.getId()).getSheetByName(tabName);
    
    if(config.LOG === true){
     Logger.log("Selected tab " + tabName);      
    }
  } else {
    var tab = SpreadsheetApp.openById(file.getId()).insertSheet(tabName);
    
    // Set content
    tab.appendRow(headers);
    
    if(tab.getMaxColumns() - tab.getLastColumn() != 0){
      tab.deleteColumns(tab.getLastColumn() + 1, tab.getMaxColumns() - tab.getLastColumn());
    }

    tab.getRange(1,1,1,tab.getLastColumn()).setFontWeight("bold");
    
      if(config.LOG === true){
       Logger.log("Created tab " + tabName);      
      }
  }
  
  // Remove default tab in Dutch
  if(SpreadsheetApp.openById(file.getId()).getSheetByName("Blad1")){
    var defaultSheet = SpreadsheetApp.openById(file.getId()).getSheetByName("Blad1")
    SpreadsheetApp.openById(file.getId()).deleteSheet(defaultSheet);
  }
  
  // Remove default tab in English
  if(SpreadsheetApp.openById(file.getId()).getSheetByName("Sheet1")){
    var defaultSheet = SpreadsheetApp.openById(file.getId()).getSheetByName("Sheet1")
    SpreadsheetApp.openById(file.getId()).deleteSheet(defaultSheet);
  }
  
  return tab;
  
} // function checkTab

////////////////////////////////////////////////////////////////////

function sendEmail(emailContent){
    
  MailApp.sendEmail({
    to: config.EMAIL_RECIPIENTS,
    subject: config.EMAIL_SUBJECT,
    htmlBody: 
    "Some campaign(s) in your accounts have an OptiScore below the threshold.<br />" +
    "Maybe you should do something about that.<br /><br />" +
    emailContent +
    "<br />Cheers,<br /><b>Adsscripts.com</b>"
  });

  if(config.LOG === true){
    Logger.log("Email send");
  }
  
} // function sendEmail