Yahoo!広告 スクリプト | Developer Center

/*このソースコードは MIT License のもとで提供されています。
https://ads-developers.yahoo.co.jp/ja/ads-script/post/30418913.html 
■スクリプト内容
スプレッドシートで指定した条件の検索クエリーをスプレッドシートに出力し、対象外キーワードとして登録します。
■利用方法
１．当スクリプトを、検索広告のアカウントに設定してください。
２．定数を以下のように設定してください。
■定数
・SPREAD_SHEET_ID   //スプレットシートIDを指定
・INPUT_SHEET_NAME  //条件指定シート名
・OUTPUT_SHEET_NAME //出力シート名
・IS_DAS_CAMPAIGN   //動的検索連動型広告キャンペーンの場合はtrue、通常キャンペーンの場合はfalse
・FLAG_MAIL         //結果をメール送信するならtrue、しないならfalse
・MAIL_TO           //メール送信先のYahoo! BusinessID
・MAIL_TITLE        //メールタイトル
・FLAG_SLACK        //結果をSlack送信するならtrue、しないならfalse
・URL_FETCH_APP     //SlackのWebhook URL
■制限事項
・タイムアウトになった場合、データが大量のため処理しきれていない可能性があります。2000件程度に収まるよう条件を指定してください。
 */
//設定が必要な定数
const SPREAD_SHEET_ID = '';
const INPUT_SHEET_NAME = '条件指定';//初期値はテンプレートのシート名
const OUTPUT_SHEET_NAME = '結果出力';//初期値はテンプレートのシート名
const IS_DAS_CAMPAIGN = false;
const FLAG_MAIL = false;
const MAIL_TO = ['Yahoo! JAPAN Business ID'];
let MAIL_TITLE = '対象外キーワード抽出・追加';
const FLAG_SLACK = false;
const URL_FETCH_APP = 'SLACK_WEBHOOK_URL';
//設定が不要な定数（変更するとエラーになります）
const accountId = AdsUtilities.getCurrentAccountId();
let TEXT_MESSAGE_ARRAY = [];
const TARGET_SERVICE = {
  '対象外キーワードリストID': Search.SharedCriterionService,
  '検索クエリーが発生したキャンペーン': Search.CampaignCriterionService,
  '検索クエリーが発生した広告グループ': Search.AdGroupCriterionService
};
const SPREAD_SHEET_URL = 'https://docs.google.com/spreadsheets/d/' + SPREAD_SHEET_ID;
const SS_FLG_RANGE_STR = 'B5:B6';//1.動作設定
const SS_PERIOD_RANGE_STR = 'B9:C9';//2.抽出条件-期間
const SS_CONDITION_RANGE_STR = 'B10:D24';//2.抽出条件-条件
const SS_TARGET_RANGE_STR = 'A27:B28';//3.関連付け対象
const SEARCH_QUERY_FIELD = [//並び順を変えた場合、後述のADGROUP_COL_IDX、QUERY_COL_IDXに反映する
  'ACCOUNT_ID', 'ACCOUNT_NAME', 'CAMPAIGN_ID', 'CAMPAIGN_NAME', 'ADGROUP_ID', 'ADGROUP_NAME',
  'SEARCH_QUERY', 'KEYWORD_ID', 'KEYWORD', 'SEARCH_QUERY_MATCH_TYPE', 'QUERY_TARGETING_STATUS',
  'COST', 'IMPS', 'CLICKS', 'CLICK_RATE', 'AVG_CPC', 'CONVERSIONS', 'CONV_RATE', 'COST_PER_CONV', 'CONV_VALUE'
];
const KEYWORDLESS_QUERY_FIELD = [//並び順を変えた場合、後述のADGROUP_COL_IDX、QUERY_COL_IDXに反映する
  'ACCOUNT_ID', 'ACCOUNT_NAME', 'CAMPAIGN_ID', 'CAMPAIGN_NAME', 'ADGROUP_ID', 'ADGROUP_NAME',
  'SEARCH_QUERY', 'COST', 'IMPS', 'CLICKS', 'CLICK_RATE', 'AVG_CPC', 'CONVERSIONS', 'CONV_RATE', 'COST_PER_CONV', 'CONV_VALUE'
];
const ADGROUP_COL_IDX = 4;
const QUERY_COL_IDX = 6;
function main() {
  try {
    //スプシから条件取得
    const ss = validateAndGetSpreadsheet(SPREAD_SHEET_ID);
    const inputSh = validateAndGetSheet(ss, INPUT_SHEET_NAME);
    const condition = getConditionFromSS(inputSh);
    //レポート取得
    const reportData = getReport(condition);
    if (condition.OutputFlag) {
      //出力フラグオンの場合のみ出力
      let outputSh = validateAndGetSheet(ss, OUTPUT_SHEET_NAME);
      outputSh.clear();
      outputSh.getRange('A1').setValues(reportData);
      logAndMessage('対象外キーワードの候補をスプレッドシートに出力しました。\n' + SPREAD_SHEET_URL);
    }
    if (condition.AddFlag) {
      //更新フラグオンの場合のみ更新
      //入稿
      addNegativeKeywords(reportData, condition);
    }
    //通知
    validateAndSendMail();
    validateAndSendSlack();
  } catch (error) {
    MAIL_TITLE = '!!エラーが発生しました!!' + MAIL_TITLE;
    logAndMessage('エラーが発生しました！！詳細は管理画面をご確認ください。\n' + error);
    //通知
    validateAndSendMail();
    validateAndSendSlack();
    throw error;
  }
}
function validateAndGetSpreadsheet() {
  if (SPREAD_SHEET_ID === 'SPREAD_SHEET_ID' || SPREAD_SHEET_ID === '') {
    throw new Error('スプレッドシートIDを設定してください。');
  }
  return SpreadsheetApp.openById(SPREAD_SHEET_ID);
}
function validateAndGetSheet(ss, sheetName) {
  if (sheetName === '') {
    throw new Error('シート名を設定してください。');
  }
  return ss.getSheetByName(sheetName);
}
function getConditionFromSS(inputSh) {
  let condition = {};
  //1.動作設定
  const flags = getFlags(inputSh);
  condition.OutputFlag = flags.outputFlag;
  condition.AddFlag = flags.addFlag;
  //2.抽出条件
  condition.Period = getPeriod(inputSh);
  const filterAndExclude = getFiltersAndExcludeQuery(inputSh);
  condition.Filters = filterAndExclude.filters;
  condition.excludeQueryList = filterAndExclude.excludeQueryList;
  //3.関連付け対象
  const addTarget = getAddTargetCondition(inputSh);
  condition.TargetEntity = addTarget.targetEntity;
  condition.TargetNegativeListId = addTarget.targetNegativeListId;
  condition.MatchTypeForAdd = addTarget.matchTypeForAdd;
  return condition;
}
function getFlags(inputSh) {
  const ssFlgData = inputSh.getRange(SS_FLG_RANGE_STR).getValues();
  let outputFlag = ssFlgData[0][0] == 'する';
  let addFlag = ssFlgData[1][0] == 'する';
  if (!outputFlag && !addFlag) {
    throw new Error('少なくとも一つの機能（スプレッドシート出力または登録）を「する」に設定してください');
  }
  return { outputFlag, addFlag };
}
function getPeriod(inputSh) {
  const ssPeriodData = inputSh.getRange(SS_PERIOD_RANGE_STR).getValues();
  const periodDDLValue = ssPeriodData[0][0].trim();
  const days = ssPeriodData[0][1];
  const periodMapping = {
    '本日': 'TODAY',
    '昨日': 'YESTERDAY',
    '先週（月～日）': 'LAST_WEEK',
    '先週（月～金）': 'LAST_BUSINESS_WEEK',
    '過去7日間（今日を含まない）': 'LAST_7_DAYS',
    '過去14日間（今日を含まない）': 'LAST_14_DAYS',
    '過去30日間（今日を含まない）': 'LAST_30_DAYS',
    '今月（今日を含む）': 'THIS_MONTH',
    '今月（今日を含まない）': 'THIS_MONTH_EXCEPT_TODAY',
    '先月': 'LAST_MONTH',
    '全期間（アカウント開設から）': 'ALL_TIME',
    '過去Ｎ日間（今日を含まない）': 'CUSTOM_DATE'
  };
  let period = {};
  const reportDateRangeType = periodMapping[periodDDLValue];
  if (!reportDateRangeType) {
    throw new Error('期間の値はドロップダウンリストから選んでください');
  }
  period.reportDateRangeType = reportDateRangeType;
  if (reportDateRangeType == 'CUSTOM_DATE') {
    if (!days) {
      throw new Error('「過去Ｎ日間（今日を含まない）」を選択した場合は、日数を指定してください');
    }
    let startDate = new Date();
    startDate.setDate(startDate.getDate() - days - 1);
    let endDate = new Date();
    endDate.setDate(endDate.getDate() - 1);
    period.dateRange = {
      startDate: Utilities.formatDate(startDate, 'Asia/Tokyo', 'yyyyMMdd'),
      endDate: Utilities.formatDate(endDate, 'Asia/Tokyo', 'yyyyMMdd')
    };
  }
  return period;
}
function getFiltersAndExcludeQuery(inputSh) {
  let filters = [];
  let excludeQueryList = '';
  const ssConditionData = inputSh.getRange(SS_CONDITION_RANGE_STR).getValues();
  for (let i = 0; i < ssConditionData.length; i++) {
    const row = ssConditionData[i];
    const fieldName = row[0];
    const ssValue = row[1].trim();
    const condition = row[2];
    if (ssValue.trim() == '' || condition == '指定しない') {
      //事前に空チェック
      continue;
    }
    if (fieldName.includes('ID')) {
      //ID系
      const ids = ssValue.split(',').map(id => id.trim());
      filters.push(createFilter(fieldName, 'IN', ids));
    } else if (fieldName == '検索クエリー') {
      if (condition == 'を含まない') {
        const exludeQueries = ssValue.split(',').map(query => query.trim());
        excludeQueryList = exludeQueries;
      } else {
        filters.push(createFilter(fieldName, getFilterOperator(condition, fieldName), [ssValue]));//値はそのままでOK
      }
    } else if (fieldName == '検索クエリーのマッチタイプ' || fieldName == 'キーワード/対象外キーワード登録状況') {
      if (ssValue != '指定しない') {
        if (IS_DAS_CAMPAIGN) {
          throw new Error('動的検索型連動広告では、' + fieldName + 'を指定できません。');
        }
        filters.push(createFilter(fieldName, 'EQUALS', [ssValue]));
      }
    } else {
      filters.push(createFilter(fieldName, getFilterOperator(condition, fieldName), [ssValue]));
    }
  }
  if (filters.length > 6) {
    //フィルタは6つまで
    throw new Error('条件は6つ以内で指定してください');
  }
  return { filters: filters, excludeQueryList: excludeQueryList };
}
function createFilter(fieldName, filterOperator, values) {
  return {
    field: getFieldName(fieldName), filterOperator: filterOperator, values: values
  };
}
function getAddTargetCondition(inputSh) {
  const ssTargetData = inputSh.getRange(SS_TARGET_RANGE_STR).getValues();
  let targetEntity = ssTargetData[0][0];
  let targetNegativeListId = getNegativeListId(ssTargetData[0][0], ssTargetData[0][1]);
  let matchTypeForAdd = getMatchTypeStr(ssTargetData[1][1]);
  return { targetEntity, targetNegativeListId, matchTypeForAdd };
}
function getFieldName(fieldName) {
  const mapping = {
    'インプレッション数': 'IMPS',
    'クリック数': 'CLICKS',
    'クリック率': 'CLICK_RATE',
    '平均CPC': 'AVG_CPC',
    'コンバージョン数': 'CONVERSIONS',
    'コンバージョン率': 'CONV_RATE',
    'コスト/コンバージョン数': 'COST_PER_CONV',
    'コスト': 'COST',
    'コンバージョンの価値': 'CONV_VALUE',
    '検索クエリーのマッチタイプ': 'SEARCH_QUERY_MATCH_TYPE',
    '検索クエリー': 'SEARCH_QUERY',
    'キャンペーンID': 'CAMPAIGN_ID',
    '広告グループID': 'ADGROUP_ID',
    'キーワードID': 'KEYWORD_ID',
    'キーワード/対象外キーワード登録状況': 'QUERY_TARGETING_STATUS'
  };
  if (!mapping.hasOwnProperty(fieldName)) {
    throw new Error('B列に想定されていない値「' + fieldName + '」が入力されています');
  }
  return mapping[fieldName];
}
function getFilterOperator(filterStr, fieldName) {
  const operatorMapping = {
    'と等しい': 'EQUALS',
    'と等しくない': 'NOT_EQUALS',
    'より大きい': 'GREATER_THAN',
    '以上': 'GREATER_THAN_EQUALS',
    'より小さい': 'LESS_THAN',
    '以下': 'LESS_THAN_EQUALS',
    'を含む': 'CONTAINS',
    'を含まない': 'NOT_CONTAINS',//実際のレポートのENUMにはない
    'いずれかに一致': 'IN'
  };
  if (!operatorMapping.hasOwnProperty(filterStr)) {
    throw new Error(fieldName + 'の条件に想定されていない値「' + filterStr + '」が入力されています');
  }
  return operatorMapping[filterStr];
}
function getMatchTypeStr(matchType) {
  const matchTypes = {
    '完全一致': 'EXACT',
    '部分一致': 'BROAD',
    'フレーズ一致': 'PHRASE'
  };
  if (!matchTypes.hasOwnProperty(matchType)) {
    throw new Error('マッチタイプの値はドロップダウンリストから選んでください');
  }
  return matchTypes[matchType];
}
function getNegativeListId(targetEntity, ssValue) {
  let negativeListId = 0;
  if (targetEntity == '対象外キーワードリストID') {
    if (ssValue != '' && Number.isInteger(Number(ssValue))) {
      negativeListId = Number(ssValue);
    } else {
      throw new Error('対象外キーワードリストIDは数字で入力してください');
    }
  }
  return negativeListId;
}
function addNegativeKeywords(reportData, condition) {
  //operand作成
  const operands = createOperands(reportData, condition);
  Logger.log(operands.length + '件のoperandを作成しました');
  //更新
  if (operands.length > 0) {
    add(operands, condition.TargetEntity);
  } else {
    logAndMessage('追加対象の対象外キーワードは0件でした。');
  }
}
function getReport(condition) {
  const selectedReportType = IS_DAS_CAMPAIGN ? 'KEYWORDLESS_QUERY' : 'SEARCH_QUERY';
  const reportFields = IS_DAS_CAMPAIGN ? KEYWORDLESS_QUERY_FIELD : SEARCH_QUERY_FIELD;
  let operand = {
    accountId: AdsUtilities.getCurrentAccountId(),// 実行中のアカウントを取得します
    reportType: selectedReportType,
    fields: reportFields,
    filters: condition.Filters,
    reportDateRangeType: condition.Period.reportDateRangeType,
    reportSkipColumnHeader: 'FALSE',
  };
  if (condition.Period.reportDateRangeType == 'CUSTOM_DATE') {
    operand.dateRange = condition.Period.dateRange;
  }
  Logger.log('次の条件で検索クエリーレポートを取得します：' + JSON.stringify(operand));
  const reportData = AdsUtilities.getSearchReport(operand).reports[0].rows;
  if (condition.excludeQueryList == '') {
    return reportData;
  }
  //検索クエリ除外はfilter不可なのでスクリプト側で除外
  let excludedReportData = [];
  for (let i = 0; i < reportData.length; i++) {
    const row = reportData[i];
    let exclude = false;
    for (let j = 0; j < condition.excludeQueryList.length; j++) {
      const excludeQuery = condition.excludeQueryList[j];
      if (row[QUERY_COL_IDX].includes(excludeQuery)) {
        //1つでも合致したら除外
        exclude = true;
        break;
      }
    }
    if (!exclude) {
      //1つも合致しない検索クエリのみ出力対象とする
      excludedReportData.push(row);
    }
  }
  return excludedReportData;
}
function createOperands(reportData, condition) {
  let operands = [];
  let duplicateChkArr = [];//重複チェック用リスト
  for (let i = 1; i < reportData.length; i++) {//ヘッダはとばす
    const row = reportData[i];
    const addQuery = row[QUERY_COL_IDX];
    //重複チェック
    if (duplicateChkArr.includes(addQuery)) {
      //既にあれば、その旨ログに出力して次へ
      Logger.log('「' + addQuery + '」は複数あるため、2つ目以降は追加対象から除外します');
      continue;
    }
    duplicateChkArr.push(addQuery);
    let operand = { accountId: accountId };
    if (condition.TargetEntity == '対象外キーワードリストID') {
      operand.sharedListId = condition.TargetNegativeListId;
      operand.text = addQuery;
      operand.keywordMatchType = condition.MatchTypeForAdd;
      operand.use = 'NEGATIVE';
    } else {
      //CMPorADG共通
      operand.campaignId = row[2];
      operand.use = 'NEGATIVE';
      operand.criterion = {
        criterionType: 'KEYWORD',
        keyword: {
          keywordMatchType: condition.MatchTypeForAdd,
          text: addQuery
        }
      };
      if (condition.TargetEntity == '検索クエリーが発生した広告グループ') {
        //ADGのみ
        operand.adGroupId = row[ADGROUP_COL_IDX];
      }
    }
    operands.push(operand);
  }
  return operands;
}
function add(operands, targetEntity) {
  const targetService = TARGET_SERVICE[targetEntity];
  let succeedCnt = 0;
  let errorCnt = 0;
  const batchSize = 200; // 1回のバッチ処理における最大件数:低い方に合わせる
  for (let i = 0; i < operands.length; i += batchSize) {
    const batchOperands = operands.slice(i, i + batchSize);
    const addResult = targetService.add({
      accountId: accountId,
      operand: batchOperands
    }).rval;
    for (let j = 0; j < addResult.values.length; j++) {
      if (addResult.values[j].operationSucceeded) {
        succeedCnt++;
      } else {
        const error = addResult.values[j].errors[0];
        if (error.code === 'D0001') {
          Logger.log(
            error.details[0].requestKey + ':' + error.details[0].requestValue +
            'において、対象外キーワード「' + error.details[1].requestValue +
            '（マッチタイプ:' + batchOperands[j].keywordMatchType + '）」が重複しています'
          );
        } else if (error.code === 'F0001') {
          Logger.log('対象外キーワード「' + error.details[0].requestValue + '」に使用できない文字が含まれています');
        } else if (error.code === '210517') {
          Logger.log('対象外キーワードが10語を超えています。「' + error.details[0].requestValue + '」');
        } else if (error.code === '210510') {
          Logger.log('指定されたキーワード、マッチタイプは既に登録されています。「' +
            error.details[0].requestValue + '（' + error.details[0].requestValue + '）」');
        }
        errorCnt++;
      }
    }
    Logger.log(batchOperands.length + '件の更新処理が終わりました');
  }
  logAndMessage('対象外キーワードを' + targetEntity + 'に' + succeedCnt + '件追加しました（うちエラー：' + errorCnt + '件）');
}
//ログ、メールなどに流すテキスト
function logAndMessage(text) {
  Logger.log(text);
  TEXT_MESSAGE_ARRAY.push(text);
}
function validateAndSendMail() {
  if (FLAG_MAIL) {
    if (MAIL_TO.length < 1 || !MAIL_TO.every(str => typeof str === 'string' && /^[a-zA-Z0-9]+$/.test(str))) {
      throw new Error('Yahoo! JAPANビジネスIDを設定してください。メール送信前までに実行された処理は完了しています。');
    }
    MailApp.sendEmail({
      to: MAIL_TO,
      subject: '【アカウントID：' + accountId + '】' + MAIL_TITLE,
      body: TEXT_MESSAGE_ARRAY.join('\n'),
    });
  }
}
function validateAndSendSlack() {
  if (FLAG_SLACK) {
    if (URL_FETCH_APP === 'SLACK_WEBHOOK_URL' || URL_FETCH_APP === '') {
      throw new Error('SlackのWebhook URLを設定してください。Slack送信前までに実行された処理は完了しています。');
    }
    UrlFetchApp.fetch(URL_FETCH_APP, {
      method: 'post',
      contentType: 'application/json',
      payload: JSON.stringify({
        text: TEXT_MESSAGE_ARRAY.join('\n'),
      }),
    });
  }
}