Google Ads에서 Google BigQuery로 원시 데이터를 업로드하는 방법

목차

• Google Ads의 원시 데이터가 필요한 이유
• Google Ads에서 BigQuery로 원시 데이터를 업로드하는 두 가지 방법
• 데이터 전송을 사용하여 업로드를 구성하는 방법
• 광고 스크립트를 사용하여 업로드를 설정하는 방법
• Google Ads에서 OWOX BI로 데이터 다운로드를 연결하는 방법
• 유용한 팁

캠페인의 진정한 가치 알아보기

모든 광고 서비스에서 Google Analytics로 비용 데이터를 자동으로 가져옵니다. 단일 보고서에서 캠페인 비용, CPC 및 ROAS를 비교합니다.

재판을 시작하다

Google Ads의 원시 데이터가 필요한 이유

Google Ads에서 BigQuery로 원시 데이터를 업로드하는 두 가지 방법

설정에 필요한 것

GBQ에서 접근 권한을 부여하는 방법

데이터 전송을 사용하여 업로드를 구성하는 방법

1단계. Google Cloud Platform에서 프로젝트 만들기

2단계. API BigQuery 사용 설정

3단계. 데이터 전송 API 활성화

4단계. GBQ에서 데이터 세트 준비

5단계. Google Ads에서 데이터 전송 설정

광고 스크립트를 사용하여 업로드를 설정하는 방법

    /** * @name Export Data to BigQuery * * @overview The Export Data to BigQuery script sets up a BigQuery * dataset and tables, downloads a report from AdWords and then * loads the report to BigQuery. * * @author AdWords Scripts Team [[email protected]] * * @version 1.3 */ var CONFIG = { BIGQUERY_PROJECT_ID: 'BQ project name', BIGQUERY_DATASET_ID: AdWordsApp.currentAccount().getCustomerId().replace(/-/g, '_'), // Truncate existing data, otherwise will append. TRUNCATE_EXISTING_DATASET: false, TRUNCATE_EXISTING_TABLES: true, // Lists of reports and fields to retrieve from AdWords. REPORTS: [], RECIPIENT_EMAILS: [ 'Your email' ] }; var report = { NAME: 'CLICK_PERFORMANCE_REPORT', //https://developers.google.com/adwords/api/docs/appendix/reports/click-performance-report CONDITIONS: '', FIELDS: {'AccountDescriptiveName': 'STRING', 'AdFormat': 'STRING', 'AdGroupId': 'STRING', 'AdGroupName': 'STRING', 'AoiCountryCriteriaId': 'STRING', 'CampaignId': 'STRING', 'CampaignLocationTargetId': 'STRING', 'CampaignName': 'STRING', 'CampaignStatus': 'STRING', 'Clicks': 'INTEGER', 'ClickType': 'STRING', 'CreativeId': 'STRING', 'CriteriaId': 'STRING', 'CriteriaParameters': 'STRING', 'Date': 'DATE', 'Device': 'STRING', 'ExternalCustomerId': 'STRING', 'GclId': 'STRING', 'KeywordMatchType': 'STRING', 'LopCountryCriteriaId': 'STRING', 'Page': 'INTEGER' }, DATE_RANGE: new Date(new Date().setDate(new Date().getDate()-1)).toISOString().slice(0, 10).replace(/-/g, "")+','+new Date(new Date().setDate(new Date().getDate()-1)).toISOString().slice(0, 10).replace(/-/g, ""), DATE: new Date(new Date().setDate(new Date().getDate()-1)).toISOString().slice(0, 10).replace(/-/g, "") }; //Regular export CONFIG.REPORTS.push(JSON.parse(JSON.stringify(report))); //One-time historical export //for(var i=2;i<91;i++){ // report.DATE_RANGE = new Date(new Date().setDate(new Date().getDate()-i)).toISOString().slice(0, 10).replace(/-/g, "")+','+new Date(new Date().setDate(new Date().getDate()-i)).toISOString().slice(0, 10).replace(/-/g, ""); // report.DATE = new Date(new Date().setDate(new Date().getDate()-i)).toISOString().slice(0, 10).replace(/-/g, ""); // CONFIG.REPORTS.push(JSON.parse(JSON.stringify(report))); //} /** * Main method */ function main() { createDataset(); for (var i = 0; i < CONFIG.REPORTS.length; i++) { var reportConfig = CONFIG.REPORTS[i]; createTable(reportConfig); } var jobIds = processReports(); waitTillJobsComplete(jobIds); sendEmail(jobIds); } /** * Creates a new dataset. * * If a dataset with the same id already exists and the truncate flag * is set, will truncate the old dataset. If the truncate flag is not * set, then will not create a new dataset. */ function createDataset() { if (datasetExists()) { if (CONFIG.TRUNCATE_EXISTING_DATASET) { BigQuery.Datasets.remove(CONFIG.BIGQUERY_PROJECT_ID, CONFIG.BIGQUERY_DATASET_ID, {'deleteContents' : true}); Logger.log('Truncated dataset.'); } else { Logger.log('Dataset %s already exists. Will not recreate.', CONFIG.BIGQUERY_DATASET_ID); return; } } // Create new dataset. var dataSet = BigQuery.newDataset(); dataSet.friendlyName = CONFIG.BIGQUERY_DATASET_ID; dataSet.datasetReference = BigQuery.newDatasetReference(); dataSet.datasetReference.projectId = CONFIG.BIGQUERY_PROJECT_ID; dataSet.datasetReference.datasetId = CONFIG.BIGQUERY_DATASET_ID; dataSet = BigQuery.Datasets.insert(dataSet, CONFIG.BIGQUERY_PROJECT_ID); Logger.log('Created dataset with id %s.', dataSet.id); } /** * Checks if dataset already exists in project. * * @return {boolean} Returns true if dataset already exists. */ function datasetExists() { // Get a list of all datasets in project. var datasets = BigQuery.Datasets.list(CONFIG.BIGQUERY_PROJECT_ID); var datasetExists = false; // Iterate through each dataset and check for an id match. if (datasets.datasets != null) { for (var i = 0; i < datasets.datasets.length; i++) { var dataset = datasets.datasets[i]; if (dataset.datasetReference.datasetId == CONFIG.BIGQUERY_DATASET_ID) { datasetExists = true; break; } } } return datasetExists; } /** * Creates a new table. * * If a table with the same id already exists and the truncate flag * is set, will truncate the old table. If the truncate flag is not * set, then will not create a new table. * * @param {Object} reportConfig Report configuration including report name, * conditions, and fields. */ function createTable(reportConfig) { var tableName = reportConfig.NAME+reportConfig.DATE; if (tableExists(tableName)) { if (CONFIG.TRUNCATE_EXISTING_TABLES) { BigQuery.Tables.remove(CONFIG.BIGQUERY_PROJECT_ID, CONFIG.BIGQUERY_DATASET_ID, tableName); Logger.log('Truncated table %s.', tableName); } else { Logger.log('Table %s already exists. Will not recreate.', tableName); return; } } // Create new table. var table = BigQuery.newTable(); var schema = BigQuery.newTableSchema(); var bigQueryFields = []; // Add each field to table schema. var fieldNames = Object.keys(reportConfig.FIELDS); for (var i = 0; i < fieldNames.length; i++) { var fieldName = fieldNames[i]; var bigQueryFieldSchema = BigQuery.newTableFieldSchema(); bigQueryFieldSchema.description = fieldName; bigQueryFieldSchema.name = fieldName; bigQueryFieldSchema.type = reportConfig.FIELDS[fieldName]; bigQueryFields.push(bigQueryFieldSchema); } schema.fields = bigQueryFields; table.schema = schema; table.friendlyName = tableName; table.tableReference = BigQuery.newTableReference(); table.tableReference.datasetId = CONFIG.BIGQUERY_DATASET_ID; table.tableReference.projectId = CONFIG.BIGQUERY_PROJECT_ID; table.tableReference.tableId = tableName; table = BigQuery.Tables.insert(table, CONFIG.BIGQUERY_PROJECT_ID, CONFIG.BIGQUERY_DATASET_ID); Logger.log('Created table with id %s.', table.id); } /** * Checks if table already exists in dataset. * * @param {string} tableId The table id to check existence. * * @return {boolean} Returns true if table already exists. */ function tableExists(tableId) { // Get a list of all tables in the dataset. var tables = BigQuery.Tables.list(CONFIG.BIGQUERY_PROJECT_ID, CONFIG.BIGQUERY_DATASET_ID); var tableExists = false; // Iterate through each table and check for an id match. if (tables.tables != null) { for (var i = 0; i < tables.tables.length; i++) { var table = tables.tables[i]; if (table.tableReference.tableId == tableId) { tableExists = true; break; } } } return tableExists; } /** * Process all configured reports * * Iterates through each report to: retrieve AdWords data, * backup data to Drive (if configured), load data to BigQuery. * * @return {Array.<string>} jobIds The list of all job ids. */ function processReports() { var jobIds = []; // Iterate over each report type. for (var i = 0; i < CONFIG.REPORTS.length; i++) { var reportConfig = CONFIG.REPORTS[i]; Logger.log('Running report %s', reportConfig.NAME); // Get data as csv var csvData = retrieveAdwordsReport(reportConfig); //Logger.log(csvData); // Convert to Blob format. var blobData = Utilities.newBlob(csvData, 'application/octet-stream'); // Load data var jobId = loadDataToBigquery(reportConfig, blobData); jobIds.push(jobId); } return jobIds; } /** * Retrieves AdWords data as csv and formats any fields * to BigQuery expected format. * * @param {Object} reportConfig Report configuration including report name, * conditions, and fields. * * @return {string} csvData Report in csv format. */ function retrieveAdwordsReport(reportConfig) { var fieldNames = Object.keys(reportConfig.FIELDS); var query = 'SELECT ' + fieldNames.join(', ') + ' FROM ' + reportConfig.NAME + '' + reportConfig.CONDITIONS + ' DURING ' + reportConfig.DATE_RANGE; Logger.log(query); var report = AdWordsApp.report(query); var rows = report.rows(); var csvRows = []; // Header row csvRows.push(fieldNames.join(',')); // Iterate over each row. while (rows.hasNext()) { var row = rows.next(); var csvRow = []; for (var i = 0; i < fieldNames.length; i++) { var fieldName = fieldNames[i]; var fieldValue = row[fieldName].toString(); var fieldType = reportConfig.FIELDS[fieldName]; // Strip off % and perform any other formatting here. if (fieldType == 'FLOAT' || fieldType == 'INTEGER') { if (fieldValue.charAt(fieldValue.length - 1) == '%') { fieldValue = fieldValue.substring(0, fieldValue.length - 1); } fieldValue = fieldValue.replace(/,/g,''); if (fieldValue == '--' || fieldValue == 'Unspecified') { fieldValue = '' } } // Add double quotes to any string values. if (fieldType == 'STRING') { if (fieldValue == '--') { fieldValue = '' } fieldValue = fieldValue.replace(/"/g, '""'); fieldValue = '"' + fieldValue + '"' } csvRow.push(fieldValue); } csvRows.push(csvRow.join(',')); } Logger.log('Downloaded ' + reportConfig.NAME + ' with ' + csvRows.length + ' rows.'); return csvRows.join('\n'); } /** * Creates a BigQuery insertJob to load csv data. * * @param {Object} reportConfig Report configuration including report name, * conditions, and fields. * @param {Blob} data Csv report data as an 'application/octet-stream' blob. * * @return {string} jobId The job id for upload. */ function loadDataToBigquery(reportConfig, data) { // Create the data upload job. var job = { configuration: { load: { destinationTable: { projectId: CONFIG.BIGQUERY_PROJECT_ID, datasetId: CONFIG.BIGQUERY_DATASET_ID, tableId: reportConfig.NAME + reportConfig.DATE }, skipLeadingRows: 1 } } }; var insertJob = BigQuery.Jobs.insert(job, CONFIG.BIGQUERY_PROJECT_ID, data); Logger.log('Load job started for %s. Check on the status of it here: ' + 'https://bigquery.cloud.google.com/jobs/%s', reportConfig.NAME, CONFIG.BIGQUERY_PROJECT_ID); return insertJob.jobReference.jobId; } /** * Polls until all jobs are 'DONE'. * * @param {Array.<string>} jobIds The list of all job ids. */ function waitTillJobsComplete(jobIds) { var complete = false; var remainingJobs = jobIds; while (!complete) { if (AdWordsApp.getExecutionInfo().getRemainingTime() < 5){ Logger.log('Script is about to timeout, jobs ' + remainingJobs.join(',') + ' are still incomplete.'); } remainingJobs = getIncompleteJobs(remainingJobs); if (remainingJobs.length == 0) { complete = true; } if (!complete) { Logger.log(remainingJobs.length + ' jobs still being processed.'); // Wait 5 seconds before checking status again. Utilities.sleep(5000); } } Logger.log('All jobs processed.'); } /** * Iterates through jobs and returns the ids for those jobs * that are not 'DONE'. * * @param {Array.<string>} jobIds The list of job ids. * * @return {Array.<string>} remainingJobIds The list of remaining job ids. */ function getIncompleteJobs(jobIds) { var remainingJobIds = []; for (var i = 0; i < jobIds.length; i++) { var jobId = jobIds[i]; var getJob = BigQuery.Jobs.get(CONFIG.BIGQUERY_PROJECT_ID, jobId); if (getJob.status.state != 'DONE') { remainingJobIds.push(jobId); } } return remainingJobIds; } /** * Sends a notification email that jobs have completed loading. * * @param {Array.<string>} jobIds The list of all job ids. */ function sendEmail(jobIds) { var html = []; html.push( '<html>', '<body>', '<table width=800 cellpadding=0 border=0 cellspacing=0>', '<tr>', '<td colspan=2 align=right>', "<div style='font: italic normal 10pt Times New Roman, serif; " + "margin: 0; color: #666; padding-right: 5px;'>" + 'Powered by AdWords Scripts</div>', '</td>', '</tr>', "<tr bgcolor='#3c78d8'>", '<td width=500>', "<div style='font: normal 18pt verdana, sans-serif; " + "padding: 3px 10px; color: white'>Adwords data load to " + "Bigquery report</div>", '</td>', '<td align=right>', "<div style='font: normal 18pt verdana, sans-serif; " + "padding: 3px 10px; color: white'>", AdWordsApp.currentAccount().getCustomerId(), '</tr>', '</table>', '<table width=800 cellpadding=0 border=1 cellspacing=0>', "<tr bgcolor='#ddd'>", "<td style='font: 12pt verdana, sans-serif; " + 'padding: 5px 0px 5px 5px; background-color: #ddd; ' + "text-align: left'>Report</td>", "<td style='font: 12pt verdana, sans-serif; " + 'padding: 5px 0px 5px 5px; background-color: #ddd; ' + "text-align: left'>JobId</td>", "<td style='font: 12pt verdana, sans-serif; " + 'padding: 5px 0px 5x 5px; background-color: #ddd; ' + "text-align: left'>Rows</td>", "<td style='font: 12pt verdana, sans-serif; " + 'padding: 5px 0px 5x 5px; background-color: #ddd; ' + "text-align: left'>State</td>", "<td style='font: 12pt verdana, sans-serif; " + 'padding: 5px 0px 5x 5px; background-color: #ddd; ' + "text-align: left'>ErrorResult</td>", '</tr>', createTableRows(jobIds), '</table>', '</body>', '</html>'); MailApp.sendEmail(CONFIG.RECIPIENT_EMAILS.join(','), 'Adwords data load to Bigquery Complete', '', {htmlBody: html.join('\n')}); } /** * Creates table rows for email report. * * @param {Array.<string>} jobIds The list of all job ids. */ function createTableRows(jobIds) { var html = []; for (var i = 0; i < jobIds.length; i++) { var jobId = jobIds[i]; var job = BigQuery.Jobs.get(CONFIG.BIGQUERY_PROJECT_ID, jobId); var errorResult = '' if (job.status.errorResult) { errorResult = job.status.errorResult; } html.push('<tr>', "<td style='padding: 0px 10px'>" + job.configuration.load.destinationTable.tableId + '</td>', "<td style='padding: 0px 10px'>" + jobId + '</td>', "<td style='padding: 0px 10px'>" + job.statistics.load?job.statistics.load.outputRows:0 + '</td>', "<td style='padding: 0px 10px'>" + job.status.state + '</td>', "<td style='padding: 0px 10px'>" + errorResult + '</td>', '</tr>'); } return html.join('\n'); }
/** * @name Export Data to BigQuery * * @overview The Export Data to BigQuery script sets up a BigQuery * dataset and tables, downloads a report from AdWords and then * loads the report to BigQuery. * * @author AdWords Scripts Team [[email protected]] * * @version 1.3 */ var CONFIG = { BIGQUERY_PROJECT_ID: 'BQ project name', BIGQUERY_DATASET_ID: AdWordsApp.currentAccount().getCustomerId().replace(/-/g, '_'), // Truncate existing data, otherwise will append. TRUNCATE_EXISTING_DATASET: false, TRUNCATE_EXISTING_TABLES: true, // Lists of reports and fields to retrieve from AdWords. REPORTS: [], RECIPIENT_EMAILS: [ 'Your email' ] }; var report = { NAME: 'CLICK_PERFORMANCE_REPORT', //https://developers.google.com/adwords/api/docs/appendix/reports/click-performance-report CONDITIONS: '', FIELDS: {'AccountDescriptiveName': 'STRING', 'AdFormat': 'STRING', 'AdGroupId': 'STRING', 'AdGroupName': 'STRING', 'AoiCountryCriteriaId': 'STRING', 'CampaignId': 'STRING', 'CampaignLocationTargetId': 'STRING', 'CampaignName': 'STRING', 'CampaignStatus': 'STRING', 'Clicks': 'INTEGER', 'ClickType': 'STRING', 'CreativeId': 'STRING', 'CriteriaId': 'STRING', 'CriteriaParameters': 'STRING', 'Date': 'DATE', 'Device': 'STRING', 'ExternalCustomerId': 'STRING', 'GclId': 'STRING', 'KeywordMatchType': 'STRING', 'LopCountryCriteriaId': 'STRING', 'Page': 'INTEGER' }, DATE_RANGE: new Date(new Date().setDate(new Date().getDate()-1)).toISOString().slice(0, 10).replace(/-/g, "")+','+new Date(new Date().setDate(new Date().getDate()-1)).toISOString().slice(0, 10).replace(/-/g, ""), DATE: new Date(new Date().setDate(new Date().getDate()-1)).toISOString().slice(0, 10).replace(/-/g, "") }; //Regular export CONFIG.REPORTS.push(JSON.parse(JSON.stringify(report))); //One-time historical export //for(var i=2;i<91;i++){ // report.DATE_RANGE = new Date(new Date().setDate(new Date().getDate()-i)).toISOString().slice(0, 10).replace(/-/g, "")+','+new Date(new Date().setDate(new Date().getDate()-i)).toISOString().slice(0, 10).replace(/-/g, ""); // report.DATE = new Date(new Date().setDate(new Date().getDate()-i)).toISOString().slice(0, 10).replace(/-/g, ""); // CONFIG.REPORTS.push(JSON.parse(JSON.stringify(report))); //} /** * Main method */ function main() { createDataset(); for (var i = 0; i < CONFIG.REPORTS.length; i++) { var reportConfig = CONFIG.REPORTS[i]; createTable(reportConfig); } var jobIds = processReports(); waitTillJobsComplete(jobIds); sendEmail(jobIds); } /** * Creates a new dataset. * * If a dataset with the same id already exists and the truncate flag * is set, will truncate the old dataset. If the truncate flag is not * set, then will not create a new dataset. */ function createDataset() { if (datasetExists()) { if (CONFIG.TRUNCATE_EXISTING_DATASET) { BigQuery.Datasets.remove(CONFIG.BIGQUERY_PROJECT_ID, CONFIG.BIGQUERY_DATASET_ID, {'deleteContents' : true}); Logger.log('Truncated dataset.'); } else { Logger.log('Dataset %s already exists. Will not recreate.', CONFIG.BIGQUERY_DATASET_ID); return; } } // Create new dataset. var dataSet = BigQuery.newDataset(); dataSet.friendlyName = CONFIG.BIGQUERY_DATASET_ID; dataSet.datasetReference = BigQuery.newDatasetReference(); dataSet.datasetReference.projectId = CONFIG.BIGQUERY_PROJECT_ID; dataSet.datasetReference.datasetId = CONFIG.BIGQUERY_DATASET_ID; dataSet = BigQuery.Datasets.insert(dataSet, CONFIG.BIGQUERY_PROJECT_ID); Logger.log('Created dataset with id %s.', dataSet.id); } /** * Checks if dataset already exists in project. * * @return {boolean} Returns true if dataset already exists. */ function datasetExists() { // Get a list of all datasets in project. var datasets = BigQuery.Datasets.list(CONFIG.BIGQUERY_PROJECT_ID); var datasetExists = false; // Iterate through each dataset and check for an id match. if (datasets.datasets != null) { for (var i = 0; i < datasets.datasets.length; i++) { var dataset = datasets.datasets[i]; if (dataset.datasetReference.datasetId == CONFIG.BIGQUERY_DATASET_ID) { datasetExists = true; break; } } } return datasetExists; } /** * Creates a new table. * * If a table with the same id already exists and the truncate flag * is set, will truncate the old table. If the truncate flag is not * set, then will not create a new table. * * @param {Object} reportConfig Report configuration including report name, * conditions, and fields. */ function createTable(reportConfig) { var tableName = reportConfig.NAME+reportConfig.DATE; if (tableExists(tableName)) { if (CONFIG.TRUNCATE_EXISTING_TABLES) { BigQuery.Tables.remove(CONFIG.BIGQUERY_PROJECT_ID, CONFIG.BIGQUERY_DATASET_ID, tableName); Logger.log('Truncated table %s.', tableName); } else { Logger.log('Table %s already exists. Will not recreate.', tableName); return; } } // Create new table. var table = BigQuery.newTable(); var schema = BigQuery.newTableSchema(); var bigQueryFields = []; // Add each field to table schema. var fieldNames = Object.keys(reportConfig.FIELDS); for (var i = 0; i < fieldNames.length; i++) { var fieldName = fieldNames[i]; var bigQueryFieldSchema = BigQuery.newTableFieldSchema(); bigQueryFieldSchema.description = fieldName; bigQueryFieldSchema.name = fieldName; bigQueryFieldSchema.type = reportConfig.FIELDS[fieldName]; bigQueryFields.push(bigQueryFieldSchema); } schema.fields = bigQueryFields; table.schema = schema; table.friendlyName = tableName; table.tableReference = BigQuery.newTableReference(); table.tableReference.datasetId = CONFIG.BIGQUERY_DATASET_ID; table.tableReference.projectId = CONFIG.BIGQUERY_PROJECT_ID; table.tableReference.tableId = tableName; table = BigQuery.Tables.insert(table, CONFIG.BIGQUERY_PROJECT_ID, CONFIG.BIGQUERY_DATASET_ID); Logger.log('Created table with id %s.', table.id); } /** * Checks if table already exists in dataset. * * @param {string} tableId The table id to check existence. * * @return {boolean} Returns true if table already exists. */ function tableExists(tableId) { // Get a list of all tables in the dataset. var tables = BigQuery.Tables.list(CONFIG.BIGQUERY_PROJECT_ID, CONFIG.BIGQUERY_DATASET_ID); var tableExists = false; // Iterate through each table and check for an id match. if (tables.tables != null) { for (var i = 0; i < tables.tables.length; i++) { var table = tables.tables[i]; if (table.tableReference.tableId == tableId) { tableExists = true; break; } } } return tableExists; } /** * Process all configured reports * * Iterates through each report to: retrieve AdWords data, * backup data to Drive (if configured), load data to BigQuery. * * @return {Array.<string>} jobIds The list of all job ids. */ function processReports() { var jobIds = []; // Iterate over each report type. for (var i = 0; i < CONFIG.REPORTS.length; i++) { var reportConfig = CONFIG.REPORTS[i]; Logger.log('Running report %s', reportConfig.NAME); // Get data as csv var csvData = retrieveAdwordsReport(reportConfig); //Logger.log(csvData); // Convert to Blob format. var blobData = Utilities.newBlob(csvData, 'application/octet-stream'); // Load data var jobId = loadDataToBigquery(reportConfig, blobData); jobIds.push(jobId); } return jobIds; } /** * Retrieves AdWords data as csv and formats any fields * to BigQuery expected format. * * @param {Object} reportConfig Report configuration including report name, * conditions, and fields. * * @return {string} csvData Report in csv format. */ function retrieveAdwordsReport(reportConfig) { var fieldNames = Object.keys(reportConfig.FIELDS); var query = 'SELECT ' + fieldNames.join(', ') + ' FROM ' + reportConfig.NAME + '' + reportConfig.CONDITIONS + ' DURING ' + reportConfig.DATE_RANGE; Logger.log(query); var report = AdWordsApp.report(query); var rows = report.rows(); var csvRows = []; // Header row csvRows.push(fieldNames.join(',')); // Iterate over each row. while (rows.hasNext()) { var row = rows.next(); var csvRow = []; for (var i = 0; i < fieldNames.length; i++) { var fieldName = fieldNames[i]; var fieldValue = row[fieldName].toString(); var fieldType = reportConfig.FIELDS[fieldName]; // Strip off % and perform any other formatting here. if (fieldType == 'FLOAT' || fieldType == 'INTEGER') { if (fieldValue.charAt(fieldValue.length - 1) == '%') { fieldValue = fieldValue.substring(0, fieldValue.length - 1); } fieldValue = fieldValue.replace(/,/g,''); if (fieldValue == '--' || fieldValue == 'Unspecified') { fieldValue = '' } } // Add double quotes to any string values. if (fieldType == 'STRING') { if (fieldValue == '--') { fieldValue = '' } fieldValue = fieldValue.replace(/"/g, '""'); fieldValue = '"' + fieldValue + '"' } csvRow.push(fieldValue); } csvRows.push(csvRow.join(',')); } Logger.log('Downloaded ' + reportConfig.NAME + ' with ' + csvRows.length + ' rows.'); return csvRows.join('\n'); } /** * Creates a BigQuery insertJob to load csv data. * * @param {Object} reportConfig Report configuration including report name, * conditions, and fields. * @param {Blob} data Csv report data as an 'application/octet-stream' blob. * * @return {string} jobId The job id for upload. */ function loadDataToBigquery(reportConfig, data) { // Create the data upload job. var job = { configuration: { load: { destinationTable: { projectId: CONFIG.BIGQUERY_PROJECT_ID, datasetId: CONFIG.BIGQUERY_DATASET_ID, tableId: reportConfig.NAME + reportConfig.DATE }, skipLeadingRows: 1 } } }; var insertJob = BigQuery.Jobs.insert(job, CONFIG.BIGQUERY_PROJECT_ID, data); Logger.log('Load job started for %s. Check on the status of it here: ' + 'https://bigquery.cloud.google.com/jobs/%s', reportConfig.NAME, CONFIG.BIGQUERY_PROJECT_ID); return insertJob.jobReference.jobId; } /** * Polls until all jobs are 'DONE'. * * @param {Array.<string>} jobIds The list of all job ids. */ function waitTillJobsComplete(jobIds) { var complete = false; var remainingJobs = jobIds; while (!complete) { if (AdWordsApp.getExecutionInfo().getRemainingTime() < 5){ Logger.log('Script is about to timeout, jobs ' + remainingJobs.join(',') + ' are still incomplete.'); } remainingJobs = getIncompleteJobs(remainingJobs); if (remainingJobs.length == 0) { complete = true; } if (!complete) { Logger.log(remainingJobs.length + ' jobs still being processed.'); // Wait 5 seconds before checking status again. Utilities.sleep(5000); } } Logger.log('All jobs processed.'); } /** * Iterates through jobs and returns the ids for those jobs * that are not 'DONE'. * * @param {Array.<string>} jobIds The list of job ids. * * @return {Array.<string>} remainingJobIds The list of remaining job ids. */ function getIncompleteJobs(jobIds) { var remainingJobIds = []; for (var i = 0; i < jobIds.length; i++) { var jobId = jobIds[i]; var getJob = BigQuery.Jobs.get(CONFIG.BIGQUERY_PROJECT_ID, jobId); if (getJob.status.state != 'DONE') { remainingJobIds.push(jobId); } } return remainingJobIds; } /** * Sends a notification email that jobs have completed loading. * * @param {Array.<string>} jobIds The list of all job ids. */ function sendEmail(jobIds) { var html = []; html.push( '<html>', '<body>', '<table width=800 cellpadding=0 border=0 cellspacing=0>', '<tr>', '<td colspan=2 align=right>', "<div style='font: italic normal 10pt Times New Roman, serif; " + "margin: 0; color: #666; padding-right: 5px;'>" + 'Powered by AdWords Scripts</div>', '</td>', '</tr>', "<tr bgcolor='#3c78d8'>", '<td width=500>', "<div style='font: normal 18pt verdana, sans-serif; " + "padding: 3px 10px; color: white'>Adwords data load to " + "Bigquery report</div>", '</td>', '<td align=right>', "<div style='font: normal 18pt verdana, sans-serif; " + "padding: 3px 10px; color: white'>", AdWordsApp.currentAccount().getCustomerId(), '</tr>', '</table>', '<table width=800 cellpadding=0 border=1 cellspacing=0>', "<tr bgcolor='#ddd'>", "<td style='font: 12pt verdana, sans-serif; " + 'padding: 5px 0px 5px 5px; background-color: #ddd; ' + "text-align: left'>Report</td>", "<td style='font: 12pt verdana, sans-serif; " + 'padding: 5px 0px 5px 5px; background-color: #ddd; ' + "text-align: left'>JobId</td>", "<td style='font: 12pt verdana, sans-serif; " + 'padding: 5px 0px 5x 5px; background-color: #ddd; ' + "text-align: left'>Rows</td>", "<td style='font: 12pt verdana, sans-serif; " + 'padding: 5px 0px 5x 5px; background-color: #ddd; ' + "text-align: left'>State</td>", "<td style='font: 12pt verdana, sans-serif; " + 'padding: 5px 0px 5x 5px; background-color: #ddd; ' + "text-align: left'>ErrorResult</td>", '</tr>', createTableRows(jobIds), '</table>', '</body>', '</html>'); MailApp.sendEmail(CONFIG.RECIPIENT_EMAILS.join(','), 'Adwords data load to Bigquery Complete', '', {htmlBody: html.join('\n')}); } /** * Creates table rows for email report. * * @param {Array.<string>} jobIds The list of all job ids. */ function createTableRows(jobIds) { var html = []; for (var i = 0; i < jobIds.length; i++) { var jobId = jobIds[i]; var job = BigQuery.Jobs.get(CONFIG.BIGQUERY_PROJECT_ID, jobId); var errorResult = '' if (job.status.errorResult) { errorResult = job.status.errorResult; } html.push('<tr>', "<td style='padding: 0px 10px'>" + job.configuration.load.destinationTable.tableId + '</td>', "<td style='padding: 0px 10px'>" + jobId + '</td>', "<td style='padding: 0px 10px'>" + job.statistics.load?job.statistics.load.outputRows:0 + '</td>', "<td style='padding: 0px 10px'>" + job.status.state + '</td>', "<td style='padding: 0px 10px'>" + errorResult + '</td>', '</tr>'); } return html.join('\n'); }

스크립트를 실행하기 전에 반드시 우측 하단의 미리보기 버튼을 클릭하여 결과를 확인하시기 바랍니다. 오류가 있는 경우 다음 스크린샷과 같이 시스템에서 사용자에게 경고하고 오류가 발생한 줄을 표시합니다.

Google Ads에서 OWOX BI로 데이터 다운로드를 연결하는 방법

유용한 팁

    SELECT ExternalCustomerId FROM `project_name.dataset_name.Customer_*` WHERE _PARTITIONTIME >= "2020-01-01 00:00:00" AND _PARTITIONTIME < "2020-07-10 00:00:00" group by 1
SELECT ExternalCustomerId FROM `project_name.dataset_name.Customer_*` WHERE _PARTITIONTIME >= "2020-01-01 00:00:00" AND _PARTITIONTIME < "2020-07-10 00:00:00" group by 1

    SELECT {source language="sql"} c.ExternalCustomerId, c.CampaignName, c.CampaignStatus, SUM(cs.Impressions) AS Impressions, SUM(cs.Interactions) AS Interactions, {/source} (SUM(cs.Cost) / 1000000) AS Cost FROM `[DATASET].Campaign_[CUSTOMER_ID]` c LEFT JOIN {source language="sql"} {source language="sql"} `[DATASET].CampaignBasicStats_[CUSTOMER_ID]` cs ON (c.CampaignId = cs.CampaignId AND cs._DATA_DATE BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY) AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)) WHERE c._DATA_DATE = c._LATEST_DATE GROUP BY 1, 2, 3 ORDER BY Impressions DESC
SELECT {source language="sql"} c.ExternalCustomerId, c.CampaignName, c.CampaignStatus, SUM(cs.Impressions) AS Impressions, SUM(cs.Interactions) AS Interactions, {/source} (SUM(cs.Cost) / 1000000) AS Cost FROM `[DATASET].Campaign_[CUSTOMER_ID]` c LEFT JOIN {source language="sql"} {source language="sql"} `[DATASET].CampaignBasicStats_[CUSTOMER_ID]` cs ON (c.CampaignId = cs.CampaignId AND cs._DATA_DATE BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY) AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)) WHERE c._DATA_DATE = c._LATEST_DATE GROUP BY 1, 2, 3 ORDER BY Impressions DESC