Free: 96 PPC tools + my AI Playbook book
Production-ready prompts, scripts, frameworks and AI agents for Google Ads professionals. No payment required.
/******************************************************************************
* ULTIMATE NEGATIVE KEYWORD ENGINE v2.0
*
* Generated by PPC.io Script Engine
* https://ppc.io
*
* THE BEST NEGATIVE KEYWORD SCRIPT IN THE WORLD
*
* Purpose: Autonomous negative keyword discovery, analysis, and management
* Author: PPC.io
* Version: 2.0
* Updated: 2026-01-22
*
* WHAT MAKES THIS THE BEST:
* ============================================================================
* 1. N-GRAM MINING ENGINE - Discovers root cause patterns from waste
* "laser lipo jobs", "smart lipo careers", "lipo employment"
* → Automatically discovers "jobs" is the problem, not each variant
*
* 2. STATISTICAL SIGNIFICANCE - Won't act on noise
* Uses binomial confidence intervals to ensure decisions are data-backed
* Calculates exact probability that a term is truly waste vs bad luck
*
* 3. CROSS-CAMPAIGN INTELLIGENCE - Learns across your account
* If 5 campaigns already block "free", suggests it for campaign #6
* Identifies negative inconsistencies automatically
*
* 4. CONFLICT DETECTION - Prevents self-sabotage
* Warns if proposed negative would block positive keywords
* Checks against existing keyword inventory before action
*
* 5. SMART PATTERN LIBRARY - 70+ built-in patterns
* Universal waste patterns (jobs, DIY, free, etc.)
* Interrogative intent patterns (what is, how to, etc.)
* Intent modifiers (cheap, review, complaints, etc.)
*
* 6. PERFORMANCE ECONOMICS - Break-even CPC analysis
* Calculates actual cost-per-click thresholds
* Prioritizes by potential savings, not just pattern matching
*
* 7. SAFE AUTOMATION - Multiple safety layers
* DRY_RUN mode shows what would happen
* Approval workflows for high-volume changes
* Automatic rollback capability via logging
*
* 8. INCREMENTAL PROCESSING - Won't re-analyze
* Tracks what's been processed using labels/properties
* Handles millions of search terms over time
*
* 9. SHARED LIST INTEGRATION - Organized negatives
* Automatically creates/updates shared negative keyword lists
* Groups by category (Jobs, DIY, Competitors, etc.)
*
* 10. SMART ROOT EXTRACTION - Finds the core problem
* "laser lipo jobs near me" → extracts "jobs", not the whole phrase
* "free consultation for..." → extracts just "free" (but protects valid uses)
*
* 11. VALUE-BASED ANALYSIS - Beyond just conversions
* Considers conversion value, not just conversion count
* ROAS analysis for e-commerce/high-value businesses
*
* 12. MULTI-DIMENSIONAL INTENT SCORING - 5 intent dimensions
* Commercial Intent, Urgency, Qualification, Specificity, Local Intent
* Weighted scoring for overall intent assessment
*
* 13. BUDGET IMPACT PROJECTION - ROI calculations
* Monthly/quarterly/annual savings projections
* Category breakdown and implementation ROI
*
* 14. CUSTOM PATTERN SUPPORT - Extensible via Sheets
* Load your own patterns from external spreadsheet
* No code editing required to add custom rules
*
* 15. SEARCH TERM CLUSTERING - Group similar terms
* Jaccard similarity algorithm for intelligent grouping
* Identify root negatives instead of dozens of variants
*
* 16. TOP 10 QUICK WINS - Prioritized recommendations
* Highest-impact negatives shown first
* Immediate actionable insights
*
* 17. AUDIT LOG WITH UNDO - Full rollback capability
* Every change logged with timestamp
* Google Ads Editor format undo commands
*
* 18. COPY-PASTE READY LISTS - Instant implementation
* Pre-formatted for Google Ads and Editor
* Match type recommendations included
*
* 19. ACCOUNT HEALTH SCORE - 0-100 grading system
* A-F letter grade with interpretation
* Actionable insights based on score
*
* 20. SCHEDULING RECOMMENDATIONS - Smart timing
* Frequency based on account health
* Best time and rationale for running
*
* 21. GETTING STARTED CHECKLIST - Step-by-step guide
* 5-step checklist for new users
* From configuration to ongoing automation
*
* 22. PERFORMANCE METRICS - Execution analytics
* Phase-by-phase timing breakdown
* Processing rate and efficiency metrics
*
* SETUP INSTRUCTIONS:
* ============================================================================
* 1. Set SPREADSHEET_URL to 'CREATE_NEW' or paste existing URL
* 2. Configure your BUSINESS_CONTEXT in the CONFIG section
* 3. Set MODE to 'DISCOVER' first to see what it finds
* 4. Review the output spreadsheet
* 5. Change MODE to 'APPLY' when ready to add negatives
* 6. Schedule to run weekly for continuous optimization
*
* OPERATION MODES:
* - DISCOVER: Analyze search terms, report findings, no changes
* - APPLY: Add negatives based on rules (respects DRY_RUN)
* - AUDIT: Compare negative coverage across campaigns
* - MINING: Deep N-gram analysis to find new patterns
*
* USE CASES:
* - "Find all wasted spend from irrelevant search terms"
* - "Discover new negative patterns I haven't thought of"
* - "Ensure negative consistency across all campaigns"
* - "Automatically add proven waste patterns as negatives"
*
* QUICK-START TEMPLATES:
* ============================================================================
* Copy the BUSINESS_CONTEXT that matches your industry:
*
* MEDICAL AESTHETICS / MED SPA:
* industry: 'medical_aesthetics',
* brand_terms: ['your brand name'],
* services_offered: ['botox', 'fillers', 'laser', 'coolsculpting'],
* services_not_offered: ['surgery', 'weight loss pills'],
* target_cpa: 150,
* expected_cvr: 0.03,
* price_positioning: 'premium'
*
* DENTAL PRACTICE:
* industry: 'dental',
* brand_terms: ['your practice name'],
* services_offered: ['cleaning', 'whitening', 'implants', 'invisalign'],
* target_cpa: 100,
* expected_cvr: 0.05,
* price_positioning: 'competitive'
*
* LAW FIRM:
* industry: 'legal',
* brand_terms: ['your firm name'],
* services_offered: ['personal injury', 'car accident', 'slip and fall'],
* target_cpa: 200,
* expected_cvr: 0.02,
* price_positioning: 'premium'
*
* E-COMMERCE:
* industry: 'ecommerce',
* brand_terms: ['your brand'],
* services_offered: ['your main products'],
* target_cpa: 30,
* expected_cvr: 0.04,
* price_positioning: 'competitive'
*
* LOCAL SERVICES (HVAC, Plumbing, etc.):
* industry: 'home_services',
* brand_terms: ['your company name'],
* services_offered: ['repair', 'installation', 'maintenance'],
* locations_served: ['your city', 'your county'],
* target_cpa: 75,
* expected_cvr: 0.08,
* price_positioning: 'competitive'
*
* SAAS / SOFTWARE:
* industry: 'saas',
* brand_terms: ['your product name'],
* services_offered: ['your main features'],
* target_cpa: 100,
* expected_cvr: 0.02,
* price_positioning: 'competitive',
* business_type: 'b2b'
*
******************************************************************************/
/******************************************************************************
* CONFIGURATION
******************************************************************************/
var CONFIG = {
// ═══════════════════════════════════════════════════════════════════════════
// OUTPUT SETTINGS
// ═══════════════════════════════════════════════════════════════════════════
SPREADSHEET_URL: 'CREATE_NEW', // Or paste existing spreadsheet URL
// Email alerts (leave empty array to disable)
EMAIL_RECIPIENTS: [], // ['you@example.com']
// Slack webhook (leave empty to disable)
SLACK_WEBHOOK_URL: '',
// ═══════════════════════════════════════════════════════════════════════════
// OPERATION MODE
// ═══════════════════════════════════════════════════════════════════════════
// DISCOVER - Analyze and report only
// APPLY - Add negatives (respects DRY_RUN)
// AUDIT - Check negative consistency
// MINING - Deep N-gram pattern discovery
MODE: 'DISCOVER',
// ═══════════════════════════════════════════════════════════════════════════
// SAFETY SETTINGS
// ═══════════════════════════════════════════════════════════════════════════
DRY_RUN: true, // CRITICAL: Set false to make changes
MAX_NEGATIVES_PER_RUN: 100, // Safety limit
REQUIRE_APPROVAL_ABOVE: 50, // Pause for human review above this count
MIN_CONFIDENCE: 0.80, // Only act on high-confidence findings
// ═══════════════════════════════════════════════════════════════════════════
// ANALYSIS THRESHOLDS
// ═══════════════════════════════════════════════════════════════════════════
DATE_RANGE: 'LAST_30_DAYS', // LAST_7_DAYS, LAST_30_DAYS, LAST_90_DAYS, ALL_TIME
MIN_IMPRESSIONS: 20, // Minimum impressions to analyze
MIN_CLICKS_FOR_PERF: 50, // Minimum clicks for performance decisions
MIN_COST_TO_FLAG: 1.00, // Minimum cost to flag a term
// ═══════════════════════════════════════════════════════════════════════════
// BUSINESS CONTEXT - CUSTOMIZE FOR YOUR BUSINESS
// ═══════════════════════════════════════════════════════════════════════════
BUSINESS_CONTEXT: {
// Your brand names and variations (NEVER negative these)
brand_terms: [], // ['acme', 'acme corp', 'acme inc']
// Core services/products you offer
services_offered: [], // ['widget repair', 'widget installation']
// Services you explicitly DON'T offer
services_not_offered: [], // ['widget rental', 'widget disposal']
// Target CPA for break-even calculations
target_cpa: null, // e.g., 150 (dollars)
// Expected conversion rate (decimal)
expected_cvr: null, // e.g., 0.03 (3%)
// Premium positioning? (affects "cheap" handling)
price_positioning: 'premium', // 'premium', 'budget', 'competitive'
// B2B or B2C?
business_type: 'b2c', // 'b2b', 'b2c', 'both'
// Geographic focus
locations_served: [], // ['los angeles', 'orange county']
// Industry vertical (for industry-specific patterns)
industry: '' // 'medical', 'legal', 'ecommerce', 'saas', 'local_services'
},
// ═══════════════════════════════════════════════════════════════════════════
// CAMPAIGN FILTERS
// ═══════════════════════════════════════════════════════════════════════════
CAMPAIGN_NAME_CONTAINS: '', // Only analyze these campaigns
CAMPAIGN_NAME_EXCLUDES: '', // Skip these campaigns
CAMPAIGN_TYPES: ['SEARCH'], // SEARCH, DISPLAY, SHOPPING, PERFORMANCE_MAX
INCLUDE_PAUSED: false,
// ═══════════════════════════════════════════════════════════════════════════
// N-GRAM MINING SETTINGS
// ═══════════════════════════════════════════════════════════════════════════
NGRAM_MIN_OCCURRENCES: 3, // N-gram must appear in this many terms
NGRAM_MIN_WASTE_RATIO: 0.80, // 80% of appearances must be waste
NGRAM_SIZES: [1, 2, 3], // Unigrams, bigrams, trigrams
// ═══════════════════════════════════════════════════════════════════════════
// SHARED NEGATIVE LIST SETTINGS
// ═══════════════════════════════════════════════════════════════════════════
USE_SHARED_LISTS: true, // Create/use shared negative lists
SHARED_LIST_PREFIX: 'AUTO: ', // Prefix for auto-created lists
SHARED_LIST_CATEGORIES: {
// Map waste signals to shared list names
'employment': 'AUTO: Jobs & Employment',
'navigation': 'AUTO: Login & Navigation',
'education_diy': 'AUTO: DIY & How-To',
'document_seeking': 'AUTO: Documents & Templates',
'free_seeking': 'AUTO: Free Seekers',
'complaints': 'AUTO: Complaints & Negative',
'competitor': 'AUTO: Competitors',
'wrong_industry': 'AUTO: Wrong Industry',
'geographic_mismatch': 'AUTO: Wrong Location'
},
// ═══════════════════════════════════════════════════════════════════════════
// SMART EXTRACTION SETTINGS
// ═══════════════════════════════════════════════════════════════════════════
EXTRACT_ROOT_TERMS: true, // Extract root cause, not full phrase
MIN_ROOT_TERM_LENGTH: 3, // Minimum characters for extracted root
// ═══════════════════════════════════════════════════════════════════════════
// CUSTOM PATTERNS (via Spreadsheet)
// ═══════════════════════════════════════════════════════════════════════════
CUSTOM_PATTERNS_SHEET_URL: '', // URL to spreadsheet with custom patterns
CUSTOM_PATTERNS_SHEET_NAME: 'Custom Patterns', // Sheet name
// Format: Column A = Pattern (regex or text), Column B = Category, Column C = Confidence
// Example row: "competitor name|other competitor" | "competitor" | 0.95
// ═══════════════════════════════════════════════════════════════════════════
// CLUSTERING SETTINGS
// ═══════════════════════════════════════════════════════════════════════════
ENABLE_CLUSTERING: true, // Group similar terms together
MIN_CLUSTER_SIZE: 3, // Minimum terms to form a cluster
CLUSTER_SIMILARITY_THRESHOLD: 0.6, // 0-1, higher = stricter matching
// ═══════════════════════════════════════════════════════════════════════════
// AUDIT LOG SETTINGS
// ═══════════════════════════════════════════════════════════════════════════
ENABLE_AUDIT_LOG: true, // Log all changes for undo/rollback
AUDIT_LOG_SHEET_NAME: 'Audit Log', // Sheet name within output spreadsheet
// ═══════════════════════════════════════════════════════════════════════════
// EXECUTION SETTINGS
// ═══════════════════════════════════════════════════════════════════════════
LOG_LEVEL: 'INFO', // DEBUG, INFO, WARN, ERROR
TIME_LIMIT_MINUTES: 25, // Exit gracefully before timeout
BATCH_SIZE: 1000 // Rows per batch
};
/******************************************************************************
* PATTERN LIBRARY - THE INTELLIGENCE ENGINE
******************************************************************************/
var PATTERNS = {
// ═══════════════════════════════════════════════════════════════════════════
// DEFINITE WASTE - These NEVER convert for commercial businesses
// ═══════════════════════════════════════════════════════════════════════════
EMPLOYMENT: {
name: 'Employment Intent',
description: 'User seeking jobs, not buying services',
confidence: 1.0,
match_type: 'BROAD',
level: 'ACCOUNT',
patterns: [
/\b(jobs?|careers?|hiring|recruit(ment|ing)?|vacanc(y|ies)|employment)\b/i,
/\b(salary|salaries|wages?|compensation|pay\s*scale)\b/i,
/\b(resume|cv|curriculum\s*vitae|cover\s*letter)\b/i,
/\b(job\s*(board|listing|posting|search|seeker|opening))\b/i,
/\b(work\s*(from\s*home|remotely)|wfh)\b/i,
/\b(intern(ship)?s?|apprentice(ship)?s?)\b/i
],
suggested_negatives: ['jobs', 'careers', 'hiring', 'salary', 'employment', 'resume']
},
NAVIGATION: {
name: 'Navigation Intent',
description: 'User trying to log into existing account',
confidence: 1.0,
match_type: 'PHRASE',
level: 'ACCOUNT',
patterns: [
/\b(log\s*in|login|sign\s*in|signin)\b/i,
/\b(my\s*account|account\s*access|member\s*portal)\b/i,
/\b(dashboard|admin\s*panel|control\s*panel)\b/i,
/\b(reset\s*password|forgot\s*password|password\s*recovery)\b/i
],
suggested_negatives: ['login', 'sign in', 'my account', 'dashboard']
},
EDUCATION_DIY: {
name: 'DIY/Educational Intent',
description: 'User seeking free information, not paid services',
confidence: 0.95,
match_type: 'PHRASE',
level: 'CAMPAIGN',
patterns: [
/\b(how\s*to|tutorial|guide|instructions?|manual|handbook)\b/i,
/\b(diy|do\s*it\s*yourself|home\s*made|self[-\s]*(made|taught))\b/i,
/\b(definition|meaning|what\s*(is|are|does)|explain)\b/i,
/\b(wiki(pedia)?|youtube|video|watch)\b/i,
/\b(free\s*(course|class|training|lesson|webinar))\b/i,
/\b(reddit|quora|forum|community|discussion)\b/i
],
suggested_negatives: ['how to', 'tutorial', 'diy', 'what is', 'reddit', 'youtube']
},
DOCUMENT_SEEKING: {
name: 'Document Seeking',
description: 'User wants templates/downloads, not services',
confidence: 0.95,
match_type: 'BROAD',
level: 'ACCOUNT',
patterns: [
/\b(pdf|download|template|sample|example)\b/i,
/\b(spreadsheet|excel|word\s*doc|powerpoint|ppt)\b/i,
/\b(checklist|worksheet|printable|fillable)\b/i,
/\b(form|document|file)\s+(download|free|template)/i
],
suggested_negatives: ['pdf', 'template', 'download', 'spreadsheet', 'checklist']
},
FREE_SEEKING: {
name: 'Free Seekers',
description: 'User explicitly seeking free options',
confidence: 0.90,
match_type: 'PHRASE',
level: 'CAMPAIGN',
patterns: [
/\bfree\b(?!\s*(trial|consultation|estimate|quote|assessment))/i,
/\b(no\s*cost|zero\s*cost|at\s*no\s*charge)\b/i,
/\b(gratis|complimentary)\b(?!\s*(consultation|assessment))/i
],
suggested_negatives: ['free'],
// Note: Excludes "free trial", "free consultation" which CAN convert
exclude_if_contains: ['trial', 'consultation', 'estimate', 'quote', 'assessment', 'demo']
},
// ═══════════════════════════════════════════════════════════════════════════
// LIKELY WASTE - High probability of waste but some exceptions
// ═══════════════════════════════════════════════════════════════════════════
COMPLAINTS: {
name: 'Complaint Intent',
description: 'User researching problems, not buying',
confidence: 0.85,
match_type: 'PHRASE',
level: 'CAMPAIGN',
patterns: [
/\b(complaint|complain|lawsuit|sue|suing|legal\s*action)\b/i,
/\b(scam|fraud|rip\s*off|ripoff|warning)\b/i,
/\b(class\s*action|attorney\s*general)\b/i,
/\b(refund|money\s*back|chargeback)\b/i,
/\b(problem|issue|trouble)\s+(with|at|from)/i
],
suggested_negatives: ['complaint', 'lawsuit', 'scam', 'fraud', 'refund']
},
COMPARISON_RESEARCH: {
name: 'Pure Research Intent',
description: 'User in research mode, not ready to buy',
confidence: 0.75,
match_type: 'PHRASE',
level: 'CAMPAIGN',
patterns: [
/\b(review|reviews|rating|ratings|testimonial)\b/i,
/\b(comparison|compare|vs|versus|difference)\b/i,
/\b(pro\s*and\s*con|advantage|disadvantage)\b/i,
/\b(worth\s*it|should\s*i)\b/i
],
suggested_negatives: ['reviews', 'comparison'],
// Lower confidence because reviews CAN indicate purchase intent
require_additional_signal: true
},
PRICE_SENSITIVE: {
name: 'Price Sensitive',
description: 'Budget-focused user, lower LTV potential',
confidence: 0.70,
match_type: 'PHRASE',
level: 'CAMPAIGN',
patterns: [
/\b(cheap(est)?|budget|affordable|low\s*cost|inexpensive)\b/i,
/\b(discount|deal|sale|bargain|clearance)\b/i,
/\b(coupon|promo\s*code|voucher|offer\s*code)\b/i
],
suggested_negatives: ['cheap', 'cheapest', 'discount', 'coupon'],
// Only apply for premium-positioned businesses
condition: function() { return CONFIG.BUSINESS_CONTEXT.price_positioning === 'premium'; }
},
// ═══════════════════════════════════════════════════════════════════════════
// POTENTIAL WASTE - Context dependent
// ═══════════════════════════════════════════════════════════════════════════
GEOGRAPHIC_MISMATCH: {
name: 'Geographic Mismatch',
description: 'User searching for different location',
confidence: 0.85,
match_type: 'PHRASE',
level: 'CAMPAIGN',
patterns: [], // Dynamically generated from CONFIG.BUSINESS_CONTEXT.locations_served
suggested_negatives: [],
dynamic: true
},
WRONG_INDUSTRY: {
name: 'Wrong Industry',
description: 'Completely unrelated industry',
confidence: 0.95,
match_type: 'BROAD',
level: 'ACCOUNT',
patterns: [
// Generic wrong-industry patterns - customize per industry
/\b(dog|cat|pet|veterinary|animal)\b/i, // Unless you're a vet
/\b(recipe|cooking|baking|food)\b/i, // Unless you're a restaurant
/\b(game|gaming|xbox|playstation|nintendo)\b/i, // Unless gaming industry
/\b(song|lyrics|music|album|spotify)\b/i // Unless music industry
],
suggested_negatives: [],
// This should be heavily customized per industry
customize_per_industry: true
},
// ═══════════════════════════════════════════════════════════════════════════
// INFORMATIONAL & NON-COMMERCIAL INTENT
// ═══════════════════════════════════════════════════════════════════════════
ACADEMIC_RESEARCH: {
name: 'Academic Research',
description: 'User doing academic or scholarly research',
confidence: 0.90,
match_type: 'PHRASE',
level: 'CAMPAIGN',
patterns: [
/\b(case\s*study|journal|article|research\s*paper|thesis)\b/i,
/\b(academic|scholarly|peer[-\s]?review|citation|bibliography)\b/i,
/\b(statistics|data|study|survey\s*results)\b/i,
/\b(scholar|pubmed|jstor|google\s*scholar)\b/i
],
suggested_negatives: ['case study', 'journal', 'research paper', 'thesis', 'statistics']
},
HISTORICAL_INFORMATIONAL: {
name: 'Historical/Informational',
description: 'User seeking historical or encyclopedic info',
confidence: 0.85,
match_type: 'PHRASE',
level: 'CAMPAIGN',
patterns: [
/\b(history\s*(of)?|origin|invention|inventor|founded)\b/i,
/\b(timeline|evolution|development|first|oldest)\b/i,
/\b(biography|life\s*(of)?|who\s*(is|was))\b/i,
/\b(facts|trivia|interesting|did\s*you\s*know)\b/i
],
suggested_negatives: ['history of', 'who invented', 'facts about']
},
REGULATORY_LEGAL: {
name: 'Regulatory/Legal Research',
description: 'User researching regulations, not buying',
confidence: 0.85,
match_type: 'PHRASE',
level: 'CAMPAIGN',
patterns: [
/\b(regulation|regulatory|compliance|legal\s*requirement)\b/i,
/\b(law|legislation|statute|act\s*of|ordinance)\b/i,
/\b(fda|osha|epa|ftc|fcc|sec|hipaa|gdpr)\b/i,
/\b(policy|guideline|standard|protocol)\b/i
],
suggested_negatives: ['regulations', 'compliance', 'legal requirements']
},
MEDIA_ENTERTAINMENT: {
name: 'Media/Entertainment',
description: 'User looking for movies, TV, celebrities',
confidence: 0.95,
match_type: 'BROAD',
level: 'ACCOUNT',
patterns: [
/\b(movie|film|tv\s*show|series|episode|season)\b/i,
/\b(actor|actress|celebrity|star|famous)\b/i,
/\b(netflix|hulu|disney|hbo|streaming)\b/i,
/\b(soundtrack|trailer|cast|plot|spoiler)\b/i
],
suggested_negatives: ['movie', 'tv show', 'netflix', 'celebrity']
},
// ═══════════════════════════════════════════════════════════════════════════
// INTERNATIONAL MISMATCH (US-focused businesses)
// ═══════════════════════════════════════════════════════════════════════════
INTERNATIONAL_MISMATCH: {
name: 'International Mismatch',
description: 'User searching for non-US locations (when business is US-only)',
confidence: 0.90,
match_type: 'PHRASE',
level: 'CAMPAIGN',
patterns: [
/\b(uk|united\s*kingdom|britain|british|england|london|manchester)\b/i,
/\b(canada|canadian|toronto|vancouver|montreal|ontario)\b/i,
/\b(australia|australian|sydney|melbourne|brisbane)\b/i,
/\b(india|indian|delhi|mumbai|bangalore)\b/i,
/\b(europe|european|germany|france|spain|italy)\b/i,
/\b(nhs|nz|zealand)\b/i
],
suggested_negatives: ['uk', 'canada', 'australia', 'india', 'europe'],
// Only enable if business is US-focused
condition: function() {
var locs = CONFIG.BUSINESS_CONTEXT.locations_served;
return locs.length === 0 || locs.some(function(l) {
return l.toLowerCase().indexOf('us') !== -1 ||
['california', 'texas', 'florida', 'new york'].some(function(state) {
return l.toLowerCase().indexOf(state) !== -1;
});
});
}
},
// ═══════════════════════════════════════════════════════════════════════════
// COMMERCIAL INVESTIGATION - PROTECT THESE (not waste)
// ═══════════════════════════════════════════════════════════════════════════
COMMERCIAL_INVESTIGATION: {
name: 'Commercial Investigation',
description: 'High-intent buyer research - KEEP these',
confidence: 0.90,
action: 'PROTECT',
patterns: [
/\b(best|top\s*\d*|leading|recommended|highest\s*rated)\b/i,
/\b(near\s*me|nearby|close\s*to|in\s*my\s*area)\b/i,
/\b(book|schedule|appointment|consultation|get\s*started)\b/i,
/\b(cost|price|pricing|quote|estimate|rates?)\s*(of|for)?/i,
/\b(where\s*(to|can\s*i)|who\s*(offers?|provides?))\b/i
]
}
};
/******************************************************************************
* INDUSTRY-SPECIFIC PATTERNS
******************************************************************************/
var INDUSTRY_PATTERNS = {
medical: {
competitors: [
/\b(mayo\s*clinic|cleveland\s*clinic|johns\s*hopkins)\b/i,
/\b(webmd|healthline|drugs\.com|medicinenet)\b/i,
/\b(zocdoc|healthgrades|vitals)\b/i
],
wrong_intent: [
/\b(symptom|diagnos(is|e)|treat(ment)?|cure|side\s*effect)\b/i,
/\b(medicine|medication|drug|pill|prescription|otc)\b/i,
/\b(home\s*remed(y|ies)|natural\s*cure|alternative\s*medicine)\b/i,
/\b(medical\s*school|nursing\s*school|residency)\b/i,
/\b(icd[-\s]?\d+|cpt\s*code|billing\s*code)\b/i
],
protect: [
/\b(doctor|surgeon|specialist|consultation|appointment)\b/i,
/\b(clinic|office|practice|center)\s*(near|in)\b/i,
/\b(board\s*certified|fellowship|experienced)\b/i
]
},
medical_aesthetics: {
competitors: [
/\b(sono\s*bello|ideal\s*image|laser\s*away)\b/i,
/\b(cool\s*sculpt(ing)?|cryolipolysis|fat\s*freez)\b/i,
/\b(emsculpt|trusculpt|sculpsure|vanquish)\b/i,
/\b(morpheus8|bodytite|facetite|accutite)\b/i,
/\b(kybella|deoxycholic|fat\s*dissolving)\b/i,
/\b(botox|dysport|xeomin|jeuveau)\b/i,
/\b(juvederm|restylane|sculptra|radiesse)\b/i
],
wrong_intent: [
/\b(diy|at\s*home|home\s*device|amazon)\b/i,
/\b(before\s*and\s*after|results|pictures?|photos?)\b/i,
/\b(gone\s*wrong|botched|horror|nightmare)\b/i,
/\b(recovery|downtime|healing|bruising)\b/i
],
protect: [
/\b(consult(ation)?|assess(ment)?|quote)\b/i,
/\b(best|top|recommended|board\s*certified)\b/i,
/\b(near\s*me|in\s+[a-z]+|[a-z]+\s+area)\b/i
]
},
dental: {
competitors: [
/\b(aspen\s*dental|heartland|pacific\s*dental)\b/i,
/\b(smile\s*direct|byte|invisalign|candid)\b/i
],
wrong_intent: [
/\b(dental\s*school|hygienist\s*school|assistant\s*program)\b/i,
/\b(tooth\s*fairy|baby\s*teeth|teething)\b/i,
/\b(diy|at\s*home|natural\s*remedy)\b/i
],
protect: [
/\b(dentist|orthodontist|oral\s*surgeon)\b/i,
/\b(emergency|same\s*day|walk\s*in)\b/i,
/\b(appointment|consultation|exam)\b/i
]
},
legal: {
competitors: [
/\b(legalzoom|rocket\s*lawyer|avvo|nolo)\b/i,
/\b(findlaw|justia|martindale)\b/i,
/\b(1-?800-?\d+-?\d+|legal\s*shield)\b/i
],
wrong_intent: [
/\b(law\s*school|bar\s*exam|lsat|paralegal)\b/i,
/\b(pro\s*bono|free\s*legal|legal\s*aid|public\s*defender)\b/i,
/\b(self[-\s]?represent|pro\s*se|without\s*lawyer)\b/i,
/\b(template|form|sample|example)\b/i,
/\b(definition|meaning|what\s*(is|does))\b/i
],
protect: [
/\b(attorney|lawyer|law\s*firm|counsel)\b/i,
/\b(consultation|case\s*review|free\s*consult)\b/i,
/\b(hire|retain|represent)\b/i
]
},
ecommerce: {
competitors: [
/\b(amazon|ebay|walmart|target|alibaba|aliexpress)\b/i,
/\b(wayfair|overstock|etsy|wish)\b/i,
/\b(costco|sam's\s*club|bjs)\b/i
],
wrong_intent: [
/\b(wholesale|bulk|resale|dropship|private\s*label)\b/i,
/\b(manufacturer|factory|oem|supplier|vendor)\b/i,
/\b(how\s*to\s*make|diy|homemade|craft)\b/i,
/\b(recall|defect|dangerous|lawsuit)\b/i
],
protect: [
/\b(buy|order|purchase|shop|add\s*to\s*cart)\b/i,
/\b(price|cost|sale|deal|discount)\b/i,
/\b(shipping|delivery|in\s*stock)\b/i
]
},
saas: {
competitors: [
/\b(salesforce|hubspot|zendesk|freshdesk)\b/i,
/\b(slack|teams|zoom|google\s*workspace)\b/i,
/\b(asana|monday|trello|jira|notion)\b/i,
/\b(mailchimp|constant\s*contact|sendgrid)\b/i
],
wrong_intent: [
/\b(open\s*source|free\s*alternative|github|self[-\s]?hosted)\b/i,
/\b(api|sdk|documentation|developer|integration)\b/i,
/\b(vs|versus|comparison|alternative\s*to)\b/i,
/\b(tutorial|how\s*to\s*use|guide|course)\b/i
],
protect: [
/\b(pricing|demo|trial|sign\s*up|get\s*started)\b/i,
/\b(enterprise|business|team|pro\s*plan)\b/i,
/\b(features|benefits|solution)\b/i
]
},
local_services: {
competitors: [
/\b(thumbtack|angi(e's\s*list)?|homeadvisor|porch)\b/i,
/\b(yelp|google\s*local|nextdoor)\b/i,
/\b(task\s*rabbit|handy|home\s*depot)\b/i
],
wrong_intent: [
/\b(license|certification|training|course|school)\b/i,
/\b(franchise|start\s*a\s*business|how\s*to\s*become)\b/i,
/\b(diy|do\s*it\s*yourself|youtube|tutorial)\b/i,
/\b(parts|supplies|equipment|tools)\b/i
],
protect: [
/\b(near\s*me|call|contact|schedule|book)\b/i,
/\b(emergency|same\s*day|24[-\s]?hour|urgent)\b/i,
/\b(quote|estimate|free\s*estimate)\b/i
]
},
real_estate: {
competitors: [
/\b(zillow|redfin|realtor\.com|trulia)\b/i,
/\b(opendoor|offerpad|ibuyer|we\s*buy\s*houses)\b/i,
/\b(coldwell|remax|keller\s*williams|century\s*21)\b/i
],
wrong_intent: [
/\b(license|exam|course|school|how\s*to\s*become)\b/i,
/\b(foreclosure|tax\s*sale|auction|sheriff)\b/i,
/\b(rent|lease|apartment|roommate)\b/i,
/\b(fsbo|for\s*sale\s*by\s*owner|no\s*agent)\b/i
],
protect: [
/\b(agent|realtor|broker|sell\s*my\s*home)\b/i,
/\b(buy|purchase|looking\s*for)\b/i,
/\b(listing|market\s*analysis|cma)\b/i
]
},
finance: {
competitors: [
/\b(nerdwallet|bankrate|credit\s*karma|mint)\b/i,
/\b(turbotax|h&r\s*block|taxact)\b/i,
/\b(betterment|wealthfront|robinhood|acorns)\b/i
],
wrong_intent: [
/\b(calculator|template|spreadsheet|formula)\b/i,
/\b(how\s*to|diy|self[-\s]?file|free)\b/i,
/\b(definition|meaning|explain|what\s*is)\b/i,
/\b(course|certification|exam|license)\b/i
],
protect: [
/\b(advisor|planner|accountant|cpa)\b/i,
/\b(consultation|meeting|appointment)\b/i,
/\b(manage|invest|plan|strategy)\b/i
]
},
insurance: {
competitors: [
/\b(geico|progressive|state\s*farm|allstate)\b/i,
/\b(liberty\s*mutual|nationwide|farmers)\b/i,
/\b(lemonade|hippo|root|metromile)\b/i,
/\b(policygenius|the\s*zebra|gabi)\b/i
],
wrong_intent: [
/\b(license|exam|course|how\s*to\s*become)\b/i,
/\b(claim|lawsuit|sue|denied)\b/i,
/\b(cancel|refund|complaint)\b/i,
/\b(government|medicare|medicaid|obamacare)\b/i
],
protect: [
/\b(quote|rate|premium|coverage)\b/i,
/\b(agent|broker|compare)\b/i,
/\b(buy|get|need|looking\s*for)\b/i
]
},
wellness: {
competitors: [
/\b(equinox|orangetheory|soulcycle|peloton)\b/i,
/\b(lifetime|planet\s*fitness|gold's|anytime)\b/i,
/\b(beachbody|noom|weight\s*watchers|ww)\b/i,
/\b(mindbody|classpass|gympass)\b/i
],
wrong_intent: [
/\b(at\s*home|home\s*workout|youtube|free\s*workout)\b/i,
/\b(reddit|forum|community|app)\b/i,
/\b(certification|certified|trainer\s*course|how\s*to\s*become)\b/i,
/\b(diy|self[-\s]?taught|learn)\b/i
],
protect: [
/\b(near\s*me|class(es)?|schedule|book|membership)\b/i,
/\b(personal\s*trainer|coach|studio|gym)\b/i,
/\b(pricing|cost|trial|sign\s*up)\b/i
]
},
home_services: {
competitors: [
/\b(home\s*depot|lowe's|menards)\b/i,
/\b(thumbtack|angi|homeadvisor|porch)\b/i,
/\b(task\s*rabbit|handy|mr\s*handyman)\b/i,
/\b(servpro|servicemaster|stanley\s*steemer)\b/i
],
wrong_intent: [
/\b(diy|do\s*it\s*yourself|how\s*to|tutorial)\b/i,
/\b(parts|supplies|materials|tools)\b/i,
/\b(franchise|start\s*a\s*business|become\s*a)\b/i,
/\b(license|certification|training)\b/i
],
protect: [
/\b(near\s*me|service|repair|install(ation)?)\b/i,
/\b(emergency|same\s*day|24[-\s]?hour|urgent)\b/i,
/\b(estimate|quote|inspection|call)\b/i
]
},
automotive: {
competitors: [
/\b(autozone|o'reilly|advance\s*auto|napa)\b/i,
/\b(jiffy\s*lube|valvoline|midas|meineke)\b/i,
/\b(carmax|carvana|vroom|autotrader)\b/i,
/\b(kelley\s*blue|kbb|edmunds|cars\.com)\b/i
],
wrong_intent: [
/\b(diy|youtube|how\s*to|tutorial|manual)\b/i,
/\b(parts|oem|aftermarket|ebay)\b/i,
/\b(recall|lawsuit|lemon\s*law|complaint)\b/i,
/\b(mechanic\s*school|ase\s*certification|training)\b/i
],
protect: [
/\b(near\s*me|shop|dealer|service\s*center)\b/i,
/\b(repair|maintenance|oil\s*change|brake)\b/i,
/\b(appointment|estimate|inspection)\b/i
]
}
};
/******************************************************************************
* MAIN EXECUTION
******************************************************************************/
function main() {
var startTime = new Date();
log('INFO', '═══════════════════════════════════════════════════════════════');
log('INFO', 'ULTIMATE NEGATIVE KEYWORD ENGINE v2.0');
log('INFO', 'Mode: ' + CONFIG.MODE + (CONFIG.DRY_RUN ? ' [DRY RUN]' : ' [LIVE]'));
log('INFO', '═══════════════════════════════════════════════════════════════');
try {
// Initialize dynamic patterns (geographic, etc.)
initializePatterns();
var ss = initializeSpreadsheet();
var results = runEngine(startTime);
writeResults(ss, results);
sendNotifications(results, ss.getUrl(), startTime);
logFinalSummary(results, startTime);
} catch (error) {
handleFatalError(error, startTime);
}
}
/******************************************************************************
* ENGINE CORE
******************************************************************************/
function runEngine(startTime) {
var results = {
searchTerms: [],
findings: [],
ngrams: [],
conflicts: [],
crossCampaignGaps: [],
actionsApplied: [],
summary: {
termsAnalyzed: 0,
patternsMatched: 0,
definiteWaste: 0,
likelyWaste: 0,
potentialWaste: 0,
protected: 0,
negativesAdded: 0,
estimatedSavings: 0,
errors: 0
},
timing: {
collection: 0,
patternAnalysis: 0,
ngramMining: 0,
conflictDetection: 0,
crossCampaign: 0,
applyNegatives: 0,
sharedLists: 0,
total: 0
}
};
var phaseStart;
// Step 1: Collect search terms data
phaseStart = new Date();
log('INFO', 'Step 1/6: Collecting search terms data...');
results.searchTerms = collectSearchTerms(startTime);
results.summary.termsAnalyzed = results.searchTerms.length;
results.timing.collection = (new Date() - phaseStart) / 1000;
log('INFO', 'Collected ' + results.searchTerms.length + ' search terms (' + results.timing.collection.toFixed(1) + 's)');
if (results.searchTerms.length === 0) {
log('WARN', 'No search terms found matching criteria');
return results;
}
// Step 2: Pattern matching analysis
phaseStart = new Date();
log('INFO', 'Step 2/6: Running pattern analysis...');
results.findings = analyzePatterns(results.searchTerms, startTime);
results.timing.patternAnalysis = (new Date() - phaseStart) / 1000;
log('INFO', 'Pattern analysis found ' + results.findings.length + ' findings (' + results.timing.patternAnalysis.toFixed(1) + 's)');
// Step 3: N-gram mining (if in MINING mode or enough data)
if (CONFIG.MODE === 'MINING' || results.searchTerms.length > 500) {
phaseStart = new Date();
log('INFO', 'Step 3/6: Mining N-grams for new patterns...');
results.ngrams = mineNgrams(results.searchTerms, results.findings, startTime);
results.timing.ngramMining = (new Date() - phaseStart) / 1000;
log('INFO', 'N-gram mining found ' + results.ngrams.length + ' patterns (' + results.timing.ngramMining.toFixed(1) + 's)');
}
// Step 4: Conflict detection
phaseStart = new Date();
log('INFO', 'Step 4/6: Checking for conflicts...');
results.conflicts = detectConflicts(results.findings, startTime);
results.timing.conflictDetection = (new Date() - phaseStart) / 1000;
log('INFO', 'Found ' + results.conflicts.length + ' potential conflicts (' + results.timing.conflictDetection.toFixed(1) + 's)');
// Step 5: Cross-campaign analysis
if (CONFIG.MODE === 'AUDIT') {
phaseStart = new Date();
log('INFO', 'Step 5/6: Analyzing cross-campaign consistency...');
results.crossCampaignGaps = analyzeCrossCampaignGaps(startTime);
results.timing.crossCampaign = (new Date() - phaseStart) / 1000;
log('INFO', 'Found ' + results.crossCampaignGaps.length + ' coverage gaps (' + results.timing.crossCampaign.toFixed(1) + 's)');
}
// Step 6: Apply negatives (if in APPLY mode)
if (CONFIG.MODE === 'APPLY') {
phaseStart = new Date();
log('INFO', 'Step 6/7: Applying negatives' + (CONFIG.DRY_RUN ? ' [DRY RUN]' : '') + '...');
results.actionsApplied = applyNegatives(results.findings, results.conflicts, startTime);
results.summary.negativesAdded = results.actionsApplied.length;
results.timing.applyNegatives = (new Date() - phaseStart) / 1000;
// Step 7: Apply shared lists to campaigns
if (CONFIG.USE_SHARED_LISTS && !CONFIG.DRY_RUN) {
phaseStart = new Date();
log('INFO', 'Step 7/7: Applying shared lists to campaigns...');
results.sharedListsApplied = applySharedListsToCampaigns(results.findings, startTime);
results.timing.sharedLists = (new Date() - phaseStart) / 1000;
log('INFO', 'Applied ' + results.sharedListsApplied.length + ' shared lists (' + results.timing.sharedLists.toFixed(1) + 's)');
}
}
// Calculate summary stats
calculateSummary(results);
// Calculate total timing
results.timing.total = (new Date() - startTime) / 1000;
return results;
}
/******************************************************************************
* SEARCH TERM COLLECTION
******************************************************************************/
function collectSearchTerms(startTime) {
var terms = [];
// Build GAQL query
var dateRange = getDateRangeCondition();
var query = [
'SELECT',
' search_term_view.search_term,',
' campaign.name,',
' campaign.id,',
' ad_group.name,',
' ad_group.id,',
' metrics.impressions,',
' metrics.clicks,',
' metrics.cost_micros,',
' metrics.conversions,',
' metrics.conversions_value',
'FROM search_term_view',
'WHERE ' + dateRange,
' AND metrics.impressions >= ' + CONFIG.MIN_IMPRESSIONS,
' AND campaign.advertising_channel_type IN (' +
CONFIG.CAMPAIGN_TYPES.map(function(t) { return '"' + t + '"'; }).join(', ') + ')'
];
if (!CONFIG.INCLUDE_PAUSED) {
query.push(' AND campaign.status = "ENABLED"');
}
if (CONFIG.CAMPAIGN_NAME_CONTAINS) {
query.push(' AND campaign.name CONTAINS "' + CONFIG.CAMPAIGN_NAME_CONTAINS + '"');
}
query.push('ORDER BY metrics.cost_micros DESC');
query.push('LIMIT 50000');
try {
var report = AdsApp.report(query.join('\n'));
var rows = report.rows();
var count = 0;
while (rows.hasNext()) {
var row = rows.next();
var term = {
searchTerm: row['search_term_view.search_term'],
campaignName: row['campaign.name'],
campaignId: row['campaign.id'],
adGroupName: row['ad_group.name'],
adGroupId: row['ad_group.id'],
impressions: parseInt(row['metrics.impressions'], 10) || 0,
clicks: parseInt(row['metrics.clicks'], 10) || 0,
cost: (parseInt(row['metrics.cost_micros'], 10) || 0) / 1000000,
conversions: parseFloat(row['metrics.conversions']) || 0,
conversionValue: parseFloat(row['metrics.conversions_value']) || 0
};
// Apply campaign name exclusion filter
if (CONFIG.CAMPAIGN_NAME_EXCLUDES &&
term.campaignName.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_EXCLUDES.toLowerCase()) !== -1) {
continue;
}
// Calculate derived metrics
term.ctr = term.impressions > 0 ? (term.clicks / term.impressions) : 0;
term.cpc = term.clicks > 0 ? (term.cost / term.clicks) : 0;
term.cvr = term.clicks > 0 ? (term.conversions / term.clicks) : 0;
term.cpa = term.conversions > 0 ? (term.cost / term.conversions) : null;
terms.push(term);
count++;
if (count % 5000 === 0) {
log('DEBUG', 'Processed ' + count + ' search terms...');
checkTimeLimit(startTime);
}
}
} catch (e) {
log('ERROR', 'Failed to collect search terms: ' + e.message);
throw e;
}
return terms;
}
function getDateRangeCondition() {
var ranges = {
'LAST_7_DAYS': 'segments.date DURING LAST_7_DAYS',
'LAST_30_DAYS': 'segments.date DURING LAST_30_DAYS',
'LAST_90_DAYS': 'segments.date DURING LAST_90_DAYS',
'ALL_TIME': 'segments.date DURING ALL_TIME'
};
return ranges[CONFIG.DATE_RANGE] || ranges['LAST_30_DAYS'];
}
/******************************************************************************
* PATTERN ANALYSIS ENGINE
******************************************************************************/
function analyzePatterns(searchTerms, startTime) {
var findings = [];
var brandTermsLower = CONFIG.BUSINESS_CONTEXT.brand_terms.map(function(t) {
return t.toLowerCase();
});
var servicesLower = CONFIG.BUSINESS_CONTEXT.services_offered.map(function(t) {
return t.toLowerCase();
});
for (var i = 0; i < searchTerms.length; i++) {
var term = searchTerms[i];
var termLower = term.searchTerm.toLowerCase();
var finding = null;
// Check 1: Brand protection (NEVER negative)
if (containsAny(termLower, brandTermsLower)) {
finding = createFinding(term, 'PROTECT', 'brand_protection', 1.0,
'Contains brand term - protected');
finding.category = 'Protected';
findings.push(finding);
continue;
}
// Check 2: Service protection (NEVER negative core services)
if (containsAny(termLower, servicesLower)) {
// Still check for waste patterns combined with services
var wastePatternMatch = checkWastePatterns(termLower);
if (wastePatternMatch && wastePatternMatch.confidence >= 0.95) {
finding = createFinding(term, 'NEGATIVE_ADD', wastePatternMatch.name,
wastePatternMatch.confidence * 0.9, // Slightly reduce confidence
wastePatternMatch.description + ' (contains service but definite waste intent)');
finding.suggested_negative = wastePatternMatch.suggested_negatives[0];
finding.match_type = wastePatternMatch.match_type;
finding.level = wastePatternMatch.level;
} else {
finding = createFinding(term, 'PROTECT', 'service_protection', 0.90,
'Contains offered service - protected');
finding.category = 'Protected';
}
findings.push(finding);
continue;
}
// Check 3: Commercial investigation patterns (PROTECT)
if (matchesPatternGroup(termLower, PATTERNS.COMMERCIAL_INVESTIGATION.patterns)) {
finding = createFinding(term, 'PROTECT', 'commercial_investigation', 0.85,
'Commercial investigation intent - high-value traffic');
finding.category = 'Protected';
findings.push(finding);
continue;
}
// Check 4: Definite waste patterns
var wasteMatch = checkWastePatterns(termLower);
if (wasteMatch) {
finding = createFinding(term, 'NEGATIVE_ADD', wasteMatch.name,
wasteMatch.confidence, wasteMatch.description);
// Smart extraction: get root cause term, not full phrase
var rootTerm = getBestRootTerm(termLower, wasteMatch.name);
if (rootTerm) {
finding.suggested_negative = rootTerm;
} else if (CONFIG.EXTRACT_ROOT_TERMS) {
finding.suggested_negative = extractSmartNegative(term.searchTerm, wasteMatch);
} else {
finding.suggested_negative = wasteMatch.suggested_negatives[0];
}
finding.match_type = wasteMatch.match_type;
finding.level = wasteMatch.level;
finding.pattern_group = wasteMatch.name;
// Categorize by confidence
if (wasteMatch.confidence >= 0.95) {
finding.category = 'Definite Waste';
} else if (wasteMatch.confidence >= 0.80) {
finding.category = 'Likely Waste';
} else {
finding.category = 'Potential Waste';
}
findings.push(finding);
continue;
}
// Check 5: Industry-specific patterns
var industryMatch = checkIndustryPatterns(termLower);
if (industryMatch) {
finding = createFinding(term, industryMatch.action, industryMatch.name,
industryMatch.confidence, industryMatch.description);
if (industryMatch.action === 'NEGATIVE_ADD') {
finding.suggested_negative = extractKeyPhrase(termLower, industryMatch.match);
finding.match_type = 'PHRASE';
finding.level = 'CAMPAIGN';
finding.category = 'Likely Waste';
} else {
finding.category = 'Protected';
}
findings.push(finding);
continue;
}
// Check 6: Performance-based analysis (enough data required)
if (term.clicks >= CONFIG.MIN_CLICKS_FOR_PERF) {
var perfAnalysis = analyzePerformance(term);
if (perfAnalysis.isWaste) {
finding = createFinding(term, 'NEGATIVE_ADD', 'performance_waste',
perfAnalysis.confidence, perfAnalysis.rationale);
finding.category = 'Performance Waste';
finding.suggested_negative = term.searchTerm;
finding.match_type = 'EXACT';
finding.level = 'AD_GROUP';
findings.push(finding);
continue;
}
// Check 6b: Value-based (ROAS) analysis
if (term.conversionValue) {
var valueAnalysis = analyzeValuePerformance(term);
if (valueAnalysis.isWaste) {
finding = createFinding(term, 'NEGATIVE_ADD', 'value_waste',
valueAnalysis.confidence, valueAnalysis.rationale);
finding.category = 'ROAS Waste';
finding.suggested_negative = term.searchTerm;
finding.match_type = 'EXACT';
finding.level = 'AD_GROUP';
findings.push(finding);
continue;
}
}
}
// Check 7: Zero conversion high spend
if (term.conversions === 0 && term.cost >= CONFIG.MIN_COST_TO_FLAG * 10) {
finding = createFinding(term, 'MONITOR', 'high_spend_no_conversion', 0.60,
'High spend ($' + term.cost.toFixed(2) + ') with zero conversions - monitor');
finding.category = 'Watch';
findings.push(finding);
}
// Periodic time check
if (i % 1000 === 0) {
checkTimeLimit(startTime);
}
}
return findings;
}
function checkWastePatterns(termLower) {
var patternGroups = [
PATTERNS.EMPLOYMENT,
PATTERNS.NAVIGATION,
PATTERNS.EDUCATION_DIY,
PATTERNS.DOCUMENT_SEEKING,
PATTERNS.FREE_SEEKING,
PATTERNS.COMPLAINTS,
PATTERNS.COMPARISON_RESEARCH,
PATTERNS.PRICE_SENSITIVE,
PATTERNS.ACADEMIC_RESEARCH,
PATTERNS.HISTORICAL_INFORMATIONAL,
PATTERNS.REGULATORY_LEGAL,
PATTERNS.MEDIA_ENTERTAINMENT,
PATTERNS.INTERNATIONAL_MISMATCH,
PATTERNS.GEOGRAPHIC_MISMATCH
];
for (var i = 0; i < patternGroups.length; i++) {
var group = patternGroups[i];
// Check conditions
if (group.condition && !group.condition()) {
continue;
}
// Check exclusions
if (group.exclude_if_contains) {
var shouldExclude = group.exclude_if_contains.some(function(exc) {
return termLower.indexOf(exc) !== -1;
});
if (shouldExclude) continue;
}
// Check patterns
for (var j = 0; j < group.patterns.length; j++) {
if (group.patterns[j].test(termLower)) {
return group;
}
}
}
return null;
}
function checkIndustryPatterns(termLower) {
var industry = CONFIG.BUSINESS_CONTEXT.industry;
if (!industry || !INDUSTRY_PATTERNS[industry]) {
return null;
}
var industryConfig = INDUSTRY_PATTERNS[industry];
// Check protection patterns first
if (industryConfig.protect) {
for (var i = 0; i < industryConfig.protect.length; i++) {
if (industryConfig.protect[i].test(termLower)) {
return {
action: 'PROTECT',
name: 'industry_protection',
confidence: 0.85,
description: 'Industry-specific high-value pattern',
match: industryConfig.protect[i]
};
}
}
}
// Check wrong intent patterns
if (industryConfig.wrong_intent) {
for (var j = 0; j < industryConfig.wrong_intent.length; j++) {
if (industryConfig.wrong_intent[j].test(termLower)) {
return {
action: 'NEGATIVE_ADD',
name: 'industry_wrong_intent',
confidence: 0.80,
description: 'Industry-specific wrong intent pattern',
match: industryConfig.wrong_intent[j]
};
}
}
}
// Check competitor patterns
if (industryConfig.competitors) {
for (var k = 0; k < industryConfig.competitors.length; k++) {
if (industryConfig.competitors[k].test(termLower)) {
return {
action: 'NEGATIVE_ADD',
name: 'competitor',
confidence: 0.85,
description: 'Industry competitor term',
match: industryConfig.competitors[k]
};
}
}
}
return null;
}
function analyzePerformance(term) {
var result = {
isWaste: false,
confidence: 0,
rationale: ''
};
// Calculate break-even CPC if we have targets
var targetCpa = CONFIG.BUSINESS_CONTEXT.target_cpa;
var expectedCvr = CONFIG.BUSINESS_CONTEXT.expected_cvr;
if (targetCpa && expectedCvr) {
var maxAllowableCpc = targetCpa * expectedCvr;
var actualCpc = term.cpc;
var actualCpa = term.cpa;
if (actualCpa && actualCpa > targetCpa * 2) {
result.isWaste = true;
result.confidence = 0.85;
result.rationale = 'CPA $' + actualCpa.toFixed(2) + ' is ' +
(actualCpa / targetCpa).toFixed(1) + 'x target $' + targetCpa;
return result;
}
}
// Statistical significance check for zero conversions
if (term.conversions === 0 && term.clicks >= CONFIG.MIN_CLICKS_FOR_PERF) {
// Calculate probability of seeing 0 conversions if true CVR was expected
var cvr = expectedCvr || 0.03; // Default 3%
var probZero = Math.pow(1 - cvr, term.clicks);
if (probZero < 0.05) { // Less than 5% chance this is bad luck
result.isWaste = true;
result.confidence = 1 - probZero;
result.rationale = term.clicks + ' clicks, 0 conversions. ' +
'Probability of bad luck: ' + (probZero * 100).toFixed(1) + '%';
return result;
}
}
return result;
}
function createFinding(term, action, signal, confidence, rationale) {
return {
searchTerm: term.searchTerm,
campaignName: term.campaignName,
campaignId: term.campaignId,
adGroupName: term.adGroupName,
adGroupId: term.adGroupId,
impressions: term.impressions,
clicks: term.clicks,
cost: term.cost,
conversions: term.conversions,
ctr: term.ctr,
cpc: term.cpc,
cvr: term.cvr,
action: action,
signal: signal,
confidence: confidence,
rationale: rationale,
suggested_negative: null,
match_type: null,
level: null,
category: null
};
}
/******************************************************************************
* N-GRAM MINING ENGINE
******************************************************************************/
function mineNgrams(searchTerms, findings, startTime) {
var ngramStats = {};
var wasteTerms = new Set();
// Build set of waste terms
findings.forEach(function(f) {
if (f.action === 'NEGATIVE_ADD') {
wasteTerms.add(f.searchTerm.toLowerCase());
}
});
// Extract n-grams from all terms
searchTerms.forEach(function(term) {
var termLower = term.searchTerm.toLowerCase();
var isWaste = wasteTerms.has(termLower);
CONFIG.NGRAM_SIZES.forEach(function(n) {
var ngrams = extractNgrams(termLower, n);
ngrams.forEach(function(ngram) {
if (!ngramStats[ngram]) {
ngramStats[ngram] = {
ngram: ngram,
total: 0,
waste: 0,
totalCost: 0,
wasteCost: 0,
terms: []
};
}
ngramStats[ngram].total++;
ngramStats[ngram].totalCost += term.cost;
if (isWaste) {
ngramStats[ngram].waste++;
ngramStats[ngram].wasteCost += term.cost;
}
if (ngramStats[ngram].terms.length < 5) {
ngramStats[ngram].terms.push(term.searchTerm);
}
});
});
});
// Filter to significant patterns
var significantNgrams = [];
for (var ngram in ngramStats) {
var stats = ngramStats[ngram];
// Must appear in minimum number of terms
if (stats.total < CONFIG.NGRAM_MIN_OCCURRENCES) continue;
// Must have high waste ratio
var wasteRatio = stats.waste / stats.total;
if (wasteRatio < CONFIG.NGRAM_MIN_WASTE_RATIO) continue;
// Skip single character or very common words
if (ngram.length < 2) continue;
if (['the', 'and', 'for', 'to', 'of', 'in', 'a', 'is', 'it'].indexOf(ngram) !== -1) continue;
// Already in our pattern library?
var alreadyKnown = isKnownPattern(ngram);
significantNgrams.push({
ngram: ngram,
occurrences: stats.total,
wasteCount: stats.waste,
wasteRatio: wasteRatio,
estimatedWaste: stats.wasteCost,
isNewPattern: !alreadyKnown,
exampleTerms: stats.terms
});
}
// Sort by estimated waste (potential savings)
significantNgrams.sort(function(a, b) {
return b.estimatedWaste - a.estimatedWaste;
});
return significantNgrams.slice(0, 100); // Top 100 patterns
}
function extractNgrams(text, n) {
var words = text.split(/\s+/).filter(function(w) { return w.length > 0; });
var ngrams = [];
for (var i = 0; i <= words.length - n; i++) {
ngrams.push(words.slice(i, i + n).join(' '));
}
return ngrams;
}
function isKnownPattern(ngram) {
var ngramLower = ngram.toLowerCase();
// Check against all pattern groups
var allPatterns = [
PATTERNS.EMPLOYMENT,
PATTERNS.NAVIGATION,
PATTERNS.EDUCATION_DIY,
PATTERNS.DOCUMENT_SEEKING,
PATTERNS.FREE_SEEKING,
PATTERNS.COMPLAINTS,
PATTERNS.COMPARISON_RESEARCH,
PATTERNS.PRICE_SENSITIVE,
PATTERNS.ACADEMIC_RESEARCH,
PATTERNS.HISTORICAL_INFORMATIONAL,
PATTERNS.REGULATORY_LEGAL,
PATTERNS.MEDIA_ENTERTAINMENT,
PATTERNS.INTERNATIONAL_MISMATCH
];
for (var i = 0; i < allPatterns.length; i++) {
var group = allPatterns[i];
if (group.suggested_negatives) {
for (var j = 0; j < group.suggested_negatives.length; j++) {
if (ngramLower === group.suggested_negatives[j].toLowerCase()) {
return true;
}
}
}
}
return false;
}
/******************************************************************************
* CONFLICT DETECTION
******************************************************************************/
function detectConflicts(findings, startTime) {
var conflicts = [];
var negativesToAdd = findings.filter(function(f) {
return f.action === 'NEGATIVE_ADD' && f.suggested_negative;
});
if (negativesToAdd.length === 0) return conflicts;
// Get existing positive keywords
var positiveKeywords = getPositiveKeywords(startTime);
// Check each proposed negative against positives
negativesToAdd.forEach(function(finding) {
var negLower = finding.suggested_negative.toLowerCase();
positiveKeywords.forEach(function(positive) {
var posLower = positive.keyword.toLowerCase();
// Check for exact match
if (posLower === negLower) {
conflicts.push({
type: 'EXACT_MATCH',
severity: 'HIGH',
proposedNegative: finding.suggested_negative,
matchType: finding.match_type,
conflictsWith: positive.keyword,
campaign: positive.campaignName,
adGroup: positive.adGroupName,
recommendation: 'DO NOT ADD - would block active keyword'
});
return;
}
// Check for phrase match containment
if (finding.match_type === 'PHRASE' || finding.match_type === 'BROAD') {
if (posLower.indexOf(negLower) !== -1) {
conflicts.push({
type: 'PHRASE_CONTAINMENT',
severity: 'MEDIUM',
proposedNegative: finding.suggested_negative,
matchType: finding.match_type,
conflictsWith: positive.keyword,
campaign: positive.campaignName,
adGroup: positive.adGroupName,
recommendation: 'REVIEW - may block substring of active keyword'
});
}
}
});
});
return conflicts;
}
function getPositiveKeywords(startTime) {
var keywords = [];
try {
var query = [
'SELECT',
' ad_group_criterion.keyword.text,',
' ad_group_criterion.keyword.match_type,',
' campaign.name,',
' ad_group.name',
'FROM keyword_view',
'WHERE ad_group_criterion.status != "REMOVED"',
' AND campaign.status = "ENABLED"',
' AND ad_group.status = "ENABLED"'
].join('\n');
var report = AdsApp.report(query);
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
keywords.push({
keyword: row['ad_group_criterion.keyword.text'],
matchType: row['ad_group_criterion.keyword.match_type'],
campaignName: row['campaign.name'],
adGroupName: row['ad_group.name']
});
}
} catch (e) {
log('WARN', 'Could not fetch positive keywords: ' + e.message);
}
return keywords;
}
/******************************************************************************
* CROSS-CAMPAIGN ANALYSIS
******************************************************************************/
function analyzeCrossCampaignGaps(startTime) {
var gaps = [];
var negativesByCampaign = {};
var campaignList = [];
// Collect negatives by campaign
try {
var query = [
'SELECT',
' campaign.name,',
' campaign.id,',
' campaign_criterion.keyword.text,',
' campaign_criterion.keyword.match_type',
'FROM campaign_criterion',
'WHERE campaign_criterion.type = "KEYWORD"',
' AND campaign_criterion.negative = TRUE',
' AND campaign.status = "ENABLED"'
].join('\n');
var report = AdsApp.report(query);
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var campaignName = row['campaign.name'];
var keyword = row['campaign_criterion.keyword.text'].toLowerCase();
if (!negativesByCampaign[campaignName]) {
negativesByCampaign[campaignName] = new Set();
campaignList.push(campaignName);
}
negativesByCampaign[campaignName].add(keyword);
}
} catch (e) {
log('WARN', 'Could not analyze cross-campaign gaps: ' + e.message);
return gaps;
}
// Find negatives that appear in multiple campaigns but not all
var allNegatives = {};
for (var campaign in negativesByCampaign) {
negativesByCampaign[campaign].forEach(function(neg) {
if (!allNegatives[neg]) {
allNegatives[neg] = [];
}
allNegatives[neg].push(campaign);
});
}
// Find gaps (negative in 2+ campaigns but missing from others)
var totalCampaigns = campaignList.length;
var minCampaignsForGap = Math.max(2, Math.floor(totalCampaigns * 0.3));
for (var negative in allNegatives) {
var campaignsWithNeg = allNegatives[negative];
var campaignsWithout = campaignList.filter(function(c) {
return campaignsWithNeg.indexOf(c) === -1;
});
if (campaignsWithNeg.length >= minCampaignsForGap && campaignsWithout.length > 0) {
gaps.push({
negative: negative,
coverage: ((campaignsWithNeg.length / totalCampaigns) * 100).toFixed(1) + '%',
campaignsWithCount: campaignsWithNeg.length,
campaignsWithoutCount: campaignsWithout.length,
campaignsWith: campaignsWithNeg.slice(0, 3).join(', ') +
(campaignsWithNeg.length > 3 ? '...' : ''),
campaignsWithout: campaignsWithout.slice(0, 3).join(', ') +
(campaignsWithout.length > 3 ? '...' : ''),
recommendation: 'Consider adding to: ' + campaignsWithout.join(', ')
});
}
}
// Sort by number of campaigns missing
gaps.sort(function(a, b) {
return b.campaignsWithoutCount - a.campaignsWithoutCount;
});
return gaps;
}
/******************************************************************************
* APPLY NEGATIVES
******************************************************************************/
function applyNegatives(findings, conflicts, startTime) {
var applied = [];
// Build set of conflicting negatives
var conflictSet = new Set();
conflicts.filter(function(c) { return c.severity === 'HIGH'; }).forEach(function(c) {
conflictSet.add(c.proposedNegative.toLowerCase());
});
// Filter to actionable findings
var toApply = findings.filter(function(f) {
if (f.action !== 'NEGATIVE_ADD') return false;
if (!f.suggested_negative) return false;
if (f.confidence < CONFIG.MIN_CONFIDENCE) return false;
if (conflictSet.has(f.suggested_negative.toLowerCase())) return false;
return true;
});
// Sort by cost (highest potential savings first)
toApply.sort(function(a, b) { return b.cost - a.cost; });
// Apply up to limit
var count = 0;
for (var i = 0; i < toApply.length && count < CONFIG.MAX_NEGATIVES_PER_RUN; i++) {
var finding = toApply[i];
try {
var success = addNegativeKeyword(finding);
if (success) {
applied.push({
negative: finding.suggested_negative,
matchType: finding.match_type,
level: finding.level,
campaign: finding.campaignName,
adGroup: finding.adGroupName,
reason: finding.signal,
estimatedSavings: finding.cost
});
count++;
}
} catch (e) {
log('WARN', 'Failed to add negative: ' + finding.suggested_negative + ' - ' + e.message);
}
if (count % 10 === 0) {
checkTimeLimit(startTime);
}
}
return applied;
}
function addNegativeKeyword(finding) {
if (CONFIG.DRY_RUN) {
log('DEBUG', '[DRY RUN] Would add: ' + finding.suggested_negative + ' (' + finding.match_type + ')');
return true;
}
var formatted = formatNegative(finding.suggested_negative, finding.match_type);
try {
if (finding.level === 'ACCOUNT' || finding.level === 'CAMPAIGN') {
// Add at campaign level
var campaigns = AdsApp.campaigns()
.withCondition('Name = "' + finding.campaignName.replace(/"/g, '\\"') + '"')
.get();
if (campaigns.hasNext()) {
var campaign = campaigns.next();
campaign.createNegativeKeyword(formatted);
return true;
}
} else {
// Add at ad group level
var adGroups = AdsApp.adGroups()
.withCondition('CampaignName = "' + finding.campaignName.replace(/"/g, '\\"') + '"')
.withCondition('Name = "' + finding.adGroupName.replace(/"/g, '\\"') + '"')
.get();
if (adGroups.hasNext()) {
var adGroup = adGroups.next();
adGroup.createNegativeKeyword(formatted);
return true;
}
}
} catch (e) {
log('ERROR', 'Failed to add negative keyword: ' + e.message);
return false;
}
return false;
}
function formatNegative(keyword, matchType) {
switch (matchType) {
case 'EXACT':
return '[' + keyword + ']';
case 'PHRASE':
return '"' + keyword + '"';
default: // BROAD
return keyword;
}
}
/******************************************************************************
* SUMMARY & REPORTING
******************************************************************************/
function calculateSummary(results) {
results.findings.forEach(function(f) {
results.summary.patternsMatched++;
if (f.category === 'Definite Waste') {
results.summary.definiteWaste++;
results.summary.estimatedSavings += f.cost;
} else if (f.category === 'Likely Waste') {
results.summary.likelyWaste++;
results.summary.estimatedSavings += f.cost * 0.8;
} else if (f.category === 'Potential Waste') {
results.summary.potentialWaste++;
results.summary.estimatedSavings += f.cost * 0.5;
} else if (f.category === 'Protected') {
results.summary.protected++;
}
});
// Calculate Account Health Score
results.summary.healthScore = calculateHealthScore(results);
}
/**
* Calculate Account Negative Keyword Health Score (0-100)
* Higher score = better negative keyword hygiene
*/
function calculateHealthScore(results) {
var score = 100;
var totalTerms = results.summary.termsAnalyzed || 1;
var totalCost = results.searchTerms.reduce(function(sum, t) { return sum + t.cost; }, 0) || 1;
// Deductions based on waste found
var wasteRatio = (results.summary.definiteWaste + results.summary.likelyWaste) / totalTerms;
score -= wasteRatio * 30; // Up to -30 for high waste ratio
// Deductions based on cost waste
var costWasteRatio = results.summary.estimatedSavings / totalCost;
score -= costWasteRatio * 40; // Up to -40 for high cost waste
// Deductions for cross-campaign gaps
var gapPenalty = Math.min(results.crossCampaignGaps.length * 2, 15);
score -= gapPenalty; // Up to -15 for coverage gaps
// Deductions for conflicts
var conflictPenalty = Math.min(results.conflicts.length * 3, 15);
score -= conflictPenalty; // Up to -15 for conflicts
// Ensure score is between 0-100
score = Math.max(0, Math.min(100, Math.round(score)));
// Determine grade
var grade;
if (score >= 90) grade = 'A';
else if (score >= 80) grade = 'B';
else if (score >= 70) grade = 'C';
else if (score >= 60) grade = 'D';
else grade = 'F';
return {
score: score,
grade: grade,
interpretation: getHealthInterpretation(score)
};
}
function getHealthInterpretation(score) {
if (score >= 90) return 'Excellent! Your negative keyword coverage is comprehensive.';
if (score >= 80) return 'Good. Minor improvements could reduce wasted spend.';
if (score >= 70) return 'Fair. Review the findings to plug waste leaks.';
if (score >= 60) return 'Needs Work. Significant optimization opportunities exist.';
return 'Critical. Immediate action needed to stop wasted spend.';
}
/**
* Get scheduling recommendations based on account health and size
*/
function getSchedulingRecommendation(healthScore, termsAnalyzed) {
var result = {
frequency: '',
bestTime: 'Monday morning (start of week)',
rationale: ''
};
// Determine frequency based on health score
if (healthScore < 60) {
result.frequency = 'Daily for 2 weeks, then Weekly';
result.rationale = 'Critical health score requires aggressive monitoring until stabilized.';
} else if (healthScore < 80) {
result.frequency = 'Weekly';
result.rationale = 'Regular monitoring needed to catch waste patterns early.';
} else {
result.frequency = 'Bi-weekly or Monthly';
result.rationale = 'Account is healthy. Periodic checks are sufficient.';
}
// Adjust for account size
if (termsAnalyzed > 10000) {
result.frequency += ' (large account)';
result.bestTime = 'Sunday night (off-peak)';
}
return result;
}
/******************************************************************************
* OUTPUT FUNCTIONS
******************************************************************************/
function initializeSpreadsheet() {
var ss;
if (!CONFIG.SPREADSHEET_URL || CONFIG.SPREADSHEET_URL === 'CREATE_NEW') {
ss = SpreadsheetApp.create('PPC.io Ultimate Negatives - ' +
AdsApp.currentAccount().getName() + ' - ' +
formatDate(new Date()));
log('INFO', 'Created spreadsheet: ' + ss.getUrl());
} else {
ss = SpreadsheetApp.openByUrl(CONFIG.SPREADSHEET_URL);
}
return ss;
}
function writeResults(ss, results) {
// 1. Summary sheet
writeSummarySheet(ss, results);
// 2. Findings (main recommendations)
if (results.findings.length > 0) {
writeDataSheet(ss, '2. Findings', results.findings.filter(function(f) {
return f.action === 'NEGATIVE_ADD';
}), [
'searchTerm', 'category', 'confidence', 'suggested_negative', 'match_type',
'level', 'campaignName', 'adGroupName', 'impressions', 'clicks', 'cost',
'conversions', 'rationale'
]);
}
// 3. Protected terms
var protected = results.findings.filter(function(f) { return f.action === 'PROTECT'; });
if (protected.length > 0) {
writeDataSheet(ss, '3. Protected Terms', protected, [
'searchTerm', 'signal', 'confidence', 'campaignName', 'impressions',
'clicks', 'cost', 'conversions', 'rationale'
]);
}
// 4. N-gram discoveries
if (results.ngrams.length > 0) {
writeDataSheet(ss, '4. Pattern Discovery', results.ngrams, [
'ngram', 'occurrences', 'wasteCount', 'wasteRatio', 'estimatedWaste',
'isNewPattern', 'exampleTerms'
]);
}
// 5. Conflicts
if (results.conflicts.length > 0) {
writeDataSheet(ss, '5. Conflicts', results.conflicts, [
'type', 'severity', 'proposedNegative', 'matchType', 'conflictsWith',
'campaign', 'adGroup', 'recommendation'
]);
}
// 6. Cross-campaign gaps
if (results.crossCampaignGaps.length > 0) {
writeDataSheet(ss, '6. Coverage Gaps', results.crossCampaignGaps, [
'negative', 'coverage', 'campaignsWithCount', 'campaignsWithoutCount',
'campaignsWith', 'campaignsWithout', 'recommendation'
]);
}
// 7. Budget Impact Projection
if (results.summary.estimatedSavings > 0) {
writeBudgetImpactSheet(ss, results);
}
// 8. Applied actions
if (results.actionsApplied.length > 0) {
writeDataSheet(ss, '8. Actions Applied', results.actionsApplied, [
'negative', 'matchType', 'level', 'campaign', 'adGroup', 'reason',
'estimatedSavings'
]);
}
// 9. Term Clusters (group similar waste terms)
if (CONFIG.ENABLE_CLUSTERING && results.findings.length > 0) {
var clusters = clusterSearchTerms(results.findings);
if (clusters.length > 0) {
writeClustersSheet(ss, clusters);
log('INFO', 'Generated ' + clusters.length + ' term clusters');
}
}
// 10. Audit Log (for undo/rollback)
if (CONFIG.ENABLE_AUDIT_LOG && results.actionsApplied.length > 0) {
writeAuditLog(ss, results);
}
// 11. Copy-Paste Ready Lists (for Google Ads Editor)
writeCopyPasteSheet(ss, results);
}
function writeSummarySheet(ss, results) {
var sheet = ss.getSheetByName('1. Summary');
if (!sheet) {
sheet = ss.insertSheet('1. Summary', 0);
}
sheet.clear();
var healthScore = results.summary.healthScore || { score: 0, grade: 'N/A', interpretation: '' };
var data = [
['ULTIMATE NEGATIVE KEYWORD ENGINE', ''],
['Generated by PPC.io Script Engine', ''],
['The Best Negative Keyword Script in the World', ''],
['', ''],
['Account: ' + AdsApp.currentAccount().getName(), ''],
['Date: ' + new Date().toISOString(), ''],
['Mode: ' + CONFIG.MODE + (CONFIG.DRY_RUN ? ' [DRY RUN]' : ' [LIVE]'), ''],
['', ''],
['═══════════════════════════════════════════════════════════════', ''],
['🏆 ACCOUNT HEALTH SCORE', ''],
['═══════════════════════════════════════════════════════════════', ''],
['', ''],
['Score: ' + healthScore.score + '/100', 'Grade: ' + healthScore.grade],
[healthScore.interpretation, ''],
['', ''],
['═══════════════════════════════════════════════════════════════', ''],
['EXECUTIVE SUMMARY', ''],
['═══════════════════════════════════════════════════════════════', ''],
['Search Terms Analyzed', results.summary.termsAnalyzed],
['Patterns Matched', results.summary.patternsMatched],
['', ''],
['Definite Waste Found', results.summary.definiteWaste],
['Likely Waste Found', results.summary.likelyWaste],
['Potential Waste Found', results.summary.potentialWaste],
['Protected Terms', results.summary.protected],
['', ''],
['Negatives Added', results.summary.negativesAdded],
['Estimated Savings', '$' + results.summary.estimatedSavings.toFixed(2)],
['', ''],
['═══════════════════════════════════════════════════════════════', ''],
['TOP WASTE CATEGORIES', ''],
['═══════════════════════════════════════════════════════════════', '']
];
// Count by category
var categoryCounts = {};
results.findings.forEach(function(f) {
if (f.action === 'NEGATIVE_ADD') {
var cat = f.signal || 'unknown';
categoryCounts[cat] = (categoryCounts[cat] || 0) + 1;
}
});
var sortedCategories = Object.keys(categoryCounts).sort(function(a, b) {
return categoryCounts[b] - categoryCounts[a];
});
sortedCategories.slice(0, 10).forEach(function(cat) {
data.push([cat.replace(/_/g, ' '), categoryCounts[cat] + ' terms']);
});
data.push(['', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
data.push(['NEXT STEPS', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
if (CONFIG.MODE === 'DISCOVER') {
data.push(['1. Review "Findings" sheet for waste patterns', '']);
data.push(['2. Check "Conflicts" sheet before applying', '']);
data.push(['3. Review "Pattern Discovery" for new insights', '']);
data.push(['4. Change MODE to "APPLY" and DRY_RUN to false', '']);
data.push(['5. Schedule weekly for continuous optimization', '']);
} else if (CONFIG.MODE === 'APPLY') {
data.push(['1. ' + results.summary.negativesAdded + ' negatives applied', '']);
data.push(['2. Monitor search terms report for impact', '']);
data.push(['3. Review protected terms to ensure no false positives', '']);
}
data.push(['', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
data.push(['🎯 TOP 10 QUICK WINS', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
data.push(['Add these negatives FIRST for maximum impact:', '']);
data.push(['', '']);
// Get top 10 negatives by cost savings
var quickWins = results.findings
.filter(function(f) { return f.action === 'NEGATIVE_ADD' && f.suggested_negative; })
.sort(function(a, b) { return b.cost - a.cost; })
.slice(0, 10);
quickWins.forEach(function(win, idx) {
var matchSymbol = win.match_type === 'EXACT' ? '[exact]' :
win.match_type === 'PHRASE' ? '"phrase"' : 'broad';
data.push([
(idx + 1) + '. ' + win.suggested_negative + ' ' + matchSymbol,
'$' + win.cost.toFixed(2) + ' waste'
]);
});
if (quickWins.length === 0) {
data.push(['No high-confidence waste patterns found!', '🎉']);
}
data.push(['', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
data.push(['AI ANALYSIS PROMPTS', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
data.push(['Copy the Findings data into Claude with these prompts:', '']);
data.push(['', '']);
data.push(['"Analyze these search terms and validate the waste classifications"', '']);
data.push(['"Identify any additional patterns I should add as negatives"', '']);
data.push(['"What\'s the optimal negative keyword strategy for this account?"', '']);
data.push(['', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
data.push(['📅 SCHEDULING RECOMMENDATIONS', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
data.push(['', '']);
// Provide scheduling recommendations based on health score
var scheduleRec = getSchedulingRecommendation(healthScore.score, results.summary.termsAnalyzed);
data.push(['Recommended Frequency: ' + scheduleRec.frequency, '']);
data.push(['Best Time: ' + scheduleRec.bestTime, '']);
data.push(['Rationale: ' + scheduleRec.rationale, '']);
data.push(['', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
data.push(['✅ GETTING STARTED CHECKLIST', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
data.push(['', '']);
data.push(['1. CONFIGURE YOUR SETTINGS:', '']);
data.push([' □ Set SPREADSHEET_URL to your output sheet', '']);
data.push([' □ Choose your INDUSTRY (e.g., "medical", "legal", "ecommerce")', '']);
data.push([' □ Set DATE_RANGE (default: LAST_30_DAYS)', '']);
data.push([' □ Add PROTECTED_TERMS for your brand/products', '']);
data.push(['', '']);
data.push(['2. TEST IN SAFE MODE (RECOMMENDED):', '']);
data.push([' □ Keep DRY_RUN: true (default)', '']);
data.push([' □ Run the script and review Findings sheet', '']);
data.push([' □ Check Conflicts sheet for potential issues', '']);
data.push([' □ Review Copy-Paste Ready sheet format', '']);
data.push(['', '']);
data.push(['3. REVIEW YOUR RESULTS:', '']);
data.push([' □ Check Top 10 Quick Wins above', '']);
data.push([' □ Review Findings sheet for all recommendations', '']);
data.push([' □ Examine Pattern Discovery for n-gram insights', '']);
data.push([' □ Check Budget Impact for projected savings', '']);
data.push(['', '']);
data.push(['4. APPLY NEGATIVES (WHEN READY):', '']);
data.push([' □ Option A: Copy from Copy-Paste Ready sheet', '']);
data.push([' □ Option B: Set DRY_RUN: false for auto-apply', '']);
data.push([' □ Always verify Conflicts sheet first!', '']);
data.push(['', '']);
data.push(['5. SCHEDULE ONGOING RUNS:', '']);
data.push([' □ Set up schedule per recommendations above', '']);
data.push([' □ Configure EMAIL_RECIPIENTS for notifications', '']);
data.push(['', '']);
data.push(['', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
data.push(['🔧 TROUBLESHOOTING', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
data.push(['', '']);
data.push(['No results? Check: DATE_RANGE, CAMPAIGN_NAME_CONTAINS filters', '']);
data.push(['Too many results? Increase MIN_CONFIDENCE or MIN_COST_TO_FLAG', '']);
data.push(['Missing patterns? Add custom patterns via CUSTOM_PATTERNS_SHEET_URL', '']);
data.push(['Conflicts? Review the Conflicts sheet before applying negatives', '']);
data.push(['Slow execution? Reduce DATE_RANGE or use CAMPAIGN_NAME_CONTAINS', '']);
// Performance metrics section
data.push(['', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
data.push(['⏱️ PERFORMANCE METRICS', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
data.push(['', '']);
if (results.timing) {
data.push(['Total Execution Time:', results.timing.total.toFixed(1) + ' seconds']);
data.push(['', '']);
data.push(['Phase Breakdown:', '']);
data.push([' • Data Collection:', results.timing.collection.toFixed(1) + 's']);
data.push([' • Pattern Analysis:', results.timing.patternAnalysis.toFixed(1) + 's']);
if (results.timing.ngramMining > 0) {
data.push([' • N-gram Mining:', results.timing.ngramMining.toFixed(1) + 's']);
}
data.push([' • Conflict Detection:', results.timing.conflictDetection.toFixed(1) + 's']);
if (results.timing.crossCampaign > 0) {
data.push([' • Cross-Campaign Analysis:', results.timing.crossCampaign.toFixed(1) + 's']);
}
if (results.timing.applyNegatives > 0) {
data.push([' • Apply Negatives:', results.timing.applyNegatives.toFixed(1) + 's']);
}
if (results.timing.sharedLists > 0) {
data.push([' • Shared Lists:', results.timing.sharedLists.toFixed(1) + 's']);
}
data.push(['', '']);
// Processing rate
var rate = results.summary.termsAnalyzed / (results.timing.total || 1);
data.push(['Processing Rate:', Math.round(rate) + ' terms/second']);
// Efficiency metrics
var findingsPerSecond = results.findings.length / (results.timing.patternAnalysis || 1);
data.push(['Pattern Efficiency:', Math.round(findingsPerSecond) + ' findings/second']);
}
sheet.getRange(1, 1, data.length, 2).setValues(data);
sheet.getRange(1, 1).setFontWeight('bold').setFontSize(16);
sheet.getRange(3, 1).setFontStyle('italic');
sheet.setColumnWidth(1, 400);
sheet.setColumnWidth(2, 200);
// Color the key stats
sheet.getRange(15, 1, 3, 2).setBackground('#f8d7da'); // Waste rows
sheet.getRange(18, 1, 1, 2).setBackground('#d4edda'); // Protected row
sheet.getRange(21, 1, 1, 2).setBackground('#cce5ff'); // Savings row
}
function writeDataSheet(ss, sheetName, data, columns) {
if (!data || data.length === 0) return;
var sheet = ss.getSheetByName(sheetName);
if (!sheet) {
sheet = ss.insertSheet(sheetName);
}
sheet.clear();
// Headers
var headers = columns.map(function(col) {
return col.replace(/([A-Z])/g, ' $1').replace(/^./, function(str) {
return str.toUpperCase();
}).trim();
});
sheet.getRange(1, 1, 1, headers.length).setValues([headers]).setFontWeight('bold');
sheet.setFrozenRows(1);
// Data rows
var rows = data.map(function(item) {
return columns.map(function(col) {
var val = item[col];
if (Array.isArray(val)) return val.join(', ');
if (typeof val === 'number') return Math.round(val * 100) / 100;
return val !== undefined && val !== null ? val : '';
});
});
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);
}
// Color code severity/category columns
var catCol = columns.indexOf('category') + 1;
if (catCol > 0 && rows.length > 0) {
var range = sheet.getRange(2, catCol, rows.length, 1);
var values = range.getValues();
var colors = values.map(function(row) {
if (row[0] === 'Definite Waste') return ['#f8d7da'];
if (row[0] === 'Likely Waste') return ['#fff3cd'];
if (row[0] === 'Potential Waste') return ['#d1ecf1'];
if (row[0] === 'Protected') return ['#d4edda'];
return ['#ffffff'];
});
range.setBackgrounds(colors);
}
// Auto-resize columns (up to 8)
for (var col = 1; col <= Math.min(columns.length, 8); col++) {
sheet.autoResizeColumn(col);
}
}
/**
* Write budget impact projection sheet
*/
function writeBudgetImpactSheet(ss, results) {
var sheet = ss.getSheetByName('7. Budget Impact');
if (!sheet) {
sheet = ss.insertSheet('7. Budget Impact');
}
sheet.clear();
var monthlySavings = results.summary.estimatedSavings;
// Project savings over time (assuming monthly waste patterns repeat)
var dateRange = CONFIG.DATE_RANGE;
var multiplier = 1;
if (dateRange === 'LAST_7_DAYS') multiplier = 4.3; // Scale to monthly
else if (dateRange === 'LAST_30_DAYS') multiplier = 1;
else if (dateRange === 'LAST_90_DAYS') multiplier = 0.33;
var projectedMonthly = monthlySavings * multiplier;
var data = [
['BUDGET IMPACT PROJECTION', ''],
['═══════════════════════════════════════', ''],
['', ''],
['Based on ' + CONFIG.DATE_RANGE.replace(/_/g, ' ').toLowerCase() + ' data', ''],
['', ''],
['CURRENT PERIOD WASTE IDENTIFIED', '$' + monthlySavings.toFixed(2)],
['', ''],
['═══════════════════════════════════════', ''],
['PROJECTED SAVINGS IF FIXED', ''],
['═══════════════════════════════════════', ''],
['', ''],
['Monthly Savings', '$' + projectedMonthly.toFixed(2)],
['Quarterly Savings', '$' + (projectedMonthly * 3).toFixed(2)],
['Annual Savings', '$' + (projectedMonthly * 12).toFixed(2)],
['', ''],
['═══════════════════════════════════════', ''],
['SAVINGS BREAKDOWN BY CATEGORY', ''],
['═══════════════════════════════════════', '']
];
// Calculate savings by category
var categoryWaste = {};
results.findings.forEach(function(f) {
if (f.action === 'NEGATIVE_ADD' && f.cost > 0) {
var cat = f.signal || 'other';
categoryWaste[cat] = (categoryWaste[cat] || 0) + f.cost;
}
});
var sortedCategories = Object.keys(categoryWaste).sort(function(a, b) {
return categoryWaste[b] - categoryWaste[a];
});
sortedCategories.forEach(function(cat) {
var waste = categoryWaste[cat];
var pct = ((waste / monthlySavings) * 100).toFixed(1);
data.push([cat.replace(/_/g, ' '), '$' + waste.toFixed(2) + ' (' + pct + '%)']);
});
data.push(['', '']);
data.push(['═══════════════════════════════════════', '']);
data.push(['ROI CALCULATION', '']);
data.push(['═══════════════════════════════════════', '']);
data.push(['', '']);
data.push(['Time to implement: ~1 hour', '']);
data.push(['Monthly return: $' + projectedMonthly.toFixed(2), '']);
data.push(['Hourly ROI: $' + projectedMonthly.toFixed(2) + '/hr', '']);
data.push(['Annual ROI: ' + ((projectedMonthly * 12) / 100 * 100).toFixed(0) + '%', '(assuming $100 implementation cost)']);
sheet.getRange(1, 1, data.length, 2).setValues(data);
sheet.getRange(1, 1).setFontWeight('bold').setFontSize(14);
sheet.setColumnWidth(1, 300);
sheet.setColumnWidth(2, 200);
// Highlight the key numbers
sheet.getRange(12, 2, 3, 1).setFontWeight('bold').setBackground('#d4edda');
}
/**
* Write audit log for undo/rollback capability
*/
function writeAuditLog(ss, results) {
if (!CONFIG.ENABLE_AUDIT_LOG) return;
if (!results.actionsApplied || results.actionsApplied.length === 0) return;
var sheet = ss.getSheetByName(CONFIG.AUDIT_LOG_SHEET_NAME);
if (!sheet) {
sheet = ss.insertSheet(CONFIG.AUDIT_LOG_SHEET_NAME);
// Add headers
sheet.getRange(1, 1, 1, 8).setValues([[
'Timestamp', 'Action', 'Negative', 'Match Type', 'Level',
'Campaign', 'Ad Group', 'Undo Command'
]]).setFontWeight('bold').setBackground('#f0f0f0');
sheet.setFrozenRows(1);
}
var timestamp = new Date().toISOString();
var lastRow = sheet.getLastRow();
var auditRows = results.actionsApplied.map(function(action) {
// Generate undo command (Google Ads Editor format)
var undoCmd = generateUndoCommand(action);
return [
timestamp,
'ADD_NEGATIVE',
action.negative,
action.matchType,
action.level,
action.campaign || '',
action.adGroup || '',
undoCmd
];
});
if (auditRows.length > 0) {
sheet.getRange(lastRow + 1, 1, auditRows.length, 8).setValues(auditRows);
}
log('INFO', 'Audit log updated with ' + auditRows.length + ' entries');
}
/**
* Generate undo command for a negative keyword action
*/
function generateUndoCommand(action) {
// Format for Google Ads Editor bulk remove
var formatted = action.negative;
if (action.matchType === 'EXACT') {
formatted = '[' + action.negative + ']';
} else if (action.matchType === 'PHRASE') {
formatted = '"' + action.negative + '"';
}
if (action.level === 'CAMPAIGN' || action.level === 'ACCOUNT') {
return 'Campaign: ' + action.campaign + ' | Remove negative: ' + formatted;
} else {
return 'Campaign: ' + action.campaign + ' | Ad Group: ' + action.adGroup + ' | Remove negative: ' + formatted;
}
}
/**
* Write copy-paste ready negative keyword lists
* Format compatible with Google Ads Editor bulk upload
*/
function writeCopyPasteSheet(ss, results) {
var negatives = results.findings.filter(function(f) {
return f.action === 'NEGATIVE_ADD' && f.suggested_negative && f.confidence >= CONFIG.MIN_CONFIDENCE;
});
if (negatives.length === 0) return;
var sheet = ss.getSheetByName('📋 Copy-Paste Ready');
if (!sheet) {
sheet = ss.insertSheet('📋 Copy-Paste Ready');
}
sheet.clear();
// Deduplicate negatives
var uniqueNegatives = {};
negatives.forEach(function(n) {
var key = n.suggested_negative.toLowerCase();
if (!uniqueNegatives[key] || uniqueNegatives[key].cost < n.cost) {
uniqueNegatives[key] = n;
}
});
var deduped = Object.keys(uniqueNegatives).map(function(k) {
return uniqueNegatives[k];
}).sort(function(a, b) {
return b.cost - a.cost;
});
var data = [
['📋 COPY-PASTE READY NEGATIVE KEYWORDS', ''],
['Copy any column below and paste directly into Google Ads or Editor', ''],
['', ''],
['═══════════════════════════════════════════════════════════════', ''],
['BROAD MATCH NEGATIVES (paste into Google Ads)', ''],
['═══════════════════════════════════════════════════════════════', '']
];
// Broad match list
var broadList = deduped.filter(function(n) {
return n.match_type === 'BROAD' || !n.match_type;
}).map(function(n) { return n.suggested_negative; });
broadList.slice(0, 50).forEach(function(neg) {
data.push([neg, '']);
});
data.push(['', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
data.push(['PHRASE MATCH NEGATIVES (with quotes)', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
// Phrase match list
var phraseList = deduped.filter(function(n) {
return n.match_type === 'PHRASE';
}).map(function(n) { return '"' + n.suggested_negative + '"'; });
phraseList.slice(0, 50).forEach(function(neg) {
data.push([neg, '']);
});
data.push(['', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
data.push(['EXACT MATCH NEGATIVES (with brackets)', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
// Exact match list
var exactList = deduped.filter(function(n) {
return n.match_type === 'EXACT';
}).map(function(n) { return '[' + n.suggested_negative + ']'; });
exactList.slice(0, 50).forEach(function(neg) {
data.push([neg, '']);
});
data.push(['', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
data.push(['MATCH TYPE RECOMMENDATIONS', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
data.push(['', '']);
data.push(['Use BROAD MATCH for:', '']);
data.push([' • Single words (jobs, free, cheap)', '']);
data.push([' • Root cause terms you always want blocked', '']);
data.push([' • High-confidence universal waste patterns', '']);
data.push(['', '']);
data.push(['Use PHRASE MATCH for:', '']);
data.push([' • Multi-word phrases (how to, near me)', '']);
data.push([' • Phrases where word order matters', '']);
data.push([' • Competitor names and variations', '']);
data.push(['', '']);
data.push(['Use EXACT MATCH for:', '']);
data.push([' • Specific search terms with poor performance', '']);
data.push([' • Terms that might be valid in other contexts', '']);
data.push([' • When you need surgical precision', '']);
data.push(['', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
data.push(['GOOGLE ADS EDITOR BULK FORMAT', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
data.push(['Campaign,Keyword,Match Type', '']);
// Generate Editor format
deduped.slice(0, 30).forEach(function(n) {
var matchType = n.match_type === 'EXACT' ? 'Exact' :
n.match_type === 'PHRASE' ? 'Phrase' : 'Broad';
data.push([n.campaignName + ',' + n.suggested_negative + ',' + matchType, '']);
});
sheet.getRange(1, 1, data.length, 2).setValues(data);
sheet.getRange(1, 1).setFontWeight('bold').setFontSize(14);
sheet.setColumnWidth(1, 500);
// Color code the section headers
var headerRows = [5, 9, 13];
headerRows.forEach(function(row) {
if (row < data.length) {
sheet.getRange(row, 1, 1, 2).setBackground('#e8f5e9');
}
});
log('INFO', 'Generated copy-paste lists with ' + deduped.length + ' unique negatives');
}
/******************************************************************************
* NOTIFICATIONS
******************************************************************************/
function sendNotifications(results, spreadsheetUrl, startTime) {
var duration = ((new Date() - startTime) / 1000).toFixed(1);
var message = [
'ULTIMATE NEGATIVE KEYWORD ENGINE',
'',
'Account: ' + AdsApp.currentAccount().getName(),
'Mode: ' + CONFIG.MODE + (CONFIG.DRY_RUN ? ' [DRY RUN]' : ' [LIVE]'),
'Duration: ' + duration + 's',
'',
'Results:',
'- Terms Analyzed: ' + results.summary.termsAnalyzed,
'- Definite Waste: ' + results.summary.definiteWaste,
'- Likely Waste: ' + results.summary.likelyWaste,
'- Protected: ' + results.summary.protected,
'- Negatives Added: ' + results.summary.negativesAdded,
'- Estimated Savings: $' + results.summary.estimatedSavings.toFixed(2),
'',
'Report: ' + spreadsheetUrl,
'',
'--',
'Generated by PPC.io Script Engine'
].join('\n');
if (CONFIG.EMAIL_RECIPIENTS && CONFIG.EMAIL_RECIPIENTS.length > 0) {
try {
MailApp.sendEmail({
to: CONFIG.EMAIL_RECIPIENTS.join(','),
subject: '[PPC.io] Negative Keywords - ' +
results.summary.definiteWaste + ' waste found, $' +
results.summary.estimatedSavings.toFixed(0) + ' potential savings',
body: message
});
log('INFO', 'Email notification sent');
} catch (e) {
log('ERROR', 'Failed to send email: ' + e.message);
}
}
if (CONFIG.SLACK_WEBHOOK_URL) {
try {
var emoji = results.summary.definiteWaste > 0 ? ':warning:' : ':white_check_mark:';
UrlFetchApp.fetch(CONFIG.SLACK_WEBHOOK_URL, {
method: 'post',
contentType: 'application/json',
payload: JSON.stringify({
text: emoji + ' *PPC.io Negative Keyword Engine*\n```' + message + '```'
})
});
log('INFO', 'Slack notification sent');
} catch (e) {
log('ERROR', 'Failed to send Slack: ' + e.message);
}
}
}
/******************************************************************************
* SMART NEGATIVE EXTRACTION
******************************************************************************/
/**
* Extracts the root cause term from a waste search term
* Instead of negating "laser lipo jobs near me", extracts just "jobs"
*/
function extractSmartNegative(searchTerm, patternMatch) {
if (!CONFIG.EXTRACT_ROOT_TERMS) {
return searchTerm;
}
var termLower = searchTerm.toLowerCase();
// Extract the specific matched portion from the pattern
for (var i = 0; i < patternMatch.patterns.length; i++) {
var match = termLower.match(patternMatch.patterns[i]);
if (match && match[0]) {
var extracted = match[0].trim();
// Only use if it meets minimum length
if (extracted.length >= CONFIG.MIN_ROOT_TERM_LENGTH) {
// Clean up the extracted term
extracted = extracted.replace(/\s+/g, ' ').trim();
// For certain patterns, prefer the suggested negative
if (patternMatch.suggested_negatives && patternMatch.suggested_negatives.length > 0) {
// Check if the extracted term contains a suggested negative
for (var j = 0; j < patternMatch.suggested_negatives.length; j++) {
var suggested = patternMatch.suggested_negatives[j].toLowerCase();
if (extracted.indexOf(suggested) !== -1 || suggested.indexOf(extracted) !== -1) {
return suggested;
}
}
}
return extracted;
}
}
}
// Fallback to first suggested negative or original term
if (patternMatch.suggested_negatives && patternMatch.suggested_negatives.length > 0) {
return patternMatch.suggested_negatives[0];
}
return searchTerm;
}
/**
* Root term library - common waste roots to extract
*/
var ROOT_TERMS = {
employment: ['jobs', 'job', 'careers', 'career', 'hiring', 'employment', 'salary', 'resume'],
free: ['free', 'gratis'],
diy: ['how to', 'tutorial', 'diy', 'guide'],
login: ['login', 'log in', 'sign in', 'signin', 'account'],
document: ['pdf', 'template', 'download', 'spreadsheet'],
complaint: ['lawsuit', 'scam', 'fraud', 'complaint', 'refund'],
cheap: ['cheap', 'cheapest', 'free', 'discount', 'coupon']
};
/**
* Given a search term and signal type, extract the best root term
*/
function getBestRootTerm(searchTerm, signal) {
var termLower = searchTerm.toLowerCase();
// Look for known root terms in the search term
var signalRoots = ROOT_TERMS[signal.split('_')[0]] || ROOT_TERMS[signal] || [];
for (var i = 0; i < signalRoots.length; i++) {
var root = signalRoots[i];
// Use word boundary check
var regex = new RegExp('\\b' + escapeRegex(root) + '\\b', 'i');
if (regex.test(termLower)) {
return root;
}
}
// Fall back to pattern extraction
return null;
}
function escapeRegex(string) {
return string.replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
}
/******************************************************************************
* SHARED NEGATIVE LIST MANAGEMENT
******************************************************************************/
/**
* Get or create a shared negative keyword list
*/
function getOrCreateSharedList(listName) {
if (!CONFIG.USE_SHARED_LISTS) return null;
try {
// Try to find existing list
var lists = AdsApp.negativeKeywordLists()
.withCondition('Name = "' + listName.replace(/"/g, '\\"') + '"')
.get();
if (lists.hasNext()) {
return lists.next();
}
// Create new list
if (!CONFIG.DRY_RUN) {
var builder = AdsApp.newNegativeKeywordListBuilder()
.withName(listName)
.build();
return builder.getResult();
}
return null;
} catch (e) {
log('WARN', 'Could not get/create shared list: ' + e.message);
return null;
}
}
/**
* Add a negative to the appropriate shared list
*/
function addToSharedList(negative, matchType, signal) {
if (!CONFIG.USE_SHARED_LISTS || CONFIG.DRY_RUN) return false;
// Determine which shared list based on signal
var listName = getSharedListName(signal);
if (!listName) return false;
try {
var list = getOrCreateSharedList(listName);
if (!list) return false;
var formatted = formatNegative(negative, matchType);
list.addNegativeKeyword(formatted);
return true;
} catch (e) {
log('DEBUG', 'Failed to add to shared list: ' + e.message);
return false;
}
}
/**
* Map signal to shared list name
*/
function getSharedListName(signal) {
// Try direct mapping
if (CONFIG.SHARED_LIST_CATEGORIES[signal]) {
return CONFIG.SHARED_LIST_CATEGORIES[signal];
}
// Try partial mapping
for (var key in CONFIG.SHARED_LIST_CATEGORIES) {
if (signal.toLowerCase().indexOf(key) !== -1) {
return CONFIG.SHARED_LIST_CATEGORIES[key];
}
}
return null;
}
/**
* Apply shared lists to campaigns
*/
function applySharedListsToCampaigns(findings, startTime) {
if (!CONFIG.USE_SHARED_LISTS || CONFIG.DRY_RUN) return [];
var applied = [];
var listsApplied = {};
// Get all campaigns
var campaigns = AdsApp.campaigns()
.withCondition('Status = ENABLED');
if (CONFIG.CAMPAIGN_NAME_CONTAINS) {
campaigns = campaigns.withCondition('Name CONTAINS "' + CONFIG.CAMPAIGN_NAME_CONTAINS + '"');
}
var campaignIterator = campaigns.get();
var campaignList = [];
while (campaignIterator.hasNext()) {
campaignList.push(campaignIterator.next());
}
// For each shared list category that has findings
for (var signal in CONFIG.SHARED_LIST_CATEGORIES) {
var listName = CONFIG.SHARED_LIST_CATEGORIES[signal];
var list = getOrCreateSharedList(listName);
if (!list) continue;
// Apply to all campaigns
for (var i = 0; i < campaignList.length; i++) {
var campaign = campaignList[i];
try {
campaign.addNegativeKeywordList(list);
if (!listsApplied[listName]) {
listsApplied[listName] = 0;
}
listsApplied[listName]++;
} catch (e) {
// List already applied, ignore
}
}
checkTimeLimit(startTime);
}
for (var name in listsApplied) {
applied.push({
list: name,
campaignsApplied: listsApplied[name]
});
}
return applied;
}
/******************************************************************************
* VALUE-BASED ANALYSIS (ROAS)
******************************************************************************/
/**
* Analyze term performance including conversion value
*/
function analyzeValuePerformance(term) {
var result = {
isWaste: false,
confidence: 0,
rationale: ''
};
// Skip if no conversion value tracking
if (!term.conversionValue && !term.conversions) {
return result;
}
// Calculate ROAS if we have value
if (term.conversionValue > 0 && term.cost > 0) {
var roas = term.conversionValue / term.cost;
// Check against target ROAS (if implied by CPA/CVR)
var targetCpa = CONFIG.BUSINESS_CONTEXT.target_cpa;
if (targetCpa && term.conversions > 0) {
var avgOrderValue = term.conversionValue / term.conversions;
var targetRoas = avgOrderValue / targetCpa;
if (roas < targetRoas * 0.5) {
result.isWaste = true;
result.confidence = 0.80;
result.rationale = 'ROAS ' + roas.toFixed(2) + 'x vs target ' +
targetRoas.toFixed(2) + 'x (50% below target)';
return result;
}
}
// General ROAS check (below 1.0 = losing money)
if (roas < 0.5 && term.clicks >= CONFIG.MIN_CLICKS_FOR_PERF) {
result.isWaste = true;
result.confidence = 0.75;
result.rationale = 'ROAS ' + roas.toFixed(2) + 'x - spending $' +
term.cost.toFixed(2) + ' to generate $' + term.conversionValue.toFixed(2);
return result;
}
}
return result;
}
/******************************************************************************
* MULTI-DIMENSIONAL INTENT SCORING
******************************************************************************/
/**
* Score a search term across multiple intent dimensions
* Returns an object with scores from 0-100 for each dimension
*/
function scoreIntentDimensions(searchTerm) {
var termLower = searchTerm.toLowerCase();
return {
// Commercial Intent (0 = informational, 100 = ready to buy)
commercialIntent: scoreCommercialIntent(termLower),
// Urgency (0 = browsing, 100 = emergency/immediate need)
urgency: scoreUrgency(termLower),
// Qualification (0 = unqualified, 100 = ideal customer)
qualification: scoreQualification(termLower),
// Specificity (0 = generic, 100 = very specific)
specificity: scoreSpecificity(termLower),
// Local Intent (0 = not local, 100 = strong local intent)
localIntent: scoreLocalIntent(termLower)
};
}
function scoreCommercialIntent(term) {
var score = 50; // Neutral baseline
// Strong buy signals (+30-50)
if (/\b(buy|purchase|order|get|hire|book|schedule|appointment)\b/i.test(term)) score += 40;
if (/\b(price|pricing|cost|quote|estimate|rates?)\b/i.test(term)) score += 30;
if (/\b(best|top|recommended|leading)\b/i.test(term)) score += 25;
if (/\b(near\s*me|nearby|in\s+[a-z]+)\b/i.test(term)) score += 20;
// Research signals (-20-40)
if (/\b(what\s*(is|are|does)|how\s*(to|does)|why)\b/i.test(term)) score -= 30;
if (/\b(review|comparison|vs|versus|reddit|forum)\b/i.test(term)) score -= 20;
if (/\b(free|diy|tutorial|guide|learn)\b/i.test(term)) score -= 40;
// Non-commercial signals (-50)
if (/\b(jobs?|careers?|salary|hiring|employment)\b/i.test(term)) score -= 50;
if (/\b(login|sign\s*in|my\s*account|dashboard)\b/i.test(term)) score -= 50;
return Math.max(0, Math.min(100, score));
}
function scoreUrgency(term) {
var score = 30; // Low baseline
// High urgency signals
if (/\b(emergency|urgent|asap|today|now|immediate)\b/i.test(term)) score += 60;
if (/\b(24[-\s]?hour|same[-\s]?day|rush)\b/i.test(term)) score += 50;
if (/\b(broken|leak|flood|fire|damage|stuck)\b/i.test(term)) score += 40;
if (/\b(open\s*(now|late|sunday)|after\s*hours)\b/i.test(term)) score += 30;
// Low urgency signals
if (/\b(research|learn|compare|information)\b/i.test(term)) score -= 20;
if (/\b(eventually|someday|future|planning)\b/i.test(term)) score -= 30;
return Math.max(0, Math.min(100, score));
}
function scoreQualification(term) {
var score = 50; // Neutral baseline
// Premium/qualified signals
if (/\b(best|top|premium|luxury|high[-\s]?end|professional)\b/i.test(term)) score += 30;
if (/\b(certified|licensed|experienced|expert)\b/i.test(term)) score += 25;
// Budget/unqualified signals
if (/\b(cheap(est)?|budget|discount|free|low[-\s]?cost)\b/i.test(term)) {
if (CONFIG.BUSINESS_CONTEXT.price_positioning === 'premium') {
score -= 40;
}
}
// B2B qualification
if (CONFIG.BUSINESS_CONTEXT.business_type === 'b2b') {
if (/\b(enterprise|business|corporate|company|b2b)\b/i.test(term)) score += 30;
if (/\b(personal|home|individual|consumer)\b/i.test(term)) score -= 30;
}
return Math.max(0, Math.min(100, score));
}
function scoreSpecificity(term) {
var words = term.split(/\s+/).filter(function(w) { return w.length > 2; });
var score = Math.min(100, words.length * 15); // More words = more specific
// Specific modifiers boost score
if (/\b(for\s+[a-z]+|[a-z]+\s+for)\b/i.test(term)) score += 15;
if (/\b(with\s+[a-z]+|[a-z]+\s+with)\b/i.test(term)) score += 10;
if (/\d+/.test(term)) score += 10; // Contains numbers
// Generic single words reduce score
if (words.length <= 2) score -= 20;
return Math.max(0, Math.min(100, score));
}
function scoreLocalIntent(term) {
var score = 20; // Low baseline
// Strong local signals
if (/\b(near\s*me|nearby|close\s*to|in\s*my\s*area)\b/i.test(term)) score += 70;
if (/\b(in\s+[a-z]+|[a-z]+\s+area|local)\b/i.test(term)) score += 50;
// City/state names (would need more sophisticated detection)
if (/\b(city|town|county|state|neighborhood)\b/i.test(term)) score += 30;
// Address-like patterns
if (/\d{5}/.test(term)) score += 40; // ZIP code
return Math.max(0, Math.min(100, score));
}
/**
* Calculate overall intent score and recommendation
*/
function getOverallIntentAssessment(scores) {
// Weighted average
var weights = {
commercialIntent: 0.35,
urgency: 0.20,
qualification: 0.25,
specificity: 0.10,
localIntent: 0.10
};
var weightedSum = 0;
for (var dim in weights) {
weightedSum += (scores[dim] || 50) * weights[dim];
}
var recommendation;
if (weightedSum >= 70) {
recommendation = 'HIGH_VALUE';
} else if (weightedSum >= 50) {
recommendation = 'MONITOR';
} else if (weightedSum >= 30) {
recommendation = 'LOW_VALUE';
} else {
recommendation = 'LIKELY_WASTE';
}
return {
overallScore: Math.round(weightedSum),
recommendation: recommendation,
dimensions: scores
};
}
/******************************************************************************
* UTILITIES
******************************************************************************/
function containsAny(text, terms) {
for (var i = 0; i < terms.length; i++) {
if (text.indexOf(terms[i]) !== -1) return true;
}
return false;
}
function matchesPatternGroup(text, patterns) {
for (var i = 0; i < patterns.length; i++) {
if (patterns[i].test(text)) return true;
}
return false;
}
function extractKeyPhrase(text, pattern) {
var match = text.match(pattern);
if (match && match[0]) {
return match[0].trim();
}
return text.split(' ')[0];
}
function formatDate(date) {
return Utilities.formatDate(date, AdsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');
}
function checkTimeLimit(startTime) {
var elapsed = (new Date() - startTime) / 1000 / 60;
if (elapsed > CONFIG.TIME_LIMIT_MINUTES) {
throw new Error('TIME_LIMIT: Script stopped after ' + elapsed.toFixed(1) + ' minutes to prevent timeout.');
}
}
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 logFinalSummary(results, startTime) {
var duration = ((new Date() - startTime) / 1000).toFixed(1);
log('INFO', '═══════════════════════════════════════════════════════════════');
log('INFO', 'EXECUTION COMPLETE');
log('INFO', '═══════════════════════════════════════════════════════════════');
log('INFO', '');
log('INFO', '📊 RESULTS SUMMARY');
log('INFO', ' Terms Analyzed: ' + results.summary.termsAnalyzed);
log('INFO', ' Waste Found: ' + (results.summary.definiteWaste + results.summary.likelyWaste));
log('INFO', ' Negatives Added: ' + results.summary.negativesAdded);
log('INFO', ' Estimated Savings: $' + results.summary.estimatedSavings.toFixed(2));
log('INFO', '');
log('INFO', '⏱️ PERFORMANCE METRICS');
log('INFO', ' Total Duration: ' + duration + ' seconds');
if (results.timing) {
log('INFO', ' Data Collection: ' + results.timing.collection.toFixed(1) + 's');
log('INFO', ' Pattern Analysis: ' + results.timing.patternAnalysis.toFixed(1) + 's');
if (results.timing.ngramMining > 0) {
log('INFO', ' N-gram Mining: ' + results.timing.ngramMining.toFixed(1) + 's');
}
log('INFO', ' Conflict Detection: ' + results.timing.conflictDetection.toFixed(1) + 's');
if (results.timing.crossCampaign > 0) {
log('INFO', ' Cross-Campaign: ' + results.timing.crossCampaign.toFixed(1) + 's');
}
var rate = results.summary.termsAnalyzed / (results.timing.total || 1);
log('INFO', ' Processing Rate: ' + Math.round(rate) + ' terms/second');
}
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] Negative Keyword Engine Failed',
body: 'Script failed after ' + ((new Date() - startTime) / 1000).toFixed(1) +
' seconds.\n\nError: ' + error.message + '\n\nStack:\n' + error.stack
});
} catch (e) {
log('ERROR', 'Could not send error email: ' + e.message);
}
}
}
/******************************************************************************
* GEOGRAPHIC PATTERN BUILDER
******************************************************************************/
/**
* Build geographic mismatch patterns based on configured locations
* Called at runtime to populate PATTERNS.GEOGRAPHIC_MISMATCH
*/
function buildGeographicPatterns() {
var locations = CONFIG.BUSINESS_CONTEXT.locations_served || [];
if (locations.length === 0) return;
// Common US states and cities that might be searched for wrong locations
var allMajorCities = [
'new york', 'los angeles', 'chicago', 'houston', 'phoenix', 'philadelphia',
'san antonio', 'san diego', 'dallas', 'san jose', 'austin', 'jacksonville',
'fort worth', 'columbus', 'charlotte', 'indianapolis', 'san francisco',
'seattle', 'denver', 'washington', 'boston', 'nashville', 'detroit',
'portland', 'las vegas', 'memphis', 'louisville', 'baltimore', 'milwaukee',
'albuquerque', 'tucson', 'fresno', 'sacramento', 'atlanta', 'miami',
'oakland', 'minneapolis', 'tulsa', 'cleveland', 'omaha', 'raleigh'
];
var allStates = [
'alabama', 'alaska', 'arizona', 'arkansas', 'california', 'colorado',
'connecticut', 'delaware', 'florida', 'georgia', 'hawaii', 'idaho',
'illinois', 'indiana', 'iowa', 'kansas', 'kentucky', 'louisiana',
'maine', 'maryland', 'massachusetts', 'michigan', 'minnesota', 'mississippi',
'missouri', 'montana', 'nebraska', 'nevada', 'new hampshire', 'new jersey',
'new mexico', 'new york', 'north carolina', 'north dakota', 'ohio', 'oklahoma',
'oregon', 'pennsylvania', 'rhode island', 'south carolina', 'south dakota',
'tennessee', 'texas', 'utah', 'vermont', 'virginia', 'washington',
'west virginia', 'wisconsin', 'wyoming'
];
// Normalize served locations
var servedLower = locations.map(function(loc) { return loc.toLowerCase(); });
// Find cities/states NOT served
var notServedCities = allMajorCities.filter(function(city) {
return servedLower.indexOf(city) === -1;
});
var notServedStates = allStates.filter(function(state) {
return !servedLower.some(function(served) {
return served.indexOf(state) !== -1 || state.indexOf(served) !== -1;
});
});
// Build patterns for common wrong-location searches
var geoPatterns = [];
// Add city patterns (limit to top 20 to avoid pattern overload)
notServedCities.slice(0, 20).forEach(function(city) {
geoPatterns.push(new RegExp('\\b' + escapeRegex(city) + '\\b', 'i'));
});
// Add state abbreviation patterns
var stateAbbrevMap = {
'alabama': 'al', 'alaska': 'ak', 'arizona': 'az', 'arkansas': 'ar',
'california': 'ca', 'colorado': 'co', 'connecticut': 'ct', 'delaware': 'de',
'florida': 'fl', 'georgia': 'ga', 'hawaii': 'hi', 'idaho': 'id',
'illinois': 'il', 'indiana': 'in', 'iowa': 'ia', 'kansas': 'ks',
'kentucky': 'ky', 'louisiana': 'la', 'maine': 'me', 'maryland': 'md',
'massachusetts': 'ma', 'michigan': 'mi', 'minnesota': 'mn', 'mississippi': 'ms',
'missouri': 'mo', 'montana': 'mt', 'nebraska': 'ne', 'nevada': 'nv',
'new hampshire': 'nh', 'new jersey': 'nj', 'new mexico': 'nm', 'new york': 'ny',
'north carolina': 'nc', 'north dakota': 'nd', 'ohio': 'oh', 'oklahoma': 'ok',
'oregon': 'or', 'pennsylvania': 'pa', 'rhode island': 'ri', 'south carolina': 'sc',
'south dakota': 'sd', 'tennessee': 'tn', 'texas': 'tx', 'utah': 'ut',
'vermont': 'vt', 'virginia': 'va', 'washington': 'wa', 'west virginia': 'wv',
'wisconsin': 'wi', 'wyoming': 'wy'
};
// Build suggested negatives from not-served cities
var suggestedNegatives = notServedCities.slice(0, 10);
// Update the patterns
PATTERNS.GEOGRAPHIC_MISMATCH.patterns = geoPatterns;
PATTERNS.GEOGRAPHIC_MISMATCH.suggested_negatives = suggestedNegatives;
}
/******************************************************************************
* INITIALIZATION
******************************************************************************/
/**
* Initialize dynamic patterns before running
*/
function initializePatterns() {
buildGeographicPatterns();
loadCustomPatterns();
log('DEBUG', 'Dynamic patterns initialized');
}
/******************************************************************************
* CUSTOM PATTERN LOADING
******************************************************************************/
/**
* Load custom patterns from external spreadsheet
* Format: Column A = Pattern, Column B = Category, Column C = Confidence (optional)
*/
function loadCustomPatterns() {
if (!CONFIG.CUSTOM_PATTERNS_SHEET_URL) return;
try {
var ss = SpreadsheetApp.openByUrl(CONFIG.CUSTOM_PATTERNS_SHEET_URL);
var sheet = ss.getSheetByName(CONFIG.CUSTOM_PATTERNS_SHEET_NAME);
if (!sheet) {
log('WARN', 'Custom patterns sheet not found: ' + CONFIG.CUSTOM_PATTERNS_SHEET_NAME);
return;
}
var data = sheet.getDataRange().getValues();
var customPatterns = [];
// Skip header row
for (var i = 1; i < data.length; i++) {
var row = data[i];
var patternText = row[0];
var category = row[1] || 'custom';
var confidence = parseFloat(row[2]) || 0.85;
if (!patternText) continue;
try {
// Convert to regex (support both plain text and regex syntax)
var regex;
if (patternText.indexOf('|') !== -1 || patternText.indexOf('\\b') !== -1) {
// Looks like regex, use as-is
regex = new RegExp(patternText, 'i');
} else {
// Plain text, wrap with word boundaries
regex = new RegExp('\\b' + escapeRegex(patternText) + '\\b', 'i');
}
customPatterns.push({
pattern: regex,
category: category,
confidence: confidence,
original: patternText
});
} catch (e) {
log('WARN', 'Invalid custom pattern: ' + patternText + ' - ' + e.message);
}
}
// Add custom patterns to PATTERNS object
if (customPatterns.length > 0) {
PATTERNS.CUSTOM = {
name: 'Custom Patterns',
description: 'User-defined patterns from spreadsheet',
confidence: 0.85,
match_type: 'PHRASE',
level: 'CAMPAIGN',
patterns: customPatterns.map(function(p) { return p.pattern; }),
suggested_negatives: [],
customData: customPatterns
};
log('INFO', 'Loaded ' + customPatterns.length + ' custom patterns');
}
} catch (e) {
log('WARN', 'Could not load custom patterns: ' + e.message);
}
}
/******************************************************************************
* SEARCH TERM CLUSTERING
******************************************************************************/
/**
* Cluster similar search terms together for batch analysis
*/
function clusterSearchTerms(findings) {
if (!CONFIG.ENABLE_CLUSTERING) return [];
var clusters = [];
var processed = new Set();
// Get all waste findings
var wasteFindings = findings.filter(function(f) {
return f.action === 'NEGATIVE_ADD';
});
for (var i = 0; i < wasteFindings.length; i++) {
var finding = wasteFindings[i];
if (processed.has(finding.searchTerm)) continue;
var cluster = {
rootTerm: finding.suggested_negative || extractRootFromTerm(finding.searchTerm),
terms: [finding],
totalCost: finding.cost,
totalClicks: finding.clicks,
signal: finding.signal
};
// Find similar terms
for (var j = i + 1; j < wasteFindings.length; j++) {
var other = wasteFindings[j];
if (processed.has(other.searchTerm)) continue;
var similarity = calculateTermSimilarity(finding.searchTerm, other.searchTerm);
if (similarity >= CONFIG.CLUSTER_SIMILARITY_THRESHOLD) {
cluster.terms.push(other);
cluster.totalCost += other.cost;
cluster.totalClicks += other.clicks;
processed.add(other.searchTerm);
}
}
processed.add(finding.searchTerm);
// Only include clusters above minimum size
if (cluster.terms.length >= CONFIG.MIN_CLUSTER_SIZE) {
clusters.push(cluster);
}
}
// Sort by total cost (highest waste first)
clusters.sort(function(a, b) {
return b.totalCost - a.totalCost;
});
return clusters;
}
/**
* Calculate similarity between two search terms using Jaccard index
*/
function calculateTermSimilarity(term1, term2) {
var words1 = new Set(term1.toLowerCase().split(/\s+/));
var words2 = new Set(term2.toLowerCase().split(/\s+/));
// Calculate Jaccard similarity
var intersection = 0;
words1.forEach(function(word) {
if (words2.has(word)) intersection++;
});
var union = words1.size + words2.size - intersection;
return union > 0 ? intersection / union : 0;
}
/**
* Extract the most meaningful root from a search term
*/
function extractRootFromTerm(term) {
var words = term.toLowerCase().split(/\s+/);
// Remove common stop words
var stopWords = ['the', 'a', 'an', 'and', 'or', 'but', 'in', 'on', 'at', 'to', 'for',
'of', 'with', 'by', 'from', 'as', 'is', 'was', 'are', 'were', 'been',
'be', 'have', 'has', 'had', 'do', 'does', 'did', 'will', 'would',
'could', 'should', 'may', 'might', 'must', 'shall', 'can', 'need',
'me', 'my', 'i', 'you', 'your', 'we', 'our', 'they', 'their', 'it'];
var meaningfulWords = words.filter(function(w) {
return w.length > 2 && stopWords.indexOf(w) === -1;
});
// Return first 2-3 meaningful words
return meaningfulWords.slice(0, 3).join(' ') || term;
}
/**
* Write clusters to spreadsheet
*/
function writeClustersSheet(ss, clusters) {
if (!clusters || clusters.length === 0) return;
var sheet = ss.getSheetByName('9. Term Clusters');
if (!sheet) {
sheet = ss.insertSheet('9. Term Clusters');
}
sheet.clear();
var data = [
['SEARCH TERM CLUSTERS', '', '', '', ''],
['Similar terms grouped together for batch negative decisions', '', '', '', ''],
['', '', '', '', ''],
['Root Term', 'Signal', 'Terms in Cluster', 'Total Cost', 'Example Terms']
];
clusters.forEach(function(cluster) {
var examples = cluster.terms.slice(0, 3).map(function(t) {
return t.searchTerm;
}).join(' | ');
data.push([
cluster.rootTerm,
cluster.signal,
cluster.terms.length,
'$' + cluster.totalCost.toFixed(2),
examples
]);
});
sheet.getRange(1, 1, data.length, 5).setValues(data);
sheet.getRange(1, 1).setFontWeight('bold').setFontSize(14);
sheet.getRange(4, 1, 1, 5).setFontWeight('bold').setBackground('#f0f0f0');
sheet.setFrozenRows(4);
for (var col = 1; col <= 5; col++) {
sheet.autoResizeColumn(col);
}
}