Compares search terms from PMax to Shopping and Search campaigns to detect overlap

Find PMax Overlapping Queries

const SETTINGS = {
    SHEET_URL:"https://docs.google.com/spreadsheets/d/1mi6dG2N-qHPvmtYkA6AzFzI4BcnopDBennlAOw6iwk8/copy",
    DAYS: 7,
    CAMPAIGN_STATUS: 'ENABLED',// ENABLED | PAUSED | ALL
    CAMPAIGN_NAME_INCLUDE: "",
    CAMPAIGN_NAME_EXCLUDE: "",
    CAMPAIGN_LABEL_INCLUDE: "",
    CAMPAIGN_IDS: [],
    MIN_CLICKS: 1,
    MIN_IMPRESSIONS: 0,
    MIN_CONVERSIONS: 0,
    MIN_COST: 0
}


function main() {
    const campaigns = getCampaigns()
    const stats = getStats(campaigns)
    updateSpreadsheet("Search↔Shopping",buildOverlapRows(stats.search, stats.shopping))
    updateSpreadsheet("Search↔PMax",buildOverlapRows(stats.search, stats.pmax))
    updateSpreadsheet("Shopping↔PMax",buildOverlapRows(stats.shopping, stats.pmax))
    updateSpreadsheet("Search↔Shopping↔PMax",buildOverlapRows(stats.search, stats.shopping, stats.pmax))
}

function getCampaigns() {
    let campaigns = {search: [], shopping: [], pmax: []}
    const statusQuery = ` AND campaign.status IN (${SETTINGS.CAMPAIGN_STATUS == 'ALL' ? `ENABLED, PAUSED` : SETTINGS.CAMPAIGN_STATUS}) `
    const idsQuery = SETTINGS.CAMPAIGN_IDS.length == 0 ? "" : ` AND campaign.id IN (${SETTINGS.CAMPAIGN_IDS.map(i => `"${i}"`).join(", ")}) `
    const labelQuery = SETTINGS.CAMPAIGN_LABEL_INCLUDE == "" ? "" : ` AND campaign.labels CONTAINS ANY ("${getLabelId(SETTINGS.CAMPAIGN_LABEL_INCLUDE)}")`
    const query = `SELECT campaign.id,
                          campaign.advertising_channel_type,
                          campaign.name,
                          campaign.labels
                   FROM campaign
                   WHERE campaign.advertising_channel_type IN ('SEARCH', 'SHOPPING', 'PERFORMANCE_MAX')
                       ${statusQuery} ${idsQuery} ${labelQuery}
    `
    const report = AdsApp.report(query)
    for (let row of report.rows()) {
        if (SETTINGS.CAMPAIGN_NAME_INCLUDE != "" & !row['campaign.name'].includes(SETTINGS.CAMPAIGN_NAME_INCLUDE)) continue
        if (SETTINGS.CAMPAIGN_NAME_EXCLUDE != "" & row['campaign.name'].includes(SETTINGS.CAMPAIGN_NAME_EXCLUDE)) continue
        if (row['campaign.advertising_channel_type'] == 'SEARCH') campaigns.search.push(row['campaign.id'])
        if (row['campaign.advertising_channel_type'] == 'SHOPPING') campaigns.shopping.push(row['campaign.id'])
        if (row['campaign.advertising_channel_type'] == 'PERFORMANCE_MAX') campaigns.pmax.push(row['campaign.id'])
    }
    return campaigns
}

function getLabelId(Labelname) {
    const labelIterator = AdsApp.labels()
        .withCondition(`label.name = "${Labelname}"`)
        .get();
    if (labelIterator.hasNext()) {
        const label = labelIterator.next();
        return {rn: label.getResourceName(), id: label.getId()};
    }
    return get_label_id(Labelname);
}

function getStats(campaigns) {
    const search = getStatsById(campaigns.search)
    const shopping = getStatsById(campaigns.shopping)
    const pmax = getPmaxStats(campaigns.pmax)
    return {search, shopping, pmax}
}

function getStatsById(ids) {
    if (ids.length == 0) return []
    const dates = getDates(SETTINGS.DAYS)
    const results = {}
    const report = AdsApp.report(`SELECT search_term_view.search_term,
                                         campaign.id,
                                         metrics.clicks,
                                         campaign.name,
                                         ad_group.name,
                                         metrics.impressions,
                                         metrics.conversions,
                                         metrics.cost_micros
                                  FROM search_term_view
                                  WHERE campaign.id IN (${ids.map(i => `"${i}"`).join(", ")})
                                    AND segments.date >= '${dates.start}'
                                    AND segments.date <= '${dates.end}'
                                    AND metrics.clicks > ${SETTINGS.MIN_CLICKS - 1}
                                    AND metrics.impressions > ${SETTINGS.MIN_IMPRESSIONS - 1}
                                    AND metrics.conversions > ${SETTINGS.MIN_CONVERSIONS - 0.01}
                                    AND metrics.cost_micros > ${SETTINGS.MIN_COST * 1000000 - 1}
    `)
    for (let row of report.rows()) {
        const searchTerm = row['search_term_view.search_term']
        if (!searchTerm) continue
        const cur = results[searchTerm] || []
        cur.push({
            campaign: row['campaign.name'],
            adgroup: row['ad_group.name'],
            clicks: parseInt(row['metrics.clicks']),
            impressions: parseInt(row['metrics.impressions']),
            conversions: parseInt(row['metrics.conversions']),
            cost: costMicros(row['metrics.cost_micros'])
        })
        results[searchTerm] = cur
    }
    return results
}

function getPmaxStats(ids) {
    if (!ids || ids.length === 0) return {};

    const dates = getDates(SETTINGS.DAYS);
    const results = {};

    const idsIn = ids.map(i => `"${i}"`).join(", ");

    const query = `
        SELECT campaign_search_term_view.search_term,
               campaign.id,
               campaign.name,
               metrics.clicks,
               metrics.impressions,
               metrics.conversions,
               metrics.cost_micros
        FROM campaign_search_term_view
        WHERE campaign.id IN (${idsIn})
          AND segments.date >= '${dates.start}'
          AND segments.date <= '${dates.end}'
          AND metrics.clicks > ${SETTINGS.MIN_CLICKS - 1}
          AND metrics.impressions > ${SETTINGS.MIN_IMPRESSIONS - 1}
          AND metrics.conversions > ${SETTINGS.MIN_CONVERSIONS - 0.01}
          AND metrics.cost_micros > ${SETTINGS.MIN_COST * 1000000 - 1}
    `;

    let report = AdsApp.report(query);



    for (let row of report.rows()) {
        const searchTerm = row['campaign_search_term_view.search_term'];
        if (!searchTerm) continue;
        const cur = results[searchTerm] || [];
        cur.push({
            campaign: row['campaign.name'],
            adgroup: '(PMax)',
            clicks: parseInt(row['metrics.clicks']) || 0,
            impressions: parseInt(row['metrics.impressions']) || 0,
            conversions: parseFloat(row['metrics.conversions']) || 0,
            cost: costMicros(row['metrics.cost_micros'])
        });
        results[searchTerm] = cur;
    }



    return results;
}


function getDates(days) {
    const start = Utilities.formatDate(new Date(Date.now() - days * 86400000), AdsApp.currentAccount().getTimeZone(), "yyyy-MM-dd")
    const end = Utilities.formatDate(new Date(Date.now() - 1 * 86400000), AdsApp.currentAccount().getTimeZone(), "yyyy-MM-dd")
    return {start, end}
}

function costMicros(cost) {
    return parseInt(cost || 0) / 1000000
}

function buildOverlapRows(objA, objB, objC = null) {
  const hasC = !!objC;
  const rows = [];

  const keysA = Object.keys(objA || {});
  const setB = new Set(Object.keys(objB || {}));
  const setC = hasC ? new Set(Object.keys(objC || {})) : null;

  const terms = keysA.filter(t =>
    setB.has(t) && (!hasC || setC.has(t))
  );

  const num = v => Number(v) || 0;
  const str = v => (v == null ? "" : String(v));

  for (const term of terms) {
    const arrA = Array.isArray(objA[term]) ? objA[term] : [];
    const arrB = Array.isArray(objB[term]) ? objB[term] : [];
    const arrC = hasC && Array.isArray(objC[term]) ? objC[term] : [];

    if (!arrA.length || !arrB.length) continue;
    if (hasC && !arrC.length) continue;

    if (!hasC) {
      for (const a of arrA) {
        for (const b of arrB) {
          const sumCost = num(a.cost) + num(b.cost);

          rows.push([
            term,

            str(a.campaign), str(a.adgroup),
            num(a.clicks), num(a.impressions), num(a.conversions), num(a.cost),

            str(b.campaign), str(b.adgroup),
            num(b.clicks), num(b.impressions), num(b.conversions), num(b.cost),

            sumCost
          ]);
        }
      }
    } else {
      for (const a of arrA) {
        for (const b of arrB) {
          for (const c of arrC) {
            const sumCost = num(a.cost) + num(b.cost) + num(c.cost);

            rows.push([
              term,

              str(a.campaign), str(a.adgroup),
              num(a.clicks), num(a.impressions), num(a.conversions), num(a.cost),

              str(b.campaign), str(b.adgroup),
              num(b.clicks), num(b.impressions), num(b.conversions), num(b.cost),

              str(c.campaign), str(c.adgroup),
              num(c.clicks), num(c.impressions), num(c.conversions), num(c.cost),

              sumCost
            ]);
          }
        }
      }
    }
  }

  rows.sort((a, b) => (b[b.length - 1] || 0) - (a[a.length - 1] || 0));

  return rows;
}

function updateSpreadsheet(tab, arr) {
    const sh = SpreadsheetApp.openByUrl(SETTINGS.SHEET_URL).getSheetByName(tab)
    const tmp = sh.getDataRange().getValues()
    if (tmp.length > 2) sh.getRange(3, 1, tmp.length - 2, tmp[0].length).clear()
    if (arr.length==0) return
    sh.getRange(3, 1, arr.length, arr[0].length).setValues(arr)
}