Using the Anthropic API for Bulk Search Query and Negative Keyword Analysis: Try the Script Yourself
In my last blog post, OpenMoves outlined a simple and novel Google Ads script for Google Responsive Search Ads analysis. We created this script using Anthropic’s Claude 3.5 model, which is great at Google Ads scripting. Although this RSA script was written with Claude, it didn’t actually use Claude’s API. We wanted to try to make some Google Ads software that was both written by Claude, and actually used the Claude API.
Thus we came up with the following use case: a software tool that reviews a Google Sheet of search queries, looks through each search query one by one, and tries to assess the relevance of the query to a given business. The output of this software would guide Google Ads users towards negative keyword opportunities and perhaps also new keyword targeting opportunities.
LLMs such as Claude and ChatGPT have been used for negative keyword analysis already by OpenMoves and others with some success. However, anyone who has tried this knows that the LLM interface is limited. ChatGPT won’t process thousands of queries, it will sometimes hallucinate suggestions, and so on. We wanted a tool that could reliably bulk analyze thousands of queries to give negative or inclusion suggestions.
Our latest script accomplished this, at least as a proof of concept. Here’s how it works:
- This is a Google Apps script, that runs inside of a Google Sheet
- It requires an Anthropic API key to use (easy to get, not expensive)
- It analyzes each query in Column A and outputs 2 response columns:
- A suggestion to either KEEP, REMOVE, or EXPAND the term
- A line of reasoning on why Claude thinks the query is good or bad
See the instructions and code below. This is just a simple test script, so we welcome any feedback. (Note this is to run at your own expense and risk and we suggest treating the output as a suggestion only.)
INSTRUCTIONS TO RUN THE SCRIPT
STEP 1: Make a Google Sheet and Input Queries
- Make a new Google Sheet
- Copy search queries from Google Ads interface or a report
- Clear all data aside from the query itself
- For best results have cell A1 be a title like “query”
STEP 2: Make a Google Apps Script for this Sheet
- Click Extensions -> Apps Script in the sheet you just made
- In the resulting screen, delete the base code
- Give the script any name
- Copy paste the CODE from the CODE section of this document into the window
- SAVE
- No changes are needed to the code
STEP 3: Run the script
- Go back to your Google Sheet and click Refresh
- You should see a new menu item in the sheet
- Click Set Api Key
- In the resulting screen enter the your API KEY
- After that click Set Business Information
- Enter the Business Information (up to you) and click Submit
- Click Run Script
CODE FOR GOOGLE APPS
const API_URL = ‘https://api.anthropic.com/v1/messages’;
const RATE_LIMIT_WAIT_TIME = 65000; // 65 seconds in milliseconds
let SCRIPT_RUNNING = false;
function getApiKey() {
return PropertiesService.getScriptProperties().getProperty(‘ANTHROPIC_API_KEY’);
}
function setApiKey(key) {
PropertiesService.getScriptProperties().setProperty(‘ANTHROPIC_API_KEY’, key);
}
function showBusinessInfoForm() {
const htmlOutput = HtmlService.createHtmlOutput(`
<form id=”businessInfoForm”>
<div>
<label for=”url”>Website URL:</label><br>
<input type=”text” id=”url” name=”url” required><br>
</div>
<div>
<label for=”description”>General description of the business:</label><br>
<textarea id=”description” name=”description” rows=”3″></textarea><br>
</div>
<div>
<label for=”products”>Products or services the business offers:</label><br>
<textarea id=”products” name=”products” rows=”3″></textarea><br>
</div>
<div>
<label for=”customers”>Typical customers of the business:</label><br>
<textarea id=”customers” name=”customers” rows=”3″></textarea><br>
</div>
<div>
<label for=”exclusions”>Ideas around exclusions (keywords to avoid):</label><br>
<textarea id=”exclusions” name=”exclusions” rows=”3″></textarea><br>
</div>
<div>
<label for=”locations”>Locations the business serves:</label><br>
<textarea id=”locations” name=”locations” rows=”3″></textarea><br>
</div>
<input type=”submit” value=”Submit”>
</form>
<script>
document.getElementById(‘businessInfoForm’).addEventListener(‘submit’, function(e) {
e.preventDefault();
var formData = {};
new FormData(this).forEach(function(value, key) {
formData[key] = value;
});
google.script.run.withSuccessHandler(function() {
google.script.host.close();
}).processFormData(formData);
});
</script>
`)
.setWidth(400)
.setHeight(600);
SpreadsheetApp.getUi().showModalDialog(htmlOutput, ‘Enter Business Information’);
}
function processFormData(formData) {
PropertiesService.getScriptProperties().setProperties(formData);
SpreadsheetApp.getUi().alert(‘Business information has been saved. You can now run the script.’);
}
function getStoredBusinessInfo() {
const props = PropertiesService.getScriptProperties().getProperties();
return {
url: props.url,
description: props.description,
products: props.products,
customers: props.customers,
exclusions: props.exclusions,
locations: props.locations
};
}
function analyzeWebsite(businessInfo) {
const prompt = `You are a Google Ads optimization expert. Your mission is to thoroughly analyze the website at ${businessInfo.url} and the provided business information. Here’s the additional context:
1. Business Description: ${businessInfo.description}
2. Products/Services: ${businessInfo.products}
3. Typical Customers: ${businessInfo.customers}
4. Exclusions to Consider: ${businessInfo.exclusions}
5. Locations Served: ${businessInfo.locations}
Please follow these steps:
1. Visit the main URL provided and analyze its content.
2. Consider the additional business information provided.
3. Based on all this information, provide a concise analysis including:
a) Business Overview
b) Key Products/Services
c) Target Audience
d) Relevant Keyword Themes
e) Potentially Irrelevant Keywords to Avoid
f) Geographical Considerations
Keep your response under 2000 characters.`;
return makeApiRequest(prompt);
}
function scoreQuery(websiteAnalysis, query) {
const prompt = `As a Google Ads optimization expert, evaluate the following search query for a Google Ads campaign:
“${query}”
Based on your previous analysis of the website and business:
${websiteAnalysis}
Determine if this query should be:
1. REMOVE: Should be removed using negative keywords.
2. KEEP: Relevant and should be kept in the account.
3. EXPAND: Highly relevant and should be expanded upon.
Consider:
– Likelihood of conversion
– Efficient use of budget
– Relevance to products, services, or target audience
– Benefit of paying for clicks on this query
– Geographical relevance
Provide your decision in this format:
DECISION: [REMOVE/KEEP/EXPAND]
REASON: [A brief, one-sentence explanation of your decision]
Keep your entire response under 500 characters.`;
const response = makeApiRequest(prompt).trim();
const [decision, reason] = response.split(‘\n’);
const score = decision.split(‘: ‘)[1];
const explanation = reason.split(‘: ‘)[1];
if (![‘REMOVE’, ‘KEEP’, ‘EXPAND’].includes(score)) {
throw new Error(`Invalid score received: ${score}`);
}
return { score, explanation };
}
function makeApiRequest(prompt, retries = 3) {
const payload = {
‘model’: ‘claude-3-sonnet-20240229’,
‘max_tokens’: 2000,
‘messages’: [
{
‘role’: ‘user’,
‘content’: prompt
}
]
};
const options = {
‘method’: ‘post’,
‘headers’: {
‘Content-Type’: ‘application/json’,
‘x-api-key’: getApiKey(),
‘anthropic-version’: ‘2023-06-01’
},
‘payload’: JSON.stringify(payload),
‘muteHttpExceptions’: true
};
for (let i = 0; i < retries; i++) {
const response = UrlFetchApp.fetch(API_URL, options);
if (response.getResponseCode() === 200) {
const jsonResponse = JSON.parse(response.getContentText());
return jsonResponse.content[0].text;
} else if (response.getResponseCode() === 429) {
Logger.log(`Rate limit hit. Waiting for ${RATE_LIMIT_WAIT_TIME/1000} seconds before retrying…`);
Utilities.sleep(RATE_LIMIT_WAIT_TIME);
} else {
throw new Error(`API request failed: ${response.getContentText()}`);
}
}
throw new Error(`Failed after ${retries} retries`);
}
function runScript() {
SCRIPT_RUNNING = true;
const businessInfo = getStoredBusinessInfo();
const sheet = SpreadsheetApp.getActiveSheet();
const data = sheet.getDataRange().getValues();
const websiteAnalysis = analyzeWebsite(businessInfo);
// Output website analysis
sheet.getRange(1, 4).setValue(“Website and Business Analysis”);
sheet.getRange(1, 4).setValue(websiteAnalysis);
for (let i = 1; i < data.length && SCRIPT_RUNNING; i++) {
const query = data[i][0];
if (query) {
try {
const { score, explanation } = scoreQuery(websiteAnalysis, query);
sheet.getRange(i + 1, 2).setValue(score);
sheet.getRange(i + 1, 3).setValue(explanation);
sheet.getRange(1, 5).setValue(`Processed ${i} out of ${data.length – 1} queries`);
SpreadsheetApp.flush();
} catch (error) {
Logger.log(`Error processing query “${query}”: ${error.message}`);
sheet.getRange(i + 1, 2).setValue(“Error”);
sheet.getRange(i + 1, 3).setValue(error.message);
}
}
if (!SCRIPT_RUNNING) {
sheet.getRange(1, 5).setValue(“Script stopped by user”);
return;
}
}
sheet.getRange(1, 5).clearContent();
SCRIPT_RUNNING = false;
}
function stopScript() {
SCRIPT_RUNNING = false;
SpreadsheetApp.getUi().alert(‘Script has been stopped.’);
}
function onOpen() {
SpreadsheetApp.getUi()
.createMenu(‘Google Ads Analysis’)
.addItem(‘Set API Key’, ‘promptForApiKey’)
.addItem(‘Set Business Information’, ‘showBusinessInfoForm’)
.addItem(‘Run Script’, ‘runScript’)
.addItem(‘Stop Script’, ‘stopScript’)
.addToUi();
}
function promptForApiKey() {
const ui = SpreadsheetApp.getUi();
const result = ui.prompt(
‘Enter Anthropic API Key’,
‘Please enter your Anthropic API key:’,
ui.ButtonSet.OK_CANCEL
);
if (result.getSelectedButton() == ui.Button.OK) {
setApiKey(result.getResponseText());
ui.alert(‘API key has been set.’);
} else {
ui.alert(‘API key was not set.’);
}
}
