Performance Max Kampagnen optimieren: Google Ads Skript zum Ausschluss irrelevanter Suchthemen

/**
 * PMax Search Categories Exporter
 * Exports search categories used in Performance Max campaigns to Google Sheets
 * Shows metrics for both last 30 days and last 7 days side by side
 * Only includes active campaigns
 * Allows marking categories for exclusion
 * 
 * Version: 1.2.0
 * Last Update: 7.4.2025
 * Author: conversion traffic / Benjamin Häntzschel
 * Web: www.conversion-traffic.de
 * 
 * Features:
 * - Exports search categories from Performance Max campaigns
 * - Shows metrics for 30 days and 7 days side by side
 * - Allows marking categories for exclusion
 * - Highlighted rows in red for marked categories
 * - Only considers active campaigns
 */

function main() {
  // ========== YOUR CREDENTIALS/SETTINGS HERE ==========
  // Google Sheets URL - Please add your URL here
  const SPREADSHEET_URL = "https://docs.google.com/spreadsheets/d/1NXpaY6nypRHcWN4b8IkHa3zTu5CjhDt4pKMCng1lrE8/";
  // ===================================================
  
  // Create date ranges for 30 days and 7 days
  const today = new Date();
  
  const startDate30Days = new Date(today);
  startDate30Days.setDate(today.getDate() - 30);
  
  const startDate7Days = new Date(today);
  startDate7Days.setDate(today.getDate() - 7);
  
  // Format dates for GAQL query (YYYY-MM-DD format with single quotes)
  const formattedStartDate30Days = formatDateForGaql(startDate30Days);
  const formattedStartDate7Days = formatDateForGaql(startDate7Days);
  const formattedEndDate = formatDateForGaql(today);
  
  Logger.log("30-day date range: " + formattedStartDate30Days + " to " + formattedEndDate);
  Logger.log("7-day date range: " + formattedStartDate7Days + " to " + formattedEndDate);
  
  // Check if PMax campaigns exist
  let pmaxCampaignIds = checkForPMaxCampaigns();
  
  // Get search category data for PMax campaigns - one campaign at a time
  let allSearchCategoryData = [];
  
  for (let i = 0; i < pmaxCampaignIds.length; i++) {
    const campaignId = pmaxCampaignIds[i];
    Logger.log(`Fetching search categories for campaign ID: ${campaignId}`);
    
    const campaignData30Days = getSearchCategoryDataForCampaign(campaignId, formattedStartDate30Days, formattedEndDate, '30d');
    const campaignData7Days = getSearchCategoryDataForCampaign(campaignId, formattedStartDate7Days, formattedEndDate, '7d');
    
    // Merge the 30-day and 7-day data
    const mergedData = mergeSearchCategoryData(campaignData30Days, campaignData7Days);
    
    // Add all campaign data to our combined array
    allSearchCategoryData = allSearchCategoryData.concat(mergedData);
  }
  
  Logger.log(`Total search categories found across all campaigns: ${allSearchCategoryData.length}`);
  
  // Export data to Google Sheets
  exportToSpreadsheet(SPREADSHEET_URL, allSearchCategoryData);
}

/**
 * Format date for GAQL query as 'YYYY-MM-DD'
 */
function formatDateForGaql(date) {
  const year = date.getFullYear();
  const month = (date.getMonth() + 1).toString().padStart(2, '0');
  const day = date.getDate().toString().padStart(2, '0');
  return "'" + year + "-" + month + "-" + day + "'";
}

/**
 * Checks if Performance Max campaigns exist and returns their IDs
 * Only returns ENABLED campaigns
 */
function checkForPMaxCampaigns() {
  let pmaxCampaignIds = [];
  
  try {
    // Use a report query to check for active PMax campaigns only
    const query = `
      SELECT
        campaign.id,
        campaign.name,
        campaign.advertising_channel_type,
        campaign.status
      FROM campaign
      WHERE campaign.status = 'ENABLED'
    `;
    
    const report = AdsApp.report(query);
    const rows = report.rows();
    
    let pmaxCount = 0;
    
    while (rows.hasNext()) {
      const row = rows.next();
      const channelType = row['campaign.advertising_channel_type'];
      const campaignName = row['campaign.name'];
      const campaignId = row['campaign.id'];
      const campaignStatus = row['campaign.status'];
      
      Logger.log(`Campaign: ${campaignName} (ID: ${campaignId}), Channel Type: ${channelType}, Status: ${campaignStatus}`);
      
      if (channelType === 'PERFORMANCE_MAX') {
        pmaxCount++;
        pmaxCampaignIds.push(campaignId);
        Logger.log(`Found active PMax campaign: ${campaignName} (ID: ${campaignId})`);
      }
    }
    
    if (pmaxCount === 0) {
      Logger.log("No active Performance Max campaigns found in the account.");
    } else {
      Logger.log(`Found ${pmaxCount} active Performance Max campaigns.`);
    }
    
    return pmaxCampaignIds;
  } catch (e) {
    Logger.log("Error checking for PMax campaigns: " + e.message);
    return [];
  }
}

/**
 * Retrieves search category data for a single PMax campaign for a specified date range
 * Only includes categories with at least one click in the specified date range
 */
function getSearchCategoryDataForCampaign(campaignId, startDate, endDate, datePeriod) {
  const searchCategoryData = [];
  
  try {
    // Try to add conversions, but handle failure gracefully
    let query = `
      SELECT 
        campaign_search_term_insight.campaign_id,
        campaign_search_term_insight.category_label,
        campaign.name,
        metrics.impressions,
        metrics.clicks,
        metrics.conversions
      FROM campaign_search_term_insight
      WHERE campaign_search_term_insight.campaign_id = ${campaignId}
      AND segments.date BETWEEN ${startDate} AND ${endDate}
      AND metrics.clicks > 0
    `;
    
    Logger.log(`Executing ${datePeriod} query: ${query}`);
    
    try {
      let report = AdsApp.report(query);
      let rows = report.rows();
      
      while (rows.hasNext()) {
        let row = rows.next();
        
        searchCategoryData.push({
          categoryLabel: row['campaign_search_term_insight.category_label'],
          campaignName: row['campaign.name'],
          campaignId: row['campaign_search_term_insight.campaign_id'],
          impressions: parseInt(row['metrics.impressions']),
          clicks: parseInt(row['metrics.clicks']),
          conversions: parseFloat(row['metrics.conversions'] || "0"),
          datePeriod: datePeriod
        });
      }
    } catch (conversionError) {
      // If adding conversions fails, retry without conversions
      Logger.log(`Could not get conversions data for ${datePeriod}, retrying without conversions: ${conversionError.message}`);
      
      query = `
        SELECT 
          campaign_search_term_insight.campaign_id,
          campaign_search_term_insight.category_label,
          campaign.name,
          metrics.impressions,
          metrics.clicks
        FROM campaign_search_term_insight
        WHERE campaign_search_term_insight.campaign_id = ${campaignId}
        AND segments.date BETWEEN ${startDate} AND ${endDate}
        AND metrics.clicks > 0
      `;
      
      Logger.log(`Retrying with ${datePeriod} query: ${query}`);
      let report = AdsApp.report(query);
      let rows = report.rows();
      
      while (rows.hasNext()) {
        let row = rows.next();
        
        searchCategoryData.push({
          categoryLabel: row['campaign_search_term_insight.category_label'],
          campaignName: row['campaign.name'],
          campaignId: row['campaign_search_term_insight.campaign_id'],
          impressions: parseInt(row['metrics.impressions']),
          clicks: parseInt(row['metrics.clicks']),
          conversions: "N/A", // Indicate that conversions are not available
          datePeriod: datePeriod
        });
      }
    }
    
    Logger.log(`Found ${searchCategoryData.length} search categories with clicks for campaign ID: ${campaignId} in ${datePeriod} period`);
    
    return searchCategoryData;
  } catch (e) {
    Logger.log(`Error fetching ${datePeriod} search categories for campaign ID ${campaignId}: ${e.message}`);
    return [];
  }
}

/**
 * Merges the 30-day and 7-day search category data
 */
function mergeSearchCategoryData(data30Days, data7Days) {
  const mergedData = [];
  const categoryMap = new Map();
  
  // First, process all 30-day data
  data30Days.forEach(item => {
    const key = `${item.categoryLabel}_${item.campaignId}`;
    categoryMap.set(key, {
      categoryLabel: item.categoryLabel,
      campaignName: item.campaignName,
      campaignId: item.campaignId,
      impressions30d: item.impressions,
      clicks30d: item.clicks,
      conversions30d: item.conversions,
      impressions7d: 0,
      clicks7d: 0,
      conversions7d: 0
    });
  });
  
  // Then, update with 7-day data or add new entries
  data7Days.forEach(item => {
    const key = `${item.categoryLabel}_${item.campaignId}`;
    if (categoryMap.has(key)) {
      // Update existing entry with 7-day data
      const existingItem = categoryMap.get(key);
      existingItem.impressions7d = item.impressions;
      existingItem.clicks7d = item.clicks;
      existingItem.conversions7d = item.conversions;
    } else {
      // Add new entry that only has 7-day data
      categoryMap.set(key, {
        categoryLabel: item.categoryLabel,
        campaignName: item.campaignName,
        campaignId: item.campaignId,
        impressions30d: 0,
        clicks30d: 0,
        conversions30d: 0,
        impressions7d: item.impressions,
        clicks7d: item.clicks,
        conversions7d: item.conversions
      });
    }
  });
  
  // Convert map to array
  categoryMap.forEach(value => {
    mergedData.push(value);
  });
  
  return mergedData;
}

/**
 * Exports the search category data to a Google Spreadsheet
 */
function exportToSpreadsheet(spreadsheetUrl, searchCategoryData) {
  try {
    // Open the spreadsheet
    const spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
    const sheet = spreadsheet.getActiveSheet();
    
    // Rename the active sheet
    sheet.setName("PMax Search Categories");
    
    // Check for and remove any existing filters
    const existingFilter = sheet.getFilter();
    if (existingFilter) {
      existingFilter.remove();
    }
    
    // Clear the sheet
    sheet.clear();
    
    // Add headers - group the same metrics together (30d and 7d)
    sheet.appendRow([
      "Search Category", 
      "Campaign Name", 
      "Campaign ID", 
      "Impressions (30D)",
      "Impressions (7D)",
      "Clicks (30D)",
      "Clicks (7D)",
      "Conversions (30D)",
      "Conversions (7D)",
      "Action"
    ]);
    
    // Format the header row - make it bold
    sheet.getRange(1, 1, 1, 10).setFontWeight("bold");
    
    if (searchCategoryData.length === 0) {
      // If no data, add a note
      sheet.getRange(2, 1).setValue("No search categories with clicks found in the last 30 or 7 days.");
    } else {
      // Sort data by campaign name first, then by 30-day clicks (descending)
      searchCategoryData.sort((a, b) => {
        // First compare campaign names
        const campaignNameComparison = a.campaignName.localeCompare(b.campaignName);
        
        // If campaign names are the same, sort by 30-day clicks (descending)
        if (campaignNameComparison === 0) {
          return b.clicks30d - a.clicks30d;
        }
        
        // Otherwise sort by campaign name
        return campaignNameComparison;
      });
      
      // Add data rows - with metrics grouped together
      searchCategoryData.forEach(item => {
        sheet.appendRow([
          item.categoryLabel,
          item.campaignName,
          item.campaignId,
          item.impressions30d,
          item.impressions7d,
          item.clicks30d,
          item.clicks7d,
          item.conversions30d === "N/A" ? "N/A" : item.conversions30d,
          item.conversions7d === "N/A" ? "N/A" : item.conversions7d,
          ""  // Empty action column
        ]);
      });
      
      // Set up dropdown menu in the Action column - include empty option, Exclude, and option to Reset
      const lastRow = sheet.getLastRow();
      if (lastRow > 1) {  // If there's data beyond the header row
        const actionRange = sheet.getRange(2, 10, lastRow - 1, 1);
        const rule = SpreadsheetApp.newDataValidation()
          .requireValueInList(['', 'Exclude', 'Reset'], true)
          .build();
        actionRange.setDataValidation(rule);
        
        // Add conditional formatting to color rows red when "Exclude" is selected
        const range = sheet.getRange(2, 1, lastRow - 1, 10);
        
        // Create a conditional format rule that checks if column J (10) contains "Exclude"
        const conditionalFormatRule = SpreadsheetApp.newConditionalFormatRule()
          .whenFormulaSatisfied('=$J2="Exclude"')
          .setBackground("#ffcccc")  // Light red background
          .setRanges([range])
          .build();
        
        // Apply the conditional format rule
        const rules = sheet.getConditionalFormatRules();
        rules.push(conditionalFormatRule);
        sheet.setConditionalFormatRules(rules);
        
        // Add a note to explain the Action column
        sheet.getRange(1, 10).setNote('Select "Exclude" to mark categories. The row will be highlighted in red. Use "Reset" to remove the marking.');
      }
      
      // Add a simple info sheet for instructions
      addInfoSheet(spreadsheet, sheet);
      
      // Freeze the header row
      sheet.setFrozenRows(1);
      
      // Auto-resize columns for better readability
      sheet.autoResizeColumns(1, 10);
    }
    
    Logger.log("Successfully exported " + searchCategoryData.length + " rows to Google Sheets");
  } catch (e) {
    Logger.log("Error exporting to Google Sheets: " + e.message);
  }
}

/**
 * Adds a simple info sheet with instructions
 */
function addInfoSheet(spreadsheet, sheet) {
  // Create a new info sheet if it doesn't exist
  let infoSheet = spreadsheet.getSheetByName('Info');
  if (!infoSheet) {
    infoSheet = spreadsheet.insertSheet('Info');
    
    // Add title
    infoSheet.getRange('A1').setValue('Guide for PMax Search Categories');
    infoSheet.getRange('A1:C1').merge();
    let titleStyle = infoSheet.getRange('A1');
    titleStyle.setFontWeight('bold');
    titleStyle.setFontSize(14);
    
    // Add instructions
    infoSheet.getRange('A3').setValue('How to use the "Action" column to mark search categories:');
    infoSheet.getRange('A3').setFontWeight('bold');
    
    infoSheet.getRange('A5').setValue('1. Filter the data as needed (e.g., by campaigns, clicks, etc.)');
    infoSheet.getRange('A6').setValue('2. Select "Exclude" in the "Action" column for unwanted categories (row will be highlighted in red)');
    infoSheet.getRange('A7').setValue('3. Select "Reset" to remove the marking');
    infoSheet.getRange('A8').setValue('4. Copy the marked categories into your Google Ads campaign settings');
    
    // Add tips section
    infoSheet.getRange('A10').setValue('Optimization Tips:');
    infoSheet.getRange('A10').setFontWeight('bold');
    
    infoSheet.getRange('A12').setValue('• Check categories with many clicks but few conversions');
    infoSheet.getRange('A13').setValue('• Compare the 7-day and 30-day data to identify trends');
    infoSheet.getRange('A14').setValue('• Categories with high clicks but low conversions are good candidates for exclusion');
    
    // Auto-resize columns
    infoSheet.autoResizeColumns(1, 3);
    
    // Switch back to the main data sheet
    sheet.activate();
  }
}