Free PPC

Toolkit

/******************************************************************************
 * AD COPY PERFORMANCE MATRIX
 *
 * Generated by PPC.io Script Engine
 * https://ppc.io
 *
 * Purpose: Export all RSA headlines and descriptions with performance data for AI analysis
 * Author:  PPC.io
 * Version: 2.0
 * Updated: 2025-01-13
 *
 * SETUP INSTRUCTIONS:
 * 1. Create a new Google Sheet or use 'CREATE_NEW' to auto-create
 * 2. Paste the spreadsheet URL in CONFIG.SPREADSHEET_URL
 * 3. Schedule: Run monthly for ad copy optimization
 *
 * USE CASE: "Which headline themes are working across this account? Which should I kill?"
 *
 * CHANGELOG:
 * v1.0 - Initial release
 * v2.0 - Added Performance Score, Ad Strength, GAQL-first, numbered sheets
 *
 ******************************************************************************/

var CONFIG = {
  // ═══════════════════════════════════════════════════════════════════════════
  // OUTPUT SETTINGS
  // ═══════════════════════════════════════════════════════════════════════════
  SPREADSHEET_URL: 'CREATE_NEW',
  EMAIL_RECIPIENTS: [],
  SLACK_WEBHOOK_URL: '',

  // ═══════════════════════════════════════════════════════════════════════════
  // DATE RANGE
  // ═══════════════════════════════════════════════════════════════════════════
  DATE_RANGE: 'LAST_30_DAYS',

  // ═══════════════════════════════════════════════════════════════════════════
  // FILTERS
  // ═══════════════════════════════════════════════════════════════════════════
  CAMPAIGN_NAME_CONTAINS: '',
  CAMPAIGN_NAME_EXCLUDES: '',
  INCLUDE_PAUSED_ADS: false,
  MINIMUM_IMPRESSIONS: 100,

  // ═══════════════════════════════════════════════════════════════════════════
  // EXECUTION SETTINGS
  // ═══════════════════════════════════════════════════════════════════════════
  LOG_LEVEL: 'INFO',
  TIME_LIMIT_MINUTES: 25,
  BATCH_SIZE: 500
};

/******************************************************************************
 * MAIN EXECUTION
 ******************************************************************************/
function main() {
  var startTime = new Date();
  log('INFO', 'Ad Copy Performance Matrix started: ' + startTime.toISOString());

  try {
    var ss = initializeSpreadsheet();
    var results = fetchAdPerformance(startTime);

    exportHeadlines(ss, results);
    exportDescriptions(ss, results);
    exportAdPerformance(ss, results);
    createSummary(ss, results);

    var duration = ((new Date() - startTime) / 1000).toFixed(1);
    log('INFO', 'Completed in ' + duration + 's. Ads: ' + results.summary.totalAds);

    sendNotifications(ss.getUrl(), results, startTime);

  } catch (error) {
    log('ERROR', 'Fatal: ' + error.message);
    sendErrorNotification(error);
  }
}

/******************************************************************************
 * FETCH AD PERFORMANCE (GAQL-First)
 ******************************************************************************/
function fetchAdPerformance(startTime) {
  var results = {
    ads: [],
    headlines: {},
    descriptions: {},
    summary: {
      totalAds: 0,
      totalImpressions: 0,
      totalClicks: 0,
      totalConversions: 0,
      totalCost: 0
    }
  };

  var query = 'SELECT ' +
    'campaign.name, ' +
    'ad_group.name, ' +
    'ad_group_ad.ad.id, ' +
    'ad_group_ad.ad.responsive_search_ad.headlines, ' +
    'ad_group_ad.ad.responsive_search_ad.descriptions, ' +
    'ad_group_ad.ad.final_urls, ' +
    'ad_group_ad.status, ' +
    'ad_group_ad.ad.strength, ' +
    'metrics.impressions, ' +
    'metrics.clicks, ' +
    'metrics.cost_micros, ' +
    'metrics.conversions, ' +
    'metrics.conversions_value ' +
    'FROM ad_group_ad ' +
    'WHERE ad_group_ad.ad.type = "RESPONSIVE_SEARCH_AD" ' +
    'AND segments.date DURING ' + CONFIG.DATE_RANGE;

  if (!CONFIG.INCLUDE_PAUSED_ADS) {
    query += ' AND ad_group_ad.status = "ENABLED"';
  }

  if (CONFIG.CAMPAIGN_NAME_CONTAINS) {
    query += " AND campaign.name LIKE '%" + CONFIG.CAMPAIGN_NAME_CONTAINS + "%'";
  }

  query += ' ORDER BY metrics.impressions DESC';

  try {
    var report = AdsApp.report(query);
    var rows = report.rows();
    var count = 0;

    while (rows.hasNext()) {
      if (count % 500 === 0 && count > 0) {
        var elapsed = (new Date() - startTime) / 60000;
        if (elapsed > CONFIG.TIME_LIMIT_MINUTES) {
          log('WARN', 'Time limit reached at ' + count + ' ads');
          break;
        }
      }

      var row = rows.next();
      var campaignName = row['campaign.name'];

      // Apply EXCLUDES filter
      if (CONFIG.CAMPAIGN_NAME_EXCLUDES &&
          campaignName.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_EXCLUDES.toLowerCase()) !== -1) {
        continue;
      }

      var impressions = parseInt(row['metrics.impressions'] || 0);
      if (impressions < CONFIG.MINIMUM_IMPRESSIONS) continue;

      var clicks = parseInt(row['metrics.clicks'] || 0);
      var cost = parseFloat(row['metrics.cost_micros'] || 0) / 1000000;
      var conversions = parseFloat(row['metrics.conversions'] || 0);
      var convValue = parseFloat(row['metrics.conversions_value'] || 0);

      var ctr = impressions > 0 ? (clicks / impressions) : 0;
      var convRate = clicks > 0 ? (conversions / clicks) : 0;

      var adData = {
        campaign: campaignName,
        adGroup: row['ad_group.name'],
        adId: row['ad_group_ad.ad.id'],
        status: row['ad_group_ad.status'],
        strength: row['ad_group_ad.ad.strength'] || 'UNKNOWN',
        finalUrl: row['ad_group_ad.ad.final_urls'] || '',
        impressions: impressions,
        clicks: clicks,
        cost: cost,
        conversions: conversions,
        convValue: convValue,
        ctr: ctr,
        convRate: convRate,
        cpa: conversions > 0 ? cost / conversions : null,
        headlines: [],
        descriptions: []
      };

      // Parse headlines
      var headlinesRaw = row['ad_group_ad.ad.responsive_search_ad.headlines'];
      if (headlinesRaw) {
        adData.headlines = parseAssets(headlinesRaw);
        aggregateAssets(results.headlines, adData.headlines, adData);
      }

      // Parse descriptions
      var descriptionsRaw = row['ad_group_ad.ad.responsive_search_ad.descriptions'];
      if (descriptionsRaw) {
        adData.descriptions = parseAssets(descriptionsRaw);
        aggregateAssets(results.descriptions, adData.descriptions, adData);
      }

      results.ads.push(adData);
      results.summary.totalAds++;
      results.summary.totalImpressions += impressions;
      results.summary.totalClicks += clicks;
      results.summary.totalCost += cost;
      results.summary.totalConversions += conversions;

      count++;
    }

  } catch (e) {
    log('WARN', 'GAQL failed, using fallback: ' + e.message);
    fetchAdPerformanceFallback(results, startTime);
  }

  return results;
}

/******************************************************************************
 * FALLBACK: Standard API
 ******************************************************************************/
function fetchAdPerformanceFallback(results, startTime) {
  var ads = AdsApp.ads()
    .withCondition('Type = RESPONSIVE_SEARCH_AD')
    .withCondition('Impressions >= ' + CONFIG.MINIMUM_IMPRESSIONS)
    .forDateRange(CONFIG.DATE_RANGE)
    .orderBy('Impressions DESC')
    .get();

  var count = 0;

  while (ads.hasNext()) {
    if (count % 500 === 0 && count > 0) {
      var elapsed = (new Date() - startTime) / 60000;
      if (elapsed > CONFIG.TIME_LIMIT_MINUTES) break;
    }

    var ad = ads.next();
    var stats = ad.getStatsFor(CONFIG.DATE_RANGE);

    results.ads.push({
      campaign: ad.getCampaign().getName(),
      adGroup: ad.getAdGroup().getName(),
      adId: ad.getId(),
      status: ad.isEnabled() ? 'ENABLED' : 'PAUSED',
      strength: 'UNKNOWN',
      impressions: stats.getImpressions(),
      clicks: stats.getClicks(),
      cost: stats.getCost(),
      conversions: stats.getConversions(),
      convValue: stats.getConversionValue(),
      ctr: stats.getCtr(),
      convRate: stats.getConversionRate()
    });

    results.summary.totalAds++;
    count++;
  }
}

/******************************************************************************
 * PARSE ASSETS
 ******************************************************************************/
function parseAssets(assetsRaw) {
  var assets = [];

  if (!assetsRaw) return assets;

  try {
    if (typeof assetsRaw === 'string') {
      try {
        var parsed = JSON.parse(assetsRaw);
        if (Array.isArray(parsed)) {
          parsed.forEach(function(item) {
            if (item.text) assets.push({ text: item.text, pinnedField: item.pinnedField || null });
          });
        }
      } catch (e) {
        var matches = assetsRaw.match(/text:\s*"([^"]+)"/g);
        if (matches) {
          matches.forEach(function(m) {
            assets.push({ text: m.replace(/text:\s*"/, '').replace(/"$/, ''), pinnedField: null });
          });
        }
      }
    } else if (Array.isArray(assetsRaw)) {
      assetsRaw.forEach(function(item) {
        if (item.text) assets.push({ text: item.text, pinnedField: item.pinnedField || null });
      });
    }
  } catch (e) {
    log('DEBUG', 'Parse error: ' + e.message);
  }

  return assets;
}

/******************************************************************************
 * AGGREGATE ASSETS
 ******************************************************************************/
function aggregateAssets(assetMap, assets, adData) {
  assets.forEach(function(asset) {
    var key = asset.text.toLowerCase().trim();
    if (!assetMap[key]) {
      assetMap[key] = {
        text: asset.text,
        pinnedPosition: asset.pinnedField || 'None',
        adCount: 0,
        impressions: 0,
        clicks: 0,
        cost: 0,
        conversions: 0,
        campaigns: {}
      };
    }
    assetMap[key].adCount++;
    assetMap[key].impressions += adData.impressions;
    assetMap[key].clicks += adData.clicks;
    assetMap[key].cost += adData.cost;
    assetMap[key].conversions += adData.conversions;
    assetMap[key].campaigns[adData.campaign] = true;
  });
}

/******************************************************************************
 * CALCULATE PERFORMANCE SCORE
 ******************************************************************************/
function calculatePerformanceScore(ctr, convRate) {
  // Performance Score = (CTR × 10) + (ConvRate × 50)
  return (ctr * 1000) + (convRate * 5000);
}

/******************************************************************************
 * EXPORT HEADLINES
 ******************************************************************************/
function exportHeadlines(ss, results) {
  var sheet = getOrCreateSheet(ss, '2. Headlines');

  var headers = [
    'Headline', 'Chars', 'Pinned', 'Ads Using', 'Campaigns',
    'Impressions', 'Clicks', 'CTR %', 'Cost', 'Conversions',
    'Conv Rate %', 'CPA', 'Performance Score'
  ];

  var data = [];
  var headlineList = Object.values(results.headlines);

  headlineList.forEach(function(h) {
    var ctr = h.impressions > 0 ? h.clicks / h.impressions : 0;
    var convRate = h.clicks > 0 ? h.conversions / h.clicks : 0;
    var cpa = h.conversions > 0 ? h.cost / h.conversions : null;
    var score = calculatePerformanceScore(ctr, convRate);

    data.push({
      text: h.text,
      chars: h.text.length,
      pinned: h.pinnedPosition,
      adCount: h.adCount,
      campaigns: Object.keys(h.campaigns).length,
      impressions: h.impressions,
      clicks: h.clicks,
      ctr: ctr,
      cost: h.cost,
      conversions: h.conversions,
      convRate: convRate,
      cpa: cpa,
      score: score
    });
  });

  // Sort by Performance Score descending
  data.sort(function(a, b) { return b.score - a.score; });

  var rows = [headers];
  data.forEach(function(d) {
    rows.push([
      d.text, d.chars, d.pinned, d.adCount, d.campaigns,
      d.impressions, d.clicks, (d.ctr * 100).toFixed(2) + '%',
      '$' + d.cost.toFixed(2), d.conversions.toFixed(2),
      (d.convRate * 100).toFixed(2) + '%',
      d.cpa !== null ? '$' + d.cpa.toFixed(2) : 'N/A',
      d.score.toFixed(1)
    ]);
  });

  writeToSheet(sheet, rows);
  log('INFO', 'Exported ' + data.length + ' headlines');
}

/******************************************************************************
 * EXPORT DESCRIPTIONS
 ******************************************************************************/
function exportDescriptions(ss, results) {
  var sheet = getOrCreateSheet(ss, '3. Descriptions');

  var headers = [
    'Description', 'Chars', 'Pinned', 'Ads Using', 'Campaigns',
    'Impressions', 'Clicks', 'CTR %', 'Cost', 'Conversions',
    'Conv Rate %', 'CPA', 'Performance Score'
  ];

  var data = [];
  var descList = Object.values(results.descriptions);

  descList.forEach(function(d) {
    var ctr = d.impressions > 0 ? d.clicks / d.impressions : 0;
    var convRate = d.clicks > 0 ? d.conversions / d.clicks : 0;
    var cpa = d.conversions > 0 ? d.cost / d.conversions : null;
    var score = calculatePerformanceScore(ctr, convRate);

    data.push({
      text: d.text,
      chars: d.text.length,
      pinned: d.pinnedPosition,
      adCount: d.adCount,
      campaigns: Object.keys(d.campaigns).length,
      impressions: d.impressions,
      clicks: d.clicks,
      ctr: ctr,
      cost: d.cost,
      conversions: d.conversions,
      convRate: convRate,
      cpa: cpa,
      score: score
    });
  });

  data.sort(function(a, b) { return b.score - a.score; });

  var rows = [headers];
  data.forEach(function(d) {
    rows.push([
      d.text, d.chars, d.pinned, d.adCount, d.campaigns,
      d.impressions, d.clicks, (d.ctr * 100).toFixed(2) + '%',
      '$' + d.cost.toFixed(2), d.conversions.toFixed(2),
      (d.convRate * 100).toFixed(2) + '%',
      d.cpa !== null ? '$' + d.cpa.toFixed(2) : 'N/A',
      d.score.toFixed(1)
    ]);
  });

  writeToSheet(sheet, rows);
  log('INFO', 'Exported ' + data.length + ' descriptions');
}

/******************************************************************************
 * EXPORT AD PERFORMANCE
 ******************************************************************************/
function exportAdPerformance(ss, results) {
  var sheet = getOrCreateSheet(ss, '4. Ad Performance');

  var headers = [
    'Campaign', 'Ad Group', 'Ad ID', 'Status', 'Ad Strength',
    'Headlines', 'Descriptions', 'Impressions', 'Clicks', 'CTR %',
    'Cost', 'Conversions', 'Conv Rate %', 'CPA', 'Conv Value'
  ];

  var rows = [headers];

  results.ads.forEach(function(ad) {
    rows.push([
      ad.campaign,
      ad.adGroup,
      ad.adId,
      ad.status,
      ad.strength,
      ad.headlines ? ad.headlines.length : 0,
      ad.descriptions ? ad.descriptions.length : 0,
      ad.impressions,
      ad.clicks,
      (ad.ctr * 100).toFixed(2) + '%',
      '$' + ad.cost.toFixed(2),
      ad.conversions.toFixed(2),
      (ad.convRate * 100).toFixed(2) + '%',
      ad.cpa !== null ? '$' + ad.cpa.toFixed(2) : 'N/A',
      '$' + (ad.convValue || 0).toFixed(2)
    ]);
  });

  writeToSheet(sheet, rows);
  log('INFO', 'Exported ' + results.ads.length + ' ads');
}

/******************************************************************************
 * CREATE SUMMARY
 ******************************************************************************/
function createSummary(ss, results) {
  var sheet = getOrCreateSheet(ss, '1. Summary');

  var avgCtr = results.summary.totalImpressions > 0
    ? (results.summary.totalClicks / results.summary.totalImpressions * 100) : 0;
  var avgConvRate = results.summary.totalClicks > 0
    ? (results.summary.totalConversions / results.summary.totalClicks * 100) : 0;

  var data = [
    ['AD COPY PERFORMANCE MATRIX', ''],
    ['Generated by PPC.io Script Engine', ''],
    ['https://ppc.io', ''],
    ['', ''],
    ['Export Date', new Date().toISOString()],
    ['Date Range', CONFIG.DATE_RANGE],
    ['', ''],
    ['TOTALS', ''],
    ['Total Ads Analyzed', results.summary.totalAds],
    ['Unique Headlines', Object.keys(results.headlines).length],
    ['Unique Descriptions', Object.keys(results.descriptions).length],
    ['Total Impressions', results.summary.totalImpressions],
    ['Total Clicks', results.summary.totalClicks],
    ['Total Cost', '$' + results.summary.totalCost.toFixed(2)],
    ['Total Conversions', results.summary.totalConversions.toFixed(2)],
    ['Average CTR', avgCtr.toFixed(2) + '%'],
    ['Average Conv Rate', avgConvRate.toFixed(2) + '%'],
    ['', ''],
    ['PERFORMANCE SCORE FORMULA', ''],
    ['Formula', '(CTR × 1000) + (ConvRate × 5000)'],
    ['Higher score = better performing asset', ''],
    ['', ''],
    ['AI ANALYSIS PROMPTS', ''],
    ['Prompt 1', 'Which headline themes are working? Which should I kill?'],
    ['Prompt 2', 'Find headlines with high impressions but low CTR - suggest replacements'],
    ['Prompt 3', 'Which description patterns drive the most conversions?'],
    ['Prompt 4', 'Identify ads with weak Ad Strength and suggest improvements'],
    ['Prompt 5', 'Generate 5 new headline variations based on top performers']
  ];

  writeToSheet(sheet, data);

  // Move summary to first position
  ss.setActiveSheet(sheet);
  ss.moveActiveSheet(1);
}

/******************************************************************************
 * UTILITY FUNCTIONS
 ******************************************************************************/
function initializeSpreadsheet() {
  if (!CONFIG.SPREADSHEET_URL || CONFIG.SPREADSHEET_URL === 'CREATE_NEW') {
    var name = 'Ad Copy Matrix - ' + AdsApp.currentAccount().getName() + ' - ' + formatDate(new Date());
    var ss = SpreadsheetApp.create(name);
    log('INFO', 'Created: ' + ss.getUrl());
    return ss;
  }
  return SpreadsheetApp.openByUrl(CONFIG.SPREADSHEET_URL);
}

function getOrCreateSheet(ss, name) {
  var sheet = ss.getSheetByName(name);
  if (!sheet) {
    sheet = ss.insertSheet(name);
  } else {
    sheet.clear();
  }
  return sheet;
}

function writeToSheet(sheet, data) {
  if (data.length === 0) return;

  var maxCols = Math.max.apply(null, data.map(function(r) { return r.length; }));
  data = data.map(function(r) {
    while (r.length < maxCols) r.push('');
    return r;
  });

  for (var i = 0; i < data.length; i += CONFIG.BATCH_SIZE) {
    var batch = data.slice(i, Math.min(i + CONFIG.BATCH_SIZE, data.length));
    sheet.getRange(1 + i, 1, batch.length, maxCols).setValues(batch);
  }

  sheet.getRange(1, 1, 1, maxCols).setFontWeight('bold');
  sheet.setFrozenRows(1);
}

function formatDate(date) {
  return Utilities.formatDate(date, AdsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');
}

function log(level, message) {
  var levels = { 'DEBUG': 0, 'INFO': 1, 'WARN': 2, 'ERROR': 3 };
  if (levels[level] >= levels[CONFIG.LOG_LEVEL]) {
    Logger.log('[' + level + '] ' + message);
  }
}

function sendNotifications(url, results, startTime) {
  var duration = ((new Date() - startTime) / 1000).toFixed(1);
  var message = 'Ad Copy Matrix completed in ' + duration + 's\n' +
                'Ads: ' + results.summary.totalAds + '\n' +
                'Headlines: ' + Object.keys(results.headlines).length + '\n' +
                'Descriptions: ' + Object.keys(results.descriptions).length + '\n' +
                'Report: ' + url;

  if (CONFIG.EMAIL_RECIPIENTS.length > 0) {
    MailApp.sendEmail({
      to: CONFIG.EMAIL_RECIPIENTS.join(','),
      subject: '[PPC.io] Ad Copy Matrix - ' + AdsApp.currentAccount().getName(),
      body: message
    });
  }

  if (CONFIG.SLACK_WEBHOOK_URL) {
    UrlFetchApp.fetch(CONFIG.SLACK_WEBHOOK_URL, {
      method: 'post',
      contentType: 'application/json',
      payload: JSON.stringify({ text: message })
    });
  }
}

function sendErrorNotification(error) {
  if (CONFIG.EMAIL_RECIPIENTS.length > 0) {
    MailApp.sendEmail({
      to: CONFIG.EMAIL_RECIPIENTS.join(','),
      subject: '[PPC.io ERROR] Ad Copy Matrix Failed',
      body: 'Error: ' + error.message + '\n\nStack: ' + error.stack
    });
  }
}