Enterprise Integration

// Salesforce CRM integration for lead quality optimization
function optimizeCampaignsWithCRMData() {
  Logger.log("=== CRM-Driven Campaign Optimization ===");
  
  // Fetch lead quality data from CRM API
  const crmData = fetchSalesforceLeadData();
  Logger.log(`Retrieved data for ${Object.keys(crmData).length} campaigns from CRM`);
  
  const campaigns = AdsApp.campaigns()
    .withCondition('Status = ENABLED')
    .withCondition('Cost > 500 DURING LAST_30_DAYS')
    .get();
  
  let optimized = 0;
  
  while (campaigns.hasNext()) {
    const campaign = campaigns.next();
    const campaignName = campaign.getName();
    
    // Match campaign with CRM data
    const leadData = crmData[campaignName];
    if (!leadData) {
      Logger.log(`No CRM data found for: ${campaignName}`);
      continue;
    }
    
    // Calculate lead quality metrics
    const leadScore = calculateLeadQuality(leadData);
    const conversionRate = leadData.closedWon / leadData.totalLeads;
    const avgDealValue = leadData.totalRevenue / leadData.closedWon;
    
    Logger.log(`${campaignName}: Score=${leadScore}, CVR=${(conversionRate*100).toFixed(1)}%, AOV=€${avgDealValue.toFixed(0)}`);
    
    // Adjust budget based on lead quality
    const currentBudget = campaign.getBudget();
    let budgetMultiplier = 1.0;
    
    if (leadScore >= 80 && conversionRate > 0.15) {
      budgetMultiplier = 1.3; // Increase high-quality campaigns
    } else if (leadScore >= 60 && conversionRate > 0.10) {
      budgetMultiplier = 1.1; // Slight increase for good campaigns
    } else if (leadScore < 40 || conversionRate < 0.05) {
      budgetMultiplier = 0.7; // Decrease poor-quality campaigns
    }
    
    const newBudget = Math.round(currentBudget * budgetMultiplier);
    
    if (newBudget !== currentBudget) {
      campaign.setBudget(newBudget);
      optimized++;
      Logger.log(`Updated budget: €${currentBudget} → €${newBudget} (Quality: ${leadScore}/100)`);
      
      // Update CRM with optimization status
      updateSalesforceRecord(campaignName, {
        lastOptimized: new Date().toISOString(),
        budgetAdjustment: budgetMultiplier,
        leadQualityScore: leadScore
      });
    }
  }
  
  Logger.log(`=== Optimization Complete: ${optimized} campaigns updated ===`);
}

function fetchSalesforceLeadData() {
  const SF_API_ENDPOINT = PropertiesService.getScriptProperties().getProperty('SF_API_URL');
  const SF_ACCESS_TOKEN = PropertiesService.getScriptProperties().getProperty('SF_ACCESS_TOKEN');
  
  const response = UrlFetchApp.fetch(SF_API_ENDPOINT + '/leads/campaign-summary', {
    method: 'GET',
    headers: {
      'Authorization': 'Bearer ' + SF_ACCESS_TOKEN,
      'Content-Type': 'application/json'
    }
  });
  
  if (response.getResponseCode() !== 200) {
    throw new Error(`CRM API Error: ${response.getResponseCode()}`);
  }
  
  return JSON.parse(response.getContentText());
}

function calculateLeadQuality(leadData) {
  // Multi-factor lead quality scoring
  const conversionScore = Math.min(leadData.closedWon / leadData.totalLeads * 100, 40);
  const valueScore = Math.min(leadData.totalRevenue / leadData.totalLeads / 100, 30);
  const velocityScore = Math.min(30 / leadData.avgSalesCycle * 20, 20);
  const engagementScore = Math.min(leadData.emailOpens / leadData.totalLeads * 10, 10);
  
  return Math.round(conversionScore + valueScore + velocityScore + engagementScore);
}

// BigQuery data warehouse integration for advanced attribution
function optimizeWithDataWarehouseInsights() {
  Logger.log("=== Data Warehouse Attribution Analysis ===");
  
  // Query advanced attribution data from BigQuery
  const attributionData = queryBigQueryAttribution();
  
  const campaigns = AdsApp.campaigns()
    .withCondition('Status = ENABLED')
    .get();
  
  while (campaigns.hasNext()) {
    const campaign = campaigns.next();
    const campaignId = campaign.getId();
    
    const attribution = attributionData[campaignId];
    if (!attribution) continue;
    
    // Calculate true ROAS including offline conversions
    const trueROAS = (attribution.onlineRevenue + attribution.offlineRevenue) / attribution.adSpend;
    const crossChannelContribution = attribution.assistedConversions / attribution.totalConversions;
    
    Logger.log(`${campaign.getName()}: True ROAS=${trueROAS.toFixed(2)}, Cross-channel=${(crossChannelContribution*100).toFixed(1)}%`);
    
    // Adjust bidding strategy based on complete attribution picture
    const keywords = campaign.keywords()
      .withCondition('Status = ENABLED')
      .get();
    
    while (keywords.hasNext()) {
      const keyword = keywords.next();
      const currentBid = keyword.getMaxCpc();
      
      // Increase bids for keywords with high cross-channel value
      let bidMultiplier = 1.0;
      
      if (trueROAS > 4.0 && crossChannelContribution > 0.3) {
        bidMultiplier = 1.25; // Strong cross-channel performance
      } else if (trueROAS > 3.0 && crossChannelContribution > 0.2) {
        bidMultiplier = 1.1; // Good assisted conversions
      } else if (trueROAS < 2.0) {
        bidMultiplier = 0.85; // Underperforming even with attribution
      }
      
      if (bidMultiplier !== 1.0) {
        const newBid = currentBid * bidMultiplier;
        keyword.setMaxCpc(newBid);
        Logger.log(`Bid adjusted: ${keyword.getText()} €${currentBid.toFixed(2)} → €${newBid.toFixed(2)}`);
      }
    }
  }
}

function queryBigQueryAttribution() {
  const BQ_PROJECT_ID = PropertiesService.getScriptProperties().getProperty('BQ_PROJECT_ID');
  const BQ_DATASET = PropertiesService.getScriptProperties().getProperty('BQ_DATASET');
  
  // BigQuery SQL for cross-channel attribution analysis
  const query = `
    SELECT 
      campaign_id,
      SUM(online_revenue) as onlineRevenue,
      SUM(offline_revenue) as offlineRevenue,
      SUM(ad_spend) as adSpend,
      SUM(assisted_conversions) as assistedConversions,
      SUM(total_conversions) as totalConversions
    FROM `${BQ_PROJECT_ID}.${BQ_DATASET}.attribution_model`
    WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    GROUP BY campaign_id
  `;
  
  // Execute BigQuery request (simplified - actual implementation would use BigQuery API)
  const response = UrlFetchApp.fetch(`https://bigquery.googleapis.com/bigquery/v2/projects/${BQ_PROJECT_ID}/queries`, {
    method: 'POST',
    headers: {
      'Authorization': 'Bearer ' + getGCPAccessToken(),
      'Content-Type': 'application/json'
    },
    payload: JSON.stringify({
      query: query,
      useLegacySql: false
    })
  });
  
  const result = JSON.parse(response.getContentText());
  
  // Transform results into lookup object
  const attributionMap = {};
  result.rows.forEach(row => {
    attributionMap[row.f[0].v] = {
      onlineRevenue: parseFloat(row.f[1].v),
      offlineRevenue: parseFloat(row.f[2].v),
      adSpend: parseFloat(row.f[3].v),
      assistedConversions: parseInt(row.f[4].v),
      totalConversions: parseInt(row.f[5].v)
    };
  });
  
  return attributionMap;
}