Free: 96 PPC tools + my AI Playbook book
Production-ready prompts, scripts, frameworks and AI agents for Google Ads professionals. No payment required.
Performance Max hides asset-group performance by design. I built this because clients kept asking which asset group was actually carrying the campaign and the UI would not tell them. The script pulls what Google buries.
/******************************************************************************
* PMAX INSIGHT ENGINE
*
* Generated by PPC.io Script Engine
* https://ppc.io
*
* Purpose: Complete PMAX transparency - channel splits, brand analysis,
* product tiers, waste identification - all with zero configuration
* Author: PPC.io
* Version: 1.0
* Updated: 2025-01-17
*
* ============================================================================
* SETUP INSTRUCTIONS (Takes 2 minutes - no spreadsheet needed!)
* ============================================================================
*
* STEP 1: Copy this script
* - Select ALL the code (Ctrl+A or Cmd+A)
* - Copy it (Ctrl+C or Cmd+C)
*
* STEP 2: Open Google Ads Scripts
* - Go to your Google Ads account
* - Click "Tools & Settings" (wrench icon) in the top menu
* - Under "Bulk Actions", click "Scripts"
* - Click the big blue "+" button to create a new script
*
* STEP 3: Paste and authorize
* - Delete any existing code in the editor
* - Paste this script (Ctrl+V or Cmd+V)
* - Click "Authorize" when prompted (required for Google Sheets access)
* - Follow the Google sign-in prompts
*
* STEP 4: Run the script
* - Click "Preview" first to test (recommended)
* - If no errors, click "Run"
* - Wait 1-2 minutes for it to complete
*
* STEP 5: View your report
* - Check the "Logs" at the bottom for the spreadsheet URL
* - Or check your Google Drive - look for "PMAX Insight Engine | [Your Account]"
* - The spreadsheet is AUTO-CREATED - you don't need to do anything!
*
* STEP 6 (Optional): Schedule it
* - Click "Save" to save your script
* - Set up a schedule: Weekly on Monday mornings is ideal
* - Add your email to EMAIL_RECIPIENTS below to get notified
*
* ============================================================================
* FAQ - COMMON QUESTIONS
* ============================================================================
*
* Q: Do I need to create a Google Sheet first?
* A: NO! The script automatically creates one for you in Google Drive.
*
* Q: Where does the spreadsheet go?
* A: It's created in the Google Drive of the account that authorized the script.
* Look for "PMAX Insight Engine | [Your Account Name] | [Date]"
*
* Q: Can I use an existing spreadsheet?
* A: Yes! Paste the spreadsheet URL into SPREADSHEET_URL below.
* The script will overwrite the data each time it runs.
*
* Q: What if I get an error?
* A: Most errors are authorization issues. Try:
* 1. Click "Authorize" again
* 2. Make sure you're signed into the correct Google account
* 3. Check that you have PMAX campaigns in this account
*
* Q: How long does it take to run?
* A: Usually 1-3 minutes depending on account size.
*
* Q: Does this make any changes to my account?
* A: NO! This script only READS data. It never changes bids, budgets, or settings.
*
* ============================================================================
* WHAT YOU'LL GET (8 Sheets of PMAX Intelligence)
* ============================================================================
*
* 1. Summary → Dashboard with health score, alerts, and AI prompts
* 2. Channel Split → Where money goes: Shopping vs Search vs Display vs Video
* 3. Brand Analysis → Is PMAX finding new customers or stealing brand traffic?
* 4. Product Tiers → Products ranked: Stars, Solid, Struggling, Zombies
* 5. Asset Groups → Which creative combinations are working?
* 6. Search Terms → Non-converting search categories wasting money
* 7. Placements → Suspicious websites flagged for exclusion
* 8. Trends → Week-over-week changes with automatic alerts
*
* ============================================================================
* CHANGELOG
* ============================================================================
* v1.0 - Initial release - Complete PMAX transparency engine
*
******************************************************************************/
/******************************************************************************
* CONFIGURATION
*
* Most users: Just run it! You don't need to change anything below.
* The script works out of the box with smart defaults.
*
* Only change settings if you have a specific need (see comments).
******************************************************************************/
var CONFIG = {
// ═══════════════════════════════════════════════════════════════════════════
// SPREADSHEET SETTINGS
// ═══════════════════════════════════════════════════════════════════════════
//
// Leave as 'CREATE_NEW' → Script creates a new spreadsheet automatically
// Or paste a URL like: 'https://docs.google.com/spreadsheets/d/abc123/edit'
//
SPREADSHEET_URL: 'CREATE_NEW',
// ═══════════════════════════════════════════════════════════════════════════
// EMAIL NOTIFICATIONS (Optional)
// ═══════════════════════════════════════════════════════════════════════════
//
// Want to get emailed when the script runs? Add your email(s):
// Example: ['you@company.com']
// Example: ['you@company.com', 'boss@company.com']
// Leave empty [] for no emails
//
EMAIL_RECIPIENTS: [],
// Slack webhook URL (leave empty if you don't use Slack)
SLACK_WEBHOOK_URL: '',
// ═══════════════════════════════════════════════════════════════════════════
// DATE RANGE
// ═══════════════════════════════════════════════════════════════════════════
//
// How much data to analyze? Options:
// 'LAST_7_DAYS', 'LAST_14_DAYS', 'LAST_30_DAYS', 'LAST_90_DAYS'
//
DATE_RANGE: 'LAST_30_DAYS',
// For trend comparison (compares this period vs previous period)
COMPARISON_RANGE: 30,
// ═══════════════════════════════════════════════════════════════════════════
// CAMPAIGN FILTERS (Optional - leave empty to analyze ALL PMAX campaigns)
// ═══════════════════════════════════════════════════════════════════════════
//
// Only analyze campaigns containing this text:
// Example: 'Brand' → only campaigns with "Brand" in the name
// Leave empty '' to include all campaigns
//
CAMPAIGN_NAME_CONTAINS: '',
// Exclude campaigns containing this text:
// Example: 'Test' → skip any campaign with "Test" in the name
// Leave empty '' to not exclude anything
//
CAMPAIGN_NAME_EXCLUDES: '',
// ═══════════════════════════════════════════════════════════════════════════
// BRAND TERMS (Usually auto-detected - you can skip this)
// ═══════════════════════════════════════════════════════════════════════════
//
// The script auto-detects your brand name from your account name.
// Only fill this in if auto-detection doesn't work well for you.
//
// Example: ['nike', 'swoosh', 'just do it']
// Leave empty [] for auto-detection
//
BRAND_TERMS: [],
// ═══════════════════════════════════════════════════════════════════════════
// THRESHOLDS (Advanced - most users should leave these alone)
// ═══════════════════════════════════════════════════════════════════════════
// Products with this many clicks and 0 conversions = "Zombie"
ZOMBIE_CLICK_THRESHOLD: 50,
// Search terms with this many clicks and 0 conversions = "High Waste"
WASTE_CLICK_THRESHOLD: 100,
// Alert if brand traffic exceeds this % (0.40 = 40%)
BRAND_CONCERN_THRESHOLD: 0.40,
// Flag placements with CTR above this and 0 conversions (likely MFA sites)
SUSPICIOUS_CTR_THRESHOLD: 0.03,
// Alert if any metric changes more than this % week-over-week
TREND_ALERT_THRESHOLD: 0.15,
// ═══════════════════════════════════════════════════════════════════════════
// TECHNICAL SETTINGS (Don't touch unless you know what you're doing)
// ═══════════════════════════════════════════════════════════════════════════
LOG_LEVEL: 'INFO', // DEBUG shows more detail, ERROR shows less
TIME_LIMIT_MINUTES: 25, // Script stops gracefully before Google's 30min limit
BATCH_SIZE: 500 // Rows written to spreadsheet at once
};
/******************************************************************************
* MAIN EXECUTION
******************************************************************************/
function main() {
var startTime = new Date();
log('INFO', '════════════════════════════════════════════════════════════');
log('INFO', 'PMAX INSIGHT ENGINE started: ' + startTime.toISOString());
log('INFO', '════════════════════════════════════════════════════════════');
try {
// Initialize
var ss = initializeSpreadsheet();
var brandTerms = initializeBrandTerms();
// Collect all data
var data = {
campaigns: [],
assetGroups: [],
channelSplit: [],
brandAnalysis: [],
productTiers: [],
searchTerms: [],
placements: [],
trends: { current: {}, previous: {} },
summary: {
totalCampaigns: 0,
totalSpend: 0,
totalConversions: 0,
totalValue: 0,
channelDistribution: { shopping: 0, search: 0, display: 0, video: 0 },
brandPercent: 0,
productHealth: { stars: 0, solid: 0, struggling: 0, zombies: 0, sleepers: 0 },
wasteIdentified: 0,
alerts: []
}
};
// Step 1: Get PMAX campaigns
log('INFO', 'Step 1/8: Fetching PMAX campaigns...');
data.campaigns = getPMaxCampaigns(startTime);
data.summary.totalCampaigns = data.campaigns.length;
if (data.campaigns.length === 0) {
log('WARN', 'No Performance Max campaigns found in this account');
writeEmptyState(ss);
return;
}
log('INFO', 'Found ' + data.campaigns.length + ' PMAX campaigns');
// Step 2: Get asset groups
log('INFO', 'Step 2/8: Fetching asset groups...');
data.assetGroups = getAssetGroups(startTime);
log('INFO', 'Found ' + data.assetGroups.length + ' asset groups');
checkTimeLimit(startTime);
// Step 3: Calculate channel split
log('INFO', 'Step 3/8: Calculating channel split...');
data.channelSplit = calculateChannelSplit(data.campaigns, startTime);
updateChannelDistribution(data);
checkTimeLimit(startTime);
// Step 4: Brand analysis
log('INFO', 'Step 4/8: Analyzing brand vs non-brand traffic...');
data.brandAnalysis = analyzeBrandTraffic(brandTerms, startTime);
updateBrandMetrics(data);
checkTimeLimit(startTime);
// Step 5: Product tiering
log('INFO', 'Step 5/8: Calculating product tiers...');
data.productTiers = calculateProductTiers(startTime);
updateProductHealth(data);
checkTimeLimit(startTime);
// Step 6: Search term waste
log('INFO', 'Step 6/8: Identifying search term waste...');
data.searchTerms = analyzeSearchTermWaste(startTime);
checkTimeLimit(startTime);
// Step 7: Placement analysis
log('INFO', 'Step 7/8: Analyzing placements...');
data.placements = analyzePlacements(startTime);
checkTimeLimit(startTime);
// Step 8: Trend comparison
log('INFO', 'Step 8/8: Calculating trends...');
data.trends = calculateTrends(data, startTime);
generateAlerts(data);
// Write all sheets
log('INFO', 'Writing output sheets...');
writeAllSheets(ss, data);
// Send notifications
sendNotifications(data, ss.getUrl(), startTime);
// Log completion
logCompletion(data, startTime);
} catch (error) {
handleFatalError(error, startTime);
}
}
/******************************************************************************
* INITIALIZATION FUNCTIONS
******************************************************************************/
function initializeSpreadsheet() {
var ss;
var accountName = AdsApp.currentAccount().getName();
var dateStr = formatDate(new Date());
if (!CONFIG.SPREADSHEET_URL ||
CONFIG.SPREADSHEET_URL === 'CREATE_NEW' ||
CONFIG.SPREADSHEET_URL === 'YOUR_SPREADSHEET_URL_HERE') {
ss = SpreadsheetApp.create('PMAX Insight Engine | ' + accountName + ' | ' + dateStr);
log('INFO', 'Created new spreadsheet: ' + ss.getUrl());
} else {
ss = SpreadsheetApp.openByUrl(CONFIG.SPREADSHEET_URL);
log('INFO', 'Using existing spreadsheet');
}
return ss;
}
function initializeBrandTerms() {
if (CONFIG.BRAND_TERMS && CONFIG.BRAND_TERMS.length > 0) {
log('INFO', 'Using configured brand terms: ' + CONFIG.BRAND_TERMS.join(', '));
return CONFIG.BRAND_TERMS.map(function(t) { return t.toLowerCase(); });
}
// Auto-detect from account name
var accountName = AdsApp.currentAccount().getName().toLowerCase();
var brandTerms = [];
// Extract potential brand terms (words > 2 chars, not common words)
var commonWords = ['the', 'and', 'for', 'ads', 'google', 'account', 'campaign', 'llc', 'inc', 'ltd'];
var words = accountName.replace(/[^a-z0-9\s]/g, '').split(/\s+/);
for (var i = 0; i < words.length; i++) {
if (words[i].length > 2 && commonWords.indexOf(words[i]) === -1) {
brandTerms.push(words[i]);
}
}
log('INFO', 'Auto-detected brand terms: ' + (brandTerms.length > 0 ? brandTerms.join(', ') : '(none)'));
return brandTerms;
}
/******************************************************************************
* DATA COLLECTION: PMAX CAMPAIGNS
******************************************************************************/
function getPMaxCampaigns(startTime) {
var campaigns = [];
var query = 'SELECT ' +
'campaign.id, ' +
'campaign.name, ' +
'campaign.status, ' +
'campaign.bidding_strategy_type, ' +
'campaign_budget.amount_micros, ' +
'metrics.impressions, ' +
'metrics.clicks, ' +
'metrics.cost_micros, ' +
'metrics.conversions, ' +
'metrics.conversions_value, ' +
'metrics.video_views ' +
'FROM campaign ' +
'WHERE campaign.advertising_channel_type = "PERFORMANCE_MAX" ' +
'AND campaign.status != "REMOVED" ' +
'AND segments.date DURING ' + CONFIG.DATE_RANGE;
try {
var rows = AdsApp.search(query);
var campaignMap = {};
while (rows.hasNext()) {
var row = rows.next();
var campaignId = row.campaign.id;
var campaignName = row.campaign.name;
// Apply filters
if (CONFIG.CAMPAIGN_NAME_CONTAINS &&
campaignName.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_CONTAINS.toLowerCase()) === -1) {
continue;
}
if (CONFIG.CAMPAIGN_NAME_EXCLUDES &&
campaignName.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_EXCLUDES.toLowerCase()) !== -1) {
continue;
}
// Aggregate by campaign (GAQL returns daily rows)
if (!campaignMap[campaignId]) {
campaignMap[campaignId] = {
'Campaign ID': campaignId,
'Campaign Name': campaignName,
'Status': row.campaign.status,
'Bidding Strategy': row.campaign.biddingStrategyType || 'N/A',
'Daily Budget': (parseInt(row.campaignBudget.amountMicros, 10) || 0) / 1000000,
'Impressions': 0,
'Clicks': 0,
'Cost': 0,
'Conversions': 0,
'Conv Value': 0,
'Video Views': 0
};
}
campaignMap[campaignId]['Impressions'] += parseInt(row.metrics.impressions, 10) || 0;
campaignMap[campaignId]['Clicks'] += parseInt(row.metrics.clicks, 10) || 0;
campaignMap[campaignId]['Cost'] += (parseInt(row.metrics.costMicros, 10) || 0) / 1000000;
campaignMap[campaignId]['Conversions'] += parseFloat(row.metrics.conversions) || 0;
campaignMap[campaignId]['Conv Value'] += parseFloat(row.metrics.conversionsValue) || 0;
campaignMap[campaignId]['Video Views'] += parseInt(row.metrics.videoViews, 10) || 0;
}
// Convert to array and calculate derived metrics
for (var id in campaignMap) {
var c = campaignMap[id];
c['CTR'] = c['Impressions'] > 0 ? c['Clicks'] / c['Impressions'] : 0;
c['CPC'] = c['Clicks'] > 0 ? c['Cost'] / c['Clicks'] : 0;
c['Conv Rate'] = c['Clicks'] > 0 ? c['Conversions'] / c['Clicks'] : 0;
c['CPA'] = c['Conversions'] > 0 ? c['Cost'] / c['Conversions'] : null;
c['ROAS'] = c['Cost'] > 0 ? c['Conv Value'] / c['Cost'] : null;
campaigns.push(c);
}
// Sort by cost descending
campaigns.sort(function(a, b) { return b['Cost'] - a['Cost']; });
} catch (e) {
log('ERROR', 'Failed to fetch PMAX campaigns: ' + e.message);
campaigns = getPMaxCampaignsFallback(startTime);
}
return campaigns;
}
function getPMaxCampaignsFallback(startTime) {
log('WARN', 'Using fallback method for campaign collection');
var campaigns = [];
var campaignIterator = AdsApp.performanceMaxCampaigns()
.withCondition('Status != REMOVED')
.forDateRange(CONFIG.DATE_RANGE)
.get();
while (campaignIterator.hasNext()) {
var campaign = campaignIterator.next();
var campaignName = campaign.getName();
if (CONFIG.CAMPAIGN_NAME_CONTAINS &&
campaignName.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_CONTAINS.toLowerCase()) === -1) {
continue;
}
if (CONFIG.CAMPAIGN_NAME_EXCLUDES &&
campaignName.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_EXCLUDES.toLowerCase()) !== -1) {
continue;
}
var stats = campaign.getStatsFor(CONFIG.DATE_RANGE);
var cost = stats.getCost();
var conversions = stats.getConversions();
var convValue = stats.getConversionValue();
campaigns.push({
'Campaign ID': campaign.getId(),
'Campaign Name': campaignName,
'Status': campaign.isEnabled() ? 'ENABLED' : 'PAUSED',
'Bidding Strategy': 'N/A',
'Daily Budget': campaign.getBudget().getAmount(),
'Impressions': stats.getImpressions(),
'Clicks': stats.getClicks(),
'Cost': cost,
'Conversions': conversions,
'Conv Value': convValue,
'Video Views': 0,
'CTR': stats.getCtr(),
'CPC': stats.getAverageCpc(),
'Conv Rate': stats.getClicks() > 0 ? conversions / stats.getClicks() : 0,
'CPA': conversions > 0 ? cost / conversions : null,
'ROAS': cost > 0 ? convValue / cost : null
});
}
campaigns.sort(function(a, b) { return b['Cost'] - a['Cost']; });
return campaigns;
}
/******************************************************************************
* DATA COLLECTION: ASSET GROUPS
******************************************************************************/
function getAssetGroups(startTime) {
var assetGroups = [];
var query = 'SELECT ' +
'campaign.name, ' +
'asset_group.id, ' +
'asset_group.name, ' +
'asset_group.status, ' +
'asset_group.primary_status, ' +
'metrics.impressions, ' +
'metrics.clicks, ' +
'metrics.cost_micros, ' +
'metrics.conversions, ' +
'metrics.conversions_value ' +
'FROM asset_group ' +
'WHERE campaign.advertising_channel_type = "PERFORMANCE_MAX" ' +
'AND segments.date DURING ' + CONFIG.DATE_RANGE;
try {
var rows = AdsApp.search(query);
var agMap = {};
while (rows.hasNext()) {
var row = rows.next();
var agId = row.assetGroup.id;
var campaignName = row.campaign.name;
// Apply filters
if (CONFIG.CAMPAIGN_NAME_CONTAINS &&
campaignName.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_CONTAINS.toLowerCase()) === -1) {
continue;
}
if (CONFIG.CAMPAIGN_NAME_EXCLUDES &&
campaignName.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_EXCLUDES.toLowerCase()) !== -1) {
continue;
}
if (!agMap[agId]) {
agMap[agId] = {
'Campaign': campaignName,
'Asset Group ID': agId,
'Asset Group': row.assetGroup.name,
'Status': row.assetGroup.status,
'Primary Status': row.assetGroup.primaryStatus || 'N/A',
'Impressions': 0,
'Clicks': 0,
'Cost': 0,
'Conversions': 0,
'Conv Value': 0
};
}
agMap[agId]['Impressions'] += parseInt(row.metrics.impressions, 10) || 0;
agMap[agId]['Clicks'] += parseInt(row.metrics.clicks, 10) || 0;
agMap[agId]['Cost'] += (parseInt(row.metrics.costMicros, 10) || 0) / 1000000;
agMap[agId]['Conversions'] += parseFloat(row.metrics.conversions) || 0;
agMap[agId]['Conv Value'] += parseFloat(row.metrics.conversionsValue) || 0;
checkTimeLimit(startTime);
}
// Calculate metrics and efficiency scores
var allCosts = [];
var allConvRates = [];
for (var id in agMap) {
var ag = agMap[id];
ag['CTR'] = ag['Impressions'] > 0 ? ag['Clicks'] / ag['Impressions'] : 0;
ag['Conv Rate'] = ag['Clicks'] > 0 ? ag['Conversions'] / ag['Clicks'] : 0;
ag['CPA'] = ag['Conversions'] > 0 ? ag['Cost'] / ag['Conversions'] : null;
ag['ROAS'] = ag['Cost'] > 0 ? ag['Conv Value'] / ag['Cost'] : null;
// Efficiency = Conversions per $1000 spent
ag['Efficiency Score'] = ag['Cost'] > 0 ? (ag['Conversions'] / ag['Cost']) * 1000 : 0;
if (ag['Cost'] > 0) allCosts.push(ag['Cost']);
if (ag['Conv Rate'] > 0) allConvRates.push(ag['Conv Rate']);
assetGroups.push(ag);
}
// Calculate vs account average
var avgEfficiency = 0;
var totalConv = 0;
var totalCost = 0;
for (var i = 0; i < assetGroups.length; i++) {
totalConv += assetGroups[i]['Conversions'];
totalCost += assetGroups[i]['Cost'];
}
avgEfficiency = totalCost > 0 ? (totalConv / totalCost) * 1000 : 0;
// Add comparison and recommendations
for (var j = 0; j < assetGroups.length; j++) {
var ag = assetGroups[j];
ag['vs Account Avg'] = avgEfficiency > 0 ?
((ag['Efficiency Score'] - avgEfficiency) / avgEfficiency * 100).toFixed(1) + '%' : 'N/A';
// Generate recommendation
if (ag['Efficiency Score'] > avgEfficiency * 1.3) {
ag['Recommendation'] = '⭐ Top performer - analyze assets for patterns to replicate';
} else if (ag['Efficiency Score'] >= avgEfficiency * 0.8) {
ag['Recommendation'] = '✓ Solid performer - maintain current strategy';
} else if (ag['Efficiency Score'] > 0) {
ag['Recommendation'] = '⚠️ Below average - consider refreshing creative';
} else if (ag['Impressions'] > 1000) {
ag['Recommendation'] = '🔴 High traffic, no conversions - investigate targeting';
} else {
ag['Recommendation'] = '💤 Low activity - may need more time or budget';
}
}
// Sort by efficiency
assetGroups.sort(function(a, b) { return b['Efficiency Score'] - a['Efficiency Score']; });
} catch (e) {
log('ERROR', 'Failed to fetch asset groups: ' + e.message);
}
return assetGroups;
}
/******************************************************************************
* CHANNEL SPLIT CALCULATION
******************************************************************************/
function calculateChannelSplit(campaigns, startTime) {
var channelData = [];
// Get listing group data for Shopping spend
var shoppingSpendByCampaign = getShoppingSpend(startTime);
// Get video data for estimated video spend
var videoDataByCampaign = getVideoSpendEstimate(startTime);
for (var i = 0; i < campaigns.length; i++) {
var campaign = campaigns[i];
var campaignName = campaign['Campaign Name'];
var totalCost = campaign['Cost'];
if (totalCost === 0) continue;
// Shopping spend from listing groups
var shoppingSpend = shoppingSpendByCampaign[campaignName] || 0;
var shoppingPercent = totalCost > 0 ? (shoppingSpend / totalCost) : 0;
// Video spend estimate (video views × estimated CPV)
var videoData = videoDataByCampaign[campaignName] || { spend: 0, views: 0 };
var videoSpend = videoData.spend;
var videoPercent = totalCost > 0 ? (videoSpend / totalCost) : 0;
// Remaining is Display + Search
var remainingSpend = totalCost - shoppingSpend - videoSpend;
remainingSpend = Math.max(0, remainingSpend);
// Estimate Search vs Display split based on conversion patterns
// Higher conversion rate typically indicates more Search traffic
var convRate = campaign['Conv Rate'] || 0;
var searchRatio = Math.min(0.7, Math.max(0.2, convRate * 10)); // 20-70% based on conv rate
var searchSpend = remainingSpend * searchRatio;
var displaySpend = remainingSpend * (1 - searchRatio);
var searchPercent = totalCost > 0 ? (searchSpend / totalCost) : 0;
var displayPercent = totalCost > 0 ? (displaySpend / totalCost) : 0;
channelData.push({
'Campaign': campaignName,
'Total Spend': totalCost,
'Shopping $': shoppingSpend,
'Shopping %': shoppingPercent,
'Search $ (Est)': searchSpend,
'Search % (Est)': searchPercent,
'Display $ (Est)': displaySpend,
'Display % (Est)': displayPercent,
'Video $ (Est)': videoSpend,
'Video % (Est)': videoPercent,
'Video Views': videoData.views,
'ROAS': campaign['ROAS'],
'Conversions': campaign['Conversions']
});
}
return channelData;
}
function getShoppingSpend(startTime) {
var shoppingSpend = {};
var query = 'SELECT ' +
'campaign.name, ' +
'metrics.cost_micros ' +
'FROM asset_group_listing_group_filter ' +
'WHERE campaign.advertising_channel_type = "PERFORMANCE_MAX" ' +
'AND asset_group_listing_group_filter.type != "SUBDIVISION" ' +
'AND segments.date DURING ' + CONFIG.DATE_RANGE;
try {
var rows = AdsApp.search(query);
while (rows.hasNext()) {
var row = rows.next();
var campaignName = row.campaign.name;
var cost = (parseInt(row.metrics.costMicros, 10) || 0) / 1000000;
if (!shoppingSpend[campaignName]) {
shoppingSpend[campaignName] = 0;
}
shoppingSpend[campaignName] += cost;
checkTimeLimit(startTime);
}
} catch (e) {
log('DEBUG', 'Listing group query not available: ' + e.message);
}
return shoppingSpend;
}
function getVideoSpendEstimate(startTime) {
var videoData = {};
// Get video views and estimate spend using account's average CPV
var query = 'SELECT ' +
'campaign.name, ' +
'metrics.video_views, ' +
'metrics.cost_micros ' +
'FROM campaign ' +
'WHERE campaign.advertising_channel_type = "PERFORMANCE_MAX" ' +
'AND metrics.video_views > 0 ' +
'AND segments.date DURING ' + CONFIG.DATE_RANGE;
try {
var rows = AdsApp.search(query);
var totalViews = 0;
var totalCost = 0;
// First pass: calculate average CPV
var tempData = [];
while (rows.hasNext()) {
var row = rows.next();
var views = parseInt(row.metrics.videoViews, 10) || 0;
var cost = (parseInt(row.metrics.costMicros, 10) || 0) / 1000000;
totalViews += views;
totalCost += cost;
tempData.push({ name: row.campaign.name, views: views });
}
// Estimate CPV (use industry average if no data)
var estimatedCPV = totalViews > 0 ? (totalCost * 0.1) / totalViews : 0.03; // Assume ~10% of spend is video
estimatedCPV = Math.min(0.10, Math.max(0.01, estimatedCPV)); // Clamp between $0.01-$0.10
// Second pass: calculate video spend per campaign
for (var i = 0; i < tempData.length; i++) {
var data = tempData[i];
videoData[data.name] = {
views: data.views,
spend: data.views * estimatedCPV
};
}
} catch (e) {
log('DEBUG', 'Video data query issue: ' + e.message);
}
return videoData;
}
function updateChannelDistribution(data) {
var totals = { shopping: 0, search: 0, display: 0, video: 0, total: 0 };
for (var i = 0; i < data.channelSplit.length; i++) {
var row = data.channelSplit[i];
totals.shopping += row['Shopping $'] || 0;
totals.search += row['Search $ (Est)'] || 0;
totals.display += row['Display $ (Est)'] || 0;
totals.video += row['Video $ (Est)'] || 0;
totals.total += row['Total Spend'] || 0;
}
if (totals.total > 0) {
data.summary.channelDistribution = {
shopping: totals.shopping / totals.total,
search: totals.search / totals.total,
display: totals.display / totals.total,
video: totals.video / totals.total
};
}
data.summary.totalSpend = totals.total;
}
/******************************************************************************
* BRAND ANALYSIS
******************************************************************************/
function analyzeBrandTraffic(brandTerms, startTime) {
var brandData = [];
if (brandTerms.length === 0) {
log('WARN', 'No brand terms configured - brand analysis will be limited');
}
var query = 'SELECT ' +
'campaign.name, ' +
'campaign_search_term_insight.category_label, ' +
'metrics.impressions, ' +
'metrics.clicks, ' +
'metrics.conversions, ' +
'metrics.conversions_value ' +
'FROM campaign_search_term_insight ' +
'WHERE campaign.advertising_channel_type = "PERFORMANCE_MAX" ' +
'AND segments.date DURING ' + CONFIG.DATE_RANGE;
try {
var rows = AdsApp.search(query);
var campaignData = {};
while (rows.hasNext()) {
var row = rows.next();
var campaignName = row.campaign.name;
var categoryLabel = (row.campaignSearchTermInsight.categoryLabel || '').toLowerCase();
// Apply filters
if (CONFIG.CAMPAIGN_NAME_CONTAINS &&
campaignName.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_CONTAINS.toLowerCase()) === -1) {
continue;
}
if (CONFIG.CAMPAIGN_NAME_EXCLUDES &&
campaignName.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_EXCLUDES.toLowerCase()) !== -1) {
continue;
}
if (!campaignData[campaignName]) {
campaignData[campaignName] = {
brand: { clicks: 0, conversions: 0, value: 0 },
nonBrand: { clicks: 0, conversions: 0, value: 0 },
unknown: { clicks: 0, conversions: 0, value: 0 }
};
}
var clicks = parseInt(row.metrics.clicks, 10) || 0;
var conversions = parseFloat(row.metrics.conversions) || 0;
var value = parseFloat(row.metrics.conversionsValue) || 0;
// Classify as brand, non-brand, or unknown
var isBrand = false;
for (var i = 0; i < brandTerms.length; i++) {
if (categoryLabel.indexOf(brandTerms[i]) !== -1) {
isBrand = true;
break;
}
}
var bucket = isBrand ? 'brand' : (categoryLabel ? 'nonBrand' : 'unknown');
campaignData[campaignName][bucket].clicks += clicks;
campaignData[campaignName][bucket].conversions += conversions;
campaignData[campaignName][bucket].value += value;
checkTimeLimit(startTime);
}
// Convert to output format
for (var campaign in campaignData) {
var d = campaignData[campaign];
var totalClicks = d.brand.clicks + d.nonBrand.clicks + d.unknown.clicks;
var totalConv = d.brand.conversions + d.nonBrand.conversions + d.unknown.conversions;
var totalValue = d.brand.value + d.nonBrand.value + d.unknown.value;
brandData.push({
'Campaign': campaign,
'Total Clicks': totalClicks,
'Brand Clicks': d.brand.clicks,
'Brand %': totalClicks > 0 ? d.brand.clicks / totalClicks : 0,
'Non-Brand Clicks': d.nonBrand.clicks,
'Non-Brand %': totalClicks > 0 ? d.nonBrand.clicks / totalClicks : 0,
'Unknown Clicks': d.unknown.clicks,
'Unknown %': totalClicks > 0 ? d.unknown.clicks / totalClicks : 0,
'Brand Conversions': d.brand.conversions,
'Non-Brand Conversions': d.nonBrand.conversions,
'Brand ROAS': d.brand.conversions > 0 ? d.brand.value / (d.brand.clicks * 0.5) : 0, // Estimate
'Non-Brand ROAS': d.nonBrand.conversions > 0 ? d.nonBrand.value / (d.nonBrand.clicks * 0.5) : 0,
'Incrementality Score': totalConv > 0 ? (d.nonBrand.conversions / totalConv) * 100 : 0
});
}
// Sort by brand % descending (highest concern first)
brandData.sort(function(a, b) { return b['Brand %'] - a['Brand %']; });
} catch (e) {
log('WARN', 'Search term insight query not available: ' + e.message);
// Create placeholder data from campaigns
for (var j = 0; j < data.campaigns.length; j++) {
brandData.push({
'Campaign': data.campaigns[j]['Campaign Name'],
'Total Clicks': data.campaigns[j]['Clicks'],
'Brand Clicks': 'N/A',
'Brand %': 'N/A',
'Non-Brand Clicks': 'N/A',
'Non-Brand %': 'N/A',
'Unknown Clicks': 'N/A',
'Unknown %': 'N/A',
'Brand Conversions': 'N/A',
'Non-Brand Conversions': 'N/A',
'Brand ROAS': 'N/A',
'Non-Brand ROAS': 'N/A',
'Incrementality Score': 'Data not available'
});
}
}
return brandData;
}
function updateBrandMetrics(data) {
var totalBrand = 0;
var totalClicks = 0;
for (var i = 0; i < data.brandAnalysis.length; i++) {
var row = data.brandAnalysis[i];
if (typeof row['Brand Clicks'] === 'number') {
totalBrand += row['Brand Clicks'];
totalClicks += row['Total Clicks'];
}
}
data.summary.brandPercent = totalClicks > 0 ? totalBrand / totalClicks : 0;
}
/******************************************************************************
* PRODUCT TIERING (Relative Performance - No COGS Needed)
******************************************************************************/
function calculateProductTiers(startTime) {
var products = [];
var query = 'SELECT ' +
'campaign.name, ' +
'asset_group.name, ' +
'asset_group_listing_group_filter.case_value.product_item_id.value, ' +
'metrics.impressions, ' +
'metrics.clicks, ' +
'metrics.cost_micros, ' +
'metrics.conversions, ' +
'metrics.conversions_value ' +
'FROM asset_group_listing_group_filter ' +
'WHERE campaign.advertising_channel_type = "PERFORMANCE_MAX" ' +
'AND asset_group_listing_group_filter.type = "UNIT_INCLUDED" ' +
'AND segments.date DURING ' + CONFIG.DATE_RANGE;
try {
var rows = AdsApp.search(query);
var productMap = {};
while (rows.hasNext()) {
var row = rows.next();
var productId = row.assetGroupListingGroupFilter.caseValue.productItemId.value;
if (!productId) continue;
var campaignName = row.campaign.name;
// Apply filters
if (CONFIG.CAMPAIGN_NAME_CONTAINS &&
campaignName.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_CONTAINS.toLowerCase()) === -1) {
continue;
}
if (CONFIG.CAMPAIGN_NAME_EXCLUDES &&
campaignName.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_EXCLUDES.toLowerCase()) !== -1) {
continue;
}
if (!productMap[productId]) {
productMap[productId] = {
'Product ID': productId,
'Campaign': campaignName,
'Asset Group': row.assetGroup.name,
'Impressions': 0,
'Clicks': 0,
'Cost': 0,
'Conversions': 0,
'Conv Value': 0
};
}
productMap[productId]['Impressions'] += parseInt(row.metrics.impressions, 10) || 0;
productMap[productId]['Clicks'] += parseInt(row.metrics.clicks, 10) || 0;
productMap[productId]['Cost'] += (parseInt(row.metrics.costMicros, 10) || 0) / 1000000;
productMap[productId]['Conversions'] += parseFloat(row.metrics.conversions) || 0;
productMap[productId]['Conv Value'] += parseFloat(row.metrics.conversionsValue) || 0;
checkTimeLimit(startTime);
}
// Convert to array and calculate metrics
var allROAS = [];
var allConvRates = [];
for (var id in productMap) {
var p = productMap[id];
p['CTR'] = p['Impressions'] > 0 ? p['Clicks'] / p['Impressions'] : 0;
p['Conv Rate'] = p['Clicks'] > 0 ? p['Conversions'] / p['Clicks'] : 0;
p['ROAS'] = p['Cost'] > 0 ? p['Conv Value'] / p['Cost'] : 0;
if (p['ROAS'] > 0) allROAS.push(p['ROAS']);
if (p['Conv Rate'] > 0) allConvRates.push(p['Conv Rate']);
products.push(p);
}
// Calculate medians for relative comparison
var medianROAS = calculateMedian(allROAS);
var medianConvRate = calculateMedian(allConvRates);
log('DEBUG', 'Median ROAS: ' + medianROAS.toFixed(2) + ', Median Conv Rate: ' + (medianConvRate * 100).toFixed(2) + '%');
// Assign tiers based on relative performance
for (var j = 0; j < products.length; j++) {
var product = products[j];
// Calculate ROAS Index (100 = median, 150 = 50% above median)
product['ROAS Index'] = medianROAS > 0 ? (product['ROAS'] / medianROAS) * 100 : 0;
product['Conv Rate Index'] = medianConvRate > 0 ? (product['Conv Rate'] / medianConvRate) * 100 : 0;
// Tier assignment
if (product['ROAS Index'] > 150 && product['Conversions'] >= 5) {
product['Tier'] = '⭐ STAR';
product['Action'] = 'Scale - top performer, increase visibility';
} else if (product['ROAS Index'] >= 80 && product['Conversions'] >= 2) {
product['Tier'] = '✓ SOLID';
product['Action'] = 'Maintain - performing at or above average';
} else if (product['ROAS Index'] < 80 && product['Conversions'] >= 2) {
product['Tier'] = '⚠️ STRUGGLING';
product['Action'] = 'Investigate - below median ROAS';
} else if (product['Clicks'] >= CONFIG.ZOMBIE_CLICK_THRESHOLD && product['Conversions'] === 0) {
product['Tier'] = '💀 ZOMBIE';
product['Action'] = 'Review - ' + product['Clicks'] + ' clicks, 0 conversions';
} else if (product['Impressions'] < 100) {
product['Tier'] = '💤 SLEEPER';
product['Action'] = 'Activate - insufficient visibility';
} else {
product['Tier'] = '📊 LEARNING';
product['Action'] = 'Monitor - gathering data';
}
}
// Sort: Zombies first (most actionable), then by cost
products.sort(function(a, b) {
var tierOrder = { '💀 ZOMBIE': 0, '⚠️ STRUGGLING': 1, '📊 LEARNING': 2, '💤 SLEEPER': 3, '✓ SOLID': 4, '⭐ STAR': 5 };
var orderA = tierOrder[a['Tier']] !== undefined ? tierOrder[a['Tier']] : 99;
var orderB = tierOrder[b['Tier']] !== undefined ? tierOrder[b['Tier']] : 99;
if (orderA !== orderB) return orderA - orderB;
return b['Cost'] - a['Cost'];
});
} catch (e) {
log('WARN', 'Product listing query not available (may not be ecommerce): ' + e.message);
}
return products;
}
function calculateMedian(arr) {
if (arr.length === 0) return 0;
var sorted = arr.slice().sort(function(a, b) { return a - b; });
var mid = Math.floor(sorted.length / 2);
return sorted.length % 2 !== 0 ? sorted[mid] : (sorted[mid - 1] + sorted[mid]) / 2;
}
function updateProductHealth(data) {
var health = { stars: 0, solid: 0, struggling: 0, zombies: 0, sleepers: 0 };
for (var i = 0; i < data.productTiers.length; i++) {
var tier = data.productTiers[i]['Tier'];
if (tier.indexOf('STAR') !== -1) health.stars++;
else if (tier.indexOf('SOLID') !== -1) health.solid++;
else if (tier.indexOf('STRUGGLING') !== -1) health.struggling++;
else if (tier.indexOf('ZOMBIE') !== -1) health.zombies++;
else if (tier.indexOf('SLEEPER') !== -1) health.sleepers++;
}
data.summary.productHealth = health;
}
/******************************************************************************
* SEARCH TERM WASTE ANALYSIS
******************************************************************************/
function analyzeSearchTermWaste(startTime) {
var searchTerms = [];
var query = 'SELECT ' +
'campaign.name, ' +
'campaign_search_term_insight.category_label, ' +
'metrics.impressions, ' +
'metrics.clicks, ' +
'metrics.conversions, ' +
'metrics.conversions_value ' +
'FROM campaign_search_term_insight ' +
'WHERE campaign.advertising_channel_type = "PERFORMANCE_MAX" ' +
'AND segments.date DURING ' + CONFIG.DATE_RANGE;
try {
var rows = AdsApp.search(query);
while (rows.hasNext()) {
var row = rows.next();
var campaignName = row.campaign.name;
var category = row.campaignSearchTermInsight.categoryLabel || '(not set)';
// Apply filters
if (CONFIG.CAMPAIGN_NAME_CONTAINS &&
campaignName.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_CONTAINS.toLowerCase()) === -1) {
continue;
}
if (CONFIG.CAMPAIGN_NAME_EXCLUDES &&
campaignName.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_EXCLUDES.toLowerCase()) !== -1) {
continue;
}
var clicks = parseInt(row.metrics.clicks, 10) || 0;
var conversions = parseFloat(row.metrics.conversions) || 0;
var impressions = parseInt(row.metrics.impressions, 10) || 0;
var value = parseFloat(row.metrics.conversionsValue) || 0;
// Estimate cost (no direct cost in search term insight)
var estimatedCPC = 2.00; // Conservative estimate
var estimatedCost = clicks * estimatedCPC;
// Flag determination
var flag = '';
var action = '';
if (clicks >= CONFIG.WASTE_CLICK_THRESHOLD && conversions === 0) {
flag = '🔴 HIGH WASTE';
action = 'Add to account-level negatives';
} else if (clicks >= 50 && conversions === 0) {
flag = '🟡 WATCH';
action = 'Monitor - may need negative';
} else if (clicks > 0 && conversions > 0) {
var convRate = conversions / clicks;
if (convRate < 0.005) { // <0.5% conversion rate
flag = '🟡 LOW INTENT';
action = 'Low conversion rate - evaluate relevance';
} else {
flag = '🟢 CONVERTING';
action = 'Consider adding to Search campaigns';
}
} else {
flag = '⚪ LOW VOLUME';
action = 'Insufficient data';
}
searchTerms.push({
'Campaign': campaignName,
'Search Category': category,
'Impressions': impressions,
'Clicks': clicks,
'Est. Cost': estimatedCost,
'Conversions': conversions,
'Conv Value': value,
'Conv Rate': clicks > 0 ? conversions / clicks : 0,
'Flag': flag,
'Suggested Action': action
});
checkTimeLimit(startTime);
}
// Sort by estimated waste (high waste first)
searchTerms.sort(function(a, b) {
var flagOrder = { '🔴 HIGH WASTE': 0, '🟡 WATCH': 1, '🟡 LOW INTENT': 2, '🟢 CONVERTING': 3, '⚪ LOW VOLUME': 4 };
var orderA = flagOrder[a['Flag']] !== undefined ? flagOrder[a['Flag']] : 99;
var orderB = flagOrder[b['Flag']] !== undefined ? flagOrder[b['Flag']] : 99;
if (orderA !== orderB) return orderA - orderB;
return b['Est. Cost'] - a['Est. Cost'];
});
} catch (e) {
log('WARN', 'Search term insight not available: ' + e.message);
}
return searchTerms;
}
/******************************************************************************
* PLACEMENT ANALYSIS
******************************************************************************/
function analyzePlacements(startTime) {
var placements = [];
var query = 'SELECT ' +
'campaign.name, ' +
'group_placement_view.placement, ' +
'group_placement_view.placement_type, ' +
'group_placement_view.target_url, ' +
'metrics.impressions, ' +
'metrics.clicks, ' +
'metrics.cost_micros, ' +
'metrics.conversions ' +
'FROM group_placement_view ' +
'WHERE campaign.advertising_channel_type = "PERFORMANCE_MAX" ' +
'AND segments.date DURING ' + CONFIG.DATE_RANGE + ' ' +
'AND metrics.impressions > 0 ' +
'ORDER BY metrics.cost_micros DESC ' +
'LIMIT 500';
// Suspicious patterns to flag
var suspiciousPatterns = [
'game', 'games', 'gaming', 'play', 'unblocked', 'cheat', 'hack',
'free', 'download', 'crack', 'mod', 'apk', 'torrent',
'manga', 'anime', 'cartoon', 'kids', 'child',
'casino', 'bet', 'gambling', 'slot', 'poker',
'adult', 'xxx', 'porn', 'nude', 'dating'
];
try {
var rows = AdsApp.search(query);
while (rows.hasNext()) {
var row = rows.next();
var campaignName = row.campaign.name;
var placement = row.groupPlacementView.placement || '';
var placementType = row.groupPlacementView.placementType || '';
var targetUrl = row.groupPlacementView.targetUrl || placement;
// Apply filters
if (CONFIG.CAMPAIGN_NAME_CONTAINS &&
campaignName.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_CONTAINS.toLowerCase()) === -1) {
continue;
}
if (CONFIG.CAMPAIGN_NAME_EXCLUDES &&
campaignName.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_EXCLUDES.toLowerCase()) !== -1) {
continue;
}
var impressions = parseInt(row.metrics.impressions, 10) || 0;
var clicks = parseInt(row.metrics.clicks, 10) || 0;
var cost = (parseInt(row.metrics.costMicros, 10) || 0) / 1000000;
var conversions = parseFloat(row.metrics.conversions) || 0;
var ctr = impressions > 0 ? clicks / impressions : 0;
var convRate = clicks > 0 ? conversions / clicks : 0;
// Quality flag determination
var flag = '';
var reason = '';
var placementLower = placement.toLowerCase();
// Check for suspicious patterns
var isSuspicious = false;
for (var i = 0; i < suspiciousPatterns.length; i++) {
if (placementLower.indexOf(suspiciousPatterns[i]) !== -1) {
isSuspicious = true;
reason = 'Contains "' + suspiciousPatterns[i] + '"';
break;
}
}
if (isSuspicious && conversions === 0 && cost > 1) {
flag = '🔴 SUSPICIOUS';
} else if (ctr > CONFIG.SUSPICIOUS_CTR_THRESHOLD && conversions === 0 && clicks > 10) {
flag = '🔴 MFA LIKELY';
reason = 'High CTR (' + (ctr * 100).toFixed(1) + '%), 0 conversions';
} else if (cost > 50 && conversions === 0) {
flag = '🟡 INVESTIGATE';
reason = '$' + cost.toFixed(2) + ' spent, 0 conversions';
} else if (conversions > 0) {
flag = '🟢 QUALITY';
reason = 'Converting placement';
} else {
flag = '⚪ UNKNOWN';
reason = 'Insufficient data';
}
placements.push({
'Campaign': campaignName,
'Placement': placement,
'Type': placementType,
'URL': targetUrl,
'Impressions': impressions,
'Clicks': clicks,
'Cost': cost,
'CTR': ctr,
'Conversions': conversions,
'Conv Rate': convRate,
'Quality Flag': flag,
'Reason': reason
});
checkTimeLimit(startTime);
}
// Sort by flag severity then cost
placements.sort(function(a, b) {
var flagOrder = { '🔴 SUSPICIOUS': 0, '🔴 MFA LIKELY': 1, '🟡 INVESTIGATE': 2, '⚪ UNKNOWN': 3, '🟢 QUALITY': 4 };
var orderA = flagOrder[a['Quality Flag']] !== undefined ? flagOrder[a['Quality Flag']] : 99;
var orderB = flagOrder[b['Quality Flag']] !== undefined ? flagOrder[b['Quality Flag']] : 99;
if (orderA !== orderB) return orderA - orderB;
return b['Cost'] - a['Cost'];
});
} catch (e) {
log('WARN', 'Placement view not available: ' + e.message);
}
return placements;
}
/******************************************************************************
* TREND CALCULATION
******************************************************************************/
function calculateTrends(data, startTime) {
var trends = [];
// Get previous period data
var previousData = getPreviousPeriodData(startTime);
// Calculate current period totals
var current = {
spend: 0,
conversions: 0,
value: 0,
clicks: 0,
impressions: 0
};
for (var i = 0; i < data.campaigns.length; i++) {
var c = data.campaigns[i];
current.spend += c['Cost'] || 0;
current.conversions += c['Conversions'] || 0;
current.value += c['Conv Value'] || 0;
current.clicks += c['Clicks'] || 0;
current.impressions += c['Impressions'] || 0;
}
current.roas = current.spend > 0 ? current.value / current.spend : 0;
current.cpa = current.conversions > 0 ? current.spend / current.conversions : 0;
current.ctr = current.impressions > 0 ? current.clicks / current.impressions : 0;
current.convRate = current.clicks > 0 ? current.conversions / current.clicks : 0;
// Calculate previous period metrics
var previous = previousData;
previous.roas = previous.spend > 0 ? previous.value / previous.spend : 0;
previous.cpa = previous.conversions > 0 ? previous.spend / previous.conversions : 0;
previous.ctr = previous.impressions > 0 ? previous.clicks / previous.impressions : 0;
previous.convRate = previous.clicks > 0 ? previous.conversions / previous.clicks : 0;
// Build trend comparisons
var metrics = [
{ name: 'Total Spend', current: current.spend, previous: previous.spend, format: 'currency', higherIsBetter: null },
{ name: 'Conversions', current: current.conversions, previous: previous.conversions, format: 'number', higherIsBetter: true },
{ name: 'Conv Value', current: current.value, previous: previous.value, format: 'currency', higherIsBetter: true },
{ name: 'ROAS', current: current.roas, previous: previous.roas, format: 'decimal', higherIsBetter: true },
{ name: 'CPA', current: current.cpa, previous: previous.cpa, format: 'currency', higherIsBetter: false },
{ name: 'CTR', current: current.ctr, previous: previous.ctr, format: 'percent', higherIsBetter: true },
{ name: 'Conv Rate', current: current.convRate, previous: previous.convRate, format: 'percent', higherIsBetter: true },
{ name: 'Clicks', current: current.clicks, previous: previous.clicks, format: 'number', higherIsBetter: null },
{ name: 'Impressions', current: current.impressions, previous: previous.impressions, format: 'number', higherIsBetter: null },
{ name: 'Brand %', current: data.summary.brandPercent, previous: previous.brandPercent || 0, format: 'percent', higherIsBetter: false },
{ name: 'Shopping %', current: data.summary.channelDistribution.shopping, previous: previous.shoppingPercent || 0, format: 'percent', higherIsBetter: true }
];
for (var j = 0; j < metrics.length; j++) {
var m = metrics[j];
var change = m.current - m.previous;
var changePercent = m.previous > 0 ? (change / m.previous) : (m.current > 0 ? 1 : 0);
var trend = '';
if (changePercent > 0.05) trend = '↑';
else if (changePercent < -0.05) trend = '↓';
else trend = '→';
var alert = '';
if (Math.abs(changePercent) > CONFIG.TREND_ALERT_THRESHOLD) {
if (m.higherIsBetter === true && changePercent < 0) alert = '⚠️';
else if (m.higherIsBetter === false && changePercent > 0) alert = '⚠️';
else if (m.higherIsBetter === null && Math.abs(changePercent) > 0.25) alert = '⚠️';
}
trends.push({
'Metric': m.name,
'This Period': formatMetricValue(m.current, m.format),
'Previous Period': formatMetricValue(m.previous, m.format),
'Change': formatMetricValue(change, m.format),
'Change %': (changePercent * 100).toFixed(1) + '%',
'Trend': trend,
'Alert': alert
});
}
return trends;
}
function getPreviousPeriodData(startTime) {
var data = {
spend: 0,
conversions: 0,
value: 0,
clicks: 0,
impressions: 0,
brandPercent: 0,
shoppingPercent: 0
};
// Calculate previous period dates
var endDate = new Date();
endDate.setDate(endDate.getDate() - CONFIG.COMPARISON_RANGE);
var startDate = new Date(endDate);
startDate.setDate(startDate.getDate() - CONFIG.COMPARISON_RANGE);
var dateRange = formatDate(startDate) + ',' + formatDate(endDate);
var query = 'SELECT ' +
'campaign.name, ' +
'metrics.impressions, ' +
'metrics.clicks, ' +
'metrics.cost_micros, ' +
'metrics.conversions, ' +
'metrics.conversions_value ' +
'FROM campaign ' +
'WHERE campaign.advertising_channel_type = "PERFORMANCE_MAX" ' +
'AND campaign.status != "REMOVED" ' +
'AND segments.date BETWEEN "' + formatDate(startDate) + '" AND "' + formatDate(endDate) + '"';
try {
var rows = AdsApp.search(query);
while (rows.hasNext()) {
var row = rows.next();
var campaignName = row.campaign.name;
// Apply filters
if (CONFIG.CAMPAIGN_NAME_CONTAINS &&
campaignName.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_CONTAINS.toLowerCase()) === -1) {
continue;
}
if (CONFIG.CAMPAIGN_NAME_EXCLUDES &&
campaignName.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_EXCLUDES.toLowerCase()) !== -1) {
continue;
}
data.impressions += parseInt(row.metrics.impressions, 10) || 0;
data.clicks += parseInt(row.metrics.clicks, 10) || 0;
data.spend += (parseInt(row.metrics.costMicros, 10) || 0) / 1000000;
data.conversions += parseFloat(row.metrics.conversions) || 0;
data.value += parseFloat(row.metrics.conversionsValue) || 0;
}
} catch (e) {
log('DEBUG', 'Previous period query issue: ' + e.message);
}
return data;
}
function formatMetricValue(value, format) {
if (value === null || value === undefined || isNaN(value)) return 'N/A';
switch (format) {
case 'currency':
return '$' + value.toFixed(2).replace(/\B(?=(\d{3})+(?!\d))/g, ',');
case 'percent':
return (value * 100).toFixed(1) + '%';
case 'decimal':
return value.toFixed(2);
case 'number':
return Math.round(value).toString().replace(/\B(?=(\d{3})+(?!\d))/g, ',');
default:
return value.toString();
}
}
/******************************************************************************
* ALERT GENERATION
******************************************************************************/
function generateAlerts(data) {
var alerts = [];
// Brand traffic alert
if (data.summary.brandPercent > CONFIG.BRAND_CONCERN_THRESHOLD) {
alerts.push({
severity: '⚠️ WARNING',
message: 'Brand traffic at ' + (data.summary.brandPercent * 100).toFixed(1) + '% (threshold: ' + (CONFIG.BRAND_CONCERN_THRESHOLD * 100) + '%)',
action: 'Consider adding brand exclusions to force prospecting'
});
}
// Zombie products alert
if (data.summary.productHealth.zombies > 10) {
alerts.push({
severity: '⚠️ WARNING',
message: data.summary.productHealth.zombies + ' zombie products (50+ clicks, 0 conversions)',
action: 'Review Product Tiers sheet - consider exclusions'
});
}
// Display creep alert
if (data.summary.channelDistribution.display > 0.25) {
alerts.push({
severity: '🔔 INFO',
message: 'Display spend at ' + (data.summary.channelDistribution.display * 100).toFixed(1) + '% of total',
action: 'Review placement quality - may indicate low-intent traffic'
});
}
// Low shopping alert (for ecommerce)
if (data.productTiers.length > 0 && data.summary.channelDistribution.shopping < 0.30) {
alerts.push({
severity: '🔔 INFO',
message: 'Shopping only ' + (data.summary.channelDistribution.shopping * 100).toFixed(1) + '% of spend',
action: 'Ecommerce accounts typically see 40-60% Shopping'
});
}
// Search term waste alert
var wasteTerms = data.searchTerms.filter(function(t) { return t['Flag'] === '🔴 HIGH WASTE'; });
if (wasteTerms.length > 5) {
var wasteAmount = 0;
for (var i = 0; i < wasteTerms.length; i++) {
wasteAmount += wasteTerms[i]['Est. Cost'];
}
alerts.push({
severity: '⚠️ WARNING',
message: wasteTerms.length + ' high-waste search categories (~$' + wasteAmount.toFixed(0) + ' est.)',
action: 'Add to account-level negatives'
});
data.summary.wasteIdentified += wasteAmount;
}
// Suspicious placements alert
var suspiciousPlacements = data.placements.filter(function(p) {
return p['Quality Flag'].indexOf('🔴') !== -1;
});
if (suspiciousPlacements.length > 10) {
var placementWaste = 0;
for (var j = 0; j < suspiciousPlacements.length; j++) {
placementWaste += suspiciousPlacements[j]['Cost'];
}
alerts.push({
severity: '⚠️ WARNING',
message: suspiciousPlacements.length + ' suspicious placements ($' + placementWaste.toFixed(2) + ' spent)',
action: 'Review Placements sheet - add exclusions'
});
data.summary.wasteIdentified += placementWaste;
}
// Trend alerts from trends data
for (var k = 0; k < data.trends.length; k++) {
var trend = data.trends[k];
if (trend['Alert'] === '⚠️') {
alerts.push({
severity: '🔔 TREND',
message: trend['Metric'] + ' changed ' + trend['Change %'] + ' vs previous period',
action: 'Investigate cause of significant change'
});
}
}
data.summary.alerts = alerts;
data.summary.totalConversions = 0;
data.summary.totalValue = 0;
for (var m = 0; m < data.campaigns.length; m++) {
data.summary.totalConversions += data.campaigns[m]['Conversions'];
data.summary.totalValue += data.campaigns[m]['Conv Value'];
}
}
/******************************************************************************
* OUTPUT FUNCTIONS
******************************************************************************/
function writeAllSheets(ss, data) {
// 1. Summary (always first)
writeSummarySheet(ss, data);
// 2. Channel Split
if (data.channelSplit.length > 0) {
writeDataSheet(ss, '2. Channel Split', data.channelSplit, [
'Campaign', 'Total Spend', 'Shopping $', 'Shopping %',
'Search $ (Est)', 'Search % (Est)', 'Display $ (Est)', 'Display % (Est)',
'Video $ (Est)', 'Video % (Est)', 'Video Views', 'ROAS', 'Conversions'
]);
}
// 3. Brand Analysis
if (data.brandAnalysis.length > 0) {
writeDataSheet(ss, '3. Brand Analysis', data.brandAnalysis, [
'Campaign', 'Total Clicks', 'Brand Clicks', 'Brand %',
'Non-Brand Clicks', 'Non-Brand %', 'Unknown Clicks', 'Unknown %',
'Brand Conversions', 'Non-Brand Conversions', 'Incrementality Score'
]);
}
// 4. Product Tiers
if (data.productTiers.length > 0) {
writeDataSheet(ss, '4. Product Tiers', data.productTiers, [
'Product ID', 'Campaign', 'Asset Group', 'Tier',
'Impressions', 'Clicks', 'Cost', 'Conversions', 'Conv Value',
'ROAS', 'ROAS Index', 'Conv Rate Index', 'Action'
]);
}
// 5. Asset Groups
if (data.assetGroups.length > 0) {
writeDataSheet(ss, '5. Asset Groups', data.assetGroups, [
'Campaign', 'Asset Group', 'Status', 'Primary Status',
'Impressions', 'Clicks', 'Cost', 'Conversions', 'Conv Value',
'CTR', 'Conv Rate', 'CPA', 'ROAS', 'Efficiency Score', 'vs Account Avg', 'Recommendation'
]);
}
// 6. Search Terms
if (data.searchTerms.length > 0) {
writeDataSheet(ss, '6. Search Terms', data.searchTerms, [
'Campaign', 'Search Category', 'Impressions', 'Clicks', 'Est. Cost',
'Conversions', 'Conv Value', 'Conv Rate', 'Flag', 'Suggested Action'
]);
}
// 7. Placements
if (data.placements.length > 0) {
writeDataSheet(ss, '7. Placements', data.placements, [
'Campaign', 'Placement', 'Type', 'Impressions', 'Clicks', 'Cost',
'CTR', 'Conversions', 'Conv Rate', 'Quality Flag', 'Reason'
]);
}
// 8. Trends
if (data.trends.length > 0) {
writeDataSheet(ss, '8. Trends', data.trends, [
'Metric', 'This Period', 'Previous Period', 'Change', 'Change %', 'Trend', 'Alert'
]);
}
}
function writeSummarySheet(ss, data) {
var sheet = ss.getSheetByName('1. Summary');
if (!sheet) {
sheet = ss.insertSheet('1. Summary', 0);
} else {
sheet.clear();
}
var rows = [];
// Header
rows.push(['PMAX INSIGHT ENGINE', '']);
rows.push(['Generated by PPC.io Script Engine', '']);
rows.push(['https://ppc.io', '']);
rows.push(['', '']);
rows.push(['Account: ' + AdsApp.currentAccount().getName(), '']);
rows.push(['Date Range: ' + CONFIG.DATE_RANGE, '']);
rows.push(['Generated: ' + new Date().toISOString(), '']);
rows.push(['', '']);
// Overview
rows.push(['════════════════════════════════════════════════════════════════', '']);
rows.push(['OVERVIEW', '']);
rows.push(['════════════════════════════════════════════════════════════════', '']);
rows.push(['PMAX Campaigns', data.summary.totalCampaigns]);
rows.push(['Total Spend', '$' + data.summary.totalSpend.toFixed(2)]);
rows.push(['Total Conversions', data.summary.totalConversions.toFixed(1)]);
rows.push(['Total Value', '$' + data.summary.totalValue.toFixed(2)]);
rows.push(['Overall ROAS', data.summary.totalSpend > 0 ? (data.summary.totalValue / data.summary.totalSpend).toFixed(2) : 'N/A']);
rows.push(['', '']);
// Channel Distribution
rows.push(['════════════════════════════════════════════════════════════════', '']);
rows.push(['CHANNEL DISTRIBUTION', '']);
rows.push(['════════════════════════════════════════════════════════════════', '']);
rows.push(['Shopping', (data.summary.channelDistribution.shopping * 100).toFixed(1) + '%']);
rows.push(['Search (Est)', (data.summary.channelDistribution.search * 100).toFixed(1) + '%']);
rows.push(['Display (Est)', (data.summary.channelDistribution.display * 100).toFixed(1) + '%']);
rows.push(['Video (Est)', (data.summary.channelDistribution.video * 100).toFixed(1) + '%']);
rows.push(['', '']);
// Incrementality
rows.push(['════════════════════════════════════════════════════════════════', '']);
rows.push(['INCREMENTALITY SIGNALS', '']);
rows.push(['════════════════════════════════════════════════════════════════', '']);
var brandStatus = data.summary.brandPercent > CONFIG.BRAND_CONCERN_THRESHOLD ? '⚠️ HIGH' : '✅ Healthy';
rows.push(['Brand Traffic', (data.summary.brandPercent * 100).toFixed(1) + '% ' + brandStatus]);
rows.push(['Threshold', (CONFIG.BRAND_CONCERN_THRESHOLD * 100) + '%']);
rows.push(['', '']);
// Product Health
rows.push(['════════════════════════════════════════════════════════════════', '']);
rows.push(['PRODUCT HEALTH (Relative Performance)', '']);
rows.push(['════════════════════════════════════════════════════════════════', '']);
rows.push(['⭐ Stars (Top performers)', data.summary.productHealth.stars]);
rows.push(['✓ Solid (Above average)', data.summary.productHealth.solid]);
rows.push(['⚠️ Struggling (Below average)', data.summary.productHealth.struggling]);
rows.push(['💀 Zombies (No conversions)', data.summary.productHealth.zombies]);
rows.push(['💤 Sleepers (Low visibility)', data.summary.productHealth.sleepers]);
rows.push(['', '']);
// Waste Identified
rows.push(['════════════════════════════════════════════════════════════════', '']);
rows.push(['WASTE IDENTIFIED', '']);
rows.push(['════════════════════════════════════════════════════════════════', '']);
rows.push(['Estimated Wasted Spend', '$' + data.summary.wasteIdentified.toFixed(2)]);
var wasteSearchTerms = data.searchTerms.filter(function(t) { return t['Flag'] === '🔴 HIGH WASTE'; }).length;
var wastePlacements = data.placements.filter(function(p) { return p['Quality Flag'].indexOf('🔴') !== -1; }).length;
rows.push(['High-Waste Search Categories', wasteSearchTerms]);
rows.push(['Suspicious Placements', wastePlacements]);
rows.push(['', '']);
// Alerts
rows.push(['════════════════════════════════════════════════════════════════', '']);
rows.push(['ALERTS (' + data.summary.alerts.length + ')', '']);
rows.push(['════════════════════════════════════════════════════════════════', '']);
if (data.summary.alerts.length === 0) {
rows.push(['✅ No alerts - account looks healthy', '']);
} else {
for (var i = 0; i < data.summary.alerts.length; i++) {
var alert = data.summary.alerts[i];
rows.push([alert.severity + ' ' + alert.message, '']);
rows.push([' → ' + alert.action, '']);
}
}
rows.push(['', '']);
// AI Prompts
rows.push(['════════════════════════════════════════════════════════════════', '']);
rows.push(['AI ANALYSIS PROMPTS', '']);
rows.push(['════════════════════════════════════════════════════════════════', '']);
rows.push(['Paste sheets into Claude with these prompts:', '']);
rows.push(['', '']);
rows.push(['1. "Analyze this PMAX data. What are the top 3 issues to address?"', '']);
rows.push(['2. "Which products should I exclude from PMAX and why?"', '']);
rows.push(['3. "Is PMAX finding new customers or just harvesting brand traffic?"', '']);
rows.push(['4. "Based on channel split, where should Google be spending more?"', '']);
rows.push(['5. "Create a prioritized 5-point action plan based on these findings."', '']);
rows.push(['', '']);
// Sheet Navigation
rows.push(['════════════════════════════════════════════════════════════════', '']);
rows.push(['SHEET NAVIGATION', '']);
rows.push(['════════════════════════════════════════════════════════════════', '']);
rows.push(['2. Channel Split', 'Where your money actually goes']);
rows.push(['3. Brand Analysis', 'Brand vs non-brand traffic breakdown']);
rows.push(['4. Product Tiers', 'Products bucketed by relative performance']);
rows.push(['5. Asset Groups', 'Asset group efficiency scores']);
rows.push(['6. Search Terms', 'Non-converting search category waste']);
rows.push(['7. Placements', 'Placement quality flags']);
rows.push(['8. Trends', 'Week-over-week performance changes']);
// Write all rows
sheet.getRange(1, 1, rows.length, 2).setValues(rows);
// Formatting
sheet.getRange(1, 1).setFontWeight('bold').setFontSize(16);
sheet.setColumnWidth(1, 500);
sheet.setColumnWidth(2, 200);
// Bold section headers
for (var j = 0; j < rows.length; j++) {
if (rows[j][0].indexOf('════') !== -1 ||
rows[j][0] === 'OVERVIEW' ||
rows[j][0] === 'CHANNEL DISTRIBUTION' ||
rows[j][0] === 'INCREMENTALITY SIGNALS' ||
rows[j][0] === 'PRODUCT HEALTH (Relative Performance)' ||
rows[j][0] === 'WASTE IDENTIFIED' ||
rows[j][0].indexOf('ALERTS') !== -1 ||
rows[j][0] === 'AI ANALYSIS PROMPTS' ||
rows[j][0] === 'SHEET NAVIGATION') {
sheet.getRange(j + 1, 1).setFontWeight('bold');
}
}
}
function writeDataSheet(ss, sheetName, data, columns) {
var sheet = ss.getSheetByName(sheetName);
if (!sheet) {
sheet = ss.insertSheet(sheetName);
} else {
sheet.clear();
}
// Header row
sheet.getRange(1, 1, 1, columns.length).setValues([columns]).setFontWeight('bold');
sheet.setFrozenRows(1);
if (data.length === 0) {
sheet.getRange(2, 1).setValue('No data available');
return;
}
// Data rows
var rows = data.map(function(row) {
return columns.map(function(col) {
var val = row[col];
if (val === null || val === undefined) return '';
if (typeof val === 'number' && !isNaN(val)) {
// Format percentages
if (col.indexOf('%') !== -1 || col === 'CTR' || col.indexOf('Rate') !== -1) {
return val;
}
return val;
}
return val;
});
});
// Batch write
for (var i = 0; i < rows.length; i += CONFIG.BATCH_SIZE) {
var batch = rows.slice(i, Math.min(i + CONFIG.BATCH_SIZE, rows.length));
sheet.getRange(2 + i, 1, batch.length, columns.length).setValues(batch);
}
// Apply formatting
applySheetFormatting(sheet, columns, rows.length);
}
function applySheetFormatting(sheet, columns, numRows) {
// Currency columns
var currencyColumns = ['Cost', 'Total Spend', 'Shopping $', 'Search $ (Est)', 'Display $ (Est)',
'Video $ (Est)', 'Est. Cost', 'CPA', 'Conv Value', 'CPC', 'Daily Budget'];
// Percentage columns
var percentColumns = ['Shopping %', 'Search % (Est)', 'Display % (Est)', 'Video % (Est)',
'Brand %', 'Non-Brand %', 'Unknown %', 'CTR', 'Conv Rate', 'Change %'];
for (var i = 0; i < columns.length; i++) {
var col = columns[i];
var colIndex = i + 1;
if (currencyColumns.indexOf(col) !== -1) {
sheet.getRange(2, colIndex, numRows, 1).setNumberFormat('$#,##0.00');
} else if (percentColumns.indexOf(col) !== -1) {
sheet.getRange(2, colIndex, numRows, 1).setNumberFormat('0.0%');
} else if (col === 'ROAS' || col === 'ROAS Index' || col === 'Conv Rate Index' || col === 'Efficiency Score') {
sheet.getRange(2, colIndex, numRows, 1).setNumberFormat('0.00');
}
}
// Auto-resize columns (up to 15)
for (var j = 1; j <= Math.min(columns.length, 15); j++) {
sheet.autoResizeColumn(j);
}
// Apply conditional formatting for flags
applyFlagFormatting(sheet, columns, numRows);
}
function applyFlagFormatting(sheet, columns, numRows) {
var flagColumns = ['Tier', 'Flag', 'Quality Flag', 'Alert', 'Trend'];
var colors = {
'⭐': '#d4edda', // Green
'✓': '#cce5ff', // Blue
'⚠️': '#fff3cd', // Yellow
'💀': '#f8d7da', // Red
'🔴': '#f8d7da', // Red
'🟡': '#fff3cd', // Yellow
'🟢': '#d4edda', // Green
'💤': '#e9ecef', // Gray
'📊': '#e9ecef', // Gray
'↑': '#d4edda', // Green
'↓': '#f8d7da', // Red
'→': '#e9ecef' // Gray
};
for (var i = 0; i < columns.length; i++) {
if (flagColumns.indexOf(columns[i]) !== -1) {
var colIndex = i + 1;
var range = sheet.getRange(2, colIndex, numRows, 1);
var values = range.getValues();
var bgColors = [];
for (var j = 0; j < values.length; j++) {
var cellValue = values[j][0].toString();
var color = '#ffffff';
for (var symbol in colors) {
if (cellValue.indexOf(symbol) !== -1) {
color = colors[symbol];
break;
}
}
bgColors.push([color]);
}
range.setBackgrounds(bgColors);
}
}
}
function writeEmptyState(ss) {
var sheet = ss.getSheetByName('1. Summary');
if (!sheet) {
sheet = ss.insertSheet('1. Summary', 0);
} else {
sheet.clear();
}
var rows = [
['PMAX INSIGHT ENGINE', ''],
['Generated by PPC.io Script Engine', ''],
['', ''],
['⚠️ NO PERFORMANCE MAX CAMPAIGNS FOUND', ''],
['', ''],
['This account does not have any Performance Max campaigns.', ''],
['', ''],
['To use this script:', ''],
['1. Create a Performance Max campaign in Google Ads', ''],
['2. Let it run for at least 7 days', ''],
['3. Re-run this script', '']
];
sheet.getRange(1, 1, rows.length, 2).setValues(rows);
sheet.getRange(1, 1).setFontWeight('bold').setFontSize(16);
sheet.getRange(4, 1).setFontWeight('bold').setFontSize(14);
sheet.setColumnWidth(1, 500);
}
/******************************************************************************
* NOTIFICATION FUNCTIONS
******************************************************************************/
function sendNotifications(data, spreadsheetUrl, startTime) {
var duration = ((new Date() - startTime) / 1000).toFixed(1);
var message = [
'PMAX INSIGHT ENGINE - Analysis Complete',
'',
'Account: ' + AdsApp.currentAccount().getName(),
'Duration: ' + duration + 's',
'',
'Summary:',
'- PMAX Campaigns: ' + data.summary.totalCampaigns,
'- Total Spend: $' + data.summary.totalSpend.toFixed(2),
'- Total Conversions: ' + data.summary.totalConversions.toFixed(1),
'',
'Channel Split:',
'- Shopping: ' + (data.summary.channelDistribution.shopping * 100).toFixed(1) + '%',
'- Search: ' + (data.summary.channelDistribution.search * 100).toFixed(1) + '%',
'- Display: ' + (data.summary.channelDistribution.display * 100).toFixed(1) + '%',
'- Video: ' + (data.summary.channelDistribution.video * 100).toFixed(1) + '%',
'',
'Brand Traffic: ' + (data.summary.brandPercent * 100).toFixed(1) + '%',
'Estimated Waste: $' + data.summary.wasteIdentified.toFixed(2),
'Alerts: ' + data.summary.alerts.length,
'',
'Full Report: ' + spreadsheetUrl,
'',
'--',
'Generated by PPC.io Script Engine'
].join('\n');
// Email
if (CONFIG.EMAIL_RECIPIENTS && CONFIG.EMAIL_RECIPIENTS.length > 0) {
try {
var subject = '[PPC.io] PMAX Insight Engine - ' +
data.summary.alerts.length + ' alerts, $' +
data.summary.wasteIdentified.toFixed(0) + ' waste identified';
MailApp.sendEmail({
to: CONFIG.EMAIL_RECIPIENTS.join(','),
subject: subject,
body: message
});
log('INFO', 'Email notification sent');
} catch (e) {
log('ERROR', 'Failed to send email: ' + e.message);
}
}
// Slack
if (CONFIG.SLACK_WEBHOOK_URL) {
try {
UrlFetchApp.fetch(CONFIG.SLACK_WEBHOOK_URL, {
method: 'post',
contentType: 'application/json',
payload: JSON.stringify({
text: ':bar_chart: *PPC.io PMAX Insight Engine*\n```' + message + '```'
})
});
log('INFO', 'Slack notification sent');
} catch (e) {
log('ERROR', 'Failed to send Slack: ' + e.message);
}
}
}
/******************************************************************************
* UTILITY FUNCTIONS
******************************************************************************/
function checkTimeLimit(startTime) {
var elapsed = (new Date() - startTime) / 1000 / 60;
if (elapsed > CONFIG.TIME_LIMIT_MINUTES) {
throw new Error('TIME_LIMIT: Processed up to current point after ' + elapsed.toFixed(1) + ' minutes. Re-run to continue.');
}
}
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 logCompletion(data, startTime) {
var duration = ((new Date() - startTime) / 1000).toFixed(1);
log('INFO', '════════════════════════════════════════════════════════════');
log('INFO', 'PMAX INSIGHT ENGINE COMPLETE');
log('INFO', '════════════════════════════════════════════════════════════');
log('INFO', 'Duration: ' + duration + ' seconds');
log('INFO', 'Campaigns analyzed: ' + data.summary.totalCampaigns);
log('INFO', 'Asset groups: ' + data.assetGroups.length);
log('INFO', 'Products tiered: ' + data.productTiers.length);
log('INFO', 'Search terms analyzed: ' + data.searchTerms.length);
log('INFO', 'Placements reviewed: ' + data.placements.length);
log('INFO', 'Alerts generated: ' + data.summary.alerts.length);
log('INFO', 'Waste identified: $' + data.summary.wasteIdentified.toFixed(2));
log('INFO', '════════════════════════════════════════════════════════════');
}
function handleFatalError(error, startTime) {
log('ERROR', '════════════════════════════════════════════════════════════');
log('ERROR', 'FATAL ERROR: ' + error.message);
log('ERROR', 'Stack: ' + error.stack);
log('ERROR', '════════════════════════════════════════════════════════════');
if (CONFIG.EMAIL_RECIPIENTS && CONFIG.EMAIL_RECIPIENTS.length > 0) {
try {
MailApp.sendEmail({
to: CONFIG.EMAIL_RECIPIENTS.join(','),
subject: '[PPC.io ERROR] PMAX Insight Engine Failed',
body: 'Error: ' + error.message + '\n\nStack:\n' + error.stack +
'\n\nAccount: ' + AdsApp.currentAccount().getName()
});
} catch (e) {
log('ERROR', 'Could not send error email: ' + e.message);
}
}
}
function formatDate(date) {
return Utilities.formatDate(date, AdsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');
}