Как выгрузить сырые данные из Google Ads в Google BigQuery

Материалы для скачивания

Видео докладов с конференций: Analyze, GoAnalytics и Ecommerce

Содержание

• Зачем нужны сырые данные из Google Ads
• Два способа выгрузки сырых данных из Google Ads в BigQuery
• Как настроить выгрузку с помощью Data Transfer
• Как настроить выгрузку с помощью Ads Script
• Как подключить выгрузку из Google Ads к OWOX BI
• Полезные советы

бонус для читателей

Видео докладов с конференций: Analyze, GoAnalytics и Ecommerce

Скачать материал

Зачем нужны сырые данные из 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 [adwords-scripts@googlegroups.com]
 *
 * @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 >= "2019-01-01 00:00:00" AND _PARTITIONTIME < "2019-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