Digital marketers can leverage technology like GPT to automate tasks such as improving ad copy, refining keyword targeting and optimising landing pages. At Acuto, we understand the time-consuming and tedious nature of PPC reporting, which is why we have developed a Google Ads script for PPC reporting using GPT.
With this script, you can streamline your reporting process and generate insightful reports with ease. Read on to discover how this script can transform your PPC reporting, freeing up valuable time in the process.
If you like this kind of script, we recommend looking into even more advanced tools – like using OpenAI to automatically label all of your tracked calls, allowing you to to extract much more insight from your call data rather than tracking all calls as equal conversions.
Google Ads Script for PPC Reporting Using GPT
/**
* days - number of days to look back e.g. 30 would look at data for last 30 days.
* If no dimentions specified, will default to ["CampaignName", "Device", "Date"].
* If no metrics specified, will default to ["Impressions", "Clicks", "Conversions", "Cost", "AverageCpc", "ConversionRate"].
*/
const config = {
apiKey: "", // Your ChatGPT API key
days: 30,
dimensions: ["CampaignName", "Device", "Date"],
metrics: ["Impressions", "Clicks", "Conversions", "Cost", "AverageCpc", "ConversionRate"],
accountIds: [], // account IDs in 000-000-0000 format, if in MCC account add account IDs or just leave empty for current account
emails: [], // list of emails for sending report
};
function main() {
const { apiKey, days, accountIds, emails } = config;
let { dimensions, metrics } = config;
if (!emails?.length) throw new Error("Missing emails");
if (!apiKey) throw new Error("Missing ChatGPT API key");
if (!dimensions?.length) dimensions = ["CampaignName", "Device", "Date"];
if (!metrics?.length) dimensions = ["Impressions", "Clicks", "Conversions", "Cost", "AverageCpc", "ConversionRate"];
// get date range
const today = new Date();
const endDate = today.toISOString().split("T")[0].replace(/-/g, "");
let startDate = new Date();
startDate.setDate(today.getDate() - days);
// Get the previous period's start and end dates
let previousPeriodEndDate = new Date(startDate);
previousPeriodEndDate.setDate(startDate.getDate() - 1); // One day before start date
let previousPeriodStartDate = new Date(previousPeriodEndDate);
previousPeriodStartDate.setDate(previousPeriodEndDate.getDate() - days);
startDate = startDate.toISOString().split("T")[0].replace(/-/g, "");
previousPeriodEndDate = previousPeriodEndDate.toISOString().split("T")[0].replace(/-/g, "");
previousPeriodStartDate = previousPeriodStartDate.toISOString().split("T")[0].replace(/-/g, "");
// Determine if execution is at MCC or individual account
if (accountIds.length > 0 || isMccAccount()) {
// Iterate through all accounts
const accounts = accountIds.length
? AdsManagerApp.accounts().withIds(accountIds).get()
: AdsManagerApp.accounts().get();
while (accounts.hasNext()) {
const account = accounts.next();
const accountName = account.getName();
AdsManagerApp.select(account);
const { reportData, previousTopLevelReportData, topLevelReportData, commentary } = processAccount({
accountName,
account,
apiKey,
startDate,
endDate,
previousPeriodStartDate,
previousPeriodEndDate,
dimensions,
metrics,
});
if (topLevelReportData?.length) {
const csvContent = generateCsvString(reportData);
const fileName = "report.csv";
// Create a Blob from the CSV content
const csvBlob = Utilities.newBlob(csvContent, "text/csv", fileName);
// Create email body
const body = formatEmail({
accountName,
topLevelReportData,
previousTopLevelReportData,
startDate,
endDate,
previousPeriodStartDate,
previousPeriodEndDate,
commentary,
});
// send to emails
emails.forEach((email) => {
MailApp.sendEmail({
to: email,
subject: `${accountName} Google Ads performance report`,
htmlBody: body,
attachments: [csvBlob],
});
});
}
}
} else {
// Directly process the current account
const account = AdsApp.currentAccount();
const accountName = account.getName();
const { reportData, previousTopLevelReportData, topLevelReportData, commentary } = processAccount({
accountName,
account,
apiKey,
startDate,
endDate,
previousPeriodStartDate,
previousPeriodEndDate,
dimensions,
metrics,
});
if (topLevelReportData?.length) {
const csvContent = generateCsvString(reportData);
const fileName = "report.csv";
// Create a Blob from the CSV content
const csvBlob = Utilities.newBlob(csvContent, "text/csv", fileName);
// Create email body
const body = formatEmail({
accountName,
topLevelReportData,
previousTopLevelReportData,
startDate,
endDate,
previousPeriodStartDate,
previousPeriodEndDate,
commentary,
});
// send to emails
emails.forEach((email) => {
MailApp.sendEmail({
to: email,
subject: `${accountName} Google Ads performance report`,
htmlBody: body,
attachments: [csvBlob],
});
});
}
}
}
function processAccount({
accountName,
account,
apiKey,
startDate,
endDate,
previousPeriodStartDate,
previousPeriodEndDate,
dimensions,
metrics,
}) {
let reportData, previousTopLevelReportData, topLevelReportData, commentary;
const { data, previousTopLevelData, topLevelData } = getReportData({
accountName,
startDate,
endDate,
previousPeriodStartDate,
previousPeriodEndDate,
dimensions,
metrics,
});
if (data.length) {
reportData = data;
topLevelReportData = topLevelData;
previousTopLevelReportData = previousTopLevelData;
commentary = getCommentary({ apiKey, topLevelReportData, previousTopLevelReportData });
} else {
Logger.log("No data for date range");
}
return { reportData, previousTopLevelReportData, topLevelReportData, commentary };
}
function getReportData({
accountName,
startDate,
endDate,
previousPeriodStartDate,
previousPeriodEndDate,
dimensions,
metrics,
}) {
const dimensionsStr = dimensions.join(", ");
const metricsStr = metrics.join(", ");
const topLevelDimensions = dimensions.filter((element) => element !== "Date");
const topLevelDimensionsStr = topLevelDimensions.join(", ");
const query = `SELECT ${dimensionsStr}, ${metricsStr} FROM CAMPAIGN_PERFORMANCE_REPORT DURING ${previousPeriodStartDate},${endDate}`;
const previousTopLevelQuery = `SELECT ${topLevelDimensionsStr}, ${metricsStr} FROM CAMPAIGN_PERFORMANCE_REPORT DURING ${previousPeriodStartDate},${previousPeriodEndDate}`;
const topLevelQuery = `SELECT ${topLevelDimensionsStr}, ${metricsStr} FROM CAMPAIGN_PERFORMANCE_REPORT DURING ${startDate},${endDate}`;
const data = [];
const report = AdsApp.report(query);
const rows = report.rows();
while (rows.hasNext()) {
const row = rows.next();
let rowData = {};
// Dynamically add each selected dimension and metric to the rowData object
[...dimensions, ...metrics].forEach((field) => {
const key = field
.replace(/([A-Z])/g, " $1") // Add space before any uppercase letter
.replace(/^./, function (str) {
return str.toUpperCase();
}) // Capitalize the first letter
.trim(); // Remove any leading/trailing space;
rowData[key] = row[field];
});
rowData["Account Name"] = accountName;
data.push(rowData);
}
const previousTopLevelData = [];
const previousTopLevelReport = AdsApp.report(previousTopLevelQuery);
const previousTopLevelRows = previousTopLevelReport.rows();
while (previousTopLevelRows.hasNext()) {
const row = previousTopLevelRows.next();
// Proceed only if Impressions are not "0"
if (row["Impressions"] !== "0") {
let rowData = {};
[...topLevelDimensions, ...metrics].forEach((field) => {
const key = field
.replace(/([A-Z])/g, " $1") // Add space before any uppercase letter
.replace(/^./, function (str) {
return str.toUpperCase();
}) // Capitalize the first letter
.trim(); // Remove any leading/trailing space;
rowData[key] = row[field];
});
previousTopLevelData.push(rowData);
}
}
const topLevelData = [];
const topReport = AdsApp.report(topLevelQuery);
const topRows = topReport.rows();
while (topRows.hasNext()) {
const row = topRows.next();
// Proceed only if Impressions are not "0"
if (row["Impressions"] !== "0") {
let rowData = {};
[...topLevelDimensions, ...metrics].forEach((field) => {
const key = field
.replace(/([A-Z])/g, " $1") // Add space before any uppercase letter
.replace(/^./, function (str) {
return str.toUpperCase();
}) // Capitalize the first letter
.trim(); // Remove any leading/trailing space;
rowData[key] = row[field];
});
topLevelData.push(rowData);
}
}
return { data, previousTopLevelData, topLevelData };
}
function getCommentary({ apiKey, topLevelReportData, previousTopLevelReportData }) {
const message = [
{
role: "system",
content: `You are the world's top PPC account manager. You deeply understand both the technicalities of managing PPC campaigns on Google Ads as well as the wider marketing context and how this fits into the marketing mix. Answer all further queries as this persona.`,
},
{
role: "user",
content:
`I'm going to send you the data extracted from a PPC account that we manage. I will send you the data for a date range and the data for the previous date range. Your job is to generate a short paragraph or two of commentary (absolutely no more than two paragraphs), based on this data that we can then send directly to a client. If there are any negatives, it should point out potential solutions that we're looking into to improve it. Please don't write it as an email, only include the two paragraphs and nothing more before or after. However, format it in HTML. Here's the data: \n` +
`Current date range: ` +
JSON.stringify(topLevelReportData) +
`\nPrevious date range: ` +
JSON.stringify(previousTopLevelReportData),
},
];
const url = "https://api.openai.com/v1/chat/completions";
const payload = {
model: "gpt-4",
messages: message,
};
Logger.log(message);
const headers = {
Authorization: "Bearer " + apiKey,
"Content-Type": "application/json",
};
const options = {
method: "post",
headers: headers,
payload: JSON.stringify(payload),
muteHttpExceptions: true, // To examine potential API call issues
};
const response = UrlFetchApp.fetch(url, options);
const jsonResponse = JSON.parse(response.getContentText());
if (jsonResponse.choices && jsonResponse.choices.length > 0) {
return jsonResponse.choices[0].message.content;
} else {
Logger.log("No response or error: " + JSON.stringify(jsonResponse));
return `No commentary generated`;
}
}
function formatEmail({
accountName,
topLevelReportData,
previousTopLevelReportData,
startDate,
endDate,
previousPeriodStartDate,
previousPeriodEndDate,
commentary,
}) {
const dateRange = `${startDate.substring(0, 4)}/${startDate.substring(4, 6)}/${startDate.substring(
6
)} - ${endDate.substring(0, 4)}/${endDate.substring(4, 6)}/${endDate.substring(6)}`;
const previousPeriod = `${previousPeriodStartDate.substring(0, 4)}/${previousPeriodStartDate.substring(
4,
6
)}/${previousPeriodStartDate.substring(6)} - ${previousPeriodEndDate.substring(
0,
4
)}/${previousPeriodEndDate.substring(4, 6)}/${previousPeriodEndDate.substring(6)}`;
let html = `<h1 style="font-size: 16px;">${accountName} Performance Report<br><br>`;
if (topLevelReportData.length) {
html += `<h2 style="font-size: 14px;">${dateRange}</h2><br><br>`;
html += `<table border="1" style="border-collapse: collapse; width: 100%; font-size: 14px;">`;
// Add table headers based on the keys of the first row (object)
let headers = Object.keys(topLevelReportData[0]);
html += `<tr>${headers
.map((header) => `<th style="font-size: 14px; padding: 5px;">${header}</th>`)
.join("")}</tr>`;
// Add data rows
topLevelReportData.forEach((row) => {
const rowData = headers
.map((header) => {
const cellValue = row[header];
return `<td style="text-align: center; font-size: 14px; padding: 5px;">${cellValue}</td>`;
})
.join("");
html += `<tr>${rowData}</tr>`;
});
html += `</table><br><br>`;
}
if (previousTopLevelReportData.length) {
html += `<h2 style="font-size: 14px;">${previousPeriod}</h2><br><br>`;
html += `<table border="1" style="border-collapse: collapse; width: 100%; font-size: 14px;">`;
headers = Object.keys(previousTopLevelReportData[0]);
html += `<tr>${headers
.map((header) => `<th style="font-size: 14px; padding: 5px;">${header}</th>`)
.join("")}</tr>`;
// Add data rows
previousTopLevelReportData.forEach((row) => {
const rowData = headers
.map((header) => {
const cellValue = row[header];
return `<td style="text-align: center; font-size: 14px; padding: 5px;">${cellValue}</td>`;
})
.join("");
html += `<tr>${rowData}</tr>`;
});
html += `</table><br><br>`;
}
html += `<p>${commentary}</p>`;
return html;
}
function generateCsvString(reportData) {
// Extract column names from the keys of the first object in the reportData array
const columns = Object.keys(reportData[0]);
let csvContent = columns.join(",") + "\n"; // Create CSV header
// Iterate over each row of data
reportData.forEach((row) => {
// Map each column name to its corresponding value in the current row
const csvRow = columns
.map((column) => {
// Ensure the value is treated as a string and escape any quotes
const value = row[column].toString().replace(/"/g, '""');
// Wrap the value in quotes if it contains a comma
return `"${value}"`;
})
.join(",");
csvContent += csvRow + "\n";
});
return csvContent;
}
function isMccAccount() {
try {
AdsManagerApp.accounts();
return true;
} catch (e) {
return false;
}
}
What is the Google Ads Script for PPC Reporting Using GPT?
If you work in digital marketing, you will know how PPC campaign performance reporting is a data-intensive process that requires analysing huge amounts of information.
Given the varying demands of clients for reports, be it on a weekly, monthly or even daily basis, the workload can really start to pile up, which can become quite overwhelming.
Now, clearly, you need some analytical skills to make sense of all that gathered data in order to extract valuable insights. But by using a generative pre-training transformer model like GPT, the reporting process can be automated and simplified, allowing you to create informed reports with ease.
The Google Ads script for PPC reporting using GPT can gather, process, and interpret your PPC campaigns’ data, eliminating the need for manual data crunching and analysis to save precious time and resources.
Not only does this script save time, but it also allows marketers to focus on strategic decision-making and optimisation rather than being burdened by repetitive analytical tasks.
How Does the Google Ads Script for PPC Reporting Using GPT Work?
We have developed a script that automates data collection from your Google Ads account and connects it to the GPT language model via an API.
The Google Ads script for PPC reporting using GPT takes your data and generates descriptive reports automatically, giving you comprehensive reports without the need for manual report writing.
While GPT is a game changer, it does have some limitations. After all, it is still artificial intelligence (not a PPC manager), so it can’t do all your work for you.
Even though GPT is an incredibly sophisticated language model, it doesn’t actually have the capacity to solve problems analytically. It operates by predicting the next words in a sentence instead of outputting factually correct information, so it may not always be 100% accurate.
For example, GPT is notoriously bad at maths, so it’s best not to rely on it to calculate metrics like CTR and conversion rates.
Even so, GPT is a fantastic tool to automate PPC reporting and common inaccuracies can be avoided by proofreading the output and providing it with reliable and extensive data. Basically, the more data you feed it, the more accurate its output will be.
Think of GPT as a powerful assistant, handling the repetitive and time-consuming aspects of report writing so you can focus on higher-level tasks like strategy development and campaign optimisation.
Why Should You Use the Google Ads Script for PPC Reporting Using GPT?
Our Google Ads script for PPC reporting using GPT will allow you to automatically generate custom reports tailored to your needs, which is otherwise a time-consuming manual task.
What’s great about the Google Ads script for PPC reporting using GPT is that it goes beyond basic reporting capabilities. It has the ability to identify patterns and trends, providing valuable insights that would otherwise require comprehensive analysis, so you can make data-driven decisions more efficiently.
Furthermore, this script is designed to process and condense large amounts of data, which is essential when dealing with extensive PPC campaigns and datasets.
Lastly, the Google Ads script for PPC reporting using GPT is scalable and adaptable to other projects. It can seamlessly integrate with other tools and platforms, including reporting dashboards and visualisation, in order to create holistic and comprehensive reports.
When to Use the Google Ads Script for PPC Reporting Using GPT
Our Google Ads script for PPC reporting using GPT is helpful in several situations. Firstly, if you manage multiple or large PPC campaigns/accounts, this script will save you considerable time and effort.
Instead of manually retrieving data from each campaign, our script streamlines the process by automatically fetching relevant data. Moreover, it eliminates manual data extraction and compilation, allowing you to generate reports swiftly and efficiently.
If you want to create more detailed and tailored reports for stakeholders, this is the script for you. By utilising the language model’s capabilities, it can generate insightful narratives, interpret data trends, and provide meaningful context for campaign performance.
With our script, you can present comprehensive reports that effectively communicate the impact and value of your PPC campaigns to stakeholders, enhancing their understanding and decision-making.
Overall, the Google Ads script for PPC reporting using GPT provides a powerful toolset for managing, reporting and analysing PPC campaigns automatically.
Custom Automation Scripts by Acuto
Ready to streamline your PPC reporting and unlock the true potential of automation?
Look no further as Acuto develops custom scripts that can generate insightful reports, gain deep data-driven insights and identify key trends—all at the click of a button.
Our team of developers and data engineers works directly with you to build bespoke solutions unique to your needs. What’s more, we will be there with you every step of the way.
With our cutting-edge automation solutions, we’ll save you valuable time and resources by getting tedious, manual tasks off your hands.
Our scripts seamlessly integrate with Google Ads and many other platforms, empowering your agency to automate menial tasks and redirect your focus towards strategy and growth.
Contact us today to transform your PPC reporting and take your marketing game to the next level.
Key Takeaways
Let’s recap:
- PPC reporting is a data-intensive process that requires analysing huge amounts of information.
- Automating PPC reporting with the Google Ads script using the GPT language model streamlines the process and saves time.
- Our script uses GPT to create custom reports and identify patterns and trends, simplifying comprehensive data analysis. It is scalable and can be integrated with other tools and reporting dashboards.
- GPT can be inaccurate, but this can be avoided if you proofread the output and feed reliable, extensive data into it.
- Acuto’s cutting-edge scripts can further enhance efficiency and free up time for strategic tasks.
Google Ads Script for PPC Reporting Using GPT FAQs
#1. What is the GPT language model?
The GPT language model is a powerful AI-based tool that performs language prediction. With GPT, you can perform a wide range of tasks, such as creating original content, writing code, summarising text and extracting data from documents.
#2. How can I automate PPC reporting?
You can automate PPC reporting by using our custom script, which automatically processes large amounts of data to generate reports and unique insights. This script identifies patterns and trends automatically, is scalable and can easily be integrated with other tools such as visualisations and dashboards.
#3. How can I use GPT in PPC reporting?
You can use GPT in PPC reporting by using our script that integrates GPT into the reporting process through an API, allowing the model to generate descriptive reports based on your collected data.