Created
October 2, 2024 17:55
-
-
Save siliconvallaeys/0527249a7aee382db6360e50e4d7487a to your computer and use it in GitHub Desktop.
Get blog topic ideas from your search terms data in Google Ads - uses GPT
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/****************************************** | |
* Trending Search Terms Blog Idea Generator | |
* @version: 1.0 | |
* @author: | |
* - Frederick Vallaeys (Optmyzr) | |
* -------------------------------- | |
* Install this script in your Google Ads account (not an MCC account) | |
* to identify trending search terms based on significant impression growth. | |
* The script finds search terms that have at least a minimum number of impressions | |
* in the past 7 days and have either increased by a specified multiplier compared to | |
* the previous 7 days or have gone from zero to meeting the minimum impression threshold. | |
* For the top 5 highest-volume trending search terms, it uses the OpenAI GPT API to | |
* generate blog post ideas. | |
* The script then compiles these ideas into an email and sends it to your specified | |
* email address. | |
* -------------------------------- | |
* For more PPC tools, visit www.optmyzr.com. | |
******************************************/ | |
function main() { | |
// Replace with your OpenAI API key | |
var OPENAI_API_KEY = 'ENTER_KEY_HERE'; | |
// Email address to send the blog post ideas to | |
var EMAIL_RECIPIENT = 'you@you.com'; | |
// **Variables for Thresholds** | |
var MIN_IMPRESSIONS = 50; // Set your desired minimum number of impressions here | |
var IMPRESSIONS_MULTIPLIER = 2; // The factor by which impressions should have increased | |
// **Variable for GPT Prompt** | |
var GPT_PROMPT_TEMPLATE = 'Generate an engaging and SEO-friendly blog post idea based on the following search term: "{searchTerm}".'; | |
// Get date strings for the last 7 days and the previous 7 days | |
var today = new Date(); | |
var timeZone = AdsApp.currentAccount().getTimeZone(); | |
// Helper function to format dates as 'yyyyMMdd' | |
function formatDate(date) { | |
return Utilities.formatDate(date, timeZone, 'yyyyMMdd'); | |
} | |
// Calculate date ranges | |
var last7DaysEndDate = new Date(today.getTime() - 1 * 24 * 60 * 60 * 1000); // Yesterday | |
var last7DaysStartDate = new Date(today.getTime() - 7 * 24 * 60 * 60 * 1000); // 7 days ago | |
var previous7DaysEndDate = new Date(today.getTime() - 8 * 24 * 60 * 60 * 1000); // 8 days ago | |
var previous7DaysStartDate = new Date(today.getTime() - 14 * 24 * 60 * 60 * 1000); // 14 days ago | |
var last7DaysStartDateStr = formatDate(last7DaysStartDate); | |
var last7DaysEndDateStr = formatDate(last7DaysEndDate); | |
var previous7DaysStartDateStr = formatDate(previous7DaysStartDate); | |
var previous7DaysEndDateStr = formatDate(previous7DaysEndDate); | |
Logger.log('Last 7 Days Date Range: ' + last7DaysStartDateStr + ' to ' + last7DaysEndDateStr); | |
Logger.log('Previous 7 Days Date Range: ' + previous7DaysStartDateStr + ' to ' + previous7DaysEndDateStr); | |
// Function to retrieve search terms data within a date range | |
function getSearchTermsData(startDateStr, endDateStr) { | |
var query = 'SELECT Query, Impressions ' + | |
'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' + | |
'WHERE Impressions > 0 ' + | |
'DURING ' + startDateStr + ',' + endDateStr; | |
Logger.log('Running report with query: ' + query); | |
var report = AdsApp.report(query); | |
var rows = report.rows(); | |
var data = {}; | |
var count = 0; | |
while (rows.hasNext()) { | |
var row = rows.next(); | |
var queryText = row['Query']; | |
var impressions = parseInt(row['Impressions']); | |
if (data[queryText]) { | |
data[queryText] += impressions; | |
} else { | |
data[queryText] = impressions; | |
} | |
count++; | |
} | |
Logger.log('Retrieved ' + count + ' search terms for date range ' + startDateStr + ' to ' + endDateStr); | |
return data; | |
} | |
// Retrieve data for both date ranges | |
var last7DaysData = getSearchTermsData(last7DaysStartDateStr, last7DaysEndDateStr); | |
var previous7DaysData = getSearchTermsData(previous7DaysStartDateStr, previous7DaysEndDateStr); | |
// Identify trending search terms | |
var trendingSearchTerms = []; | |
for (var searchTerm in last7DaysData) { | |
var lastImpressions = last7DaysData[searchTerm]; | |
if (lastImpressions >= MIN_IMPRESSIONS) { | |
var previousImpressions = previous7DaysData[searchTerm] || 0; | |
// Modified condition to include terms that went from 0 to MIN_IMPRESSIONS | |
if (previousImpressions === 0 || lastImpressions >= IMPRESSIONS_MULTIPLIER * previousImpressions) { | |
trendingSearchTerms.push({ | |
term: searchTerm, | |
lastImpressions: lastImpressions, | |
previousImpressions: previousImpressions | |
}); | |
Logger.log('Trending Term Found: ' + searchTerm + ' with ' + lastImpressions + ' impressions (Previous: ' + previousImpressions + ')'); | |
} | |
} | |
} | |
if (trendingSearchTerms.length === 0) { | |
Logger.log('No trending search terms found.'); | |
} else { | |
Logger.log('Total trending search terms found: ' + trendingSearchTerms.length); | |
} | |
// Sort search terms by impressions in descending order | |
trendingSearchTerms.sort(function(a, b) { | |
return b.lastImpressions - a.lastImpressions; | |
}); | |
// Get the top 5 search terms | |
var top5Terms = trendingSearchTerms.slice(0, 5); | |
// Function to get a blog post idea from OpenAI GPT API | |
function getBlogPostIdea(searchTerm) { | |
var url = 'https://api.openai.com/v1/chat/completions'; | |
// Use the GPT prompt template | |
var prompt = GPT_PROMPT_TEMPLATE.replace('{searchTerm}', searchTerm); | |
var payload = { | |
'model': 'gpt-3.5-turbo', | |
'messages': [ | |
{'role': 'system', 'content': 'You are a creative marketing assistant.'}, | |
{'role': 'user', 'content': prompt} | |
], | |
'temperature': 0.7, | |
'max_tokens': 60 | |
}; | |
var options = { | |
'method': 'post', | |
'contentType': 'application/json', | |
'headers': { | |
'Authorization': 'Bearer ' + OPENAI_API_KEY | |
}, | |
'payload': JSON.stringify(payload), | |
'muteHttpExceptions': true | |
}; | |
var response = UrlFetchApp.fetch(url, options); | |
var result = JSON.parse(response.getContentText()); | |
if (result.error) { | |
throw new Error('OpenAI API Error: ' + result.error.message); | |
} | |
var blogIdea = result.choices[0].message.content.trim(); | |
return blogIdea; | |
} | |
// Get blog post ideas for each top search term | |
var blogIdeas = []; | |
for (var i = 0; i < top5Terms.length; i++) { | |
var term = top5Terms[i].term; | |
try { | |
Logger.log('Generating blog idea for term: ' + term); | |
var blogIdea = getBlogPostIdea(term); | |
blogIdeas.push({ | |
term: term, | |
idea: blogIdea | |
}); | |
} catch (e) { | |
Logger.log('Error getting blog idea for term "' + term + '": ' + e); | |
} | |
} | |
// Check if there are any blog ideas generated | |
var emailBody = ''; | |
if (blogIdeas.length === 0) { | |
Logger.log('No blog ideas were generated.'); | |
emailBody = 'No trending search terms were found for the specified date ranges.'; | |
} else { | |
// Compose the email body | |
emailBody = 'Here are some blog post ideas based on trending search terms:\n\n'; | |
for (var i = 0; i < blogIdeas.length; i++) { | |
emailBody += (i + 1) + '. Search Term: "' + blogIdeas[i].term + '"\n'; | |
emailBody += ' Blog Post Idea: ' + blogIdeas[i].idea + '\n\n'; | |
} | |
} | |
// Send the email | |
MailApp.sendEmail(EMAIL_RECIPIENT, 'Trending Blog Post Ideas', emailBody); | |
Logger.log('Email sent to ' + EMAIL_RECIPIENT); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment