Google Ads - no spend alert (never miss out on dead accounts again)

What the Script Does:

1. What the Script Does
   
   • Looks at every client account linked to your MCC.
   • Totals Cost in the last 2 hours (or your set window) and the same 2-hour window exactly one week earlier.
   • Flags accounts that spent last week but are spending €0 now.
   • Sends a single HTML e-mail per day listing only those flagged accounts (Customer ID · Name · last-week spend).

How to Set Up the Script:

1. How to Set It Up
   
   1. Create a new MCC-level script
      Tools & Settings ▸ Scripts ▸ “+ New Script”.
   2. Paste the script from here to the Google Ads interface
   3. Configure
      • Update the RECIPIENTS list with your email address.
      • Optionally change:
        • HOURS_BACK (look-back window - increase if you have many small spend accounts)
        • CURRENCY_SYMBOL (display symbol in the email)
   4. Authorize & Preview
      • Authorize once so the script can read reports and send mail.
      • Click Preview; watch the Logs pane for live progress messages.
   5. Schedule
      • Set the frequency to Hourly
      • Thanks to the daily throttle, you’ll never get more than one email per day.
      • enjoy :)

// ----------------------------------------------------------------------------
//  MCC Spend‑Drop Monitor • v1.8 (2025‑04‑30)
// ----------------------------------------------------------------------------
//  NEW IN v1.8
//   • **Daily throttle** – sends at most 1 alert e‑mail per calendar day.
//     Uses ScriptProperties key `lastAlertDate` (YYYY‑MM‑DD) in account TZ.
//   • Logs when an alert is skipped because one was already sent today.
// ----------------------------------------------------------------------------

/** ============== CONFIG ============== */
var HOURS_BACK      = 2;                       // look‑back window (hours)
var RECIPIENTS      = ["yourmail"]; // e‑mail addresses
var CURRENCY_SYMBOL = "€";                     // cosmetic
var HEARTBEAT_MIN   = 1;                       // smallest log interval
var HEARTBEAT_MAX   = 5;                       // largest log interval
/** ===================================== */

function main () {
  var scriptStart = new Date();
  var tz    = AdsApp.currentAccount().getTimeZone();
  var now   = new Date();
  var todayKey = Utilities.formatDate(now, tz, "yyyy-MM-dd");

  // --- Daily send‑cap check -------------------------------------------------
  var props     = PropertiesService.getScriptProperties();
  var lastSent  = props.getProperty('lastAlertDate');
  var sentToday = (lastSent === todayKey);
  if (sentToday) {
    Logger.log("📨 Alert already sent today (%s); mail throttle active.", todayKey);
  }

  // ------------------------------------------------------------------------
  var from  = new Date(now.getTime() - HOURS_BACK * 60 * 60 * 1000);
  var fromLW = new Date(from.getTime() - 7 * 24 * 60 * 60 * 1000);
  var toLW   = new Date(now .getTime() - 7 * 24 * 60 * 60 * 1000);

  var thisHours = buildHourArray(from,  now,  tz);
  var prevHours = buildHourArray(fromLW, toLW, tz);

  var flaggedRows = [];

  var accIter   = MccApp.accounts().get();
  var totalAcc  = accIter.totalNumEntities();
  var heartbeat = Math.min(Math.max(Math.floor(totalAcc / 5), HEARTBEAT_MIN), HEARTBEAT_MAX);
  Logger.log("Scanning %s client accounts (heartbeat %s)…", totalAcc, heartbeat);

  var processed = 0;
  while (accIter.hasNext()) {
    var acc = accIter.next();
    MccApp.select(acc);

    var costNow  = safeFetchCost(thisHours);
    var costPrev = safeFetchCost(prevHours);

    if (costNow === 0 && costPrev > 0) {
      flaggedRows.push({ id: acc.getCustomerId(), name: acc.getName(), prev: costPrev.toFixed(2) });
    }

    processed++;
    if (processed % heartbeat === 0 || processed === totalAcc) {
      Logger.log("%s / %s checked – flagged so far: %s", processed, totalAcc, flaggedRows.length);
    }

    if ((new Date() - scriptStart) / 60000 > 25) {
      throw "⚠️ Exiting early to avoid timeout; reschedule sooner or split workload.";
    }
  }

  if (flaggedRows.length && !sentToday) {
    sendHtmlAlert(flaggedRows, from, now, tz);
    props.setProperty('lastAlertDate', todayKey);
  } else if (flaggedRows.length && sentToday) {
    Logger.log("⚠️ %s account(s) flagged, but email not sent due to daily limit", flaggedRows.length);
  } else {
    Logger.log("✅ No accounts flagged – no email needed");
  }
}

// -------------------------- helper functions remain unchanged -------------
function buildHourArray(start,end,tz){var s=Utilities.formatDate(start,tz,'yyyyMMdd')===Utilities.formatDate(end,tz,'yyyyMMdd');var r=[];if(s){r.push({date:Utilities.formatDate(start,tz,'yyyyMMdd'),hours:hr(start.getHours(),end.getHours())});}else{r.push({date:Utilities.formatDate(start,tz,'yyyyMMdd'),hours:hr(start.getHours(),24)});r.push({date:Utilities.formatDate(end,tz,'yyyyMMdd'),hours:hr(0,end.getHours())});}return r;}
function hr(s,e){var a=[];for(var i=s;i<e;i++)a.push(i);return a;}
function safeFetchCost(arr){try{return fetchCostUnits(arr);}catch(e){Logger.log('AWQL error: %s',e);throw e;}}
function fetchCostUnits(arr){var t=0;arr.forEach(function(d){if(!d.hours.length)return;var q="SELECT Cost, HourOfDay FROM ACCOUNT_PERFORMANCE_REPORT WHERE Date = '"+d.date+"' AND HourOfDay IN ["+d.hours.join(',')+"]";var rep=AdsApp.report(q);var rows=rep.rows();while(rows.hasNext()){t+=parseFloat(rows.next().Cost);}});return t;}
function sendHtmlAlert(rows,start,end,tz){var fmt=function(d){return Utilities.formatDate(d,tz,'yyyy-MM-dd HH:mm');};var subj='⚠️ '+rows.length+' account'+(rows.length!==1?'s':'')+' stopped spending • window '+fmt(start)+' → '+fmt(end);var table=rows.map(function(r){return '<tr><td style="padding:4px 8px;font-family:monospace;">'+r.id+'</td><td style="padding:4px 8px;">'+esc(r.name)+'</td><td style="padding:4px 8px;text-align:right;">'+CURRENCY_SYMBOL+r.prev+'</td></tr>';}).join('');var html='<html><body style="font-family:Arial,sans-serif"><p>Hi there,</p><p>The following account'+(rows.length>1?'s have':' has')+' registered <strong>€0 spend in the last '+HOURS_BACK+' hours</strong> but did spend during the same window last week:</p><table border="1" cellpadding="0" cellspacing="0" style="border-collapse:collapse"><thead><tr style="background:#f2f2f2"><th style="padding:6px 10px">Customer ID</th><th style="padding:6px 10px">Account Name</th><th style="padding:6px 10px">Spend Last Week</th></tr></thead><tbody>'+table+'</tbody></table><p style="margin-top:20px">Best,<br>Your Google Ads Script</p></body></html>';MailApp.sendEmail({to:RECIPIENTS.join(','),subject:subj,body:'(HTML email)',htmlBody:html});}
function esc(s){return s.replace(/[&<>"']/g,function(c){return {'&':'&amp;','<':'&lt;','>':'&gt;','"':'&quot;',"'":'&#39;'}[c];});}
// ----------------------------------------------------------------------------