What the Script Does:
Pulls search term data from the past 30 days that had not cost induced in the preceding 180 days.
So you always stay on top of new search terms in your account.
How to Set Up the Script:
- Copy this gsheet:
- Configure the Script in Google Ads:
- Log in to your Google Ads account.
- Click the "+" button to create a new script.
- Copy and paste the script below into the script editor.
- Replace
"YOUR_SPREADSHEET_URL_HERE"
in the script with the URL of the Google Sheet you created. - Preview the script for it to pull account data into the reporting sheet
- Schedule the script to run as often as you need it
function main() {
// Edit your settings here. and put your gsheet url
var sheetUrl = 'YOUR_GSHEET_URL_HERE'; // Replace with your Google Sheet URL
var sheetId = extractSheetIdFromUrl(sheetUrl);
var n = 1; // Set your desired N-gram value (e.g., 2 for bi-grams)
// Fetch settings from the "Settings" tab
var settingsSheet = SpreadsheetApp.openByUrl(sheetUrl).getSheetByName("Settings");
var clickThreshold = settingsSheet.getRange("C3").getValue();
var pmaxEnabled = settingsSheet.getRange("C5").getValue().toString().toLowerCase() === 'true';
//check for pmax campaigns
var campaignIds = getCampaignIds();
// no changes after here
var last30Days = 'LAST_30_DAYS';
var preceding180Days = getPreceding180DaysRange();
// Fetch data for the last 30 days
var last30DaysData = fetchSearchTermsData(last30Days, clickThreshold);
// Fetch data for the preceding 180 days
var preceding180DaysData = fetchSearchTermsData(preceding180Days, clickThreshold);
// Get unique search terms from the preceding 180 days
var preceding180DaysQueries = new Set(preceding180DaysData.map(function(row) {
return row[2]; // Query is the first element in each row
}));
// Filter out the search terms that occurred in the preceding 180 days
var uniqueLast30DaysData = last30DaysData.filter(function(row) {
return !preceding180DaysQueries.has(row[2]);
});
// Collect all data for writing
var allData = {
'Last 30 Days Data': addMetrics(last30DaysData),
'Preceding 180 Days Data': addMetrics(preceding180DaysData),
'30 Days Data (new Search terms)': addMetrics(uniqueLast30DaysData)
};
// Only fetch and process PMAX data if enabled and available
if (pmaxEnabled && campaignIds.length > 0) {
// Fetch PMax data for the last 30 days and preceding 180 days
var last30DaysPmaxData = fetchPmaxData("last_30_days", clickThreshold);
var preceding180DaysPmaxData = fetchPmaxData("preceding_180_days", clickThreshold);
// Get unique search terms from the preceding 180 days
var preceding180DaysPmaxQueries = new Set(preceding180DaysPmaxData.map(function(row) {
return row[1];
}));
// Filter out the search terms that occurred in the preceding 180 days
var uniqueLast30DaysPmaxData = last30DaysPmaxData.filter(function(row) {
return !preceding180DaysPmaxQueries.has(row[1]);
});
// Add PMAX data to allData
allData['Last 30 Days Data (Pmax)'] = addPmaxMetrics(last30DaysPmaxData);
allData['Preceding 180 days data (Pmax)'] = addPmaxMetrics(preceding180DaysPmaxData);
allData['30 Days Data (new Search terms - Pmax)'] = addPmaxMetrics(uniqueLast30DaysPmaxData);
}
writeAllDataToSheet(sheetId, allData);
performNGramAnalysis(sheetId, 'N-Gram Data (new Search terms)', uniqueLast30DaysData, n);
if (pmaxEnabled) {
performNGramAnalysis(sheetId, 'N-Gram Data (new Search terms - Pmax)', uniqueLast30DaysPmaxData, n);
}
}
function addMetrics(data) {
return data.map(function(row) {
var cost = row[4];
var conversions = row[7];
var conversionValue = row[8];
var costPerConversion = conversions > 0 ? cost / conversions : 0;
var conversionValuePerCost = cost > 0 ? conversionValue / cost : 0;
var campaignType = row[10]; // Campaign type is in position 10
return [
row[0], // Campaign Name
row[1], // Ad Group Name
row[2], // Search Term
row[3], // Match Type
row[4], // Cost
row[5], // Impressions
row[6], // Clicks
row[7], // Conversions
row[8], // Conversion Value
costPerConversion, // Cost / Conversion
campaignType, // Campaign Type
"", // Adgroup Cost / Conv. (formula)
"", // Adgroup Conv. Value / Cost (formula)
"", // CPC bucket (formula)
conversionValuePerCost // Conv. Value / Cost
];
});
}
function addPmaxMetrics(data) {
return data.map(function(row) {
return [
row[0], // Campaign
row[1], // Search Category
row[2], // Impressions
row[3], // Clicks
row[4], // Conversions
row[5] // Conversion Value
];
});
}
function extractSheetIdFromUrl(url) {
var matches = url.match(/\/spreadsheets\/d\/([a-zA-Z0-9-_]+)/);
if (matches && matches[1]) {
return matches[1];
} else {
throw new Error('Invalid Google Sheet URL');
}
}
function getCampaignIds() {
var campaignIds = [];
var gaqlQuery = "SELECT campaign.id, campaign.name FROM campaign WHERE campaign.advertising_channel_type = 'PERFORMANCE_MAX' AND campaign.status = 'ENABLED'";
var results = AdsApp.search(gaqlQuery);
Logger.log('Starting campaign ID search');
while (results.hasNext()) {
var result = results.next();
campaignIds.push(result.campaign.id);
Logger.log(`Found campaign: ${result.campaign.name} (${result.campaign.id})`);
}
Logger.log(`Total PMax campaigns found: ${campaignIds.length}`);
return campaignIds;
}
function getCampaignNameById(campaignId) {
var gaqlQuery = "SELECT campaign.name FROM campaign WHERE campaign.id = '" + campaignId + "'";
var results = AdsApp.search(gaqlQuery);
if (results.hasNext()) {
var result = results.next();
return result.campaign.name;
} else {
console.log("No campaign with id: " + campaignId);
return "";
}
}
function fetchPmaxData(period, clickThreshold) {
const campaignIds = getCampaignIds();
// Check if campaignIds is empty
if (!campaignIds || campaignIds.length === 0) {
Logger.log("No PMAX campaign found. Exiting function.");
return;
}
const campaignNames = {};
// Batch fetch campaign names
const nameQuery = "SELECT campaign.id, campaign.name FROM campaign WHERE campaign.id IN (" + campaignIds.join(",") + ")";
const nameResults = AdsApp.search(nameQuery);
while (nameResults.hasNext()) {
const result = nameResults.next();
campaignNames[result.campaign.id] = result.campaign.name;
}
return campaignIds.reduce((allPmaxData, campaignId) => {
const searchCategoryStats = getSearchCategorieStats(campaignId, period, clickThreshold);
for (const [category, stats] of Object.entries(searchCategoryStats)) {
allPmaxData.push([
campaignNames[campaignId],
category,
stats.impressions,
stats.clicks,
stats.conversions,
stats.conversionValue
]);
}
return allPmaxData;
}, []);
}
function getSearchCategorieStats(campaignId, period, clickThreshold) {
Logger.log('Starting stats retrieval for campaign ' + campaignId);
const searchCategoryObject = {};
const periodString = period === "last_30_days"
? ` BETWEEN '${dates(30)}' AND '${dates(1)}'`
: ` BETWEEN '${dates(210)}' AND '${dates(31)}'`;
// Get only category data
const query = `
SELECT
campaign_search_term_insight.category_label,
metrics.impressions,
metrics.clicks,
metrics.conversions,
metrics.conversions_value
FROM campaign_search_term_insight
WHERE campaign_search_term_insight.campaign_id = ${campaignId}
AND segments.date ${periodString}`;
Logger.log('Executing query...');
const report = AdsApp.search(query);
Logger.log('Processing query results...');
let rowCount = 0;
while (report.hasNext()) {
rowCount++;
const result = report.next();
const category = result.campaignSearchTermInsight.categoryLabel;
const clicks = parseInt(result.metrics.clicks);
if (clicks >= clickThreshold) {
if (!searchCategoryObject[category]) {
searchCategoryObject[category] = {
impressions: 0,
clicks: 0,
conversions: 0,
conversionValue: 0
};
}
searchCategoryObject[category].impressions += parseInt(result.metrics.impressions);
searchCategoryObject[category].clicks += clicks;
searchCategoryObject[category].conversions += parseFloat(result.metrics.conversions);
searchCategoryObject[category].conversionValue += parseFloat(result.metrics.conversionsValue);
}
}
Logger.log(`Completed processing ${rowCount} rows`);
return searchCategoryObject;
}
function fetchSearchTermsData(dateRange, clickThreshold) {
var query = `
SELECT
CampaignName,
AdGroupName,
Query,
QueryMatchTypeWithVariant,
Cost,
Impressions,
Clicks,
Conversions,
ConversionValue,
CostPerConversion
FROM
SEARCH_QUERY_PERFORMANCE_REPORT
WHERE
Impressions > 0 AND Clicks >= ${clickThreshold}
DURING
${dateRange}
`;
// Create campaign type mapping
var campaignTypeMap = {};
var campaignQuery = "SELECT campaign.name, campaign.advertising_channel_type FROM campaign WHERE campaign.status != 'REMOVED'";
var campaignResults = AdsApp.search(campaignQuery);
while (campaignResults.hasNext()) {
var result = campaignResults.next();
var campaignName = result.campaign.name;
var channelType = result.campaign.advertisingChannelType;
campaignTypeMap[campaignName] = channelType;
}
var report = AdsApp.report(query);
var rows = report.rows();
var data = [];
while (rows.hasNext()) {
var row = rows.next();
// Special handling for cost
var cost = row['Cost'];
cost = cost.toString().replace(/,/g, '');
cost = parseFloat(cost);
// Get campaign name and determine type
var campaignName = row['CampaignName'];
var channelType = campaignTypeMap[campaignName];
var campaignType = 'Unknown';
// Map the channel type to a readable format
if (channelType === 'SHOPPING') {
campaignType = 'Shopping';
} else if (channelType === 'SEARCH') {
campaignType = 'Search';
} else if (channelType === 'PERFORMANCE_MAX') {
campaignType = 'PMax';
}
data.push([
campaignName,
row['AdGroupName'],
row['Query'],
row['QueryMatchTypeWithVariant'],
cost,
parseInt(row['Impressions']),
parseInt(row['Clicks']),
parseFloat(row['Conversions']),
parseFloat(row['ConversionValue']),
parseFloat(row['CostPerConversion']),
campaignType
]);
}
Logger.log('Total campaigns processed: ' + Object.keys(campaignTypeMap).length);
Logger.log('Total rows processed: ' + data.length);
return data;
}
function getOrCreateSheet(spreadsheet, sheetName) {
var sheet = spreadsheet.getSheetByName(sheetName);
if (!sheet) {
sheet = spreadsheet.insertSheet(sheetName);
}
return sheet;
}
function performNGramAnalysis(sheetId, sheetName, data, n) {
Logger.log('Performing N-Gram analysis for sheet: ' + sheetName);
Logger.log('Data length: ' + (data ? data.length : 'undefined'));
var spreadsheet = SpreadsheetApp.openById(sheetId);
var sheet = getOrCreateSheet(spreadsheet, sheetName);
sheet.clear();
// Determine the header based on the sheet name
var headers;
if (sheetName.includes('Pmax')) {
headers = ['N-Gram', 'Impressions', 'Clicks', 'Conversions', 'Conversion Value'];
} else {
headers = ['N-Gram', 'Cost', 'Impressions', 'Clicks', 'Conversions', 'Conversion Value', 'Cost / Conv'];
}
// Write the header row
sheet.appendRow(headers);
// Perform the N-Gram analysis
if (sheetName.includes('Pmax')) {
performPmaxNGramAnalysis(sheet, data, n);
} else {
performRegularNGramAnalysis(sheet, data, n);
}
}
function getPreceding180DaysRange() {
var today = new Date();
var startDate = new Date(today.getFullYear(), today.getMonth(), today.getDate() - 210);
var endDate = new Date(today.getFullYear(), today.getMonth(), today.getDate() - 31);
var start = Utilities.formatDate(startDate, AdsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
var end = Utilities.formatDate(endDate, AdsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
return `${start},${end}`;
}
function dates(x){
var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
var now = new Date();
var date = new Date(now.getTime() - x * MILLIS_PER_DAY);
var timeZone = AdsApp.currentAccount().getTimeZone();
var output = Utilities.formatDate(date, timeZone, 'yyyy-MM-dd');
return output;
}
function writeAllDataToSheet(sheetId, allData) {
try {
var spreadsheet = SpreadsheetApp.openById(sheetId);
for (var sheetName in allData) {
var data = allData[sheetName];
var sheet = getOrCreateSheet(spreadsheet, sheetName);
sheet.clear();
var headers;
var formulasNeeded = false;
if (sheetName.includes('Pmax')) {
headers = [
'Campaign', // A
'Search Category', // B
'Impressions', // C
'Clicks', // D
'Conversions', // E
'Conversion Value' // F
];
} else {
headers = [
'Campaign Name', // A
'Ad Group Name', // B
'Search Term', // C
'Match Type', // D
'Cost', // E
'Impressions', // F
'Clicks', // G
'Conversions', // H
'Conversion Value', // I
'Cost / Conversion', // J
'Campaign Type', // K
'Adgroup Cost / Conv.', // L
'Adgroup Conv. Value / Cost', // M
'CPC bucket', // N
'Conv. Value / Cost' // O
];
formulasNeeded = true;
}
var values = [headers];
// Only add non-empty rows to values array
for (var i = 0; i < data.length; i++) {
var row = data[i];
if (row.some(cell => cell !== "")) {
values.push(row);
}
}
// Write only non-empty rows
if (values.length > 1) {
sheet.getRange(1, 1, values.length, headers.length).setValues(values);
if (formulasNeeded) {
// Set formula for Adgroup Cost / Conv. (column L)
var adGroupCostPerConvFormula = '=IF(B2<>"", SUMIF(B:B, B2, E:E) / SUMIF(B:B, B2, H:H), 0)';
var costPerConvRange = sheet.getRange(2, 12, values.length - 1, 1);
costPerConvRange.setFormula(adGroupCostPerConvFormula);
// Set formula for Adgroup Conv. Value / Cost (column M)
var adGroupValuePerCostFormula = '=IFERROR(ARRAYFORMULA(IF(B2:B<>"", SUMIF(B2:B, B2:B, H2:H) / SUMIF(B2:B, B2:B, E2:E), )), 0)';
sheet.getRange(2, 13).setFormula(adGroupValuePerCostFormula);
// Add CPC bucket formula (column N)
var cpcBucketFormula = '=ARRAYFORMULA(IF(G2:G = 0, "Division by 0", IF(E2:E / G2:G <= 1, "0 <= CPC <= 1", IF(E2:E / G2:G <= 2, "1 < CPC <= 2", IF(E2:E / G2:G <= 3, "2 < CPC <= 3", IF(E2:E / G2:G <= 4, "3 < CPC <= 4", IF(E2:E / G2:G <= 5, "4 < CPC <= 5", IF(E2:E / G2:G <= 6, "5 < CPC <= 6", IF(E2:E / G2:G <= 7, "6 < CPC <= 7", IF(E2:E / G2:G <= 8, "7 < CPC <= 8", IF(E2:E / G2:G <= 9, "8 < CPC <= 9", IF(E2:E / G2:G <= 10, "9 < CPC <= 10", "CPC > 10"))))))))))))';
sheet.getRange(2, 14).setFormula(cpcBucketFormula);
}
}
}
} catch (error) {
Logger.log('Error writing to sheet: ' + error.message);
}
}
function performRegularNGramAnalysis(sheet, data, n) {
if (!data || !Array.isArray(data) || data.length === 0 || typeof data.length === 'undefined' ) {
Logger.log('Error: Invalid or empty data passed to performregularNGramAnalysis');
return;
}
// Build a dictionary of N-Grams and their associated metrics
var nGramData = {};
for (var i = 0; i < data.length; i++) {
var query = data[i][2];
var impressions = data[i][5];
var clicks = data[i][6];
var conversions = data[i][7];
var conversionValue = data[i][8];
var cost = data[i][4];
// Split query into words
var words = query.split(/\s+/);
// Generate N-grams
for (var j = 0; j <= words.length - n; j++) {
var nGram = words.slice(j, j + n).join(' ');
if (!nGramData[nGram]) {
nGramData[nGram] = { impressions: 0, clicks: 0, conversions: 0, conversionValue: 0, cost: 0, costPerConversion: 0 };
}
nGramData[nGram].impressions += impressions;
nGramData[nGram].clicks += clicks;
nGramData[nGram].conversions += conversions;
nGramData[nGram].conversionValue += conversionValue;
nGramData[nGram].cost += cost;
nGramData[nGram].costPerConversion = nGramData[nGram].conversions ? (nGramData[nGram].cost / nGramData[nGram].conversions) : 0;
}
}
// Write N-gram data to sheet
var values = [];
for (var nGram in nGramData) {
var record = nGramData[nGram];
values.push([nGram, record.cost, record.impressions, record.clicks, record.conversions, record.conversionValue, record.costPerConversion]);
}
sheet.getRange(2, 1, values.length, values[0].length).setValues(values);
}
function checkPermissions() {
try {
const account = AdsApp.currentAccount();
Logger.log(`Account: ${account.getName()} (${account.getCustomerId()})`);
return true;
} catch (e) {
Logger.log(`Permission Error: ${e.message}`);
return false;
}
}
function performPmaxNGramAnalysis(sheet, data, n) {
// Add error checking
if (!data || !Array.isArray(data) || data.length === 0 || typeof data.length === 'undefined' ) {
Logger.log('Error: Invalid or empty data passed to performPmaxNGramAnalysis');
return;
}
// Build a dictionary of N-Grams and their associated metrics
var nGramData = {};
for (var i = 0; i < data.length; i++) {
// Add error checking for each row
if (!data[i] || !Array.isArray(data[i]) || data[i].length < 6) {
Logger.log('Error: Invalid data row at index ' + i + ' in performPmaxNGramAnalysis');
continue;
}
var query = data[i][1];
var impressions = data[i][2];
var clicks = data[i][3];
var conversions = data[i][4];
var conversionValue = data[i][5];
// Check if query is a string
if (typeof query !== 'string') {
Logger.log('Error: Invalid query at index ' + i + ' in performPmaxNGramAnalysis');
continue;
}
// Split query into words
var words = query.split(/\s+/);
// Generate N-grams
for (var j = 0; j <= words.length - n; j++) {
var nGram = words.slice(j, j + n).join(' ');
if (!nGramData[nGram]) {
nGramData[nGram] = { impressions: 0, clicks: 0, conversions: 0, conversionValue: 0 };
}
nGramData[nGram].impressions += impressions;
nGramData[nGram].clicks += clicks;
nGramData[nGram].conversions += conversions;
nGramData[nGram].conversionValue += conversionValue;
}
}
// Write N-gram data to sheet
var values = [];
for (var nGram in nGramData) {
var record = nGramData[nGram];
values.push([nGram, record.impressions, record.clicks, record.conversions, record.conversionValue]);
}
// Add error checking before writing to sheet
if (values.length > 0) {
sheet.getRange(2, 1, values.length, values[0].length).setValues(values);
} else {
Logger.log('Warning: No N-gram data to write to sheet in performPmaxNGramAnalysis');
}
}