Automatically identify underperforming geographic locations in your Google Ads account and optimize your location targeting settings.
What The Google Ads script does
👉Finds poor-performing locations across regions, provinces, and cities
👉Analyzes 30, 90, 180, and 365-day performance data
👉Checks location targeting settings across all campaign types
👉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)
- Add that URL as a ‘Sheet_URL’ in the script.
- Optional: Add the performance treshold of the locations you want to export.
- Select your treshold 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 Manually Switching Date Ranges for Location Analysis
Still manually switching between 30, 90, and 365-day date ranges to analyze your location performance?
This script does it all automatically.
Get comprehensive location performance data across multiple time periods in one click, plus identify which campaigns have suboptimal location targeting settings.
No more manual date range switching or complex analysis. Actionable insights in a Google Sheet, after one run!
Script Code
* NEGATIVE LOCATION FINDER
* Written by Matinique Roelse from Adcrease
* Website: https://adcrease.nl
* Linkedin: https://www.linkedin.com/in/matiniqueroelse/
*
* Senior-only Google Ads agency
* ===========================
*
* HOW TO USE THIS SCRIPT:
*
* 1. CONFIGURATION:
* - SHEET_URL: Leave empty to create a new Google Sheet, or paste your existing sheet URL
* - THRESHOLD_DAYS: Number of days to use for threshold evaluation (default: 90)
* The script will use the last 90 days of data to evaluate the performance of the campaigns.
* The script checks 30, 180 and 365 days of data, next to your treshold period
* - THRESHOLDS: Minimum clicks, cost, and conversions required
* - PERFORMANCE_TARGET: Optional ROAS/CPA filtering (set enabled: true to activate)
* - CAMPAIGN_FILTERS: Filter campaigns by name or status
*
* 2. WHAT THE SCRIPT DOES:
* - Analyzes Search, Shopping, and Performance Max campaigns
* - Finds underperforming geographic locations (regions, provinces, cities)
* - Checks location targeting settings across ALL campaign types
* - Exports data to 5 Google Sheets tabs
*
* 3. OUTPUT TABS:
* - "Region Performance - Summary": Filtered underperforming regions
* - "Region Performance - Raw Data": All regions meeting basic thresholds
* - "City Performance - Summary": Filtered underperforming cities
* - "City Performance - Raw Data": All cities meeting basic thresholds
* - "Location Targeting Settings": Campaign location targeting configuration
*
* 4. PERFORMANCE FILTERING:
* - Only works when PERFORMANCE_TARGET.enabled = true
* - Requires poor performance in ALL periods (30d, 90d, 180d, 365d)
* - Uses ROAS (below threshold) or CPA (above threshold) metrics
*
* 5. LOCATION TARGETING CHECK:
* - Shows all campaigns and their location targeting settings
*
* 6. RUNNING THE SCRIPT:
* - Set your configuration values
* - Run the script in Google Ads Scripts
* - Check the logs for progress and any errors
* - Results will appear in your Google Sheet
*
* 7. TROUBLESHOOTING:
* - If no data appears: Check campaign filters and thresholds
* - If performance filtering shows no results: Adjust PERFORMANCE_TARGET settings
*
* Created for Google Ads location optimization and negative targeting analysis.
*/
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: 25, // 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: 1, // 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();
endDate.setDate(endDate.getDate() - 1); // Exclude today
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 getDateRangeForLogging(numDays) {
const endDate = new Date();
endDate.setDate(endDate.getDate() - 1); // Exclude today
const startDate = new Date();
startDate.setDate(endDate.getDate() - numDays + 1); // Inclusive
const format = date => Utilities.formatDate(date, AdsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd');
return {
startDate: format(startDate),
endDate: format(endDate),
description: `${format(startDate)} to ${format(endDate)}`
};
}
function formatToTwoDecimals(value) {
return value ? parseFloat(value).toFixed(2) : '0.00';
}
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);
}
// Cache for geo target constant lookups
let geoTargetCache = {};
// Cache for existing negative location criteria
function extractNumericId(id) {
if (!id) return null;
// If it's already a number, return it
if (typeof id === 'number') return id;
// If it's a string that's just a number, parse it
if (/^\d+$/.test(id)) return parseInt(id);
// If it's a resource name like "geoTargetConstants/20761", extract the number
const match = id.match(/geoTargetConstants\/(\d+)/);
if (match) return parseInt(match[1]);
// If it's some other format, try to extract any number
const numberMatch = id.match(/(\d+)/);
if (numberMatch) return parseInt(numberMatch[1]);
return null;
}
function lookupGeoTargetNames(ids) {
if (ids.length === 0) return {};
// Extract numeric IDs and filter out invalid ones
const numericIds = ids
.map(id => extractNumericId(id))
.filter(id => id !== null && !isNaN(id) && id > 0);
// Filter out IDs we already have in cache
const uncachedIds = numericIds.filter(id => !geoTargetCache[id] && !geoTargetCache[id.toString()]);
if (uncachedIds.length > 0) {
try {
const query = `
SELECT
geo_target_constant.id,
geo_target_constant.name,
geo_target_constant.canonical_name,
geo_target_constant.country_code,
geo_target_constant.target_type
FROM geo_target_constant
WHERE geo_target_constant.id IN (${uncachedIds.join(',')})
AND geo_target_constant.status = "ENABLED"
`;
const rows = AdsApp.search(query);
while (rows.hasNext()) {
const row = rows.next();
const id = row['geoTargetConstant'] && row['geoTargetConstant']['id'] ? row['geoTargetConstant']['id'] : "";
const name = row['geoTargetConstant'] && row['geoTargetConstant']['name'] ? row['geoTargetConstant']['name'] : "";
const canonicalName = row['geoTargetConstant'] && row['geoTargetConstant']['canonicalName'] ? row['geoTargetConstant']['canonicalName'] : "";
const countryCode = row['geoTargetConstant'] && row['geoTargetConstant']['countryCode'] ? row['geoTargetConstant']['countryCode'] : "";
const targetType = row['geoTargetConstant'] && row['geoTargetConstant']['targetType'] ? row['geoTargetConstant']['targetType'] : "";
if (id) {
// Cache by both string and integer versions of the ID
geoTargetCache[id] = {
name: name,
canonicalName: canonicalName,
countryCode: countryCode,
targetType: targetType
};
geoTargetCache[id.toString()] = geoTargetCache[id];
}
}
} catch (e) {
Logger.log(` Error looking up geo target names: ${e}`);
}
}
// Return all requested IDs (cached + newly looked up)
const result = {};
ids.forEach(originalId => {
const numericId = extractNumericId(originalId);
const cachedData = geoTargetCache[numericId] || geoTargetCache[numericId?.toString()] || geoTargetCache[originalId];
if (cachedData) {
result[originalId] = cachedData;
} else {
result[originalId] = {
name: `ID: ${originalId}`,
canonicalName: `ID: ${originalId}`,
countryCode: '',
targetType: 'UNKNOWN'
};
}
});
return result;
}
function fetchLocationTargetingSettings() {
Logger.log("Fetching location targeting settings...");
const campaignStatusFilter = CAMPAIGN_FILTERS.enabled ? 'WHERE campaign.status = "ENABLED"' : '';
const query = `
SELECT
campaign.name,
campaign.status,
campaign.advertising_channel_type,
campaign.geo_target_type_setting.positive_geo_target_type
FROM campaign
${campaignStatusFilter}
`;
const campaignSettings = {};
try {
const rows = AdsApp.search(query);
while (rows.hasNext()) {
const row = rows.next();
const campaignName = row['campaign'] && row['campaign']['name'] ? row['campaign']['name'] : "";
const geoTargetType = row['campaign'] && row['campaign']['geoTargetTypeSetting'] && row['campaign']['geoTargetTypeSetting']['positiveGeoTargetType']
? row['campaign']['geoTargetTypeSetting']['positiveGeoTargetType'] : "";
if (campaignName) {
campaignSettings[campaignName] = {
campaignName: campaignName,
geoTargetType: geoTargetType,
hasPresenceOrInterest: false,
hasPresenceOnly: false,
hasInterestOnly: false
};
// Determine the targeting type
if (geoTargetType === 'PRESENCE_OR_INTEREST') {
campaignSettings[campaignName].hasPresenceOrInterest = true;
} else if (geoTargetType === 'PRESENCE') {
campaignSettings[campaignName].hasPresenceOnly = true;
} else if (geoTargetType === 'INTEREST') {
campaignSettings[campaignName].hasInterestOnly = true;
}
}
}
Logger.log(` Found location targeting settings for ${Object.keys(campaignSettings).length} campaigns`);
} catch (error) {
Logger.log(` Error fetching location targeting settings: ${error.message}`);
}
return campaignSettings;
}
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 fetchGeographicData(dateCondition, periodLabel) {
const campaignStatusFilter = CAMPAIGN_FILTERS.enabled ? 'AND campaign.status = "ENABLED"' : '';
const query = `
SELECT
campaign.name,
campaign.status,
campaign.advertising_channel_type,
geographic_view.country_criterion_id,
geographic_view.location_type,
segments.geo_target_region,
segments.geo_target_province,
segments.geo_target_city,
metrics.cost_micros,
metrics.conversions,
metrics.conversions_value,
metrics.clicks,
metrics.impressions
FROM geographic_view
WHERE segments.date ${dateCondition}
AND geographic_view.country_criterion_id IS NOT NULL
AND campaign.advertising_channel_type IN ("SEARCH", "SHOPPING", "PERFORMANCE_MAX")
${campaignStatusFilter}
`;
const data = {};
const campaignTypeCounts = {};
let totalRows = 0;
try {
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";
const countryId = row['geographicView'] && row['geographicView']['countryCriterionId'] ? row['geographicView']['countryCriterionId'] : "";
const locationType = row['geographicView'] && row['geographicView']['locationType'] ? row['geographicView']['locationType'] : "";
const region = row['segments'] && row['segments']['geoTargetRegion'] ? row['segments']['geoTargetRegion'] : "";
const province = row['segments'] && row['segments']['geoTargetProvince'] ? row['segments']['geoTargetProvince'] : "";
const city = row['segments'] && row['segments']['geoTargetCity'] ? row['segments']['geoTargetCity'] : "";
// Count campaign types
campaignTypeCounts[campaignType] = (campaignTypeCounts[campaignType] || 0) + 1;
// Apply campaign name filters
if (!shouldIncludeCampaign(campaignName)) {
continue;
}
// Use region or province if available, otherwise fall back to country
// City data will be handled separately in city-specific tabs
const locationKey = region || province || countryId;
const key = locationKey + '||' + locationType + '||' + 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;
const impressions = row['metrics'] && row['metrics']['impressions'] ? Number(row['metrics']['impressions']) : 0;
if (!data[key]) {
data[key] = {
campaignName,
countryId,
region,
province,
city,
locationType,
cost: 0,
conversions: 0,
convValue: 0,
clicks: 0,
impressions: 0
};
}
data[key].cost += cost;
data[key].conversions += conversions;
data[key].convValue += convValue;
data[key].clicks += clicks;
data[key].impressions += impressions;
}
} catch (e) {
Logger.log(` Error fetching geographic data: ${e}`);
}
return data;
}
function fetchCityData(dateCondition, periodLabel) {
const campaignStatusFilter = CAMPAIGN_FILTERS.enabled ? 'AND campaign.status = "ENABLED"' : '';
const query = `
SELECT
campaign.name,
campaign.status,
campaign.advertising_channel_type,
geographic_view.country_criterion_id,
geographic_view.location_type,
segments.geo_target_region,
segments.geo_target_province,
segments.geo_target_city,
metrics.cost_micros,
metrics.conversions,
metrics.conversions_value,
metrics.clicks,
metrics.impressions
FROM geographic_view
WHERE segments.date ${dateCondition}
AND geographic_view.country_criterion_id IS NOT NULL
AND campaign.advertising_channel_type IN ("SEARCH", "SHOPPING", "PERFORMANCE_MAX")
AND segments.geo_target_city IS NOT NULL
${campaignStatusFilter}
`;
const data = {};
const campaignTypeCounts = {};
let totalRows = 0;
try {
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";
const countryId = row['geographicView'] && row['geographicView']['countryCriterionId'] ? row['geographicView']['countryCriterionId'] : "";
const locationType = row['geographicView'] && row['geographicView']['locationType'] ? row['geographicView']['locationType'] : "";
const region = row['segments'] && row['segments']['geoTargetRegion'] ? row['segments']['geoTargetRegion'] : "";
const province = row['segments'] && row['segments']['geoTargetProvince'] ? row['segments']['geoTargetProvince'] : "";
const city = row['segments'] && row['segments']['geoTargetCity'] ? row['segments']['geoTargetCity'] : "";
// Count campaign types
campaignTypeCounts[campaignType] = (campaignTypeCounts[campaignType] || 0) + 1;
// Apply campaign name filters
if (!shouldIncludeCampaign(campaignName)) {
continue;
}
// For cities, use city as the primary key
const locationKey = city;
const key = locationKey + '||' + locationType + '||' + 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;
const impressions = row['metrics'] && row['metrics']['impressions'] ? Number(row['metrics']['impressions']) : 0;
if (!data[key]) {
data[key] = {
campaignName,
countryId,
region,
province,
city,
locationType,
cost: 0,
conversions: 0,
convValue: 0,
clicks: 0,
impressions: 0
};
}
data[key].cost += cost;
data[key].conversions += conversions;
data[key].convValue += convValue;
data[key].clicks += clicks;
data[key].impressions += impressions;
}
} catch (e) {
Logger.log(` Error fetching city data: ${e}`);
}
return data;
}
function filterRegionsByThresholds(data) {
const filteredData = {};
const thresholdPeriod = `${THRESHOLD_DAYS}d`;
for (const key in data) {
const regionData = data[key];
// Check if region meets threshold period criteria
if (regionData[`clicks_${thresholdPeriod}`] >= THRESHOLDS.minClicks &&
regionData[`cost_${thresholdPeriod}`] >= THRESHOLDS.minCost &&
regionData[`conversions_${thresholdPeriod}`] >= THRESHOLDS.minConversions) {
filteredData[key] = regionData;
}
}
return filteredData;
}
function filterRegionsByPerformanceTarget(data) {
if (!PERFORMANCE_TARGET.enabled) {
return data;
}
const filteredData = {};
const periods = ['30d', `${THRESHOLD_DAYS}d`, '180d', '365d'];
for (const key in data) {
const regionData = data[key];
let hasBadPerformanceInAllPeriods = true;
// Check if region has bad performance in ALL periods
for (const period of periods) {
const metricValue = regionData[`${PERFORMANCE_TARGET.metric.toLowerCase()}_${period}`] || 0;
const conversions = regionData[`conversions_${period}`] || 0;
if (PERFORMANCE_TARGET.metric === 'CPA') {
// For CPA: exclude if BELOW threshold (good performance)
if (metricValue <= PERFORMANCE_TARGET.threshold) {
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] = regionData;
}
}
return filteredData;
}
function main() {
Logger.log("=== Starting Negative Location Finder Script (Search, Shopping, and Performance Max campaigns) ===");
let ss;
if (!SHEET_URL) {
ss = SpreadsheetApp.create("Region 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', dateRange: getDateRangeForLogging(30) },
{ label: `${THRESHOLD_DAYS}d`, condition: getDateRangeString(THRESHOLD_DAYS), dateRange: getDateRangeForLogging(THRESHOLD_DAYS) },
{ label: '180d', condition: getDateRangeString(180), dateRange: getDateRangeForLogging(180) },
{ label: '365d', condition: getDateRangeString(365), dateRange: getDateRangeForLogging(365) }
];
Logger.log(`Analyzing data for periods: ${periods.map(p => p.label).join(', ')}`);
// Log exact date ranges being used
Logger.log("\n=== Date Ranges ===");
periods.forEach(period => {
Logger.log(`${period.label}: ${period.dateRange.description}`);
});
// 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 region data
Logger.log("\n=== Fetching Region Data ===");
const allRegionData = {};
const allRegionKeys = new Set();
const allLocationIds = new Set();
const processedCampaigns = new Set();
periods.forEach(period => {
Logger.log(` Fetching region data for ${period.label} (${period.dateRange.description})...`);
const periodData = fetchGeographicData(period.condition, period.label);
for (const key in periodData) {
if (!allRegionData[key]) allRegionData[key] = {
campaignName: periodData[key].campaignName,
countryId: periodData[key].countryId,
region: periodData[key].region,
province: periodData[key].province,
city: periodData[key].city,
locationType: periodData[key].locationType
};
// Track unique campaigns
processedCampaigns.add(periodData[key].campaignName);
// Collect all unique location IDs for lookup
if (periodData[key].countryId) allLocationIds.add(periodData[key].countryId);
if (periodData[key].region) allLocationIds.add(periodData[key].region);
if (periodData[key].province) allLocationIds.add(periodData[key].province);
if (periodData[key].city) allLocationIds.add(periodData[key].city);
allRegionData[key][`cost_${period.label}`] = periodData[key].cost;
allRegionData[key][`conversions_${period.label}`] = periodData[key].conversions;
allRegionData[key][`convValue_${period.label}`] = periodData[key].convValue;
allRegionData[key][`clicks_${period.label}`] = periodData[key].clicks;
allRegionData[key][`impressions_${period.label}`] = periodData[key].impressions;
allRegionData[key][`roas_${period.label}`] = periodData[key].cost > 0 ? periodData[key].convValue / periodData[key].cost : 0;
allRegionData[key][`cpa_${period.label}`] = periodData[key].conversions > 0 ? periodData[key].cost / periodData[key].conversions : 0;
allRegionKeys.add(key);
}
});
// Log processed campaigns in a pretty format
Logger.log(`\n=== Processed Campaigns ===`);
Logger.log(`Total campaigns: ${processedCampaigns.size}`);
const campaignList = Array.from(processedCampaigns).sort();
campaignList.forEach(campaign => {
Logger.log(` - ${campaign}`);
});
// Fetch city data separately
Logger.log("\n=== Fetching City Data ===");
const allCityData = {};
const allCityKeys = new Set();
periods.forEach(period => {
Logger.log(` Fetching city data for ${period.label} (${period.dateRange.description})...`);
const periodData = fetchCityData(period.condition, period.label);
for (const key in periodData) {
if (!allCityData[key]) allCityData[key] = {
campaignName: periodData[key].campaignName,
countryId: periodData[key].countryId,
region: periodData[key].region,
province: periodData[key].province,
city: periodData[key].city,
locationType: periodData[key].locationType
};
// Collect all unique location IDs for lookup
if (periodData[key].countryId) allLocationIds.add(periodData[key].countryId);
if (periodData[key].region) allLocationIds.add(periodData[key].region);
if (periodData[key].province) allLocationIds.add(periodData[key].province);
if (periodData[key].city) allLocationIds.add(periodData[key].city);
allCityData[key][`cost_${period.label}`] = periodData[key].cost;
allCityData[key][`conversions_${period.label}`] = periodData[key].conversions;
allCityData[key][`convValue_${period.label}`] = periodData[key].convValue;
allCityData[key][`clicks_${period.label}`] = periodData[key].clicks;
allCityData[key][`impressions_${period.label}`] = periodData[key].impressions;
allCityData[key][`roas_${period.label}`] = periodData[key].cost > 0 ? periodData[key].convValue / periodData[key].cost : 0;
allCityData[key][`cpa_${period.label}`] = periodData[key].conversions > 0 ? periodData[key].cost / periodData[key].conversions : 0;
allCityKeys.add(key);
}
});
// Look up geo target names
Logger.log("Looking up location names...");
const locationNames = lookupGeoTargetNames(Array.from(allLocationIds));
// Fetch location targeting settings
const locationTargetingSettings = fetchLocationTargetingSettings();
// Filter regions based on thresholds and performance targets
Logger.log("\n=== Filtering Data ===");
Logger.log("Filtering regions...");
let filteredRegionData = filterRegionsByThresholds(allRegionData);
const thresholdFilteredRegionData = { ...filteredRegionData }; // Copy for raw data tab
filteredRegionData = filterRegionsByPerformanceTarget(filteredRegionData);
const filteredRegionKeys = Object.keys(filteredRegionData);
const thresholdFilteredRegionKeys = Object.keys(thresholdFilteredRegionData);
// Filter cities based on thresholds and performance targets
Logger.log("Filtering cities...");
let filteredCityData = filterRegionsByThresholds(allCityData);
const thresholdFilteredCityData = { ...filteredCityData }; // Copy for raw data tab
filteredCityData = filterRegionsByPerformanceTarget(filteredCityData);
const filteredCityKeys = Object.keys(filteredCityData);
const thresholdFilteredCityKeys = Object.keys(thresholdFilteredCityData);
// Prepare region output
Logger.log("\n=== Preparing Output ===");
Logger.log("Preparing region output...");
const regionHeaders = [
'Campaign', 'Country', 'Region', 'Province', 'Location 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 regionOutput = [regionHeaders];
filteredRegionKeys.forEach(key => {
const d = filteredRegionData[key];
const thresholdPeriod = `${THRESHOLD_DAYS}d`;
// Get readable names for each location
const countryName = locationNames[d.countryId] ? locationNames[d.countryId].name : (d.countryId || '');
const regionName = d.region && locationNames[d.region] ? locationNames[d.region].name : (d.region || '');
const provinceName = d.province && locationNames[d.province] ? locationNames[d.province].name : (d.province || '');
regionOutput.push([
d.campaignName,
countryName,
regionName,
provinceName,
d.locationType,
formatToTwoDecimals(d.cost_30d), formatToTwoDecimals(d.conversions_30d), formatToTwoDecimals(d.convValue_30d), formatToTwoDecimals(d.roas_30d), formatToTwoDecimals(d.cpa_30d),
formatToTwoDecimals(d[`cost_${thresholdPeriod}`]), formatToTwoDecimals(d[`conversions_${thresholdPeriod}`]), formatToTwoDecimals(d[`convValue_${thresholdPeriod}`]), formatToTwoDecimals(d[`roas_${thresholdPeriod}`]), formatToTwoDecimals(d[`cpa_${thresholdPeriod}`]),
formatToTwoDecimals(d.cost_180d), formatToTwoDecimals(d.conversions_180d), formatToTwoDecimals(d.convValue_180d), formatToTwoDecimals(d.roas_180d), formatToTwoDecimals(d.cpa_180d),
formatToTwoDecimals(d.cost_365d), formatToTwoDecimals(d.conversions_365d), formatToTwoDecimals(d.convValue_365d), formatToTwoDecimals(d.roas_365d), formatToTwoDecimals(d.cpa_365d)
]);
});
// Write to region summary tab
Logger.log("Writing to Region Performance - Summary tab...");
const regionTabName = 'Region Performance - Summary';
let regionSheet = ss.getSheetByName(regionTabName);
if (!regionSheet) {
regionSheet = ss.insertSheet(regionTabName);
} else {
regionSheet.clearContents();
}
regionSheet.getRange(1, 1, regionOutput.length, regionHeaders.length).setValues(regionOutput);
// Sort by cost in threshold period
sortSheetByCost(regionSheet, regionHeaders, regionOutput, THRESHOLD_DAYS);
// Write to region raw data tab (all regions without performance filtering)
Logger.log("Writing to Region Performance - Raw Data tab...");
const regionRawTabName = 'Region Performance - Raw Data';
let regionRawSheet = ss.getSheetByName(regionRawTabName);
if (!regionRawSheet) {
regionRawSheet = ss.insertSheet(regionRawTabName);
} else {
regionRawSheet.clearContents();
}
const regionRawOutput = [regionHeaders];
thresholdFilteredRegionKeys.forEach(key => {
const d = thresholdFilteredRegionData[key];
const thresholdPeriod = `${THRESHOLD_DAYS}d`;
// Get readable names for each location
const countryName = locationNames[d.countryId] ? locationNames[d.countryId].name : (d.countryId || '');
const regionName = d.region && locationNames[d.region] ? locationNames[d.region].name : (d.region || '');
const provinceName = d.province && locationNames[d.province] ? locationNames[d.province].name : (d.province || '');
regionRawOutput.push([
d.campaignName,
countryName,
regionName,
provinceName,
d.locationType,
formatToTwoDecimals(d.cost_30d), formatToTwoDecimals(d.conversions_30d), formatToTwoDecimals(d.convValue_30d), formatToTwoDecimals(d.roas_30d), formatToTwoDecimals(d.cpa_30d),
formatToTwoDecimals(d[`cost_${thresholdPeriod}`]), formatToTwoDecimals(d[`conversions_${thresholdPeriod}`]), formatToTwoDecimals(d[`convValue_${thresholdPeriod}`]), formatToTwoDecimals(d[`roas_${thresholdPeriod}`]), formatToTwoDecimals(d[`cpa_${thresholdPeriod}`]),
formatToTwoDecimals(d.cost_180d), formatToTwoDecimals(d.conversions_180d), formatToTwoDecimals(d.convValue_180d), formatToTwoDecimals(d.roas_180d), formatToTwoDecimals(d.cpa_180d),
formatToTwoDecimals(d.cost_365d), formatToTwoDecimals(d.conversions_365d), formatToTwoDecimals(d.convValue_365d), formatToTwoDecimals(d.roas_365d), formatToTwoDecimals(d.cpa_365d)
]);
});
regionRawSheet.getRange(1, 1, regionRawOutput.length, regionHeaders.length).setValues(regionRawOutput);
// Sort by cost in threshold period
sortSheetByCost(regionRawSheet, regionHeaders, regionRawOutput, THRESHOLD_DAYS);
// Prepare city output (filter cities only)
Logger.log("Preparing city output...");
const cityHeaders = [
'Campaign', 'Country', 'Region', 'Province', 'City', 'Location 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 cityOutput = [cityHeaders];
// Use the separate city data
filteredCityKeys.forEach(key => {
const d = filteredCityData[key];
const thresholdPeriod = `${THRESHOLD_DAYS}d`;
// Get readable names for each location
const countryName = locationNames[d.countryId] ? locationNames[d.countryId].name : (d.countryId || '');
const regionName = d.region && locationNames[d.region] ? locationNames[d.region].name : (d.region || '');
const provinceName = d.province && locationNames[d.province] ? locationNames[d.province].name : (d.province || '');
const cityName = d.city && locationNames[d.city] ? locationNames[d.city].name : (d.city || '');
cityOutput.push([
d.campaignName,
countryName,
regionName,
provinceName,
cityName,
d.locationType,
formatToTwoDecimals(d.cost_30d), formatToTwoDecimals(d.conversions_30d), formatToTwoDecimals(d.convValue_30d), formatToTwoDecimals(d.roas_30d), formatToTwoDecimals(d.cpa_30d),
formatToTwoDecimals(d[`cost_${thresholdPeriod}`]), formatToTwoDecimals(d[`conversions_${thresholdPeriod}`]), formatToTwoDecimals(d[`convValue_${thresholdPeriod}`]), formatToTwoDecimals(d[`roas_${thresholdPeriod}`]), formatToTwoDecimals(d[`cpa_${thresholdPeriod}`]),
formatToTwoDecimals(d.cost_180d), formatToTwoDecimals(d.conversions_180d), formatToTwoDecimals(d.convValue_180d), formatToTwoDecimals(d.roas_180d), formatToTwoDecimals(d.cpa_180d),
formatToTwoDecimals(d.cost_365d), formatToTwoDecimals(d.conversions_365d), formatToTwoDecimals(d.convValue_365d), formatToTwoDecimals(d.roas_365d), formatToTwoDecimals(d.cpa_365d)
]);
});
// Write to city performance tab
Logger.log("Writing to City Performance - Summary tab...");
const cityTabName = 'City Performance - Summary';
let citySheet = ss.getSheetByName(cityTabName);
if (!citySheet) {
citySheet = ss.insertSheet(cityTabName);
} else {
citySheet.clearContents();
}
citySheet.getRange(1, 1, cityOutput.length, cityHeaders.length).setValues(cityOutput);
// Sort by cost in threshold period
sortSheetByCost(citySheet, cityHeaders, cityOutput, THRESHOLD_DAYS);
// Write to city raw data tab (all cities without performance filtering)
Logger.log("Writing to City Performance - Raw Data tab...");
const cityRawTabName = 'City Performance - Raw Data';
let cityRawSheet = ss.getSheetByName(cityRawTabName);
if (!cityRawSheet) {
cityRawSheet = ss.insertSheet(cityRawTabName);
} else {
cityRawSheet.clearContents();
}
const cityRawOutput = [cityHeaders];
thresholdFilteredCityKeys.forEach(key => {
const d = thresholdFilteredCityData[key];
const thresholdPeriod = `${THRESHOLD_DAYS}d`;
// Get readable names for each location
const countryName = locationNames[d.countryId] ? locationNames[d.countryId].name : (d.countryId || '');
const regionName = d.region && locationNames[d.region] ? locationNames[d.region].name : (d.region || '');
const provinceName = d.province && locationNames[d.province] ? locationNames[d.province].name : (d.province || '');
const cityName = d.city && locationNames[d.city] ? locationNames[d.city].name : (d.city || '');
cityRawOutput.push([
d.campaignName,
countryName,
regionName,
provinceName,
cityName,
d.locationType,
formatToTwoDecimals(d.cost_30d), formatToTwoDecimals(d.conversions_30d), formatToTwoDecimals(d.convValue_30d), formatToTwoDecimals(d.roas_30d), formatToTwoDecimals(d.cpa_30d),
formatToTwoDecimals(d[`cost_${thresholdPeriod}`]), formatToTwoDecimals(d[`conversions_${thresholdPeriod}`]), formatToTwoDecimals(d[`convValue_${thresholdPeriod}`]), formatToTwoDecimals(d[`roas_${thresholdPeriod}`]), formatToTwoDecimals(d[`cpa_${thresholdPeriod}`]),
formatToTwoDecimals(d.cost_180d), formatToTwoDecimals(d.conversions_180d), formatToTwoDecimals(d.convValue_180d), formatToTwoDecimals(d.roas_180d), formatToTwoDecimals(d.cpa_180d),
formatToTwoDecimals(d.cost_365d), formatToTwoDecimals(d.conversions_365d), formatToTwoDecimals(d.convValue_365d), formatToTwoDecimals(d.roas_365d), formatToTwoDecimals(d.cpa_365d)
]);
});
cityRawSheet.getRange(1, 1, cityRawOutput.length, cityHeaders.length).setValues(cityRawOutput);
// Sort by cost in threshold period
sortSheetByCost(cityRawSheet, cityHeaders, cityRawOutput, THRESHOLD_DAYS);
// Create location targeting settings tab
Logger.log("Preparing location targeting settings...");
const targetingHeaders = ['Campaign', 'Location Targeting Setting', 'Status', 'Notes'];
const targetingOutput = [targetingHeaders];
Object.values(locationTargetingSettings).forEach(campaign => {
let setting = 'Unknown';
let status = '⚠️ Needs Review';
let notes = '';
if (campaign.hasPresenceOrInterest) {
setting = 'Presence or Interest';
status = '⚠️ Needs Review';
notes = 'Consider changing to "Presence Only"';
} else if (campaign.hasPresenceOnly) {
setting = 'Presence Only';
status = 'âś… Good';
notes = 'Optimal setting';
} else if (campaign.hasInterestOnly) {
setting = 'Interest Only';
status = '❌ Not Recommended';
notes = 'Change to "Presence Only"';
} else {
setting = 'No Location Targeting';
status = '⚠️ Check';
notes = 'No location criteria found';
}
targetingOutput.push([campaign.campaignName, setting, status, notes]);
});
// Write to location targeting settings tab
Logger.log("Writing to Location Targeting Settings tab...");
const targetingTabName = 'Location Targeting Settings';
let targetingSheet = ss.getSheetByName(targetingTabName);
if (!targetingSheet) {
targetingSheet = ss.insertSheet(targetingTabName);
} else {
targetingSheet.clearContents();
}
targetingSheet.getRange(1, 1, targetingOutput.length, targetingHeaders.length).setValues(targetingOutput);
// Sort by status (Good first, then Needs Review, then Not Recommended)
if (targetingOutput.length > 1) {
const statusColumnIndex = targetingHeaders.indexOf('Status');
targetingSheet.getRange(2, 1, targetingOutput.length - 1, targetingHeaders.length).sort({column: statusColumnIndex + 1, ascending: true});
}
// Calculate potential cost savings in threshold period
Logger.log("\n=== Calculating Cost Savings ===");
const thresholdPeriod = `${THRESHOLD_DAYS}d`;
let regionCostSavings = 0;
// Sum up region costs in threshold period
filteredRegionKeys.forEach(key => {
const regionData = filteredRegionData[key];
regionCostSavings += regionData[`cost_${thresholdPeriod}`] || 0;
});
Logger.log("\n=== Script Summary ===");
Logger.log(`Exported ${regionOutput.length - 1} regions (filtered from ${allRegionKeys.size} total) to the "${regionTabName}" tab.`);
Logger.log(`Exported ${regionRawOutput.length - 1} regions (thresholds only) to the "${regionRawTabName}" tab.`);
Logger.log(`Exported ${cityOutput.length - 1} cities (filtered) to the "${cityTabName}" tab.`);
Logger.log(`Exported ${cityRawOutput.length - 1} cities (thresholds only) to the "${cityRawTabName}" tab.`);
Logger.log(`Exported ${targetingOutput.length - 1} campaigns to the "${targetingTabName}" 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): Regions: ${regionCostSavings.toFixed(2)}`);
Logger.log("=== Script Completed Successfully ===");
}
Â
That’s it!
Any comments or add-ons?
Happy to hear!