Free: 96 PPC tools + my AI Playbook book
Production-ready prompts, scripts, frameworks and AI agents for Google Ads professionals. No payment required.
/******************************************************************************
* AI KEYWORD EXPANSION ENGINE
*
* Generated by PPC.io Script Engine
* https://ppc.io
*
* Purpose: AI-powered keyword suggestions using multi-signal intelligence
* Author: PPC.io
* Version: 1.0
* Updated: 2025-01-16
*
* SETUP INSTRUCTIONS:
* 1. Set CLAUDE_API_KEY to your Anthropic API key (required)
* 2. Run in Preview mode first to verify
* 3. Schedule: Weekly recommended
*
* That's it! Everything else is auto-detected:
* - Brand terms (from account name, domain, high-CTR keywords, brand campaigns)
* - Competitor terms (from auction insights, competitor campaigns)
* - Business type (from landing page themes)
* - Target CPA (from historical performance)
*
* OPTIONAL OVERRIDES (if auto-detection isn't accurate):
* - BRAND_TERMS: Manually specify your brand terms
* - COMPETITOR_TERMS: Manually specify competitor terms
* - TARGET_CPA / TARGET_ROAS: Set specific targets
*
* INTELLIGENCE SIGNALS:
* 1. Top Converting Keywords - Foundation of what's working
* 2. High-Value Search Terms - Converting queries not yet keywords
* 3. High-CTR Engagement - Strong interest signals
* 4. Intent Distribution - Coverage gaps by intent category
* 5. Competitor Signals - Auction insights analysis
* 6. Landing Page Themes - What you SHOULD be targeting
* 7. Ad Copy Value Props - Messaging themes that convert
*
* OUTPUT:
* - 50-100 high-quality keyword suggestions
* - Each with: match type, confidence, bid, placement, reasoning
* - 6-tab Google Sheet with full analysis
*
* CHANGELOG:
* v1.0 - Initial release with Claude API integration
*
******************************************************************************/
/******************************************************************************
* CONFIGURATION - Adjust these values for your account
******************************************************************************/
var CONFIG = {
// ═══════════════════════════════════════════════════════════════════════════
// OUTPUT SETTINGS
// ═══════════════════════════════════════════════════════════════════════════
SPREADSHEET_URL: 'CREATE_NEW', // 'CREATE_NEW' or existing sheet URL
EMAIL_RECIPIENTS: [], // ['email@example.com']
SLACK_WEBHOOK_URL: '', // Optional Slack notifications
// ═══════════════════════════════════════════════════════════════════════════
// AI CONFIGURATION
// ═══════════════════════════════════════════════════════════════════════════
CLAUDE_API_KEY: '', // Required: Your Anthropic API key
CLAUDE_MODEL: 'claude-sonnet-4-20250514', // Model to use
TARGET_SUGGESTIONS: 100, // Target number of suggestions (50-100)
AI_TEMPERATURE: 0.7, // Creativity (0.0 = focused, 1.0 = creative)
// ═══════════════════════════════════════════════════════════════════════════
// ACCOUNT FILTERS
// ═══════════════════════════════════════════════════════════════════════════
DATE_RANGE: 'LAST_30_DAYS', // Data lookback period
CAMPAIGN_NAME_CONTAINS: '', // Filter to specific campaigns
CAMPAIGN_NAME_EXCLUDES: '', // Exclude campaigns (comma-separated)
INCLUDE_PAUSED: false, // Include paused campaigns/keywords
// ═══════════════════════════════════════════════════════════════════════════
// INTELLIGENCE THRESHOLDS
// ═══════════════════════════════════════════════════════════════════════════
// Signal 1: Top Converting Keywords
MIN_CONVERSIONS_TOP_KEYWORDS: 2, // Minimum conversions to be "top performer"
TOP_KEYWORDS_LIMIT: 50, // How many top keywords to analyze
// Signal 2: High-Value Search Terms
MIN_CONVERSIONS_SEARCH_TERMS: 1, // Minimum conversions for search term
MIN_CLICKS_SEARCH_TERMS: 5, // Minimum clicks for search term
SEARCH_TERMS_LIMIT: 100, // How many search terms to analyze
// Signal 3: High-CTR Engagement
MIN_CTR_ENGAGEMENT: 0.05, // 5% CTR minimum for engagement signal
MIN_CLICKS_ENGAGEMENT: 10, // Minimum clicks for engagement signal
ENGAGEMENT_LIMIT: 30, // How many engagement signals to analyze
// Signal 5: Competitor Analysis
MIN_IMPRESSION_SHARE_COMPETITOR: 0.1, // 10% impression share to be relevant
// Signal 6 & 7: Landing Page & Ad Copy
LANDING_PAGE_SAMPLE_SIZE: 20, // How many landing pages to analyze
AD_COPY_SAMPLE_SIZE: 30, // How many ads to analyze for themes
// ═══════════════════════════════════════════════════════════════════════════
// FILTERING & QUALITY
// ═══════════════════════════════════════════════════════════════════════════
MIN_CONFIDENCE_SCORE: 0.3, // Minimum confidence to include suggestion
ENABLE_SEMANTIC_DEDUP: true, // Use AI for semantic deduplication
// ═══════════════════════════════════════════════════════════════════════════
// BRAND & COMPETITOR TERMS
// Leave empty [] for AUTO-DETECTION from account data, or specify manually
// ═══════════════════════════════════════════════════════════════════════════
BRAND_TERMS: [], // Empty = auto-detect from account name, URLs, high-CTR keywords
COMPETITOR_TERMS: [], // Empty = auto-detect from auction insights
AUTO_DETECT_BRAND: true, // Auto-detect brand terms if BRAND_TERMS is empty
AUTO_DETECT_COMPETITORS: true, // Auto-detect competitors from auction insights
BLOCKED_TERMS: [], // Terms to never suggest
// ═══════════════════════════════════════════════════════════════════════════
// BUSINESS CONTEXT (helps AI understand your business)
// ═══════════════════════════════════════════════════════════════════════════
BUSINESS_TYPE: '', // 'ecommerce', 'saas', 'local_service', 'lead_gen'
INDUSTRY: '', // 'legal', 'healthcare', 'finance', 'retail', etc.
TARGET_CPA: 0, // Target cost per acquisition (0 = auto-detect)
TARGET_ROAS: 0, // Target return on ad spend (0 = auto-detect)
// ═══════════════════════════════════════════════════════════════════════════
// EXECUTION SETTINGS
// ═══════════════════════════════════════════════════════════════════════════
LOG_LEVEL: 'INFO', // 'DEBUG', 'INFO', 'WARN', 'ERROR'
TIME_LIMIT_MINUTES: 25, // Exit gracefully before 30-min limit
BATCH_SIZE: 500, // Rows per batch write
API_RETRY_ATTEMPTS: 3, // Retry attempts for API calls
API_RETRY_DELAY_MS: 2000 // Initial delay between retries
};
/******************************************************************************
* INTENT CLASSIFICATION PATTERNS
******************************************************************************/
var INTENT_PATTERNS = {
TRANSACTIONAL: [
'buy', 'purchase', 'order', 'shop', 'price', 'pricing', 'cost',
'cheap', 'affordable', 'discount', 'deal', 'sale', 'coupon',
'hire', 'book', 'schedule', 'appointment', 'quote', 'estimate',
'subscribe', 'sign up', 'signup', 'register', 'get started',
'download', 'install', 'free trial', 'demo', 'pricing plans'
],
INFORMATIONAL: [
'how to', 'how do', 'what is', 'what are', 'why', 'when',
'guide', 'tutorial', 'tips', 'tricks', 'learn', 'training',
'example', 'examples', 'template', 'templates', 'ideas',
'definition', 'meaning', 'explain', 'difference between',
'pros and cons', 'benefits', 'advantages', 'disadvantages'
],
NAVIGATIONAL: [
'near me', 'nearby', 'location', 'locations', 'address',
'directions', 'hours', 'open now', 'closest', 'local',
'in my area', 'around me', 'close to me', 'nearest'
],
COMMERCIAL: [
'best', 'top', 'review', 'reviews', 'compare', 'comparison',
'vs', 'versus', 'alternative', 'alternatives', 'like',
'similar to', 'rated', 'rating', 'rankings', 'recommended'
]
};
/******************************************************************************
* GLOBAL STATE
******************************************************************************/
var STATE = {
startTime: null,
spreadsheet: null,
existingKeywords: [],
existingKeywordsNormalized: {},
negativeKeywords: [],
detectedBrandTerms: [],
detectedCompetitorTerms: [],
signals: {
topKeywords: [],
searchTerms: [],
highCTR: [],
intentDistribution: {},
competitors: [],
landingPageThemes: [],
adCopyThemes: [],
brandCampaignKeywords: [],
competitorCampaignKeywords: []
},
context: {
accountName: '',
primaryDomain: '',
totalKeywords: 0,
totalCampaigns: 0,
avgCPA: 0,
avgCTR: 0,
avgConvRate: 0,
avgROAS: 0,
avgCPC: 0,
totalCost: 0,
totalConversions: 0,
businessType: '',
industry: '',
intentGaps: []
},
suggestions: [],
filteredSuggestions: []
};
/******************************************************************************
* MAIN EXECUTION
******************************************************************************/
function main() {
STATE.startTime = new Date();
log('INFO', '════════════════════════════════════════════════════════════════');
log('INFO', 'AI KEYWORD EXPANSION ENGINE - Started: ' + STATE.startTime.toISOString());
log('INFO', '════════════════════════════════════════════════════════════════');
try {
// Validate configuration
validateConfig();
// Phase 1: Collect Intelligence Signals
log('INFO', '');
log('INFO', '▶ PHASE 1: COLLECTING INTELLIGENCE SIGNALS');
log('INFO', '────────────────────────────────────────────');
collectAllSignals();
// Phase 2: Build Context
log('INFO', '');
log('INFO', '▶ PHASE 2: BUILDING CONTEXT');
log('INFO', '────────────────────────────────────────────');
buildContext();
// Phase 3: Generate AI Suggestions
log('INFO', '');
log('INFO', '▶ PHASE 3: GENERATING AI SUGGESTIONS');
log('INFO', '────────────────────────────────────────────');
generateAISuggestions();
// Phase 4: Filter Suggestions
log('INFO', '');
log('INFO', '▶ PHASE 4: FILTERING SUGGESTIONS');
log('INFO', '────────────────────────────────────────────');
filterSuggestions();
// Phase 5: Enrich Suggestions
log('INFO', '');
log('INFO', '▶ PHASE 5: ENRICHING SUGGESTIONS');
log('INFO', '────────────────────────────────────────────');
enrichSuggestions();
// Phase 6: Output Results
log('INFO', '');
log('INFO', '▶ PHASE 6: GENERATING OUTPUT');
log('INFO', '────────────────────────────────────────────');
generateOutput();
// Send notifications
sendNotifications();
// Log summary
logFinalSummary();
} catch (error) {
handleFatalError(error);
}
}
/******************************************************************************
* CONFIGURATION VALIDATION
******************************************************************************/
function validateConfig() {
if (!CONFIG.CLAUDE_API_KEY) {
throw new Error('CLAUDE_API_KEY is required. Get your API key from https://console.anthropic.com/');
}
log('INFO', 'Configuration validated');
}
/******************************************************************************
* PHASE 1: INTELLIGENCE SIGNAL COLLECTION
******************************************************************************/
function collectAllSignals() {
// First, collect existing keywords for deduplication
log('INFO', 'Collecting existing keywords for deduplication...');
collectExistingKeywords();
log('INFO', ' ✓ Found ' + STATE.existingKeywords.length + ' existing keywords');
// Collect negative keywords for conflict detection
log('INFO', 'Collecting negative keywords...');
collectNegativeKeywords();
log('INFO', ' ✓ Found ' + STATE.negativeKeywords.length + ' negative keywords');
checkTimeLimit();
// Signal 1: Top Converting Keywords
log('INFO', 'Signal 1: Collecting top converting keywords...');
collectTopConvertingKeywords();
log('INFO', ' ✓ Found ' + STATE.signals.topKeywords.length + ' top converters');
checkTimeLimit();
// Signal 2: High-Value Search Terms
log('INFO', 'Signal 2: Collecting high-value search terms...');
collectHighValueSearchTerms();
log('INFO', ' ✓ Found ' + STATE.signals.searchTerms.length + ' high-value search terms');
checkTimeLimit();
// Signal 3: High-CTR Engagement
log('INFO', 'Signal 3: Collecting high-CTR engagement signals...');
collectHighCTRSignals();
log('INFO', ' ✓ Found ' + STATE.signals.highCTR.length + ' high-CTR signals');
checkTimeLimit();
// Signal 5: Competitor Signals (moved before auto-detection)
log('INFO', 'Signal 5: Collecting competitor signals...');
collectCompetitorSignals();
log('INFO', ' ✓ Found ' + STATE.signals.competitors.length + ' competitors');
checkTimeLimit();
// AUTO-DETECTION: Brand and Competitor Terms
log('INFO', 'Auto-detecting brand and competitor terms...');
autoDetectBrandTerms();
autoDetectCompetitorTerms();
log('INFO', ' ✓ Brand terms: ' + STATE.detectedBrandTerms.slice(0, 5).join(', ') + (STATE.detectedBrandTerms.length > 5 ? '...' : ''));
log('INFO', ' ✓ Competitor terms: ' + STATE.detectedCompetitorTerms.slice(0, 5).join(', ') + (STATE.detectedCompetitorTerms.length > 5 ? '...' : ''));
checkTimeLimit();
// Signal 4: Intent Distribution (needs brand/competitor terms first)
log('INFO', 'Signal 4: Analyzing intent distribution...');
analyzeIntentDistribution();
log('INFO', ' ✓ Intent analysis complete');
checkTimeLimit();
// Signal 6: Landing Page Themes
log('INFO', 'Signal 6: Extracting landing page themes...');
extractLandingPageThemes();
log('INFO', ' ✓ Extracted ' + STATE.signals.landingPageThemes.length + ' themes');
checkTimeLimit();
// Signal 7: Ad Copy Themes
log('INFO', 'Signal 7: Mining ad copy value props...');
mineAdCopyThemes();
log('INFO', ' ✓ Extracted ' + STATE.signals.adCopyThemes.length + ' themes');
checkTimeLimit();
}
/******************************************************************************
* COLLECT EXISTING KEYWORDS (for deduplication)
******************************************************************************/
function collectExistingKeywords() {
var query = 'SELECT keyword.text, keyword.match_type ' +
'FROM keyword_view ' +
'WHERE campaign.status != \'REMOVED\' ' +
'AND ad_group.status != \'REMOVED\' ' +
'AND keyword.status != \'REMOVED\'';
if (!CONFIG.INCLUDE_PAUSED) {
query += ' AND campaign.status = \'ENABLED\' AND ad_group.status = \'ENABLED\' AND keyword.status = \'ENABLED\'';
}
try {
var report = AdsApp.report(query);
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var keywordText = row['keyword.text'];
STATE.existingKeywords.push({
text: keywordText,
matchType: row['keyword.match_type']
});
STATE.existingKeywordsNormalized[normalizeKeyword(keywordText)] = true;
}
} catch (e) {
log('WARN', 'GAQL keyword collection failed, using fallback: ' + e.message);
collectExistingKeywordsFallback();
}
}
function collectExistingKeywordsFallback() {
var selector = AdsApp.keywords();
if (!CONFIG.INCLUDE_PAUSED) {
selector = selector.withCondition('Status = ENABLED')
.withCondition('CampaignStatus = ENABLED')
.withCondition('AdGroupStatus = ENABLED');
}
var keywords = selector.get();
while (keywords.hasNext()) {
var kw = keywords.next();
var keywordText = kw.getText();
STATE.existingKeywords.push({
text: keywordText,
matchType: kw.getMatchType()
});
STATE.existingKeywordsNormalized[normalizeKeyword(keywordText)] = true;
}
}
/******************************************************************************
* COLLECT NEGATIVE KEYWORDS (for conflict detection)
******************************************************************************/
function collectNegativeKeywords() {
try {
var query = 'SELECT campaign_criterion.keyword.text ' +
'FROM campaign_criterion ' +
'WHERE campaign_criterion.type = \'KEYWORD\' ' +
'AND campaign_criterion.negative = TRUE ' +
'AND campaign.status != \'REMOVED\'';
var report = AdsApp.report(query);
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
STATE.negativeKeywords.push(row['campaign_criterion.keyword.text']);
}
} catch (e) {
log('WARN', 'Failed to collect negative keywords: ' + e.message);
}
}
/******************************************************************************
* SIGNAL 1: TOP CONVERTING KEYWORDS
******************************************************************************/
function collectTopConvertingKeywords() {
var query = 'SELECT ' +
'keyword.text, ' +
'keyword.match_type, ' +
'campaign.name, ' +
'ad_group.name, ' +
'metrics.conversions, ' +
'metrics.conversions_value, ' +
'metrics.cost_micros, ' +
'metrics.clicks, ' +
'metrics.impressions, ' +
'metrics.ctr, ' +
'metrics.average_cpc ' +
'FROM keyword_view ' +
'WHERE segments.date DURING ' + CONFIG.DATE_RANGE + ' ' +
'AND metrics.conversions >= ' + CONFIG.MIN_CONVERSIONS_TOP_KEYWORDS + ' ' +
'AND campaign.status = \'ENABLED\' ' +
'AND ad_group.status = \'ENABLED\' ' +
'AND keyword.status = \'ENABLED\' ' +
'ORDER BY metrics.conversions DESC ' +
'LIMIT ' + CONFIG.TOP_KEYWORDS_LIMIT;
try {
var report = AdsApp.report(query);
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var campaignName = row['campaign.name'];
if (!matchesCampaignFilters(campaignName)) continue;
var costMicros = parseInt(row['metrics.cost_micros'], 10) || 0;
var cost = costMicros / 1000000;
var clicks = parseInt(row['metrics.clicks'], 10) || 0;
var impressions = parseInt(row['metrics.impressions'], 10) || 0;
var conversions = parseFloat(row['metrics.conversions']) || 0;
var convValue = parseFloat(row['metrics.conversions_value']) || 0;
STATE.signals.topKeywords.push({
keyword: row['keyword.text'],
matchType: row['keyword.match_type'],
campaign: campaignName,
adGroup: row['ad_group.name'],
conversions: conversions,
convValue: convValue,
cost: cost,
clicks: clicks,
impressions: impressions,
ctr: impressions > 0 ? clicks / impressions : 0,
cpa: conversions > 0 ? cost / conversions : null,
roas: cost > 0 ? convValue / cost : null,
avgCpc: clicks > 0 ? cost / clicks : 0,
intent: classifyIntent(row['keyword.text'])
});
}
} catch (e) {
log('ERROR', 'Failed to collect top keywords: ' + e.message);
}
}
/******************************************************************************
* SIGNAL 2: HIGH-VALUE SEARCH TERMS
******************************************************************************/
function collectHighValueSearchTerms() {
var query = 'SELECT ' +
'search_term_view.search_term, ' +
'search_term_view.status, ' +
'campaign.name, ' +
'ad_group.name, ' +
'segments.keyword.info.text, ' +
'segments.keyword.info.match_type, ' +
'metrics.conversions, ' +
'metrics.clicks, ' +
'metrics.impressions, ' +
'metrics.cost_micros, ' +
'metrics.conversions_value ' +
'FROM search_term_view ' +
'WHERE segments.date DURING ' + CONFIG.DATE_RANGE + ' ' +
'AND metrics.conversions >= ' + CONFIG.MIN_CONVERSIONS_SEARCH_TERMS + ' ' +
'AND metrics.clicks >= ' + CONFIG.MIN_CLICKS_SEARCH_TERMS + ' ' +
'ORDER BY metrics.conversions DESC ' +
'LIMIT ' + CONFIG.SEARCH_TERMS_LIMIT;
try {
var report = AdsApp.report(query);
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var campaignName = row['campaign.name'];
if (!matchesCampaignFilters(campaignName)) continue;
var searchTerm = row['search_term_view.search_term'];
var matchedKeyword = row['segments.keyword.info.text'] || '';
// Skip if search term is already a keyword
if (STATE.existingKeywordsNormalized[normalizeKeyword(searchTerm)]) {
continue;
}
var costMicros = parseInt(row['metrics.cost_micros'], 10) || 0;
var cost = costMicros / 1000000;
var clicks = parseInt(row['metrics.clicks'], 10) || 0;
var impressions = parseInt(row['metrics.impressions'], 10) || 0;
var conversions = parseFloat(row['metrics.conversions']) || 0;
var convValue = parseFloat(row['metrics.conversions_value']) || 0;
STATE.signals.searchTerms.push({
searchTerm: searchTerm,
matchedKeyword: matchedKeyword,
matchType: row['segments.keyword.info.match_type'] || 'UNKNOWN',
campaign: campaignName,
adGroup: row['ad_group.name'],
conversions: conversions,
convValue: convValue,
cost: cost,
clicks: clicks,
impressions: impressions,
ctr: impressions > 0 ? clicks / impressions : 0,
cpa: conversions > 0 ? cost / conversions : null,
intent: classifyIntent(searchTerm),
status: row['search_term_view.status']
});
}
} catch (e) {
log('ERROR', 'Failed to collect search terms: ' + e.message);
}
}
/******************************************************************************
* SIGNAL 3: HIGH-CTR ENGAGEMENT SIGNALS
******************************************************************************/
function collectHighCTRSignals() {
var minCtrDecimal = CONFIG.MIN_CTR_ENGAGEMENT;
var query = 'SELECT ' +
'keyword.text, ' +
'campaign.name, ' +
'ad_group.name, ' +
'metrics.clicks, ' +
'metrics.impressions, ' +
'metrics.ctr, ' +
'metrics.conversions, ' +
'metrics.cost_micros ' +
'FROM keyword_view ' +
'WHERE segments.date DURING ' + CONFIG.DATE_RANGE + ' ' +
'AND metrics.clicks >= ' + CONFIG.MIN_CLICKS_ENGAGEMENT + ' ' +
'AND campaign.status = \'ENABLED\' ' +
'AND keyword.status = \'ENABLED\' ' +
'ORDER BY metrics.ctr DESC ' +
'LIMIT 200';
try {
var report = AdsApp.report(query);
var rows = report.rows();
var count = 0;
while (rows.hasNext() && count < CONFIG.ENGAGEMENT_LIMIT) {
var row = rows.next();
var campaignName = row['campaign.name'];
if (!matchesCampaignFilters(campaignName)) continue;
var clicks = parseInt(row['metrics.clicks'], 10) || 0;
var impressions = parseInt(row['metrics.impressions'], 10) || 0;
var ctr = impressions > 0 ? clicks / impressions : 0;
if (ctr < minCtrDecimal) continue;
var costMicros = parseInt(row['metrics.cost_micros'], 10) || 0;
var conversions = parseFloat(row['metrics.conversions']) || 0;
STATE.signals.highCTR.push({
keyword: row['keyword.text'],
campaign: campaignName,
adGroup: row['ad_group.name'],
ctr: ctr,
clicks: clicks,
impressions: impressions,
conversions: conversions,
cost: costMicros / 1000000,
intent: classifyIntent(row['keyword.text'])
});
count++;
}
} catch (e) {
log('ERROR', 'Failed to collect high-CTR signals: ' + e.message);
}
}
/******************************************************************************
* SIGNAL 4: INTENT DISTRIBUTION ANALYSIS
******************************************************************************/
function analyzeIntentDistribution() {
var distribution = {
BRANDED: { count: 0, conversions: 0, cost: 0 },
COMPETITOR: { count: 0, conversions: 0, cost: 0 },
TRANSACTIONAL: { count: 0, conversions: 0, cost: 0 },
INFORMATIONAL: { count: 0, conversions: 0, cost: 0 },
NAVIGATIONAL: { count: 0, conversions: 0, cost: 0 },
COMMERCIAL: { count: 0, conversions: 0, cost: 0 },
OTHER: { count: 0, conversions: 0, cost: 0 }
};
// Analyze from existing keywords
STATE.existingKeywords.forEach(function(kw) {
var intent = classifyIntent(kw.text);
distribution[intent].count++;
});
// Analyze conversions from top keywords
STATE.signals.topKeywords.forEach(function(kw) {
var intent = kw.intent;
distribution[intent].conversions += kw.conversions;
distribution[intent].cost += kw.cost;
});
STATE.signals.intentDistribution = distribution;
// Calculate percentages and identify gaps
var total = STATE.existingKeywords.length;
var gaps = [];
for (var intent in distribution) {
var pct = total > 0 ? (distribution[intent].count / total * 100) : 0;
distribution[intent].percentage = pct;
// Identify potential gaps
if (intent === 'TRANSACTIONAL' && pct < 30) {
gaps.push('Low TRANSACTIONAL coverage (' + pct.toFixed(1) + '%) - high-intent keywords underrepresented');
}
if (intent === 'NAVIGATIONAL' && pct < 5) {
gaps.push('Very low NAVIGATIONAL coverage (' + pct.toFixed(1) + '%) - missing local/location keywords');
}
if (intent === 'COMMERCIAL' && pct < 10) {
gaps.push('Low COMMERCIAL coverage (' + pct.toFixed(1) + '%) - missing comparison/review keywords');
}
}
STATE.context.intentGaps = gaps;
}
/******************************************************************************
* SIGNAL 5: COMPETITOR SIGNALS
******************************************************************************/
function collectCompetitorSignals() {
try {
var query = 'SELECT ' +
'auction_insight.domain, ' +
'metrics.auction_impression_share ' +
'FROM auction_insight ' +
'WHERE segments.date DURING ' + CONFIG.DATE_RANGE;
var report = AdsApp.report(query);
var rows = report.rows();
var competitorMap = {};
while (rows.hasNext()) {
var row = rows.next();
var domain = row['auction_insight.domain'];
var impressionShare = parseFloat(row['metrics.auction_impression_share']) || 0;
if (!competitorMap[domain]) {
competitorMap[domain] = { domain: domain, impressionShare: 0, count: 0 };
}
competitorMap[domain].impressionShare += impressionShare;
competitorMap[domain].count++;
}
// Calculate average and filter
for (var d in competitorMap) {
var comp = competitorMap[d];
comp.avgImpressionShare = comp.impressionShare / comp.count;
if (comp.avgImpressionShare >= CONFIG.MIN_IMPRESSION_SHARE_COMPETITOR) {
STATE.signals.competitors.push(comp);
}
}
// Sort by impression share
STATE.signals.competitors.sort(function(a, b) {
return b.avgImpressionShare - a.avgImpressionShare;
});
// Keep top 10
STATE.signals.competitors = STATE.signals.competitors.slice(0, 10);
} catch (e) {
log('WARN', 'Failed to collect competitor signals: ' + e.message);
}
}
/******************************************************************************
* AUTO-DETECTION: BRAND TERMS
* Detects brand terms from:
* 1. Account name
* 2. Primary domain from landing page URLs
* 3. Keywords with abnormally high CTR (>10%) - brand keywords signature
* 4. Keywords in campaigns with "brand" in the name
******************************************************************************/
function autoDetectBrandTerms() {
// If user provided brand terms, use those
if (CONFIG.BRAND_TERMS && CONFIG.BRAND_TERMS.length > 0) {
STATE.detectedBrandTerms = CONFIG.BRAND_TERMS.slice();
log('DEBUG', 'Using user-provided brand terms');
return;
}
if (!CONFIG.AUTO_DETECT_BRAND) {
log('DEBUG', 'Brand auto-detection disabled');
return;
}
var brandCandidates = {};
// Source 1: Account name
var accountName = AdsApp.currentAccount().getName();
STATE.context.accountName = accountName;
var accountWords = extractBrandWords(accountName);
accountWords.forEach(function(word) {
if (!brandCandidates[word]) brandCandidates[word] = { word: word, score: 0, sources: [] };
brandCandidates[word].score += 10;
brandCandidates[word].sources.push('account_name');
});
// Source 2: Primary domain from URLs
try {
var urlQuery = 'SELECT ad_group_ad.ad.final_urls ' +
'FROM ad_group_ad ' +
'WHERE ad_group_ad.status = \'ENABLED\' ' +
'LIMIT 50';
var report = AdsApp.report(urlQuery);
var rows = report.rows();
var domainCounts = {};
while (rows.hasNext()) {
var row = rows.next();
var urlsJson = row['ad_group_ad.ad.final_urls'];
if (!urlsJson) continue;
try {
var urls = JSON.parse(urlsJson);
urls.forEach(function(url) {
var domain = extractDomainFromUrl(url);
if (domain) {
if (!domainCounts[domain]) domainCounts[domain] = 0;
domainCounts[domain]++;
}
});
} catch (e) {}
}
// Find primary domain
var primaryDomain = '';
var maxCount = 0;
for (var domain in domainCounts) {
if (domainCounts[domain] > maxCount) {
maxCount = domainCounts[domain];
primaryDomain = domain;
}
}
if (primaryDomain) {
STATE.context.primaryDomain = primaryDomain;
var domainWords = extractBrandWords(primaryDomain);
domainWords.forEach(function(word) {
if (!brandCandidates[word]) brandCandidates[word] = { word: word, score: 0, sources: [] };
brandCandidates[word].score += 15;
brandCandidates[word].sources.push('primary_domain');
});
}
} catch (e) {
log('DEBUG', 'Failed to extract domain: ' + e.message);
}
// Source 3: Keywords with abnormally high CTR (>10%)
// Brand keywords typically have 10-30%+ CTR
STATE.signals.highCTR.forEach(function(kw) {
if (kw.ctr >= 0.10) { // 10%+ CTR is a strong brand signal
var words = extractBrandWords(kw.keyword);
words.forEach(function(word) {
if (!brandCandidates[word]) brandCandidates[word] = { word: word, score: 0, sources: [] };
brandCandidates[word].score += 5;
brandCandidates[word].sources.push('high_ctr_keyword');
});
}
});
// Source 4: Keywords in campaigns with "brand" in the name
try {
var brandCampaignQuery = 'SELECT keyword.text, campaign.name ' +
'FROM keyword_view ' +
'WHERE campaign.status = \'ENABLED\' ' +
'AND keyword.status = \'ENABLED\' ' +
'LIMIT 500';
var report2 = AdsApp.report(brandCampaignQuery);
var rows2 = report2.rows();
while (rows2.hasNext()) {
var row2 = rows2.next();
var campaignName = (row2['campaign.name'] || '').toLowerCase();
if (campaignName.indexOf('brand') !== -1 ||
campaignName.indexOf('branded') !== -1 ||
campaignName.indexOf('trademark') !== -1) {
var kwText = row2['keyword.text'];
STATE.signals.brandCampaignKeywords.push(kwText);
var words = extractBrandWords(kwText);
words.forEach(function(word) {
if (!brandCandidates[word]) brandCandidates[word] = { word: word, score: 0, sources: [] };
brandCandidates[word].score += 8;
brandCandidates[word].sources.push('brand_campaign');
});
}
}
} catch (e) {
log('DEBUG', 'Failed to scan brand campaigns: ' + e.message);
}
// Select top scoring brand terms
var sortedCandidates = [];
for (var word in brandCandidates) {
if (brandCandidates[word].score >= 8) { // Minimum score threshold
sortedCandidates.push(brandCandidates[word]);
}
}
sortedCandidates.sort(function(a, b) { return b.score - a.score; });
STATE.detectedBrandTerms = sortedCandidates.slice(0, 10).map(function(c) { return c.word; });
log('DEBUG', 'Auto-detected brand terms: ' + JSON.stringify(sortedCandidates.slice(0, 5)));
}
/******************************************************************************
* AUTO-DETECTION: COMPETITOR TERMS
* Detects competitor terms from:
* 1. Auction Insights domains (already collected)
* 2. Keywords in campaigns with "competitor" or "conquest" in the name
******************************************************************************/
function autoDetectCompetitorTerms() {
// If user provided competitor terms, use those
if (CONFIG.COMPETITOR_TERMS && CONFIG.COMPETITOR_TERMS.length > 0) {
STATE.detectedCompetitorTerms = CONFIG.COMPETITOR_TERMS.slice();
log('DEBUG', 'Using user-provided competitor terms');
return;
}
if (!CONFIG.AUTO_DETECT_COMPETITORS) {
log('DEBUG', 'Competitor auto-detection disabled');
return;
}
var competitorCandidates = {};
// Source 1: Auction Insights domains (already collected in STATE.signals.competitors)
STATE.signals.competitors.forEach(function(comp) {
var brandName = extractBrandFromDomain(comp.domain);
if (brandName && brandName !== STATE.context.primaryDomain) {
if (!competitorCandidates[brandName]) {
competitorCandidates[brandName] = { word: brandName, score: 0, sources: [] };
}
competitorCandidates[brandName].score += Math.round(comp.avgImpressionShare * 100);
competitorCandidates[brandName].sources.push('auction_insights');
}
});
// Source 2: Keywords in campaigns with "competitor" or "conquest" in the name
try {
var competitorCampaignQuery = 'SELECT keyword.text, campaign.name ' +
'FROM keyword_view ' +
'WHERE campaign.status = \'ENABLED\' ' +
'AND keyword.status = \'ENABLED\' ' +
'LIMIT 500';
var report = AdsApp.report(competitorCampaignQuery);
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var campaignName = (row['campaign.name'] || '').toLowerCase();
if (campaignName.indexOf('competitor') !== -1 ||
campaignName.indexOf('conquest') !== -1 ||
campaignName.indexOf('competitive') !== -1) {
var kwText = row['keyword.text'];
STATE.signals.competitorCampaignKeywords.push(kwText);
var words = extractBrandWords(kwText);
words.forEach(function(word) {
if (!competitorCandidates[word]) {
competitorCandidates[word] = { word: word, score: 0, sources: [] };
}
competitorCandidates[word].score += 10;
competitorCandidates[word].sources.push('competitor_campaign');
});
}
}
} catch (e) {
log('DEBUG', 'Failed to scan competitor campaigns: ' + e.message);
}
// Select top scoring competitor terms
var sortedCandidates = [];
for (var word in competitorCandidates) {
// Exclude our own brand terms
if (STATE.detectedBrandTerms.indexOf(word) === -1 && competitorCandidates[word].score >= 5) {
sortedCandidates.push(competitorCandidates[word]);
}
}
sortedCandidates.sort(function(a, b) { return b.score - a.score; });
STATE.detectedCompetitorTerms = sortedCandidates.slice(0, 15).map(function(c) { return c.word; });
log('DEBUG', 'Auto-detected competitor terms: ' + JSON.stringify(sortedCandidates.slice(0, 5)));
}
/******************************************************************************
* HELPER: Extract brand-worthy words from text
******************************************************************************/
function extractBrandWords(text) {
if (!text) return [];
// Common words to exclude
var stopWords = [
'the', 'and', 'for', 'with', 'from', 'this', 'that', 'your', 'our',
'inc', 'llc', 'ltd', 'corp', 'company', 'co', 'group', 'services',
'solutions', 'agency', 'www', 'com', 'net', 'org', 'io', 'app',
'best', 'top', 'free', 'cheap', 'online', 'buy', 'get', 'new'
];
// Clean and split
var words = text.toLowerCase()
.replace(/[^a-z0-9\s]/g, ' ')
.split(/\s+/)
.filter(function(word) {
return word.length >= 3 &&
word.length <= 20 &&
stopWords.indexOf(word) === -1 &&
!/^\d+$/.test(word); // Exclude pure numbers
});
return words;
}
/******************************************************************************
* HELPER: Extract domain from URL
******************************************************************************/
function extractDomainFromUrl(url) {
try {
var match = url.match(/^https?:\/\/(?:www\.)?([^\/]+)/i);
if (match && match[1]) {
// Remove common TLDs to get brand name
return match[1].replace(/\.(com|net|org|io|co|app|biz|info)$/i, '');
}
} catch (e) {}
return '';
}
/******************************************************************************
* HELPER: Extract brand name from competitor domain
******************************************************************************/
function extractBrandFromDomain(domain) {
if (!domain) return '';
// Remove www. prefix
domain = domain.replace(/^www\./i, '');
// Remove common TLDs
var brandName = domain.replace(/\.(com|net|org|io|co|app|biz|info|co\.[a-z]{2}|[a-z]{2})$/i, '');
// If it's still a reasonable length, return it
if (brandName.length >= 3 && brandName.length <= 30) {
return brandName.toLowerCase();
}
return '';
}
/******************************************************************************
* SIGNAL 6: LANDING PAGE THEME EXTRACTION
******************************************************************************/
function extractLandingPageThemes() {
try {
var query = 'SELECT ' +
'ad_group_ad.ad.final_urls, ' +
'metrics.conversions ' +
'FROM ad_group_ad ' +
'WHERE segments.date DURING ' + CONFIG.DATE_RANGE + ' ' +
'AND ad_group_ad.status = \'ENABLED\' ' +
'AND metrics.impressions > 100 ' +
'ORDER BY metrics.conversions DESC ' +
'LIMIT ' + CONFIG.LANDING_PAGE_SAMPLE_SIZE;
var report = AdsApp.report(query);
var rows = report.rows();
var urlsProcessed = {};
var themes = {};
while (rows.hasNext()) {
var row = rows.next();
var urlsJson = row['ad_group_ad.ad.final_urls'];
if (!urlsJson) continue;
// Parse the URLs array
var urls;
try {
urls = JSON.parse(urlsJson);
} catch (e) {
urls = [urlsJson];
}
urls.forEach(function(url) {
if (urlsProcessed[url]) return;
urlsProcessed[url] = true;
// Extract themes from URL path
var extractedThemes = extractThemesFromURL(url);
extractedThemes.forEach(function(theme) {
if (!themes[theme]) themes[theme] = 0;
themes[theme]++;
});
});
}
// Convert to array and sort by frequency
for (var theme in themes) {
if (themes[theme] >= 2) { // Only include themes appearing 2+ times
STATE.signals.landingPageThemes.push({
theme: theme,
frequency: themes[theme]
});
}
}
STATE.signals.landingPageThemes.sort(function(a, b) {
return b.frequency - a.frequency;
});
// Keep top themes
STATE.signals.landingPageThemes = STATE.signals.landingPageThemes.slice(0, 30);
} catch (e) {
log('WARN', 'Failed to extract landing page themes: ' + e.message);
}
}
function extractThemesFromURL(url) {
var themes = [];
try {
// Remove protocol and domain
var path = url.replace(/^https?:\/\/[^\/]+\/?/, '');
// Split by common delimiters
var parts = path.split(/[\/\-_?&=]/);
parts.forEach(function(part) {
// Clean and filter
part = part.toLowerCase().replace(/[^a-z0-9]/g, '');
// Skip common non-theme words and short strings
var skipWords = ['www', 'com', 'html', 'php', 'aspx', 'index', 'page', 'category', 'tag', 'utm', 'source', 'medium', 'campaign'];
if (part.length >= 3 && skipWords.indexOf(part) === -1) {
themes.push(part);
}
});
} catch (e) {
// Ignore URL parsing errors
}
return themes;
}
/******************************************************************************
* SIGNAL 7: AD COPY THEME MINING
******************************************************************************/
function mineAdCopyThemes() {
try {
var query = 'SELECT ' +
'ad_group_ad.ad.responsive_search_ad.headlines, ' +
'ad_group_ad.ad.responsive_search_ad.descriptions, ' +
'metrics.conversions ' +
'FROM ad_group_ad ' +
'WHERE segments.date DURING ' + CONFIG.DATE_RANGE + ' ' +
'AND ad_group_ad.ad.type = \'RESPONSIVE_SEARCH_AD\' ' +
'AND ad_group_ad.status = \'ENABLED\' ' +
'AND metrics.impressions > 100 ' +
'ORDER BY metrics.conversions DESC ' +
'LIMIT ' + CONFIG.AD_COPY_SAMPLE_SIZE;
var report = AdsApp.report(query);
var rows = report.rows();
var themes = {};
while (rows.hasNext()) {
var row = rows.next();
var headlinesJson = row['ad_group_ad.ad.responsive_search_ad.headlines'];
var descriptionsJson = row['ad_group_ad.ad.responsive_search_ad.descriptions'];
// Extract themes from headlines
if (headlinesJson) {
extractThemesFromAdAssets(headlinesJson, themes);
}
// Extract themes from descriptions
if (descriptionsJson) {
extractThemesFromAdAssets(descriptionsJson, themes);
}
}
// Convert to array and sort
for (var theme in themes) {
if (themes[theme] >= 3) { // Only include themes appearing 3+ times
STATE.signals.adCopyThemes.push({
theme: theme,
frequency: themes[theme]
});
}
}
STATE.signals.adCopyThemes.sort(function(a, b) {
return b.frequency - a.frequency;
});
// Keep top themes
STATE.signals.adCopyThemes = STATE.signals.adCopyThemes.slice(0, 30);
} catch (e) {
log('WARN', 'Failed to mine ad copy themes: ' + e.message);
}
}
function extractThemesFromAdAssets(assetsJson, themes) {
try {
var assets = JSON.parse(assetsJson);
assets.forEach(function(asset) {
var text = asset.text || '';
// Extract key phrases
var phrases = extractKeyPhrases(text);
phrases.forEach(function(phrase) {
if (!themes[phrase]) themes[phrase] = 0;
themes[phrase]++;
});
});
} catch (e) {
// Ignore parsing errors
}
}
function extractKeyPhrases(text) {
var phrases = [];
// Common value prop patterns
var patterns = [
/free\s+\w+/gi,
/\d+%\s+off/gi,
/save\s+\$?\d+/gi,
/\d+\s+years?/gi,
/24\/7/gi,
/same\s+day/gi,
/next\s+day/gi,
/no\s+\w+\s+fee/gi,
/licensed/gi,
/certified/gi,
/insured/gi,
/guaranteed/gi,
/award\s*winning/gi,
/top\s+rated/gi,
/best\s+\w+/gi
];
patterns.forEach(function(pattern) {
var matches = text.match(pattern);
if (matches) {
matches.forEach(function(match) {
phrases.push(match.toLowerCase().trim());
});
}
});
return phrases;
}
/******************************************************************************
* PHASE 2: CONTEXT BUILDING
******************************************************************************/
function buildContext() {
STATE.context.accountName = AdsApp.currentAccount().getName();
STATE.context.totalKeywords = STATE.existingKeywords.length;
// Calculate averages from top keywords
if (STATE.signals.topKeywords.length > 0) {
var totalCost = 0;
var totalConversions = 0;
var totalClicks = 0;
var totalImpressions = 0;
var totalConvValue = 0;
STATE.signals.topKeywords.forEach(function(kw) {
totalCost += kw.cost;
totalConversions += kw.conversions;
totalClicks += kw.clicks;
totalImpressions += kw.impressions;
totalConvValue += kw.convValue;
});
STATE.context.totalCost = totalCost;
STATE.context.totalConversions = totalConversions;
STATE.context.avgCPA = totalConversions > 0 ? totalCost / totalConversions : 0;
STATE.context.avgCTR = totalImpressions > 0 ? totalClicks / totalImpressions : 0;
STATE.context.avgConvRate = totalClicks > 0 ? totalConversions / totalClicks : 0;
STATE.context.avgROAS = totalCost > 0 ? totalConvValue / totalCost : 0;
STATE.context.avgCPC = totalClicks > 0 ? totalCost / totalClicks : 0;
}
// Detect business type if not specified
STATE.context.businessType = CONFIG.BUSINESS_TYPE || detectBusinessType();
STATE.context.industry = CONFIG.INDUSTRY || '';
// Use configured targets or auto-detected
STATE.context.targetCPA = CONFIG.TARGET_CPA > 0 ? CONFIG.TARGET_CPA : STATE.context.avgCPA;
STATE.context.targetROAS = CONFIG.TARGET_ROAS > 0 ? CONFIG.TARGET_ROAS : STATE.context.avgROAS;
log('INFO', ' Account: ' + STATE.context.accountName);
log('INFO', ' Total Keywords: ' + STATE.context.totalKeywords);
log('INFO', ' Avg CPA: $' + STATE.context.avgCPA.toFixed(2));
log('INFO', ' Avg CTR: ' + (STATE.context.avgCTR * 100).toFixed(2) + '%');
log('INFO', ' Business Type: ' + (STATE.context.businessType || 'auto-detect'));
}
function detectBusinessType() {
var indicators = {
ecommerce: 0,
saas: 0,
local_service: 0,
lead_gen: 0
};
// Check landing page themes
STATE.signals.landingPageThemes.forEach(function(t) {
var theme = t.theme.toLowerCase();
if (['shop', 'cart', 'product', 'buy', 'checkout', 'shipping'].indexOf(theme) !== -1) {
indicators.ecommerce += t.frequency;
}
if (['software', 'platform', 'demo', 'trial', 'pricing', 'enterprise', 'saas'].indexOf(theme) !== -1) {
indicators.saas += t.frequency;
}
if (['service', 'local', 'emergency', 'repair', 'install'].indexOf(theme) !== -1) {
indicators.local_service += t.frequency;
}
if (['quote', 'contact', 'consultation', 'estimate', 'inquiry'].indexOf(theme) !== -1) {
indicators.lead_gen += t.frequency;
}
});
// Check intent patterns
var navCount = STATE.signals.intentDistribution.NAVIGATIONAL.count;
if (navCount > STATE.existingKeywords.length * 0.1) {
indicators.local_service += 10;
}
// Find highest indicator
var maxType = '';
var maxScore = 0;
for (var type in indicators) {
if (indicators[type] > maxScore) {
maxScore = indicators[type];
maxType = type;
}
}
return maxScore > 5 ? maxType : '';
}
/******************************************************************************
* PHASE 3: AI SUGGESTION GENERATION
******************************************************************************/
function generateAISuggestions() {
var prompt = buildExpansionPrompt();
log('INFO', 'Calling Claude API...');
log('DEBUG', 'Prompt length: ' + prompt.length + ' characters');
var response = callClaudeAPIWithRetry(prompt);
if (response && response.content && response.content.length > 0) {
var suggestions = parseClaudeResponse(response);
STATE.suggestions = suggestions;
log('INFO', ' ✓ Received ' + suggestions.length + ' suggestions from Claude');
} else {
log('ERROR', 'No valid response from Claude API');
STATE.suggestions = [];
}
}
function buildExpansionPrompt() {
var topKeywordsData = STATE.signals.topKeywords.slice(0, 25).map(function(kw) {
return {
keyword: kw.keyword,
conversions: kw.conversions,
cpa: kw.cpa ? '$' + kw.cpa.toFixed(2) : 'N/A',
intent: kw.intent,
campaign: kw.campaign
};
});
var searchTermsData = STATE.signals.searchTerms.slice(0, 40).map(function(st) {
return {
searchTerm: st.searchTerm,
conversions: st.conversions,
cpa: st.cpa ? '$' + st.cpa.toFixed(2) : 'N/A',
intent: st.intent,
matchedKeyword: st.matchedKeyword
};
});
var highCTRData = STATE.signals.highCTR.slice(0, 15).map(function(hc) {
return {
keyword: hc.keyword,
ctr: (hc.ctr * 100).toFixed(2) + '%',
clicks: hc.clicks,
conversions: hc.conversions,
intent: hc.intent
};
});
var intentSummary = {};
for (var intent in STATE.signals.intentDistribution) {
var data = STATE.signals.intentDistribution[intent];
intentSummary[intent] = {
count: data.count,
percentage: data.percentage ? data.percentage.toFixed(1) + '%' : '0%',
conversions: data.conversions
};
}
var competitorDomains = STATE.signals.competitors.map(function(c) {
return c.domain + ' (' + (c.avgImpressionShare * 100).toFixed(1) + '% IS)';
});
var landingThemes = STATE.signals.landingPageThemes.slice(0, 20).map(function(t) {
return t.theme;
});
var adThemes = STATE.signals.adCopyThemes.slice(0, 20).map(function(t) {
return t.theme;
});
var existingSample = STATE.existingKeywords.slice(0, 150).map(function(k) {
return k.text;
});
var prompt = 'You are an expert Google Ads keyword strategist. Analyze the following account intelligence and suggest ' + CONFIG.TARGET_SUGGESTIONS + ' new keywords to add.\n\n';
prompt += '## ACCOUNT CONTEXT\n';
prompt += 'Account Name: ' + STATE.context.accountName + '\n';
prompt += 'Business Type: ' + (STATE.context.businessType || 'Not specified') + '\n';
prompt += 'Industry: ' + (STATE.context.industry || 'Not specified') + '\n';
prompt += 'Target CPA: $' + STATE.context.targetCPA.toFixed(2) + '\n';
prompt += 'Current Avg CPA: $' + STATE.context.avgCPA.toFixed(2) + '\n';
prompt += 'Current Avg CTR: ' + (STATE.context.avgCTR * 100).toFixed(2) + '%\n';
prompt += 'Current Avg Conv Rate: ' + (STATE.context.avgConvRate * 100).toFixed(2) + '%\n';
prompt += 'Total Existing Keywords: ' + STATE.context.totalKeywords + '\n\n';
prompt += '## INTELLIGENCE SIGNALS\n\n';
prompt += '### Signal 1: Top Converting Keywords (' + STATE.signals.topKeywords.length + ' keywords)\n';
prompt += 'These are the best performers - suggest variations, long-tail expansions, and related terms:\n';
prompt += JSON.stringify(topKeywordsData, null, 2) + '\n\n';
prompt += '### Signal 2: High-Value Search Terms (' + STATE.signals.searchTerms.length + ' terms)\n';
prompt += 'These searches converted but are NOT yet keywords - strong candidates for addition:\n';
prompt += JSON.stringify(searchTermsData, null, 2) + '\n\n';
prompt += '### Signal 3: High-CTR Engagement (' + STATE.signals.highCTR.length + ' items)\n';
prompt += 'High engagement signals strong relevance - consider related keywords:\n';
prompt += JSON.stringify(highCTRData, null, 2) + '\n\n';
prompt += '### Signal 4: Intent Distribution\n';
prompt += 'Current keyword coverage by intent:\n';
prompt += JSON.stringify(intentSummary, null, 2) + '\n';
if (STATE.context.intentGaps.length > 0) {
prompt += 'GAPS IDENTIFIED:\n';
STATE.context.intentGaps.forEach(function(gap) {
prompt += '- ' + gap + '\n';
});
}
prompt += '\n';
prompt += '### Signal 5: Competitor Landscape\n';
prompt += 'Top competitors in this space:\n';
prompt += competitorDomains.join(', ') + '\n\n';
prompt += '### Signal 6: Landing Page Themes\n';
prompt += 'Key themes extracted from landing pages (what the business offers):\n';
prompt += landingThemes.join(', ') + '\n\n';
prompt += '### Signal 7: Ad Copy Value Propositions\n';
prompt += 'Key messaging themes from top-performing ads:\n';
prompt += adThemes.join(', ') + '\n\n';
prompt += '## EXISTING KEYWORDS (DO NOT SUGGEST THESE OR CLOSE VARIANTS)\n';
prompt += existingSample.join(', ') + '\n\n';
if (CONFIG.BLOCKED_TERMS.length > 0) {
prompt += '## BLOCKED TERMS (NEVER SUGGEST)\n';
prompt += CONFIG.BLOCKED_TERMS.join(', ') + '\n\n';
}
prompt += '## OUTPUT REQUIREMENTS\n';
prompt += 'Return a JSON array of keyword suggestions. For each keyword provide:\n';
prompt += '1. keyword: The suggested keyword text (lowercase, no special characters except spaces)\n';
prompt += '2. matchType: Recommended match type (EXACT, PHRASE, or BROAD)\n';
prompt += '3. intent: Intent category (TRANSACTIONAL, COMMERCIAL, INFORMATIONAL, NAVIGATIONAL, BRANDED, COMPETITOR, OTHER)\n';
prompt += '4. confidence: Your confidence score 0.0-1.0\n';
prompt += '5. reasoning: Brief explanation of why this keyword (which signals support it, max 100 chars)\n';
prompt += '6. suggestedCampaign: Which existing campaign this fits best, or "NEW" if new campaign needed\n';
prompt += '7. suggestedAdGroup: Which existing ad group this fits, or "NEW"\n';
prompt += '8. estimatedCPA: Estimated CPA based on similar keywords (number only, no $)\n';
prompt += '9. priority: HIGH, MEDIUM, or LOW based on expected impact\n\n';
prompt += 'IMPORTANT GUIDELINES:\n';
prompt += '- Prioritize TRANSACTIONAL and COMMERCIAL intent keywords (higher conversion potential)\n';
prompt += '- Include long-tail variations of top converters\n';
prompt += '- Fill identified intent gaps\n';
prompt += '- Align suggestions with landing page themes\n';
prompt += '- DO NOT suggest keywords that are semantically identical to existing ones\n';
prompt += '- Focus on QUALITY over quantity - only suggest keywords likely to convert\n';
prompt += '- Suggest EXACT match for high-intent, specific keywords\n';
prompt += '- Suggest PHRASE match for moderate specificity\n';
prompt += '- Suggest BROAD match only for discovery/testing\n\n';
prompt += 'Return ONLY the JSON array, no other text or markdown formatting.';
return prompt;
}
function callClaudeAPIWithRetry(prompt) {
var lastError;
for (var attempt = 1; attempt <= CONFIG.API_RETRY_ATTEMPTS; attempt++) {
try {
var response = callClaudeAPI(prompt);
if (response.error) {
if (response.error.type === 'rate_limit_error' || response.error.type === 'overloaded_error') {
log('WARN', 'API rate limited, attempt ' + attempt + '/' + CONFIG.API_RETRY_ATTEMPTS);
if (attempt < CONFIG.API_RETRY_ATTEMPTS) {
Utilities.sleep(CONFIG.API_RETRY_DELAY_MS * Math.pow(2, attempt - 1));
continue;
}
}
throw new Error(response.error.message || 'API error');
}
return response;
} catch (e) {
lastError = e;
log('WARN', 'API call failed (attempt ' + attempt + '): ' + e.message);
if (attempt < CONFIG.API_RETRY_ATTEMPTS) {
Utilities.sleep(CONFIG.API_RETRY_DELAY_MS * Math.pow(2, attempt - 1));
}
}
}
throw new Error('API failed after ' + CONFIG.API_RETRY_ATTEMPTS + ' attempts: ' + (lastError ? lastError.message : 'Unknown error'));
}
function callClaudeAPI(prompt) {
var url = 'https://api.anthropic.com/v1/messages';
var payload = {
model: CONFIG.CLAUDE_MODEL,
max_tokens: 8192,
temperature: CONFIG.AI_TEMPERATURE,
messages: [{
role: 'user',
content: prompt
}]
};
var options = {
method: 'post',
contentType: 'application/json',
headers: {
'x-api-key': CONFIG.CLAUDE_API_KEY,
'anthropic-version': '2023-06-01'
},
payload: JSON.stringify(payload),
muteHttpExceptions: true
};
var response = UrlFetchApp.fetch(url, options);
return JSON.parse(response.getContentText());
}
function parseClaudeResponse(response) {
try {
var content = response.content[0].text;
// Remove potential markdown code blocks
content = content.replace(/```json\n?/g, '').replace(/```\n?/g, '').trim();
// Try to find JSON array in the response
var jsonStart = content.indexOf('[');
var jsonEnd = content.lastIndexOf(']');
if (jsonStart !== -1 && jsonEnd !== -1) {
content = content.substring(jsonStart, jsonEnd + 1);
}
var suggestions = JSON.parse(content);
// Validate and clean each suggestion
return suggestions.filter(function(s) {
return s.keyword &&
typeof s.keyword === 'string' &&
s.keyword.trim().length > 0;
}).map(function(s) {
return {
keyword: s.keyword.toLowerCase().trim(),
matchType: validateMatchType(s.matchType),
intent: validateIntent(s.intent),
confidence: Math.min(Math.max(parseFloat(s.confidence) || 0.5, 0), 1),
reasoning: (s.reasoning || '').substring(0, 200),
suggestedCampaign: s.suggestedCampaign || 'NEW',
suggestedAdGroup: s.suggestedAdGroup || 'NEW',
estimatedCPA: parseFloat(s.estimatedCPA) || STATE.context.avgCPA,
priority: validatePriority(s.priority)
};
});
} catch (e) {
log('ERROR', 'Failed to parse Claude response: ' + e.message);
log('DEBUG', 'Response content: ' + (response.content ? response.content[0].text.substring(0, 500) : 'empty'));
return [];
}
}
function validateMatchType(matchType) {
var valid = ['EXACT', 'PHRASE', 'BROAD'];
matchType = (matchType || '').toUpperCase();
return valid.indexOf(matchType) !== -1 ? matchType : 'PHRASE';
}
function validateIntent(intent) {
var valid = ['BRANDED', 'COMPETITOR', 'TRANSACTIONAL', 'INFORMATIONAL', 'NAVIGATIONAL', 'COMMERCIAL', 'OTHER'];
intent = (intent || '').toUpperCase();
return valid.indexOf(intent) !== -1 ? intent : 'OTHER';
}
function validatePriority(priority) {
var valid = ['HIGH', 'MEDIUM', 'LOW'];
priority = (priority || '').toUpperCase();
return valid.indexOf(priority) !== -1 ? priority : 'MEDIUM';
}
/******************************************************************************
* PHASE 4: FILTERING SUGGESTIONS
******************************************************************************/
function filterSuggestions() {
var beforeCount = STATE.suggestions.length;
// Step 1: Exact match deduplication
log('INFO', ' Exact match deduplication...');
var afterExact = exactMatchDedup(STATE.suggestions);
log('INFO', ' Removed ' + (STATE.suggestions.length - afterExact.length) + ' exact duplicates');
// Step 2: Blocked terms filter
log('INFO', ' Blocked terms filter...');
var afterBlocked = filterBlockedTerms(afterExact);
log('INFO', ' Removed ' + (afterExact.length - afterBlocked.length) + ' blocked terms');
// Step 3: Negative keyword conflict detection
log('INFO', ' Negative keyword conflict detection...');
var afterConflicts = detectNegativeConflicts(afterBlocked);
// Step 4: Minimum confidence filter
log('INFO', ' Minimum confidence filter...');
var afterConfidence = afterConflicts.filter(function(s) {
return s.confidence >= CONFIG.MIN_CONFIDENCE_SCORE;
});
log('INFO', ' Removed ' + (afterConflicts.length - afterConfidence.length) + ' low-confidence suggestions');
// Step 5: Semantic deduplication (if enabled)
if (CONFIG.ENABLE_SEMANTIC_DEDUP && afterConfidence.length > 0) {
log('INFO', ' Semantic deduplication...');
checkTimeLimit();
afterConfidence = semanticDedup(afterConfidence);
}
STATE.filteredSuggestions = afterConfidence;
log('INFO', ' ✓ Filtered from ' + beforeCount + ' to ' + STATE.filteredSuggestions.length + ' suggestions');
}
function exactMatchDedup(suggestions) {
return suggestions.filter(function(s) {
var normalized = normalizeKeyword(s.keyword);
return !STATE.existingKeywordsNormalized[normalized];
});
}
function filterBlockedTerms(suggestions) {
if (CONFIG.BLOCKED_TERMS.length === 0) return suggestions;
var blockedLower = CONFIG.BLOCKED_TERMS.map(function(t) { return t.toLowerCase(); });
return suggestions.filter(function(s) {
var kwLower = s.keyword.toLowerCase();
for (var i = 0; i < blockedLower.length; i++) {
if (kwLower.indexOf(blockedLower[i]) !== -1) {
return false;
}
}
return true;
});
}
function detectNegativeConflicts(suggestions) {
if (STATE.negativeKeywords.length === 0) return suggestions;
var negativesLower = STATE.negativeKeywords.map(function(n) { return n.toLowerCase(); });
return suggestions.map(function(s) {
var kwLower = s.keyword.toLowerCase();
var conflicts = [];
negativesLower.forEach(function(neg) {
if (kwLower.indexOf(neg) !== -1 || neg.indexOf(kwLower) !== -1) {
conflicts.push(neg);
}
});
if (conflicts.length > 0) {
s.warning = 'NEGATIVE_CONFLICT: ' + conflicts.slice(0, 3).join(', ');
s.confidence *= 0.5; // Reduce confidence
}
return s;
});
}
function semanticDedup(suggestions) {
// For semantic dedup, we'll use Claude to identify near-duplicates
// This is expensive, so we only do it if we have many suggestions
if (suggestions.length < 20) {
return suggestions; // Not worth the API call for small lists
}
try {
var existingSample = STATE.existingKeywords.slice(0, 100).map(function(k) { return k.text; });
var suggestionTexts = suggestions.map(function(s) { return s.keyword; });
var prompt = 'You are a keyword deduplication expert. Given these EXISTING keywords:\n' +
existingSample.join(', ') + '\n\n' +
'And these SUGGESTED keywords:\n' +
suggestionTexts.join(', ') + '\n\n' +
'Identify which SUGGESTED keywords are semantically redundant (mean essentially the same thing as an existing keyword). ' +
'Return ONLY a JSON array of the suggested keywords that should be REMOVED because they are redundant. ' +
'Be conservative - only mark as redundant if they truly mean the same thing. ' +
'Return an empty array [] if none are redundant.';
var response = callClaudeAPI(prompt);
if (response && response.content && response.content.length > 0) {
var content = response.content[0].text;
content = content.replace(/```json\n?/g, '').replace(/```\n?/g, '').trim();
var redundant = JSON.parse(content);
if (Array.isArray(redundant)) {
var redundantSet = {};
redundant.forEach(function(r) {
redundantSet[r.toLowerCase()] = true;
});
var filtered = suggestions.filter(function(s) {
return !redundantSet[s.keyword.toLowerCase()];
});
log('INFO', ' Removed ' + (suggestions.length - filtered.length) + ' semantic duplicates');
return filtered;
}
}
} catch (e) {
log('WARN', 'Semantic dedup failed: ' + e.message);
}
return suggestions;
}
/******************************************************************************
* PHASE 5: ENRICHMENT & SCORING
******************************************************************************/
function enrichSuggestions() {
STATE.filteredSuggestions.forEach(function(s) {
// Calculate suggested bid
s.suggestedBid = calculateSuggestedBid(s);
// Recalculate priority based on all factors
s.priority = calculateFinalPriority(s);
// Add source signals
s.sourceSignals = identifySourceSignals(s);
});
// Sort by priority and confidence
STATE.filteredSuggestions.sort(function(a, b) {
var priorityOrder = { 'HIGH': 3, 'MEDIUM': 2, 'LOW': 1 };
var priorityDiff = priorityOrder[b.priority] - priorityOrder[a.priority];
if (priorityDiff !== 0) return priorityDiff;
return b.confidence - a.confidence;
});
log('INFO', ' ✓ Enriched ' + STATE.filteredSuggestions.length + ' suggestions');
}
function calculateSuggestedBid(suggestion) {
// Find similar keywords by intent
var similarKeywords = STATE.signals.topKeywords.filter(function(kw) {
return kw.intent === suggestion.intent;
});
if (similarKeywords.length > 0) {
var avgCPC = similarKeywords.reduce(function(sum, kw) {
return sum + kw.avgCpc;
}, 0) / similarKeywords.length;
// Suggest slightly below average to start conservatively
return Math.max(avgCPC * 0.85, 0.10);
}
// Fallback to account average
return Math.max(STATE.context.avgCPC * 0.8, 0.10);
}
function calculateFinalPriority(suggestion) {
var score = 0;
// High confidence boost
if (suggestion.confidence >= 0.8) score += 3;
else if (suggestion.confidence >= 0.6) score += 2;
else score += 1;
// High-intent boost
if (suggestion.intent === 'TRANSACTIONAL') score += 3;
else if (suggestion.intent === 'COMMERCIAL') score += 2;
else if (suggestion.intent === 'NAVIGATIONAL') score += 1;
// Warning penalty
if (suggestion.warning) score -= 2;
// Estimated CPA comparison
if (suggestion.estimatedCPA && suggestion.estimatedCPA <= STATE.context.targetCPA) {
score += 2;
}
if (score >= 6) return 'HIGH';
if (score >= 4) return 'MEDIUM';
return 'LOW';
}
function identifySourceSignals(suggestion) {
var sources = [];
var kwLower = suggestion.keyword.toLowerCase();
// Check if derived from top keywords
STATE.signals.topKeywords.forEach(function(tk) {
if (kwLower.indexOf(tk.keyword.toLowerCase()) !== -1 ||
tk.keyword.toLowerCase().indexOf(kwLower) !== -1) {
sources.push('TopConverter:' + tk.keyword);
}
});
// Check if matches search term
STATE.signals.searchTerms.forEach(function(st) {
if (kwLower === st.searchTerm.toLowerCase()) {
sources.push('SearchTerm:' + st.conversions + 'conv');
}
});
// Check landing page theme match
STATE.signals.landingPageThemes.forEach(function(t) {
if (kwLower.indexOf(t.theme.toLowerCase()) !== -1) {
sources.push('LPTheme:' + t.theme);
}
});
return sources.slice(0, 3).join(', ') || 'AI-generated';
}
/******************************************************************************
* PHASE 6: OUTPUT GENERATION
******************************************************************************/
function generateOutput() {
// Initialize spreadsheet
initializeSpreadsheet();
// Write all sheets
writeSummarySheet();
writeHighPrioritySheet();
writeMediumPrioritySheet();
writeAllSuggestionsSheet();
writeIntelligenceSourcesSheet();
writeExistingKeywordsSheet();
log('INFO', ' ✓ Output generated: ' + STATE.spreadsheet.getUrl());
}
function initializeSpreadsheet() {
if (!CONFIG.SPREADSHEET_URL || CONFIG.SPREADSHEET_URL === 'CREATE_NEW') {
STATE.spreadsheet = SpreadsheetApp.create(
'PPC.io AI Keyword Expansion - ' +
AdsApp.currentAccount().getName() + ' - ' +
formatDate(new Date())
);
log('INFO', ' Created new spreadsheet');
} else {
STATE.spreadsheet = SpreadsheetApp.openByUrl(CONFIG.SPREADSHEET_URL);
log('INFO', ' Using existing spreadsheet');
}
// Remove default sheet if it exists
var sheets = STATE.spreadsheet.getSheets();
if (sheets.length === 1 && sheets[0].getName() === 'Sheet1') {
sheets[0].setName('1. Summary');
}
}
function writeSummarySheet() {
var sheet = getOrCreateSheet('1. Summary');
sheet.clear();
var highCount = STATE.filteredSuggestions.filter(function(s) { return s.priority === 'HIGH'; }).length;
var mediumCount = STATE.filteredSuggestions.filter(function(s) { return s.priority === 'MEDIUM'; }).length;
var lowCount = STATE.filteredSuggestions.filter(function(s) { return s.priority === 'LOW'; }).length;
var data = [
['AI KEYWORD EXPANSION ENGINE', '', '', ''],
['Generated by PPC.io Script Engine', '', '', ''],
['https://ppc.io', '', '', ''],
['', '', '', ''],
['Account:', STATE.context.accountName, '', ''],
['Date Range:', CONFIG.DATE_RANGE, '', ''],
['Generated:', new Date().toISOString(), '', ''],
['', '', '', ''],
['═══════════════════════════════════════════════════════════════', '', '', ''],
['SUGGESTIONS SUMMARY', '', '', ''],
['═══════════════════════════════════════════════════════════════', '', '', ''],
['Total Suggestions:', STATE.filteredSuggestions.length, '', ''],
['High Priority:', highCount, '(add immediately)', ''],
['Medium Priority:', mediumCount, '(review and add)', ''],
['Low Priority:', lowCount, '(test carefully)', ''],
['', '', '', ''],
['═══════════════════════════════════════════════════════════════', '', '', ''],
['INTELLIGENCE SIGNALS USED', '', '', ''],
['═══════════════════════════════════════════════════════════════', '', '', ''],
['Top Converting Keywords:', STATE.signals.topKeywords.length, '', ''],
['High-Value Search Terms:', STATE.signals.searchTerms.length, '', ''],
['High-CTR Engagement Signals:', STATE.signals.highCTR.length, '', ''],
['Competitors Analyzed:', STATE.signals.competitors.length, '', ''],
['Landing Page Themes:', STATE.signals.landingPageThemes.length, '', ''],
['Ad Copy Themes:', STATE.signals.adCopyThemes.length, '', ''],
['', '', '', ''],
['═══════════════════════════════════════════════════════════════', '', '', ''],
['ACCOUNT BENCHMARKS', '', '', ''],
['═══════════════════════════════════════════════════════════════', '', '', ''],
['Avg CPA:', '$' + STATE.context.avgCPA.toFixed(2), '', ''],
['Avg CTR:', (STATE.context.avgCTR * 100).toFixed(2) + '%', '', ''],
['Avg Conv Rate:', (STATE.context.avgConvRate * 100).toFixed(2) + '%', '', ''],
['Target CPA:', '$' + STATE.context.targetCPA.toFixed(2), '', ''],
['Business Type:', STATE.context.businessType || 'Not detected', '', ''],
['Primary Domain:', STATE.context.primaryDomain || 'Not detected', '', ''],
['', '', '', ''],
['═══════════════════════════════════════════════════════════════', '', '', ''],
['AUTO-DETECTED TERMS', '', '', ''],
['═══════════════════════════════════════════════════════════════', '', '', ''],
['Brand Terms:', STATE.detectedBrandTerms.join(', ') || 'None detected', '', ''],
['Competitor Terms:', STATE.detectedCompetitorTerms.join(', ') || 'None detected', '', ''],
['', '', '', '']
];
// Add intent gaps if any
if (STATE.context.intentGaps.length > 0) {
data.push(['═══════════════════════════════════════════════════════════════', '', '', '']);
data.push(['INTENT GAPS IDENTIFIED', '', '', '']);
data.push(['═══════════════════════════════════════════════════════════════', '', '', '']);
STATE.context.intentGaps.forEach(function(gap) {
data.push([gap, '', '', '']);
});
data.push(['', '', '', '']);
}
// Add AI prompts
data.push(['═══════════════════════════════════════════════════════════════', '', '', '']);
data.push(['AI ANALYSIS PROMPTS', '', '', '']);
data.push(['═══════════════════════════════════════════════════════════════', '', '', '']);
data.push(['Copy these prompts to use with Claude for deeper analysis:', '', '', '']);
data.push(['', '', '', '']);
data.push(['1. "Which HIGH priority suggestions should I add first and why?"', '', '', '']);
data.push(['2. "Are there any suggestions that might cannibalize existing keywords?"', '', '', '']);
data.push(['3. "What ad copy should I write for the top 10 suggestions?"', '', '', '']);
data.push(['4. "Group these suggestions into logical ad group themes."', '', '', '']);
data.push(['5. "Estimate the monthly search volume and competition for top suggestions."', '', '', '']);
// Normalize column count
var maxCols = 4;
data = data.map(function(row) {
while (row.length < maxCols) row.push('');
return row;
});
sheet.getRange(1, 1, data.length, maxCols).setValues(data);
// Formatting
sheet.getRange(1, 1).setFontWeight('bold').setFontSize(16);
sheet.getRange(10, 1).setFontWeight('bold').setFontSize(12);
sheet.getRange(18, 1).setFontWeight('bold').setFontSize(12);
sheet.getRange(28, 1).setFontWeight('bold').setFontSize(12);
sheet.setColumnWidth(1, 350);
sheet.setColumnWidth(2, 150);
}
function writeHighPrioritySheet() {
var sheet = getOrCreateSheet('2. High Priority');
sheet.clear();
var highPriority = STATE.filteredSuggestions.filter(function(s) {
return s.priority === 'HIGH';
});
writeSuggestionsToSheet(sheet, highPriority, 'HIGH PRIORITY SUGGESTIONS - Add Immediately');
}
function writeMediumPrioritySheet() {
var sheet = getOrCreateSheet('3. Medium Priority');
sheet.clear();
var mediumPriority = STATE.filteredSuggestions.filter(function(s) {
return s.priority === 'MEDIUM';
});
writeSuggestionsToSheet(sheet, mediumPriority, 'MEDIUM PRIORITY SUGGESTIONS - Review and Add');
}
function writeAllSuggestionsSheet() {
var sheet = getOrCreateSheet('4. All Suggestions');
sheet.clear();
writeSuggestionsToSheet(sheet, STATE.filteredSuggestions, 'ALL SUGGESTIONS - Complete List');
}
function writeSuggestionsToSheet(sheet, suggestions, title) {
if (suggestions.length === 0) {
sheet.getRange(1, 1).setValue(title);
sheet.getRange(2, 1).setValue('No suggestions in this category');
return;
}
var headers = [
'Keyword', 'Match Type', 'Intent', 'Confidence', 'Priority',
'Suggested Bid', 'Est. CPA', 'Campaign', 'Ad Group',
'Reasoning', 'Source Signals', 'Warning'
];
var rows = suggestions.map(function(s) {
return [
s.keyword,
s.matchType,
s.intent,
s.confidence,
s.priority,
s.suggestedBid,
s.estimatedCPA,
s.suggestedCampaign,
s.suggestedAdGroup,
s.reasoning,
s.sourceSignals,
s.warning || ''
];
});
// Write title
sheet.getRange(1, 1).setValue(title).setFontWeight('bold').setFontSize(14);
sheet.getRange(2, 1).setValue('Count: ' + suggestions.length);
// Write headers
sheet.getRange(4, 1, 1, headers.length).setValues([headers]).setFontWeight('bold');
sheet.setFrozenRows(4);
// Write data in batches
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(5 + i, 1, batch.length, headers.length).setValues(batch);
}
// Format columns
sheet.getRange(5, 4, rows.length, 1).setNumberFormat('0.00'); // Confidence
sheet.getRange(5, 6, rows.length, 1).setNumberFormat('$#,##0.00'); // Suggested Bid
sheet.getRange(5, 7, rows.length, 1).setNumberFormat('$#,##0.00'); // Est. CPA
// Color code priority
var priorityCol = 5;
for (var j = 0; j < rows.length; j++) {
var priority = rows[j][4];
var color = priority === 'HIGH' ? '#d4edda' : (priority === 'MEDIUM' ? '#fff3cd' : '#f8f9fa');
sheet.getRange(5 + j, priorityCol).setBackground(color);
}
// Color code warnings
for (var k = 0; k < rows.length; k++) {
if (rows[k][11]) { // Warning column
sheet.getRange(5 + k, 12).setBackground('#f8d7da');
}
}
// Auto-resize key columns
sheet.setColumnWidth(1, 250); // Keyword
sheet.setColumnWidth(10, 300); // Reasoning
sheet.setColumnWidth(11, 200); // Source Signals
}
function writeIntelligenceSourcesSheet() {
var sheet = getOrCreateSheet('5. Intelligence Sources');
sheet.clear();
var data = [
['INTELLIGENCE SOURCES', ''],
['Data that fed the AI keyword suggestions', ''],
['', ''],
['═══════════════════════════════════════════════════════════════', ''],
['TOP CONVERTING KEYWORDS (' + STATE.signals.topKeywords.length + ')', ''],
['═══════════════════════════════════════════════════════════════', ''],
['Keyword', 'Conversions', 'CPA', 'Intent', 'Campaign']
];
STATE.signals.topKeywords.slice(0, 25).forEach(function(kw) {
data.push([
kw.keyword,
kw.conversions,
kw.cpa ? '$' + kw.cpa.toFixed(2) : 'N/A',
kw.intent,
kw.campaign
]);
});
data.push(['', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
data.push(['HIGH-VALUE SEARCH TERMS (' + STATE.signals.searchTerms.length + ')', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
data.push(['Search Term', 'Conversions', 'CPA', 'Intent', 'Matched Keyword']);
STATE.signals.searchTerms.slice(0, 25).forEach(function(st) {
data.push([
st.searchTerm,
st.conversions,
st.cpa ? '$' + st.cpa.toFixed(2) : 'N/A',
st.intent,
st.matchedKeyword
]);
});
data.push(['', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
data.push(['LANDING PAGE THEMES', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
var lpThemes = STATE.signals.landingPageThemes.map(function(t) { return t.theme; }).join(', ');
data.push([lpThemes || 'None extracted', '']);
data.push(['', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
data.push(['AD COPY THEMES', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
var adThemes = STATE.signals.adCopyThemes.map(function(t) { return t.theme; }).join(', ');
data.push([adThemes || 'None extracted', '']);
data.push(['', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
data.push(['INTENT DISTRIBUTION', '']);
data.push(['═══════════════════════════════════════════════════════════════', '']);
data.push(['Intent', 'Count', 'Percentage', 'Conversions']);
for (var intent in STATE.signals.intentDistribution) {
var d = STATE.signals.intentDistribution[intent];
data.push([
intent,
d.count,
d.percentage ? d.percentage.toFixed(1) + '%' : '0%',
d.conversions
]);
}
// Normalize columns
var maxCols = 5;
data = data.map(function(row) {
while (row.length < maxCols) row.push('');
return row;
});
sheet.getRange(1, 1, data.length, maxCols).setValues(data);
sheet.getRange(1, 1).setFontWeight('bold').setFontSize(14);
sheet.setColumnWidth(1, 300);
}
function writeExistingKeywordsSheet() {
var sheet = getOrCreateSheet('6. Existing Keywords');
sheet.clear();
var data = [
['EXISTING KEYWORDS REFERENCE'],
['Total: ' + STATE.existingKeywords.length + ' keywords (used for deduplication)'],
[''],
['Keyword', 'Match Type']
];
// Only write a sample to avoid huge sheets
var sample = STATE.existingKeywords.slice(0, 500);
sample.forEach(function(kw) {
data.push([kw.text, kw.matchType]);
});
if (STATE.existingKeywords.length > 500) {
data.push(['... and ' + (STATE.existingKeywords.length - 500) + ' more keywords', '']);
}
sheet.getRange(1, 1, data.length, 2).setValues(data);
sheet.getRange(1, 1).setFontWeight('bold').setFontSize(14);
sheet.getRange(4, 1, 1, 2).setFontWeight('bold');
sheet.setFrozenRows(4);
sheet.setColumnWidth(1, 300);
}
function getOrCreateSheet(name) {
var sheet = STATE.spreadsheet.getSheetByName(name);
if (!sheet) {
sheet = STATE.spreadsheet.insertSheet(name);
}
return sheet;
}
/******************************************************************************
* NOTIFICATIONS
******************************************************************************/
function sendNotifications() {
var duration = ((new Date() - STATE.startTime) / 1000).toFixed(1);
var highCount = STATE.filteredSuggestions.filter(function(s) { return s.priority === 'HIGH'; }).length;
var mediumCount = STATE.filteredSuggestions.filter(function(s) { return s.priority === 'MEDIUM'; }).length;
var message = [
'AI Keyword Expansion Complete',
'',
'Account: ' + STATE.context.accountName,
'Duration: ' + duration + 's',
'',
'SUGGESTIONS:',
'- Total: ' + STATE.filteredSuggestions.length,
'- High Priority: ' + highCount,
'- Medium Priority: ' + mediumCount,
'',
'INTELLIGENCE USED:',
'- ' + STATE.signals.topKeywords.length + ' top converting keywords',
'- ' + STATE.signals.searchTerms.length + ' high-value search terms',
'- ' + STATE.signals.highCTR.length + ' high-CTR signals',
'',
'Spreadsheet: ' + STATE.spreadsheet.getUrl(),
'',
'--',
'Generated by PPC.io Script Engine'
].join('\n');
// Email notification
if (CONFIG.EMAIL_RECIPIENTS && CONFIG.EMAIL_RECIPIENTS.length > 0) {
try {
MailApp.sendEmail({
to: CONFIG.EMAIL_RECIPIENTS.join(','),
subject: '[PPC.io] AI Keyword Expansion - ' + STATE.filteredSuggestions.length + ' suggestions',
body: message
});
log('INFO', ' Email notification sent');
} catch (e) {
log('ERROR', 'Failed to send email: ' + e.message);
}
}
// Slack notification
if (CONFIG.SLACK_WEBHOOK_URL) {
try {
UrlFetchApp.fetch(CONFIG.SLACK_WEBHOOK_URL, {
method: 'post',
contentType: 'application/json',
payload: JSON.stringify({
text: ':bulb: *PPC.io AI Keyword Expansion*\n```' + message + '```'
})
});
log('INFO', ' Slack notification sent');
} catch (e) {
log('ERROR', 'Failed to send Slack: ' + e.message);
}
}
}
/******************************************************************************
* UTILITY FUNCTIONS
******************************************************************************/
function normalizeKeyword(keyword) {
return keyword.toLowerCase()
.replace(/[^a-z0-9\s]/g, '')
.replace(/\s+/g, ' ')
.trim();
}
function classifyIntent(text) {
var textLower = text.toLowerCase();
// Use detected brand terms (auto-detected or user-provided)
var brandTerms = STATE.detectedBrandTerms.length > 0 ? STATE.detectedBrandTerms : CONFIG.BRAND_TERMS;
for (var i = 0; i < brandTerms.length; i++) {
if (textLower.indexOf(brandTerms[i].toLowerCase()) !== -1) {
return 'BRANDED';
}
}
// Use detected competitor terms (auto-detected or user-provided)
var competitorTerms = STATE.detectedCompetitorTerms.length > 0 ? STATE.detectedCompetitorTerms : CONFIG.COMPETITOR_TERMS;
for (var j = 0; j < competitorTerms.length; j++) {
if (textLower.indexOf(competitorTerms[j].toLowerCase()) !== -1) {
return 'COMPETITOR';
}
}
// Check intent patterns
for (var intent in INTENT_PATTERNS) {
var patterns = INTENT_PATTERNS[intent];
for (var k = 0; k < patterns.length; k++) {
if (textLower.indexOf(patterns[k]) !== -1) {
return intent;
}
}
}
return 'OTHER';
}
function matchesCampaignFilters(campaignName) {
if (CONFIG.CAMPAIGN_NAME_CONTAINS &&
campaignName.toLowerCase().indexOf(CONFIG.CAMPAIGN_NAME_CONTAINS.toLowerCase()) === -1) {
return false;
}
if (CONFIG.CAMPAIGN_NAME_EXCLUDES) {
var excludes = CONFIG.CAMPAIGN_NAME_EXCLUDES.split(',');
for (var i = 0; i < excludes.length; i++) {
if (campaignName.toLowerCase().indexOf(excludes[i].trim().toLowerCase()) !== -1) {
return false;
}
}
}
return true;
}
function checkTimeLimit() {
var elapsed = (new Date() - STATE.startTime) / 1000 / 60;
if (elapsed > CONFIG.TIME_LIMIT_MINUTES) {
throw new Error('TIME_LIMIT: Script approaching time limit at ' + elapsed.toFixed(1) + ' minutes. Partial results may be available.');
}
}
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 formatDate(date) {
return Utilities.formatDate(date, AdsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');
}
function logFinalSummary() {
var duration = ((new Date() - STATE.startTime) / 1000).toFixed(1);
log('INFO', '');
log('INFO', '════════════════════════════════════════════════════════════════');
log('INFO', 'AI KEYWORD EXPANSION ENGINE - COMPLETE');
log('INFO', '════════════════════════════════════════════════════════════════');
log('INFO', 'Duration: ' + duration + ' seconds');
log('INFO', 'Total Suggestions: ' + STATE.filteredSuggestions.length);
log('INFO', 'Spreadsheet: ' + STATE.spreadsheet.getUrl());
log('INFO', '════════════════════════════════════════════════════════════════');
}
function handleFatalError(error) {
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] AI Keyword Expansion Failed',
body: 'Error: ' + error.message + '\n\nStack:\n' + error.stack
});
} catch (e) {
log('ERROR', 'Could not send error email: ' + e.message);
}
}
}