Campaign performance monitor script

				
					//------------------------------------------------------------------------------
// Developed by Robbert Smit ©
// www.robbertsmit.com
// hello@robbertsmit.com
//
// Campaign performance monitor script
// 
//
//   1. You can set either the script to either monitor ROAS for campaigns or CPA
//     in the configuration section.
//
//   2. You then configure LAST_X_DAYs, this is the most recent time period you want to use.
//      You then configure PREVIOUS_Y_DAYS, this is the time period in
//      which you want to compare it against.
//
//   3. The delta treshold is the difference you want to log. E.g. if I set it to
//      35 it means it will log all campaigns that have a 35% decrease
//      or increase when comparing LAST_X_DAYS to PREVIOUS_Y_DAYS
//
//   4. In addition, a SHIFT_DAYS configurator is included. By default, if I take the last 10 days,
//      it will include Today. If I set shift days to 1, it will take the last 10 days
//      not including today. If I set it to 2, it will take the last 10 days before today and yesterday etc.
//      The reason why you want to use this is for conversion maturity and is dependent on purchase cycle.
//      E.g. if all your conversions are usually matured at 5 days since last click, then you'd set it to 5 days
//      This is to make sure that you can compare fully matured traffic & conversion cohorts
//
//------------------------------------------------------------------------------

function main() {
  // Configuration
  var METRIC = "CPA"; // Set to "CPA" or "ROAS"
  var LAST_X_DAYS = 14; // Number of days for recent performance
  var PREVIOUS_Y_DAYS = 70; // Number of days for comparison period before the last X days
  var DELTA_THRESHOLD = 35; // Percentage change threshold
  var SHIFT_DAYS = 0; // Number of days to shift the last X days period backward
  var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/1B00Hq3bi_lqdcZwgejsUTR-sSLrry5WhwdUyGAp0NXw/edit?usp=sharing'; // Replace with your Google Sheet URL
  var EMAIL_ADDRESSES = ['robbertsmit.nl@gmail.com']; // Add your email addresses here

  // Open the spreadsheet and get the active sheet
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = spreadsheet.getActiveSheet();

  // Add headers to the sheet if it is empty
  if (sheet.getLastRow() === 0) {
    sheet.appendRow([
        "Date", 
        "Campaign Name", 
        "Metric", 
        "Last " + LAST_X_DAYS + " Days", 
        "Previous " + PREVIOUS_Y_DAYS + " Days", 
        "Delta (%)",
        "Last " + LAST_X_DAYS + " Days Period",
        "Previous " + PREVIOUS_Y_DAYS + " Days Period"
    ]);
  }

  // Get the date ranges
  var today = new Date();
  var logDate = Utilities.formatDate(today, AdsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');

  // Corrected date range calculation
  var lastXDaysRange = getCorrectedDateRange(today, LAST_X_DAYS, SHIFT_DAYS);
  var previousYDaysRange = getCorrectedDateRange(today, PREVIOUS_Y_DAYS + LAST_X_DAYS + SHIFT_DAYS, LAST_X_DAYS + SHIFT_DAYS);

  // Build queries with specific date ranges
  var lastXDaysQuery = buildQuery(lastXDaysRange.startDate, lastXDaysRange.endDate, METRIC);
  var previousYDaysQuery = buildQuery(previousYDaysRange.startDate, previousYDaysRange.endDate, METRIC);

  var lastXDaysReport = AdsApp.report(lastXDaysQuery);
  var previousYDaysReport = AdsApp.report(previousYDaysQuery);

  var lastXDaysRows = lastXDaysReport.rows();
  var previousYDaysRows = previousYDaysReport.rows();

  var campaignsLogged = 0;

  while (lastXDaysRows.hasNext()) {
    var lastXDaysRow = lastXDaysRows.next();
    var campaignName = lastXDaysRow['campaign.name'];

    var lastXDaysCost, lastXDaysConversions, lastXDaysConversionValue;
    var previousYDaysCost, previousYDaysConversions, previousYDaysConversionValue;

    if (METRIC === "CPA") {
      lastXDaysCost = parseFloat(lastXDaysRow['metrics.cost_micros'] || 0) / 1e6;
      lastXDaysConversions = parseFloat(lastXDaysRow['metrics.conversions'] || 0);
      var metricLastXDays = lastXDaysConversions > 0 ? lastXDaysCost / lastXDaysConversions : 0;

      var previousYDaysRow = previousYDaysRows.hasNext() ? previousYDaysRows.next() : null;
      previousYDaysCost = previousYDaysRow ? parseFloat(previousYDaysRow['metrics.cost_micros'] || 0) / 1e6 : 0;
      previousYDaysConversions = previousYDaysRow ? parseFloat(previousYDaysRow['metrics.conversions'] || 0) : 0;
      var metricPreviousYDays = previousYDaysConversions > 0 ? previousYDaysCost / previousYDaysConversions : 0;
    } else if (METRIC === "ROAS") {
      lastXDaysCost = parseFloat(lastXDaysRow['metrics.cost_micros'] || 0) / 1e6;
      lastXDaysConversionValue = parseFloat(lastXDaysRow['metrics.conversions_value'] || 0);
      var metricLastXDays = lastXDaysCost > 0 ? lastXDaysConversionValue / lastXDaysCost : 0;

      var previousYDaysRow = previousYDaysRows.hasNext() ? previousYDaysRows.next() : null;
      previousYDaysCost = previousYDaysRow ? parseFloat(previousYDaysRow['metrics.cost_micros'] || 0) / 1e6 : 0;
      previousYDaysConversionValue = previousYDaysRow ? parseFloat(previousYDaysRow['metrics.conversions_value'] || 0) : 0;
      var metricPreviousYDays = previousYDaysCost > 0 ? previousYDaysConversionValue / previousYDaysCost : 0;
    } else {
      Logger.log("Invalid METRIC value. Please use 'CPA' or 'ROAS'.");
      return;
    }

    // Use 0 as fallback if any value is undefined, then apply toFixed()
    var lastXDaysCostFormatted = lastXDaysCost ? lastXDaysCost.toFixed(2) : '0.00';
    var lastXDaysConversionValueFormatted = lastXDaysConversionValue ? lastXDaysConversionValue.toFixed(2) : '0.00';
    var previousYDaysCostFormatted = previousYDaysCost ? previousYDaysCost.toFixed(2) : '0.00';
    var previousYDaysConversionValueFormatted = previousYDaysConversionValue ? previousYDaysConversionValue.toFixed(2) : '0.00';

    // Log the cost, conversion value, and corrected date range for debugging
    Logger.log("Campaign: " + campaignName);
    Logger.log("Last X Days Date Range: " + lastXDaysRange.startDate + " to " + lastXDaysRange.endDate);
    Logger.log("Previous Y Days Date Range: " + previousYDaysRange.startDate + " to " + previousYDaysRange.endDate);
    Logger.log("Last " + LAST_X_DAYS + " Days Cost: " + lastXDaysCostFormatted);
    Logger.log("Last " + LAST_X_DAYS + " Days Conversion Value: " + lastXDaysConversionValueFormatted);
    Logger.log("Previous " + PREVIOUS_Y_DAYS + " Days Cost: " + previousYDaysCostFormatted);
    Logger.log("Previous " + PREVIOUS_Y_DAYS + " Days Conversion Value: " + previousYDaysConversionValueFormatted);
    
    // Calculate the delta (percentage change)
    var delta = ((metricLastXDays - metricPreviousYDays) / metricPreviousYDays) * 100;

    // Log campaigns where the delta exceeds the threshold
    if (Math.abs(delta) >= DELTA_THRESHOLD) {
      sheet.appendRow([
        logDate,
        campaignName,
        METRIC,
        metricLastXDays.toFixed(2),
        metricPreviousYDays.toFixed(2),
        delta.toFixed(2) + "%",
        lastXDaysRange.startDate + " to " + lastXDaysRange.endDate,
        previousYDaysRange.startDate + " to " + previousYDaysRange.endDate
      ]);
      campaignsLogged++;
    }
  }

  // Send a single email if any campaigns were logged
  if (campaignsLogged > 0) {
    var emailBody = "High performance variance in " + METRIC + " detected:\n\n";
    emailBody += "You can view the details in the spreadsheet: " + SPREADSHEET_URL;
    sendEmailNotification(emailBody, EMAIL_ADDRESSES);
  }

  // Log the number of entities written to the Google Ads logs
  Logger.log(campaignsLogged + " campaigns logged to the spreadsheet on " + logDate);
}

function getCorrectedDateRange(today, daysAgo, shiftDays) {
  // Subtract SHIFT_DAYS first
  var endDate = new Date(today);
  endDate.setDate(endDate.getDate() - shiftDays);

  // Then calculate the start date by subtracting LAST_X_DAYS from endDate
  var startDate = new Date(endDate);
  startDate.setDate(startDate.getDate() - daysAgo + 1);

  return {
    startDate: formatDate(startDate),
    endDate: formatDate(endDate)
  };
}

function buildQuery(startDate, endDate, metric) {
  var query = "SELECT campaign.name, ";
  if (metric === "CPA") {
    query += "metrics.cost_micros, metrics.conversions ";
  } else if (metric === "ROAS") {
    query += "metrics.cost_micros, metrics.conversions_value ";
  }
  query += "FROM campaign WHERE segments.date BETWEEN '" + startDate + "' AND '" + endDate + "'";
  query += " AND campaign.status = 'ENABLED'"; // Only enabled campaigns
  query += " AND metrics.impressions > 0"; // Only campaigns with more than 0 impressions
  return query;
}

function sendEmailNotification(emailBody, emailAddresses) {
  var subject = "High performance variance in CPA/ROAS detected";

  // Send email to all configured addresses
  for (var i = 0; i < emailAddresses.length; i++) {
    MailApp.sendEmail(emailAddresses[i], subject, emailBody);
  }
}

function formatDate(date) {
  var year = date.getFullYear();
  var month = (date.getMonth() + 1).toString().padStart(2, '0');
  var day = date.getDate().toString().padStart(2, '0');
  return year + month + day;
}