Automatically identify underperforming keywords and search terms in your Google Ads account to exclude!
What The Google Ads script does
👉Finds poor-performing keywords and queries
👉Analyzes 30, 90, 180, and 365-day performance data
👉Exports actionable reports to Google Sheets, ready for you to take action on.
Installing The Google Ads Script
- Put the script in Google Ads (Tools > Bulk Actions > Scripts)
- First run: Leave the ‘Sheet_URL’ empty. It’ll create a new Google Sheet, you can find that in the ‘logs’ of the script when you preview the script.
Your first run should be a Preview, not a ‘Run’ in Google Ads. - After the first run: Add that URL as a ‘Sheet_URL’ in the script.
- Optional: Add the performance treshold of the keywords and queries you want to export.
- Select your treshold date range (or keep 90).
- Add your performance tresholds for every date range.
- Select an optional campaign filter.
You can include and exclude based on campaign names. - Select your performance target.
You can choose CPA or ROAS and set a goal.
Example:
Choosing a ROAS of 2, exports all search terms with ROAS >2
Choosing a CPA of 10, exports all search terms with CPA <10 - Hit ‘Preview’ and monitor the ‘Logs’
- Use the sheet to decide on exclusions
Script Introduction
Stop Wasting Budget on Underperforming Keywords
Still manually analyzing search terms one by one, wondering which ones deserve to become negative keywords?
This script does the heavy lifting for you.
Automatically identifies underperforming keywords and search terms across multiple time periods (30, 90, 180, and 365 days), filtered by your custom performance thresholds. Works for both Search and Shopping campaigns.
No more guesswork or manual analysis.
Clear negative keyword recommendations in a Google Sheet, ready to review and implement!
Script Code
/**
* NEGATIVE ELEMENT FINDER
* Written by Matinique Roelse from Adcrease
* Website: https://adcrease.nl
* Linkedin: https://www.linkedin.com/in/matiniqueroelse/
*
* Senior-only Google Ads agency
* ===========================
*
* This script identifies underperforming keywords and search terms in your Google Ads account
* that may be candidates for negative keyword addition.
*
* What it analyzes:
* - Keywords from Search campaigns
* - Search terms from Search and Shopping campaigns
*
* The script evaluates performance across multiple time periods (30, 90, 180, and 365 days)
* and filters based on your specified thresholds for clicks, cost, conversions, and performance targets (CPA/ROAS).
*
* Results are exported to a Google Sheet with both filtered summaries and raw data tabs.
*/
const SHEET_URL = ''; // Paste your Google Sheet URL here, or leave blank to create a new one
// CONFIGURATION - Adjust these values as needed
const THRESHOLD_DAYS = 90; // Number of days to use for threshold evaluation
const THRESHOLDS = {
minClicks: 1, // Minimum clicks in threshold period
minCost: 1, // Minimum cost in threshold period (in account currency)
minConversions: 0 // Minimum conversions in threshold period
};
// OPTIONAL PERFORMANCE TARGETS - Set one or leave both null to disable
const PERFORMANCE_TARGET = {
metric: 'ROAS', // Choose: 'CPA' or 'ROAS'
threshold: 0.75, // Threshold value
enabled: true // Set to false to disable performance filtering
};
// CAMPAIGN FILTERS - Optional filters for campaign names
const CAMPAIGN_FILTERS = {
enabled: true, // Set to false to include paused campaigns
includeString: '', // Only include campaigns containing this string (case-insensitive)
excludeString: '' // Exclude campaigns containing this string (case-insensitive)
};
function getDateRangeString(numDays) {
const endDate = new Date();
const startDate = new Date();
startDate.setDate(endDate.getDate() - numDays + 1); // Inclusive
const format = date => Utilities.formatDate(date, AdsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
return `BETWEEN "${format(startDate)}" AND "${format(endDate)}"`;
}
function shouldIncludeCampaign(campaignName) {
// Check if campaign should be included based on filters
if (!campaignName) return false;
// Check include string filter
if (CAMPAIGN_FILTERS.includeString && !campaignName.toLowerCase().includes(CAMPAIGN_FILTERS.includeString.toLowerCase())) {
return false;
}
// Check exclude string filter
if (CAMPAIGN_FILTERS.excludeString && campaignName.toLowerCase().includes(CAMPAIGN_FILTERS.excludeString.toLowerCase())) {
return false;
}
return true;
}
function getColumnIndex(headers, columnName) {
return headers.indexOf(columnName);
}
function sortSheetByCost(sheet, headers, output, thresholdDays) {
const costColumnName = `Cost (${thresholdDays}d)`;
const costColumnIndex = getColumnIndex(headers, costColumnName);
if (costColumnIndex !== -1 && output.length > 1) {
// Sort by cost in threshold period (descending - highest cost first)
sheet.getRange(2, 1, output.length - 1, headers.length).sort({column: costColumnIndex + 1, ascending: false});
Logger.log(` Sorted by ${costColumnName} (column ${costColumnIndex + 1}) - descending`);
} else if (costColumnIndex === -1) {
Logger.log(` Warning: Could not find column "${costColumnName}" for sorting`);
}
}
function fetchKeywordData(dateCondition) {
const campaignStatusFilter = CAMPAIGN_FILTERS.enabled ? 'AND campaign.status = "ENABLED"' : '';
const query = `
SELECT
campaign.name,
ad_group_criterion.keyword.text,
ad_group_criterion.keyword.match_type,
ad_group_criterion.status,
metrics.cost_micros,
metrics.conversions,
metrics.conversions_value,
metrics.clicks
FROM keyword_view
WHERE segments.date ${dateCondition}
AND ad_group_criterion.keyword.text IS NOT NULL
AND campaign.advertising_channel_type = "SEARCH"
AND ad_group_criterion.status = "ENABLED"
AND ad_group.status = "ENABLED"
${campaignStatusFilter}
`;
const rows = AdsApp.search(query);
const data = {};
while (rows.hasNext()) {
const row = rows.next();
const campaignName = row['campaign'] && row['campaign']['name'] ? row['campaign']['name'] : "";
// Apply campaign name filters
if (!shouldIncludeCampaign(campaignName)) {
continue;
}
const keyword = row['adGroupCriterion'] && row['adGroupCriterion']['keyword'] ? row['adGroupCriterion']['keyword']['text'] : "";
const matchType = row['adGroupCriterion'] && row['adGroupCriterion']['keyword'] && row['adGroupCriterion']['keyword']['matchType'] ? row['adGroupCriterion']['keyword']['matchType'] : "";
const key = keyword + '||' + matchType + '||' + campaignName;
const cost = row['metrics'] && row['metrics']['costMicros'] ? Number(row['metrics']['costMicros']) / 1e6 : 0;
const conversions = row['metrics'] && row['metrics']['conversions'] ? Number(row['metrics']['conversions']) : 0;
const convValue = row['metrics'] && row['metrics']['conversionsValue'] ? Number(row['metrics']['conversionsValue']) : 0;
const clicks = row['metrics'] && row['metrics']['clicks'] ? Number(row['metrics']['clicks']) : 0;
if (!data[key]) {
data[key] = { campaignName, keyword, matchType, cost: 0, conversions: 0, convValue: 0, clicks: 0 };
}
data[key].cost += cost;
data[key].conversions += conversions;
data[key].convValue += convValue;
data[key].clicks += clicks;
}
return data;
}
function fetchSearchTermData(dateCondition) {
const campaignStatusFilter = CAMPAIGN_FILTERS.enabled ? 'AND campaign.status = "ENABLED"' : '';
// Get search terms from search_term_view (Search and Shopping)
const query = `
SELECT
campaign.name,
campaign.advertising_channel_type,
search_term_view.search_term,
metrics.cost_micros,
metrics.conversions,
metrics.conversions_value,
metrics.clicks
FROM search_term_view
WHERE segments.date ${dateCondition}
AND search_term_view.search_term IS NOT NULL
AND campaign.advertising_channel_type IN ("SEARCH", "SHOPPING")
${campaignStatusFilter}
`;
const data = {};
const campaignTypeCounts = {};
let totalRows = 0;
try {
Logger.log(" Fetching search terms...");
const rows = AdsApp.search(query);
while (rows.hasNext()) {
const row = rows.next();
totalRows++;
const campaignName = row['campaign'] && row['campaign']['name'] ? row['campaign']['name'] : "";
const campaignType = row['campaign'] && row['campaign']['advertisingChannelType'] ? row['campaign']['advertisingChannelType'] : "UNKNOWN";
// Count campaign types
campaignTypeCounts[campaignType] = (campaignTypeCounts[campaignType] || 0) + 1;
// Apply campaign name filters
if (!shouldIncludeCampaign(campaignName)) {
continue;
}
const searchTerm = row['searchTermView'] && row['searchTermView']['searchTerm'] ? row['searchTermView']['searchTerm'] : "";
const key = searchTerm + '||' + campaignName;
const cost = row['metrics'] && row['metrics']['costMicros'] ? Number(row['metrics']['costMicros']) / 1e6 : 0;
const conversions = row['metrics'] && row['metrics']['conversions'] ? Number(row['metrics']['conversions']) : 0;
const convValue = row['metrics'] && row['metrics']['conversionsValue'] ? Number(row['metrics']['conversionsValue']) : 0;
const clicks = row['metrics'] && row['metrics']['clicks'] ? Number(row['metrics']['clicks']) : 0;
if (!data[key]) {
data[key] = { campaignName, searchTerm, cost: 0, conversions: 0, convValue: 0, clicks: 0 };
}
data[key].cost += cost;
data[key].conversions += conversions;
data[key].convValue += convValue;
data[key].clicks += clicks;
}
} catch (e) {
Logger.log(` Error fetching search terms: ${e}`);
}
// Log campaign type distribution
Logger.log(`Search term data breakdown:`);
Logger.log(` Total rows processed: ${totalRows}`);
for (const [type, count] of Object.entries(campaignTypeCounts)) {
Logger.log(` ${type}: ${count} search terms`);
}
return data;
}
function filterKeywordsByThresholds(data) {
const filteredData = {};
const thresholdPeriod = `${THRESHOLD_DAYS}d`;
for (const key in data) {
const keywordData = data[key];
// Check if keyword meets threshold period criteria
if (keywordData[`clicks_${thresholdPeriod}`] >= THRESHOLDS.minClicks &&
keywordData[`cost_${thresholdPeriod}`] >= THRESHOLDS.minCost &&
keywordData[`conversions_${thresholdPeriod}`] >= THRESHOLDS.minConversions) {
filteredData[key] = keywordData;
}
}
return filteredData;
}
function filterSearchTermsByThresholds(data) {
const filteredData = {};
const thresholdPeriod = `${THRESHOLD_DAYS}d`;
for (const key in data) {
const searchTermData = data[key];
// Check if search term meets threshold period criteria
if (searchTermData[`clicks_${thresholdPeriod}`] >= THRESHOLDS.minClicks &&
searchTermData[`cost_${thresholdPeriod}`] >= THRESHOLDS.minCost &&
searchTermData[`conversions_${thresholdPeriod}`] >= THRESHOLDS.minConversions) {
filteredData[key] = searchTermData;
}
}
return filteredData;
}
function filterKeywordsByPerformanceTarget(data) {
if (!PERFORMANCE_TARGET.enabled) {
return data;
}
const filteredData = {};
const periods = ['30d', `${THRESHOLD_DAYS}d`, '180d', '365d'];
for (const key in data) {
const keywordData = data[key];
let hasBadPerformanceInAllPeriods = true;
// Check if keyword has bad performance in ALL periods
for (const period of periods) {
const metricValue = keywordData[`${PERFORMANCE_TARGET.metric.toLowerCase()}_${period}`] || 0;
const conversions = keywordData[`conversions_${period}`] || 0;
if (PERFORMANCE_TARGET.metric === 'CPA') {
// For CPA: exclude if BELOW threshold (good performance) AND has conversions
if (metricValue <= PERFORMANCE_TARGET.threshold && conversions > 0) {
hasBadPerformanceInAllPeriods = false;
break;
}
} else if (PERFORMANCE_TARGET.metric === 'ROAS') {
// For ROAS: exclude if ABOVE threshold (good performance) AND has conversions
if (metricValue >= PERFORMANCE_TARGET.threshold && conversions > 0) {
hasBadPerformanceInAllPeriods = false;
break;
}
}
}
if (hasBadPerformanceInAllPeriods) {
filteredData[key] = keywordData;
}
}
return filteredData;
}
function filterSearchTermsByPerformanceTarget(data) {
if (!PERFORMANCE_TARGET.enabled) {
return data;
}
const filteredData = {};
const periods = ['30d', `${THRESHOLD_DAYS}d`, '180d', '365d'];
for (const key in data) {
const searchTermData = data[key];
let hasBadPerformanceInAllPeriods = true;
// Check if search term has bad performance in ALL periods
for (const period of periods) {
const metricValue = searchTermData[`${PERFORMANCE_TARGET.metric.toLowerCase()}_${period}`] || 0;
const conversions = searchTermData[`conversions_${period}`] || 0;
if (PERFORMANCE_TARGET.metric === 'CPA') {
// For CPA: exclude if BELOW threshold (good performance) AND has conversions
if (metricValue <= PERFORMANCE_TARGET.threshold && conversions > 0) {
hasBadPerformanceInAllPeriods = false;
break;
}
} else if (PERFORMANCE_TARGET.metric === 'ROAS') {
// For ROAS: exclude if ABOVE threshold (good performance) AND has conversions
if (metricValue >= PERFORMANCE_TARGET.threshold && conversions > 0) {
hasBadPerformanceInAllPeriods = false;
break;
}
}
}
if (hasBadPerformanceInAllPeriods) {
filteredData[key] = searchTermData;
}
}
return filteredData;
}
function main() {
Logger.log("=== Starting Negative Element Finder Script (Keywords: Search only, Search Terms: Search/Shopping) ===");
let ss;
if (!SHEET_URL) {
ss = SpreadsheetApp.create("Keyword Performance Report");
Logger.log("No SHEET_URL found, so this sheet was created: " + ss.getUrl());
} else {
ss = SpreadsheetApp.openByUrl(SHEET_URL);
Logger.log("Using existing spreadsheet: " + SHEET_URL);
}
// Fetch data for each period
const periods = [
{ label: '30d', condition: 'DURING LAST_30_DAYS' },
{ label: `${THRESHOLD_DAYS}d`, condition: getDateRangeString(THRESHOLD_DAYS) },
{ label: '180d', condition: getDateRangeString(180) },
{ label: '365d', condition: getDateRangeString(365) }
];
Logger.log(`Analyzing data for periods: ${periods.map(p => p.label).join(', ')}`);
// Log campaign filters
Logger.log(`Campaign filters: Enabled campaigns only: ${CAMPAIGN_FILTERS.enabled}`);
if (CAMPAIGN_FILTERS.includeString) {
Logger.log(`Campaign include filter: "${CAMPAIGN_FILTERS.includeString}"`);
}
if (CAMPAIGN_FILTERS.excludeString) {
Logger.log(`Campaign exclude filter: "${CAMPAIGN_FILTERS.excludeString}"`);
}
// Fetch keyword data
Logger.log("Fetching keyword data...");
const allKeywordData = {};
const allKeywordKeys = new Set();
periods.forEach(period => {
Logger.log(` Fetching keywords for ${period.label} period...`);
const periodData = fetchKeywordData(period.condition);
for (const key in periodData) {
if (!allKeywordData[key]) allKeywordData[key] = {
campaignName: periodData[key].campaignName,
keyword: periodData[key].keyword,
matchType: periodData[key].matchType
};
allKeywordData[key][`cost_${period.label}`] = periodData[key].cost;
allKeywordData[key][`conversions_${period.label}`] = periodData[key].conversions;
allKeywordData[key][`convValue_${period.label}`] = periodData[key].convValue;
allKeywordData[key][`clicks_${period.label}`] = periodData[key].clicks;
allKeywordData[key][`roas_${period.label}`] = periodData[key].cost > 0 ? periodData[key].convValue / periodData[key].cost : 0;
allKeywordData[key][`cpa_${period.label}`] = periodData[key].conversions > 0 ? periodData[key].cost / periodData[key].conversions : 0;
allKeywordKeys.add(key);
}
});
Logger.log(`Found ${allKeywordKeys.size} unique keywords`);
// Fetch search term data
Logger.log("Fetching search term data...");
const allSearchTermData = {};
const allSearchTermKeys = new Set();
periods.forEach(period => {
Logger.log(` Fetching search terms for ${period.label} period...`);
const periodData = fetchSearchTermData(period.condition);
for (const key in periodData) {
if (!allSearchTermData[key]) allSearchTermData[key] = {
campaignName: periodData[key].campaignName,
searchTerm: periodData[key].searchTerm
};
allSearchTermData[key][`cost_${period.label}`] = periodData[key].cost;
allSearchTermData[key][`conversions_${period.label}`] = periodData[key].conversions;
allSearchTermData[key][`convValue_${period.label}`] = periodData[key].convValue;
allSearchTermData[key][`clicks_${period.label}`] = periodData[key].clicks;
allSearchTermData[key][`roas_${period.label}`] = periodData[key].cost > 0 ? periodData[key].convValue / periodData[key].cost : 0;
allSearchTermData[key][`cpa_${period.label}`] = periodData[key].conversions > 0 ? periodData[key].cost / periodData[key].conversions : 0;
allSearchTermKeys.add(key);
}
});
Logger.log(`Found ${allSearchTermKeys.size} unique search terms`);
// Filter keywords based on thresholds and performance targets
Logger.log("Filtering keywords...");
let filteredKeywordData = filterKeywordsByThresholds(allKeywordData);
Logger.log(` After threshold filtering: ${Object.keys(filteredKeywordData).length} keywords`);
const thresholdFilteredKeywordData = { ...filteredKeywordData }; // Copy for raw data tab
filteredKeywordData = filterKeywordsByPerformanceTarget(filteredKeywordData);
const filteredKeywordKeys = Object.keys(filteredKeywordData);
const thresholdFilteredKeywordKeys = Object.keys(thresholdFilteredKeywordData);
Logger.log(` After performance filtering: ${filteredKeywordKeys.length} keywords`);
// Filter search terms based on thresholds and performance targets
Logger.log("Filtering search terms...");
let filteredSearchTermData = filterSearchTermsByThresholds(allSearchTermData);
Logger.log(` After threshold filtering: ${Object.keys(filteredSearchTermData).length} search terms`);
const thresholdFilteredSearchTermData = { ...filteredSearchTermData }; // Copy for raw data tab
filteredSearchTermData = filterSearchTermsByPerformanceTarget(filteredSearchTermData);
const filteredSearchTermKeys = Object.keys(filteredSearchTermData);
const thresholdFilteredSearchTermKeys = Object.keys(thresholdFilteredSearchTermData);
Logger.log(` After performance filtering: ${filteredSearchTermKeys.length} search terms`);
// Prepare keyword output
Logger.log("Preparing keyword output...");
const keywordHeaders = [
'Campaign', 'Keyword', 'Match Type',
'Cost (30d)', 'Conversions (30d)', 'Conv. Value (30d)', 'ROAS (30d)', 'CPA (30d)',
`Cost (${THRESHOLD_DAYS}d)`, `Conversions (${THRESHOLD_DAYS}d)`, `Conv. Value (${THRESHOLD_DAYS}d)`, `ROAS (${THRESHOLD_DAYS}d)`, `CPA (${THRESHOLD_DAYS}d)`,
'Cost (180d)', 'Conversions (180d)', 'Conv. Value (180d)', 'ROAS (180d)', 'CPA (180d)',
'Cost (365d)', 'Conversions (365d)', 'Conv. Value (365d)', 'ROAS (365d)', 'CPA (365d)'
];
const keywordOutput = [keywordHeaders];
filteredKeywordKeys.forEach(key => {
const d = filteredKeywordData[key];
const thresholdPeriod = `${THRESHOLD_DAYS}d`;
keywordOutput.push([
d.campaignName,
d.keyword,
d.matchType,
d.cost_30d || 0, d.conversions_30d || 0, d.convValue_30d || 0, d.roas_30d || 0, d.cpa_30d || 0,
d[`cost_${thresholdPeriod}`] || 0, d[`conversions_${thresholdPeriod}`] || 0, d[`convValue_${thresholdPeriod}`] || 0, d[`roas_${thresholdPeriod}`] || 0, d[`cpa_${thresholdPeriod}`] || 0,
d.cost_180d || 0, d.conversions_180d || 0, d.convValue_180d || 0, d.roas_180d || 0, d.cpa_180d || 0,
d.cost_365d || 0, d.conversions_365d || 0, d.convValue_365d || 0, d.roas_365d || 0, d.cpa_365d || 0
]);
});
// Prepare search term output
Logger.log("Preparing search term output...");
const searchTermHeaders = [
'Campaign', 'Search Term',
'Cost (30d)', 'Conversions (30d)', 'Conv. Value (30d)', 'ROAS (30d)', 'CPA (30d)',
`Cost (${THRESHOLD_DAYS}d)`, `Conversions (${THRESHOLD_DAYS}d)`, `Conv. Value (${THRESHOLD_DAYS}d)`, `ROAS (${THRESHOLD_DAYS}d)`, `CPA (${THRESHOLD_DAYS}d)`,
'Cost (180d)', 'Conversions (180d)', 'Conv. Value (180d)', 'ROAS (180d)', 'CPA (180d)',
'Cost (365d)', 'Conversions (365d)', 'Conv. Value (365d)', 'ROAS (365d)', 'CPA (365d)'
];
const searchTermOutput = [searchTermHeaders];
filteredSearchTermKeys.forEach(key => {
const d = filteredSearchTermData[key];
const thresholdPeriod = `${THRESHOLD_DAYS}d`;
searchTermOutput.push([
d.campaignName,
d.searchTerm,
d.cost_30d || 0, d.conversions_30d || 0, d.convValue_30d || 0, d.roas_30d || 0, d.cpa_30d || 0,
d[`cost_${thresholdPeriod}`] || 0, d[`conversions_${thresholdPeriod}`] || 0, d[`convValue_${thresholdPeriod}`] || 0, d[`roas_${thresholdPeriod}`] || 0, d[`cpa_${thresholdPeriod}`] || 0,
d.cost_180d || 0, d.conversions_180d || 0, d.convValue_180d || 0, d.roas_180d || 0, d.cpa_180d || 0,
d.cost_365d || 0, d.conversions_365d || 0, d.convValue_365d || 0, d.roas_365d || 0, d.cpa_365d || 0
]);
});
// Write to keyword summary tab
Logger.log("Writing to Keyword Summary tab...");
const keywordTabName = 'Keyword Summary';
let keywordSheet = ss.getSheetByName(keywordTabName);
if (!keywordSheet) {
keywordSheet = ss.insertSheet(keywordTabName);
} else {
keywordSheet.clearContents();
}
keywordSheet.getRange(1, 1, keywordOutput.length, keywordHeaders.length).setValues(keywordOutput);
// Sort by cost in threshold period
sortSheetByCost(keywordSheet, keywordHeaders, keywordOutput, THRESHOLD_DAYS);
// Write to search term summary tab
Logger.log("Writing to Search Term Summary tab...");
const searchTermTabName = 'Search Term Summary';
let searchTermSheet = ss.getSheetByName(searchTermTabName);
if (!searchTermSheet) {
searchTermSheet = ss.insertSheet(searchTermTabName);
} else {
searchTermSheet.clearContents();
}
searchTermSheet.getRange(1, 1, searchTermOutput.length, searchTermHeaders.length).setValues(searchTermOutput);
// Sort by cost in threshold period
sortSheetByCost(searchTermSheet, searchTermHeaders, searchTermOutput, THRESHOLD_DAYS);
// Write to keyword raw data tab (all keywords without performance filtering)
Logger.log("Writing to Keyword - Raw Data tab...");
const keywordRawTabName = 'Keyword - Raw Data';
let keywordRawSheet = ss.getSheetByName(keywordRawTabName);
if (!keywordRawSheet) {
keywordRawSheet = ss.insertSheet(keywordRawTabName);
} else {
keywordRawSheet.clearContents();
}
const keywordRawOutput = [keywordHeaders];
thresholdFilteredKeywordKeys.forEach(key => {
const d = thresholdFilteredKeywordData[key];
const thresholdPeriod = `${THRESHOLD_DAYS}d`;
keywordRawOutput.push([
d.campaignName,
d.keyword,
d.matchType,
d.cost_30d || 0, d.conversions_30d || 0, d.convValue_30d || 0, d.roas_30d || 0, d.cpa_30d || 0,
d[`cost_${thresholdPeriod}`] || 0, d[`conversions_${thresholdPeriod}`] || 0, d[`convValue_${thresholdPeriod}`] || 0, d[`roas_${thresholdPeriod}`] || 0, d[`cpa_${thresholdPeriod}`] || 0,
d.cost_180d || 0, d.conversions_180d || 0, d.convValue_180d || 0, d.roas_180d || 0, d.cpa_180d || 0,
d.cost_365d || 0, d.conversions_365d || 0, d.convValue_365d || 0, d.roas_365d || 0, d.cpa_365d || 0
]);
});
keywordRawSheet.getRange(1, 1, keywordRawOutput.length, keywordHeaders.length).setValues(keywordRawOutput);
// Sort by cost in threshold period
sortSheetByCost(keywordRawSheet, keywordHeaders, keywordRawOutput, THRESHOLD_DAYS);
// Write to search term raw data tab (all search terms without performance filtering)
Logger.log("Writing to Search Terms - Raw Data tab...");
const searchTermRawTabName = 'Search Terms - Raw Data';
let searchTermRawSheet = ss.getSheetByName(searchTermRawTabName);
if (!searchTermRawSheet) {
searchTermRawSheet = ss.insertSheet(searchTermRawTabName);
} else {
searchTermRawSheet.clearContents();
}
const searchTermRawOutput = [searchTermHeaders];
thresholdFilteredSearchTermKeys.forEach(key => {
const d = thresholdFilteredSearchTermData[key];
const thresholdPeriod = `${THRESHOLD_DAYS}d`;
searchTermRawOutput.push([
d.campaignName,
d.searchTerm,
d.cost_30d || 0, d.conversions_30d || 0, d.convValue_30d || 0, d.roas_30d || 0, d.cpa_30d || 0,
d[`cost_${thresholdPeriod}`] || 0, d[`conversions_${thresholdPeriod}`] || 0, d[`convValue_${thresholdPeriod}`] || 0, d[`roas_${thresholdPeriod}`] || 0, d[`cpa_${thresholdPeriod}`] || 0,
d.cost_180d || 0, d.conversions_180d || 0, d.convValue_180d || 0, d.roas_180d || 0, d.cpa_180d || 0,
d.cost_365d || 0, d.conversions_365d || 0, d.convValue_365d || 0, d.roas_365d || 0, d.cpa_365d || 0
]);
});
searchTermRawSheet.getRange(1, 1, searchTermRawOutput.length, searchTermHeaders.length).setValues(searchTermRawOutput);
// Sort by cost in threshold period
sortSheetByCost(searchTermRawSheet, searchTermHeaders, searchTermRawOutput, THRESHOLD_DAYS);
// Calculate potential cost savings in threshold period
Logger.log("Calculating potential cost savings...");
const thresholdPeriod = `${THRESHOLD_DAYS}d`;
let keywordCostSavings = 0;
let searchTermCostSavings = 0;
// Sum up keyword costs in threshold period
filteredKeywordKeys.forEach(key => {
const keywordData = filteredKeywordData[key];
keywordCostSavings += keywordData[`cost_${thresholdPeriod}`] || 0;
});
// Sum up search term costs in threshold period
filteredSearchTermKeys.forEach(key => {
const searchTermData = filteredSearchTermData[key];
searchTermCostSavings += searchTermData[`cost_${thresholdPeriod}`] || 0;
});
const totalCostSavings = keywordCostSavings + searchTermCostSavings;
Logger.log("=== Script Summary ===");
Logger.log(`Exported ${keywordOutput.length - 1} keywords (filtered from ${allKeywordKeys.size} total) to the "${keywordTabName}" tab.`);
Logger.log(`Exported ${searchTermOutput.length - 1} search terms (filtered from ${allSearchTermKeys.size} total) to the "${searchTermTabName}" tab.`);
Logger.log(`Exported ${keywordRawOutput.length - 1} keywords (thresholds only) to the "${keywordRawTabName}" tab.`);
Logger.log(`Exported ${searchTermRawOutput.length - 1} search terms (thresholds only) to the "${searchTermRawTabName}" tab.`);
Logger.log(`Applied thresholds: Min ${THRESHOLDS.minClicks} clicks, Min ${THRESHOLDS.minCost} cost, Min ${THRESHOLDS.minConversions} conversions (${THRESHOLD_DAYS}-day period)`);
if (PERFORMANCE_TARGET.enabled) {
const comparison = PERFORMANCE_TARGET.metric === 'CPA' ? 'above' : 'below';
Logger.log(`Applied performance filter: ${PERFORMANCE_TARGET.metric} ${comparison} ${PERFORMANCE_TARGET.threshold} in ALL periods`);
Logger.log(`Potential cost savings (${THRESHOLD_DAYS}-day period): Keywords: ${keywordCostSavings.toFixed(2)}, Search Terms: ${searchTermCostSavings.toFixed(2)}, Total: ${totalCostSavings.toFixed(2)}`);
}
Logger.log("=== Script Completed Successfully ===");
}
That’s it!
Any comments or add-ons?
Happy to hear!