TẮT GIỚI TÍNH & ĐỘ TUỔI

tat_nhan_khau_hoc.js

/****************************
 * Loại bỏ giới tính + độ tuổi 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: 7, // thời gian quét từ ngày quét trở về trước đó
  MAX_COST: 170000, // 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: 170000, // 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

  GENDER_SHEET_TAB_NAME: 'giới tính', // Tên tab trong sheet, copy đúng tên bỏ vào đây
  AGE_SHEET_TAB_NAME: 'độ tuổi', // Tên tab trong sheet, copy đúng tên bỏ vào đây
};

var genderRows = [];
var agesRows = [];

function main() {
  const ss = SpreadsheetApp.openByUrl(CONFIG.SHEET_URL);
  genderRows = [];
  agesRows = [];

  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 genders = getGenderReport(DATE_RANGE);
  const ages = getAgeReport(DATE_RANGE);

  var adGroupIdsFromGender = Object.keys(genders);
  var adGroupIdsFromAges = Object.keys(ages);

  // console.log('genders', genders);
  // console.log('ages', ages);

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

    while (videoAdGroupsIterator.hasNext()) {
      const adGroup = videoAdGroupsIterator.next();
      console.log('adGroup', adGroup.getName());
      disableGender(adGroup, genders);
    }
  }

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

    while (videoAdGroupsIterator.hasNext()) {
      const adGroup = videoAdGroupsIterator.next();
      disableAge(adGroup, ages);
    }
  }

 // append to sheet gender

  if (genderRows.length > 0) {
    const sheet = ss.getSheetByName(CONFIG.GENDER_SHEET_TAB_NAME);
    if (sheet === null) {
      console.log(
        'Vui lòng copy tên của sheet tab vào biến GENDER_SHEET_TAB_NAME trên cấu hình'
      );
    } else {
      for (const item of genderRows) {
        sheet.appendRow([
          convertDateToShortDay(new Date()),
          AdsApp.currentAccount().getName(),

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

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

          item.adGroupCriterion.displayName,
          item.adGroupCriterion.gender.type,

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

  if (agesRows.length > 0) {
    const sheet = ss.getSheetByName(CONFIG.AGE_SHEET_TAB_NAME);
    if (sheet === null) {
      console.log(
        'Vui lòng copy tên của sheet tab vào biến AGE_SHEET_TAB_NAME trên cấu hình'
      );
    } else {
      for (const item of agesRows) {
        sheet.appendRow([
          convertDateToShortDay(new Date()),
          AdsApp.currentAccount().getName(),

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

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

          item.adGroupCriterion.displayName,
          item.adGroupCriterion.ageRange.type,

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

  // 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 disableGender(adGroup, genders) {
  const id = adGroup.getId();

  if (genders.hasOwnProperty(id)) {
    const rows = genders[id];
    
    for (const key in rows) {
      const row = rows[key];
      
      const advertising_channel_type = row.campaign.advertisingChannelType;
      const gender = row.adGroupCriterion.gender.type;
      if (advertising_channel_type === 'VIDEO') {
        const genderOperation = adGroup
          .videoTargeting()
          .newGenderBuilder()
          .withGenderType(`${gender}`)
          .exclude();

        if (genderOperation.isSuccessful()) {
          console.log(`ĐÃ tắt giới tính ${gender} khỏi nhóm`);
          genderRows.push(row);
        } else {
          console.warn(`Errors: ${operation.getErrors().join(', ')}'`);
        }

        genderRows.push(row);
      }
    }
  }
}

function disableAge(adGroup, ages) {
  const id = adGroup.getId();

  if (ages.hasOwnProperty(id)) {
    const rows = ages[id];
    for (const key in rows) {
      const row = rows[key];
      const advertising_channel_type = row.campaign.advertisingChannelType;
      const range = row.adGroupCriterion.ageRange.type;
      if (advertising_channel_type === 'VIDEO') {
        const genderOperation = adGroup
          .videoTargeting()
          .newAgeBuilder()
          .withAgeRange(range)
          .exclude();

        if (genderOperation.isSuccessful()) {
          console.log(`ĐÃ tắt độ tuổi ${range} khỏi nhóm`);
          agesRows.push(row);
        } else {
          console.warn(`Errors: ${operation.getErrors().join(', ')}'`);
        }

        agesRows.push(row);
      }
    }
  }
}

function getGenderReport(range) {
  const query = `SELECT 
  gender_view.resource_name, 
  metrics.conversions, 
  metrics.cost_micros, 
  metrics.cost_per_conversion, 
  metrics.impressions,
  metrics.ctr, 
  ad_group.id, 
  ad_group.name, 
  ad_group.status, 
  ad_group.type, 
  ad_group_criterion.display_name, 
  ad_group_criterion.gender.type, 
  ad_group_criterion.status, 
  ad_group_criterion.type, 
  campaign.advertising_channel_type, 
  campaign.id, 
  campaign.name, 
  campaign.status, 
  customer.id, 
  customer.descriptive_name 
  FROM gender_view
    WHERE
    segments.date BETWEEN ${range}
    AND metrics.cost_micros >= ${CONFIG.MAX_COST} 
    AND campaign.bidding_strategy_type != 'MANUAL_CPV'
    AND campaign.status = 'ENABLED'
    AND ad_group_criterion.status = 'ENABLED'
    ORDER BY
    metrics.cost_micros DESC`;

  const report = AdsApp.search(query);

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

    const conversions = parseFloat(row.metrics.conversions);
    const 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);

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

  return results;
}

function getAgeReport(range) {
  const query = `SELECT 
  
  ad_group_criterion.display_name, 
  ad_group_criterion.criterion_id,
  ad_group_criterion.age_range.type,
    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 age_range_view 
  WHERE 
  segments.date BETWEEN ${range}
  AND metrics.cost_micros >= ${CONFIG.MAX_COST} 
  AND campaign.bidding_strategy_type != 'MANUAL_CPV'
  AND campaign.status = 'ENABLED'
  AND ad_group_criterion.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 conversions = parseFloat(row.metrics.conversions);
    const 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);

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