Google has been steadily increasing the percentage of hidden search terms in Search and Shopping campaigns, making it harder to optimize effectively. Performance Max takes it a step further, offering even fewer insights into what’s driving performance.
This lack of transparency is frustrating, especially when you’re trying to scale campaigns or diagnose underperformance.
So we have something that let us keep an eye on our Pmax campaigns.
This script help you with:
- Track search term performance over time.
- Search through all search terms with over time.
- Separate visible from hidden data.
- Identify trends and optimise campaigns faster.
Glance:
View total, hidden, and visible search terms data at the account level for quick insights.
Overview:
Analyze search terms over time using charts and totals.
Includes filters to:
- Search by Term: See stats for specific terms across all campaigns.
- Filter by Campaign: Drill down to individual campaign-level performance.
Spot overlapping search terms across multiple campaigns.
Search:
Search for specific terms and access detailed over-time data and totals.
Search Categories:
Review categorized search terms from PMax campaigns for better segmentation.
Campaigns:
Get campaign-level data, including totals for all, hidden, and visible search terms.
Config Sheet:
It has 4 settings
Days Data: Specify the range of days for analysis.
Run Script For: Choose between fetching “Search terms” or “Over-time data“.
Days to Skip: Exclude initial days if needed.
Rows to Fetch: It’s a row number from “Search Categories” sheet. Focus on impactful terms, such as those with over 20 clicks.
How to Run the Script:
Remember, you need to run the script two times. And reload the sheet after each run.
First, for fetching search terms.
Second, for fetching search terms, overtime data.
Fetching search terms and overtime data together were taking a lot of time and sometimes end up running failure. So this is the best I have come up with.
Step 1: Fetch Search Terms
Go to the Config Sheet.
Set the following:
- Days Data: 30
- Run Script For: Search Terms
- Days to Skip: Leave empty.
- Run the script.
Step 2: Identify High-Performing Terms
- Reload the sheet.
- Open the Search Categories Sheet after the script completes.
- Scroll to find search terms with more than 20 clicks.
- Note the corresponding row number (e.g., 550).
Step 3: Fetch Over-Time Data
Update the Config Sheet:
- Keep the same settings.
- Update “How Many Rows to Fetch” with the row number you noted earlier (e.g., 550).
- Run the script again.
Rest you need to duplicate the sheet and paste it in the script as usual.
Script:
// V1 PMax Search Terms Script By PPC Leap
// make a copy of https://docs.google.com/spreadsheets/d/1aF0foGmSmRgQiiiSLVmuumLhnQjBJvPYQP4eeCnlb9k/copy
var sheetUrl = 'PASTE_SHEET_URL';
// No changes after this.
const spreadsheet = SpreadsheetApp.openByUrl(sheetUrl);
const configSheet = spreadsheet.getSheetByName('Config');
const scriptRunFor = configSheet.getRange('B3').getValue();
const daysAgo = configSheet.getRange('B2').getValue();
const daysToSkip = configSheet.getRange('B4').getValue();
const dateRange = getDateRange(daysToSkip, daysAgo);
const dateValue = formatDateRange(dateRange);
function main() {
setDateInGlance(dateValue);
if (scriptRunFor === 'Search Terms') {
getSearchTermsReport();
} else if (scriptRunFor === 'Over-Time Data') {
getSearchTermsOverTimeData();
} else {
Logger.log('No matching condition found in B2');
}
}
function getSearchTermsReport() {
var campaigns = [];
var searchTermsData = [];
// Define the query for fetching campaign data
var campaignQuery = `
SELECT campaign.id, campaign.name, metrics.clicks, metrics.cost_micros, metrics.conversions, metrics.conversions_value
FROM campaign
WHERE metrics.cost_micros > 0
AND campaign.advertising_channel_type = 'PERFORMANCE_MAX'
AND segments.date BETWEEN ${dateRange}
ORDER BY metrics.clicks DESC
`;
// Run the query
var campaignReport = AdsApp.report(campaignQuery);
var campaignRows = campaignReport.rows();
// Iterate through the rows and push the results into the campaigns array
while (campaignRows.hasNext()) {
var row = campaignRows.next();
// Convert cost from micros to real value
var cost = row['metrics.cost_micros'] / 1000000;
campaigns.push([
row['campaign.name'],
row['campaign.id'],
row['metrics.clicks'],
cost,
row['metrics.conversions'],
row['metrics.conversions_value']
]);
}
// Loop through each campaign to fetch search terms data
campaigns.forEach(function(campaign) {
var searchTermQuery = `
SELECT campaign.id, campaign.name, campaign_search_term_insight.category_label, metrics.impressions, metrics.clicks, metrics.conversions, metrics.conversions_value, campaign_search_term_insight.id
FROM campaign_search_term_insight
WHERE metrics.clicks > 0
AND campaign.id = ${campaign[1]}
AND segments.date BETWEEN ${dateRange}
`;
var searchTermReport = AdsApp.report(searchTermQuery);
var searchTermRows = searchTermReport.rows();
// Iterate through the search term rows and push the results into the searchTermsData array
while (searchTermRows.hasNext()) {
var searchTermRow = searchTermRows.next();
searchTermsData.push([
searchTermRow['campaign.name'],
searchTermRow['campaign.id'],
searchTermRow['campaign_search_term_insight.id'] === '0' ? "Hidden" : searchTermRow['campaign_search_term_insight.category_label'],
searchTermRow['metrics.impressions'],
searchTermRow['metrics.clicks'],
searchTermRow['metrics.conversions'],
searchTermRow['metrics.conversions_value'],
searchTermRow['campaign_search_term_insight.id']
]);
}
});
searchTermsData.sort((a, b) => b[4] - a[4]);
// Add headers to the data arrays
campaigns.unshift(['Campaign Name', 'Campaign ID', 'Clicks', 'Cost', 'Conversions', 'Conversion Value']);
searchTermsData.unshift(['Campaign Name', 'Campaign ID', 'Category Label', 'Impressions', 'Clicks', 'Conversions', 'Conversion Value', 'Search_Term_ID']);
// Use the data stored in campaigns and searchTermsData as needed
// For example, you could push the data to a Google Sheet
pushDataToSheet(campaigns, searchTermsData);
}
function pushDataToSheet(campaigns, searchTermsData) {
var spreadsheet = SpreadsheetApp.openByUrl(sheetUrl);
var sheet = spreadsheet.getSheetByName('campaigns_data');
var searchTermSheet = spreadsheet.getSheetByName('Search Categories');
// Clear existing content
sheet.clearContents();
searchTermSheet.clearContents();
// Write campaign data to the sheet
sheet.getRange(1, 1, campaigns.length, campaigns[0].length).setValues(campaigns);
// Write search terms data to the sheet
searchTermSheet.getRange(1, 1, searchTermsData.length, searchTermsData[0].length).setValues(searchTermsData);
}
function getSearchTermsOverTimeData() {
// Use the global sheetUrl variable to open the spreadsheet
var spreadsheet = SpreadsheetApp.openByUrl(sheetUrl);
// Fetch the 'Selected S Terms' sheet and the 'r_data' sheet
var selectedTermsSheet = spreadsheet.getSheetByName('Search Categories');
var rDataSheet = spreadsheet.getSheetByName('raw_data');
// Define header row
const headers = [
"Campaign", "Category_Label", "Impressions", "Clicks",
"Conversions", "Conversions Value", "Date", "campaign_id", "STermID"
];
rDataSheet.clearContents();
// Set headers in the first row of 'r_data' sheet
rDataSheet.getRange(1, 1, 1, headers.length).setValues([headers]);
// Get the range of data (assuming data starts from row 2 and column 1)
var dataRange = selectedTermsSheet.getRange(2, 1, selectedTermsSheet.getLastRow() - 1, 8).getValues();
// Initialize an array to hold all data to export at once
var allData = [];
// Loop through each row in the 'Selected S Terms' sheet
for (var i = 0; i < 456; i++) {
var campaignId = dataRange[i][1]; // Campaign ID from column A
var insightId = dataRange[i][7]; // Search Term Insight ID from column D
var query = `
SELECT
campaign.name,
campaign_search_term_insight.category_label,
metrics.impressions,
metrics.clicks,
metrics.conversions,
metrics.conversions_value,
segments.date,
campaign_search_term_insight.campaign_id,
campaign_search_term_insight.id
FROM
campaign_search_term_insight
WHERE
campaign_search_term_insight.campaign_id = ${campaignId}
AND campaign_search_term_insight.id = ${insightId}
AND segments.date BETWEEN ${dateRange}`;
// Execute the query and store results
var report = AdsApp.report(query);
var rows = report.rows(); // Get the rows from the report
// Loop through the rows and push to allData array
while (rows.hasNext()) {
var row = rows.next();
allData.push([
row['campaign.name'],
row['campaign_search_term_insight.id'] === '0' ? "Hidden" : row['campaign_search_term_insight.category_label'],
row['metrics.impressions'],
row['metrics.clicks'],
row['metrics.conversions'],
row['metrics.conversions_value'],
row['segments.date'],
row['campaign_search_term_insight.campaign_id'],
row['campaign_search_term_insight.id']
]);
}
}
// Now export all the data at once to the 'r_data' sheet starting from row 2
if (allData.length > 0) {
rDataSheet.getRange(2, 1, allData.length, allData[0].length).setValues(allData); // Assuming data starts from row 2
}
}
function setDateInGlance(dateValue) {
var glanceSheet = spreadsheet.getSheetByName('Glance');
// Set the date value in cell G1
glanceSheet.getRange('G1').setValue(dateValue);
}
function getDateRange(numDaysToSkip, numDaysToLookBack) {
const endDate = new Date();
endDate.setDate(endDate.getDate() - numDaysToSkip);
// Calculate the start date by subtracting the lookback days from the end date
const startDate = new Date(endDate);
startDate.setDate(startDate.getDate() - numDaysToLookBack + 1);
const format = date => Utilities.formatDate(date, AdsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
return `${format(startDate)} AND ${format(endDate)}`;
}
function formatDateRange(dateString) {
const dates = dateString.split(" AND ");
return dates.map(date => date.replace(/(\d{4})(\d{2})(\d{2})/, "$3-$2-$1")).join(" to ");
}