วิธีอัปโหลดข้อมูลดิบจาก Google Ads ไปยัง Google BigQuery

สารบัญ

• ทำไมคุณถึงต้องการข้อมูลดิบจาก Google Ads
• สองวิธีในการอัปโหลดข้อมูลดิบจาก Google Ads ไปยัง BigQuery
• วิธีกำหนดค่าการอัปโหลดโดยใช้ Data Transfer
• วิธีตั้งค่าการอัปโหลดโดยใช้ Ads Script
• วิธีเชื่อมต่อการดาวน์โหลดข้อมูลจาก Google Ads กับ OWOX BI
• เคล็ดลับที่เป็นประโยชน์

ค้นหามูลค่าที่แท้จริงของแคมเปญ

นำเข้าข้อมูลค่าใช้จ่ายไปยัง Google Analytics โดยอัตโนมัติจากบริการโฆษณาทั้งหมดของคุณ เปรียบเทียบต้นทุนแคมเปญ CPC และ ROAS ในรายงานเดียว

เริ่มทดลองใช้

ทำไมคุณถึงต้องการข้อมูลดิบจาก Google Ads

สองวิธีในการอัปโหลดข้อมูลดิบจาก Google Ads ไปยัง BigQuery

สิ่งที่คุณต้องการสำหรับการตั้งค่า

วิธีให้สิทธิ์การเข้าถึงใน GBQ

วิธีกำหนดค่าการอัปโหลดโดยใช้ Data Transfer

ขั้นตอนที่ 1 สร้างโครงการใน Google Cloud Platform

ขั้นตอนที่ 2 เปิด API BigQuery

ขั้นตอนที่ 3 เปิดใช้งาน Data Transfer API

ขั้นตอนที่ 4 เตรียมชุดข้อมูลใน GBQ

ขั้นตอนที่ 5. ตั้งค่าการถ่ายโอนข้อมูลจาก Google Ads

วิธีตั้งค่าการอัปโหลดโดยใช้ Ads Script

    /** * @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