OptiScore Tracker - MCC

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

Start Now!
Google Ads OptiScore Tracker for MCC
OptiScore Get started!

As PPC specialists, we can hardly ignore it anymore; Google's Optimization Score. After introducing this statistic, it has gained more and more value for Google. Implementing recommendations potentially affects the performance of your campaigns and is a requirement for agencies to obtain the partner badge. But getting a quick overview of the score per campaign has not been made as easy.

OptiScore per campaign

This script will therefore help you monitore the OptiScore for the campaigns in your accounts. To make it as easy as possible, you can easily hang this script in your MCC with copy/paste. Do provide in the script a lower limit of the OptiScore that the campaigns should reach, if you want to receive automatic email notifications with the campaigns you need to take action on.

Overview of accounts

This script allows you to check up to 50 accounts in 1x. The script automatically adds a sheet to your drive containing an overview of the scores per account. In addition, you get a folder per account that keeps track of the score per campaign, so you can dive in further there. Make sure you are quickly informed of the score per account by having the script send you an email.

Note: this is a 'New Scripts Experience' script, so you have to enable this option at the top of the script (default for new scripts). This allows you to use the Google Ads API, which contains the optimization score of Google.


  • LOG: Specify whether the script should report the intermediate steps, by adjusting the value to true.
  • DRIVE_FOLDER: Choose a folder on Drive to store the collected statistics.
  • OPTISCORE_THRESHOLD: Specify a value that you want your campaigns to meet as a minimum.
  • EMAIL_REPORT: Set to true to get an email with the campaigns scoring below the threshold.
  • EMAIL_RECIPIENTS: Specify to whom this mail should be sent.
  • EMAIL_SUBJECT: Subject of the mail, does not need to be modified.

Frequency: Set this script to run once a week or month.

The script
// 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
// 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

  // 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) {
    // 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

    var campaignRows = campaignReport.rows();
      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();
      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
  if(config.EMAIL_REPORT === true){
} // function main()


function checkFolder(root, folderName){
    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){
    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());

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


function checkTab(file, tabName, headers){
    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
    if(tab.getMaxColumns() - tab.getLastColumn() != 0){
      tab.deleteColumns(tab.getLastColumn() + 1, tab.getMaxColumns() - tab.getLastColumn());

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


function sendEmail(emailContent){
    to: config.EMAIL_RECIPIENTS,
    subject: config.EMAIL_SUBJECT,
    "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
Show whole script!
Loading Comments
The Experts
Tibbe van Asten Team Lead Performance Marketing
Nils Rooijmans Water Cooler Topics
Martijn Kraan Freelance PPC Specialist
Bas Baudoin Teamlead SEA @ Happy Leads
Jermaya Leijen Digital Marketing Strategist
Krzysztof Bycina PPC Specialist from Poland
How about you? JOIN US!
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 &
Contact us!
Adsscripts Training & Workshop