OptiScore Tracker

// Copyright 2021
// 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: 28-11-2021
//
// 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.
//
////////////////////////////////////////////////////////////////////

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",
  
  // To receive an email with campaigns that score below the threshold, set to 'true'
  EMAIL_REPORT : true,
  EMAIL_RECIPIENTS : "example@example.com",
  EMAIL_SUBJECT : "OptiScore Alert " + AdsApp.currentAccount().getName()
  
}

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

function main() {
  
  // Select the folder for this account. The script will create folder(s) when not in Drive yet.
  var rootFolder = checkFolder(DriveApp.getRootFolder(), config.DRIVE_FOLDER);
  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);
  var emailContent = "";
  
  // 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)]);
      emailContent += row["campaign.name"] + ": " + (row["campaign.optimization_score"] * 100).toFixed(2) + "%<br />";
    }
      
  } // campaignIterator
  
  // Collect account data for the summary spreadsheet
  var accountQuery = "SELECT customer.optimization_score, customer.optimization_score_weight 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)]);
  } // accountIterator
  
  // Last fix of sheets
  campaignAlertTab.autoResizeColumns(1,campaignAlertTab.getLastColumn());  
  campaignDataTab.autoResizeColumns(1,campaignDataTab.getLastColumn()); 
  accountSummaryTab.autoResizeColumns(1,accountSummaryTab.getLastColumn()); 
  
  if(emailContent){
    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){
  
  if(config.EMAIL_REPORT === true){
    
    MailApp.sendEmail({
      to: config.EMAIL_RECIPIENTS,
      subject: config.EMAIL_SUBJECT,
      htmlBody: 
        "Some campaign(s) in your account 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