LOẠI TRỪ VỊ TRÍ HIỂN THỊ

loai_tru_vi_tri_hien_thi.js

/****************************
 * Loại bỏ vị trí hiển thị 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ô
 ****************************/

 var CONFIG = {
  LAST_N_DAYS: 30, // thời gian quét từ ngày quét trở về trước đó
  MAX_COST: 80000, // 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: 80000, // 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_placement', // Tên tab trong sheet, copy đúng tên bỏ vào đây

  // Có cho phép tạo danh sách loại trừ vị trí ở cấp tài khoản không
  // true => cho phép | false => không cho phép
  // khi loại trừ vị trí ở cấp tài khoản thì tất cả các chiến dịch sau này sẽ không hiển thị quảng cáo trong các vị trí có trong dnh sách đã bị loại trừ
  CREATE_EXCLUDED_PLACEMENT_LIST_AND_ADD_TO_ALL_COMPAIN: false, // Hiện tại google chưa hỗ trợ tính năng này, sau này nếu gg hỗ trợ sẽ mở chức năng này ra
  EXCLUDED_PLACEMENT_LIST_NAME: 'Danh sách loại trừ vị trí hiển thị',
};

var out = {};

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')}'`;

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

  const groupPlacement = getGroupPlacementForRemove(DATE_RANGE);
  const detailPlacement = getDetailPlacementForRemove(DATE_RANGE);

  var adGroupIdsFromGroupPlacement = Object.keys(groupPlacement);
  var adGroupIdsFromDetailPlacement = Object.keys(detailPlacement);

  // remove youtube channel from ad group
  if (adGroupIdsFromGroupPlacement.length > 0) {
    const videoAdGroupsIterator = AdsApp.videoAdGroups()
      .withIds(adGroupIdsFromGroupPlacement)
      .get();

    const adGroupsIterator = AdsApp.adGroups()
      .withIds(adGroupIdsFromGroupPlacement)
      .get();

    while (videoAdGroupsIterator.hasNext()) {
      const adGroup = videoAdGroupsIterator.next();

      removeTargetUrlFromAdGroup(adGroup, groupPlacement);
    }

    while (adGroupsIterator.hasNext()) {
      const adGroup = adGroupsIterator.next();
      removeTargetUrlFromAdGroup(adGroup, groupPlacement);
    }
  }

  //remove website from ad group
  if (adGroupIdsFromDetailPlacement.length > 0) {
    const videoAdGroupsIterator = AdsApp.videoAdGroups()
      .withIds(adGroupIdsFromDetailPlacement)
      .get();

    const adGroupsIterator = AdsApp.adGroups()
      .withIds(adGroupIdsFromDetailPlacement)
      .get();

    while (videoAdGroupsIterator.hasNext()) {
      const adGroup = videoAdGroupsIterator.next();
      removeTargetUrlFromAdGroup(adGroup, detailPlacement);
    }

    while (adGroupsIterator.hasNext()) {
      const adGroup = adGroupsIterator.next();
      removeTargetUrlFromAdGroup(adGroup, detailPlacement);
    }
  }

  // append to sheet
  const outValue = Object.values(out);

  if (outValue.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 outValue) {
        const existIndex = excludedRow.findIndex((o) => {
          return `${o[4]}` === `${item.adGroup.id}` && `${o[7]}` === item.placement;
        });
      

        if (existIndex === -1) {
          sheet.appendRow([
            convertDateToShortDay(new Date()),
            AdsApp.currentAccount().getName(),

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

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

            item.placement_type,
            item.placement,
            item.target_url,

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

    if (CONFIG.CREATE_EXCLUDED_PLACEMENT_LIST_AND_ADD_TO_ALL_COMPAIN) {
      const urls = outValue.map((o) => o.target_url);
      console.log('target_url', urls);

      const excludedPlacementList = createExcludedPlacementListIfRequired(
        CONFIG.EXCLUDED_PLACEMENT_LIST_NAME
      );

      excludedPlacementList.addExcludedPlacements(urls);

      // add exclude placements to video compain
      const videoCampaignIterator = AdsApp.videoCampaigns()
        .withCondition("Status = 'ENABLED'")
        .get();
      while (videoCampaignIterator.hasNext()) {
        var videoCampaign = videoCampaignIterator.next();
        videoCampaign.addExcludedPlacementList(excludedPlacementList);
      }

      // add exclude placements to display compain
      const displayCampaignIterator = AdsApp.campaigns()
        .withCondition("Status = 'ENABLED'")
        .get();
      while (displayCampaignIterator.hasNext()) {
        var displayCampaign = displayCampaignIterator.next();
        displayCampaign.addExcludedPlacementList(excludedPlacementList);
      }
    }
  }

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

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

function createExcludedPlacementListIfRequired(listName) {
  var negativeListIterator = AdsApp.excludedPlacementLists()
    .withCondition(`Name = '${CONFIG.EXCLUDED_PLACEMENT_LIST_NAME}'`)
    .get();

  if (negativeListIterator.totalNumEntities() == 0) {
    var builder = AdsApp.newExcludedPlacementListBuilder()
      .withName(listName)
      .build();

    return builder.getResult();
  } else {
    return negativeListIterator.next();
  }
}

function removeTargetUrlFromAdGroup(adGroup, placements) {
  const id = adGroup.getId();

  if (placements.hasOwnProperty(id)) {
    const rows = placements[id];

    for (const key in rows) {
      const row = rows[key];
      const placement_type = row.placement_type;
      const placement = row.placement;
      const advertising_channel_type = row.campaign.advertisingChannelType;

      if (advertising_channel_type === 'VIDEO') {
        if (placement_type === 'YOUTUBE_VIDEO') {
          adGroup
            .videoTargeting()
            .newYouTubeVideoBuilder()
            .withVideoId(placement)
            .exclude();
        }

        if (placement_type === 'YOUTUBE_CHANNEL') {
          adGroup
            .videoTargeting()
            .newYouTubeChannelBuilder()
            .withChannelId(placement)
            .exclude();
        }
        if (placement_type === 'WEBSITE') {
          adGroup
            .videoTargeting()
            .newPlacementBuilder()
            .withUrl(placement)
            .exclude();
        }

        if (placement_type === 'MOBILE_APPLICATION') {
          adGroup
            .videoTargeting()
            .newMobileApplicationBuilder()
            .withAppId(placement)
            .exclude();
        }
      }

      if (advertising_channel_type === 'DISPLAY') {
        if (placement_type === 'YOUTUBE_VIDEO') {
          adGroup
            .display()
            .newYouTubeVideoBuilder()
            .withVideoId(placement)
            .exclude();
        }

        if (placement_type === 'YOUTUBE_CHANNEL') {
          adGroup
            .display()
            .newYouTubeChannelBuilder()
            .withChannelId(placement)
            .exclude();
        }
        if (placement_type === 'WEBSITE') {
          adGroup.display().newPlacementBuilder().withUrl(placement).exclude();
        }
      }

      if (!out.hasOwnProperty(placement)) {
        out[placement] = row;
      }
    }
  }
}

function getGroupPlacementForRemove(range) {
  const query = `SELECT
    group_placement_view.target_url,
    group_placement_view.display_name,
    group_placement_view.placement_type,
    group_placement_view.placement,
    metrics.conversions,
    metrics.cost_micros,
    metrics.cost_per_conversion,
    metrics.impressions,
    metrics.ctr,
    campaign.id,
    campaign.name,
    campaign.advertising_channel_type,
    ad_group.id,
    ad_group.name
    FROM group_placement_view
    WHERE
    segments.date BETWEEN ${range}
    AND metrics.cost_micros >= ${CONFIG.MAX_COST} 
    AND campaign.bidding_strategy_type != 'MANUAL_CPV'
     AND campaign.status = 'ENABLED'
    ORDER BY
    metrics.cost_micros DESC,
    campaign.name ASC`;

  const report = AdsApp.search(query);

  var results = {};
  for (const row of report) {
    var adGroupId = row.adGroup.id;
    var targetUrl = row.groupPlacementView.targetUrl;
    var conversions = parseFloat(row.metrics.conversions);
    var cost = parseFloat(row.metrics.costMicros);
    var cost_per_conversion = parseFloat(row.metrics.costPerConversion);
    var cpa = conversions === 0 ? cost : cost_per_conversion;

    row.cpa = Math.floor(cpa / 1000000);
    row.cost = Math.floor(cost / 1000000);
    row.target_url = targetUrl;
    row.placement_type = row.groupPlacementView.placementType;
    row.placement = row.groupPlacementView.placement;

    if (targetUrl === 'youtube.com') continue;

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

  return results;
}

function getDetailPlacementForRemove(range) {
  const query = `SELECT 
    detail_placement_view.target_url, 
    detail_placement_view.display_name, 
    detail_placement_view.placement_type,
    detail_placement_view.placement,
    metrics.conversions, 
    metrics.cost_micros, 
    metrics.cost_per_conversion,
    metrics.impressions,
    metrics.ctr,
    campaign.id,
    campaign.name,
    campaign.advertising_channel_type,
    ad_group.id, 
    ad_group.name 
  FROM detail_placement_view 
  WHERE 
  segments.date BETWEEN ${range}
  AND metrics.cost_micros >= ${CONFIG.MAX_COST} 
  AND campaign.bidding_strategy_type != 'MANUAL_CPV'
   AND campaign.status = 'ENABLED'
  ORDER BY 
    metrics.cost_micros DESC `;

  const report = AdsApp.search(query);

  const results = {};
  for (const row of report) {
    const adGroupId = row.adGroup.id;
    const targetUrl = row.detailPlacementView.targetUrl;
    const conversions = parseFloat(row.metrics.conversions);
    var cost = parseFloat(row.metrics.costMicros);
    const cost_per_conversion = parseFloat(row.metrics.costPerConversion);
    const cpa = conversions === 0 ? cost : cost_per_conversion;

    row.cpa = Math.floor(cpa / 1000000);
    row.cost = Math.floor(cost / 1000000);
    row.target_url = targetUrl;
    row.placement_type = row.detailPlacementView.placementType;
    row.placement = row.detailPlacementView.placement;

    if (targetUrl === 'youtube.com') continue;

    if (
      (conversions === 0 && cost >= CONFIG.MAX_COST) ||
      cpa >= CONFIG.MAX_CPA
    ) {
      if (!results.hasOwnProperty(adGroupId)) {
        results[adGroupId] = [];
      }
      results[adGroupId].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
  );
}