Untitled

// Define the Google Drive folder ID where the CSV will be saved
var FOLDER_ID = '1LTqGUIrhIO4hmtn0fiEpwS-CUCDdL-k-'; // Replace with your Google Drive folder ID
var FILE_NAME = 'offer_id_notes_5308844207.csv'; // Define the file name

function main() {
  // Fetch data from Google Ads
  var data = fetchGoogleAdsData();
  
  // Apply scoring logic and format data for CSV
  var formattedData = applyScoringLogic(data);
  
  // Convert data to CSV format
  var csvData = convertToCSV(formattedData);
  
  // Save CSV to Google Drive, overwriting the existing file if it exists
  exportToGoogleDrive(csvData);
}

function fetchGoogleAdsData() {
  var dateRange = 'LAST_30_DAYS';
  var query = `
    SELECT segments.product_item_id, metrics.impressions, metrics.clicks, metrics.ctr, metrics.average_cpc, metrics.cost_micros, metrics.conversions, metrics.conversions_value
    FROM shopping_performance_view
    WHERE segments.date DURING ${dateRange}
  `;
  
  var report = AdsApp.report(query);
  var rows = report.rows();
  
  var data = [];
  
  while (rows.hasNext()) {
    var row = rows.next();
    var offerId = row['segments.product_item_id'];
    var impressions = row['metrics.impressions'];
    var clicks = row['metrics.clicks'];
    var ctr = row['metrics.ctr'];
    var avgCpcMicros = row['metrics.average_cpc'];
    var avgCpc = avgCpcMicros / 1000000; // Convert from micros to currency unit
    var costMicros = row['metrics.cost_micros'];
    var cost = costMicros / 1000000; // Convert from micros to currency unit
    var conversions = row['metrics.conversions'];
    var conversionValue = row['metrics.conversions_value'];
    
    data.push({ offerId: offerId, impressions: impressions, clicks: clicks, ctr: ctr, avgCpc: avgCpc, cost: cost, conversions: conversions, conversionValue: conversionValue });
  }
  
  return data;
}

function applyScoringLogic(data) {
  var scoredData = [];
  
  data.forEach(function(row) {
    var offerId = row.offerId;
    var conversionValue = row.conversionValue;
    var cost = row.cost;
    var clicks = row.clicks;
    var conversions = row.conversions;
    var impressions = row.impressions;
    
    var conversionValuePerCost = conversionValue / cost || 0;
    var custom_label_0 = '';
    
    if (conversionValuePerCost < 5 && conversionValuePerCost > 0) {
      custom_label_0 = 'below_target';
    } else if (conversionValuePerCost >= 5) {
      custom_label_0 = 'above_target';
    } else if (clicks >= 100 && conversions === 0) {
      custom_label_0 = 'inefficient_spend';
    } else if (impressions <= 50) {
      custom_label_0 = 'low_impressions';
    } else {
      custom_label_0 = 'data_deficient';
    }
    
    scoredData.push({ offerId: offerId, custom_label_0: custom_label_0 });
  });
  
  return scoredData;
}

function convertToCSV(data) {
  var csv = 'id,custom_label_0\n';
  
  data.forEach(function(row) {
    csv += row.offerId + ',' + row.custom_label_0 + '\n';
  });
  
  return csv;
}

function exportToGoogleDrive(csvData) {
  var folder = DriveApp.getFolderById(FOLDER_ID);
  var file = getFileByName(folder, FILE_NAME);
  
  if (file) {
    // Overwrite the existing file
    file.setContent(csvData);
    Logger.log('File URL: ' + file.getUrl());
  } else {
    // Create a new file
    file = folder.createFile(FILE_NAME, csvData, MimeType.CSV);
    Logger.log('File URL: ' + file.getUrl());
  }
}

function getFileByName(folder, fileName) {
  var files = folder.getFilesByName(fileName);
  return files.hasNext() ? files.next() : null;
}