Cannibalisation Search / Performance Max

/**
 * Cannibalisation Search / Performance Max
 * Par Pierre Bordier — https://pierrebordier.com
 *
 * Performance Max et vos campagnes Search se disputent les mêmes requêtes,
 * et Google ne fournit aucun rapport pour le voir. Ce script croise les
 * catégories de termes de recherche de vos campagnes PMax avec les termes
 * de recherche et les mots-clés de vos campagnes Search, puis liste les
 * chevauchements avec la performance de chaque côté (clics, coût,
 * conversions, taux de conversion, CPA). Lecture seule : il ne modifie
 * rien, il vous dit où regarder.
 *
 * INSTALLATION
 * 1. Google Ads > Outils et paramètres > Actions groupées > Scripts > "+".
 * 2. Collez tout ce fichier, remplacez les valeurs du bloc CONFIG.
 * 3. Créez un Google Sheet vide et copiez son URL dans SPREADSHEET_URL
 *    (le script écrase deux onglets : "Chevauchements" et "Catégories PMax").
 * 4. "Aperçu" pour un premier test, puis "Enregistrer".
 * 5. Planifiez une exécution hebdomadaire.
 *
 * COMMENT LIRE LE RÉSULTAT
 * - Une ligne = une catégorie de recherche PMax qui recoupe des termes de
 *   recherche Search (mêmes mots, après retrait des mots vides).
 * - "Mot-clé identique" : un mot-clé Search actif a exactement le même
 *   texte que la catégorie. Si PMax diffuse quand même, c'est en général que
 *   le mot-clé est limité par le budget, par un Ad Rank trop faible, ou que
 *   la catégorie agrège des variantes que le mot-clé ne couvre pas.
 * - "Verdict" : comparaison du taux de conversion et du CPA des deux côtés.
 *   Quand Search convertit mieux, PMax vous coûte des conversions ; quand
 *   PMax convertit mieux, le mot-clé Search fait peut-être doublon.
 *
 * LIMITES CONNUES
 * - PMax ne donne que des catégories (groupes de requêtes), pas des requêtes
 *   exactes : le rapprochement se fait sur les mots, il est indicatif.
 * - Google renvoie parfois des métriques partielles sur certaines catégories
 *   (impressions sans clics ni coût) par seuil de confidentialité. Le CPA
 *   PMax est alors affiché "n/d".
 * - Seuls les termes Search ayant reçu au moins un clic sont analysés, pour
 *   garder un temps d'exécution raisonnable sur les gros comptes.
 * - Les exclusions PMax se posent au niveau du compte (listes de mots-clés
 *   négatifs au niveau du compte, exclusions de marque) : le script ne les
 *   pose pas, il vous donne la liste.
 */

const CONFIG = {
  // URL d'un Google Sheet VIDE créé au préalable.
  SPREADSHEET_URL: 'https://docs.google.com/spreadsheets/d/VOTRE_ID_ICI/edit',

  // Adresses destinataires du résumé. [] pour ne rien envoyer.
  EMAIL_RECIPIENTS: ['vous@exemple.com'],

  // Période analysée : LAST_7_DAYS, LAST_14_DAYS, LAST_30_DAYS, LAST_MONTH...
  DATE_RANGE: 'LAST_30_DAYS',

  // Termes de marque (en minuscules). Toute catégorie PMax qui en contient un
  // est signalée "Marque", même sans chevauchement avec Search.
  BRAND_TERMS: ['votremarque'],

  // Catégories PMax ignorées en dessous de ce nombre de clics.
  MIN_PMAX_CLICKS: 5,

  // Nombre de lignes dans l'e-mail (le Sheet contient tout).
  EMAIL_TOP_N: 15,
};

// Mots vides retirés avant comparaison (français, anglais, allemand).
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('Aucune campagne Performance Max active. Fin du script.');
    return;
  }

  Logger.log('Lecture des mots-clés Search actifs...');
  const keywordIndex = getSearchKeywords_();
  Logger.log('Lecture des termes de recherche Search...');
  const searchTerms = getSearchTerms_();
  Logger.log(Object.keys(searchTerms).length + ' termes Search avec clics, ' + Object.keys(keywordIndex).length + ' mots-clés actifs.');

  const categories = [];
  pmaxCampaigns.forEach((campaign) => {
    try {
      categories.push(...getPmaxCategories_(campaign));
    } catch (e) {
      Logger.log('[PMax] échec pour "' + campaign.name + '" : ' + e);
    }
  });
  Logger.log(categories.length + ' catégories PMax avec au moins ' + CONFIG.MIN_PMAX_CLICKS + ' clics.');

  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, 'Chevauchements', [
    'Campagne PMax', 'Catégorie PMax', 'Marque', 'Mot-clé identique', 'Type de correspondance',
    'Termes Search recoupés', 'Exemples de termes Search',
    'PMax impr.', 'PMax clics', 'PMax coût', 'PMax conv.', 'PMax taux conv.', 'PMax CPA',
    'Search impr.', 'Search clics', 'Search coût', 'Search conv.', 'Search taux conv.', 'Search CPA',
    'Verdict', 'Piste',
  ], overlaps.map(overlapToRow_));

  writeSheet_(spreadsheet, 'Catégories PMax', [
    'Campagne', 'Catégorie', 'Impressions', 'Clics', 'Coût', 'Conversions', 'Verdict',
  ], categoryRows);

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

// ---------- Lecture du compte ----------

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;
}

// Mots-clés Search actifs, indexés par texte normalisé.
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;
}

// Termes de recherche Search avec au moins un clic, agrégés par texte normalisé.
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;
}

// Cette ressource exige un filtre sur UNE campagne à la fois.
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;
}

// ---------- Rapprochement ----------

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 = 'Marque dans PMax';
    hint = 'Ajouter la marque aux exclusions de marque de la campagne PMax et la laisser à Search.';
  } else if (matched.length === 0) {
    verdict = 'Mot-clé identique sans clic Search';
    hint = 'Le mot-clé existe mais ne reçoit rien : vérifier budget, Ad Rank et statut du groupe.';
  } else if (search.clicks >= 10 && searchRate > pmaxRate * 1.2) {
    verdict = 'Search convertit mieux';
    hint = 'Exclure ces termes de PMax (liste de négatifs au niveau du compte) et garder le mot-clé Search.';
  } else if (cat.clicks >= 10 && pmaxRate > searchRate * 1.2) {
    verdict = 'PMax convertit mieux';
    hint = 'Vérifier annonces et page du mot-clé Search, ou le laisser à PMax.';
  } else {
    verdict = 'Doublon, performance équivalente';
    hint = 'Choisir un seul canal pour ces termes afin de simplifier l’attribution.';
  }

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

// Deux jeux de mots se recoupent si l'un contient l'autre (au moins un mot
// significatif en commun, et tous les mots du plus court présents dans le
// plus long).
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);
}

// ---------- Sorties ----------

function overlapToRow_(o) {
  const c = o.pmax;
  const s = o.search;
  return [
    c.campaign, c.label, o.brand ? 'oui' : '',
    o.identical.length ? 'oui' : '',
    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('fr-CH');

  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 convertit mieux').length;

  let body = 'Cannibalisation Search / PMax — ' + today + '\n';
  body += 'Compte : ' + account.getName() + ' (' + account.getCustomerId() + ')\n';
  body += 'Période : ' + CONFIG.DATE_RANGE + '\n\n';
  body += campaigns.length + ' campagne(s) PMax, ' + categories.length + ' catégorie(s) analysée(s)\n';
  body += overlaps.length + ' catégorie(s) recoupent des termes Search : ' + pmaxClicksInOverlap + ' clics PMax, ' + round_(pmaxCostInOverlap) + ' ' + currency + '\n';
  body += brandCount + ' catégorie(s) de marque dans PMax, ' + searchBetter + ' catégorie(s) où Search convertit mieux\n\n';

  if (overlaps.length > 0) {
    body += 'Top ' + Math.min(CONFIG.EMAIL_TOP_N, overlaps.length) + ' (par clics PMax) :\n';
    overlaps.slice(0, CONFIG.EMAIL_TOP_N).forEach((o) => {
      body += '- ' + o.pmax.label + ' : PMax ' + o.pmax.clicks + ' clics / ' + pct_(o.pmaxRate) + ' conv.'
        + ' vs Search ' + o.search.clicks + ' clics / ' + pct_(o.searchRate) + ' conv.'
        + ' -> ' + o.verdict + (o.identical.length ? ' (mot-clé identique)' : '') + '\n';
    });
    body += '\n';
  }
  body += 'Détail complet : ' + spreadsheet.getUrl();

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

// ---------- Utilitaires ----------

function openConfiguredSpreadsheet_() {
  if (!CONFIG.SPREADSHEET_URL || CONFIG.SPREADSHEET_URL.indexOf('VOTRE_ID_ICI') !== -1) {
    throw new Error("CONFIG.SPREADSHEET_URL n'est pas configuré. Créez un Google Sheet vide et copiez son URL en haut du 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/d';
  if (!cost) return 'n/d';
  return round_(cost / conversions);
}

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