What do you think about this script?

Joined
Aug 11, 2023
Messages
1
Reaction score
0
Hello,

I created this script for Google Ads budget tracking.

Code:
//
// Created: 11-08-2023
//
// ABOUT THE SCRIPT
// The script will alert you via email if the spent budget from the current month
// is 20% higher than the average spent budget of the last 6 months
// or 50% lower than the average spent budget of the last 6 months
// The script takes in consideration only the months where budget > 0
// If all previous 6 months have 0 spent budget, then the script
// will display a message in the logs of Google Ads platform
//
////////////////////////////////////////////////////////////////////

function main() {
  var currentMonthSpend = getMonthlySpend("THIS_MONTH");
  var averageLastSixMonthsSpend = getAverageSpendLastSixMonths();

  // Email addresses to send alerts
  var emailAddresses = [
    "EMAIL1",
    "EMAIL2"
  ];

  // Check if the current month's spend is 20% higher than the average
  if (currentMonthSpend > averageLastSixMonthsSpend * 1.20) {
    var subject = "Overbudget Alert for " + AdsApp.currentAccount().getName();
    var body = "The current month's spend is 20% higher than the average of the last 6 months.";
    sendEmailAlert(emailAddresses.join(','), subject, body);
  }

  // Check if the current month's spend is 50% lower than the average
  if (currentMonthSpend < averageLastSixMonthsSpend * 0.50) {
    var subject = "Underbudget Alert for " + AdsApp.currentAccount().getName();
    var body = "The current month's spend is 50% lower than the average of the last 6 months.";
    sendEmailAlert(emailAddresses.join(','), subject, body);
  }
}

function getMonthlySpend(dateRange) {
  var report = AdsApp.report(
    "SELECT Cost " +
    "FROM CAMPAIGN_PERFORMANCE_REPORT " +
    "DURING " + dateRange
  );
  var rows = report.rows();
  var totalSpend = 0;
  while (rows.hasNext()) {
    var row = rows.next();
    totalSpend += parseFloat(row["Cost"]);
  }
  return totalSpend;
}

function getAverageSpendLastSixMonths() {
  var totalSpend = 0;
  var activeMonthsCount = 0;

  for (var i = 1; i <= 6; i++) {
    var monthlySpend = getMonthlySpend("LAST_" + i + "_MONTHS, LAST_" + (i - 1) + "_MONTHS");
    if (monthlySpend > 0) {
      totalSpend += monthlySpend;
      activeMonthsCount++;
    }
  }

  // Check if all 6 previous months have 0 budget
  if (activeMonthsCount == 0) {
    Logger.log("All 6 previous months have 0 budget.");
    return 0; // Return 0 to prevent division by zero
  }

  return totalSpend / activeMonthsCount;
}

function sendEmailAlert(email, subject, body) {
  MailApp.sendEmail({
    to: email,
    subject: subject,
    body: body
  });
}

What do you think, it will work?

Thank you!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,787
Messages
2,569,630
Members
45,334
Latest member
66Zeinab9

Latest Threads

Top