Category: Webマーケティング

Automating cost data import in Google Analytics with Google Apps Scriptで、こちらを参考に原型がわからないほどカスタマイズしています。

インポートまでの流れ

Bing AdsのデータをGoogle Analyticsにインポートするまでの簡単な流れになります。

1. Bing Ads管理画面で日次レポートを作成し、Gmailに送る
2. Gmail内でメールを検索
3. 添付ファイルを解凍
4. 解凍したデータのうち必要な箇所を抽出
5. Google Analyticsにアップロード

Bingの広告データをGmailに送る

Bingの管理画面からレポート作成のページへ進みます。

Bingレポートの作成

次にレポートの詳細設定で下記のようにします。

Bingレポートの概要

• Report name：任意（※Gmailで検索する際に必要なのでわかりやすく他と重複しない名前が良いです）
• Report type: Keyword
• Show: Day
• Date range: Yesterday
• Timezone: (GMT + 09:00)Osaka, Sapport, Tokyo
• Format: CSV
• What to report on: Specific accounts and campaignsを選択し、下の一覧から該当のクライアントを選択

Bingレポートのカラムを選択

レポートに含める項目（カラム）は下記を選びます。

• Unit of Date
• Account
• Impressions
• Clicks
• Spend
• AD ID
• Keyword
• Campaign
• Destination URL

※念のため、この順番を変えないでください。

Bingレポートの送付設定

最後に、このレポートをGmailに送付します。

• Schedule this report : チェック
• Frequency：Daily
• At : 任意ですが、後でこの時間を使うため、ここでは午前10時
• Start Date, End Date : 任意
• Send report to : 自分のGmailアドレス
• Include report as an attachment : チェック
• Include total row : チェックを外す

最後に、「Save and run」または「Save」をクリックします。「Save and run」をクリックすると実行されてGmail宛にレポートが添付ファイル付きで届きますので、「Save and run」をクリックして、ファイルを実際に確認することをおすすめします。 Bing Adsの設定は以上になります。

Google Analyticsのデータインポート

次にGoogle AnalyticsにBingの広告データをインポートする箱を用意します。

Google AnalyticsのアカウントIDとプロパティIDを取得

Google Analyticsへログインして、データをインポートしたいプロパティを選びます。このアカウントID（下の画像では4517…の部分）とプロパティID（UA-4517…-1の部分）を後で使うのでメモしておいてください。

Google Analyticsのデータセット作成

次にGoogle Analyticsでデータをインポートするため箱を作ります。 左下にある「管理」からプロパティ→データインポートをクリックします。 この時、アカウントおよびプロパティが間違っていない事を確認しましょう。 データスキーマの一覧と、左上に「CREATE」ボタンが表示されますので、これをクリックして作成します。

データセットタイプ

最初にデータの種類を選択します。ここでは一番下にある「Cost Data」（費用データ）を選択します。

データセット名と紐付けるビューの選択

次にこのデータセットの名前を入力します。日本語でも英語でもOKです。他で使うことはありませんが、わかりやすい名前（例：○○クライアント Bing広告）にしたほうが良いでしょう。 その下でこのデータセットを使うビューを選択します。特に表示させたくないビューがなければすべて選んでも構いませんが、広告レポート等以外では使うことがあまりないと思います。

カラムの選択

最後にこのデータセットのカラムの選択をします。 最初の表に3項目（Date,Medium,Source）ありますが、これらは必須なのでそのままで構いません。 2つ目の表からは、

• Clicks
• Cost
• Impressions

をこの順番で選びます。 最後の表からは、

• Ad Content
• Campaign
• Keyword

をやはりこの順番で選びます。 最後の「Import Behavior」では「Summation」を選択して、「Save」をクリックします。

補足：Import Behaviorについて

データの中に重複する行が存在する場合に、合計するか（=Summation）？最後の行のみを採用するか（=Overwrite）？の選択になります。 Bingのキーワードの設定が不十分だと重複する行が存在するので、Overwriteにしてしまうと、最後にインポートした行のみが結果として反映されてしまいます。そのため、今回は合計を選びます。

データセットIDをメモ

設定完了後、データセット一覧の画面に戻ります。そこで今作ったデータセットが作成されていることを確認し、右側の「Data Set ID」の列に書かれている文字列（下記の画像では-3esA….MA）をメモしておいてください。 Google Analytics側の設定は以上で終了です。

Google Sheetの設定

次にGoogle Sheetでの設定になります。

Google Analytics APIを有効

まずは、Google AnalyticsにアクセスできるアカウントでGoogleにログインして、Google Sheetで新しいスプレッドシートを開きます。 メニューから「アドオン」→「アドオンを取得」をクリックします。 検索フォームに「Google Analytics」と入力するとGoogle Analyticsが出てきますので、それをクリックします。 Google Analyticsのウィンドウが表示されますので、「インストール」をクリックします。（※バージョンによって画面が画像と異なるかもしれません） すると、「インストールの準備」というウィンドウが新しく表示されるので「続行」をクリックします。 今度はアカウントを選択するウィンドウが表示されますので、Google Analyticsに実際にアクセスするアカウントを選択してください。（問題なければ最初にログインしたアカウントが表示されますが、複数のアカウントでログインしているとアカウントがいくつか出てきますので気をつけてください。 さらに次は確認画面になりますので、「許可」をクリックしてください。 最後に完了画面が出てきますので、クリックしたら完了です。 Google Sheetの画面に戻りますので、「アドオン」をクリックすると「Google Analytics」が追加されていればOKです。

基本設定をSheetに記入

Google Sheetに下記のようにBing、Google Analyticsの情報を入力します。カッコ内は前述の設定した時に使用したものになります。

• A：名前（任意）
• B：Google Analyticsのアカウント（4517…）
• C：Google AnalyticsのプロパティID（UA-4517…-1）
• D：Google AnalytisのDataSet（-3esA…….MA）
• E：スキーマ（ga:date,ga:medium,ga:source,ga:adClicks,ga:adCost,ga:impressions,ga:adContent,ga:campaign,ga:keyword）※変更しないでください
• F：Bingから送られてくるメールの件名（Your scheduled report is ready to view）
• G：レポート名（ABC_DailyReport.zip）
• H：Bingのauto-taggingの設定（通常はON1）
• I：Bingのレポート画面で設定したレポートの送り先メールアドレス（[email protected]）

Google Apps ScriptでのGAへインポート

ようやくここからが今回の本番のスクリプト部分になります。 （書いてるだけで疲れました。。。） 今回はGoogle Apps Scriptになります。Google Apps Scriptはあまり馴染みがないかもしれませんが、基本的な部分はJavaScriptと同じでですので、JavaScriptの概念や関数の作り方などがわかればそれほど苦労しないと思います。 今回のスクリプトは以下の流れになります。

1. Google Sheetから情報を読み込む
2. Gmailからメールを検索
3. Gmailからzipファイルを取得
4. ファイルを解凍し、データを取得
5. データをGoogle Analytics用に加工
6. データをGoogle Analyticsにアップロード
7. データがGoogle Analyticsにアップされていることを確認

なお、Google Apps ScriptはGoogle Sheetの「ツール」から「スクリプト エディタ」を選びます。 すると、Googl Apps Scriptのウィンドウが表示されますので、右側の「コード.gs」と書かれているタブの下にfunction myfunction(){ } と書かれている箇所に入力します。 ※今回はmyfunction()は使用しないので、消してオリジナルの関数を作成していきます。

Google Sheetから情報を読み込む

まず最初に、さきほどGoogle Sheetに入力した情報をこのGoogle Apps Scriptに読み込みます。 関数名はgetConfigとし、この関数を実際に呼び出す際は第１引数のfileKeyにはGoogle Sheetのパスを、sheetNameにはさきほど入力したGoogle Sheetのシート名をそれぞれ入れます。

• パス：（1w2xXXX……..OJs）
• シート：（client）

function getConfig(fileKey, sheetName){
  var retConfig = [];
  var spreadsheet = SpreadsheetApp.openById(fileKey);
  var sheet = spreadsheet.getSheetByName(sheetName);
  var tmpLastRow = sheet.getLastRow();
  for(var i = 2;i <= sheet.getLastRow();i++){
    var val = sheet.getRange(i, 1).getValue();
    if (!val) {
      break;
    }else{
      retConfig[i-2] = [];
      retConfig[i-2]['client'] =  sheet.getRange(i,1).getValue();
      retConfig[i-2]['analyticsAccountId'] = Utilities.formatString('%s',sheet.getRange(i, 2).getValue());
      retConfig[i-2]['analyticsPropertyId'] = sheet.getRange(i, 3).getValue();
      retConfig[i-2]['customDataSourceId'] = sheet.getRange(i, 4).getValue();
      retConfig[i-2]['header'] = sheet.getRange(i, 5).getValue();
      retConfig[i-2]['emailSubject'] = sheet.getRange(i, 6).getValue();
      retConfig[i-2]['zipFileName'] = sheet.getRange(i, 7).getValue();
      retConfig[i-2]['autoTagging'] = sheet.getRange(i, 8).getValue();
      retConfig[i-2]['account'] = sheet.getRange(i, 10).getValue();
      retConfig[i-2]['sendTo'] = sheet.getRange(i, 11).getValue();
      retConfig[i-2]['previousZipFileName'] = sheet.getRange(i, 12).getValue();
    }
  }
  return retConfig;
}

いちおう、複数のBing広告データを1回の実行で完了できるように、Google Sheetに記載されている情報をすべて読み込みます。 ただし、途中で1列目が空白の行があるとそこから先は読み込まないようにしています。

Gmailからメールを検索

Gmailからレポートのメールを探します。 Googleは同じブラウザでも複数のアカウントでログインできますので、現在のアカウントと実際に検索するメールアドレスが違うということがよくあります。 そのため、事前に検索するメールアドレスを確認するようにしています。 その後、件名、受信日、添付ファイル名をキーにしてメールを探しますが、Gmailはメールをスレッドという塊で管理しているため、

1. 件名などからスレッドを探す
2. 該当する添付ファイルを探す

という2段階で検索します。 Gmailの検索方法については、Gmail で使用できる検索演算子などを参照してください。 8行目で検索期間を指定しています。formatDateAsString(addDaysToDate(new Date(), -1))が昨日、formatDateAsString(new Date())が今日を意味していますが、今日の方は状況によっては翌日formatDateAsString(addDaysToDate(new Date(), 1))にしないと検索に引っかからない場合があります。 日付の指定等によっては2ファイル以上検索に引っかかる場合があります。そのため、私は毎日抽出が終わってからその日のレポートのメールを削除するようにして、25行目のisIntrash関数を使うことでゴミ箱に入っているメールアドレスは除外するようにしています。

// 設定情報を取得
var conf = getConfig(path, sheet);
// アクセスするメールアドレスがGoogle Sheetに記載したアドレスと同じであるかチェック
var msg = null:
if(Session.getActiveUser().getEmail() != conf[i]['account']){
 // アドレスが違うので終了
}else{
 // メールスレッドがmsg変数に入る
 msg = getMessage(conf[i]['emailSubject'], formatDateAsString(addDaysToDate(new Date(), -1)), formatDateAsString(new Date()), conf[i]['zipFileName']);
 if(!msg){
  // メールが存在しなかったので終了
 }
}
function getMessage(subject, after, before, zipfileName){
  var myMsg = null;
  var query = 'subject:' + subject + ' has:attachment ' + 'after:' + after + ' ' + 'before:' + before;
  // Gmailで検索し、スレッド（同じ件名のメールの塊）を探す
  var myThread = GmailApp.search(query);
  // スレッドの中からメールに該当する添付ファイルがあればそれを結果として返す。
  var myMsgs = GmailApp.getMessagesForThreads(myThread);
  for(var threadIndex = 0; threadIndex < myMsgs.length;threadIndex++){
    for(var i = 0;i < myMsgs[threadIndex].length; i++){
      var myMsg =  myMsgs[threadIndex][i];
      if(!myMsg.isInTrash()){
        var attachments =  myMsg.getAttachments(); // GmailAttachment[]
        for (var j = 0; j < attachments.length; j++) {
          var tmpFileName = attachments[j].getName();
          if (attachments[j].getName().match(zipfileName)) {
            return myMsg;
          }
        }
      }
    }
  }
  // メールが見つからない場合はNULLを返す。
  return null;
}

Gmailからzipファイルを取得、解凍、加工

メールが見つかった場合、上記のスクリプトの変数msgにメールオブジェクトが入ります。このmsgオブジェクトから添付ファイルを取得します。 Bingから送られてくるこの添付ファイルはzip形式で圧縮されているので、解凍して必要なデータだけ抽出します。 Bingから送られてくる圧縮ファイルの中身はcsvファイルなので、そのままデータをCSV形式で抽出します。 詳しくは、Google Apps ScriptのClass Utilities.unzip(blob)やリファレンスのparseCsv(csv, delimiter)を参照してください。

// conf[i]['zipFileName']：圧縮ファイル名
var csv = findCsvAttachment(msg.getAttachments(), conf[i]['zipFileName']);
// ファイル内に該当する圧縮ファイルがあれば解凍して返す
function findCsvAttachment(attachments, zipFileToSearch) {
  var counter = 0;
  for (var i = 0; i < attachments.length; i++) {
    if (attachments[i].getName().search(zipFileToSearch) != -1) {
      var unzip = Utilities.unzip(attachments[i]);
      var csvData = Utilities.parseCsv(unzip[0].getDataAsString(), ",");
      return csvData;
    }
  }
 // ファイルが見つからなかった場合はnullを返す。
 return null;
}

データをGoogle Analytics用に加工

ここが一番面倒なところで、各自広告データなどによって多少の加工が必要です。 本来、私もBingのAuto TaggingやGoogle Analyticsの設定に合わせるためにロジックを複数用意していますが、そのあたりまで含めるとややこしいコードになるため、今回は省略しました。 もし、詳しく知りたい場合はコメントにて連絡ください。

// Google Analyticsへアップロードする際に必要なヘッダー
    var header = conf[i]['header'];
    // Google Analytics用にデータを加工
    var csvForUpload = processCsv(csv, header);
function processCsv(csvData, header) {
  var dataForUpload = header + '\n';
  var tempData = [];
  var j = 0;
  // 添付ファイルの中身の最初の10行は不要な情報なので飛ばします。
  for (var i = 10; i < csvData.length; i++) {
    // ファイルの中身がなければ次の行へ移動
    if(!csvData[i][1]){
      break;
    }
    var ga_medium = 'cpc';
    var ga_source = 'bing';
    var ga_date = csvData[i][0].replace(/-/g, "");
    var ga_adClick = Number(csvData[i][3]);
    var ga_adCost = Number(csvData[i][4]);
    var ga_impressions = Number(csvData[i][2]);
    var ga_campaign = ''; // utm_campaign
    var ga_keyword = '';// utm_term
    var ga_adContent = '';// utm_content
    var destinationURL = null;
    // BingのadContentなどに[]が入っているとGoogle Analyticsでは削除されて取り込まれているため、事前に除外する
　  csvData[i][5]= csvData[i][5].replace('[','').replace(']','');
    ga_campaign =  csvData[i][7];
    ga_adContent = csvData[i][5];
    ga_keyword = csvData[i][6];
    // 各行のデータをGoogle Analytics用に用意したヘッダーと同じ順で並べて配列に格納
    tempData[j]=[ga_date,ga_medium,ga_source,ga_adClick,ga_adCost,ga_impressions,ga_adContent,ga_campaign,ga_keyword]
    j++;
  }
  // Google Analyticsへアップロードするために配列のデータを文字列にまとめる
  for(var i =0; i < tempData.length;i++){
    dataForUpload += tempData[i][0] + ',' + tempData[i][1] + ',' +  tempData[i][2] + ',' + tempData[i][3] + ',' + tempData[i][4].toFixed(2) + ',' + tempData[i][5] + ',\"' + tempData[i][6] + '\"'  +  ',\"' + tempData[i][7] + '\"' + ',\"' + tempData[i][8] + '\"'\n'';
  }
  return dataForUpload;
}

データをGoogle Analyticsにアップロード

ここまで来たらあともう少しです。いよいよ、Google Analyticsにデータをインポートします。コード自体は難しくありませんが、このあたりはもう定型なので変える必要もあまりないでしょう。 珍しい関数についてはリンクを貼っておきます。

• Utilities.newBlob
• Analytics.Management.Uploads.uploadData
• JSON.parse
• Analytics.Management.Uploads.get

// 引数はインポートするデータ、Google Analyticsのアカウント、プロパティID、DatasetのIDの4つ
var analyticsUploadStr = uploadDataToAnalytics(csvForUpload, conf[i]['analyticsAccountId'], conf[i]['analyticsPropertyId'], conf[i]['customDataSourceId']);
function uploadDataToAnalytics(data, accountId, webPropertyId, customDataSourceId) {
  var ret = false;
  try{
    // 前述で作成したアップロード用データを"application/octet-stream"形式でBlobオブジェクトに書き出します。
    var dataBlob = Utilities.newBlob(data, "application/octet-stream", "GA import data");
    // Google Analytics へデータをアップロードし、Idを取得
    var upload = Analytics.Management.Uploads.uploadData(accountId, webPropertyId, customDataSourceId, dataBlob);
    // JSON形式なので変換
    var uploadId = JSON.parse(upload);
    var count = 0;
    var sleepingTime = 10000;
    // アップロードが成功したか？失敗したか？を定期的に確認。
    while(count < 5){
      var status =Analytics.Management.Uploads.get(accountId, webPropertyId , customDataSourceId, uploadId.id );
      status = JSON.parse(status);
      if(status['status'] == 'PENDING'){
        count++;
        Utilities.sleep(sleepingTime*count);
      }else if(status['status'] == 'COMPLETED'){
          ret = status['id'];
        break;
      } else if(status['status'] == 'FAILED'){
        var error = "";
        for(var j=0;j<status.errors.length;j++){
          error += (j+1)+".) "+status.errors[j]+" \n";
          // エラーはログに書き出すなどする
        }
        ret = false;
        break;
      }
    }
  }catch(e) {
    ret = false;
  }finally{
    return ret;
  }
}

ジョブの設定

これでGoogle AnalyticsへBingの広告データをインポートするスクリプトは完了ですが、これを毎日実行しなければならないため、ジョブの設定が必要です。 Google Apps Scriptの「編集」→「現在のプロジェクトのトリガー」をクリックすると、トリガーのウィンドウが表示されます。 このとき、プロジェクトを保存していないとプロジェクトを保存するダイアログが表示されますので、名前はGoogle Sheetと同じ名前でプロジェクトを保存しておきましょう。 ※名前は任意ですが同じにしておいたほうがわかりやすいので。 そこで右下の「トリガーを追加」ボタンをクリックすると、トリガーの設定ダイアログが表示されるので、

• 実行する関数を選択：呼び出す関数（今回のスクリプトでは作成していませんが呼び出しているコード部分をまとめたものを upload といった名前で関数を作ります）
• イベントのソースを選択：時間主導型
• 時間ベースのトリガーのタイプを選択：日付ベースのタイマー
• 時刻を選択：任意（Bingからメールが届く以降の時間であれば何時でも構わないですが、念のため2時間程度余裕をもたせておいた方が良いです）

メール削除やデータのリフレッシュが必要

Bingはたまにですが、レポートの送付が遅れたり、後日インプレッション数やクリック数が変わったりすることがあります。（Facebookほどではありませんが） そのため、前日のデータのGAにインポートしている場合は、数日後に再アップロードして以前アップロードしたファイルを削除する必要があります。 ※私は面倒くさがりなのと、相談してきた人が「月初に更新されていればOK」ということでしたので、月初にGAから前月のデータをすべて削除して、1ヶ月分のデータをまとめてインポートしています。 さらに、Bingがレポートの送付が遅れると、毎日ジョブを実行する際に、前日のデータが検索結果に含まれてしまう場合があります。それらを避けるために実行後にメールを削除する必要があります。 これらの方法については改めて紹介したいと思います。 ]]>