Conversions Issue Detector v1.2 (MCC)

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.

Author

Francesco Cifardi
August 09, 2024

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 v1.2 - 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.

How to Use It

  1. First, add a new label “Conversions Issue Detector“ to the accounts under your MCC that you want the script to monitor

  2. In your Google Ads account, create a new script and copy-paste the code below.

  3. Replace the 'SHEET_URL_HERE' placeholder inside the script with the URL of a blank Google Sheet.

  4. Schedule the script to run daily.

  5. Run the script. It will populate the first tab in your spreadsheet with the latest data.

NOTE: Make sure that at least one of your sheets is named “Sheet1“. The script will run into errors if your google account language is not in English. Sorry about that, will update this in the future!

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 ©
*
* 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.
*
* Scheduling:
* The script is designed to run once a day to provide more meaningful alerts and reduce the frequency of notifications.
*
* Config variables:
* 'sheetUrl' points to the Google Sheet where the data is written. Add a blank sheet, and make sure the sheet is called "Sheet1". The script won't work if you use Google Sheets in a different language than English

* '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.
*/

// Add a blank sheet url here, and make sure the sheet tab is called "Sheet1". The script won't work if you use Google Sheets in a different language than English
var sheetUrl = ''SHEET_URL_HERE'';
var emailAddress = "YOUR_EMAIL_ADDRESS_HERE"; // Your email address
var percentageThreshold = -0.8;  // -0.8 means an alert for accounts that had a decrease of 80% or more compared to the historic average
var historicDays = 4 // How many days in the past you want the script to look at


function main() {


 var sheet = SpreadsheetApp.openByUrl(sheetUrl).getSheetByName('Sheet1');
 Logger.log("Starting new run of the Conversions Issue Detector script.");
 sheet.clearContents();  // Clear the entire sheet content before writing new data
 Logger.log("Cleared the contents of the sheet to prepare for new data.");


 // Prepare the sheet: Clear previous content, set the introductory text, and define headers for data columns.
 sheet.getRange("B2").setValue("MCC Account Anomaly Detector (modified)");
 var headers = ["Account ID", "Account Name", "Yesterday Conversions", "Historic Average", "Absolute Difference", "Percentage Difference"];
 sheet.getRange("B3:G3").setValues([headers]);


 // Date stuff
 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); // Ensure the start of the day (midnight)
 var yesterday = new Date(today);
 yesterday.setDate(today.getDate() - 1);
 var dateStringYesterday = Utilities.formatDate(yesterday, "Europe/Berlin", 'yyyyMMdd');
 var historicDates = getHistoricDates(yesterday);


 var emailSent = false;
 var row = 4; // Start writing data from the fourth row
 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")


 // Fetch and process data for each account labeled as 'Conversions Issue Detector'.
 // Calculate the average historic conversions and compare them with yesterday's conversions.
 // Populate the sheet with data and check for significant drops in conversions.
 while (accountIterator.hasNext()) {
   var account = accountIterator.next();
   AdsManagerApp.select(account);


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


   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);


   // Writing Account ID, Account Name, and conversion data to the sheet
   Logger.log("Writing data for " + accountId)
   sheet.getRange(row, 2).setValue(accountId);
   sheet.getRange(row, 3).setValue(accountName);
   sheet.getRange(row, 4).setValue(conversionsYesterday);
   sheet.getRange(row, 5).setValue(averageHistoricConversions);
   sheet.getRange(row, 6).setValue(difference);
   sheet.getRange(row, 7).setValue(percentageDifference);


   if (percentageDifference <= percentageThreshold) {
     alertMessages.push(accountName + " (" + accountId + ") conversions are too low: " +
                        conversionsYesterday.toFixed(2) + " conversions yesterday, expecting at least " +
                        averageHistoricConversions.toFixed(2));
   }


   row++; // Move to the next row for the next account
 }


 // Leaving one empty row for spacing
 row++;


 // Summarize the script's run: Log the last run time, yesterday's date, and the dates considered for historic data.
 Logger.log("Writing summary")
 var lastRun = Utilities.formatDate(now, "Europe/Berlin", 'MMMM dd, yyyy HH:mm:ss');
 var historicDateStrings = historicDates.join(', ');


 sheet.getRange(row, 2).setValue("Last run time:");
 sheet.getRange(row, 3).setValue(lastRun);
 sheet.getRange(row + 1, 2).setValue("Yesterday's date:");
 sheet.getRange(row + 1, 3).setValue(dateStringYesterday);
 sheet.getRange(row + 2, 2).setValue("Historic dates:");
 sheet.getRange(row + 2, 3).setValue(historicDateStrings);


 // Send an alert email if any account's conversion drop meets or exceeds the threshold.
 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);
   emailSent = true;
   sheet.getRange(row + 3, 2).setValue("Email Alert Sent:");
   sheet.getRange(row + 3, 3).setValue("Yes, to " + emailAddress);
 } else {
   Logger.log("Finished with no alert")
   sheet.getRange(row + 3, 2).setValue("Email Alert Sent:");
   sheet.getRange(row + 3, 3).setValue("No");
 }
}


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;
}

FAQs

  • Where can I find all your scripts? I haven’t published them all yet because they are custom made. I need to adapt them for public use first. With that being said, on the home page of the newsletter you can find the Archive. (work in progress)

  • Can you make this xyz script for me? I can’t possibly accomodate everybody’s requests. I’m sure you can understand! 🙏 However you can submit your scripts ideas on this form, I’ll use those for future builds.

  • How can I give you feedback? You can simply reply to this email, I’ll soon create groups for beta testers, and anyone else that wants to join the community too!

Loved it? Share it! 🙂

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