· 6 years ago · Nov 22, 2019, 06:38 AM
1// Copyright 2016, Google Inc. All Rights Reserved.
2//
3// Licensed under the Apache License, Version 2.0 (the "License");
4// you may not use this file except in compliance with the License.
5// You may obtain a copy of the License at
6//
7// http://www.apache.org/licenses/LICENSE-2.0
8//
9// Unless required by applicable law or agreed to in writing, software
10// distributed under the License is distributed on an "AS IS" BASIS,
11// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12// See the License for the specific language governing permissions and
13// limitations under the License.
14
15/**
16 * @name Export Data to BigQuery
17 *
18 * @overview The Export Data to BigQuery script sets up a BigQuery
19 * dataset and tables, downloads a report from Google Ads and then
20 * loads the report to BigQuery.
21 *
22 * @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
23 *
24 * @version 1.4
25 *
26 * @changelog
27 * - version 1.4
28 * - Inserts are split into <10Mb chunks.
29 * - Compress backups to Drive.
30 *
31 * @changelog
32 * - version 1.3
33 * - Global string replace to escape quotes.
34 *
35 * @changelog
36 * - version 1.2
37 * - Global string replace to remove commas.
38 *
39 * @changelog
40 * - version 1.1
41 * - Removed commas from numbers to fix formatting issues.
42 *
43 * @changelog
44 * - version 1.0
45 * - Released initial version.
46 */
47
48var CONFIG = {
49 BIGQUERY_PROJECT_ID: 'ru-zarplata',
50 BIGQUERY_DATASET_ID: 'google_ads',
51
52 // Truncate existing data, otherwise will append.
53 TRUNCATE_EXISTING_DATASET: false,
54 TRUNCATE_EXISTING_TABLES: false,
55
56 // Back up reports to Google Drive.
57 WRITE_DATA_TO_DRIVE: false,
58 // Folder to put all the intermediate files.
59 DRIVE_FOLDER: 'INSERT_FOLDER_NAME',
60
61 // Default date range over which statistics fields are retrieved.
62 //DEFAULT_DATE_RANGE: 'YESTERDAY',
63 DEFAULT_DATE_RANGE: 'LAST_7_DAYS',
64
65 // Lists of reports and fields to retrieve from Google Ads.
66 REPORTS: [{NAME: 'AD_PERFORMANCE_REPORT',
67 CONDITIONS: 'WHERE Impressions > 0',
68 FIELDS: {'CampaignId' : 'INTEGER',
69 'CampaignName' : 'STRING',
70 'Cost' : 'FLOAT',
71 'Impressions' : 'INTEGER',
72 'Clicks' : 'INTEGER',
73 'Date' : 'STRING',
74 'AverageCpc' : 'FLOAT',
75 'Ctr' : 'FLOAT',
76 'AdGroupId' : 'INTEGER',
77 'AdGroupName' : 'STRING',
78 'CriterionId' : 'INTEGER',
79 'Id' : 'INTEGER'
80 }
81 }, {NAME: 'ACCOUNT_PERFORMANCE_REPORT',
82 CONDITIONS: '',
83 FIELDS: {'Cost' : 'FLOAT',
84 'AverageCpc' : 'FLOAT',
85 'Ctr' : 'FLOAT',
86 'AveragePosition' : 'FLOAT',
87 'Impressions' : 'INTEGER',
88 'Clicks' : 'INTEGER',
89 'Date' : 'STRING'
90 }
91 }, {NAME: 'CAMPAIGN_PERFORMANCE_REPORT',
92 CONDITIONS: 'WHERE Impressions > 0',
93 FIELDS: {'CampaignId' : 'STRING',
94 'CampaignName' : 'STRING',
95 'Date' : 'STRING',
96 'Impressions' : 'INTEGER',
97 'Clicks' : 'INTEGER',
98 'Cost' : 'FLOAT'
99 }
100 }, {NAME: 'KEYWORDS_PERFORMANCE_REPORT',
101 CONDITIONS: 'WHERE CampaignStatus = ENABLED',
102 FIELDS: {'CampaignName' : 'STRING',
103 'AdGroupName' : 'STRING',
104 'Criteria' : 'STRING',
105 'Impressions' : 'INTEGER',
106 'Cost' : 'FLOAT',
107 'Clicks' : 'INTEGER',
108 'QualityScore' : 'FLOAT',
109 'Date' : 'STRING'
110 }
111 }, {NAME: 'SEARCH_QUERY_PERFORMANCE_REPORT',
112 CONDITIONS: '',
113 FIELDS: {'Query' : 'STRING',
114 'Cost' : 'FLOAT',
115 'AverageCpc' : 'FLOAT',
116 'Ctr' : 'FLOAT',
117 'AveragePosition' : 'FLOAT',
118 'Impressions' : 'INTEGER',
119 'Clicks' : 'INTEGER',
120 'Date' : 'STRING'
121 }
122 }],
123
124 RECIPIENT_EMAILS: [
125 'an.anisimova@zarplata.ru'
126 ]
127};
128
129// Impose a limit on the size of BQ inserts: 10MB - 512Kb for overheads.
130var MAX_INSERT_SIZE = 10 * 1024 * 1024 - 512 * 1024;
131
132/**
133 * Main method
134 */
135function main() {
136 createDataset();
137 for (var i = 0; i < CONFIG.REPORTS.length; i++) {
138 var reportConfig = CONFIG.REPORTS[i];
139 createTable(reportConfig);
140 }
141
142 var jobIds = processReports();
143 waitTillJobsComplete(jobIds);
144 sendEmail(jobIds);
145}
146
147
148/**
149 * Creates a new dataset.
150 *
151 * If a dataset with the same id already exists and the truncate flag
152 * is set, will truncate the old dataset. If the truncate flag is not
153 * set, then will not create a new dataset.
154 */
155function createDataset() {
156 if (datasetExists()) {
157 if (CONFIG.TRUNCATE_EXISTING_DATASET) {
158 BigQuery.Datasets.remove(CONFIG.BIGQUERY_PROJECT_ID,
159 CONFIG.BIGQUERY_DATASET_ID, {'deleteContents' : true});
160 Logger.log('Truncated dataset.');
161 } else {
162 Logger.log('Dataset %s already exists. Will not recreate.',
163 CONFIG.BIGQUERY_DATASET_ID);
164 return;
165 }
166 }
167
168 // Create new dataset.
169 var dataSet = BigQuery.newDataset();
170 dataSet.friendlyName = CONFIG.BIGQUERY_DATASET_ID;
171 dataSet.datasetReference = BigQuery.newDatasetReference();
172 dataSet.datasetReference.projectId = CONFIG.BIGQUERY_PROJECT_ID;
173 dataSet.datasetReference.datasetId = CONFIG.BIGQUERY_DATASET_ID;
174
175 dataSet = BigQuery.Datasets.insert(dataSet, CONFIG.BIGQUERY_PROJECT_ID);
176 Logger.log('Created dataset with id %s.', dataSet.id);
177}
178
179/**
180 * Checks if dataset already exists in project.
181 *
182 * @return {boolean} Returns true if dataset already exists.
183 */
184function datasetExists() {
185 // Get a list of all datasets in project.
186 var datasets = BigQuery.Datasets.list(CONFIG.BIGQUERY_PROJECT_ID);
187 var datasetExists = false;
188 // Iterate through each dataset and check for an id match.
189 if (datasets.datasets != null) {
190 for (var i = 0; i < datasets.datasets.length; i++) {
191 var dataset = datasets.datasets[i];
192 if (dataset.datasetReference.datasetId == CONFIG.BIGQUERY_DATASET_ID) {
193 datasetExists = true;
194 break;
195 }
196 }
197 }
198 return datasetExists;
199}
200
201/**
202 * Creates a new table.
203 *
204 * If a table with the same id already exists and the truncate flag
205 * is set, will truncate the old table. If the truncate flag is not
206 * set, then will not create a new table.
207 *
208 * @param {Object} reportConfig Report configuration including report name,
209 * conditions, and fields.
210 */
211function createTable(reportConfig) {
212 if (tableExists(reportConfig.NAME)) {
213 if (CONFIG.TRUNCATE_EXISTING_TABLES) {
214 BigQuery.Tables.remove(CONFIG.BIGQUERY_PROJECT_ID,
215 CONFIG.BIGQUERY_DATASET_ID, reportConfig.NAME);
216 Logger.log('Truncated table %s.', reportConfig.NAME);
217 } else {
218 Logger.log('Table %s already exists. Will not recreate.',
219 reportConfig.NAME);
220 return;
221 }
222 }
223
224 // Create new table.
225 var table = BigQuery.newTable();
226 var schema = BigQuery.newTableSchema();
227 var bigQueryFields = [];
228
229 // Add each field to table schema.
230 var fieldNames = Object.keys(reportConfig.FIELDS);
231 for (var i = 0; i < fieldNames.length; i++) {
232 var fieldName = fieldNames[i];
233 var bigQueryFieldSchema = BigQuery.newTableFieldSchema();
234 bigQueryFieldSchema.description = fieldName;
235 bigQueryFieldSchema.name = fieldName;
236 bigQueryFieldSchema.type = reportConfig.FIELDS[fieldName];
237
238 bigQueryFields.push(bigQueryFieldSchema);
239 }
240
241 schema.fields = bigQueryFields;
242 table.schema = schema;
243 table.friendlyName = reportConfig.NAME;
244
245 table.tableReference = BigQuery.newTableReference();
246 table.tableReference.datasetId = CONFIG.BIGQUERY_DATASET_ID;
247 table.tableReference.projectId = CONFIG.BIGQUERY_PROJECT_ID;
248 table.tableReference.tableId = reportConfig.NAME;
249
250 table = BigQuery.Tables.insert(table, CONFIG.BIGQUERY_PROJECT_ID,
251 CONFIG.BIGQUERY_DATASET_ID);
252
253 Logger.log('Created table with id %s.', table.id);
254}
255
256/**
257 * Checks if table already exists in dataset.
258 *
259 * @param {string} tableId The table id to check existence.
260 *
261 * @return {boolean} Returns true if table already exists.
262 */
263function tableExists(tableId) {
264 // Get a list of all tables in the dataset.
265 var tables = BigQuery.Tables.list(CONFIG.BIGQUERY_PROJECT_ID,
266 CONFIG.BIGQUERY_DATASET_ID);
267 var tableExists = false;
268 // Iterate through each table and check for an id match.
269 if (tables.tables != null) {
270 for (var i = 0; i < tables.tables.length; i++) {
271 var table = tables.tables[i];
272 if (table.tableReference.tableId == tableId) {
273 tableExists = true;
274 break;
275 }
276 }
277 }
278 return tableExists;
279}
280
281/**
282 * Process all configured reports
283 *
284 * Iterates through each report to: retrieve Google Ads data,
285 * backup data to Drive (if configured), load data to BigQuery.
286 *
287 * @return {Array.<string>} jobIds The list of all job ids.
288 */
289function processReports() {
290 var jobIds = [];
291
292 // Iterate over each report type.
293 for (var i = 0; i < CONFIG.REPORTS.length; i++) {
294 var reportConfig = CONFIG.REPORTS[i];
295 Logger.log('Running report %s', reportConfig.NAME);
296 // Get data as an array of CSV chunks.
297 var csvData = retrieveAdsReport(reportConfig);
298
299 // If configured, back up data.
300 if (CONFIG.WRITE_DATA_TO_DRIVE) {
301 var folder = getDriveFolder();
302 for (var r = 0; r < csvData.length; r++) {
303 var fileName = reportConfig.NAME + '_' + (r + 1);
304 saveCompressedCsvFile(folder, fileName, csvData[r]);
305 }
306 Logger.log('Exported data to Drive folder %s for report %s.',
307 CONFIG.DRIVE_FOLDER, reportConfig.NAME);
308 }
309
310 for (var j = 0; j < csvData.length; j++) {
311 // Convert to Blob format.
312 var blobData = Utilities.newBlob(csvData[j], 'application/octet-stream');
313 // Load data
314 var jobId = loadDataToBigquery(reportConfig, blobData, !j ? 1 : 0);
315 jobIds.push(jobId);
316 }
317 }
318 return jobIds;
319}
320
321/**
322 * Writes a CSV file to Drive, compressing as a zip file.
323 *
324 * @param {!Folder} folder The parent folder for the file.
325 * @param {string} fileName The name for the file.
326 * @param {string} csvData The CSV data to write to the file.
327 */
328function saveCompressedCsvFile(folder, fileName, csvData) {
329 var compressed = Utilities.zip([Utilities.newBlob(csvData)]);
330 compressed.setName(fileName);
331 folder.createFile(compressed);
332}
333
334/**
335 * Retrieves Google Ads data as csv and formats any fields
336 * to BigQuery expected format.
337 *
338 * @param {Object} reportConfig Report configuration including report name,
339 * conditions, and fields.
340 *
341 * @return {!Array.<string>} a chunked report in csv format.
342 */
343function retrieveAdsReport(reportConfig) {
344 var fieldNames = Object.keys(reportConfig.FIELDS);
345 var report = AdsApp.report(
346 'SELECT ' + fieldNames.join(',') +
347 ' FROM ' + reportConfig.NAME + ' ' + reportConfig.CONDITIONS +
348 ' DURING ' + CONFIG.DEFAULT_DATE_RANGE);
349 var rows = report.rows();
350 var chunks = [];
351 var chunkLen = 0;
352 var csvRows = [];
353 var totalRows = 0;
354 // Header row
355 var header = fieldNames.join(',');
356 csvRows.push(header);
357 chunkLen += Utilities.newBlob(header).getBytes().length + 1;
358
359 // Iterate over each row.
360 while (rows.hasNext()) {
361 var row = rows.next();
362
363 if (chunkLen > MAX_INSERT_SIZE) {
364 chunks.push(csvRows.join('\n'));
365 totalRows += csvRows.length;
366 chunkLen = 0;
367 csvRows = [];
368 }
369 var csvRow = [];
370 for (var i = 0; i < fieldNames.length; i++) {
371 var fieldName = fieldNames[i];
372 var fieldValue = row[fieldName].toString();
373 var fieldType = reportConfig.FIELDS[fieldName];
374 // Strip off % and perform any other formatting here.
375 if (fieldType == 'FLOAT' || fieldType == 'INTEGER') {
376 if (fieldValue.charAt(fieldValue.length - 1) == '%') {
377 fieldValue = fieldValue.substring(0, fieldValue.length - 1);
378 }
379 fieldValue = fieldValue.replace(/,/g,'');
380 }
381 // Add double quotes to any string values.
382 if (fieldType == 'STRING') {
383 fieldValue = fieldValue.replace(/"/g, '""');
384 fieldValue = '"' + fieldValue + '"';
385 }
386 csvRow.push(fieldValue);
387 }
388 var rowString = csvRow.join(',');
389 csvRows.push(rowString);
390 chunkLen += Utilities.newBlob(rowString).getBytes().length + 1;
391 }
392 if (csvRows) {
393 totalRows += csvRows.length;
394 chunks.push(csvRows.join('\n'));
395 }
396 Logger.log('Downloaded ' + reportConfig.NAME + ' with ' + totalRows +
397 ' rows, in ' + chunks.length + ' chunks.');
398 return chunks;
399}
400
401/**
402 * Creates a new Google Drive folder. If folder name is already in
403 * use will pick the first folder with a matching name.
404 *
405 * @return {Folder} Google Drive folder to store reports.
406 */
407function getDriveFolder() {
408 var folders = DriveApp.getFoldersByName(CONFIG.DRIVE_FOLDER);
409 // Assume first folder is the correct one.
410 if (folders.hasNext()) {
411 Logger.log('Folder name found. Using existing folder.');
412 return folders.next();
413 }
414 return DriveApp.createFolder(CONFIG.DRIVE_FOLDER);
415}
416
417/**
418 * Creates a BigQuery insertJob to load csv data.
419 *
420 * @param {Object} reportConfig Report configuration including report name,
421 * conditions, and fields.
422 * @param {Blob} data Csv report data as an 'application/octet-stream' blob.
423 * @param {number=} skipLeadingRows Optional number of rows to skip.
424 *
425 * @return {string} jobId The job id for upload.
426 */
427function loadDataToBigquery(reportConfig, data, skipLeadingRows) {
428 // Create the data upload job.
429 var job = {
430 configuration: {
431 load: {
432 destinationTable: {
433 projectId: CONFIG.BIGQUERY_PROJECT_ID,
434 datasetId: CONFIG.BIGQUERY_DATASET_ID,
435 tableId: reportConfig.NAME
436 },
437 skipLeadingRows: skipLeadingRows ? skipLeadingRows : 0,
438 nullMarker: '--'
439 }
440 }
441 };
442
443 var insertJob = BigQuery.Jobs.insert(job, CONFIG.BIGQUERY_PROJECT_ID, data);
444 Logger.log('Load job started for %s. Check on the status of it here: ' +
445 'https://bigquery.cloud.google.com/jobs/%s', reportConfig.NAME,
446 CONFIG.BIGQUERY_PROJECT_ID);
447 return insertJob.jobReference.jobId;
448}
449
450/**
451 * Polls until all jobs are 'DONE'.
452 *
453 * @param {Array.<string>} jobIds The list of all job ids.
454 */
455function waitTillJobsComplete(jobIds) {
456 var complete = false;
457 var remainingJobs = jobIds;
458 while (!complete) {
459 if (AdsApp.getExecutionInfo().getRemainingTime() < 5){
460 Logger.log('Script is about to timeout, jobs ' + remainingJobs.join(',') +
461 ' are still incomplete.');
462 }
463 remainingJobs = getIncompleteJobs(remainingJobs);
464 if (remainingJobs.length == 0) {
465 complete = true;
466 }
467 if (!complete) {
468 Logger.log(remainingJobs.length + ' jobs still being processed.');
469 // Wait 5 seconds before checking status again.
470 Utilities.sleep(5000);
471 }
472 }
473 Logger.log('All jobs processed.');
474}
475
476/**
477 * Iterates through jobs and returns the ids for those jobs
478 * that are not 'DONE'.
479 *
480 * @param {Array.<string>} jobIds The list of job ids.
481 *
482 * @return {Array.<string>} remainingJobIds The list of remaining job ids.
483 */
484function getIncompleteJobs(jobIds) {
485 var remainingJobIds = [];
486 for (var i = 0; i < jobIds.length; i++) {
487 var jobId = jobIds[i];
488 var getJob = BigQuery.Jobs.get(CONFIG.BIGQUERY_PROJECT_ID, jobId);
489 if (getJob.status.state != 'DONE') {
490 remainingJobIds.push(jobId);
491 }
492 }
493 return remainingJobIds;
494}
495
496
497/**
498 * Sends a notification email that jobs have completed loading.
499 *
500 * @param {Array.<string>} jobIds The list of all job ids.
501 */
502function sendEmail(jobIds) {
503 var html = [];
504 html.push(
505 '<html>',
506 '<body>',
507 '<table width=800 cellpadding=0 border=0 cellspacing=0>',
508 '<tr>',
509 '<td colspan=2 align=right>',
510 "<div style='font: italic normal 10pt Times New Roman, serif; " +
511 "margin: 0; color: #666; padding-right: 5px;'>" +
512 'Powered by Google Ads Scripts</div>',
513 '</td>',
514 '</tr>',
515 "<tr bgcolor='#3c78d8'>",
516 '<td width=500>',
517 "<div style='font: normal 18pt verdana, sans-serif; " +
518 "padding: 3px 10px; color: white'>Ads data load to " +
519 "Bigquery report</div>",
520 '</td>',
521 '<td align=right>',
522 "<div style='font: normal 18pt verdana, sans-serif; " +
523 "padding: 3px 10px; color: white'>",
524 AdsApp.currentAccount().getCustomerId(),
525 '</tr>',
526 '</table>',
527 '<table width=800 cellpadding=0 border=1 cellspacing=0>',
528 "<tr bgcolor='#ddd'>",
529 "<td style='font: 12pt verdana, sans-serif; " +
530 'padding: 5px 0px 5px 5px; background-color: #ddd; ' +
531 "text-align: left'>Report</td>",
532 "<td style='font: 12pt verdana, sans-serif; " +
533 'padding: 5px 0px 5px 5px; background-color: #ddd; ' +
534 "text-align: left'>JobId</td>",
535 "<td style='font: 12pt verdana, sans-serif; " +
536 'padding: 5px 0px 5x 5px; background-color: #ddd; ' +
537 "text-align: left'>Rows</td>",
538 "<td style='font: 12pt verdana, sans-serif; " +
539 'padding: 5px 0px 5x 5px; background-color: #ddd; ' +
540 "text-align: left'>State</td>",
541 "<td style='font: 12pt verdana, sans-serif; " +
542 'padding: 5px 0px 5x 5px; background-color: #ddd; ' +
543 "text-align: left'>ErrorResult</td>",
544 '</tr>',
545 createTableRows(jobIds),
546 '</table>',
547 '</body>',
548 '</html>');
549
550 MailApp.sendEmail(CONFIG.RECIPIENT_EMAILS.join(','),
551 'Ads data load to Bigquery Complete', '',
552 {htmlBody: html.join('\n')});
553}
554
555/**
556 * Creates table rows for email report.
557 *
558 * @param {Array.<string>} jobIds The list of all job ids.
559 */
560function createTableRows(jobIds) {
561 var html = [];
562 for (var i = 0; i < jobIds.length; i++) {
563 var jobId = jobIds[i];
564 var job = BigQuery.Jobs.get(CONFIG.BIGQUERY_PROJECT_ID, jobId);
565 var errorResult = '';
566 if (job.status.errorResult) {
567 errorResult = job.status.errorResult;
568 }
569
570 html.push('<tr>',
571 "<td style='padding: 0px 10px'>" +
572 job.configuration.load.destinationTable.tableId + '</td>',
573 "<td style='padding: 0px 10px'>" + jobId + '</td>',
574 "<td style='padding: 0px 10px'>" +
575 (job.statistics.load ? job.statistics.load.outputRows : 0) +'</td>',
576 "<td style='padding: 0px 10px'>" + job.status.state + '</td>',
577 "<td style='padding: 0px 10px'>" + errorResult + '</td>',
578 '</tr>');
579 }
580 return html.join('\n');
581}