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広告のレポートを作成する方法をご紹介しました。

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

詳しく見る

Google AdWords Editorの使い方(基本編2)

Google AdWords Editorの使い方を引き続き解説します。

 

最新の変更を取得

Google AdWordsから最新のデータをダウンロードしましょう。画面上部の黒い帯の「最新の情報を取得」をクリック。

 

20151221-09

この時、2つのダウンロードオプションを選択できます。

  • 基本(速い):First Page Bid 見積もり、ページ上部表示推定入札単価、品質スコア、広告の承認情報が必要ない
  • 詳細データ(遅い):上記の情報を全て含めたい場合

特にお急ぎでない場合は「詳細データ」を選択しましょう。

 

検索連動型広告用キャンペーン追加手順

検索連動型広告用キャンペーンを以下の手順で設定していきます。

(1)キャンペーンの追加

(2)広告グループの追加

(3)広告グループにキーワード追加

(4)広告グループにテキスト広告追加

 

キャンペーンの追加

ウインドウ左下の「キャンペーン」を選択し+ キャンペーンを追加]をクリック。

新しいキャンペーンが追加されました。ここで設定する情報は、管理画面でキャンペーンを追加する場合と同じですが、青囲みした項目については注意をする必要があります。

キャンペーン タイプ

初期値にはまず「検索ネットワークのみ」を選びましょう。「検索ネットワーク(ディスプレイ ネットワーク対応)」を選択してしまうとディスプレイネットワークにも同時に広告が配信されるため、このキャンペーンタイプの使い方をよく知っているようになるまではを「検索ネットワークのみ」を使うことをおすすめします。

ステータス

「有効」の状態で設定すると、Google アドワーズ側で審査手続きが終了したい広告掲載が開始されてしまうため、「一時停止」のステータスにしておくことをおすすめします。任意のタイミングで広告掲載を開始できるので「一時停止」→「有効」にしましょう。

開始日

開始日は基登録した日が設定されます。特定のひづけから運用開始したい場合は、その日を入力してください。ただし、ステータスが「一時停止」の場合、指定した日付に掲載は開催されませんので注意が必要です。

 

広告グループの追加

広告グループの追加は、ウインドウ左上にあり広告グループを追加したいキャンペーンを選択してで対象となるキャンペーンを選択し、ウインドウ左下の中から「広告グループ」を選択[+ 広告グループを追加]をクリックします。すると画面中央に選択するキャンペーンがもう一度出てくるのでチェックボックスをマークしてOKをクリックします。

新しく追加された広告グループが表示されます。ここでは、赤枠で囲んだ部分の設定に気をつけて下さい。

ターゲティング方法の最適化について

この値はまず「無効」とすることをおすすめします。「慎重な拡張」の場合、リマーケティングリストなどのユーザーリストを追加すると、その対象ユーザーと拡張されたユーザーに広告が配信されてしまいますので、運用に慣れてからその設定を使うことをおすすめします。

 

広告グループにキーワード追加

 

キーワード追加は、①ウインドウ左上の対象キャンペーンと広告グループを選択して、左下の「キーワードとターゲット設定」から「キーワード」を選択して「+キーワードを追加」をクリック。

ウィンドが空きますので、キーワードを追加したい広告グループをチェックしてOKをクリック。

追加したいキーワードを入力します。また、「タイプ」を選択しますが、項目として「完全一致」「部分一致」「フレーズ一致」の3つがあり、この中から選択します。

この時マッチタイプとしては、運用を開始されたばかりであれば、「部分一致」で運用をはじめられることをおすすめします。部分一致での運用を行っていき、設定したキーワードが設定したキャンペーン、広告グループに対して当初設定したように、ユーザの検索に対する表示回数、クリック数との関係を見ていきながら、そのキーワードが検索にマッチしているか、それともマッチしていないか、ということが感覚的につかめるようになってから、完全一致、フレーズ一致をバリエーションとして増やしていくことが望ましいと思います。

更に「絞り込み部分一致」を設定していけるようになれば、積極的に使っていきましょう。但し「絞り込み部分一致」は前述の項目としては存在しないので、絞り込みを行いたいキーワードテキストの直前に「+」をつけて、マッチタイプは「部分一致」として設定します。

また、設定に際しては、上限クリック単価の設定が重要です。クリック単価をあまり低くすると、表示順位が下がります。しかし、高くしすぎると必要以上の広告費用がかかってしまうので、設定したキーワードの相場観を持つことが大切です。

広告グループに拡張テキスト広告追加

テキスト広告の追加は「拡張テキスト広告」についてご説明します。拡張テキスト広告は、表示URLの自動識別&パスを個別に2枠設定できることと、従来の広告文のスタイル変更&文字数が増える、というものです。

ウインドウ左上で対象となるキャンペーンと広告グループを選択、「広告と広告表示オプション」の中から「拡張テキスト広告」を選択し、「+ テキスト広告を追加」をクリック。

見出し1,見出し2、説明を入力します。右側に広告表示が掲載されるので、いろいろ試しながらベストな広告文を作成しましょう。必ずキーワードを含めて下さい。

パス、最終ページURL,モバイル用最終ページURLを入力します.

広告計測ツールなどを利用している場合は、「URLオプション」タブに切り替え、最終ページURL、トラッキングテンプレートなど必要な情報を入力します。

変更をアップロード

変更が完了したら、次はその内容をウェブにアップロードします。「変更内容を確認」をクリックしてポップアップウィンドウの「変更内容を確認」をクリック。

最後にウェブ上のGoogleアドワーズに変更内容をアップロードして終了です。黒帯の「送信」-「変更を送信」をクリック。

ポップアップウィンドウの「送信」をクリックすると、変更内容がアップロードされます。これで作業は完了です。

念のために、ウェブ上のGoogleアドワーズにアクセスして変更内容反映の確認をおすすめします。

 

 

Google AdWords Editorの使い方(基本編1)

リスティング広告は、運用がうまくいけばたくさんのユーザーをウェブサイトに呼び込みことができます。ただし、それはリスティング広告の運用方法を正しく理解していること、使用するツールをの正しい使い方を把握している必要があります。こういった作業はキーワード数が数10個程度であれば全く問題はないのですが、広告を大量に運用するようになると作業はとても煩雑になります。一日、コンソールとにらめっこ、ということになりかねません。

そんなときは是非GoogleのAdWords Editorを使って作業量を減らしましょう!AdWords Editorは、オフラインでアカウントの管理作業や、大量の追加や変更作業もスムーズに行うことができます。アカウントの掲載内容をAdWords Editorにダウンロードすることができるので掲載内容の把握も管理画面と比べてスピーディーに行うことができます。

今回の記事では、AdWords Editorのダウンロードから検索連動型広告とGoogle ディスプレイネットワークへの広告配信迄、AdWords Editorを使いこなすまでの最低限の設定やその手順をお話しします。

AdWords Editorをダウンロードする

次のURLからアプリをダウンロードしてください。

http://www.google.co.jp/intl/ja/adwordseditor/

Windows版とMac版が提供されていますので、自分のパソコンの環境にあった方のバージョンを選んでダウンロードしましょう。ここではWindowsのダウンロード方法をご紹介します。手順に従っていけば、簡単にダウンロードができます。ダウンロードが無事完了すると、スタートアップにAdWords Editorが登録されます。アイコンをクリックするとAdWords Editorがが起動します。

 

 

AdWords Editorの起動~設定

AdWords Editorを起動すると、まず表示言語を選択します。「日本語」をクリック。

次に利用規約の確認です。利用規約の文章を最後までスクロールすると「同意する」がアクティブになります。また、規約は保存、印刷が可能です。

次にキャンペーンの既定のターゲットを設定します。言語:日本、地域:日本とします。

次にアカウントの情報をダウンロードします。左上の「追加」をクリック。

Adwordsアカウントにログインします。

 

アカウントへのログイン方法が選べます。「ブラウザを開いてログイン」「アプリ内ブラウザからログイン」のどちらを選択しても問題ないですが、今回は「ブラウザを開いてログイン」する方法でご紹介します。

20151221-04

「ブラウザを開いてログイン」を選択すると上図のウインドウが開きます。

20151221-05

同時に、ブラウザ側にアカウントへのログイン画面が表示され、必要事項を入力すると上図のようなコードが表示されるので、このコードを先ほどのウインドウにコピー&ペーストします。

20151221-06

無事に認証が通ると、ダウンロードを確認するメッセージが表示されます。「すべてのキャンペーン」を選択して「OK」をクリックします。

 

Adwords Editorのメインとなるウインドウが開き、準備完了です。

AdWords Editorを使う際の注意点

AdWords Editorの使用時に最も大切なことは、必ず「最新の変更を取得」をクリックしてアカウントの最新情報をダウンロードすることです。これをしないと、アカウントで作業をして、アップロードした際に、アカウントの情報が先祖返りしてしまうことがあります。ですので、必ず「最新の変更を取得」することを忘れないでください。