Performance Max search terms

/**
 * PMax Insights & Placements Report
 * By Pierre Bordier — https://pierrebordier.com
 *
 * Performance Max gives almost no native visibility on the search terms and on the
 * Display/YouTube placements its campaigns consume. This script reads both reports
 * (read-only, no change to the account) and publishes them to a Google Sheet, with a
 * summary sent by email.
 *
 * INSTALLATION
 * 1. Google Ads > Tools and settings > Bulk actions > Scripts > "+".
 * 2. Paste this whole file, replace SPREADSHEET_URL and EMAIL_RECIPIENTS below with your
 *    own values.
 * 3. First create an EMPTY Google Sheet, copy its URL into SPREADSHEET_URL (the script
 *    creates/overwrites two tabs in it: "Search terms" and "Placements" — do not reuse a
 *    Sheet that holds data you want to keep).
 * 4. "Preview" for a first test, then "Save".
 * 5. Schedule it (clock icon): weekly is a good default.
 *
 * KNOWN LIMITS
 * - "campaign_search_term_insight" is a fairly recent Google Ads API resource: Google may
 *   return partial metrics (impressions only, no clicks/cost) on some categories, for
 *   privacy thresholds. That is normal on Google's side, not a script bug.
 * - If Google changes the fields available on "performance_max_placement_view" or
 *   "campaign_search_term_insight", this script may throw a one-off "field not compatible"
 *   error. Check the current schema at
 *   https://developers.google.com/google-ads/api/fields/latest/overview and adjust the
 *   fields of the matching SELECT — each report is isolated in its own try/catch, so an
 *   error on one does not stop the other.
 */

const CONFIG = {
  // Paste here the URL of an EMPTY Google Sheet you created beforehand.
  SPREADSHEET_URL: 'https://docs.google.com/spreadsheets/d/VOTRE_ID_ICI/edit',

  // One or more addresses, comma-separated. Leave [] to disable the email
  // (the Sheet is still updated).
  EMAIL_RECIPIENTS: ['you@example.com'],

  // Period analysed on each run. Possible values: LAST_7_DAYS,
  // LAST_14_DAYS, LAST_30_DAYS, LAST_MONTH, THIS_MONTH...
  DATE_RANGE: 'LAST_30_DAYS',

  // Number of categories shown in the email summary (the Sheet always
  // holds the full list).
  EMAIL_TOP_N: 10,
};

function main() {
  const spreadsheet = openConfiguredSpreadsheet_();
  const campaigns = getPerformanceMaxCampaigns_();

  if (campaigns.length === 0) {
    Logger.log('No active Performance Max campaign found on this account. Done.');
    return;
  }
  Logger.log(campaigns.length + ' Performance Max campaign(s) found: ' + campaigns.map((c) => c.name).join(', '));

  const searchTermRows = [];
  const placementRows = [];

  campaigns.forEach((campaign) => {
    try {
      searchTermRows.push(...getSearchTermInsights_(campaign));
    } catch (e) {
      Logger.log('[Search terms] failed for campaign "' + campaign.name + '": ' + e);
    }
    try {
      placementRows.push(...getPlacementInsights_(campaign));
    } catch (e) {
      Logger.log('[Placements] failed for campaign "' + campaign.name + '": ' + e);
    }
  });

  writeSheet_(spreadsheet, 'Search terms', [
    'Campaign', 'Search category', 'Impressions', 'Clicks', 'Conversions', 'Conv. value', 'Cost',
  ], searchTermRows);

  writeSheet_(spreadsheet, 'Placements', [
    'Campaign', 'Type', 'Placement', 'Impressions', 'Clicks', 'Cost',
  ], placementRows);

  sendSummaryEmail_(spreadsheet, campaigns, searchTermRows, placementRows);

  Logger.log('Done. ' + searchTermRows.length + ' search term rows, ' + placementRows.length + ' placement rows. Sheet: ' + spreadsheet.getUrl());
}

function openConfiguredSpreadsheet_() {
  if (!CONFIG.SPREADSHEET_URL || CONFIG.SPREADSHEET_URL.indexOf('VOTRE_ID_ICI') !== -1) {
    throw new Error(
      'CONFIG.SPREADSHEET_URL is not set. Create an empty Google Sheet and copy its URL at the top of this script.',
    );
  }
  return SpreadsheetApp.openByUrl(CONFIG.SPREADSHEET_URL);
}

function getPerformanceMaxCampaigns_() {
  const campaigns = [];
  const rows = AdsApp.search(`
    SELECT campaign.id, campaign.name
    FROM campaign
    WHERE campaign.advertising_channel_type = 'PERFORMANCE_MAX'
      AND campaign.status = 'ENABLED'
  `);
  while (rows.hasNext()) {
    const row = rows.next();
    campaigns.push({ id: row.campaign.id, name: row.campaign.name });
  }
  return campaigns;
}

// "campaign_search_term_insight" requires filtering on ONE campaign at a time
// (a Google Ads API constraint for this resource): we therefore loop campaign
// by campaign rather than fetching everything in one query.
function getSearchTermInsights_(campaign) {
  const rows = AdsApp.search(`
    SELECT
      campaign_search_term_insight.category_label,
      metrics.impressions,
      metrics.clicks,
      metrics.conversions,
      metrics.conversions_value,
      metrics.cost_micros
    FROM campaign_search_term_insight
    WHERE campaign_search_term_insight.campaign_id = ${campaign.id}
      AND segments.date DURING ${CONFIG.DATE_RANGE}
    ORDER BY metrics.impressions DESC
  `);
  const out = [];
  while (rows.hasNext()) {
    const row = rows.next();
    out.push([
      campaign.name,
      row.campaignSearchTermInsight.categoryLabel || '(uncategorised)',
      row.metrics.impressions || 0,
      row.metrics.clicks || 0,
      row.metrics.conversions || 0,
      row.metrics.conversionsValue || 0,
      microsToUnits_(row.metrics.costMicros),
    ]);
  }
  return out;
}

function getPlacementInsights_(campaign) {
  const rows = AdsApp.search(`
    SELECT
      performance_max_placement_view.placement_type,
      performance_max_placement_view.display_name,
      performance_max_placement_view.target_url,
      metrics.impressions,
      metrics.clicks,
      metrics.cost_micros
    FROM performance_max_placement_view
    WHERE campaign.id = ${campaign.id}
      AND segments.date DURING ${CONFIG.DATE_RANGE}
    ORDER BY metrics.impressions DESC
  `);
  const out = [];
  while (rows.hasNext()) {
    const row = rows.next();
    const view = row.performanceMaxPlacementView;
    out.push([
      campaign.name,
      view.placementType || '',
      view.displayName || view.targetUrl || '(unknown)',
      row.metrics.impressions || 0,
      row.metrics.clicks || 0,
      microsToUnits_(row.metrics.costMicros),
    ]);
  }
  return out;
}

function microsToUnits_(micros) {
  return micros ? Math.round((micros / 1e6) * 100) / 100 : 0;
}

function writeSheet_(spreadsheet, sheetName, headers, rows) {
  let sheet = spreadsheet.getSheetByName(sheetName);
  if (!sheet) sheet = spreadsheet.insertSheet(sheetName);
  sheet.clearContents();
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]).setFontWeight('bold');
  sheet.setFrozenRows(1);
  if (rows.length > 0) {
    sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
  }
  sheet.autoResizeColumns(1, headers.length);
}

function sendSummaryEmail_(spreadsheet, campaigns, searchTermRows, placementRows) {
  if (!CONFIG.EMAIL_RECIPIENTS || CONFIG.EMAIL_RECIPIENTS.length === 0) return;

  const currency = AdsApp.currentAccount().getCurrencyCode();
  const topCategories = searchTermRows.slice(0, CONFIG.EMAIL_TOP_N);
  const today = new Date().toLocaleDateString('en-GB');

  let body = 'PMax Insights report — ' + today + '\n';
  body += 'Account: ' + AdsApp.currentAccount().getName() + ' (' + AdsApp.currentAccount().getCustomerId() + ')\n';
  body += 'Period: ' + CONFIG.DATE_RANGE + '\n\n';
  body += campaigns.length + ' Performance Max campaign(s) analysed\n';
  body += searchTermRows.length + ' search term category row(s)\n';
  body += placementRows.length + ' placement(s) found\n\n';

  if (topCategories.length > 0) {
    body += 'Top ' + topCategories.length + ' search term categories (by impressions):\n';
    topCategories.forEach((row) => {
      body += '- ' + row[1] + ' (' + row[0] + ') : ' + row[2] + ' impr., ' + row[3] + ' clicks, ' + row[6] + ' ' + currency + '\n';
    });
    body += '\n';
  }

  body += 'Full detail: ' + spreadsheet.getUrl();

  MailApp.sendEmail({
    to: CONFIG.EMAIL_RECIPIENTS.join(','),
    subject: 'PMax Insights report - ' + today,
    body: body,
  });
}