Search / Performance Max cannibalisation

/**
 * Search / Performance Max cannibalisation
 * By Pierre Bordier — https://pierrebordier.com
 *
 * Performance Max and your Search campaigns compete for the same queries,
 * and Google gives you no report to see it. This script matches the search
 * term categories of your PMax campaigns against the search terms and
 * keywords of your Search campaigns, then lists the overlaps with the
 * performance on each side (clicks, cost, conversions, conversion rate,
 * CPA). Read-only: it changes nothing, it tells you where to look.
 *
 * INSTALLATION
 * 1. Google Ads > Tools and settings > Bulk actions > Scripts > "+".
 * 2. Paste this whole file, replace the values in the CONFIG block.
 * 3. Create an empty Google Sheet and copy its URL into SPREADSHEET_URL
 *    (the script overwrites two tabs: "Overlaps" and "PMax categories").
 * 4. "Preview" for a first test, then "Save".
 * 5. Schedule a weekly run.
 *
 * HOW TO READ THE OUTPUT
 * - One row = one PMax search category that overlaps Search search terms
 *   (same words, after removing stop words).
 * - "Identical keyword": an active Search keyword has exactly the same text
 *   as the category. If PMax still serves, the keyword is usually limited by
 *   budget or by a low Ad Rank, or the category aggregates variants the
 *   keyword does not cover.
 * - "Verdict": comparison of conversion rate and CPA on both sides. When
 *   Search converts better, PMax is costing you conversions; when PMax
 *   converts better, the Search keyword may be redundant.
 *
 * KNOWN LIMITS
 * - PMax only exposes categories (groups of queries), not exact queries: the
 *   matching is done on words and is indicative.
 * - Google sometimes returns partial metrics on some categories (impressions
 *   without clicks or cost) for privacy thresholds. PMax CPA then shows "n/a".
 * - Only Search terms with at least one click are analysed, to keep the run
 *   time reasonable on large accounts.
 * - PMax exclusions are set at account level (account-level negative keyword
 *   lists, brand exclusions): the script does not set them, it gives you the
 *   list.
 */

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

  // Recipients of the summary. [] to send nothing.
  EMAIL_RECIPIENTS: ['you@company.com'],

  // Period analysed: LAST_7_DAYS, LAST_14_DAYS, LAST_30_DAYS, LAST_MONTH...
  DATE_RANGE: 'LAST_30_DAYS',

  // Brand terms (lowercase). Any PMax category containing one is flagged
  // "Brand", even without a Search overlap.
  BRAND_TERMS: ['yourbrand'],

  // PMax categories below this number of clicks are ignored.
  MIN_PMAX_CLICKS: 5,

  // Number of rows in the email (the Sheet holds everything).
  EMAIL_TOP_N: 15,
};

// Stop words removed before comparison (French, English, German).
const STOPWORDS = (
  'le la les l de des du d un une et en pour a au aux sur avec dans par ou ' +
  'the a an of for in to and with on at or by ' +
  'der die das und fur mit von im in am zu oder'
).split(' ');

function main() {
  const spreadsheet = openConfiguredSpreadsheet_();
  const pmaxCampaigns = getCampaigns_('PERFORMANCE_MAX');
  if (pmaxCampaigns.length === 0) {
    Logger.log('No active Performance Max campaign. Exiting.');
    return;
  }

  Logger.log('Reading active Search keywords...');
  const keywordIndex = getSearchKeywords_();
  Logger.log('Reading Search search terms...');
  const searchTerms = getSearchTerms_();
  Logger.log(Object.keys(searchTerms).length + ' Search terms with clicks, ' + Object.keys(keywordIndex).length + ' active keywords.');

  const categories = [];
  pmaxCampaigns.forEach((campaign) => {
    try {
      categories.push(...getPmaxCategories_(campaign));
    } catch (e) {
      Logger.log('[PMax] failed for "' + campaign.name + '": ' + e);
    }
  });
  Logger.log(categories.length + ' PMax categories with at least ' + CONFIG.MIN_PMAX_CLICKS + ' clicks.');

  const overlaps = [];
  const categoryRows = [];
  categories.forEach((cat) => {
    const analysis = analyseCategory_(cat, searchTerms, keywordIndex);
    categoryRows.push([
      cat.campaign, cat.label, cat.impressions, cat.clicks, round_(cat.cost), round_(cat.conversions),
      analysis ? analysis.verdict : '',
    ]);
    if (analysis) overlaps.push(analysis);
  });

  overlaps.sort((a, b) => b.pmax.clicks - a.pmax.clicks);

  writeSheet_(spreadsheet, 'Overlaps', [
    'PMax campaign', 'PMax category', 'Brand', 'Identical keyword', 'Match type',
    'Search terms matched', 'Search term examples',
    'PMax impr.', 'PMax clicks', 'PMax cost', 'PMax conv.', 'PMax conv. rate', 'PMax CPA',
    'Search impr.', 'Search clicks', 'Search cost', 'Search conv.', 'Search conv. rate', 'Search CPA',
    'Verdict', 'Suggestion',
  ], overlaps.map(overlapToRow_));

  writeSheet_(spreadsheet, 'PMax categories', [
    'Campaign', 'Category', 'Impressions', 'Clicks', 'Cost', 'Conversions', 'Verdict',
  ], categoryRows);

  sendSummaryEmail_(spreadsheet, pmaxCampaigns, categories, overlaps);
  Logger.log('Done. ' + overlaps.length + ' overlap(s). Sheet: ' + spreadsheet.getUrl());
}

// ---------- Reading the account ----------

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

// Active Search keywords, indexed by normalised text.
function getSearchKeywords_() {
  const index = {};
  const rows = AdsApp.search(`
    SELECT ad_group_criterion.keyword.text, ad_group_criterion.keyword.match_type,
           campaign.name, ad_group.name
    FROM keyword_view
    WHERE campaign.advertising_channel_type = 'SEARCH'
      AND campaign.status = 'ENABLED'
      AND ad_group.status = 'ENABLED'
      AND ad_group_criterion.status = 'ENABLED'
      AND ad_group_criterion.negative = FALSE
  `);
  while (rows.hasNext()) {
    const row = rows.next();
    const kw = row.adGroupCriterion.keyword;
    const key = normalise_(kw.text).join(' ');
    if (!key) continue;
    if (!index[key]) index[key] = [];
    index[key].push({ text: kw.text, matchType: kw.matchType, campaign: row.campaign.name, adGroup: row.adGroup.name });
  }
  return index;
}

// Search search terms with at least one click, aggregated by normalised text.
function getSearchTerms_() {
  const terms = {};
  const rows = AdsApp.search(`
    SELECT search_term_view.search_term, campaign.name,
           metrics.impressions, metrics.clicks, metrics.cost_micros,
           metrics.conversions, metrics.conversions_value
    FROM search_term_view
    WHERE campaign.advertising_channel_type = 'SEARCH'
      AND campaign.status = 'ENABLED'
      AND segments.date DURING ${CONFIG.DATE_RANGE}
      AND metrics.clicks > 0
  `);
  while (rows.hasNext()) {
    const row = rows.next();
    const raw = row.searchTermView.searchTerm;
    const tokens = normalise_(raw);
    if (tokens.length === 0) continue;
    const key = tokens.join(' ');
    if (!terms[key]) {
      terms[key] = { text: raw, tokens: tokens, impressions: 0, clicks: 0, cost: 0, conversions: 0, value: 0, campaigns: {} };
    }
    const t = terms[key];
    t.impressions += row.metrics.impressions || 0;
    t.clicks += row.metrics.clicks || 0;
    t.cost += microsToUnits_(row.metrics.costMicros);
    t.conversions += row.metrics.conversions || 0;
    t.value += row.metrics.conversionsValue || 0;
    t.campaigns[row.campaign.name] = true;
  }
  return terms;
}

// This resource requires a filter on ONE campaign at a time.
function getPmaxCategories_(campaign) {
  const out = [];
  const rows = AdsApp.search(`
    SELECT campaign_search_term_insight.category_label,
           metrics.impressions, metrics.clicks, metrics.cost_micros,
           metrics.conversions, metrics.conversions_value
    FROM campaign_search_term_insight
    WHERE campaign_search_term_insight.campaign_id = ${campaign.id}
      AND segments.date DURING ${CONFIG.DATE_RANGE}
    ORDER BY metrics.clicks DESC
  `);
  while (rows.hasNext()) {
    const row = rows.next();
    const clicks = row.metrics.clicks || 0;
    if (clicks < CONFIG.MIN_PMAX_CLICKS) continue;
    const label = row.campaignSearchTermInsight.categoryLabel || '';
    if (!label) continue;
    out.push({
      campaign: campaign.name,
      label: label,
      tokens: normalise_(label),
      impressions: row.metrics.impressions || 0,
      clicks: clicks,
      cost: microsToUnits_(row.metrics.costMicros),
      conversions: row.metrics.conversions || 0,
      value: row.metrics.conversionsValue || 0,
    });
  }
  return out;
}

// ---------- Matching ----------

function analyseCategory_(cat, searchTerms, keywordIndex) {
  if (cat.tokens.length === 0) return null;
  const key = cat.tokens.join(' ');

  const brand = CONFIG.BRAND_TERMS.some((b) => b && cat.label.toLowerCase().indexOf(b.toLowerCase()) !== -1);
  const identical = keywordIndex[key] || [];

  const matched = [];
  Object.keys(searchTerms).forEach((termKey) => {
    const term = searchTerms[termKey];
    if (tokensOverlap_(cat.tokens, term.tokens)) matched.push(term);
  });

  if (!brand && matched.length === 0 && identical.length === 0) return null;

  const search = { impressions: 0, clicks: 0, cost: 0, conversions: 0, value: 0 };
  matched.forEach((t) => {
    search.impressions += t.impressions;
    search.clicks += t.clicks;
    search.cost += t.cost;
    search.conversions += t.conversions;
    search.value += t.value;
  });
  matched.sort((a, b) => b.clicks - a.clicks);

  const pmaxRate = cat.clicks ? cat.conversions / cat.clicks : 0;
  const searchRate = search.clicks ? search.conversions / search.clicks : 0;

  let verdict;
  let hint;
  if (brand) {
    verdict = 'Brand in PMax';
    hint = 'Add the brand to the PMax campaign brand exclusions and leave it to Search.';
  } else if (matched.length === 0) {
    verdict = 'Identical keyword with no Search clicks';
    hint = 'The keyword exists but gets nothing: check budget, Ad Rank and ad group status.';
  } else if (search.clicks >= 10 && searchRate > pmaxRate * 1.2) {
    verdict = 'Search converts better';
    hint = 'Exclude these terms from PMax (account-level negative list) and keep the Search keyword.';
  } else if (cat.clicks >= 10 && pmaxRate > searchRate * 1.2) {
    verdict = 'PMax converts better';
    hint = 'Check the ads and landing page of the Search keyword, or leave it to PMax.';
  } else {
    verdict = 'Duplicate, similar performance';
    hint = 'Pick a single channel for these terms to simplify attribution.';
  }

  return {
    pmax: cat,
    brand: brand,
    identical: identical,
    matched: matched,
    search: search,
    pmaxRate: pmaxRate,
    searchRate: searchRate,
    verdict: verdict,
    hint: hint,
  };
}

// Two token sets overlap when one contains the other (at least one meaningful
// word in common, and every word of the shorter set present in the longer).
function tokensOverlap_(a, b) {
  const short = a.length <= b.length ? a : b;
  const long = a.length <= b.length ? b : a;
  if (short.length === 0) return false;
  return short.every((t) => long.indexOf(t) !== -1);
}

function normalise_(text) {
  if (!text) return [];
  return text
    .toLowerCase()
    .normalize('NFD')
    .replace(/[̀-ͯ]/g, '')
    .replace(/[^a-z0-9\s]/g, ' ')
    .split(/\s+/)
    .filter((t) => t && STOPWORDS.indexOf(t) === -1);
}

// ---------- Output ----------

function overlapToRow_(o) {
  const c = o.pmax;
  const s = o.search;
  return [
    c.campaign, c.label, o.brand ? 'yes' : '',
    o.identical.length ? 'yes' : '',
    o.identical.map((k) => k.matchType).filter(uniq_).join(', '),
    o.matched.length,
    o.matched.slice(0, 3).map((t) => t.text).join(' | '),
    c.impressions, c.clicks, round_(c.cost), round_(c.conversions), pct_(o.pmaxRate), cpa_(c.cost, c.conversions),
    s.impressions, s.clicks, round_(s.cost), round_(s.conversions), pct_(o.searchRate), cpa_(s.cost, s.conversions),
    o.verdict, o.hint,
  ];
}

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, categories, overlaps) {
  if (!CONFIG.EMAIL_RECIPIENTS || CONFIG.EMAIL_RECIPIENTS.length === 0) return;
  const account = AdsApp.currentAccount();
  const currency = account.getCurrencyCode();
  const today = new Date().toLocaleDateString('en-GB');

  const pmaxClicksInOverlap = overlaps.reduce((sum, o) => sum + o.pmax.clicks, 0);
  const pmaxCostInOverlap = overlaps.reduce((sum, o) => sum + o.pmax.cost, 0);
  const brandCount = overlaps.filter((o) => o.brand).length;
  const searchBetter = overlaps.filter((o) => o.verdict === 'Search converts better').length;

  let body = 'Search / PMax cannibalisation — ' + today + '\n';
  body += 'Account: ' + account.getName() + ' (' + account.getCustomerId() + ')\n';
  body += 'Period: ' + CONFIG.DATE_RANGE + '\n\n';
  body += campaigns.length + ' PMax campaign(s), ' + categories.length + ' categories analysed\n';
  body += overlaps.length + ' categories overlap Search terms: ' + pmaxClicksInOverlap + ' PMax clicks, ' + round_(pmaxCostInOverlap) + ' ' + currency + '\n';
  body += brandCount + ' brand categories in PMax, ' + searchBetter + ' categories where Search converts better\n\n';

  if (overlaps.length > 0) {
    body += 'Top ' + Math.min(CONFIG.EMAIL_TOP_N, overlaps.length) + ' (by PMax clicks):\n';
    overlaps.slice(0, CONFIG.EMAIL_TOP_N).forEach((o) => {
      body += '- ' + o.pmax.label + ': PMax ' + o.pmax.clicks + ' clicks / ' + pct_(o.pmaxRate) + ' conv.'
        + ' vs Search ' + o.search.clicks + ' clicks / ' + pct_(o.searchRate) + ' conv.'
        + ' -> ' + o.verdict + (o.identical.length ? ' (identical keyword)' : '') + '\n';
    });
    body += '\n';
  }
  body += 'Full detail: ' + spreadsheet.getUrl();

  MailApp.sendEmail({
    to: CONFIG.EMAIL_RECIPIENTS.join(','),
    subject: 'Search / PMax cannibalisation - ' + today + ' - ' + overlaps.length + ' overlap(s)',
    body: body,
  });
}

// ---------- Helpers ----------

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

function microsToUnits_(micros) {
  return micros ? micros / 1e6 : 0;
}

function round_(n) {
  return Math.round((n || 0) * 100) / 100;
}

function pct_(rate) {
  return (Math.round(rate * 1000) / 10) + '%';
}

function cpa_(cost, conversions) {
  if (!conversions) return 'n/a';
  if (!cost) return 'n/a';
  return round_(cost / conversions);
}

function uniq_(value, index, arr) {
  return arr.indexOf(value) === index;
}