Conversions Issue Detector v2.4 (MCC)

SPONSOR - Custom Automations by MediaLauncher.io 🚀

Hey, before I leave you to your script I have an announcement to make: you can now get me (and soon my team) to create custom automations for you!

You can book us for a one-off project, or on a monthly basis. We offer:

• Up to Unlimited requests

• Unlimited revisions

• Free technical project analysis 

• Tailored recommendations

• High priority support

• Full access to the source code

• Whitelabeling

• 30 days money back guarantee

This is perfect for you if your business relies heavily on advertising for revenue, but lacks technical capabilities to automate tasks to increase productivity. Ideal for mid to large Ecommerce, startups that raised funding, agencies, or corporates with small marketing teams.

Platforms we automate: Google Ads, Meta Ads, Linkedin Ads, Native, & more.

The tools we use: Custom scripts, Zapier, Make, n8n, Supermetrics, Funnel, & more.

We’re still working on the website, so if you’re interested in learning more submit a request here. Describe your needs, attach your company website, and let’s see if we’re a fit.

Looking forward to taking your ads game to the next level! 🚀 

Conversions Issue Detector v2.4 - Purpose

This script runs at the MCC level and monitors Google Ads accounts labeled as "Conversions Issue Detector" Conversions data.

It alerts when an account's Conversions deviate significantly from historical patterns.

An alert email is triggered for a decrease in Conversions of 80% or more compared to the historical average.

Unlike other similar scripts, the Conversions Issue Detector reports exclusively on Conversions data. It also ensures only one alert is sent per day, minimizing noise and false triggers.

In addition, the script tracks conversion data for each monitored account over the last 30 days, generating a visual trend line that highlights patterns and anomalies.

Behavior

The script first filters the accounts, and for each account that has been labeled as "Conversions Issue Detector," it compares "yesterday's" full-day stats to the same day of the week over the past few weeks. The default is set to the last 4 occurrences.

For example, if the script runs on a Wednesday, it assesses Tuesday's Conversions against the average Conversions from the previous four Tuesdays.

How to Use It

1. Add a new label "Conversions Issue Detector" to the accounts under your MCC that you wish to monitor.

2. Make a copy of the template sheet.

3. In your Google Ads account, create a new script and paste the provided code below.

4. Replace the 'SHEET_URL_HERE' placeholder inside the script with the URL of your copied template.

5. Replace the 'YOUR_EMAIL_ADDRESS_HERE' placeholder with the email address where you want to receive alerts.

6. Schedule the script to run daily.

7. Run the script—it will automatically populate both Report1 and Report2 with the latest data.

Customize this script

Want to an MCC version? Maybe specific filters, or even add AI features? We got you covered!

Submit a request here. Describe your needs, attach your company website, and let’s see if we’re a fit.

The code

/**
 * Created by Francesco Cifardi
 * For issues or questions reachout on Linkedin (https://www.linkedin.com/in/francescocifardi/)
 * Free to use and share, a mention on Linkedin would be appreciated :-)
 * Looking to get new script updates via email? Subscribe to my Free Google Ads Scripts Newsletter
 * No content, no regular emails, only scripts and other automation tools whenever they are ready.
 * https://medialauncher.beehiiv.com/subscribe
 *
 * MCC Conversions Issue Detector v2.4 ©
 *
 * Purpose:
 * This script runs at the MCC level and monitors Google Ads accounts labeled as "Conversions Issue Detector" 
 * Conversions data. It alerts when an account's Conversions deviate significantly from historical patterns.
 * An alert email is triggered for a decrease in Conversions of 80% or more compared to the historic average.
 * Unlike other similar scripts, the Conversions Issue Detector reports exclusively on Conversions data. 
 * It also ensures only one alert is sent per day, minimizing noise and false triggers.
 *
 * Behavior:
 * The script first filters the accounts, and for each account that has been labeled as 
 * "Conversions Issue Detector" it compares "yesterday's" full-day stats to the same day of the week 
 * over the past few weeks. Default is set to the last 4 occurrences.
 * For example, if the script runs on a Wednesday, it assesses Tuesday's Conversions 
 * against the average Conversions from the previous four Tuesdays.
 *
 * Additionally, the script tracks and visualizes the last 30 days of conversion data for each account,
 * providing a trend line to help identify patterns and anomalies over a longer period.
 *
 * Report Structure:
 * The script writes to two sheets:
 *
 * Report1:
 * - Rows 1-4: Summary information (Last run time, Yesterday's date, Historic dates, Email Alert Status)
 * - Row 6: Column headers for account data
 * - Row 7 onwards: Account performance data including:
 *   * Account ID
 *   * Account Name
 *   * Yesterday's Conversions
 *   * Historic Average
 *   * Absolute Difference
 *   * Percentage Difference
 *   * 30-Day Trend (visual line chart)
 * - Red highlighting on Percentage Difference and Trend when conversions drop significantly
 *
 * Report2:
 * - Row 1: Headers (Account ID, Account Name, followed by 30 dates)
 * - Row 2 onwards: Daily conversion data for each account over the last 30 days
 *
 * Configuration:
 * - 'sheetUrl' points to the Google Sheet where the data is written. 
 *    MAKE A COPY OF THIS TEMPLATE: https://docs.google.com/spreadsheets/d/1SQw40ip0kuM5JZ1AQdFqsKWRvQZILrSMw4oRbXIxCoM/
 * - 'emailAddress' is the recipient of alert emails
 * - 'percentageThreshold' sets the threshold for triggering an alert (expressed as a negative percentage)
 * - 'historicDays' defines how many past days to consider for historic average calculation
 */

// Configuration variables
var sheetUrl = 'SHEET_URL_HERE'; // TEMPLATE: https://docs.google.com/spreadsheets/d/1SQw40ip0kuM5JZ1AQdFqsKWRvQZILrSMw4oRbXIxCoM/
var emailAddress = "YOUR_EMAIL_ADDRESS_HERE"; // Your email address
var percentageThreshold = -0.8;  // Alerts when conversions decrease by 80% or more vs historic average
var historicDays = 4 // Number of past weeks to compare against (e.g., 4 means last 4 same weekdays)

function main() {
  // Initialize and validate sheets
  var spreadsheet = SpreadsheetApp.openByUrl(sheetUrl);
  var sheet = spreadsheet.getSheetByName('Report1');
  var dailySheet = spreadsheet.getSheetByName('Report2');
  
  if (!sheet) {
    sheet = spreadsheet.insertSheet('Report1');
  }
  if (!dailySheet) {
    dailySheet = spreadsheet.insertSheet('Report2');
  }
  
  Logger.log("Starting new run of the Conversions Issue Detector script.");
  sheet.clearContents();
  dailySheet.clearContents();
  Logger.log("Cleared the contents of the sheets to prepare for new data.");

  // Calculate dates for comparison
  var now = new Date();
  var currentBerlinTime = Utilities.formatDate(now, "Europe/Berlin", 'MMM dd, yyyy HH:mm:ss');
  var today = new Date(currentBerlinTime);
  today.setHours(0, 0, 0, 0);
  var yesterday = new Date(today);
  yesterday.setDate(today.getDate() - 1);
  var dateStringYesterday = Utilities.formatDate(yesterday, "Europe/Berlin", 'yyyyMMdd');
  var historicDates = getHistoricDates(yesterday);
  var lastRun = Utilities.formatDate(now, "Europe/Berlin", 'MMMM dd, yyyy HH:mm:ss');
  var historicDateStrings = historicDates.join(', ');

  // Initialize Report1 with summary information
  sheet.getRange("A1").setValue("Last run time:");
  sheet.getRange("B1").setValue(lastRun);
  sheet.getRange("A2").setValue("Yesterday's date:");
  sheet.getRange("B2").setValue(dateStringYesterday);
  sheet.getRange("A3").setValue("Historic dates:");
  sheet.getRange("B3").setValue(historicDateStrings);
  sheet.getRange("A4").setValue("Email Alert Sent:");
  sheet.getRange("B4").setValue("No"); // Will be updated if alert is sent

  // Set up Report1 headers
  var headers = ["Account ID", "Account Name", "Yesterday Conversions", "Historic Average", "Absolute Difference", "Percentage Difference", "30-Day Trend"];
  sheet.getRange("A6:G6").setValues([headers]);

  // Initialize variables for data collection
  var row = 7; // Start data from row 7
  var accountSelector = AdsManagerApp.accounts().withCondition('LabelNames CONTAINS "Conversions Issue Detector"');
  var accountIterator = accountSelector.get();
  var alertMessages = [];
  Logger.log("Fetching and calculating data for the accounts");

  // Prepare Report2 with last 30 days headers
  var dates = [];
  for (var i = 0; i < 30; i++) {
    var date = new Date(today);
    date.setDate(today.getDate() - i);
    dates.unshift(Utilities.formatDate(date, "Europe/Berlin", 'yyyy-MM-dd'));
  }
  
  var dailyHeaders = ["Account ID", "Account Name"].concat(dates);
  dailySheet.getRange(1, 1, 1, dailyHeaders.length).setValues([dailyHeaders]);
  var dailyRow = 2; // Start daily data from row 2

  // Process each labeled account
  while (accountIterator.hasNext()) {
    var account = accountIterator.next();
    AdsManagerApp.select(account);

    // Collect account data
    var accountId = account.getCustomerId();
    var accountName = account.getName();
    var conversionsYesterday = account.getStatsFor(dateStringYesterday, dateStringYesterday).getConversions();
    var totalConversions = 0;

    // Calculate historic average
    for (var i = 0; i < historicDates.length; i++) {
      var stats = account.getStatsFor(historicDates[i], historicDates[i]);
      totalConversions += stats.getConversions();
    }

    var averageHistoricConversions = totalConversions / historicDates.length;
    var difference = conversionsYesterday - averageHistoricConversions;
    var percentageDifference = (difference / averageHistoricConversions);

    // Update Report1 with account data
    Logger.log("Writing data for " + accountId);
    sheet.getRange(row, 1).setValue(accountId);
    sheet.getRange(row, 2).setValue(accountName);
    sheet.getRange(row, 3).setValue(conversionsYesterday);
    sheet.getRange(row, 4).setValue(averageHistoricConversions);
    sheet.getRange(row, 5).setValue(difference);
    sheet.getRange(row, 6).setValue(percentageDifference);
    
    // Add trend visualization from Report2 data
    var sparklineFormula = '=SPARKLINE(Report2!C' + dailyRow + ':AF' + dailyRow + ',{"charttype","line"})';
    sheet.getRange(row, 7).setFormula(sparklineFormula);

    // Highlight drops in red and create email text
    if (percentageDifference <= percentageThreshold) {
      sheet.getRange(row, 6, 1, 2).setFontColor('red');
      alertMessages.push(accountName + " (" + accountId + ") conversions are too low: " +
                        conversionsYesterday.toFixed(2) + " conversions yesterday, expecting at least " +
                        averageHistoricConversions.toFixed(2));
    }

    // Update Report2 with daily conversion data
    var dailyData = [accountId, accountName];
    for (var dateStr of dates) {
      var formattedDate = dateStr.replace(/-/g, '');
      var dailyConversions = account.getStatsFor(formattedDate, formattedDate).getConversions();
      dailyData.push(dailyConversions);
    }
    
    dailySheet.getRange(dailyRow, 1, 1, dailyData.length).setValues([dailyData]);
    dailyRow++;
    row++;
  }

  // Send email alerts if needed
  if (alertMessages.length > 0) {
    Logger.log("Sending alert email");
    var subject = "One or more Google Ads Accounts in the MCC misbehaved.";
    var body = alertMessages.join('\n\n');
    MailApp.sendEmail(emailAddress, subject, body);
    sheet.getRange("B4").setValue("Yes, to " + emailAddress);
  }
}

// Helper function to get historic dates for comparison
function getHistoricDates(baseDate) {
  var dates = [];
  for (var i = 1; i <= historicDays; i++) {
    var historicDate = new Date(baseDate.getFullYear(), baseDate.getMonth(), baseDate.getDate() - (7 * i));
    dates.push(Utilities.formatDate(historicDate, "Europe/Berlin", 'yyyyMMdd'));
  }
  return dates;
}

Loved it? Share it! 🙂

And if this was forwarded to you, and you’re not subscribed…