OptiScore Tracker

Easily monitor the OptiScore of your campaigns and receive a notification when action is required!

Start Now!
Google Ads OptiScore Tracker
OptiScore Get started!

As PPC specialists, we can hardly ignore it; Google's optimization score. After the introduction of this statistic, it has become increasingly valuable for Google. Implementing recommendations may affect the performance of your campaigns and is a requirement for agencies to obtain the Premier Partner badge. But getting a quick overview of the score per campaign has been made less easy.

OptiScore per campaign

This script will therefore help you monitor the OptiScore for the campaigns in your account. To make it as easy as possible, you can simply copy / paste this script into your account. In the script, enter a threshold of the OptiScore that the campaigns must achieve if you want to receive an automatic notification by email with the campaigns that you need to take action on.

Note: this is a 'New Scripts Experience' script, so you must enable this option at the top of the script. With this you can use the Google Ads API, where the optimization score of Google can be found.

Settings

  • LOG:Indicate whether the script should report the intermediate steps, by adjusting the value to 'true'.
  • DRIVE_FOLDER: Pick a home folder on your Drive
  • OPTISCORE_THRESHOLD: Set a minimum OptiScore your campaign should achieve.
  • EMAIL_REPORT: Set to 'true' to receive an email with the campaigns that do not make the threshold.
  • EMAIL_RECIPIENTS: The e-mailaddress(es) the email is send to.
  • EMAIL_SUBJECT: No need to change this.

Frequency: Set this script to run once a week.

The script
// 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
Show whole script!
Loading Comments
The Experts
Tibbe van Asten Team Lead Search
Nils Rooijmans Water Cooler Topics
Martijn Kraan Freelance PPC Specialist
Bas Baudoin Teamlead SEA @ Happy Leads
How about you? JOIN US!
Sharing Knowledge
Caring

Sharing Knowledge

Adsscripts.com is all about sharing knowledge. In the current market, PPC specialists like to keep their knowledge and experience to themselves. We're convinced that sharing knowledge can ensure that everyone gets better at their work. We want to change this by sharing our knowledge about scripts with everyone.

Do you also want to contribute? We are open to new ideas and feedback on everything you find on Adsscripts.com.

Contact us

Training &
Workshop
Contact us!
Adsscripts Training & Workshop