Skip to content

Instantly share code, notes, and snippets.

@siliconvallaeys
Last active June 27, 2023 15:08
Show Gist options
  • Save siliconvallaeys/170da7ff147862aeab15ef88885de2fb to your computer and use it in GitHub Desktop.
Save siliconvallaeys/170da7ff147862aeab15ef88885de2fb to your computer and use it in GitHub Desktop.
Get a written performance summary of a Google Ads account's campaigns using GPT
/******************************************
* RSA Report
* @version: 1.0
* @authors: Frederick Vallaeys (Optmyzr), GPT-4 (openAI)
* -------------------------------
* Install this script in your Google Ads account (not an MCC account)
* to generate a textual summary of campaign performance.
* The text is generated using GPT and uses data from the Google Ads
* account in which this script is installed
* --------------------------------
* For more PPC tools, visit www.optmyzr.com.
******************************************/
var OPEN_AI_API_KEY = ''; // get your own API key at https://platform.openai.com/account/api-keys
var GPT_MODEL = 'gpt-3.5-turbo';
// Enter your own list of facts so that the summary follows your PPC management style and goals
var FACTS = ["facts: The target CPA is $20.",
"A cost higher is bad, and a cost lower is good.",
"If the Search lost IS (budget) > 10% and the CPA is below the target, the budget should be raised.",
"If the Search lost IS (budget) > 10% the bids should be adjusted"];
// Play with the prompt to get different styles of summarizations
var PROMPT = "prompt: write a summary of the campaigns' performance comparing the 2 periods. Do not include Search Lost IS in the summary.";
function main() {
var date = new Date();
var firstDayPrevMonth = new Date(date.getFullYear(), date.getMonth() - 1, 1);
var lastDayPrevMonth = new Date(date.getFullYear(), date.getMonth(), 0);
var firstDayMonthBeforeThat = new Date(date.getFullYear(), date.getMonth() - 2, 1);
var lastDayMonthBeforeThat = new Date(date.getFullYear(), date.getMonth() - 1, 0);
var prevMonth = getFormattedDate(firstDayPrevMonth) + ',' + getFormattedDate(lastDayPrevMonth);
var monthBeforeThat = getFormattedDate(firstDayMonthBeforeThat) + ',' + getFormattedDate(lastDayMonthBeforeThat);
var metrics = ['Clicks', 'Impressions', 'Cost', 'Ctr', 'AverageCpc', 'Conversions', 'ConversionRate', 'CostPerConversion'];
var headers = ['CampaignName'];
metrics.forEach(function (metric) {
headers.push(metric + '_PrevMonth');
headers.push(metric + '_MonthBeforeThat');
headers.push(metric + '_ChangePercent');
});
var campaigns = {};
var report = AdsApp.report(
'SELECT CampaignName, ' + metrics.join(', ') +
' FROM CAMPAIGN_PERFORMANCE_REPORT ' +
' WHERE CampaignStatus IN ["ENABLED", "PAUSED"] ' +
' DURING ' + monthBeforeThat
);
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var campaignName = row['CampaignName'];
if (!campaigns[campaignName]) {
campaigns[campaignName] = {};
}
metrics.forEach(function (metric) {
campaigns[campaignName][metric + '_MonthBeforeThat'] = parseFloat(row[metric] || '0');
});
}
report = AdsApp.report(
'SELECT CampaignName, ' + metrics.join(', ') +
' FROM CAMPAIGN_PERFORMANCE_REPORT ' +
' WHERE CampaignStatus IN ["ENABLED", "PAUSED"] ' +
' DURING ' + prevMonth
);
rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var campaignName = row['CampaignName'];
if (!campaigns[campaignName]) {
campaigns[campaignName] = {};
}
metrics.forEach(function (metric) {
var valuePrevMonth = parseFloat(row[metric] || '0');
campaigns[campaignName][metric + '_PrevMonth'] = valuePrevMonth;
var valueMonthBeforeThat = campaigns[campaignName][metric + '_MonthBeforeThat'] || 0;
var changePercent = ((valuePrevMonth - valueMonthBeforeThat) / valueMonthBeforeThat) * 100;
campaigns[campaignName][metric + '_ChangePercent'] = isNaN(changePercent) ? 'N/A' : changePercent.toFixed(2) + '%';
});
}
var csvRows = [headers.join(',')];
for (var campaignName in campaigns) {
var csvRow = [campaignName];
metrics.forEach(function (metric) {
csvRow.push(campaigns[campaignName][metric + '_PrevMonth'] || '0');
csvRow.push(campaigns[campaignName][metric + '_MonthBeforeThat'] || '0');
csvRow.push(campaigns[campaignName][metric + '_ChangePercent'] || 'N/A');
});
csvRows.push(csvRow.join(','));
}
var adsData = csvRows.join('\n');
Logger.log(adsData);
var factsText = FACTS.join("\n");
Logger.log("factsText: " + factsText);
var prompt = "data in csv: " + adsData + "\n " + factsText + "\n " + PROMPT;
var gptResponse = generateTextOpenAI(prompt);
Logger.log("Summary: " + gptResponse);
}
function getFormattedDate(date) {
var month = '' + (date.getMonth() + 1);
var day = '' + date.getDate();
var year = date.getFullYear();
if (month.length < 2) month = '0' + month;
if (day.length < 2) day = '0' + day;
return [year, month, day].join('');
}
function generateTextOpenAI(prompt) {
//texts.pop();
Logger.log("prompt: " + prompt);
var url = 'https://api.openai.com/v1/chat/completions';
var messages= [
{"role": "user", "content": prompt}
];
var payload = {
"model": GPT_MODEL,
"messages": messages
};
var httpOptions = {
"method" : "POST",
"muteHttpExceptions": true,
"contentType": "application/json",
"headers" : {
"Authorization" : 'Bearer ' + OPEN_AI_API_KEY
},
'payload': JSON.stringify(payload)
};
//Logger.log(JSON.stringify(payload));
//var response = JSON.parse(UrlFetchApp.fetch(url, httpOptions));
var response = UrlFetchApp.fetch(url, httpOptions);
// Parsing the response
var responseJson = JSON.parse(response.getContentText());
//Logger.log("response: " + response);
//Logger.log("responseJson: " + responseJson);
// Access the 'choices' array in the response
var choices = responseJson.choices;
// Access the first choice and the 'text' key within it
var text = choices[0].message.content;
//Logger.log(text);
return (text);
}
@codename2121
Copy link

@siliconvallaeys great script thank you, works on most accounts but throws an error on larger accounts:

TypeError: Cannot read properties of undefined (reading '0')
at generateTextOpenAI (Code:168:21)
at main (Code:115:21)
at Object. (adsapp_compiled:20103:54)

@RedVelvet2121
Copy link

@codename2121 not sure if this will help or not, but I also got the same error when I forgot to add the API key.

@codename2121
Copy link

codename2121 commented Jun 13, 2023

@RedVelvet2121 The API key is there, but it still throws the error. However, the exact same script works fine with other accounts.

@marafx
Copy link

marafx commented Jun 23, 2023

Hi, I was wondering if it would be possible to have the script sending the data to a sheet? Thank you!
I tried to generate something like that but got lots of errors. Guess I'm not so good at it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment