Google スプレッドシートでGoogle広告のレポートを作成する(無料テンプレ進呈)

はじめに

みなさんこんにちはALEXです。お客様のウェブサイトの集客ツールとして有効な「google広告」。これは非常に効果的にリードをウェブサイトに誘導することができます。私も多くのクライアント様のgoogle広告の運用をお手伝いさせていただいております。

 

google広告運用で大切なこと

効果的に広告を運用するにあたり大切なことは

「データを収集して理解する」ということです。この作業とても大切ですが

めんどくさいなあ。。。というお声をよく伺います。

そこで、今回はお客様のその面倒くささを解決するお手伝いとして、

 

Google広告のデータレポートを無料で作成する方法をご紹介します。

 

Google広告のデータレポートを無料で作成する方法

まずは、無料でデータを入手しましょう。

事前にGoogleスプレッドシートを使えるようにしておいてください。

Google広告にログインします。次に画面上の「ツール」ー「スクリプト」をクリックします。

続いて、スクリプトの画面から新規追加のアイコンをクリックします。

新規スクリプトの画面が出ますので、スクリプト名を記入(adwordsreportとか)しスクリプトの部分に以下のコードをコピペしてください。

 

// グローバル変数 
    //レポート表紙情報
    var REP_AUTHOR = "レポート作成者名";//見出しページの差し込み文
    var CUSTOMER = "レポート宛名";//見出しページの差し込み文
    var COPYRIGHT = "Copyrigth(C) Alex and compnay AII Right Reserved.";//一番下のコピーライト
    var DATA_DATE = "2019/01/01"; //yyyy/mm/dd 
    var DATA_MONTH = "2019年1月";//レポートでの表記用
    //AWQL用データ
    var AWQL_DURING = "20190101, 20190131"//レポート取得期間。例>2019年1月1~31日;

var FOLDER_ID = '{1MbP915l_7dIYO3sYfJrv20i9OzVhcuyj}';//レポートを出力したいフォルダを指定。Googleドライブでフォルダを開くとURLから参照できます。現在は、[AdWordsレポート生成]のフォルダを参照中。
var email = 'info@alexandcompany.net';//ここにメールアドレスを登録。レポートの送信先
var spreadsheet_url = 'https://docs.google.com/spreadsheets/d/1tG_bjt85bWyOfAiFTdgQ7aHEIvGG86MyDqwCEmmzOyI/edit';//レポートテンプレートのファイルを参照。変更不要。
var formattedDate = Utilities.formatDate(new Date(), "JST", "yyyy/MM/dd' 'HH:mm:ss");
var cid = AdWordsApp.currentAccount().getCustomerId();
var accountname = AdWordsApp.currentAccount().getName();


function main() {




//    var SSId = copySpreadsheet(spreadsheet_url);
//    var spreadsheet = SpreadsheetApp.openById(SSId);
    var spreadsheet = copySpreadsheet(spreadsheet_url);
    //「見出し」ページの作成
        var toppage = spreadsheet.getSheetByName('見出し');
        toppage.getRange(4, 1).setValue(CUSTOMER);
        toppage.getRange(8, 3).setValue(cid);
        toppage.getRange(31, 4).setValue(REP_AUTHOR);
        toppage.getRange(34, 2).setValue(COPYRIGHT);
    //「総計」ページの作成
        var sumarrypage = spreadsheet.getSheetByName('総計');
        sumarrypage.getRange(1, 12).setValue(DATA_DATE);

    //検索連動レポート(日別)
        var rawsheet = spreadsheet.getSheetByName('rawdata');
        var formatsheet = spreadsheet.getSheetByName('アドワーズ');
        formatsheet.getRange(1, 7, 1, 1).setValue(formattedDate);//セルの範囲取得:getRange(row, column [, numrows [, numcolumns]])
        formatsheet.getRange(1, 2, 1, 1).setValue('CID:');
        formatsheet.getRange(1, 3, 1, 1).setValue(cid);
        formatsheet.getRange(2, 2, 1, 1).setValue('アカウント名:');
        formatsheet.getRange(2, 3, 1, 1).setValue(accountname);
    //検索連動レポート(KW別)
        var rawsheet2 = spreadsheet.getSheetByName('rawdata2');
        var formatsheet2 = spreadsheet.getSheetByName('アドワーズKW');
        formatsheet2.getRange(1, 7, 1, 1).setValue(formattedDate);//セルの範囲取得:getRange(row, column [, numrows [, numcolumns]])
        formatsheet2.getRange(1, 2, 1, 1).setValue('CID:');
        formatsheet2.getRange(1, 3, 1, 1).setValue(cid);
        formatsheet2.getRange(2, 2, 1, 1).setValue('アカウント名:');
        formatsheet2.getRange(2, 3, 1, 1).setValue(accountname);
    //ディスプレイネットワーク(日別)
        var rawsheet3 = spreadsheet.getSheetByName('rawdata3');
        var formatsheet3 = spreadsheet.getSheetByName('GDN');
        formatsheet3.getRange(1, 7, 1, 1).setValue(formattedDate);//セルの範囲取得:getRange(row, column [, numrows [, numcolumns]])
        formatsheet3.getRange(1, 2, 1, 1).setValue('CID:');
        formatsheet3.getRange(1, 3, 1, 1).setValue(cid);
        formatsheet3.getRange(2, 2, 1, 1).setValue('アカウント名:');
        formatsheet3.getRange(2, 3, 1, 1).setValue(accountname);
  
  

    // レポート取得
    var adwordsreport = AdWordsApp.report(
        'SELECT Date, Impressions, Clicks, Ctr, Conversions, ConversionRate, CostPerConversion, AveragePosition, Cost ' +
        'FROM   ACCOUNT_PERFORMANCE_REPORT ' +
        'WHERE  AdNetworkType1 = SEARCH ' +
        //'WHERE  Impressions > 0 ' +
        'DURING '+AWQL_DURING);
    var keywordreport = AdWordsApp.report(
        'SELECT Criteria, KeywordMatchType, CampaignName, Impressions, Clicks, AveragePosition, Ctr, Conversions, ConversionRate, CostPerConversion, Cost ' +
        'FROM   KEYWORDS_PERFORMANCE_REPORT ' +
        'DURING '+AWQL_DURING);
    var gdnreport = AdWordsApp.report(
        'SELECT Date, Impressions, Clicks, Ctr, Conversions, ConversionRate, CostPerConversion, Cost ' +
        'FROM   ACCOUNT_PERFORMANCE_REPORT ' +
        'WHERE  AdNetworkType1 = CONTENT ' +
        //'FROM   DISPLAY_TOPICS_PERFORMANCE_REPORT ' +
        //'WHERE  Impressions > 0 ' +
        'DURING '+AWQL_DURING);
//    'WHERE Status = ENABLED '+
//    'WHERE  Impressions > 0 ' +
  

  	 
  // レポート取得  
  var groupreport = AdWordsApp.report(
    'SELECT CampaignName, AdGroupName,Impressions, Clicks, AveragePosition, Ctr, Conversions, ConversionRate, CostPerConversion, Cost  ' +
    'FROM   KEYWORDS_PERFORMANCE_REPORT ' +
    'WHERE Status = ENABLED '+
        // 'WHERE  Impressions > 0 ' +
    'DURING THIS_MONTH');
   
  // 上記AWQLで指定したデータをエキスポート
  //report.exportToSheet(rawdata3);

    // 上記AWQLで指定したデータをエキスポート
        adwordsreport.exportToSheet(rawsheet);
        rawsheet.insertColumns(2);//曜日欄
        rawsheet.insertColumns(6);//CPC欄
    // 上記AWQLで指定したデータをエキスポート
        keywordreport.exportToSheet(rawsheet2);
        rawsheet2.insertColumns(8);//CPC欄
    // 上記AWQLで指定したデータをエキスポート
        groupreport.exportToSheet(rawsheet3);
    //    rawsheet3.insertColumns(2);//曜日欄
    //    rawsheet3.insertColumns(6);//CPC欄
    //    rawsheet3.insertColumns(9);//平均順位欄

    // 日付列でソート
    rawsheet.sort(1, true);
    // コスト列でソート
    rawsheet2.sort(12, false);
    // 日付列でソート
    rawsheet3.sort(1, true);

    // ヘッダー設定
        var header = ['日付', '曜日', 'インプレッション数', 'クリック数', 'クリック率', 'クリック単価', 'コンバージョン数', 'コンバージョン率', 'コンバージョン単価', '平均掲載順位', 'コスト',];
        var headerrange = formatsheet.getRange(4, 2, 1, 11);
        headerrange.setValues([header]);
        headerrange.setHorizontalAlignment("center");
        headerrange.setFontWeight("bold");
        headerrange.setFontSize(8);
    // ヘッダー設定
        var header2 = [ 'キーワード', 'マッチタイプ', 'キャンペーン', '表示数', 'クリック数', '平均掲載順位', 'クリック率', 'CPC', 'CV数', 'CV率', 'コンバージョン単価', 'コスト',];
        var headerrange2 = formatsheet2.getRange(4, 2, 1, 12);
        headerrange2.setValues([header2]);
        headerrange2.setHorizontalAlignment("center");
        headerrange2.setFontWeight("bold");
        headerrange2.setFontSize(8);
    // ヘッダー設定
        var header3 = ['日付', '曜日', 'インプレッション数', 'クリック数', 'クリック率', 'クリック単価', 'コンバージョン数', 'コンバージョン率', 'コンバージョン単価', '平均掲載順位', 'コスト',];
        var headerrange3 = formatsheet3.getRange(4, 2, 1, 11);
        headerrange3.setValues([header3]);
        headerrange3.setHorizontalAlignment("center");
        headerrange3.setFontWeight("bold");
        headerrange3.setFontSize(8);

    // rawdataをフォーマットシートへ値コピー(フォーマットシートのクリック率列の書式を%にしておく)
        var lastRow = 31; //rawsheet.getLastRow();
        var lastColumn = rawsheet.getLastColumn();
        // var copyValue = rawsheet.getRange(2, 1, lastRow, lastColumn).getValues();
        // formatsheet.getRange(5, 2, lastRow, lastColumn).setValues(copyValue);
        var copyValue = rawsheet.getRange(2, 1, lastRow, 1).getValues();
        formatsheet.getRange(5, 2, lastRow, 1).setValues(copyValue);
        var copyValue = rawsheet.getRange(2, 3, lastRow, 3).getValues();
        formatsheet.getRange(5, 4, lastRow, 3).setValues(copyValue);
        var copyValue = rawsheet.getRange(2, 7, lastRow, 5).getValues();
        formatsheet.getRange(5, 8, lastRow, 5).setValues(copyValue);
    // rawdataをフォーマットシートへ値コピー(フォーマットシートのクリック率列の書式を%にしておく)
        var lastRow2 = 50;//rawsheet2.getLastRow();
        var lastColumn2 = rawsheet2.getLastColumn();
        // var copyValue2 = rawsheet2.getRange(2, 1, lastRow2, lastColumn2).getValues();
        // formatsheet2.getRange(5, 2, lastRow2, lastColumn2).setValues(copyValue2);
        var copyValue2 = rawsheet2.getRange(2, 1, lastRow2, 7).getValues();
        formatsheet2.getRange(5, 2, lastRow2, 7).setValues(copyValue2);
        var copyValue2 = rawsheet2.getRange(2, 9, lastRow2, 4).getValues();
        formatsheet2.getRange(5, 10, lastRow2, 4).setValues(copyValue2);
    // rawdataをフォーマットシートへ値コピー(フォーマットシートのクリック率列の書式を%にしておく)
        var lastRow3 = 31; //rawsheet.getLastRow();
        var lastColumn = rawsheet.getLastColumn();
        // var copyValue = rawsheet.getRange(2, 1, lastRow, lastColumn).getValues();
        // formatsheet.getRange(5, 2, lastRow, lastColumn).setValues(copyValue);
        var copyValue3 = rawsheet3.getRange(2, 1, lastRow, 1).getValues();
        formatsheet3.getRange(5, 2, lastRow, 1).setValues(copyValue3);
        var copyValue3 = rawsheet3.getRange(2, 3, lastRow, 3).getValues();
        formatsheet3.getRange(5, 4, lastRow, 3).setValues(copyValue3);
        var copyValue3 = rawsheet3.getRange(2, 7, lastRow, 5).getValues();
        formatsheet3.getRange(5, 8, lastRow, 5).setValues(copyValue3);


    // 処理後、rawdataシートを削除する
    // spreadsheet.deleteSheet(rawsheet);
    // spreadsheet.deleteSheet(rawsheet2);
    // spreadsheet.deleteSheet(rawsheet3);

/*    Driv.Files.insert({
        "title": ,
        "mineType": "application/vnd.google-apps.spreadsheet",
        "parents": [{"id": FOLDER_ID }]
    });
*/
//     moveSS(SSId);
//     function moveSS(SSId) {
//
// //        var idSs = SpreadsheetApp.create("新規SS").getId();
//         var fileSS = DriveApp.getFileById(SSId);
//         var folderTarget = DriveApp.getFolderById(FOLDER_ID);
//
//         folderTarget.addFile(fileSS);
//         DriveApp.getRootFolder().removeFile(fileSS);
//
//     }




    // ログ記録とメール通知設定
    Logger.log('キャンペーンレポート(今月)の準備ができました\n' +
        spreadsheet.getUrl());
    if (email) {
        MailApp.sendEmail(email,
            'キャンペーンレポート(今月)の準備ができました' + ' ' +accountname + ' ' + formattedDate,
            spreadsheet.getUrl());
    }
}

// スプレッドシートのコピー利用
function copySpreadsheet(spreadsheetUrl) {
    return SpreadsheetApp.openByUrl(spreadsheetUrl).copy(
        'キャンペーンレポート('+ DATA_MONTH +')' + ' ' + accountname + " [作成日:" + formattedDate +"]");
//        'キャンペーンレポート(今月)' + ' ' + accountname + " " + formattedDate).getId();
}

 

そして保存をクリック。

スクリプトが登録されたら、その名前をクリックします。次に画面下の「実行を」クリック。

これでスクリプトが実行され、Googleドライブ上にデータシートが作成されました。「ログステートメント」をクリックすると画面下にURLが出ますのでこれをクリックしてください。

Googleスプレッドシートにレポートが表示されています。

各データの詳しいご説明は次の記事でご紹介します。

 

まとめ

Google スプレッドシートでGoogle広告のレポートを作成する方法をご紹介しました。

もっとさらに掘り下げたい、運用を効果的に行いたい、とお悩みのお客様。お気軽にご相談ください。

詳しく見る

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です