ILG Growth Playbook

/**
 * ILG – Daily spend & tracking guard (MCC script)
 * Runs across every account under the MCC once a day and emails ONE summary.
 * Report-only: changes nothing.
 *
 * Catches the problems found in the audit:
 *  - an account that suddenly stops spending (Orchard Wimbledon, Orchard Cambridge,
 *    St Christopher's went dark on 1 Sept and nobody noticed)
 *  - clicks with no conversions for several days (a broken tag or form)
 *  - a sudden spend spike (budget edits like the July wave)
 *
 * Install: Google Ads (MCC) > Tools > Bulk actions > Scripts > + > paste > Authorise >
 *          Preview > Schedule "Daily, 07:00".
 */
var EMAILS = 'admissions-marketing@example.com';   // comma-separated
var DARK_MIN_AVG_SPEND = 20;     // £/day average (prior 7 days) before a £0 day counts as "went dark"
var BLIND_MIN_CLICKS = 60;       // clicks in the last 3 days with 0 conversions = tracking alert
var SPIKE_RATIO = 1.6;           // yesterday vs prior-7-day average
var EXCLUDE_NAME = /camp|swim/i; // campaigns ignored when counting admissions conversions

function main() {
  var alerts = [];
  var accounts = AdsManagerApp.accounts().get();
  while (accounts.hasNext()) {
    var account = accounts.next();
    AdsManagerApp.select(account);
    var tz = AdsApp.currentAccount().getTimeZone();
    var days = lastNDates(8, tz);           // [d-8 ... d-1]
    var byDate = {};
    days.forEach(function (d) { byDate[d] = { cost: 0, clicks: 0, conv: 0 }; });

    var q = 'SELECT segments.date, campaign.name, metrics.cost_micros, metrics.clicks, metrics.conversions ' +
            'FROM campaign WHERE segments.date DURING LAST_14_DAYS AND metrics.impressions > 0';
    var rows = AdsApp.search(q);
    while (rows.hasNext()) {
      var r = rows.next();
      var d = r.segments.date;
      if (!byDate[d]) continue;
      byDate[d].cost += Number(r.metrics.costMicros) / 1e6;
      byDate[d].clicks += Number(r.metrics.clicks);
      if (!EXCLUDE_NAME.test(r.campaign.name)) byDate[d].conv += Number(r.metrics.conversions);
    }
    var y = byDate[days[7]];
    var prior = days.slice(0, 7).map(function (d) { return byDate[d]; });
    var avg = prior.reduce(function (s, x) { return s + x.cost; }, 0) / 7;
    var last3 = days.slice(5).map(function (d) { return byDate[d]; });
    var clicks3 = last3.reduce(function (s, x) { return s + x.clicks; }, 0);
    var conv3 = last3.reduce(function (s, x) { return s + x.conv; }, 0);
    var name = account.getName();

    if (avg >= DARK_MIN_AVG_SPEND && y.cost < 1) {
      alerts.push(['WENT DARK', name, 'Spent £0 yesterday vs £' + avg.toFixed(0) + '/day average. Check budgets, billing, paused campaigns.']);
    }
    if (clicks3 >= BLIND_MIN_CLICKS && conv3 === 0) {
      alerts.push(['NO CONVERSIONS', name, clicks3 + ' clicks in 3 days, 0 admissions conversions. Test-submit the form and check the tag.']);
    }
    if (avg > 0 && y.cost > avg * SPIKE_RATIO) {
      alerts.push(['SPEND SPIKE', name, '£' + y.cost.toFixed(0) + ' yesterday vs £' + avg.toFixed(0) + '/day average.']);
    }
  }
  if (alerts.length) {
    var html = '<p>' + alerts.length + ' issue(s) across ILG accounts:</p><table border="1" cellpadding="6" style="border-collapse:collapse">' +
      '<tr><th>Type</th><th>Account</th><th>Detail</th></tr>' +
      alerts.map(function (a) { return '<tr><td><b>' + a[0] + '</b></td><td>' + a[1] + '</td><td>' + a[2] + '</td></tr>'; }).join('') + '</table>';
    MailApp.sendEmail({ to: EMAILS, subject: 'ILG Google Ads – ' + alerts.length + ' alert(s)', htmlBody: html });
  }
  Logger.log(JSON.stringify(alerts));
}

function lastNDates(n, tz) {
  var out = [];
  for (var i = n; i >= 1; i--) {
    out.push(Utilities.formatDate(new Date(Date.now() - i * 86400000), tz, 'yyyy-MM-dd'));
  }
  return out;
}

/**
 * ILG – Weekly missed-call list per school (MCC script)
 * Sends each school's admissions contact the calls from its Google ads that were
 * MISSED in the last 7 days, plus a group summary to marketing.
 * Audit: 283 of 2,348 ad calls were missed (St Felix 55%, Wood Wharf 39%, Westbury 26%),
 * 207 of them during office hours.
 * Google only provides the caller's area code, not the full number, so the list
 * tells the school WHEN it lost calls and from WHERE - use it to fix phone cover.
 * Report-only. Schedule: Weekly, Monday 08:00.
 */
var MARKETING_EMAIL = 'admissions-marketing@example.com';
// Account name exactly as in Google Ads  ->  school admissions email
var SCHOOL_EMAILS = {
  'St. Felix School': 'admissions@example.com',
  'Wood Wharf Kindergarten': 'admissions@example.com',
  'Westbury House': 'admissions@example.com'
  // add the rest...
};

function main() {
  var summary = [];
  var accounts = AdsManagerApp.accounts().get();
  while (accounts.hasNext()) {
    var account = accounts.next();
    AdsManagerApp.select(account);
    var tz = AdsApp.currentAccount().getTimeZone();
    var since = Utilities.formatDate(new Date(Date.now() - 7 * 86400000), tz, 'yyyy-MM-dd HH:mm:ss');
    var q = 'SELECT call_view.start_call_date_time, call_view.call_status, call_view.call_duration_seconds, ' +
            'call_view.caller_area_code, campaign.name FROM call_view ' +
            "WHERE call_view.start_call_date_time >= '" + since + "'";
    var total = 0, missed = [];
    var rows = AdsApp.search(q);
    while (rows.hasNext()) {
      var row = rows.next();
      total++;
      if (row.callView.callStatus === 'MISSED') {
        missed.push([row.callView.startCallDateTime, row.callView.callerAreaCode || '', row.campaign.name]);
      }
    }
    if (!total) continue;
    var name = account.getName();
    summary.push([name, total, missed.length]);
    if (missed.length && SCHOOL_EMAILS[name]) {
      var html = '<p>' + missed.length + ' of ' + total + ' calls from your Google ads in the last 7 days were not answered.</p>' +
        '<table border="1" cellpadding="5" style="border-collapse:collapse"><tr><th>Time</th><th>Caller area code</th><th>Ad campaign</th></tr>' +
        missed.map(function (m) { return '<tr><td>' + m[0] + '</td><td>' + m[1] + '</td><td>' + m[2] + '</td></tr>'; }).join('') +
        '</table><p>Each missed call is a family who tried to reach you. Please check phone cover at these times.</p>';
      MailApp.sendEmail({ to: SCHOOL_EMAILS[name], cc: MARKETING_EMAIL, subject: name + ' – missed ad calls this week', htmlBody: html });
    }
  }
  summary.sort(function (a, b) { return b[2] / b[1] - a[2] / a[1]; });
  var body = '<table border="1" cellpadding="5" style="border-collapse:collapse"><tr><th>School</th><th>Calls</th><th>Missed</th><th>Missed %</th></tr>' +
    summary.map(function (s) { return '<tr><td>' + s[0] + '</td><td>' + s[1] + '</td><td>' + s[2] + '</td><td>' + Math.round(100 * s[2] / s[1]) + '%</td></tr>'; }).join('') + '</table>';
  MailApp.sendEmail({ to: MARKETING_EMAIL, subject: 'ILG – missed ad calls, last 7 days', htmlBody: body });
}

/**
 * ILG – Search-term guard (MCC script)
 * Last 30 days of search terms in every account, written to one Google Sheet with
 * three tabs:
 *   Blocklist   – free/funded-hours, term dates, jobs, holiday club, council, etc.
 *   Own brand   – the school's own name bought inside a NON-brand campaign
 *                 (audit: £8,308 in Jun–Aug vs £580 in the brand campaigns)
 *   Zero conv   – terms that cost more than MIN_COST with no conversions
 * APPLY = false  -> report only.
 * APPLY = true   -> adds each Blocklist term as an exact-match negative on the
 *                   campaign it came from. Review the sheet for a week before
 *                   switching this on.
 * Schedule: Weekly, Monday 06:00.
 */
var SHEET_URL = 'https://docs.google.com/spreadsheets/d/XXXX/edit'; // an empty sheet you own
var APPLY = false;
var MIN_COST = 40;
var BLOCK = /\b(free|funded|funding|15 hours|30 hours|tax[- ]free|term dates?|holiday club|jobs?|vacanc|careers?|salary|council|ofsted report|uniform|login|childminders?|apprentice)/i;
// Words that identify each school's own brand, by Google Ads account name
var BRAND = {
  'Westbury House': /westbury/i, 'Orchard Day Kingston': /orchard/i, 'Warlingham Park Prep': /warlingham/i,
  'Eton End': /eton end/i, 'Ruskin House Nursery': /ruskin/i, 'St. Felix School': /felix/i,
  'St Nicholas School': /nicholas/i, 'Sunninghill Prep School': /sunninghill/i, 'Orchard Day Wimbledon': /orchard/i,
  'Orchard Hampton Nursery': /orchard/i, 'Gidea Park': /gidea/i, 'GPPS': /grange park/i, 'The Study School': /\bstudy\b/i,
  'Yateley Manor School': /yateley manor|little lodge/i, 'Derby Grammar School': /derby grammar|little oaks/i,
  'Clayesmore Marketing': /clayesmore/i, 'Rookwood School': /rookwood/i, 'The Orchard Nursery - Kew': /orchard/i,
  'Collingwood School': /collingwood/i, 'Orchard Day Cambridge': /orchard/i, 'St. Francis Pewsey': /st francis/i,
  "St. Christopher's School": /christopher/i, 'Essendene Lodge': /essendene/i, 'Corner House Nursery': /corner house/i,
  'Wood Wharf Kindergarten': /wood wharf/i, 'St. Francis College': /st francis/i, 'Danesfield Manor School': /danesfield/i,
  'Walton Montessori': /walton montessori/i, 'Royal Hospital School': /royal hospital/i, 'Shebbear College': /shebbear/i
};

function main() {
  var ss = SpreadsheetApp.openByUrl(SHEET_URL);
  var out = { 'Blocklist': [], 'Own brand': [], 'Zero conv': [] };
  var accounts = AdsManagerApp.accounts().get();
  while (accounts.hasNext()) {
    var account = accounts.next();
    AdsManagerApp.select(account);
    var name = account.getName();
    var q = 'SELECT search_term_view.search_term, campaign.id, campaign.name, metrics.cost_micros, metrics.clicks, metrics.conversions ' +
            'FROM search_term_view WHERE segments.date DURING LAST_30_DAYS AND metrics.cost_micros > 0';
    var rows = AdsApp.search(q);
    var toNegate = {};
    while (rows.hasNext()) {
      var r = rows.next();
      var term = r.searchTermView.searchTerm, camp = r.campaign.name;
      var cost = Number(r.metrics.costMicros) / 1e6, conv = Number(r.metrics.conversions), clicks = Number(r.metrics.clicks);
      var line = [name, camp, term, Math.round(cost * 100) / 100, clicks, conv];
      if (/camp|swim/i.test(camp)) continue;
      if (BLOCK.test(term)) {
        out['Blocklist'].push(line);
        (toNegate[r.campaign.id] = toNegate[r.campaign.id] || []).push(term);
      } else if (BRAND[name] && BRAND[name].test(term) && !/brand/i.test(camp)) {
        out['Own brand'].push(line);
      } else if (conv === 0 && cost >= MIN_COST) {
        out['Zero conv'].push(line);
      }
    }
    if (APPLY) {
      Object.keys(toNegate).forEach(function (id) {
        var it = AdsApp.campaigns().withIds([Number(id)]).get();
        if (!it.hasNext()) return; // e.g. Performance Max – add these via a shared list instead
        var c = it.next();
        toNegate[id].forEach(function (t) { c.createNegativeKeyword('[' + t.replace(/[\[\]"]/g, '') + ']'); });
      });
    }
  }
  Object.keys(out).forEach(function (tab) {
    var sh = ss.getSheetByName(tab) || ss.insertSheet(tab);
    sh.clearContents();
    var data = [['Account', 'Campaign', 'Search term', 'Cost £', 'Clicks', 'Conversions']].concat(
      out[tab].sort(function (a, b) { return b[3] - a[3]; }));
    sh.getRange(1, 1, data.length, 6).setValues(data);
  });
  Logger.log('Blocklist ' + out['Blocklist'].length + ', own brand ' + out['Own brand'].length + ', zero conv ' + out['Zero conv'].length);
}

/**
 * ILG – Sister-school overlap (MCC script)
 * Finds towns where two or more ILG accounts are paying for the same searchers.
 * Audit example: New Malden – Westbury £1,446, Orchard Kingston £1,231,
 * The Study £828, Orchard Wimbledon £170 in Jun–Aug.
 * Writes one row per town with each school's spend and conversions, so the
 * cluster can decide which school "owns" the town and exclude it elsewhere.
 * Report-only. Schedule: Monthly, 1st, 06:00.
 */
var SHEET_URL = 'https://docs.google.com/spreadsheets/d/XXXX/edit';
var MIN_COST = 25; // ignore tiny spend per school per town

function main() {
  var towns = {}; // geoTargetConstants/123 -> {acct: [cost, conv]}
  var accounts = AdsManagerApp.accounts().get();
  while (accounts.hasNext()) {
    var account = accounts.next();
    AdsManagerApp.select(account);
    var q = 'SELECT segments.geo_target_city, metrics.cost_micros, metrics.conversions ' +
            'FROM geographic_view WHERE segments.date DURING LAST_30_DAYS AND metrics.cost_micros > 0';
    var rows = AdsApp.search(q);
    while (rows.hasNext()) {
      var r = rows.next();
      var city = r.segments.geoTargetCity;
      if (!city) continue;
      var t = towns[city] = towns[city] || {};
      var a = t[account.getName()] = t[account.getName()] || [0, 0];
      a[0] += Number(r.metrics.costMicros) / 1e6;
      a[1] += Number(r.metrics.conversions);
    }
  }
  var shared = Object.keys(towns).filter(function (c) {
    return Object.keys(towns[c]).filter(function (a) { return towns[c][a][0] >= MIN_COST; }).length >= 2;
  });
  var names = resolveNames(shared);
  var data = [['Town', 'ILG accounts', 'Total £', 'Breakdown (school: £ / conv)']];
  shared.forEach(function (c) {
    var accs = Object.keys(towns[c]).filter(function (a) { return towns[c][a][0] >= MIN_COST; })
      .sort(function (x, y) { return towns[c][y][0] - towns[c][x][0]; });
    var total = accs.reduce(function (s, a) { return s + towns[c][a][0]; }, 0);
    data.push([names[c] || c, accs.length, Math.round(total),
      accs.map(function (a) { return a + ': £' + Math.round(towns[c][a][0]) + ' / ' + towns[c][a][1].toFixed(1); }).join(' | ')]);
  });
  data = [data[0]].concat(data.slice(1).sort(function (a, b) { return b[2] - a[2]; }));
  var sh = SpreadsheetApp.openByUrl(SHEET_URL).getSheets()[0];
  sh.clearContents();
  sh.getRange(1, 1, data.length, 4).setValues(data);
}

function resolveNames(resourceNames) {
  var out = {};
  for (var i = 0; i < resourceNames.length; i += 200) {
    var chunk = resourceNames.slice(i, i + 200).map(function (r) { return "'" + r + "'"; }).join(',');
    if (!chunk) break;
    var rows = AdsApp.search('SELECT geo_target_constant.resource_name, geo_target_constant.name ' +
                             'FROM geo_target_constant WHERE geo_target_constant.resource_name IN (' + chunk + ')');
    while (rows.hasNext()) {
      var g = rows.next().geoTargetConstant;
      out[g.resourceName] = g.name;
    }
  }
  return out;
}

/**
 * ILG – Landing-page health check (MCC script)
 * Fetches every live final URL (ads and Performance Max asset groups) once a day
 * and emails anything broken. Audit findings it would have caught:
 *  - Orchard Hampton final URL with a second URL embedded ({ignore}?https://...)
 *  - Orchard Kingston "Book a tour" button pointing to ANOTHER school's Digistorm
 *  - template placeholders left on pages (IMAGE-URL-2, IMAGE-URL-3)
 *  - phone numbers that cannot be tapped on a phone
 * Report-only. Schedule: Daily, 06:30.
 */
var EMAILS = 'admissions-marketing@example.com';
// Optional: the Digistorm account code each Google Ads account should use.
// Any other *.app.digistorm.com code on its pages is flagged.
var DIGISTORM = {
  'Orchard Day Kingston': ['phps-gb-sry-1122'],
  'Orchard Hampton Nursery': ['odnh-gb-eng-1359'],
  'Ruskin House Nursery': ['rhn-gb-lnd-1123'],
  'St Nicholas School': ['snn-gb-lnd-1126']
  // add the rest from the audit's page survey
};

function main() {
  var issues = [];
  var seen = {};
  var accounts = AdsManagerApp.accounts().get();
  while (accounts.hasNext()) {
    var account = accounts.next();
    AdsManagerApp.select(account);
    var name = account.getName();
    var urls = {};
    collect(urls, "SELECT ad_group_ad.ad.final_urls FROM ad_group_ad WHERE ad_group_ad.status = 'ENABLED' " +
                  "AND ad_group.status = 'ENABLED' AND campaign.status = 'ENABLED'", function (r) { return r.adGroupAd.ad.finalUrls; });
    collect(urls, "SELECT asset_group.final_urls FROM asset_group WHERE asset_group.status = 'ENABLED' AND campaign.status = 'ENABLED'",
            function (r) { return r.assetGroup.finalUrls; });
    Object.keys(urls).forEach(function (raw) {
      if (/[?&=]https?:\/\/|https?:\/\/.*https?:\/\//i.test(raw)) issues.push([name, raw, 'Malformed final URL (a second URL inside it)']);
      var url = raw.replace(/\{ignore\}/gi, '').split('?')[0];
      if (seen[url]) return; seen[url] = true;
      var res;
      try { res = UrlFetchApp.fetch(url, { muteHttpExceptions: true, followRedirects: true }); }
      catch (e) { issues.push([name, url, 'Could not load: ' + e.message]); return; }
      var code = res.getResponseCode(), html = res.getContentText();
      if (code >= 400) { issues.push([name, url, 'HTTP ' + code]); return; }
      if (/IMAGE-URL-\d|lorem ipsum|\[school name\]/i.test(html)) issues.push([name, url, 'Template placeholder text on page']);
      var codes = (html.match(/[a-z0-9-]+(?=\.app\.digistorm\.com)/g) || []).filter(function (v, i, a) { return a.indexOf(v) === i; });
      if (!codes.length) issues.push([name, url, 'No Digistorm form or link found']);
      if (DIGISTORM[name]) {
        var wrong = codes.filter(function (c) { return DIGISTORM[name].indexOf(c) < 0; });
        if (wrong.length) issues.push([name, url, 'Links to another Digistorm account: ' + wrong.join(', ')]);
      }
      if (!/href="tel:|clkn\/tel\//i.test(html)) issues.push([name, url, 'No tap-to-call phone link']);
    });
  }
  if (issues.length) {
    MailApp.sendEmail({ to: EMAILS, subject: 'ILG landing pages – ' + issues.length + ' issue(s)',
      htmlBody: '<table border="1" cellpadding="5" style="border-collapse:collapse"><tr><th>Account</th><th>URL</th><th>Issue</th></tr>' +
        issues.map(function (i) { return '<tr><td>' + i[0] + '</td><td>' + i[1] + '</td><td>' + i[2] + '</td></tr>'; }).join('') + '</table>' });
  }
  Logger.log(issues.length + ' issues');
}

function collect(urls, query, pick) {
  var rows = AdsApp.search(query);
  while (rows.hasNext()) {
    (pick(rows.next()) || []).forEach(function (u) { urls[u] = true; });
  }
}

/**
 * ILG – Settings & conversion-goal audit (MCC script)
 * Lists, for every account, the settings the audit found leaking money or
 * corrupting bidding:
 *  - Search campaigns with Search Partners or Display network switched on
 *    (audit: £7,286 for 3.5 conversions)
 *  - Search campaigns on "Maximise clicks" (audit: £190 per conversion vs £116)
 *  - Location option "presence OR interest" instead of "presence"
 *  - Primary conversion actions that are not admissions enquiries
 *    (Book Now clicks, swim forms, calls, email clicks, prospectus)
 *  - More than one primary lead-form action (double counting)
 * APPLY_TABLET = true also sets a -100% tablet bid adjustment on enabled Search
 * campaigns (audit: £6,571 on tablets for 6 conversions). Default false.
 * Network settings and conversion goals cannot be changed by scripts – fix those
 * in the interface or Google Ads Editor using the sheet.
 * Schedule: Weekly.
 */
var SHEET_URL = 'https://docs.google.com/spreadsheets/d/XXXX/edit';
var APPLY_TABLET = false;
var NOT_ENQUIRY = /book now|swim|call|email|prospectus|camp|click/i;

function main() {
  var data = [['Account', 'Type', 'Item', 'Detail']];
  var accounts = AdsManagerApp.accounts().get();
  while (accounts.hasNext()) {
    var account = accounts.next();
    AdsManagerApp.select(account);
    var name = account.getName();

    var camps = AdsApp.search(
      'SELECT campaign.id, campaign.name, campaign.bidding_strategy_type, ' +
      'campaign.network_settings.target_search_network, campaign.network_settings.target_content_network, ' +
      'campaign.geo_target_type_setting.positive_geo_target_type ' +
      "FROM campaign WHERE campaign.status = 'ENABLED' AND campaign.advertising_channel_type = 'SEARCH'");
    while (camps.hasNext()) {
      var c = camps.next().campaign;
      if (c.networkSettings.targetSearchNetwork) data.push([name, 'Search Partners ON', c.name, 'Turn off in campaign settings']);
      if (c.networkSettings.targetContentNetwork) data.push([name, 'Display network ON', c.name, 'Turn off in campaign settings']);
      if (c.biddingStrategyType === 'TARGET_SPEND') data.push([name, 'Maximise clicks', c.name, 'Move to Maximise conversions once tracking is fixed']);
      if (c.geoTargetTypeSetting && c.geoTargetTypeSetting.positiveGeoTargetType === 'PRESENCE_OR_INTEREST')
        data.push([name, 'Location: presence or interest', c.name, 'Switch to "Presence" for nursery campaigns']);
      if (APPLY_TABLET) {
        var it = AdsApp.campaigns().withIds([Number(c.id)]).get();
        if (it.hasNext()) {
          var tab = it.next().targeting().platforms().tablet().get();
          if (tab.hasNext()) tab.next().setBidModifier(0);
        }
      }
    }

    var primaryForms = [];
    var conv = AdsApp.search(
      'SELECT conversion_action.name, conversion_action.category, conversion_action.primary_for_goal ' +
      "FROM conversion_action WHERE conversion_action.status = 'ENABLED'");
    while (conv.hasNext()) {
      var a = conv.next().conversionAction;
      if (!a.primaryForGoal) continue;
      if (NOT_ENQUIRY.test(a.name) || a.category === 'PHONE_CALL_LEAD' || a.category === 'CONTACT')
        data.push([name, 'Primary goal is not an enquiry', a.name, a.category + ' – set to Secondary, or give camps their own campaign goal']);
      else primaryForms.push(a.name);
    }
    if (primaryForms.length > 1)
      data.push([name, 'Several primary enquiry actions', primaryForms.join(' | '), 'Keep ONE primary Digistorm enquiry action; others Secondary']);
  }
  var sh = SpreadsheetApp.openByUrl(SHEET_URL).getSheets()[0];
  sh.clearContents();
  sh.getRange(1, 1, data.length, 4).setValues(data);
  Logger.log((data.length - 1) + ' items');
}