[GAS] How to get operational data of Yahoo Display Ads into a spreadsheet by yourself (v9 compatible)

2022年12月11日

This is a method to get operational data such as impressions and conversions of Yahoo Display Ads in a spreadsheet by yourself. This time we will use GAS (GoogleAppsScript)

Click here for other advertising media

[GAS] How to get operational data of Yahoo Ads into a spreadsheet by yourself (v7 compatible)
[Google ADS]Obtain operational reports from all MCC accounts
【GAS】How to get operational data for TikTok ads

Register Yahoo Ad Application

Access the Yahoo! Advertising API Management Tool and log in with your business ID.

Fill in the required information on the registration screen and select confirm. Enter “oob" for the redirect URI.

After registration is complete, the application information will be displayed as shown below, so note the Client ID and Client Secret.

Obtaining an Authentication Code

Create the following URL

CLIENT_ID: Client ID noted at the beginning of this document
REDIRECT_URI: URL set in the application registration
THIS_VALUE_SHOULD_BE_UNIQUE_PER_REQUEST: Arbitrary string (*Random number is OK)

https://biz-oauth.yahoo.co.jp/oauth/v1/authorize?response_type=code
&client_id=CLIENT_ID
&redirect_uri=REDIRECT_URI
&scope=yahooads
&state=THIS_VALUE_SHOULD_BE_UNIQUE_PER_REQUEST

Access the above in your browser and approve the permissions and you will see the authorization code.

https://developer.yahoo.co.jp/yconnect/v1/server_app/explicit/authorization.html

Obtaining access and refresh tokens

Execute the following request from a terminal, etc.
CLIENT_ID: Client ID that you wrote down at the beginning of this document
CLIENT_SECRET: The client secret that you wrote down at the beginning of this document.
REDIRECT_URI: URL set in the application registration
AUTH_CODE: Authorization code obtained earlier

curl -X GET 
https://biz-oauth.yahoo.co.jp/oauth/v1/token?grant_type=authorization_code
&client_id=CLIENT_ID
&client_secret=CLIENT_SECRET
&redirect_uri=REDIRECT_URI
&code=AUTH_CODE

The access token and refresh token are returned as shown below, and the values of each are written down.

It can also be accessed with a browser.

Since the access token expires in one hour, a new access token must be issued each time using a refresh token.

Creating Spreadsheets and GAS

spreadsheet and create a “db" sheet.

Create a script from “Tools" -> “Scripts". The code is as follows

function main() {
    
    const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('db');
 
    const clientId = "****"; 
    const clientSecret = "****"; 
    const refreshToken = "****"; 
    const accountId = "****"; 


      
  const loginResponse = yahooAdLogin(clientId, clientSecret, refreshToken);
  const accessToken = loginResponse.access_token;

  // Create a YAHOO ad report and get the ID of the job created
  const createReportResponse = yahooAdCreateDisplayReport(accessToken, accountId);
  const reportJobIds = createReportResponse.rval.values.map(item => Number(item.reportDefinition.reportJobId));

  reportJobIds.forEach(reportJobId => {
    // レポートの作成が完了するまでループ
    while (!yahooAdcheckDisplayReport(accessToken, accountId, reportJobId)) {
      // レポート作成完了まで10秒間待つ
      Utilities.sleep(10000);
    }
    // Get report
    const downloadResponse = yahooAdDownloadDisplayReport(accessToken, accountId, reportJobId);

    let target_row = ss.getRange(ss.getMaxRows(), 2).getNextDataCell(SpreadsheetApp.Direction.UP).getRow() + 1;
    ss.getRange(target_row, 1, downloadResponse.length, downloadResponse[0].length).setValues(downloadResponse)

  });
}

// Get Access Token
function yahooAdLogin(clientId, clientSecret, refreshToken) {
  const url = "https://biz-oauth.yahoo.co.jp/oauth/v1/token";
  const httpResponse = UrlFetchApp.fetch(url, {
    method: 'get',
    payload: {
      grant_type: "refresh_token",
      client_id: clientId,
      client_secret: clientSecret,
      refresh_token: refreshToken,
    },
  });
  const status = httpResponse.getResponseCode();
  if (status !== 200) {
    throw 'HttpRequestError';
  }
  const response = JSON.parse(httpResponse.getContentText());
  return response;
}

// Create ADS report
function yahooAdCreateDisplayReport(accessToken, accountId) {
  const url = "https://ads-display.yahooapis.jp/api/v9/ReportDefinitionService/add";
  const httpResponse = UrlFetchApp.fetch(url, {
    method: 'post',
    // muteHttpExceptions : true,
    contentType: 'application/json',
    headers: {
      'Authorization': `Bearer ${accessToken}`,
    },
    payload: JSON.stringify({
      accountId: accountId,
      operand: [
        {
          fields: [
            "ACCOUNT_ID",
            "ACCOUNT_NAME",
            "CAMPAIGN_ID",
            "CAMPAIGN_NAME",
            // "ADGROUP_ID",
            // "ADGROUP_NAME",
            // "AD_ID",
            // "AD_NAME",
            // "AD_TYPE",
            // "URL_ID",
            // "URL_NAME",
            // "PREF_ID",
            // "PREF_NAME",
            // "CITY_ID",
            // "CITY_NAME",
            // "WARD_ID",
            // "WARD_NAME",
            // "GENDER",
            // "AGE",
            // "MONTH",
            "DAY",
            "CLICK", // クリック数
            "CONVERSIONS", // コンバージョン数
            "COST", // 費用
            "IMPS", // 表示回数
          ],
          reportCompressType: "NONE", // レポートファイルは圧縮しない
          // reportDateRangeType: "YESTERDAY",

          // 期間指定するときは"CUSTOM_DATE"にする
          reportDateRangeType: "CUSTOM_DATE",
          dateRange: {
            startDate: "20211101",
            endDate: "20211130",
          }, 
          reportDownloadEncode: "UTF8",
          reportDownloadFormat: "CSV",
          reportLanguage: "JA",
        }
      ],
    }),
  });
  const status = httpResponse.getResponseCode();
  if (status !== 200) {
    throw 'HttpRequestError';
  }
  const response = JSON.parse(httpResponse.getContentText());
  return response;
}




function yahooAdDownloadDisplayReport(accessToken, accountId, reportJobId) {
  const url = "https://ads-display.yahooapis.jp/api/v9/ReportDefinitionService/download";
  const httpResponse = UrlFetchApp.fetch(url, {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'Authorization': `Bearer ${accessToken}`,
    },
    payload: JSON.stringify({
      accountId,
      reportJobId,
    }),
  });
  const status = httpResponse.getResponseCode();
  if (status !== 200) {
    throw 'HttpRequestError';
  }
  const response = httpResponse.getContentText();
  let lines = response.split('n');
  if (lines.length === 1) {
    return [];
  }

  
  lines = lines.slice(0, -1).map(elm => elm.split(","))
  return lines
  
}

function yahooAdcheckDisplayReport(accessToken, accountId, reportJobId) {
  const url = "https://ads-display.yahooapis.jp/api/v9/ReportDefinitionService/get";
  const httpResponse = UrlFetchApp.fetch(url, {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'Authorization': `Bearer ${accessToken}`,
    },
    payload: JSON.stringify({
      accountId,
      "reportJobIds": [reportJobId],
    }),
  });
  const status = httpResponse.getResponseCode();
  if (status !== 200) {
    throw 'HttpRequestError';
  }
  const json = JSON.parse(httpResponse)
  console.log(json.rval.values[0].reportDefinition)

  if (json.rval.values[0].reportDefinition.reportJobStatus == "COMPLETED") {
    return true
  } else if (json.rval.values[0].reportDefinition.reportJobStatus == "IN_PROGRESS") {
    return false
  } else {
    throw 'HttpRequestError';
  }

}

reference

Yahoo!ADS

https://github.com/yahoojp-marketing/ads-display-api-documents/blob/master/bestpractice/ja/display_ads_report.md

API reference

https://ads-developers.yahoo.co.jp/reference/ads-display-api/v9/ReportDefinitionService/

GAS

Posted by Next-k