Google Ads Health Checker

				
					/**
 * Ads Health Checker Script — PEMAVOR
 *
 * Author: Stefan Neefischer
 * Role: Co-Founder, Innovation & Strategy, PEMAVOR
 * Contact: info@pemavor.com
 * Hosted Tool: https://adshealthchecker.pemavor.com
 *
 * This script loops through all child accounts under your MCC,
 * pulls historical Quality Score sub-metrics (including Landing Page QS)
 * from Google Ads' keyword_view, aggregates counts by date, and writes
 * the results into a Google Sheet for easy visualization.
 **/

// ---------------------------------
// CONFIGURATION
// ---------------------------------

// 1) Replace with your Google Sheet ID (string between /d/ and /edit in the sheet URL).
const SPREADSHEET_ID = 'SPREADSHEET_ID'; // <<< IMPORTANT: Update this line!

// 2) Time window (in days) for historical data. 365 = one year of daily values.
const TIME_WINDOW = 365;

// 3) List of QS fields to retrieve. Add or remove fields as needed.
const SELECTED_FIELDS = [
  'metrics.historical_quality_score',
  'metrics.historical_creative_quality_score',
  'metrics.historical_landing_page_quality_score',
  'metrics.historical_search_predicted_ctr'
];

// Helper: Convert snake_case to camelCase
function snakeToCamel(s) {
  return s.replace(/_([a-z])/g, (g) => g[1].toUpperCase());
}

// Map: snake_case field → camelCase field
const FIELD_CAMELCASE_MAP = Object.fromEntries(
  SELECTED_FIELDS.map(f => [f, f.replace(/\.([a-z_]+)/g, (m, p1) => '.' + snakeToCamel(p1))])
);
// Example: 'metrics.historical_quality_score' → 'metrics.historicalQualityScore'

/**
 * MAIN FUNCTION
 * • Iterates through each sub-account in your MCC.
 * • Queries keyword_view for QS sub-metrics over the last TIME_WINDOW days.
 * • Aggregates daily counts for each QS category (e.g., BELOW_AVERAGE).
 * • Writes a summary table into the specified Google Sheet.
 */
function main() {
  // 1. Compute date range: from (today – TIME_WINDOW) to (today – 1).
  const today = new Date();
  today.setDate(today.getDate() - 1);   // Yesterday
  const startDate = new Date(today);
  startDate.setDate(today.getDate() - TIME_WINDOW + 1);

  const tz = AdsApp.currentAccount().getTimeZone();
  const dateFormat = 'yyyy-MM-dd';
  const startDateStr = Utilities.formatDate(startDate, tz, dateFormat);
  const endDateStr   = Utilities.formatDate(today, tz, dateFormat);

  Logger.log(`📅 Processing date range: ${startDateStr} → ${endDateStr}`);

  // 2. Container to accumulate daily QS category counts across all accounts.
  const summary = {};
  let accountsProcessed = 0;

  const accountIterator = MccApp.accounts().get();
  const totalAccounts = accountIterator.totalNumEntities();

  // 3. Loop through each sub-account under the MCC.
  while (accountIterator.hasNext()) {
    const account = accountIterator.next();
    MccApp.select(account);
    accountsProcessed++;
    Logger.log(`▶ Processing account ${accountsProcessed}/${totalAccounts} (ID: ${account.getCustomerId()})`);

    // 4. Build the GAQL query for keyword-level QS metrics over the defined date range.
    const query = `
      SELECT
        ad_group_criterion.keyword.text,
        segments.date,
        ${SELECTED_FIELDS.join(', ')}
      FROM keyword_view
      WHERE
        segments.date BETWEEN '${startDateStr}' AND '${endDateStr}'
        AND metrics.impressions >= 0
        AND campaign.advertising_channel_type = 'SEARCH'
    `;

    // 5. Run the query and process results.
    const rows = AdsApp.search(query);
    let rowCount = 0;

    while (rows.hasNext()) {
      const row = rows.next();
      const flatRow = flattenObject(row);
      const date = flatRow['segments.date'];
      if (!date) continue;

      // Initialize daily summary if needed.
      if (!summary[date]) {
        summary[date] = { activeElements: 0 };
      }

      summary[date].activeElements++;
      const daySummary = summary[date];

      // 6. Tally each QS category into daySummary, e.g.:
      //    metrics.historicalLandingPageQualityScore_BELOW_AVERAGE
      for (const [key, value] of Object.entries(flatRow)) {
        if (key === 'segments.date') continue;
        // Use camelCase field names for matching
        if (!Object.values(FIELD_CAMELCASE_MAP).some(field => key.startsWith(field))) continue;

        if (Array.isArray(value)) {
          // Some fields (like primary_status_reasons) are arrays; iterate through each element.
          value.forEach(v => {
            const columnKey = `${key}_${v}`;
            daySummary[columnKey] = (daySummary[columnKey] || 0) + 1;
          });
        } else {
          const columnKey = `${key}_${value}`;
          daySummary[columnKey] = (daySummary[columnKey] || 0) + 1;
        }
      }

      rowCount++;
    }

    Logger.log(`    ✔ Rows processed: ${rowCount}`);
    if (accountsProcessed % 5 === 0 || accountsProcessed === totalAccounts) {
      Logger.log(`--- Completed ${accountsProcessed}/${totalAccounts} accounts ---`);
    }
  }

  // 7. Build header row dynamically, based on all observed summary keys.
  const headersSet = new Set();
  for (const date in summary) {
    Object.keys(summary[date]).forEach(key => headersSet.add(key));
  }
  const headers = ['activeElements', ...Array.from(headersSet).filter(h => h !== 'activeElements').sort()];

  // 8. Write data into the Google Sheet.
  const sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getActiveSheet();
  sheet.clear();  // Clear any existing data
  sheet.appendRow(['Date', ...headers]);

  Object.keys(summary).sort().forEach(date => {
    const counts = headers.map(key => summary[date][key] || 0);
    sheet.appendRow([date, ...counts]);
  });

  Logger.log(`\n✅ Completed processing ${accountsProcessed} accounts.`);
  Logger.log('📄 Data written to Google Sheet successfully.');
}

/**
 * Recursively flattens a nested AdsApp row object into a dot-notated key→value map.
 * Example: { segments: { date: '2025-02-10' }, metrics: { ... } }
 * → { 'segments.date': '2025-02-10', 'metrics.metricName': value, ... }
 */
function flattenObject(obj, prefix = '') {
  const result = {};
  for (const key in obj) {
    if (!obj.hasOwnProperty(key)) continue;
    const val = obj[key];
    const fullKey = prefix ? `${prefix}.${key}` : key;

    if (val !== null && typeof val === 'object' && !Array.isArray(val)) {
      Object.assign(result, flattenObject(val, fullKey));
    } else {
      result[fullKey] = val;
    }
  }
  return result;
}