はじめに
みなさんこんにちは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広告のレポートを作成する方法をご紹介しました。
もっとさらに掘り下げたい、運用を効果的に行いたい、とお悩みのお客様。お気軽にご相談ください。