/**
 *
 * Conversion Drop Alert 
 *
 * Description:
 * This script runs daily, checks if yesterday's conversion performance is
 * significantly lower than the historical average for the same day of the
 * week, based on standard deviation. 
 * Sends an email alert if a significant drop is detected.
 *
 * Version: 1.0 
 * Author: Nils Rooijmans (C)
 *
 * For the MCC version that runs on Google Ads manager accounts and checks all client accounts contact nils@nilsrooijmans.com 
 */

// ~~~~~~~~~~~~~~~~~~~ Configuration ~~~~~~~~~~~~~~~~~~~
const CONFIG = {
  // --- Email Settings ---
  // Comma-separated list of email addresses to notify.
  RECIPIENT_EMAILS: ["your_email@example.com"], // <<< --- !!! CHANGE THIS !!!

  // --- Historical Data Settings ---
  // Number of past weeks to include when calculating the historical average & std dev.
  // For example, 8 means looking at the last 8 Mondays (if yesterday was a Monday).
  WEEKS_FOR_AVERAGE: 8,

  // --- Alert Thresholds ---
  // Number of standard deviations below the historical average to trigger an alert.
  // Common values are 2.0 or 3.0. A higher value means drops need to be more extreme.
  STD_DEV_THRESHOLD: 2, 

  // Optional: Minimum number of *total* historical conversions required over the lookback period.
  // This prevents alerts based on averages calculated from very low conversion volume,
  // even if the standard deviation is calculable. Set to 0 to disable this check.
  MIN_HISTORICAL_CONVERSIONS_TOTAL: 16,

};
// ~~~~~~~~~~~~~~~~~ End Configuration ~~~~~~~~~~~~~~~~~~~

// DO NOT CHANGE ANYHTING BELOW THIS LINE

function main() {
  // --- Configuration Validation ---
  if (!CONFIG.RECIPIENT_EMAILS || CONFIG.RECIPIENT_EMAILS.length === 0 || CONFIG.RECIPIENT_EMAILS[0] === "your_email@example.com") {
    Logger.log("ERROR: Please configure the RECIPIENT_EMAILS variable in the script.");
    return;
  }
  if (CONFIG.WEEKS_FOR_AVERAGE <= 3) {
     Logger.log("ERROR: WEEKS_FOR_AVERAGE must be greater than 3 for meaningful standard deviation.");
     return;
  }
  if (CONFIG.STD_DEV_THRESHOLD <= 0) {
     Logger.log("ERROR: STD_DEV_THRESHOLD must be greater than 0.");
     return;
  }

  // --- Basic Setup ---
  const accountName = AdsApp.currentAccount().getName();
  const accountId = AdsApp.currentAccount().getCustomerId();
  const timeZone = AdsApp.currentAccount().getTimeZone();

  // --- Date Calculations ---
  const yesterday = new Date();
  yesterday.setDate(yesterday.getDate() - 1); // Set date to yesterday

  const yesterdayFormattedGAQL = Utilities.formatDate(yesterday, timeZone, "yyyy-MM-dd");
  const dayOfWeek = Utilities.formatDate(yesterday, timeZone, "EEEE").toUpperCase(); // e.g., MONDAY

  Logger.log(`Account: ${accountName} (${accountId})`);
  Logger.log(`Checking conversions for yesterday: ${yesterdayFormattedGAQL} (${dayOfWeek})`);
  Logger.log(`Using historical data from the past ${CONFIG.WEEKS_FOR_AVERAGE} weeks for ${dayOfWeek}s.`);
  Logger.log(`Alert threshold: alert if number of conversions is below ( Mean - (${CONFIG.STD_DEV_THRESHOLD} * Standard Deviation) )`);

  // --- Fetch Yesterday's Conversions ---
  const yesterdayConversions = getConversionsForDate(yesterdayFormattedGAQL);
  Logger.log(`Yesterday's conversions: ${yesterdayConversions.toFixed(2)}`);


  // --- Fetch Historical Conversions (List of Values) ---
  const historyEndDate = new Date(yesterday);
  historyEndDate.setDate(historyEndDate.getDate() - 1); // End day *before* yesterday
  const historyStartDate = new Date(historyEndDate);
  historyStartDate.setDate(historyStartDate.getDate() - (CONFIG.WEEKS_FOR_AVERAGE * 7) + 1); // Go back N weeks

  const historyStartDateFormattedGAQL = Utilities.formatDate(historyStartDate, timeZone, "yyyy-MM-dd");
  const historyEndDateFormattedGAQL = Utilities.formatDate(historyEndDate, timeZone, "yyyy-MM-dd");

  Logger.log(`Fetching historical data for ${dayOfWeek}s between ${historyStartDateFormattedGAQL} and ${historyEndDateFormattedGAQL}`);

  const historicalData = getHistoricalConversionValues(historyStartDateFormattedGAQL, historyEndDateFormattedGAQL, dayOfWeek);

  // --- Calculate Mean, Standard Deviation, and Compare ---
  if (historicalData.dataPoints === 0) {
    Logger.log("No historical data found for this day of the week in the specified range. Cannot perform comparison.");
    return;
  }

  Logger.log(`Historical Data Points (${dayOfWeek}s found): ${historicalData.dataPoints}`);
  Logger.log(`Total Historical Conversions: ${historicalData.totalConversions.toFixed(2)}`);
  // Log the actual values for debugging if needed
  Logger.log(`Historical Values: [${historicalData.historicalValues.map(v => v.toFixed(2)).join(', ')}]`);

  // Check minimum thresholds before proceeding with calculations
   if (CONFIG.MIN_HISTORICAL_CONVERSIONS_TOTAL > 0 && historicalData.totalConversions < CONFIG.MIN_HISTORICAL_CONVERSIONS_TOTAL) {
       Logger.log(`Skipping alert check: Total historical conversions (${historicalData.totalConversions.toFixed(2)}) is below the minimum threshold (${CONFIG.MIN_HISTORICAL_CONVERSIONS_TOTAL}).`);
       return;
   }

  // Calculate statistics
  const historicalMean = historicalData.totalConversions / historicalData.dataPoints;
  const standardDeviation = calculateStandardDeviation(historicalData.historicalValues);

  // Calculate the alert threshold (ensure it doesn't go below zero)
  const alertThresholdValue = Math.max(0, historicalMean - (CONFIG.STD_DEV_THRESHOLD * standardDeviation));

  Logger.log(`Historical Mean Conversions: ${historicalMean.toFixed(2)}`);
  Logger.log(`Historical Standard Deviation: ${standardDeviation.toFixed(2)}`);
  Logger.log(`Alert Threshold (${CONFIG.STD_DEV_THRESHOLD} std dev below mean): ${alertThresholdValue.toFixed(2)}`);

  // --- Check Condition and Send Alert ---
  if (yesterdayConversions < alertThresholdValue) {
    Logger.log(`ALERT: Yesterday's conversions (${yesterdayConversions.toFixed(2)}) are significantly lower than expected based on historical performance (Mean: ${historicalMean.toFixed(2)}, StdDev: ${standardDeviation.toFixed(2)}).`);
    sendAlertEmailStdDev(
      accountName,
      accountId,
      yesterdayFormattedGAQL,
      dayOfWeek,
      yesterdayConversions,
      historicalMean,
      standardDeviation,
      historicalData.dataPoints,
      alertThresholdValue
    );
  } else {
    Logger.log("Yesterday's conversions are within the acceptable range (based on standard deviation) compared to the historical average. No alert needed.");
  }

  Logger.log("Script finished.");
}

/**
 * Calculates the sample standard deviation of an array of numbers.
 * @param {number[]} data Array of numbers.
 * @return {number} The sample standard deviation. Returns 0 if fewer than 2 data points.
 */
function calculateStandardDeviation(data) {
  const n = data.length;
  if (n < 2) {
    return 0; // Standard deviation requires at least 2 data points
  }

  const mean = data.reduce((a, b) => a + b, 0) / n;
  const variance = data.reduce((a, b) => a + Math.pow(b - mean, 2), 0) / (n - 1); // Use (n-1) for sample standard deviation
  return Math.sqrt(variance);
}


/**
 * Fetches the total number of conversions for a specific date using GAQL.
 * Queries the campaign resource and sums conversions across all campaigns.
 * @param {string} dateFormatted The date in 'yyyy-MM-dd' format.
 * @return {number} The total number of conversions for that date.
 */
function getConversionsForDate(dateFormatted) {
  let totalConversions = 0;
  let whereClauses = [`segments.date = '${dateFormatted}'`, `metrics.conversions > 0`];

  const query = `
    SELECT metrics.conversions
    FROM campaign
    WHERE ${whereClauses.join(' AND ')}
  `;

  Logger.log("Executing GAQL Query for single date:\n" + query);
  try {
    const report = AdsApp.search(query);
    while (report.hasNext()) {
      const row = report.next();
      totalConversions += row.metrics.conversions;
    }
  } catch (e) {
      Logger.log(`ERROR executing GAQL query for single date: ${e}\nQuery:\n${query}`);
  }
  return totalConversions;
}


/**
 * Fetches historical conversion values for a specific day of the week within a date range using GAQL.
 * Returns the list of daily conversion totals for that day of the week.
 * @param {string} startDateFormatted Start date in 'yyyy-MM-dd' format.
 * @param {string} endDateFormatted End date in 'yyyy-MM-dd' format.
 * @param {string} dayOfWeek Day of the week (uppercase, e.g., "MONDAY").
 * @return {{historicalValues: number[], dataPoints: number, totalConversions: number}}
 *         An object containing the array of daily conversion values, the count of days, and the total sum.
 */
function getHistoricalConversionValues(startDateFormatted, endDateFormatted, dayOfWeek) {
  const dailyConversionTotals = {}; // Object to store conversions summed per day { 'YYYY-MM-DD': totalConv, ... }

  let whereClauses = [
    `segments.date BETWEEN '${startDateFormatted}' AND '${endDateFormatted}'`,
    `segments.day_of_week = ${dayOfWeek}`
  ];

  // Need segments.date to aggregate by day, and segments.day_of_week because it's in WHERE
  const query = `
    SELECT
      metrics.conversions,
      segments.date,
      segments.day_of_week  
    FROM campaign
    WHERE ${whereClauses.join(' AND ')}
    ORDER BY segments.date 
  `;

  Logger.log("Executing GAQL Query for historical data:\n" + query);

  let totalHistoricalConversions = 0;

  try {
      const report = AdsApp.search(query);
      while (report.hasNext()) {
          const row = report.next();
          const currentDate = row.segments.date;
          const conversions = row.metrics.conversions;

          // Initialize the date key if it doesn't exist
          if (!dailyConversionTotals[currentDate]) {
              dailyConversionTotals[currentDate] = 0;
          }
          // Add conversions for this row (could be from a specific campaign/conversion type)
          dailyConversionTotals[currentDate] += conversions;
          totalHistoricalConversions += conversions; // Keep track of overall total as well
      }
  } catch (e) {
      Logger.log(`ERROR executing GAQL query for historical data: ${e}\nQuery:\n${query}`);
      return { historicalValues: [], dataPoints: 0, totalConversions: 0 }; // Return empty on error
  }

  // Extract the daily totals into an array
  const historicalValues = Object.values(dailyConversionTotals);

  return {
    historicalValues: historicalValues,
    dataPoints: historicalValues.length, // Number of unique days with data (including zeros if returned)
    totalConversions: totalHistoricalConversions
  };
}


function sendAlertEmailStdDev(accountName, accountId, yesterdayDate, dayOfWeek, yesterdayConversions, historicalMean, standardDeviation, dataPoints, alertThresholdValue) {
  const subject = `[Google Ads] ### ALERT ### Significant Conversion Drop Detected for ${accountName}`;

  try {
      let body = `A significant conversion drop was detected in your Google Ads account based on historical standard deviation.\n\n`;
      body += `Account:\n\t ${accountName} (${accountId})\n\n`;
      body += `Date Checked (yesterday):\n\t ${yesterdayDate} (${dayOfWeek})\n\n`;
      body += `Yesterday's Conversions: ${yesterdayConversions.toFixed(2)}\n`;
      body += `Historical Mean (${dataPoints} prior ${dayOfWeek}s): ${historicalMean.toFixed(2)}\n`;
      body += `Historical Standard Deviation: ${standardDeviation.toFixed(2)}\n`;
      body += `Alert Threshold (Mean - ${CONFIG.STD_DEV_THRESHOLD} * StdDev): ${alertThresholdValue.toFixed(2)} conversions\n\n`;
      body += `\nPlease investigate the account for potential causes (e.g., budget issues, tracking problems, bid changes, seasonality, competition, website issues).\n\n`;
      body += `----------------------------------------------------------------------------\nThis email is generated by the Google Ads Script "Conversion Drop Alert", (C) Nils Rooijmans.\n`;
      body += `This script runs daily, checks if yesterday's conversion performance is significantly lower than the historical average for the same day of the week, based on standard deviation.\n`;  
      body += `If a significant drop is detected, an alert is sent via email.\n\n`;
      body += `For the MCC version that runs on Google Ads manager accounts and checks all client accounts contact nils@nilsrooijmans.com\n`;


      MailApp.sendEmail({
          to: CONFIG.RECIPIENT_EMAILS.join(','),
          subject: subject,
          body: body,
      });
      Logger.log(`Alert email successfully sent to: ${CONFIG.RECIPIENT_EMAILS.join(',')}`);

  } catch (e) {
      Logger.log(`ERROR sending email: ${e}`);
      Logger.log(`Failed email data: Account=${accountName}(${accountId}), Date=${yesterdayDate}, Yesterday=${yesterdayConversions.toFixed(2)}, Mean=${historicalMean.toFixed(2)}, StdDev=${standardDeviation.toFixed(2)}, Threshold=${alertThresholdValue.toFixed(2)}`);
  }
}