Google広告スクリプトでレポートを自動化する手順｜直近14日をスプレッドシートに毎日上書きするコードと、管理画面と数字を合わせる確認
Google広告スクリプトでレポートを自動化しようとサンプルを探すと、前日分を1行ずつシートに追記するコードがよく見つかります。動かせば毎朝シートが埋まるので、これで完成だと思いたくなります。ところが1週間たって先週の数字を管理画面と比べると、コンバージョンだけが合いません。コードは正しく動いています。ずれの原因は、数字を取るタイミングの側にあります。
Google広告の数字には、あとから過去の日付が書き換わる性質があります。その性質に合わせて「直近14日を毎日取り直す」スクリプトを作り、本当に管理画面と合っているかを確かめるところまでを、コードつきで示します。
Google広告スクリプトでレポートを作る仕組み
スクリプトは管理画面の中で動く無料のJavaScript
Google広告スクリプトは、管理画面の「ツール」→「一括操作」→「スクリプト」で作る、JavaScriptのプログラムです。追加の料金はかからず、毎時・毎日・毎週・毎月の頻度で自動実行でき、Googleスプレッドシートへの書き込みもできます。上限は公式ドキュメントに決まっていて、1回の実行は最大30分、承認済みのスクリプトは1アカウント250件までです（Google Ads Scripts「Limits」）。キャンペーン単位で日別の数字を出す程度なら、30分にはまず届きません。列を1つ足したいだけなら、スクリプトを書く前にGoogle広告のカスタム列で足りないかを確かめると、保守するコードが増えずに済みます。
数字はGAQLで取り出し、費用は100万で割る
レポートの数字は、GAQL（Google Ads Query Language）というSQLに似た文で指定して取り出します。取り出す関数はAdsApp.report()とAdsApp.search()の2つで、同じ文を受け付けますが、結果の形が違います。つまずきやすいのは単位で、費用の項目metrics.cost_microsは100万分の1円単位のため、100万で割らないと費用が100万倍で表示されます。コンバージョンは小数になることがあります。データドリブンのアトリビューションでは、1件の成果が複数のクリックに分けて割り当てられるからです。またsegments.date（日付）を取り出すときは、WHEREで期間を指定する必要があります。
ADminiのGoogle広告連携なら、コードを書かずに費用・クリック・コンバージョンを1日1回取り込み、ほかの媒体と同じ表に並べられます。ADminiなら無料で始められます。無料で試してみる →
直近14日を毎日上書きするスクリプトの作り方
出力先のシートを用意し、コードを貼る
最初に出力先のGoogleスプレッドシートを作り、シート名を「raw」にしてURLを控えます。ファイルのタイムゾーン（「ファイル」→「設定」）は、広告アカウントと同じ日本にそろえます。ずれていると、日付の比較が1日ずれて、残すべき行を消すことがあります。次に、スクリプトの画面で新しいスクリプトを作り、次のコードを貼って1行目のURLを書き換えます。
const SHEET_URL = 'ここに出力先スプレッドシートのURL';
const SHEET_NAME = 'raw';
const DAYS = 14; // 毎回取り直す日数
const HEADER = ['日付', 'キャンペーン', '表示回数', 'クリック', '費用', 'コンバージョン', 'コンバージョン値'];
function main() {
const tz = AdsApp.currentAccount().getTimeZone();
const day = 24 * 60 * 60 * 1000;
const fmt = function (d) { return Utilities.formatDate(d, tz, 'yyyy-MM-dd'); };
const num = function (v) { return Number(v || 0); };
const start = fmt(new Date(Date.now() - DAYS * day));
const end = fmt(new Date(Date.now() - day));
const query = 'SELECT segments.date, campaign.name, metrics.impressions, metrics.clicks, ' +
'metrics.cost_micros, metrics.conversions, metrics.conversions_value FROM campaign ' +
"WHERE segments.date BETWEEN '" + start + "' AND '" + end + "' ORDER BY segments.date";
const fresh = [];
const rows = AdsApp.search(query);
while (rows.hasNext()) {
const r = rows.next();
fresh.push([r.segments.date, r.campaign.name, num(r.metrics.impressions), num(r.metrics.clicks),
num(r.metrics.costMicros) / 1000000, num(r.metrics.conversions), num(r.metrics.conversionsValue)]);
}
const sheet = SpreadsheetApp.openByUrl(SHEET_URL).getSheetByName(SHEET_NAME);
const last = sheet.getLastRow();
const kept = last > 1 ? sheet.getRange(2, 1, last - 1, HEADER.length).getValues().filter(function (row) {
const d = row[0] instanceof Date ? fmt(row[0]) : String(row[0]);
return d < start; // 取り直す期間より前の行だけ残す
}) : [];
const all = [HEADER].concat(kept, fresh);
sheet.clearContents();
sheet.getRange(1, 1, all.length, HEADER.length).setValues(all);
}このコードは、昨日までの14日分をキャンペーン×日付の行で取り出し、シートにある同じ期間の行を消してから書き直します。14日より前の行は残るため、毎日動かすと過去の数字が積み上がり、直近の2週間だけが毎朝入れ替わります。num()で空の値を0にしているのは、AdsApp.search()の結果では値が0の項目が省かれることがあるためです。
プレビューで確かめ、午前3時以降に毎日実行する
自動実行は頻度を「毎日」、時刻を午前3時以降にします。Googleは前日のデータでシートを更新するサンプル「アカウント概要レポート」で、統計の反映が最大3時間遅れるとして午前3時以降の実行を勧めています。その前に「承認」でスプレッドシートへのアクセスを許可し、プレビューで実行してログとシートを確かめます。表示回数とクリックが管理画面の昨日の値と同じなら、取り出し方は合っています。広告アカウントが複数あるなら、同じスクリプトを各アカウントに置くか、MCC（クライアントセンター）のスクリプトで1回の実行につき最大50アカウントを並行して処理します。
自動で出した数字が管理画面と合っているかを確かめる
前日分の追記だけでは、あとから増えたコンバージョンを取り逃がす
前日分だけを追記するシートは、追記した朝の数字のまま固まります。Google広告の「コンバージョン」列は、成果が起きた日ではなく広告がクリックされた日に計上されるため、数日前のクリックから今日申し込みがあると、過去の日付の数字があとから増えるからです。次の表は、10月14日の行が取得のたびにどう変わるかを、再現用のサンプルデータで示したものです（実在のアカウントの数字ではありません）。
| 取得した朝 | 管理画面の10月14日 | 前日分を追記するシート | 直近14日を上書きするシート |
|---|---|---|---|
| 10月15日 | 12件 | 12件 | 12件 |
| 10月17日 | 15件 | 12件 | 15件 |
| 10月21日 | 16件 | 12件 | 16件 |
| 10月29日 | 17件 | 12件 | 16件（取り直す範囲の外） |
上書きするシートも万能ではありません。10月29日の朝には10月14日が取り直す範囲から外れ、その後に増えた1件は拾えません。14日より前の変動は、月の締めで前月分をまとめて取り直す運用で埋めます。月初にDAYSを45にして1回だけ手動で実行し、前月分をまとめて書き直す方法が手軽です。常にDAYSを30にすれば拾える範囲は広がりますが、そのぶん毎回書き直す行も増えます。
合わないときに見る3か所
取り直しても管理画面と合わない場合、原因はたいてい3か所のどれかです。1つ目はタイムゾーンで、スプレッドシートと広告アカウントの設定が違うと、日付の境目がずれます。2つ目は列の種類で、管理画面で「すべてのコンバージョン」を見ていると、スクリプトが取るmetrics.conversions（「コンバージョン」列）とは一致しません。3つ目は期間で、管理画面の期間に今日が含まれていると、スクリプトの「昨日まで」と範囲が違います。確かめる順番は、表示回数→費用→コンバージョンです。表示回数が合っていてコンバージョンだけ合わないなら、列の種類か取得のタイミングを疑うと切り分けが早く進みます。
ここまでを自分で保守する手間を減らしたいなら、ADmini（アドミニ）のように媒体と直接つないで毎日取り込む道具に任せる手もあります。ADminiはGoogle広告をキャンペーン・広告グループ・キーワード・検索語句の単位で連携し、1日1回の自動更新で表示回数・クリック・費用・コンバージョン・コンバージョン値をそろえます。ただし、取得する指標はこの5つで、インプレッションシェアなどを並べたいならスクリプトのほうが自由がききます。LINEヤフー広告にも同じ考え方のスクリプトがあり、承認が外れて止まる条件はYahoo!広告のレポートをスクリプトで自動化する手順で整理しています。
まとめ
Google広告スクリプトのレポートは、GAQLで数字を取り出し、費用を100万で割ってシートに書くだけで動きます。ただし前日分を追記するだけの作りでは、あとから増えたコンバージョンを取り逃がします。直近14日を毎日上書きし、月の締めで前月分を取り直せば、管理画面とのずれの大半は消えます。
よくある失敗は、追記型のシートで月次報告を作り、クライアントから「管理画面とCVの数が違う」と指摘されてから気づく形です。その時点では、どの日の数字がいつ固まったのかを誰も説明できません。スクリプトでもADmini（アドミニ）のような連携ツールでも、道具が担うのは数字を運ぶところまでで、どの日付から先を確定値として扱うかは人が決めて、シートの使い方に書いておく必要があります。数字のまとめ方を含めたレポート全体の組み立ては広告レポートの作り方と自動化のまとめにあります。まず今週やることは、上のコードを1つのアカウントで動かし、3日後に5日前の行のコンバージョンを管理画面と見比べることです。
ADminiのGoogle広告連携なら、スクリプトを保守せずに前日までの数字を1日1回取り込めます。ADminiなら無料で始められます。無料で試してみる →
よくある質問
Google広告スクリプトでレポートをスプレッドシートに自動出力できますか？
できます。管理画面の「ツール」→「一括操作」→「スクリプト」でスクリプトを作り、GAQLで取り出した数字をスプレッドシートへ書き込むコードを貼って、頻度を「毎日」に設定します。追加の料金はかかりません。前日分だけを追記するとあとから増えたコンバージョンを取り逃がすため、直近14日程度を毎日取り直す作りにします。
Google広告スクリプトの費用の数字が大きすぎるのはなぜですか？
費用の項目metrics.cost_microsが、100万分の1円単位で返るためです。1,000,000で割ると円になります。割らずにシートへ書くと、費用が100万倍で表示されます。コンバージョンが小数になるのは、データドリブンのアトリビューションで1件の成果が複数のクリックに分けて割り当てられるためで、誤りではありません。
Google広告スクリプトで出した数字が管理画面と合わないのはなぜですか？
多くは取得のタイミングです。Google広告のコンバージョンは広告がクリックされた日に計上されるため、過去の日付の数字があとから増えます。ほかに、スプレッドシートと広告アカウントのタイムゾーンの違い、管理画面で「すべてのコンバージョン」を見ている、管理画面の期間に今日が含まれている、の3点を確かめます。
Google広告スクリプトは何時に実行すればよいですか？
前日のデータを書き込むなら、午前3時以降に毎日実行します。Googleは公式サンプル「アカウント概要レポート」で、統計の反映が最大3時間遅れることがあるとして、午前3時以降の実行を勧めています。1回の実行は最大30分までなので、出力する行が多い場合は期間や集計の単位を絞ります。