Google Apps Scriptを駆使せよ！Adsenseデータをスプレッドシートに自動取得する方法

Adsenseのデータをスプレッドシートに自動取得できる？？

どうも、Linです。全力で、血糖値あげてます。

サイト運営者が導入しやすいウェブ広告といえばGoogle Adsense。

コードを貼り付けるだけで Google アドネットワークの広告を表示できるので、初心者の方にもすぐに利用にできるおすすめの広告です。

僕もこの Google AdSense に日頃からお世話になっている身です。

アドセンスの見積もり収益額を毎日スプレッドシートにコピー＆ペーストして月の売上を予測したりしていましたが、

毎日コピー&ペーストするのは正直だるい！

1日当たりの作業量は大したことないかもしれませんが、毎日やると長い目でとんでもない時間とカロリーをロスしていることになります。

そこで、

Google アドセンスのレポートデータを取得して自動でスプレットシートに記録する方法

を探してみてました。

すると、こちらの神記事にたどり着き、なんとかGoogle アドセンスのデータをスプレッドシートに自動で取得できるようになったのです↓

LIVESENSE DIGITAL MARKETING

AdSense Management APIを使用したパフォーマンスデータの自動レポーティング

http://marketing.livesense.co.jp/post/68748588614

こんにちは、リブセンスの岩崎亮です。 LDMの投稿としてはデマンドサイドの話が多かったので、今回は趣向を変えてサプライサイドの話を少ししようかと思います。 AdSenseといえばサプライサイドにとっては無くてはならない収益源の一つですが、高い収益性を上げるた...

今日はその方法を忘れないうちにまとめておきますね。

Adsenseのデータをスプレッドシートに自動取得する方法

Adsenseのデータをスプレッドシートに自動取得するためには、

Adsnse Management API
Google Apps Script

を使っていきますよ。

Step1. スプレッドシートを作成する

まずデータを取得するためのスプレッドシートを作成していきましょう。

今回は「AdSense 練習」という名前のスプレッドシートにしてみました。

Step2. スクリプトエディタを開く

スプレッドシートのメニューから「ツール」と進むと「スクリプトエディタ」というものがあります。

こちらをクリックすると、プロジェクト新規作成の画面になります。

名前が必要なので適当に「AdSense API」とでも名付けておきましょう。

すると、別ウィンドウでスクリプトエディタが開きます。

こちらにGoogle Apps Scriptのコードを書いていくことになりますね。

Step3. プロジェクト作成

スクリプトエディタのメニューから「リソース」と進むと、「Google の拡張サービス」があると思います。

Step4. AdSense の API を有効化する

API の一覧ができますので、今回は「 AdSense Management API」を有効にしましょう。

有効にしただけは十分ではないので、さらに下の方にある「 Google Cloud platform APIダッシュボード」をクリックしてみます。

そこの「API とサービスの有効化」をクリックすると、

Google Cloud platform の画面になります。

「adsense」と検索してみると、 AdSense Management API が出てきます。

こちらを「有効にする」を選んであげましょう。

これでAPIの有効化の設定は完了なので、先ほどのスクリプトエディタに戻りましょう。

Step5. スクリプトを書く

いよいよGoogle Apps Scriptのコードを書いていきますよ。

次のようなコードをスクリプトエディタにコピー＆ペーストしてみてください。

function report() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];
 var adClientId = 'ca-pub-xxxxxxxxxxxxx'
 var startDate = '2018-12-01';
 var endDate = '2018-12-31';
 var metrics = ['PAGE_VIEWS', 'CLICKS', 'PAGE_VIEWS_CTR', 'COST_PER_CLICK', 'PAGE_VIEWS_RPM', 'EARNINGS']
 var args = {
 'metric': metrics,
 'dimension': ['DATE'],
 'filter': ['AD_CLIENT_ID==' + adClientId],
 };
 var report = AdSense.Reports.generate(startDate, endDate, args).getRows();
 sheet.getRange(2, 1, report.length, metrics.length + 1).setValues(report);
}

function report() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheets()[0];

var adClientId = 'ca-pub-xxxxxxxxxxxxx'

var startDate = '2018-12-01';

var endDate = '2018-12-31';

var metrics = ['PAGE_VIEWS', 'CLICKS', 'PAGE_VIEWS_CTR', 'COST_PER_CLICK', 'PAGE_VIEWS_RPM', 'EARNINGS']

var args = {

'metric': metrics,

'dimension': ['DATE'],

'filter': ['AD_CLIENT_ID==' + adClientId],

};

var report = AdSense.Reports.generate(startDate, endDate, args).getRows();

sheet.getRange(2, 1, report.length, metrics.length + 1).setValues(report);

}

ポイントは次の3つ。

まず自分のアドセンス IDを記載する必要がありますね。

スクリプトの中の「’ca-pub-xxxxxxxxxxxxx’」のpub以下のxxxxの部分には自分のアドセンスIDを入れてみましょう。

アドセンスIDは自分のアドセンス広告コードを見れば「’ca-pub-xxxxxxxxxxxxx’」という書式でかいてあるはずです。

それと、データを取得する日付も変更します。

startDate
endDate

という変数がデータ取得開始日と終了日になりますので、

var startDate = '2018-12-01'; 
var endDate = '2018-12-31';

1 2	var startDate = '2018-12-01'; var endDate = '2018-12-31';

と自分好みに変えてやればいいわけですね。

後はメトリックス（metrics）。

これはどのデータを取得するかを指定できますね。

今回のサンプルコードでは、

var metrics = ['PAGE_VIEWS', 'CLICKS', 'PAGE_VIEWS_CTR', 'COST_PER_CLICK', 'PAGE_VIEWS_RPM', 'EARNINGS']

1	var metrics = ['PAGE_VIEWS', 'CLICKS', 'PAGE_VIEWS_CTR', 'COST_PER_CLICK', 'PAGE_VIEWS_RPM', 'EARNINGS']

となっていますので、

Metrics	意味
PAGE_VIEWS	PV数
CLICKS	標準的なコンテンツ広告がクリックされた回数
PAGE_VIEWS_CTR	クリックにつながったページビューの割合
COST_PER_CLICK	広告がユーザーにクリックされるたびに発生する収益
PAGE_VIEWS_RPM	ページビュー 1,000 回あたりの収益
EARNINGS	サイト運営者の見積もり収益額

というデータが取得できます。

メトリックスについては様々な仕様が用意されていますので、詳しくはこちらのページから選んでみてくださいね。

Google Developers

指標とディメンション | AdSense Management API | ...

https://developers.google.com/adsense/management/metrics-dimensions?hl=ja#metrics

Step6. スクリプトを実行する

スクリプトを実行してアドセンスのデータを取得していきましょう。

ただし、スクリプトを実行するときにアクセスの許可を取らなければなりません。

この作業には粘り強さが必要で、強引に突破していきましょう。

スクリプトを実行すると、

まず「許可を確認」して、

アカウントを指定すると、

やばそうな警告画面が出現。

ここで動じずに「詳細」をクリックして隠し画面を出します。

そこに小さく「安全ではないページに移動」がありますので、そちらをクリックして、

「許可」を押してやるとスクリプトを実行できるでしょう。

ふう、長かったぜ。

Step7. トリガーの設定

ここまででデータを取得することができましたね。

後は

どのタイミングでデータを取得するのか？？

を設定していきます。

これを設定してやれば、サイト運営者が手を下さずとも、自動的にアドセンスのデータを取得してスプレッドシートに記録してくれるのですね。

スクリプトエディタのメニューから「編集」と進むと「現在のプロジェクトのトリガー」がありますのでそちらをクリック。

すると、次のようなトリガーの画面が出てきます。

まず下の「トリガーを追加」をクリックしてみます。

すると、トリガー設定画面が出てきますので今回は「イベントのソースを選択」から「時間主導型」を選んでみます。

すると、

時間ベースのトリガーのタイプを選択
時刻を選択

が出てきますのでこれらを設定していきましょう。

「時間ベースのトリガーのタイプを選択」では、どういう頻度でデータを取得するかを決め、「時刻を選択」ではスクリプトを実行する時間帯を選ぶのです。

僕の例

最後に、僕がどのようにアドセンスのデータをスプレッドシートに記録したのかを書いてきましょう。

僕は毎日、

アドセンスの売上見積もり金額
表示回数
CTR
CPC

が知りたかったのでMetricsには、

EARNINGS
PAGE_VIEWS
PAGE_VIEWS_CTR
COST_PER_CLICK

を指定してやりました。

スクリプトは次のようになりましたね↓

function report() {

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var now = new Date();
 
  if(now.getDate() == 1){
    var yesterday=new Date(now.getFullYear(), now.getMonth(), 0);
    var firstDate = new Date(now.getFullYear(), now.getMonth()-1, 1);
    
  }else{    
 var yesterday = new Date(now.getFullYear(), now.getMonth(), now.getDate() - 1);
    var firstDate = new Date(now.getFullYear(), now.getMonth(), 1);
    
  }
 
 var sheet = ss.getSheets()[0];
 var adClientId = 'ca-pub-xxxxxxxxxxxxx'
 var startDate = Utilities.formatDate( firstDate, 'Asia/Tokyo', 'yyyy-MM-dd');
 var endDate = Utilities.formatDate( yesterday, 'Asia/Tokyo', 'yyyy-MM-dd');
 var metrics = ['PAGE_VIEWS', 'PAGE_VIEWS_CTR', 'COST_PER_CLICK', 'EARNINGS']
 var args = {
 'metric': metrics,
 'dimension': ['DATE'],
 'filter': ['AD_CLIENT_ID==' + adClientId],
  'useTimezoneReporting': true,
 };
 var report = AdSense.Reports.generate(startDate, endDate, args).getRows();

 sheet.getRange(7, 2, report.length, metrics.length + 1).setValues(report);
}

function report() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var now = new Date();

if(now.getDate() == 1){

var yesterday=new Date(now.getFullYear(), now.getMonth(), 0);

var firstDate = new Date(now.getFullYear(), now.getMonth()-1, 1);

}else{

var yesterday = new Date(now.getFullYear(), now.getMonth(), now.getDate() - 1);

var firstDate = new Date(now.getFullYear(), now.getMonth(), 1);

}

var sheet = ss.getSheets()[0];

var adClientId = 'ca-pub-xxxxxxxxxxxxx'

var startDate = Utilities.formatDate( firstDate, 'Asia/Tokyo', 'yyyy-MM-dd');

var endDate = Utilities.formatDate( yesterday, 'Asia/Tokyo', 'yyyy-MM-dd');

var metrics = ['PAGE_VIEWS', 'PAGE_VIEWS_CTR', 'COST_PER_CLICK', 'EARNINGS']

var args = {

'metric': metrics,

'dimension': ['DATE'],

'filter': ['AD_CLIENT_ID==' + adClientId],

'useTimezoneReporting': true,

};

var report = AdSense.Reports.generate(startDate, endDate, args).getRows();

sheet.getRange(7, 2, report.length, metrics.length + 1).setValues(report);

}

データの取得期間に関しては、月初から昨日まで。

トリガーは「時間主導型」の「日付ベース」で「4時から5時 am」と設定してみました。

これにより、

毎日朝起きる前にアドセンスのデータを自動でスプレットシートに記録してもらえます。

後は、タイムゾーンに注意ですね。

デフォルトは米国の時間になっていてレポートの値がずれてしまうので「useTimezoneReporting」という引数をtrueにしてやる必要がありました。

Qiita

「AdSense Management API経由の数字がウェブUIで見た数字とずれてる？」と思った...

https://qiita.com/marqs/items/36e45f0bb26b838cfa23

AdSense Management APIはGoogle Apps Scriptで数字がとれたりして、とても便利です。ただ、チュートリアルにあるサンプルを使っていると、タイムゾーンがデフォルト（PTZ）のままになっていて、ウェブU...

もっとも苦戦したのが月初1日にデータを取得するケースです。

1日の場合はgetMonth()メソッドを発動すると新しいその月が取得されてしまうので、月初に先月の月末データが取れませんでした。

そこで、if文で1日だけを場合分けして日付を指定したところ、なんとか月初も動いてくれました汗

「エラーが出てしまってデータ取得できねえよ！？」

いう方にはエンジニア専門のQAサイト「teratail」で質問してみるいいかもしれません。

よかったら使ってみてください。

それでは！

Lin

飯橋凛（Ihashi Lin）

新卒で入ったネット広告代理店を1年半で退職。
その後、副業として活動していたウェブメディア運営のフリーランスへ。
現在はプログラミングの勉強に励み、0からフリーエンジニアの道を目指す。好きな映画は「メリーに首ったけ」
>>Twitterをフォローする