Adwords Scriptで日次レポートをメール送信するスクリプトを書いた

「書いた」と言っても、公式のサンプルソリューションにあるスクリプトを改変したものになるんですけどね。

 

このAdwordsスクリプトを使って得られるもの

前日のAdwordsアカウント成果の概要をメールで自動送信することができます。
情報を見に行く手間が省けるため、

1. チームで成果を共有出来る。
2. 習慣化しやすい

というのが最も大きいメリットだと思います。

 

このAdwordsスクリプトを作った理由

公式のサンプルソリューションのスクリプトを改変して、しばらくの間運用していたのですが、少しずつ課題が出てきたためです。

また、先日Adwordsスクリプト使おうぜ！みたいな記事を書いた手前、実際のサンプル的なものも上げとかないといけない気分になったからですｗ

公式のAdwords Scriptの機能

サンプルソリューションとして提供されているアドワーズスクリプトが提供する機能は下記のとおりです。

• 日次で”アカウント”の下記要素をGoogleスプレッドシートに記録する
  • コスト
  • 平均クリック単価（CPC）
  • クリック率（CTR）
  • 平均掲載順位
  • 表示回数（Impression)
  • クリック数
• htmlメールで「前日」「前々日」「7日前」のデータを送信する

このスクリプトを毎日朝8時などに自動で起動するようにしておけば、前日のおおよそのアカウント健全度がわかるようになっています。

予算が少なく、検索数も少ないようなアカウントなどで、最低限の予算管理をしたい場合、このスクリプトを使うだけで、管理画面に入る手間を減らせるメリットがあります。

アカウント単位という弊害

アカウント全体の予算感を把握するだけなら、サンプルスクリプトで何ら問題がありませんでした。
ですが、アカウントによっては「平均掲載順位」や「CTR」の数値を追っておきたいものも少なくありません。

しかし、平均掲載順位やCTR、Impressionなどは「検索連動広告」と「ディスプレイ広告」で全く違うデータになってしまいます。
つまり、1アカウント内で検索連動とディスプレイ両方に出稿しているアカウントにおいて、平均掲載順位やCTRのデータは”正しい”といえる平均値ではないものが返って来てしまうことが課題として噴出しました。

公式スクリプトからの改変点

公式で紹介されているアドワーズスクリプトから、下記の点を改変しております。

表示項目の追加

表示項目に

• コンバージョンに至ったクリック
• CPA

を追加。

グラフの変更・追加

要素の追加に合わせて、スプレッドシートにグラフの追加。
また、小規模アカウントは日によってグラフの数値が変動しすぎるため、７日の移動平均でのグラフ描画に変更。

データをネットワークごとに分割

「アカウントすべてのデータ」を「ネットワークごと」に分割することで、検索のデータとディスプレイネットワークのデータを分けて表示するように変更。

メールにグラフデータを追加

メールで概要把握が出来るのは素晴らしかったのですが、細かい数字の変動を見てもよくわからないので、上記の移動平均グラフをメールに添付するように変更。

以上、４点の改変を加えております。

日次レポートスクリプトの使い方

１．Googleスプレッドシートをテンプレートから作成

下記URLからテンプレートであるスプレッドシートを開き「コピーを作成」を選択。

２．スプレッドシートの編集

「コピーを作成」で、自分のGoogleドライブに保存したスプレッドシートの編集を行います。

ファイル名を変更。

わかりやすいファイル名に変更しておいたほうが良いでしょう。
名前はなんでもかまいません。

メール送信機能を利用する場合、メールの宛先を登録。

メールの宛先をスプレッドシートに記入します。
CCも登録出来ますので、複数人に送る場合にご利用ください。

また、CCが複数人いる場合、カラム内にカンマで区切ってメールアドレスを入力ください。

３．Google Adwordsのスクリプト作成

[Adwords > アカウント > 一括処理 > スクリプト] のスクリプトの作成と管理をクリック。

「+スクリプト」ボタンを押して、新規スクリプト作成画面へ。

下記スクリプトをコピー＆貼り付け。

// スプレッドシートのURLを入力して下さい
var SPREADSHEET_URL = "";

var settings = {
  "Summary" : {
    "sheet_name": "Summary",    //スプレッドシートの検索＋ディスプレイ結果シート名
  },  
  "Search Network" : {
    "sheet_name": "Search Network", //スプレッドシートの検索連動広告結果シート名
    "graphs" : {
      "impression" : "ここにグラフ画像（検索連動広告・表示回数）のURLを入力",
      "click" : "ここにグラフ画像（検索連動広告・クリック数）のURLを入力",
      "ctr" : "ここにグラフ画像（検索連動広告・CTR）のURLを入力",
      "avg_cpc" : "ここにグラフ画像（検索連動広告・CPC）のURLを入力",
      "avg_position" : "ここにグラフ画像（検索連動広告・平均掲載順位）のURLを入力",
      "cost" : "ここにグラフ画像（検索連動広告・費用）のURLを入力",
      "conversion_click" : "ここにグラフ画像（検索連動広告・コンバージョンクリック）のURLを入力",
      "cpa": "ここにグラフ画像（検索連動広告・CPA）のURLを入力"
    }
  },
  "Display Network" : {
    "sheet_name": "Display Network",    //スプレッドシートのディスプレイ広告結果シート名
    "graphs" : {
      "impression" : "ここにグラフ画像（ディスプレイ・表示回数）のURLを入力",
      "click" : "ここにグラフ画像（ディスプレイ・クリック数）のURLを入力",
      "ctr" : "ここにグラフ画像（ディスプレイ・CTR）のURLを入力",
      "avg_cpc" : "ここにグラフ画像（ディスプレイ・CPC）のURLを入力",
      "cost" : "ここにグラフ画像（ディスプレイ・費用）のURLを入力",
      "conversion_click" : "ここにグラフ画像（ディスプレイ・コンバージョンクリック）のURLを入力",
      "cpa": "ここにグラフ画像（ディスプレイ・CPA）のURLを入力"
    },
  }
};

// グラフの横幅
var graph_w = 400;

// 日付を明示指定する場合入力。通常は空欄でOK。
var start_date = "";
var end_date = "";
var last_check_date = "";

/**
* メイン関数
*/
function main() {
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  // スプレッドシートのアカウントIDをAdwordsのアカウントIDで上書き
  spreadsheet.getRangeByName("account_id_report").setValue(AdWordsApp.currentAccount().getCustomerId());

  var email = spreadsheet.getRangeByName("email").getValue();
  var email_cc = spreadsheet.getRangeByName("email_cc").getValue();

  // データの最終更新日をサーチ・ディスプレイそれぞれスプレッドシートから取得する
  last_check_date = spreadsheet.getRangeByName("last_check").getValue();

  var check_date = getStartDate(start_date, last_check_date);
  var finish_date = getEndDate(end_date);

  var results = {
    "Search Network" : [],
    "Display Network" : [],
    "Summary" : []
  };

  // 開始日から終了日までループさせ、キャンペーンのデータを取得する
  while(check_date.getTime() <= finish_date.getTime()) {
    var report_data = getReportRowForDate(check_date);
     
    for(k in report_data) {
      results[k].push([new Date(check_date), report_data[k]['Cost'], report_data[k]['AverageCpc'], report_data[k]['Ctr'],
                       report_data[k]['AveragePosition'], report_data[k]['Impressions'], report_data[k]['Clicks'], report_data[k]['CostPerConversion'], report_data[k]['Conversions']]);
    }
    // 日付を進める
    check_date.setDate(check_date.getDate() + 1);
  }
   
  for(k in settings){
    Logger.log(results[k]);
    // スプレッドシートのシートをシート名で取得
    var access = new SpreadsheetAccess(SPREADSHEET_URL, settings[k]["sheet_name"]);
    // 空白行を走査し、書き込めるかどうかをチェックする
    var emptyRow = access.findEmptyRow(6, 2);
    if (emptyRow < 0) {
      access.addRows(results[k].length);
      emptyRow = access.findEmptyRow(6, 2);
    }
    // 空白行にデータを書き込む
    access.writeRows(results[k], emptyRow, 2);
  }

  // メールの送信先が定義されていればレポートメールを送信する
  if (email) {
    sendEmail(email,email_cc);
  }

  //　スプレッドシートの最終取得日を更新する
  spreadsheet.getRangeByName("last_check").setValue(finish_date);
}

/**
* データの取得開始日を取得する。
* 優先度: 1．明示指定した開始日。 2.スプレッドシートの最終更新日の翌日。 3.昨日
*/
function getStartDate(start_date, last_check_date) {
  var date = "";
  // 最終更新日が未定義なら昨日、定義済みなら最終日の翌日からデータ取得を開始する
  if(start_date !== "") {
    date = new Date(start_date);
  } else {
    if (last_check_date === "") {
      date = new Date(getYesterday());
    } else {
      date = new Date(last_check_date);
      date.setDate(date.getDate() + 1);
    }
  }
  return date;
}
  
/**
* データの取得終了日を取得する。
* 優先度: 1.明示指定した終了日。 2.昨日
*/
function getEndDate(end_date) {
  var date = "";
  if(end_date !== "") {
    date = new Date(end_date);
  } else {
    date = new Date(getYesterday());
  }
  return date;
}
  
/**
* 指定日のレポートデータを取得する
*/
function getReportRowForDate(date, network) {
  var accountDate = new Date(Utilities.formatDate(date, "PST", "MMM dd,yyyy HH:mm:ss"));
  var dateString = Utilities.formatDate(accountDate, "PST", "yyyyMMdd");
  return getReportRowForDuring(dateString + "," + dateString);
}
  
/**
* レポートデータを取得し、それぞれの配列に追加する
* @param during 取得指定範囲日
*/
function getReportRowForDuring(during) {
  // AWQLで取得データを定義。https://developers.google.com/adwords/api/docs/guides/awql
  var report = AdWordsApp.report(
    "SELECT AdNetworkType1, Cost, AverageCpc, Ctr, AveragePosition, Impressions, Clicks, CostPerConversion, Conversions " +
    "FROM ACCOUNT_PERFORMANCE_REPORT " +
    "DURING " + during);
   
  var rows = report.rows();
  var results = {};
  var networks = [];
  while (rows.hasNext()) {
    var row = rows.next();
    networks.push(row["AdNetworkType1"]);
    try {
      results[row["AdNetworkType1"]] = {};
      results[row["AdNetworkType1"]]["Ctr"] = row["Ctr"];
      results[row["AdNetworkType1"]]["AverageCpc"] = row["AverageCpc"];
      results[row["AdNetworkType1"]]["AveragePosition"] = row["AveragePosition"];
      results[row["AdNetworkType1"]]["Impressions"] = toInteger(row["Impressions"]);
      results[row["AdNetworkType1"]]["Clicks"] = toInteger(row["Clicks"]);
      results[row["AdNetworkType1"]]["Cost"] = toInteger(row["Cost"]);
      results[row["AdNetworkType1"]]["Conversions"] = toInteger(row["Conversions"]);
      results[row["AdNetworkType1"]]["CostPerConversion"] = toInteger(row["CostPerConversion"]);
    } catch (ex) {}  
  }
   
  // ネットワークすべての値を加算して、サマリーを算出する
  if(networks.length > 0) {
    results["Summary"] = {"Impressions": 0, "Clicks": 0, "Cost": 0, "Conversions": 0};
    for(k in networks) {
      results["Summary"]["Impressions"] += results[networks[k]]["Impressions"];
      results["Summary"]["Clicks"] += results[networks[k]]["Clicks"];
      results["Summary"]["Cost"] += results[networks[k]]["Cost"];
      results["Summary"]["Conversions"] += results[networks[k]]["Conversions"];
    }
    if(results["Summary"]["Impressions"] > 0 && results["Summary"]["Clicks"]) {
        results["Summary"]["Ctr"] = parseFloat(results["Summary"]["Clicks"] / results["Summary"]["Impressions"] * 100).toFixed(2) + "%";
        results["Summary"]["AverageCpc"] = parseFloat(results["Summary"]["Cost"] / results["Summary"]["Clicks"]).toFixed(2);
        if(results["Summary"]["Conversions"] > 0){
          results["Summary"]["CostPerConversion"] = toInteger(results["Summary"]["Cost"] / results["Summary"]["Conversions"]);
        } else {
          results["Summary"]["CostPerConversion"] = 0;
        }
    } else {
        results["Summary"]["Ctr"] = 0;
        results["Summary"]["AverageCpc"] = 0;
        results["Summary"]["CostPerConversion"] = 0;
    }
  }
   
  return results;
}
  
/**
* レポートメールの送信を行う。
*/
function sendEmail(email,email_cc) {
  // 前日データを取得
  var target_day = getYesterday();
  var yesterday_report = getReportRowForDate(target_day);
  // 一昨日データ（前日の1日前）を取得
  target_day.setDate(target_day.getDate() - 1);
  var two_days_ago_report = getReportRowForDate(target_day);
  // 先週データ（2日前の5日前）を取得
  target_day.setDate(target_day.getDate() - 5);
  var week_ago_report = getReportRowForDate(target_day);
  
  var yesterday = getYesterday();
  var format_day = yesterday.getFullYear() + '/' + (yesterday.getMonth() + 1) + '/' + yesterday.getDate();
     
  // 画像を取得し、Blob化する（インライン画像埋め込みのため）
  var s_impression_blob = UrlFetchApp.fetch(settings["Search Network"]["graphs"]["impression"]).getBlob().setName('s_impression_blob');
  var s_click_blob = UrlFetchApp.fetch(settings["Search Network"]["graphs"]["click"]).getBlob().setName('s_click_blob');
  var s_ctr_blob = UrlFetchApp.fetch(settings["Search Network"]["graphs"]["ctr"]).getBlob().setName('s_ctr_blob');
  var s_avg_cpc_blob = UrlFetchApp.fetch(settings["Search Network"]["graphs"]["avg_cpc"]).getBlob().setName('s_avg_cpc_blob');
  var s_avg_pos_blob = UrlFetchApp.fetch(settings["Search Network"]["graphs"]["avg_position"]).getBlob().setName('s_avg_pos_blob');
  var s_cost_blob = UrlFetchApp.fetch(settings["Search Network"]["graphs"]["cost"]).getBlob().setName('s_cost_blob');
  var s_conversion_blob = UrlFetchApp.fetch(settings["Search Network"]["graphs"]["conversion_click"]).getBlob().setName('s_conversion_blob');
  var s_cpa_blob = UrlFetchApp.fetch(settings["Search Network"]["graphs"]["cpa"]).getBlob().setName('s_cpa_blob');
   
  var d_impression_blob = UrlFetchApp.fetch(settings["Display Network"]["graphs"]["impression"]).getBlob().setName('d_impression_blob');
  var d_click_blob = UrlFetchApp.fetch(settings["Display Network"]["graphs"]["click"]).getBlob().setName('d_click_blob');
  var d_ctr_blob = UrlFetchApp.fetch(settings["Display Network"]["graphs"]["ctr"]).getBlob().setName('d_ctr_blob');
  var d_avg_cpc_blob = UrlFetchApp.fetch(settings["Display Network"]["graphs"]["avg_cpc"]).getBlob().setName('d_avg_cpc_blob');
  var d_cost_blob = UrlFetchApp.fetch(settings["Display Network"]["graphs"]["cost"]).getBlob().setName('d_cost_blob');
  var d_conversion_blob = UrlFetchApp.fetch(settings["Display Network"]["graphs"]["conversion_click"]).getBlob().setName('d_conversion_blob');
  var d_cpa_blob = UrlFetchApp.fetch(settings["Display Network"]["graphs"]["cpa"]).getBlob().setName('d_cpa_blob');
  
  var html = [];
  html.push(
    "<html>",
      "<body>",
    "<h1><a href='" + SPREADSHEET_URL + "'>" + AdWordsApp.currentAccount().getName() + " - アカウントレポート</a></h1>",
        "<table width=800 cellpadding=0 border=0 cellspacing=0>",
          "<tr>",
            "<td colspan=2 align=right>",
              "<div style='font: italic normal 10pt Times New Roman, serif; margin: 0; color: #666; padding-right: 5px;'>Powered by AdWords Scripts</div>",
            "</td>",
          "</tr>",
          "<tr bgcolor='#3c78d8'>",
            "<td width=500>",
              "<div style='font: normal 18pt verdana, sans-serif; padding: 3px 10px; color: white'>サマリー</div>",
            "</td>",
            "<td align=right>",
              "<div style='font: normal 14pt verdana, sans-serif; padding: 3px 10px; color: white'>", AdWordsApp.currentAccount().getName() + "<br>" + AdWordsApp.currentAccount().getCustomerId(), "</div>",
            "</td>",
            "</tr>",
          "</table>",
          "<table width=800 cellpadding=0 border=0 cellspacing=0>",
            "<tr bgcolor='#ddd'>",
              "<td></td>",
              "<td style='font: 12pt verdana, sans-serif; padding: 5px 0px 5px 5px; background-color: #ddd; text-align: left'>前日</td>",
              "<td style='font: 12pt verdana, sans-serif; padding: 5px 0px 5px 5px; background-color: #ddd; text-align: left'>一昨日</td>",
              "<td style='font: 12pt verdana, sans-serif; padding: 5px 0px 5x 5px; background-color: #ddd; text-align: left'>先週</td>",
            "</tr>",
            emailRow('Cost', 'Cost', yesterday_report["Summary"], two_days_ago_report["Summary"], week_ago_report["Summary"]),
            emailRow('Average Cpc', 'AverageCpc', yesterday_report["Summary"], two_days_ago_report["Summary"], week_ago_report["Summary"]),
            emailRow('Ctr', 'Ctr', yesterday_report["Summary"], two_days_ago_report["Summary"], week_ago_report["Summary"]),
            emailRow('Impressions', 'Impressions', yesterday_report["Summary"], two_days_ago_report["Summary"], week_ago_report["Summary"]),
            emailRow('Clicks', 'Clicks', yesterday_report["Summary"], two_days_ago_report["Summary"], week_ago_report["Summary"]),
            emailRow('CostPerConversion', 'CostPerConversion', yesterday_report["Summary"], two_days_ago_report["Summary"], week_ago_report["Summary"]),
            emailRow('Conversions', 'Conversions', yesterday_report["Summary"], two_days_ago_report["Summary"], week_ago_report["Summary"]),
        "</table>",
        "<hr />",
          "<table width=800 cellpadding=0 border=0 cellspacing=0>",
          "<tr bgcolor='#3c78d8'>",
            "<td colspan=2>",
              "<div style='font: normal 18pt verdana, sans-serif; padding: 3px 10px; color: white'>検索</div>",
            "</td>",
            "</tr>",
          "</table>",
          "<table width=800 cellpadding=0 border=0 cellspacing=0>",
            "<tr bgcolor='#ddd'>",
              "<td></td>",
              "<td style='font: 12pt verdana, sans-serif; padding: 5px 0px 5px 5px; background-color: #ddd; text-align: left'>前日</td>",
              "<td style='font: 12pt verdana, sans-serif; padding: 5px 0px 5px 5px; background-color: #ddd; text-align: left'>一昨日</td>",
              "<td style='font: 12pt verdana, sans-serif; padding: 5px 0px 5x 5px; background-color: #ddd; text-align: left'>先週</td>",
            "</tr>",
            emailRow('Cost', 'Cost', yesterday_report["Search Network"], two_days_ago_report["Search Network"], week_ago_report["Search Network"]),
            emailRow('Average Cpc', 'AverageCpc', yesterday_report["Search Network"], two_days_ago_report["Search Network"], week_ago_report["Search Network"]),
            emailRow('Ctr', 'Ctr', yesterday_report["Search Network"], two_days_ago_report["Search Network"], week_ago_report["Search Network"]),
            emailRow('Average Position', 'AveragePosition', yesterday_report["Search Network"], two_days_ago_report["Search Network"], week_ago_report["Search Network"]),
            emailRow('Impressions', 'Impressions', yesterday_report["Search Network"], two_days_ago_report["Search Network"], week_ago_report["Search Network"]),
            emailRow('Clicks', 'Clicks', yesterday_report["Search Network"], two_days_ago_report["Search Network"], week_ago_report["Search Network"]),
            emailRow('CostPerConversion', 'CostPerConversion', yesterday_report["Search Network"], two_days_ago_report["Search Network"], week_ago_report["Search Network"]),
            emailRow('Conversions', 'Conversions', yesterday_report["Search Network"], two_days_ago_report["Search Network"], week_ago_report["Search Network"]),
              "</table>",
        "<h2>グラフ</h2>",
        "<img src='cid:s_impression_graph' width='"+ graph_w +"' border='2'>　",
        "<img src='cid:s_click_graph' width='"+ graph_w +"' border='2'><br>",
        "<img src='cid:s_ctr_graph' width='"+ graph_w +"' border='2'>　",
        "<img src='cid:s_avg_cpc_graph' width='"+ graph_w +"' border='2'><br>",
        "<img src='cid:s_avg_pos_graph' width='"+ graph_w +"' border='2'>　",
        "<img src='cid:s_cost_graph' width='"+ graph_w +"' border='2'><br>",
        "<img src='cid:s_conversion_graph' width='"+ graph_w +"' border='2'>　",
        "<img src='cid:s_cpa_graph' width='"+ graph_w +"' border='2'><br>",
        "<hr />",
          "<table width=800 cellpadding=0 border=0 cellspacing=0>",
          "<tr bgcolor='#3c78d8'>",
            "<td colspan=2>",
              "<div style='font: normal 18pt verdana, sans-serif; padding: 3px 10px; color: white'>ディスプレイ</div>",
            "</td>",
            "</tr>",
          "</table>",
          "<table width=800 cellpadding=0 border=0 cellspacing=0>",
            "<tr bgcolor='#ddd'>",
              "<td></td>",
              "<td style='font: 12pt verdana, sans-serif; padding: 5px 0px 5px 5px; background-color: #ddd; text-align: left'>前日</td>",
              "<td style='font: 12pt verdana, sans-serif; padding: 5px 0px 5px 5px; background-color: #ddd; text-align: left'>一昨日</td>",
              "<td style='font: 12pt verdana, sans-serif; padding: 5px 0px 5x 5px; background-color: #ddd; text-align: left'>先週</td>",
            "</tr>",
            emailRow('Cost', 'Cost', yesterday_report["Display Network"], two_days_ago_report["Display Network"], week_ago_report["Display Network"]),
            emailRow('Average Cpc', 'AverageCpc', yesterday_report["Display Network"], two_days_ago_report["Display Network"], week_ago_report["Display Network"]),
            emailRow('Ctr', 'Ctr', yesterday_report["Display Network"], two_days_ago_report["Display Network"], week_ago_report["Display Network"]),
            emailRow('Average Position', 'AveragePosition', yesterday_report["Display Network"], two_days_ago_report["Display Network"], week_ago_report["Display Network"]),
            emailRow('Impressions', 'Impressions', yesterday_report["Display Network"], two_days_ago_report["Display Network"], week_ago_report["Display Network"]),
            emailRow('Clicks', 'Clicks', yesterday_report["Display Network"], two_days_ago_report["Display Network"], week_ago_report["Display Network"]),
            emailRow('CostPerConversion', 'CostPerConversion', yesterday_report["Display Network"], two_days_ago_report["Display Network"], week_ago_report["Display Network"]),
            emailRow('Conversions', 'Conversions', yesterday_report["Display Network"], two_days_ago_report["Display Network"], week_ago_report["Display Network"]),
              "</table>",
        "<h2>グラフ</h2>",
        "<img src='cid:d_impression_graph' width='"+ graph_w +"' border='2'>　",
        "<img src='cid:d_click_graph' width='"+ graph_w +"' border='2'><br>",
        "<img src='cid:d_ctr_graph' width='"+ graph_w +"' border='2'>　",
        "<img src='cid:d_avg_cpc_graph' width='"+ graph_w +"' border='2'><br>",
        "<img src='cid:d_cost_graph' width='"+ graph_w +"' border='2'><br>",
        "<img src='cid:d_conversion_graph' width='"+ graph_w +"' border='2'>　",
        "<img src='cid:d_cpa_graph' width='"+ graph_w +"' border='2'><br>",
      "</body>",
    "</html>");
  MailApp.sendEmail({
    to: email,
    cc: email_cc,
    subject: "【" + AdWordsApp.currentAccount().getName() + "】" + "AdWords日次レポート " + format_day+ " (" +AdWordsApp.currentAccount().getCustomerId() + ") ",
    htmlBody: html.join("\n"),
    inlineImages:
    {
      s_impression_graph: s_impression_blob,
      s_click_graph: s_click_blob,
      s_ctr_graph: s_ctr_blob,
      s_avg_cpc_graph: s_avg_cpc_blob,
      s_avg_pos_graph: s_avg_pos_blob,
      s_cost_graph: s_cost_blob,
      s_conversion_graph: s_conversion_blob,
      s_cpa_graph: s_cpa_blob,
      d_impression_graph: d_impression_blob,
      d_click_graph: d_click_blob,
      d_ctr_graph: d_ctr_blob,
      d_avg_cpc_graph: d_avg_cpc_blob,
      d_cost_graph: d_cost_blob,
      d_conversion_graph: d_conversion_blob,
      d_cpa_graph: d_cpa_blob
    }
  });
}
function emailRow(title, column, yesterdayRow, twoDaysAgoRow, weekAgoRow) {
  return "<tr> \
 <td style='padding: 5px 10px'>" + title + "</td> \
 <td style='padding: 0px 10px'>" + yesterdayRow[column] + "</td> \
 <td style='padding: 0px 10px'>" + twoDaysAgoRow[column] + formatChangeString(yesterdayRow[column], twoDaysAgoRow[column]) + "</td> \
 <td style='padding: 0px 10px'>" + weekAgoRow[column] + formatChangeString(yesterdayRow[column], weekAgoRow[column]) + "</td> \
 </tr>";
}
/**
* タイムゾーンを考慮した昨日を取得する
*/
function getYesterday() {
  var now = new Date(Utilities.formatDate(new Date(),
                  AdWordsApp.currentAccount().getTimeZone(), "MMM dd,yyyy HH:mm:ss"));
  var yesterday = new Date(now.getTime() - 24 * 3600 * 1000);
  yesterday.setHours(12);
  return yesterday;
}
  
/**
* 前日対比などを計算し、要素に合わせて整形する
*/
function formatChangeString(newValue,  oldValue) {
  newValue += "";
  oldValue += "";
  var x = newValue.indexOf('%');
  if (x != -1) {
    newValue = newValue.substring(0, x);
    var y = oldValue.indexOf('%');
    oldValue = oldValue.substring(0, y);
  }
 
  newValue = toInteger(newValue);
  oldValue = toInteger(oldValue);

  var change = parseFloat(newValue - oldValue).toFixed(2);
  var changeString = change;
  if (x != -1) {
    changeString = change + '%';
  }
   
  if (change >= 0) {
    return "<span style='color: #38761d; font-size: 8pt'> (+" + changeString + ")</span>";
  } else {
    return "<span style='color: #cc0000; font-size: 8pt'> (" + changeString +  ")</span>";
  }
}
/**
* スプレッドシートにアクセスし、データを書き込む
*/
function SpreadsheetAccess(spreadsheetUrl, sheetName) {
  this.spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  this.sheet = this.spreadsheet.getSheetByName(sheetName);
   
  // what column should we be looking at to check whether the row is empty?
  this.findEmptyRow = function(minRow, column) {
    var values = this.sheet.getRange(minRow, column, this.sheet.getMaxRows(), 1).getValues();
    for (var i = 0; i < values.length; i ++) {
      if (!values[i][0]) {
        return i + minRow;
      }
    }
    return -1;
  }
  this.addRows = function(howMany) {
    this.sheet.insertRowsAfter(this.sheet.getMaxRows(), howMany);
  }
  this.writeRows = function(rows, startRow, startColumn) {
    this.sheet.getRange(startRow, startColumn, rows.length, rows[0].length).setValues(rows);
  }
}
  
/**
 * 文字列を数値化する
 */
function toInteger(value) {
  try {
    // 空文字を追加し、stringに変換する
    value += "";
    return parseInt(value.split(",").join(""), 10)
  } catch (ex) {
    Logger.log(ex + ":" + value);
    return 0;
  }
}

 

４．スプレッドシートのURLを設定

１行目のスプレッドシートURLを、２でコピーしたスプレッドシートURLに修正する。

５．グラフ関連の画像URLを取得

スプレッドシートで[ファイル > ウェブに公開]を選択。

リンクタブで公開するドキュメントを指定できます。
公開ドキュメントにあたるグラフには、シート別にそれぞれ

• Cost
• CPC
• CTR
• Clicks
• AverageCPC
• Average Position
• CV
• CPA

と名前がついています。

今回、メールに掲載するグラフは

検索連動広告・ディスプレイ広告のグラフすべて。

なので、「Search Network」「Display Network」のシート内のグラフすべてを公開設定に変更します。

この時、インタラクティブと画像の２種類が選択できますが、「画像」のURLをコピーしてください。

公開を押すと、公開URLが発布されます。
その公開URLをAdwords Scriptのsettings欄にそれぞれ記入していきます。

 

６．スクリプトに名前をつける

ここまでできれば最低限の設定は完了です。
せっかくなので、スクリプトに名前をつけましょう。

名前自体は何でも構いません。
今回はわかりやすく「アカウント日次レポート」としました。

７．プレビュー

続いて、実際に稼働するかプレビューを実行します。
プレビューを実行すると「承認が必要です」と実行許可を求められます。

承認をしないとスクリプトは動かないので、「今すぐ許可」を押して承認をします。

問題がなければ３０秒程度でプレビューが完了します。
「プレビュー」といっても、スクリプトは実際に稼働しているので、

1. スプレッドシートにデータの追記
2. メールの送信

が実行されています。

スプレッドシート

メール

データは実際に動かしているアカウントなので、詳細はマスクします。
グラフ部分が表示されていないのは、７日の移動平均データのグラフを採用しているからです。

運用額が小さく、コンバージョンが無い日もあるようなアカウントを短日グラフで追ってしまうと、起伏が激しすぎて本来グラフとして読み取りたいデータが読み取れなかったためです。

実際に運用を続けると下記のようなグラフに変わっていくと思います。

動かない場合は…。コメント欄に情報を添えて記入いただければわかるかもしれません。

８．実行予約

問題なくプレビューで動作した場合、Adwords Scriptの自動実行を登録します。
このスクリプトは昨日のデータ集計を行うものなので、日が変わった後に実行します。

これで設定は完了です。

番外編：過去データを取得する

当スクリプトの基本機能は

1. 最終データ取得から、昨日のデータを取得し、スプレッドシートに転記する
2. 昨日のデータと一昨日、１週間前のデータを比較し、メール通知する

というものです。

基本的に毎日稼働し始めれば、キレイに回るのですが、過去データをスプレッドシートに転記したいと思うと、少し手を加える必要があります。

開始日・終了日の設定

通常の動作においては空欄にするべき項目ですが、過去データを日時指定してデータ取得する場合のみに利用するオプションです。

画像の例では、2014年2月6日から運用開始しているアカウントでしたので、そこから現在までのデータを取得しようと試みています。
現在までなのに、なぜ2015年1月31日が終了日になっているかは次の実行時間制限があるからです。

Adwords Scriptの実行時間制限

Adwordsスクリプトには1スクリプト「30分」という実行時間の制限があります。

数年間のデータを取得にはどうしても時間がかかり、制限である30分を超える可能性が大いにあります。
30分を超えると、処理が打ち切られ、データの転記が出来ません。

そこで、約1年程度に期間を制限して取得しています。

これを複数回繰り返すことで、過去数年のデータもスプレッドシートに転記することが出来ます。

長期的なデータをあつめ、分析すると、いろいろと分かることも増えてきます。

1年で「平均掲載順位が横ばいなのに、CPCが1.8倍程度になっている」ようなアカウントも競合の参入が激しい分野では珍しくありません。

きちんとデータを日々追って、正しい施策を打てるようにしていきたいですね。

当スクリプトの補足事項

当スクリプトには、いろいろとオレオレ仕様があります。

1. 小規模アカウントで凸凹グラフになり過ぎないよう、CVが０件時のCPAはCV=1と同義としてグラフを描く
2. CVR関係はまったく指標に載せていない

などです。

CVRを載せない理由

リスティングを運用している人なら、誰しもがわかっていることだと思いますが、コンバージョンは「コンバージョンした日」ではなく「広告をクリックした日」につきます。

つまり、

1. ５月１日に広告クリック
2. サイトを訪問し、類似サイトと比較のためブックマークに入れてサイトを離脱
3. ５月４日にブックマークからサイトに訪れコンバージョン

上記ケースの場合、コンバージョンデータは５月１日に付与されます。

つまり、前日のデータを取得し、スプレッドシートに転記する当スクリプトでは、正確なCV数とそれに関連するデータを把握することは難しいのです。

検討期間がほぼ０日しかないアカウントならば、ある程度正しい数値が取れるでしょうが、１日以上の検討が存在するアカウントにおいて、CV関連データはほぼ虚像データとなってしまいます。

CVRという重要なデータだからこそ、正しくないデータを見続けるのは、誤った認識を植え付ける原因になり、良くないと考えているから、トラッキングから外しています。

CPAのグラフ描画を適当に丸めているのも同様の理由となります。

自らが把握したい本当のデータをしっかりと見定めて、ツールに使われるのではなく、ただのツールとして使うことが大切です。

スクリプトの価値

今回はレポーティングのAdwords Scriptの紹介でした。
このスクリプトのコーディングにかかった時間は約２時間です。

この２時間をスクリプトの価値で取り戻せれば、効果の高いものと言えるでしょう。

しかし、今回は反省の多いハックでした。

コードを読める人ならばわかるでしょうが、かなりのクソコードです。
既存の機能を元に拡張していた結果、「あれも欲しいな」「こんなこともしたいな」「こんなのも想定したほうがいいな」とツギハギだらけのプログラムになりました。

特に後半のメールまわりは、メソッド化しようとすれば出来るのですが、面倒になって強行突破しましたｗ

きちんとやりたいことを明確にして、ゼロから作ったほうが速くてキレイなものが組めたような気がします。
まあとりあえず動くからいいだろう。

また、自分でコードを書かなくても、サンプルプログラムで有用なものも非常に多くありますし、便利なスクリプトを公開していただいている方も多いです。

今回は、アカウントの詳細を見るために、ある程度のサマリー＋グラフを用いましたが、もっと「ざっくりと手間をかけずに、複数アカウントの概要だけ把握したい」という場合は、山田さんが公開されているMCCスクリプトが非常に便利で活用できると思います。

コードが書ける方は、是非よりよいスクリプトを作成・公開してスクリプトの輪を広げていきましょう。