Display Excluder

/**
 * @name Placement Excluder YouTube & Display
 *
 * @instructions
 *     - ⚠ Enable "New scripts experience" above google ads script ⚠
 *     - Find settings in the config variable
 *     
 * @notes
 *     - Check log files below for execution information.
 *     - Run a preview before first usage.
 *
 * @version 2.10
 *
 * @author Bas Baudoin - adsscripts.com
 *
 */

const config = {
  modus: 'list', // add 'campaign', or 'list',
  listName: 'script_display', // when in list modus, e.g. 'exclusions list' (should already exist)
  excludeDomain: true, // by default it only excludes URLs, but it can also exclude the domain
  excludeUrl: false, // exclude full url
  type: 'youtube', // 'display' or 'youtube'
  lastXdays: 1, // 0 = today, 1 = today+yesterday, etc.

  excludeDomains: ['20min.ch', '.tk', '.ru', 'viral', '.za', '.ru'], // example: ['20min.ch', '.tk', '.de', 'viral'],
  mustInclude: ['.nl', '.com', '.nu', '.net'],
  exceptions: ['zaaltje'],

  // for developer
  testMode: false, // = false for normal operation
  reverse: false, // = false for normal operation
}

function main() {
  console.log('starting script...')
  const { excludeDomains, mustIncludes } = createRegexes()
  //console.log(excludeDomains)
  const startDate = getDateByDaysAgo(config.lastXdays)
  const endDate = getDateByDaysAgo(0)
  console.log(startDate, endDate)
  const rev = config.reverse && config.testMode ? 'NOT ' : ''

  let qBase = "SELECT detail_placement_view.target_url, detail_placement_view.placement_type, detail_placement_view.placement, detail_placement_view.group_placement_target_url, detail_placement_view.display_name, metrics.ctr, ad_group.name, campaign.id, campaign.name, metrics.impressions, metrics.clicks, metrics.conversions, metrics.ctr "
  qBase += 'FROM detail_placement_view '
  qBase += `WHERE segments.date BETWEEN '${startDate}' AND '${endDate}' `
  //console.log(qBase)

  console.log('Excluding 🔂 terms and domains')
  const qDomains = qBase + `AND detail_placement_view.target_url ${rev}REGEXP_MATCH '${excludeDomains}' `
  if (config.excludeDomains.length > 0) excludeLoop(qDomains)

  console.log('Excluding 🔂 "must contain"')
  const qPerformance = qBase + `AND detail_placement_view.target_url NOT REGEXP_MATCH '${mustIncludes}' `
  excludeLoop(qPerformance)
}

// --- //

function excludeLoop(q) {
  var report = AdsApp.report(q)

  let list
  if (config.listName !== '') {
    list = AdsApp.excludedPlacementLists().withCondition('Name = "' + config.listName + '"').get().next()
  }

  let exclusions = []
  const accountName = AdsApp.currentAccount().getName()
  const startDate = getDateByDaysAgo(config.lastXdays)
  const endDate = getDateByDaysAgo(0)
  const dateRange = `${startDate} - ${endDate}`

  const rows = report.rows()
  
  while (rows.hasNext()) {
    const row = rows.next()
    console.log('---')
    const placementUrl = row['detail_placement_view.target_url']
    const campaignId = row['campaign.id']
    const campaignName = row['campaign.name']
    const impressions = row['metrics.impressions']
    const clicks = row['metrics.clicks']
    const conversions = row['metrics.conversions']
    console.log('🛑 excluding', placementUrl, campaignName)

    // check if in exclusion list
    const isInExceptionList = config.exceptions.find(x => {
      if (placementUrl) {
        return placementUrl.includes(x)
      }
    })
    if (isInExceptionList) {
      console.log(`Placement excluded because it is in exclusion list: ${isInExceptionList} in ${placementUrl}`)
      continue
    }

    if (config.modus === 'campaign') {
      if (config.excludeUrl) {
        excludePlacementInCampaign(placementUrl, campaignId)
      }
      
      if (config.excludeDomain) {
        const domain = placementUrl.split('/')[0]
        if (domain.includes('.')) {
          excludePlacementInCampaign(domain, campaignId)
        }
      }
    }
    
    if (config.modus === 'list') {
      if (config.excludeUrl) {
        list.addExcludedPlacement(placementUrl)
      }
      
      if (config.excludeDomain) {
        const domain = placementUrl.split('/')[0]
        if (domain.includes('.')) {
          list.addExcludedPlacement(domain)
        }
      }
    }
    
    exclusions.push([accountName, dateRange, placementUrl, campaignName, impressions, clicks, conversions]) // not used
  }
}

function createRegexes() {
  const excludeDomains = '(^.*' + config.excludeDomains
    .map(x => x.replace(/\./g, '[.]'))
    .join('.*$)|(^.*') + '.*$)'
  const mustIncludes = '(^.*' + config.mustInclude
    .map(x => x.replace(/\./g, '[.]'))
    .join('.*$)|(^.*') + '.*$)'
  return { excludeDomains, mustIncludes }
}

function excludePlacementInCampaign(placementUrl, campaignId) {
  let campaigns
  
  if (config.type === 'youtube') {
    console.log('❗❗ cannot exclude placements in youtube campaigns via scripts, use list mode')
    // hope that google will allow this in the future (2022-07-26)
    return
  }
  
  if (config.type === 'display') {
    campaigns = AdsApp.campaigns().withIds([parseInt(campaignId)]).get()
    if (campaigns.totalNumEntities() != 1) {
      console.log('⚠ campaign Id not found ' + campaignId)
      return
    }
  }
    
  const campaign = campaigns.next()
  const res = campaign.display().newPlacementBuilder().withUrl(placementUrl).exclude()
}

function getDateByDaysAgo(daysAgo) {
  let today = new Date()
  today.setDate(today.getDate() - daysAgo)
  var formattedDate = Utilities.formatDate(today, 'PST', 'yyyy-MM-dd')
  return formattedDate
}