// Campaign filters (optional)
filters: {
includeCampaignNameContains: [],
excludeCampaignNameContains: [],
campaignTypes: [] // Leave empty for all types, or specify: ‘SEARCH’, ‘SHOPPING’, ‘DISPLAY’, ‘VIDEO’, ‘PERFORMANCE_MAX’
},
// Display options
display: {
showLessThan10AsText: true, // Show “< 10%” instead of the actual value when below 10%
roundPercentages: false, // Round percentages to whole numbers
decimalPlaces: 2 // Number of decimal places to show for percentages
}
};
/**
* Main function that runs the impression share aggregation
*/
function main() {
Logger.log(‘Starting Impression Share Aggregation Script…’);
// Determine the date range to use
const dateRange = getDateRange();
Logger.log(`Using date range: ${dateRange.start} to ${dateRange.end}`);
// Get campaign data with impression share metrics
const campaignData = getCampaignData(dateRange);
// Calculate properly weighted impression share metrics by campaign type
const aggregatedData = calculateWeightedMetricsByCampaignType(campaignData);
// Output the results
outputResults(campaignData, aggregatedData, dateRange);
Logger.log(‘Script completed successfully.’);
}
function getDateRange() {
if (CONFIG.dateRange.useCustomDateRange) {
if (CONFIG.dateRange.customDateRange.start && CONFIG.dateRange.customDateRange.end) {
return {
start: CONFIG.dateRange.customDateRange.start,
end: CONFIG.dateRange.customDateRange.end
};
}
}
return getDateRangeFromPredefined(CONFIG.dateRange.predefinedRange);
}
function getDateRangeFromPredefined(predefinedRange) {
const now = new Date();
let startDate, endDate;
switch (predefinedRange) {
case ‘TODAY’:
startDate = new Date(now);
endDate = new Date(now);
break;
case ‘YESTERDAY’:
startDate = new Date(now);
startDate.setDate(startDate.getDate() – 1);
endDate = new Date(startDate);
break;
case ‘LAST_7_DAYS’:
endDate = new Date(now);
startDate = new Date(now);
startDate.setDate(startDate.getDate() – 7);
break;
case ‘LAST_WEEK’:
endDate = new Date(now);
endDate.setDate(endDate.getDate() – (endDate.getDay() + 1));
startDate = new Date(endDate);
startDate.setDate(startDate.getDate() – 6);
break;
case ‘LAST_14_DAYS’:
endDate = new Date(now);
startDate = new Date(now);
startDate.setDate(startDate.getDate() – 14);
break;
case ‘LAST_30_DAYS’:
endDate = new Date(now);
startDate = new Date(now);
startDate.setDate(startDate.getDate() – 30);
break;
case ‘THIS_MONTH’:
startDate = new Date(now.getFullYear(), now.getMonth(), 1);
endDate = new Date(now);
break;
case ‘LAST_MONTH’:
startDate = new Date(now.getFullYear(), now.getMonth() – 1, 1);
endDate = new Date(now.getFullYear(), now.getMonth(), 0);
break;
case ‘ALL_TIME’:
startDate = new Date(2000, 0, 1);
endDate = new Date(now);
break;
default:
endDate = new Date(now);
startDate = new Date(now);
startDate.setDate(startDate.getDate() – 30);
}
return {
start: formatDateYYYYMMDD(startDate),
end: formatDateYYYYMMDD(endDate)
};
}
function formatDateYYYYMMDD(date) {
const year = date.getFullYear();
const month = (date.getMonth() + 1).toString().padStart(2, ‘0’);
const day = date.getDate().toString().padStart(2, ‘0’);
return `${year}${month}${day}`;
}
function getCampaignData(dateRange) {
let conditions = [
`metrics.impressions > 0`,
`segments.date BETWEEN ${dateRange.start} AND ${dateRange.end}`
];
if (CONFIG.filters.campaignTypes && CONFIG.filters.campaignTypes.length > 0) {
conditions.push(`campaign.advertising_channel_type IN (${CONFIG.filters.campaignTypes.map(t => `’${t}’`).join(‘,’)})`);
}
const query = `
SELECT
campaign.id,
campaign.name,
campaign.status,
campaign.advertising_channel_type,
campaign.bidding_strategy_type,
metrics.impressions,
metrics.clicks,
metrics.all_conversions,
metrics.cost_micros,
metrics.conversions,
metrics.search_impression_share,
metrics.search_top_impression_share,
metrics.search_absolute_top_impression_share,
metrics.search_click_share,
metrics.search_exact_match_impression_share,
metrics.video_view_rate,
metrics.video_views,
metrics.engagements
FROM campaign
WHERE ${conditions.join(‘ AND ‘)}
ORDER BY metrics.impressions DESC
`;
try {
const report = AdsApp.report(query);
const rows = report.rows();
const campaigns = [];
while (rows.hasNext()) {
try {
const row = rows.next();
if (CONFIG.filters.includeCampaignNameContains.length > 0) {
const nameMatches = CONFIG.filters.includeCampaignNameContains.some(
term => row[‘campaign.name’].indexOf(term) !== -1
);
if (!nameMatches) continue;
}
if (CONFIG.filters.excludeCampaignNameContains.length > 0) {
const nameMatches = CONFIG.filters.excludeCampaignNameContains.some(
term => row[‘campaign.name’].indexOf(term) !== -1
);
if (nameMatches) continue;
}
const campaignType = row[‘campaign.advertising_channel_type’];
const searchIS = parseFloat(row[‘metrics.search_impression_share’] || 0) * 100;
const clickShare = parseFloat(row[‘metrics.search_click_share’] || 0) * 100;
const exactMatchIS = parseFloat(row[‘metrics.search_exact_match_impression_share’] || 0) * 100;
const topIS = parseFloat(row[‘metrics.search_top_impression_share’] || 0) * 100;
const absTopIS = parseFloat(row[‘metrics.search_absolute_top_impression_share’] || 0) * 100;
const impressions = parseInt(row[‘metrics.impressions’] || 0, 10);
const clicks = parseInt(row[‘metrics.clicks’] || 0, 10);
const cost = parseInt(row[‘metrics.cost_micros’] || 0, 10) / 1000000;
const conversions = parseFloat(row[‘metrics.conversions’] || 0);
const allConversions = parseFloat(row[‘metrics.all_conversions’] || 0);
const videoViews = parseInt(row[‘metrics.video_views’] || 0, 10);
const videoViewRate = parseFloat(row[‘metrics.video_view_rate’] || 0) * 100;
const engagements = parseInt(row[‘metrics.engagements’] || 0, 10);
let interactions = clicks;
if (campaignType === ‘VIDEO’) {
interactions = engagements;
} else if (campaignType === ‘PERFORMANCE_MAX’) {
interactions = clicks + videoViews;
}
const eligibleImpressions = searchIS > 0 ? Math.round(impressions / (searchIS / 100)) : 0;
const eligibleExactMatchImpressions = exactMatchIS > 0 ? Math.round(impressions / (exactMatchIS / 100)) : 0;
const eligibleClicks = clickShare > 0 ? Math.round(clicks / (clickShare / 100)) : 0;
campaigns.push({
id: row[‘campaign.id’],
name: row[‘campaign.name’],
status: row[‘campaign.status’],
type: campaignType,
biddingStrategy: row[‘campaign.bidding_strategy_type’],
impressions: impressions,
clicks: clicks,
interactions: interactions,
cost: cost,
conversions: conversions,
allConversions: allConversions,
searchIS: searchIS,
clickShare: clickShare,
exactMatchIS: exactMatchIS,
topIS: topIS,
absTopIS: absTopIS,
videoViews: videoViews,
videoViewRate: videoViewRate,
engagements: engagements,
eligibleImpressions: eligibleImpressions,
eligibleExactMatchImpressions: eligibleExactMatchImpressions,
eligibleClicks: eligibleClicks,
hasImpressionShareMetrics: [‘SEARCH’, ‘SHOPPING’, ‘PERFORMANCE_MAX’].includes(campaignType)
});
} catch (rowError) {
Logger.log(`Error processing row: ${rowError}`);
}
}
Logger.log(`Retrieved data for ${campaigns.length} campaigns`);
return campaigns;
} catch (reportError) {
Logger.log(`Error running report: ${reportError}`);
return [];
}
}
function calculateWeightedMetricsByCampaignType(campaignData) {
const campaignsByType = {};
campaignData.forEach(campaign => {
if (!campaignsByType) {
campaignsByType = [];
}
campaignsByType.push(campaign);
});
const metricsByType = {};
Object.keys(campaignsByType).forEach(type => {
metricsByType = calculateWeightedMetrics(campaignsByType);
});
const overallMetrics = calculateWeightedMetrics(campaignData);
return {
overall: overallMetrics,
byType: metricsByType
};
}
function calculateWeightedMetrics(campaigns) {
let totalImpressions = 0;
let totalClicks = 0;
let totalInteractions = 0;
let totalCost = 0;
let totalConversions = 0;
let totalAllConversions = 0;
let totalEligibleImpressions = 0;
let totalEligibleExactMatchImpressions = 0;
let totalEligibleClicks = 0;
let totalVideoViews = 0;
let totalEngagements = 0;
let campaignsWithImpressionShareMetrics = 0;
campaigns.forEach(campaign => {
totalImpressions += campaign.impressions;
totalClicks += campaign.clicks;
totalInteractions += campaign.interactions;
totalCost += campaign.cost;
totalConversions += campaign.conversions;
totalAllConversions += campaign.allConversions;
totalVideoViews += campaign.videoViews;
totalEngagements += campaign.engagements;
if (campaign.hasImpressionShareMetrics) {
totalEligibleImpressions += campaign.eligibleImpressions;
totalEligibleExactMatchImpressions += campaign.eligibleExactMatchImpressions;
totalEligibleClicks += campaign.eligibleClicks;
campaignsWithImpressionShareMetrics++;
}
});
let aggregatedSearchIS = 0;
let aggregatedExactMatchIS = 0;
let aggregatedClickShare = 0;
if (campaignsWithImpressionShareMetrics > 0) {
aggregatedSearchIS = totalEligibleImpressions > 0 ?
(totalImpressions / totalEligibleImpressions * 100) : 0;
aggregatedExactMatchIS = totalEligibleExactMatchImpressions > 0 ?
(totalImpressions / totalEligibleExactMatchImpressions * 100) : 0;
aggregatedClickShare = totalEligibleClicks > 0 ?
(totalClicks / totalEligibleClicks * 100) : 0;
}
const formattedSearchIS = formatImpressionShareMetric(aggregatedSearchIS);
const formattedClickShare = formatImpressionShareMetric(aggregatedClickShare);
const formattedExactMatchIS = formatImpressionShareMetric(aggregatedExactMatchIS);
const ctr = totalImpressions > 0 ? (totalClicks / totalImpressions * 100) : 0;
const convRate = totalClicks > 0 ? (totalConversions / totalClicks * 100) : 0;
const avgCpc = totalClicks > 0 ? (totalCost / totalClicks) : 0;
const costPerConv = totalConversions > 0 ? (totalCost / totalConversions) : 0;
return {
impressions: totalImpressions,
clicks: totalClicks,
interactions: totalInteractions,
cost: totalCost.toFixed(2),
conversions: totalConversions.toFixed(2),
allConversions: totalAllConversions.toFixed(2),
ctr: ctr.toFixed(2) + ‘%’,
convRate: convRate.toFixed(2) + ‘%’,
avgCpc: avgCpc.toFixed(2),
costPerConv: costPerConv.toFixed(2),
searchImpressionShare: formattedSearchIS,
clickShare: formattedClickShare,
exactMatchImpressionShare: formattedExactMatchIS,
eligibleImpressions: totalEligibleImpressions,
eligibleExactMatchImpressions: totalEligibleExactMatchImpressions,
eligibleClicks: totalEligibleClicks,
videoViews: totalVideoViews,
engagements: totalEngagements,
hasImpressionShareMetrics: campaignsWithImpressionShareMetrics > 0
};
}
function formatImpressionShareMetric(value) {
if (value === 0) {
return ‘—’;
}
if (value < 10) {
return ‘< 10%’;
}
if (CONFIG.display.roundPercentages) {
return Math.round(value) + ‘%’;
}
return value.toFixed(CONFIG.display.decimalPlaces) + ‘%’;
}
function outputResults(campaignData, aggregatedData, dateRange) {
let spreadsheet;
if (CONFIG.output.spreadsheetUrl) {
spreadsheet = SpreadsheetApp.openByUrl(CONFIG.output.spreadsheetUrl);
} else {
const formattedStartDate = dateRange.start.replace(/(\d{4})(\d{2})(\d{2})/, ‘$1-$2-$3’);
const formattedEndDate = dateRange.end.replace(/(\d{4})(\d{2})(\d{2})/, ‘$1-$2-$3’);
spreadsheet = SpreadsheetApp.create(`Google Ads Impression Share Report (${formattedStartDate} to ${formattedEndDate})`);
Logger.log(‘Created new spreadsheet: ‘ + spreadsheet.getUrl());
}
const sheet = spreadsheet.getActiveSheet();
sheet.clear();
const hasVideoOrPMax = campaignData.some(c => c.type === ‘VIDEO’ || c.type === ‘PERFORMANCE_MAX’);
const headers = [
‘Campaign’, ‘Status’, ‘Type’, ‘Bidding Strategy’,
‘Impressions’, hasVideoOrPMax ? ‘Interactions’ : ‘Clicks’, ‘Cost’, ‘Conv.’,
‘CTR’, ‘Conv. Rate’, ‘Avg. CPC’, ‘Cost/Conv.’,
‘Search Impr. Share’, ‘Click Share’, ‘Search Exact Match IS’
];
sheet.appendRow(headers);
sheet.getRange(1, 1, 1, headers.length).setFontWeight(‘bold’);
campaignData.forEach(campaign => {
const ctr = campaign.impressions > 0 ? (campaign.clicks / campaign.impressions * 100).toFixed(2) + ‘%’ : ‘0%’;
const convRate = campaign.clicks > 0 ? (campaign.conversions / campaign.clicks * 100).toFixed(2) + ‘%’ : ‘0%’;
const avgCpc = campaign.clicks > 0 ? (campaign.cost / campaign.clicks).toFixed(2) : ‘0’;
const costPerConv = campaign.conversions > 0 ? (campaign.cost / campaign.conversions).toFixed(2) : ‘0’;
const searchISFormatted = campaign.hasImpressionShareMetrics ?
formatImpressionShareMetric(campaign.searchIS) : ‘—’;
const clickShareFormatted = campaign.hasImpressionShareMetrics ?
formatImpressionShareMetric(campaign.clickShare) : ‘—’;
const exactMatchISFormatted = campaign.hasImpressionShareMetrics ?
formatImpressionShareMetric(campaign.exactMatchIS) : ‘—’;
sheet.appendRow([
campaign.name,
campaign.status,
campaign.type,
campaign.biddingStrategy,
campaign.impressions,
hasVideoOrPMax ? campaign.interactions : campaign.clicks,
campaign.cost.toFixed(2),
campaign.conversions.toFixed(2),
ctr,
convRate,
avgCpc,
costPerConv,
searchISFormatted,
clickShareFormatted,
exactMatchISFormatted
]);
});
const separatorRow = Array(headers.length).fill(”);
sheet.appendRow(separatorRow);
sheet.appendRow([
‘TOTAL’,
”,
”,
”,
aggregatedData.overall.impressions,
hasVideoOrPMax ? aggregatedData.overall.interactions : aggregatedData.overall.clicks,
aggregatedData.overall.cost,
aggregatedData.overall.conversions,
aggregatedData.overall.ctr,
aggregatedData.overall.convRate,
aggregatedData.overall.avgCpc,
aggregatedData.overall.costPerConv,
aggregatedData.overall.searchImpressionShare,
aggregatedData.overall.clickShare,
aggregatedData.overall.exactMatchImpressionShare
]);
sheet.autoResizeColumns(1, headers.length);
if (CONFIG.output.emailResults) {
const subject = ‘Google Ads Impression Share Report – ‘ +
dateRange.start.replace(/(\d{4})(\d{2})(\d{2})/, ‘$1-$2-$3’) + ‘ to ‘ +
dateRange.end.replace(/(\d{4})(\d{2})(\d{2})/, ‘$1-$2-$3’);
const body = `
Google Ads Impression Share Report
Date Range: ${dateRange.start.replace(/(\d{4})(\d{2})(\d{2})/, ‘$1-$2-$3’)} to ${dateRange.end.replace(/(\d{4})(\d{2})(\d{2})/, ‘$1-$2-$3’)}
ACCOUNT TOTALS:
Impressions: ${aggregatedData.overall.impressions}
Clicks: ${aggregatedData.overall.clicks}
Cost: $${aggregatedData.overall.cost}
Conversions: ${aggregatedData.overall.conversions}
Search Impression Share: ${aggregatedData.overall.searchImpressionShare}
Click Share: ${aggregatedData.overall.clickShare}
Search Exact Match IS: ${aggregatedData.overall.exactMatchImpressionShare}
Full report available at: ${spreadsheet.getUrl()}
`;
MailApp.sendEmail({
to: CONFIG.output.emailAddress,
subject: subject,
body: body
});
Logger.log(`Email report sent to: ${CONFIG.output.emailAddress}`);
}
}
Next Steps
- Paste this script into your Google Ads Scripts.
- Adjust the CONFIG object to suit your needs—especially your date range, email settings, and any campaign filters.
- Run the script and watch it generate a neat spreadsheet of your data, complete with a total row for an at-a-glance view.
With these insights, you’ll have a clearer sense of which campaigns are capturing the bulk of eligible impressions and where you might still be leaving potential traffic on the table. Happy optimizing!