/**
 * RSA BOOSTER v1.5
 * Created by Francesco Cifardi
 * For issues or questions reach out on Linkedin (https://www.linkedin.com/in/francescocifardi/)
 * Free to use and share, a mention would be appreciated :-)
 * 
 * This script monitors and evaluates asset performance across Google Ads campaigns,
 * identifying low-performing assets and generating comprehensive reports. It exports
 * data to a Google Sheet and sends email alerts when low-performing assets are detected.
 * 
 * Key Features:
 * - Tracks performance metrics (clicks, impressions, CTR)
 * - Identifies low-performing assets
 * - Generates unique ad labels within ad groups
 * - Creates detailed performance reports
 * - Sends email alerts for low-performing assets
 * 
 * Note: The provided Google Sheet template includes an additional Apps Script that can
 * generate alternative suggestions for low-performing assets using OpenAI's API
 * (requires API key configuration).
 * 
 * For complete setup instructions and documentation, visit:
 * https://medialauncher.beehiiv.com/p/rsa-booster
 */

// User-modifiable criteria:
var NUMBER_OF_CLICKS = 100;  // Minimum number of clicks to qualify for evaluation
var LOOKBACK_WINDOW = 'LAST_30_DAYS'; // Look-back window for performance data
// Spreadsheet template: https://docs.google.com/spreadsheets/d/1ydQFFkGojmcxjvp3PkXaT-CpypawHpeil2xSD5qaB2c/copy
var SPREADSHEET_URL = 'YOUR_URL'; // Add the URL of your Google Sheets template copy h
var EMAIL_ADDRESSES = [
  '[email protected]'
  // Add more email addresses as needed, between signle quotes, and comma separated
];

function main() {
  // Get date range for reporting
  const dates = getLookbackDates(LOOKBACK_WINDOW);
  
  // Initialize spreadsheet
  const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  const sheet = ss.getActiveSheet();
  
  // Define headers first
  const headers = [
    'Campaign', 'Ad Group', 'Ad Label', 'Clicks', 'Impressions', 'CTR', 
    'Asset Type', 'Asset Text', 'Performance Label',
    'Report Start Date', 'Report End Date'
  ];
  
  // Clear existing data while preserving formatting
  const lastRow = Math.max(sheet.getLastRow(), 1);
  const lastCol = Math.max(sheet.getLastColumn(), headers.length);
  if (lastRow > 1) { // Only clear if there's data (preserve headers)
    sheet.getRange(2, 1, lastRow - 1, lastCol).clearContent();
  }
  
  // Set headers
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  
  // Rest of query remains the same
  const query = `
    SELECT
      ad_group_ad.ad.id,
      ad_group_ad.ad.responsive_search_ad.headlines,
      ad_group_ad.ad.responsive_search_ad.descriptions,
      campaign.name,
      ad_group.name,
      metrics.clicks,
      metrics.impressions
    FROM ad_group_ad
    WHERE 
      campaign.status = "ENABLED"
      AND metrics.clicks > ${NUMBER_OF_CLICKS}
      AND segments.date DURING ${LOOKBACK_WINDOW}
      LIMIT 1000
  `;
  
  const response = AdsApp.search(query);
  let rowData = [];
  let lowPerformingAssets = [];
  let adGroupCounter = {}; // Track ad counts per ad group
  
  while (response.hasNext()) {
    const row = response.next();
    const jsonString = JSON.stringify(row);
    const data = JSON.parse(jsonString);
    
    // Generate ad label
    const adGroupKey = `${row.campaign.name}_${row.adGroup.name}`;
    adGroupCounter[adGroupKey] = (adGroupCounter[adGroupKey] || 0) + 1;
    const adLabel = `Ad ${adGroupCounter[adGroupKey]}`;
    
    // Process headlines and descriptions
    const assets = processAssets(data);
    
    // Create row for each asset
    assets.forEach(asset => {
      if (asset.performanceLabel === 'LOW') {
        lowPerformingAssets.push({
          campaign: row.campaign.name,
          adGroup: row.adGroup.name,
          adLabel: adLabel,
          type: asset.type,
          text: asset.text
        });
      }
      
      rowData.push([
        row.campaign.name,
        row.adGroup.name,
        adLabel,  // Add ad label to the row
        row.metrics.clicks,
        row.metrics.impressions,
        (row.metrics.clicks/row.metrics.impressions * 100).toFixed(2) + '%',
        asset.type,
        asset.text,
        asset.performanceLabel,
        dates.startDate,
        dates.endDate
      ]);
    });
  }
  
  // Write all data at once
  if (rowData.length > 0) {
    sheet.getRange(2, 1, rowData.length, headers.length).setValues(rowData);
  }
  
  // Send email if there are low performing assets
  if (lowPerformingAssets.length > 0) {
    sendLowPerformanceAlert(lowPerformingAssets, SPREADSHEET_URL);
  }
}

// New function to process assets and return structured data
function processAssets(data) {
  const assets = [];
  
  function findAssets(obj, context) {
    if (typeof obj === 'object') {
      let currentAsset = {};
      
      for (const key in obj) {
        if (Array.isArray(obj[key])) {
          for (const item of obj[key]) {
            findAssets(item, key);
          }
        } else if (typeof obj[key] === 'object') {
          findAssets(obj[key], key);
        } else if (key === 'text') {
          currentAsset.type = context === 'headlines' ? 'Headline' : 'Description';
          currentAsset.text = obj[key];
        } else if (key === 'assetPerformanceLabel') {
          currentAsset.performanceLabel = obj[key];
          assets.push({...currentAsset});
          currentAsset = {};
        }
      }
    }
  }
  
  findAssets(data, '');
  return assets;
}

// New function to calculate date range based on lookback window
function getLookbackDates(lookbackWindow) {
  const endDate = new Date();
  let startDate = new Date();
  
  switch(lookbackWindow) {
    case 'LAST_30_DAYS':
      startDate.setDate(endDate.getDate() - 30);
      break;
    case 'LAST_7_DAYS':
      startDate.setDate(endDate.getDate() - 7);
      break;
    // Add more cases as needed
  }
  
  return {
    startDate: startDate.toISOString().split('T')[0],
    endDate: endDate.toISOString().split('T')[0]
  };
}

// Updated email alert function
function sendLowPerformanceAlert(lowPerformingAssets, spreadsheetUrl) {
  const subject = 'Google Ads Alert: Low Performing RSA Assets Detected';
  
  let emailBody = 'The following RSA assets are showing low performance:\n\n';
  
  lowPerformingAssets.forEach(asset => {
    emailBody += `Campaign: ${asset.campaign}\n`;
    emailBody += `Ad Group: ${asset.adGroup}\n`;
    emailBody += `Ad Label: ${asset.adLabel}\n`;
    emailBody += `Asset Type: ${asset.type}\n`;
    emailBody += `Asset Text: ${asset.text}\n\n`;
  });
  
  emailBody += `\nView full report here: ${spreadsheetUrl}`;
  
  MailApp.sendEmail({
    to: EMAIL_ADDRESSES.join(','),
    subject: subject,
    body: emailBody
  });
}