Here is an example of how you could structure a detailed email report for underperforming search terms organized by theme (verbs, nouns, adjectives), using google ads scripts. This email will include information such as impressions, search term, match type, clicks, conversions, and associated keywords:
—
Subject: Underperforming Search Term Report
Dear ,
Here’s the underperforming search terms report from your Google Ads campaigns:
Nouns:
– term1
– term2
– …
Verbs:
– term1
– term2
– …
Adjectives:
– term1
– term2
– …
Adverbs:
– term1
– term2
– …
Pronouns:
– term1
– term2
– …
Prepositions:
– term1
– term2
– …
Conjunctions:
– term1
– term2
– …
Interjections:
– term1
– term2
– …
Top 5 N-Grams from Queries that Convert:
1. ngram1
2. ngram2
3. ngram3
4. ngram4
5. ngram5
Top 20 N-Grams from Queries that Do Not Convert:
1. ngram1
2. ngram2
3. ngram3
…
19. ngram19
20. ngram20
Total Underperforming Search Terms: X
For further assistance, feel free to reach out.
Best regards,
function main() {
var CLICKS_THRESHOLD = 1;
var report = AdsApp.report(
`SELECT Query, Impressions, Clicks, Cost, Ctr, Conversions, CostPerConversion, AdGroupId, CampaignId ` +
`FROM SEARCH_QUERY_PERFORMANCE_REPORT ` +
`WHERE Clicks >= ${CLICKS_THRESHOLD} ` +
`AND Conversions = 0 ` +
`DURING LAST_30_DAYS`
);
var searchData = [];
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var data = {
query: row['Query'],
impressions: row['Impressions'],
clicks: row['Clicks'],
cost: row['Cost'],
ctr: row['Ctr'],
conversions: row['Conversions'],
costPerConversion: row['CostPerConversion'],
adGroupId: row['AdGroupId'],
campaignId: row['CampaignId']
};
searchData.push(data);
}
var convertReport = AdsApp.report(
`SELECT Query ` +
`FROM SEARCH_QUERY_PERFORMANCE_REPORT ` +
`WHERE Clicks >= ${CLICKS_THRESHOLD} ` +
`AND Conversions > 0 ` +
`DURING LAST_30_DAYS`
);
var convertSearchData = [];
var convertRows = convertReport.rows();
while (convertRows.hasNext()) {
var convertRow = convertRows.next();
convertSearchData.push(convertRow['Query']);
}
var topConvertNGrams = getTopNGrams(convertSearchData, 5);
var topNonConvertNGrams = getTopNGrams(searchData.map(data => data.query), 20);
generateUnderperformingSearchTermsReport(searchData, topConvertNGrams, topNonConvertNGrams);
}
function getTopNGrams(searchQueries, n) {
var nGrams = {};
searchQueries.forEach(function (query) {
var words = query.split(' ');
for (var i = 0; i < words.length - 1; i++) {
var nGram = words.slice(i, i + n).join(' ');
if (!nGrams) {
nGrams = 1;
} else {
nGrams++;
}
}
});
var sortedNGrams = Object.keys(nGrams).sort(function (a, b) {
return nGrams - nGrams;
});
return sortedNGrams.slice(0, n);
}
function generateUnderperformingSearchTermsReport(searchData, topConvertNGrams, topNonConvertNGrams) {
var searchTermsByCategory = {
'Noun': [],
'Verb': [],
'Adjective': [],
'Adverb': [],
'Pronoun': [],
'Preposition': [],
'Conjunction': [],
'Interjection': []
};
searchData.forEach(function (data) {
var category = determineCategory(data.query);
searchTermsByCategory.push({
searchTerm: data.query,
impressions: data.impressions,
clicks: data.clicks,
conversions: data.conversions,
costPerConversion: data.costPerConversion,
adGroupId: data.adGroupId,
campaignId: data.campaignId
});
});
sendEmailReport(searchTermsByCategory, topConvertNGrams, topNonConvertNGrams);
// Log what was sent and any changes made
Logger.log("Email sent with underperforming search terms report.");
}
function determineCategory(searchTerm) {
// Determine the category of the search term based on its linguistic properties
if (searchTerm.includes(' ')) {
return 'Noun';
} else if (searchTerm.includes('ing')) {
return 'Verb';
} else if (searchTerm.endsWith('ly')) {
return 'Adverb';
} else if (searchTerm === searchTerm.toUpperCase()) {
return 'Pronoun';
} else if (['a', 'an', 'the'].includes(searchTerm.toLowerCase())) {
return 'Article';
} else if (['and', 'but', 'or', 'nor', 'for', 'so', 'yet'].includes(searchTerm.toLowerCase())) {
return 'Conjunction';
} else if (['!', '!!', '!!!'].includes(searchTerm)) {
return 'Interjection';
} else {
return 'Adjective';
}
}
function sendEmailReport(searchTermsByCategory, topConvertNGrams, topNonConvertNGrams) {
var emailBody = "Dear ,\n\nI hope this email finds you well. As requested, here's a detailed report on underperforming search terms from your Google Ads campaigns.\n\n";
for (var category in searchTermsByCategory) {
if (searchTermsByCategory.hasOwnProperty(category) && searchTermsByCategory.length > 0) {
emailBody += "**" + category + "s:**\n\n";
emailBody += "| Search Term | Impressions | Clicks | Conversions | Cost Per Conversion | Ad Group ID | Campaign ID |\n";
emailBody += "|-------------------|-------------|--------|-------------|----------------------|-------------|------------|\n";
searchTermsByCategory.forEach(function (term) {
var impressions = term.impressions ? term.impressions.toString() : '';
var clicks = term.clicks ? term.clicks.toString() : '';
var conversions = term.conversions ? term.conversions.toString() : '';
var costPerConversion = term.costPerConversion ? term.costPerConversion.toString() : '';
var adGroupId = term.adGroupId ? term.adGroupId : '';
var campaignId = term.campaignId ? term.campaignId : '';
emailBody += "| " + padRight(term.searchTerm, 18) + " | " + padLeft(impressions, 11) + " | " + padLeft(clicks, 6) + " | " + padLeft(conversions, 11) + " | " + padLeft(costPerConversion, 20) + " | " + padRight(adGroupId, 11) + " | " + padRight(campaignId, 10) + " |\n";
});
emailBody += "\n";
}
}
emailBody += "**Top 5 N-Grams from Queries that Convert:**\n\n";
emailBody += topConvertNGrams.join(", ") + "\n\n";
emailBody += "**Top 20 N-Grams from Queries that Do Not Convert:**\n\n";
emailBody += topNonConvertNGrams.join(", ") + "\n\n";
emailBody += "**Total Underperforming Search Terms: X**\n\n";
emailBody += "If you need further analysis or assistance in optimizing your Google Ads campaigns, please feel free to reach out.\n\n";
emailBody += "Best regards,\n John M. Williams";
// Replace placeholders such as and with your actual name and email address
var recipientEmail = "youremail@email.com"; // Replace with your email address
var subject = "Underperforming Search Term Report";
// Send email
MailApp.sendEmail(recipientEmail, subject, emailBody);
Logger.log("Email body:\n" + emailBody);
}
function padRight(str, length) {
return (str + " ".repeat(length)).slice(0, length);
}
function padLeft(str, length) {
return (" ".repeat(length) + str).slice(-length);
}
This email template provides a clear breakdown of underperforming search terms organized by theme (verbs, nouns, adjectives), along with relevant performance metrics and associated keywords. You can customize the template as needed to fit your specific requirements and branding guidelines.
The Nerdy What:
- main() Function:Sets a threshold for the minimum number of clicks required (CLICKS_THRESHOLD).Queries the Google Ads API to retrieve data from the SEARCH_QUERY_PERFORMANCE_REPORT for search queries that meet the criteria:Clicks greater than or equal to the threshold (CLICKS_THRESHOLD).Conversions equal to 0.Data collected over the last 30 days.Extracts relevant data (such as query, impressions, clicks, conversions, etc.) from the report and stores it in an array (searchData).Queries the Google Ads API again to retrieve search queries that converted (had conversions > 0) and stores them in convertSearchData.Calls getTopNGrams() function to get the top n-grams from both converting and non-converting search queries.Calls generateUnderperformingSearchTermsReport() to generate and send an email report based on the collected data.
- getTopNGrams() Function:Takes an array of search queries (searchQueries) and a number n as input.Iterates through each search query to extract n-grams.Counts the occurrence of each n-gram and stores it in an object (nGrams).Sorts the n-grams based on their occurrence count in descending order.Returns the top n n-grams from the sorted list.
- generateUnderperformingSearchTermsReport() Function:Takes three arguments: searchData (an array of search data), topConvertNGrams (top n-grams from converting queries), and topNonConvertNGrams (top n-grams from non-converting queries).Categorizes search terms by their linguistic properties (Noun, Verb, Adjective, etc.) and stores them in searchTermsByCategory.Constructs an email body containing a detailed report on underperforming search terms, including their metrics and categorized by linguistic properties.Includes the top 5 n-grams from converting queries and the top 20 n-grams from non-converting queries in the email body.Sends the email report to the specified recipient email address and logs the email body for reference.
- determineCategory() Function:Takes a search term (searchTerm) as input.Determines the linguistic category of the search term based on its properties such as presence of spaces, endings, case, or specific words.Returns the category of the search term (Noun, Verb, Adjective, etc.).
- sendEmailReport() Function:Constructs the email body with detailed information on underperforming search terms, categorized data, and top n-grams.Includes placeholders for the recipient’s name and email address.Sets the recipient’s email address, subject, and email body.Sends the email using MailApp service and logs the email body for reference.
- padRight() and padLeft() Functions:Helper functions used to pad strings with spaces to ensure consistent column alignment in the email report. padRight() pads spaces to the right of the string, while padLeft() pads spaces to the left.
These functions work together to collect, analyze, categorize, and report on underperforming search terms from Google Ads campaigns, providing actionable insights for optimization.