This script automates the generation of a detailed report in Google Sheets by fetching data from Google Ads for a specified period. It organizes the data, calculates metrics such as Cost, Average CPC, CTR, and more, and presents it in a structured format. Additionally, it provides forecasting capabilities by predicting future metrics using rolling 14-day forecasts and simple moving averages for the next 21 days. The script also includes optimization functionality by determining the average optimal CPC (Cost Per Click) required to achieve a single conversion. With features for both retrospective analysis and forward-looking projections, this script streamlines the reporting process and aids in campaign optimization for Google Ads users.
This script is designed to generate a report in a Google Sheets document using data retrieved from Google Ads. Here’s what the script does:
Setting Up: The script begins with defining the URL of the Google Sheets document where the report will be generated.
Main Function (main()):It opens the specified Google Sheets document and selects the sheet named “Sheet1”.Sets the last check date and time in cell C1.Retrieves start and end dates from cells D3 and G3.Retrieves data from Google Ads for the specified period using a query.Clears existing data below row 4 and writes a header row.Sorts the data rows by date and writes sorted data to the sheet.Inserts an empty row after the last populated date, highlighted in black.Highlights the next 21 rows after the empty row in yellow.Calculates the Forecast CPA using a rolling 14-day forecast and simple moving average for the next 21 days.Calls the function determineAverageOptimalCPC() to determine and output the optimal Avg. CPC to cell J3.
Formatting Date Function (formatDate(date)): Formats a date to ‘yyyyMMdd’ format.
Get Next Date Function (getNextDate(currentDate, daysToAdd)): Gets the next date excluding today.
Determine Average Optimal CPC Function (determineAverageOptimalCPC(sheet)): Analyzes the data to determine the average Avg. CPC to achieve a single conversion. It calculates the optimal Avg. CPC for each row to achieve one conversion and outputs the average Avg. CPC to cell J3.
Overall, this script automates the process of generating a comprehensive report in Google Sheets based on data fetched from Google Ads, including forecasts and analysis for future dates.
//Make A Copy of The SpreadSheet
// Google Sheets URL
const SPREADSHEET_URL = ‘https://docs.google.com/spreadsheets/d/1VOFltyZKNEPiQ97roDkqLQtFMfMCIrkVVejoWu4q96M/edit#gid=3’;
/**
- The main function to generate the report.
*/
function main() {
const spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
const sheet = spreadsheet.getSheetByName(‘Sheet1’); // Change ‘Sheet1’ to the actual sheet name // Set last check date and time in cell C1
const lastCheckDateTime = new Date();
sheet.getRange(‘C1’).setValue(lastCheckDateTime); // Get start and end dates from cells D3 and G3
const startDate = sheet.getRange(‘D3’).getValue();
const endDate = sheet.getRange(‘G3’).getValue(); // Retrieve data from Google Ads for the specified period
const report = AdsApp.report(SELECT Cost, AverageCpc, Ctr, SearchImpressionShare, Impressions, Clicks, Conversions, CostPerConversion, Date FROM ACCOUNT_PERFORMANCE_REPORT DURING ${formatDate(startDate)}, ${formatDate(endDate)}); // Clear existing data below row 4
sheet.getRange(‘B5:K’).clearContent(); // Write header row
const headers = [‘Date’, ‘Cost’, ‘Avg. CPC’, ‘CTR’, ‘Search Impr. share’, ‘Impressions’, ‘Clicks’, ‘Conversions’, ‘Cost Per Conversion’, ‘Forecast CPA’];
sheet.getRange(‘B4:K4’).setValues(); // Get the report data
const rows = report.rows(); // Create an array to hold the data rows
const dataRows = []; // Iterate over the rows and store them in the array
while (rows.hasNext()) {
const rowData = rows.next();
const values = [
rowData[‘Date’],
rowData[‘Cost’],
rowData[‘AverageCpc’],
rowData[‘Ctr’],
rowData[‘SearchImpressionShare’],
rowData[‘Impressions’],
rowData[‘Clicks’],
rowData[‘Conversions’],
rowData[‘CostPerConversion’]
];
dataRows.push(values);
} // Sort the data rows by date
dataRows.sort((a, b) => {
return new Date(a[0]) – new Date(b[0]);
}); // Write sorted data to the sheet along with Forecast CPA initialized as empty strings
for (let i = 0; i < dataRows.length; i++) {
dataRows.push(”); // Initialize Forecast CPA as an empty string
sheet.getRange(B${i + 5}:K${i + 5}).setValues(]);
} // Find the index of the last populated date
const lastIndex = dataRows.length + 4; // Insert an empty row after the last populated date, highlighted in black
sheet.insertRowAfter(lastIndex);
sheet.getRange(lastIndex + 1, 2, 1, 10).setBackground(“black”); // Highlight the next 21 rows after the empty row in yellow
sheet.getRange(lastIndex + 2, 2, 21, 10).setBackground(“yellow”); // Calculate Forecast CPA
const dataRange = sheet.getRange(‘B5:K’);
const dataValues = dataRange.getValues();
const numRows = dataValues.length;
const forecastCpaColumn = 9; // Index of ‘Forecast CPA’ column // Calculate 14-day rolling forecast
for (let i = 0; i < numRows – 14; i++) {
let totalCost = 0;
let totalClicks = 0;
for (let j = i; j < i + 14; j++) {
totalCost += dataValues[1]; // Cost
totalClicks += dataValues[6]; // Clicks
}
const avgCpc = totalCost / totalClicks;
const forecastCpa = avgCpc * dataValues[3]; // CTR
dataValues = forecastCpa;
} // Calculate forecasts for the next 21 days using simple moving average
const startForecastIndex = lastIndex + 2; // Starting row for forecasts
const historicalDataRange = sheet.getRange(‘B5:K’ + lastIndex);
const historicalDataValues = historicalDataRange.getValues(); for (let i = 0; i < 21; i++) {
const forecastRowIndex = startForecastIndex + i;
const forecastDate = getNextDate(new Date(dataValues[0]), i + 1);
let forecastedValues = []; // Calculate simple moving average for each column using updated historical data
for (let col = 1; col < 10; col++) { let total = 0; const endIndex = historicalDataValues.length + i – 21; const startIndex = endIndex – 14 >= 0 ? endIndex – 14 : 0;
for (let row = startIndex; row < endIndex; row++) {
total += historicalDataValues;
}
const movingAverage = total / 14;
forecastedValues.push(movingAverage);
}
forecastedValues.push(”); // Leave Forecast CPA empty // Populate forecasted values in the sheet
sheet.getRange(forecastRowIndex, 2, 1, 10).setValues([[
formatDate(forecastDate),
forecastedValues[0], // Cost
forecastedValues[1], // Avg. CPC
forecastedValues[2], // CTR
forecastedValues[3], // Search Impr. share
forecastedValues[4], // Impressions
forecastedValues[5], // Clicks
forecastedValues[6], // Conversions
forecastedValues[7], // Cost Per Conversion
” // Forecast CPA
]]);
} // Call the function to determine and output the optimal Avg. CPC to cell J3
determineAverageOptimalCPC(sheet);
}
/**
- Formats a date to ‘yyyyMMdd’ format.
- @param {Date} date The date to format.
- @return {string} The formatted date string.
*/
function formatDate(date) {
const year = date.getFullYear();
const month = (date.getMonth() + 1).toString().padStart(2, ‘0’);
const day = date.getDate().toString().padStart(2, ‘0’);
return${year}${month}${day};
}
/**
- Get the next date excluding today.
- @param {Date} currentDate The current date.
- @param {number} daysToAdd The number of days to add to the current date.
- @return {Date} The next date.
*/
function getNextDate(currentDate, daysToAdd) {
const nextDate = new Date(currentDate);
nextDate.setDate(nextDate.getDate() + daysToAdd); // Increment to the next date
return nextDate;
}
/**
- Analyzes the data to determine the average Avg. CPC to achieve a single conversion.
*/
function determineAverageOptimalCPC(sheet) {
// Get data range for Avg. CPC and Conversions columns
const cpcRange = sheet.getRange(‘D5:D’);
const conversionRange = sheet.getRange(‘I5:I’); // Get values from the ranges
const cpcValues = cpcRange.getValues().flat().filter(value => value !== ”); // Remove empty values
const conversionValues = conversionRange.getValues().flat().filter(value => value !== ”); // Remove empty values // Calculate the optimal Avg. CPC for each row to achieve one conversion
const optimalCPCs = [];
for (let i = 0; i < cpcValues.length; i++) {
if (conversionValues !== ‘0’) { // Consider only rows with non-zero conversions
const optimalCPC = cpcValues / conversionValues;
optimalCPCs.push(optimalCPC);
}
} // Calculate the average of the optimal Avg. CPCs
let totalOptimalCPC = 0;
for (let i = 0; i < optimalCPCs.length; i++) {
totalOptimalCPC += optimalCPCs;
}
const averageOptimalCPC = totalOptimalCPC / optimalCPCs.length; // Output the average Avg. CPC to cell J3
sheet.getRange(‘J3’).setValue(averageOptimalCPC);
}