Get a written performance summary of a Google Ads account's campaigns using GPT

Google Ads summarizer with 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);

}

@siliconvallaeys this script seems very helpful & interesting! One thing I wanted to check - after running the script, where should we see the actual summary of campaign performance?

@RedVelvet2121 the script gets the summary in line 115 in a variable. I realize that may be hard to use so I added line 116 which will print the summary on screen in the Google Ads scripts logs. You could easily add an extra line to send that summary in email or do something else with it. Hope that helps!

@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)

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

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

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.