TẮT ĐỐI TƯỢNG CÓ HIỆU SUẤT KÉM

tat_doi_tuong.js

/****************************
 * Loại bỏ đối tượng có hiệu suất kém ra khỏi nhóm quảng cáo
 * Version 1.0 (21/07/2022)
 *
 * Created By: Quý Ngô
 ****************************/

// cấu hình
var CONFIG = {
  LAST_N_DAYS: 7, // thời gian quét từ ngày quét trở về trước đó
  MAX_COST: 150000, // chi tiêu từ số này trở lên mới bắt đầu tính theo điều kiện dưới là CPA
  MAX_CPA: 150000, // CPA tối đa có thể chịu được
  EMAIL: '', //Email để nhận thông báo, nhiều email cách nhau bởi dấu ",".VD:'[email protected], [email protected]'
  SHEET_URL:
    'https://docs.google.com/spreadsheets/d/1mMCUvlM4Dq6fIevsZ_uvcwPwiZewoxLpe6GWgSYKgmQ/edit?usp=sharing',
  //Tạo 1 bản sao của sheet này https://docs.google.com/spreadsheets/d/1mMCUvlM4Dq6fIevsZ_uvcwPwiZewoxLpe6GWgSYKgmQ/copy
  //Sau đó chia sẻ sheet vừa tạo thêm quyền chỉnh sửa và lấy đường liên kết dán vào SHEET_URL
  SHEET_TAB_NAME: 'excluded_audience',
};

function main() {
  const ss = SpreadsheetApp.openByUrl(CONFIG.SHEET_URL);
  const DATE_RANGE = `'${getAdWordsFormattedDate(
    CONFIG.LAST_N_DAYS,
    'yyyy-MM-dd'
  )}' AND '${getAdWordsFormattedDate(0, 'yyyy-MM-dd')}'`;

  var appendToSheetRow = [];
  var videoAdGroupIds = [];
  var displayAdGroupIds = [];

  CONFIG.MAX_COST = CONFIG.MAX_COST * 1000000;
  CONFIG.MAX_CPA = CONFIG.MAX_CPA * 1000000;

  const adGroupAudienceMap = getAudienceReport(DATE_RANGE);

  console.log('adGroupAudienceMap', adGroupAudienceMap);

  if (adGroupAudienceMap.hasOwnProperty('VIDEO')) {
    const videoAudienceMap = adGroupAudienceMap['VIDEO'];
    const ids = Object.keys(videoAudienceMap).map((o) => {
      const arr = o.split('_');
      return [arr[0], arr[1]];
    });

    const audiencesIterator = AdsApp.videoTargeting()
      .audiences()
      .withIds(ids)
      .get();
    while (audiencesIterator.hasNext()) {
      const audience = audiencesIterator.next();
      const id = audience.getId();
      const audienceId = audience.getAudienceId();

      const adGroupId = audience.getVideoAdGroup().getId();

      const uniqueId = `${adGroupId}_${id}`;
      const rows = videoAudienceMap[uniqueId] ?? [];

      if (rows.length > 0) {
        for (const item of rows) {
          videoAdGroupIds.push(item.adGroup.id);
          appendToSheetRow.push([
            convertDateToShortDay(new Date()),
            AdsApp.currentAccount().getName(),

            item.campaign.id,
            item.campaign.name,

            item.adGroup.id,
            item.adGroup.name,

            item.adGroupCriterion.criterionId,
            audienceId,
            audience.getName(),

            item.metrics.impressions,
            item.metrics.ctr,
            item.cost,
            item.metrics.conversions,
            item.cpa,
          ]);
        }
      }

      if (audience.isEnabled()) audience.remove();
    }
  }

  if (adGroupAudienceMap.hasOwnProperty('DISPLAY')) {
    const displayAudienceMap = adGroupAudienceMap['DISPLAY'];
    const ids = Object.keys(displayAudienceMap).map((o) => {
      const arr = o.split('_');
      return [arr[0], arr[1]];
    });

    const audiencesIterator = AdsApp.display().audiences().withIds(ids).get();
    while (audiencesIterator.hasNext()) {
      const audience = audiencesIterator.next();
      const id = audience.getId();
      const audienceId = audience.getAudienceId();

      const adGroupId = audience.getAdGroup().getId();

      const uniqueId = `${adGroupId}_${id}`;
      const rows = displayAudienceMap[uniqueId] ?? [];

      if (rows.length > 0) {
        for (const item of rows) {
          displayAdGroupIds.push(item.adGroup.id);
          appendToSheetRow.push([
            convertDateToShortDay(new Date()),
            AdsApp.currentAccount().getName(),

            item.campaign.id,
            item.campaign.name,

            item.adGroup.id,
            item.adGroup.name,

            item.adGroupCriterion.criterionId,
            audienceId,
            item.adGroupCriterion.displayName,

            item.metrics.impressions,
            item.metrics.ctr,
            item.cost,
            item.metrics.conversions,
            item.cpa,
          ]);
        }
      }

      if (audience.isEnabled()) audience.remove();
    }
  }

  // stop ad group nếu đã loại trừ hết đối tượng
  if (videoAdGroupIds.length > 0) {
    const ids = [...new Set(videoAdGroupIds)];
    const videoAdGroupIterator = AdsApp.videoAdGroups().withIds(ids).get();
    while (videoAdGroupIterator.hasNext()) {
      const videoAdGroup = videoAdGroupIterator.next();
      var audienceIterator = videoAdGroup
        .videoTargeting()
        .audiences()
        .withCondition("ad_group_criterion.status = 'ENABLED'")
        .get();
      if (audienceIterator.totalNumEntities() === 0) {
        if (videoAdGroup.isEnabled()) videoAdGroup.pause();
      } else {
        let pause = true;
        while (audienceIterator.hasNext()) {
          const audience = audienceIterator.next();
          if (audience.isEnabled()) pause = false;
        }
        if (pause) videoAdGroup.pause();
      }
    }
  }

  if (displayAdGroupIds.length > 0) {
    const ids = [...new Set(displayAdGroupIds)];
    const displayAdGroupIterator = AdsApp.adGroups().withIds(ids).get();
    while (displayAdGroupIterator.hasNext()) {
      const displayAdGroup = displayAdGroupIterator.next();
      const audienceIterator = displayAdGroup
        .videoTargeting()
        .audiences()
        .get();
      if (audienceIterator.totalNumEntities() === 0) {
        if (displayAdGroup.isEnabled()) displayAdGroup.pause();
      }
    }
  }

  // append to sheet

  if (appendToSheetRow.length > 0) {
    const sheet = ss.getSheetByName(CONFIG.SHEET_TAB_NAME);
    if (sheet === null) {
      console.log(
        'Vui lòng copy tên của sheet tab vào biến SHEET_TAB_NAME trên cấu hình'
      );
    } else {
      const allRow = sheet.getDataRange().getValues();
      let excludedRow = [];
      if (allRow.length > 0) excludedRow = allRow.slice(1);

      for (const item of appendToSheetRow) {
        const existIndex = excludedRow.findIndex((o) => {
          return `${o[4]}` === `${item[4]}` && `${o[6]}` === `${item[6]}`;
        });
        if (existIndex === -1) {
          sheet.appendRow(item);
        }
      }
    }
  }

  // sent email
  if (CONFIG.EMAIL != '') {
    var SUB = `${AdsApp.currentAccount().getName()} - Automatic remove bad audience`;
    var BODY = `Hi\n\nPlease find below the report of Automatic remove bad audience which were excluded from the AdGroups:\n ${ss.getUrl()}\n\nThanks`;

    MailApp.sendEmail(CONFIG.EMAIL, SUB, BODY);
  }
}

function getAudienceReport(range) {
  var query = `SELECT 
    ad_group.id, 
    ad_group.name, 
    ad_group.status, 
    ad_group_criterion.ad_group, 
    ad_group_criterion.audience.audience, 
    ad_group_criterion.criterion_id, 
    ad_group_criterion.custom_audience.custom_audience, 
    ad_group_criterion.display_name, 
    ad_group_criterion.labels, 
    ad_group_criterion.placement.url, 
    ad_group_criterion.status, 
    ad_group_criterion.resource_name, 
    ad_group_criterion.type, 
    ad_group_criterion.user_list.user_list, 
    ad_group_criterion.user_interest.user_interest_category, 
    campaign.id, 
    campaign.name, 
    campaign.advertising_channel_type, 
    metrics.cost_micros, 
    metrics.ctr, 
    metrics.cost_per_conversion, 
    metrics.impressions, 
    metrics.conversions 
  FROM ad_group_audience_view 
  WHERE 
    ad_group_criterion.status = 'ENABLED'
    AND ad_group_criterion.type IN ('USER_INTEREST', 'USER_LIST')
    AND metrics.cost_micros >= ${CONFIG.MAX_COST}
    AND campaign.status = 'ENABLED'
    AND segments.date BETWEEN ${range}
  ORDER BY 
    metrics.cost_micros DESC`;

  const report = AdsApp.search(query);
  const results = {};
  for (const row of report) {
    const adGroupId = row.adGroup.id;

    const conversions = parseFloat(row.metrics.conversions);
    const cost = parseFloat(row.metrics.costMicros);
    const costPerConversion = parseFloat(row.metrics.costPerConversion);
    const cpa = conversions === 0 ? cost : costPerConversion;

    row.cpa = Math.floor(cpa / 1000000);
    row.cost = Math.floor(cost / 1000000);

    const uniqueId = `${adGroupId}_${row.adGroupCriterion.criterionId}`;

    if (
      (conversions === 0 && cost >= CONFIG.MAX_COST) ||
      cpa >= CONFIG.MAX_CPA
    ) {
      if (row.campaign.advertisingChannelType === 'VIDEO') {
        if (!results.hasOwnProperty('VIDEO')) {
          results['VIDEO'] = {};
        }
        if (!results['VIDEO'].hasOwnProperty(uniqueId)) {
          results['VIDEO'][uniqueId] = [];
        }
        results['VIDEO'][uniqueId].push(row);
      }

      if (row.campaign.advertisingChannelType === 'DISPLAY') {
        if (!results.hasOwnProperty('DISPLAY')) {
          results['DISPLAY'] = {};
        }

        if (!results['DISPLAY'].hasOwnProperty(uniqueId)) {
          results['DISPLAY'][uniqueId] = [];
        }
        results['DISPLAY'][uniqueId].push(row);
      }
    }
  }

  return results;
}

function convertDateToShortDay(date) {
  const dd = String(date.getDate()).padStart(2, '0');
  const mm = String(date.getMonth() + 1).padStart(2, '0'); //January is 0!
  const yyyy = date.getFullYear();

  return dd + '/' + mm + '/' + yyyy;
}

function getAdWordsFormattedDate(d, format) {
  var date = new Date();
  date.setDate(date.getDate() - d);
  return Utilities.formatDate(
    date,
    AdsApp.currentAccount().getTimeZone(),
    format
  );
}