運用に携わっている人で、今日のパフォーマンスが気になるキャンペーンがあるけども外出先でなかなかチェックできない、ということはないでしょうか?今回はそんな方のためのサンプルコードを公開します。
指定したキャンペーンの、キャンペーン(今月日別)、広告グループ(今日)、キーワード(今日)、広告(今日)、サーチクエリー(今日)といったレポートをGoogleスプレッドシートに展開し、メールで通知してくれます。Googleスプレッドシートはスマホ(ウェブ、アプリ)でも確認できるので、外出先でも見れる、というものです。
レポートを取得したいアカウントにAdWords管理画面から入り、一括処理→スクリプトに行き、新規スクリプトを作成し、以下のサンプルコードをまずコピペして保存してください。
サンプルコード
// グローバル変数 var email = 'ここに通知先のメールアドレスをペースト'; var spreadsheet_url = 'https://docs.google.com/spreadsheets/〜という感じのURLをペースト'; var spreadsheet = SpreadsheetApp.openByUrl(spreadsheet_url); var formattedDate = Utilities.formatDate(new Date(), "JST", "yyyy/MM/dd' 'HH:mm:ss"); var cid = AdWordsApp.currentAccount().getCustomerId(); var accountname = AdWordsApp.currentAccount().getName(); var rawsheet = spreadsheet.getSheetByName('データ用'); // 6つの関数を順番に実行していきます function main() { cpreport(); agreport() kwreport(); adreport(); sqreport(); sendmail() } // キャンペーンレポート(今月) function cpreport() { var formatsheet = spreadsheet.getSheetByName('キャンペーン'); rawsheet.clear(); formatsheet.clear(); // 基本情報とフォーマット formatsheet.getRange(1, 1, 1, 1).setValue('更新時間:'); formatsheet.getRange(1, 2, 1, 1).setValue(formattedDate); formatsheet.getRange(2, 1, 1, 1).setValue('CID:'); formatsheet.getRange(2, 2, 1, 1).setValue(cid); formatsheet.getRange(3, 1, 1, 1).setValue('アカウント名:'); formatsheet.getRange(3, 2, 1, 1).setValue(accountname); formatsheet.getRange(4, 1, 1, 1).setValue('キャンペーンレポート(日別)'); formatsheet.getRange(4, 1, 1, 10).setBackground('#B0E0E6'); // レポート取得 var report = AdWordsApp.report( 'SELECT Date, CampaignName, Impressions, Clicks, Ctr, Cost, ConversionsManyPerClick, CostPerConversionManyPerClick, ConversionRateManyPerClick, AveragePosition ' + 'FROM CAMPAIGN_PERFORMANCE_REPORT ' + 'WHERE CampaignName = "キャンペーン名をペースト" ' + 'DURING THIS_MONTH'); // 上記AWQLで指定したデータをエキスポート report.exportToSheet(rawsheet); // 日付列で昇順ソート rawsheet.sort(1, true); // ヘッダー設定 var header = [ '日付', 'キャンペーン名', 'IMP数', 'クリック数', 'クリック率', 'コスト', 'CV数', 'CV単価', 'CV率', '平均掲載順位', ]; var headerrange = formatsheet.getRange(5, 1, 1, 10); headerrange.setValues([header]); headerrange.setHorizontalAlignment("center"); headerrange.setFontWeight("bold"); headerrange.setFontSize(8); // rawdataをフォーマットシートへ値コピー(フォーマットシートのクリック率列の書式を%にしておく) var lastRow = rawsheet.getLastRow(); var lastColumn = rawsheet.getLastColumn(); var copyValue = rawsheet.getRange(2, 1, lastRow, lastColumn).getValues(); formatsheet.getRange(6, 1, lastRow, lastColumn).setValues(copyValue); // クリック率列とコンバージョン率列のパーセント表示変換 var formatlastRow = formatsheet.getLastRow(); formatsheet.getRange(1, 5, formatlastRow, 1).setNumberFormat("0.00%"); formatsheet.getRange(1, 9, formatlastRow, 1).setNumberFormat("0.00%"); } // 広告グループレポート(今日) function agreport() { var formatsheet = spreadsheet.getSheetByName('広告グループ'); rawsheet.clear(); formatsheet.clear(); // 基本情報とフォーマット formatsheet.getRange(1, 1, 1, 1).setValue('更新時間:'); formatsheet.getRange(1, 2, 1, 1).setValue(formattedDate); formatsheet.getRange(2, 1, 1, 1).setValue('CID:'); formatsheet.getRange(2, 2, 1, 1).setValue(cid); formatsheet.getRange(3, 1, 1, 1).setValue('アカウント名:'); formatsheet.getRange(3, 2, 1, 1).setValue(accountname); formatsheet.getRange(4, 1, 1, 1).setValue('広告グループレポート(今日)'); formatsheet.getRange(4, 1, 1, 10).setBackground('#B0E0E6'); // レポート取得 var report = AdWordsApp.report( 'SELECT CampaignName, AdGroupName, Impressions, Clicks, Ctr, Cost, ConversionsManyPerClick, CostPerConversionManyPerClick, ConversionRateManyPerClick, AveragePosition ' + 'FROM ADGROUP_PERFORMANCE_REPORT ' + 'WHERE AdGroupStatus = ENABLED AND CampaignName = "キャンペーン名をペースト" ' + 'DURING TODAY'); // 上記AWQLで指定したデータをエキスポート report.exportToSheet(rawsheet); // 日付列で昇順ソート rawsheet.sort(1, true); // ヘッダー設定 var header = [ 'キャンペーン名', '広告グループ名', 'IMP数', 'クリック数', 'クリック率', 'コスト', 'CV数', 'CV単価', 'CV率', '平均掲載順位', ]; var headerrange = formatsheet.getRange(5, 1, 1, 10); headerrange.setValues([header]); headerrange.setHorizontalAlignment("center"); headerrange.setFontWeight("bold"); headerrange.setFontSize(8); // rawdataをフォーマットシートへ値コピー(フォーマットシートのクリック率列の書式を%にしておく) var lastRow = rawsheet.getLastRow(); var lastColumn = rawsheet.getLastColumn(); var copyValue = rawsheet.getRange(2, 1, lastRow, lastColumn).getValues(); formatsheet.getRange(6, 1, lastRow, lastColumn).setValues(copyValue); // クリック率列とコンバージョン率列のパーセント表示変換 var formatlastRow = formatsheet.getLastRow(); formatsheet.getRange(1, 5, formatlastRow, 1).setNumberFormat("0.00%"); formatsheet.getRange(1, 9, formatlastRow, 1).setNumberFormat("0.00%"); } // キーワードレポート(今日) function kwreport() { var formatsheet = spreadsheet.getSheetByName('キーワード'); rawsheet.clear(); formatsheet.clear(); // 基本情報とフォーマット formatsheet.getRange(1, 1, 1, 1).setValue('更新時間:'); formatsheet.getRange(1, 2, 1, 1).setValue(formattedDate); formatsheet.getRange(2, 1, 1, 1).setValue('CID:'); formatsheet.getRange(2, 2, 1, 1).setValue(cid); formatsheet.getRange(3, 1, 1, 1).setValue('アカウント名:'); formatsheet.getRange(3, 2, 1, 1).setValue(accountname); formatsheet.getRange(4, 1, 1, 1).setValue('キーワードレポート(今日)'); formatsheet.getRange(4, 1, 1, 12).setBackground('#B0E0E6'); // レポート取得 var report = AdWordsApp.report( 'SELECT CampaignName, AdGroupName, KeywordText, KeywordMatchType, Impressions, Clicks, Ctr, Cost, ConversionsManyPerClick, CostPerConversionManyPerClick, ConversionRateManyPerClick, AveragePosition ' + 'FROM KEYWORDS_PERFORMANCE_REPORT ' + 'WHERE Status = ENABLED and CampaignName = "キャンペーン名をペースト" ' + // 'WHERE Impressions > 0 ' + 'DURING TODAY'); // 上記AWQLで指定したデータをエキスポート report.exportToSheet(rawsheet); // コスト列でソート rawsheet.sort(8, false); // ヘッダー設定 var header = [ 'キャンペーン名', '広告グループ名', 'キーワード', 'マッチタイプ', 'IMP数', 'クリック数', 'クリック率', 'コスト', 'CV数', 'CV単価', 'CV率', '平均掲載順位', ]; var headerrange = formatsheet.getRange(5, 1, 1, 12); headerrange.setValues([header]); headerrange.setHorizontalAlignment("center"); headerrange.setFontWeight("bold"); headerrange.setFontSize(8); // rawdataをフォーマットシートへ値コピー(フォーマットシートのクリック率列の書式を%にしておく) var lastRow = rawsheet.getLastRow(); var lastColumn = rawsheet.getLastColumn(); var copyValue = rawsheet.getRange(2, 1, lastRow, lastColumn).getValues(); formatsheet.getRange(6, 1, lastRow, lastColumn).setValues(copyValue); // クリック率列とコンバージョン率列のパーセント表示変換 var formatlastRow = formatsheet.getLastRow(); formatsheet.getRange(1, 7, formatlastRow, 1).setNumberFormat("0.00%"); formatsheet.getRange(1, 11, formatlastRow, 1).setNumberFormat("0.00%"); } //広告別レポート(今日) function adreport() { var formatsheet = spreadsheet.getSheetByName('広告'); rawsheet.clear(); formatsheet.clear(); // 基本情報とフォーマット formatsheet.getRange(1, 1, 1, 1).setValue('更新時間:'); formatsheet.getRange(1, 2, 1, 1).setValue(formattedDate); formatsheet.getRange(2, 1, 1, 1).setValue('CID:'); formatsheet.getRange(2, 2, 1, 1).setValue(cid); formatsheet.getRange(3, 1, 1, 1).setValue('アカウント名:'); formatsheet.getRange(3, 2, 1, 1).setValue(accountname); formatsheet.getRange(4, 1, 1, 1).setValue('広告レポート(今日)'); formatsheet.getRange(4, 1, 1, 13).setBackground('#B0E0E6'); // レポート取得 var report = AdWordsApp.report( 'SELECT CampaignName, AdGroupName, Headline, Description1, Description2, Impressions, Clicks, Ctr, Cost, ConversionsManyPerClick, CostPerConversionManyPerClick, ConversionRateManyPerClick, AveragePosition ' + 'FROM AD_PERFORMANCE_REPORT ' + 'WHERE Status = ENABLED and CampaignName = "キャンペーン名をペースト" ' + // 'WHERE Impressions > 0 ' + 'DURING TODAY'); // 上記AWQLで指定したデータをエキスポート report.exportToSheet(rawsheet); // コスト列でソート rawsheet.sort(9, false); // ヘッダー設定 var header = [ 'キャンペーン名', '広告グループ名', 'タイトル', '説明文1', '説明文2', 'IMP数', 'クリック数', 'クリック率', 'コスト', 'CV数', 'CV単価', 'CV率', '平均掲載順位', ]; var headerrange = formatsheet.getRange(5, 1, 1, 13); headerrange.setValues([header]); headerrange.setHorizontalAlignment("center"); headerrange.setFontWeight("bold"); headerrange.setFontSize(8); // rawdataをフォーマットシートへ値コピー(フォーマットシートのクリック率列の書式を%にしておく) var lastRow = rawsheet.getLastRow(); var lastColumn = rawsheet.getLastColumn(); var copyValue = rawsheet.getRange(2, 1, lastRow, lastColumn).getValues(); formatsheet.getRange(6, 1, lastRow, lastColumn).setValues(copyValue); // クリック率列とコンバージョン率列のパーセント表示変換 var formatlastRow = formatsheet.getLastRow(); formatsheet.getRange(1, 8, formatlastRow, 1).setNumberFormat("0.00%"); formatsheet.getRange(1,12, formatlastRow, 1).setNumberFormat("0.00%"); } //サーチクエリーレポート(今日) function sqreport() { var formatsheet = spreadsheet.getSheetByName('サーチクエリー'); rawsheet.clear(); formatsheet.clear(); // 基本情報とフォーマット formatsheet.getRange(1, 1, 1, 1).setValue('更新時間:'); formatsheet.getRange(1, 2, 1, 1).setValue(formattedDate); formatsheet.getRange(2, 1, 1, 1).setValue('CID:'); formatsheet.getRange(2, 2, 1, 1).setValue(cid); formatsheet.getRange(3, 1, 1, 1).setValue('アカウント名:'); formatsheet.getRange(3, 2, 1, 1).setValue(accountname); formatsheet.getRange(4, 1, 1, 1).setValue('サーチクエリーレポート(今日)'); formatsheet.getRange(4, 1, 1, 13).setBackground('#B0E0E6'); // レポート取得 var report = AdWordsApp.report( 'SELECT CampaignName, AdGroupName, Query, KeywordTextMatchingQuery, MatchType, Impressions, Clicks, Ctr, Cost, ConversionsManyPerClick, CostPerConversionManyPerClick, ConversionRateManyPerClick, AveragePosition ' + 'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' + 'WHERE CampaignName = "キャンペーン名をペースト" ' + // 'WHERE Impressions > 0 ' + 'DURING TODAY'); // 上記AWQLで指定したデータをエキスポート report.exportToSheet(rawsheet); // コスト列でソート rawsheet.sort(9, false); // ヘッダー設定 var header = [ 'キャンペーン名', '広告グループ名', 'クエリー', 'キーワード', 'マッチタイプ', 'IMP数', 'クリック数', 'クリック率', 'コスト', 'CV数', 'CV単価', 'CV率', '平均掲載順位', ]; var headerrange = formatsheet.getRange(5, 1, 1, 13); headerrange.setValues([header]); headerrange.setHorizontalAlignment("center"); headerrange.setFontWeight("bold"); headerrange.setFontSize(8); // rawdataをフォーマットシートへ値コピー(フォーマットシートのクリック率列の書式を%にしておく) var lastRow = rawsheet.getLastRow(); var lastColumn = rawsheet.getLastColumn(); var copyValue = rawsheet.getRange(2, 1, lastRow, lastColumn).getValues(); formatsheet.getRange(6, 1, lastRow, lastColumn).setValues(copyValue); // クリック率列とコンバージョン率列のパーセント表示変換 var formatlastRow = formatsheet.getLastRow(); formatsheet.getRange(1, 8, formatlastRow, 1).setNumberFormat("0.00%"); formatsheet.getRange(1, 12, formatlastRow, 1).setNumberFormat("0.00%"); } // ログ記録とメール通知設定 function sendmail() { Logger.log('キーワードレポート(今月)の準備ができました\n' + spreadsheet.getUrl()); if (email) { MailApp.sendEmail(email, accountname + 'のレポートの準備ができました' + ' ' + formattedDate, spreadsheet.getUrl()); } }
処理手順
処理手順は以下の通りです:
- あらかじめGoogleスプレッドシートで作ったレポートテンプレートを用意しておきます
- 生データ流し込み用のシート(「データ用」)と本レポート用のシートに分かれています
- 本レポート用シートに更新日時、CID、アカウント名を自動的に書き込み、若干セルに色をつけるフォーマットもします(それ以外のフォーマットは何もする必要はないかと思います。強いていえば列幅は自分で変更ください)
- それぞれのレポートから、指定した項目のデータをを生データ流し込み用シートに展開します
- データを本レポート用シートにコピペします
- クリック率列とコンバージョン率列の数値をパーセントに変換します
- レポートが完成したら、指定したメールアドレスに通知します
注意点
- メール通知用のメールアドレスをコードの2行目に入力してください。
- Googleスプレッドシートはこちらをコピー作成して使ってください。(メニュー→ファイル→コピーを作成)
https://goo.gl/xWQ4XN
または、ご自分でGoogleスプレッドシートを新規作成しても大丈夫です。その際、以下の名前でシートを追加してください。「キャンペーン」、「広告グループ」、「キーワード」、「広告」、「サーチクエリー」、「データ用」(これは非表示にして問題ありません)
コピー作成したスプレッドシートのURLを、コードの3行目に入力してください。
- キャンペーン名はなぜか変数化できなかったので、各レポートのWHERE CampaignName = “キャンペーン名をペースト”となっている部分の””の間にキャンペーン名をペーストしてください(くれぐれも間違えのないよう)。
- データ項目を追加・変更したい場合は、SELECT部分に指定してください。データ項目の選び方は、以前のブログで取り上げたAWQL.meを使いながらやると楽に指定できます。
http://www.quantumleap.jp/2015/02/adwords-script1-google.html
- 本レポートはキャンペーンのみは今月分、その他は当日のデータを取得する設定にしていますが、それぞれのレポート期間指定部分のTHIS_MONTHまたはTODAYの部分を、以下に変更することで違う期間のデータを取得できます。
TODAY | YESTERDAY | LAST_7_DAYS | THIS_WEEK_SUN_TODAY | THIS_WEEK_MON_TODAY | LAST_WEEK | LAST_14_DAYS | LAST_30_DAYS | LAST_BUSINESS_WEEK | LAST_WEEK_SUN_SAT | THIS_MONTH
期間指定を日付で行いたい場合は、DURING 20150301,20150308という表記をしてください。
完成イメージ
メール通知が必要ない方は、17行目のsendmail()を // sendmail()に変更してください。メール通知は停止します。
スプレッドシートに展開されたレポートは以下の通りです。
スマホのGoogleスプレッドシートですと、以下のような感じで見れます。
自動実行の設定(オプション)
このままであれば「実行」を押せば都度スクリプトを走らせることは可能ですが、頻繁に更新/メール確認したい人は、コードを保存した後、管理画面上でスケジュール設定すれば、指定した頻度(毎時間でも)、指定した日時に自動実行できます。
本スクリプトは単一のキャンペーンのみに対応していますが、人によっては他のキャンペーンも同時に見たいと思います。また、前月比較などもニーズとしてはあると思います。このスクリプトをベースに編集いただいてもいいですし、今後私のほうでもグレードアップはしていこうと持っています。
Happy scripting!