Here’s An AdWords Script To Mine Your Search Query Reports
Former Googler Daniel Gilbert shares another script from the Brainlabs team that will look at your search query data to reveal performance insights.
Paid search revolves around the search query. Most of the time, we think about keywords, as we have direct control over them. But the search query is what the searcher actually wants to find — and this can be different than what you had in mind (especially if broad match or ambiguous words are in play).
That means the search query report is a vital resource for your AdWords account, showing you where you could expand your keywords and where you should exclude irrelevant searches. And if something is important, that means we want to make it easier to work with!
This script takes all the words in your search query report and works out the performance of a word by adding up the performance of all the queries in a campaign that contain that word. It ignores paused campaigns, paused ad groups and search queries already excluded by negative keywords.
For example, if you had the search queries:
Then the script would say:
This gives you an idea of how much money you could have saved if you had that word as a campaign-level broad match negative, and if you’d have missed out on any conversions. In this example, you could add “cheap” as a negative keyword, or look more closely into the highest cost/low conversion words (like “sale”) to see if they should be negatives (or if phrases containing them could be negatives). Of course, you have to use your judgement: “for” hasn’t performed well, but it’s a generic word so you may not want to have it as a negative keyword on its own.
The script also analyses the performance of search queries by the number of words they contain. (This isn’t an exact measure of how long tail a search is — the phrase “how i met your mother dvds” is no more long tail than “friends dvds,” despite the four extra words — but it is generally a good approximation and is much easier to measure.)
If you find longer searches perform better for you, you may want to focus on your long-tail strategy. At Brainlabs (my employer) we’ve found this report to be particularly useful for Shopping campaigns, where longer queries tend to perform better — probably because they are more specific. Here’s an example which allowed us to revamp our Shopping campaign to focus on the queries driving more profit:
To use the script:
Go to Google Drive and make a blank spreadsheet. Make a note of its URL.
Then, go to your AdWords account, into “Bulk operations” and then into “Scripts.” Make a new Script and paste the code in.
At the start of the code, there are a few variables to set: Put your spreadsheet’s URL into “spreadsheetUrl” and your currency symbol into “currencySymbol.” Change the “startDate” and “endDate” according to what date range you want to look at; if you have low traffic, you may need to look at a longer period. And “campaignNameContains” can be used to look at particular campaigns (like just campaigns with “Shopping” in their name), but you can leave it blank.
If you want to, you can change the thresholds — the default is for the script to not report on words that have fewer than 10 impressions, but you could change these to only look at high impression or high cost words.
Then, save and run the script (giving authorization when asked).
The results should appear in new sheets (“Campaign Word Analysis,” “Total Word Analysis” and “Word Count Analysis”) in the Google Doc.
For the campaign and total word analysis, you can use the Google Spreadsheet filter (or export into Excel and use Excel’s filter) to find the words that are performing badly or that look out of place. (Remember, you can cross-reference the search query report in AdWords if you’re confused about why a particular word performs so badly — it may be that the problem is a phrase containing the word, rather than the word itself.)
For the word count analysis you can see what number of words performs best. If you want to add conditional formatting to the cost/conv, to make the variation easier to see, then click the “Format” menu, then ‘Conditional formatting…’ and in the sidebar select ‘Colour scale’.
Note: If you’ve got a big account, the script might time out before it finishes — if that happens, try using “campaignNameContains” to only look at a few campaigns at a time.
/**
*
* Search Query Mining Tool
*
* This script calculates the contribution of each word found in the search query report
* and outputs a report into a Google Doc spreadsheet.
*
* Version: 1.0
* Google Apps Script maintained on brainlabsdigital.com
*
**/
function main() {
//////////////////////////////////////////////////////////////////////////////
// Options
var startDate = "2015-04-01";
var endDate = "2015-04-30";
// The start and end date of the date range for your search query data
// Format is yyyy-mm-dd
var currencySymbol = "£";
// The currency symbol used for formatting. For example "£", "$" or "€".
var campaignNameContains = "";
// Use this if you only want to look at some campaigns
// such as campaigns with names containing 'Brand' or 'Shopping'.
// Leave as "" if not wanted.
var spreadsheetUrl = "https://docs.google.com/YOUR-SPREADSHEET-URL-HERE";
// The URL of the Google Doc the results will be put into.
//////////////////////////////////////////////////////////////////////////////
// Thresholds
var impressionThreshold = 10;
var clickThreshold = 0;
var costThreshold = 0;
var conversionThreshold = 0;
// Words will be ignored if their statistics are lower than any of these thresholds
//////////////////////////////////////////////////////////////////////////////
// Find the negative keywords
var negativesByGroup = [];
var negativesByCampaign = [];
var sharedSetData = [];
var sharedSetNames = [];
var sharedSetCampaigns = [];
var dateRange = startDate.replace(/-/g, "") + "," + endDate.replace(/-/g, "");
var activeCampaignIds = [];
// Gather ad group level negative keywords
var keywordReport = AdWordsApp.report(
"SELECT CampaignId, AdGroupId, KeywordText, KeywordMatchType " +
"FROM KEYWORDS_PERFORMANCE_REPORT " +
"WHERE CampaignStatus = ENABLED AND AdGroupStatus = ENABLED AND Status = ENABLED AND IsNegative = TRUE " +
"AND CampaignName CONTAINS_IGNORE_CASE '" + campaignNameContains + "' " +
"DURING " + dateRange);
var keywordRows = keywordReport.rows();
while (keywordRows.hasNext()) {
var keywordRow = keywordRows.next();
if (negativesByGroup[keywordRow["AdGroupId"]] == undefined) {
negativesByGroup[keywordRow["AdGroupId"]] =
[[keywordRow["KeywordText"].toLowerCase(),keywordRow["KeywordMatchType"].toLowerCase()]];
} else {
negativesByGroup[keywordRow["AdGroupId"]].push([keywordRow["KeywordText"].toLowerCase(),keywordRow["KeywordMatchType"].toLowerCase()]);
}
if (activeCampaignIds.indexOf(keywordRow["CampaignId"]) < 0) {
activeCampaignIds.push(keywordRow["CampaignId"]);
}
}//end while
// Gather campaign level negative keywords
var campaignNegReport = AdWordsApp.report(
"SELECT CampaignId, KeywordText, KeywordMatchType " +
"FROM CAMPAIGN_NEGATIVE_KEYWORDS_PERFORMANCE_REPORT " +
"WHERE IsNegative = TRUE " +
"AND CampaignId IN [" + activeCampaignIds.join(",") + "]"
);
var campaignNegativeRows = campaignNegReport.rows();
while (campaignNegativeRows.hasNext()) {
var campaignNegativeRow = campaignNegativeRows.next();
if (negativesByCampaign[campaignNegativeRow["CampaignId"]] == undefined) {
negativesByCampaign[campaignNegativeRow["CampaignId"]] = [[campaignNegativeRow["KeywordText"].toLowerCase(),campaignNegativeRow["KeywordMatchType"].toLowerCase()]];
} else {
negativesByCampaign[campaignNegativeRow["CampaignId"]].push([campaignNegativeRow["KeywordText"].toLowerCase(),campaignNegativeRow["KeywordMatchType"].toLowerCase()]);
}
}//end while
// Find which campaigns use shared negative keyword sets
var campaignSharedReport = AdWordsApp.report(
"SELECT CampaignName, CampaignId, SharedSetName, SharedSetType, Status " +
"FROM CAMPAIGN_SHARED_SET_REPORT " +
"WHERE SharedSetType = NEGATIVE_KEYWORDS " +
"AND CampaignName CONTAINS_IGNORE_CASE '" + campaignNameContains + "'");
var campaignSharedRows = campaignSharedReport.rows();
while (campaignSharedRows.hasNext()) {
var campaignSharedRow = campaignSharedRows.next();
if (sharedSetCampaigns[campaignSharedRow["SharedSetName"]] == undefined) {
sharedSetCampaigns[campaignSharedRow["SharedSetName"]] = [campaignSharedRow["CampaignId"]];
} else {
sharedSetCampaigns[campaignSharedRow["SharedSetName"]].push(campaignSharedRow["CampaignId"]);
}
}//end while
// Map the shared sets' IDs (used in the criteria report below)
// to their names (used in the campaign report above)
var sharedSetReport = AdWordsApp.report(
"SELECT Name, SharedSetId, MemberCount, ReferenceCount, Type " +
"FROM SHARED_SET_REPORT " +
"WHERE ReferenceCount > 0 AND Type = NEGATIVE_KEYWORDS ");
var sharedSetRows = sharedSetReport.rows();
while (sharedSetRows.hasNext()) {
var sharedSetRow = sharedSetRows.next();
sharedSetNames[sharedSetRow["SharedSetId"]] = sharedSetRow["Name"];
}//end while
// Collect the negative keyword text from the sets,
// and record it as a campaign level negative in the campaigns that use the set
var sharedSetReport = AdWordsApp.report(
"SELECT SharedSetId, KeywordMatchType, KeywordText " +
"FROM SHARED_SET_CRITERIA_REPORT ");
var sharedSetRows = sharedSetReport.rows();
while (sharedSetRows.hasNext()) {
var sharedSetRow = sharedSetRows.next();
var setName = sharedSetNames[sharedSetRow["SharedSetId"]];
if (sharedSetCampaigns[setName] !== undefined) {
for (var i=0; i<sharedSetCampaigns[setName].length; i++) {
var campaignId = sharedSetCampaigns[setName][i];
if (negativesByCampaign[campaignId] == undefined) {
negativesByCampaign[campaignId] =
[[sharedSetRow["KeywordText"].toLowerCase(),sharedSetRow["KeywordMatchType"].toLowerCase()]];
} else {
negativesByCampaign[campaignId].push([sharedSetRow["KeywordText"].toLowerCase(),sharedSetRow["KeywordMatchType"].toLowerCase()]);
}
}
}
}//end while
Logger.log("Finished negative keyword lists.");
//////////////////////////////////////////////////////////////////////////////
// Defines the statistics to download or calculate, and their formatting
var statColumns = ["Clicks", "Impressions", "Cost", "ConvertedClicks", "ConversionValue"];
var calculatedStats = [["CTR","Clicks","Impressions"],
["CPC","Cost","Clicks"],
["Conv. Rate","ConvertedClicks","Clicks"],
["Cost / conv.","Cost","ConvertedClicks"],
["Conv. value/cost","ConversionValue","Cost"]]
var currencyFormat = currencySymbol + "#,##0.00";
var formatting = ["#,##0", "#,##0", currencyFormat, "#,##0", currencyFormat,"0.00%",currencyFormat,"0.00%",currencyFormat,"0.00%"];
//////////////////////////////////////////////////////////////////////////////
// Go through the search query report, remove searches already excluded by negatives
// record the performance of each word in each remaining query
var queryReport = AdWordsApp.report(
"SELECT CampaignName, CampaignId, AdGroupId, AdGroupName, Query, " + statColumns.join(", ") + " " +
"FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
"WHERE CampaignStatus = ENABLED AND AdGroupStatus = ENABLED " +
"AND CampaignName CONTAINS_IGNORE_CASE '" + campaignNameContains + "' " +
"DURING " + dateRange);
var campaignSearchWords = [];
var totalSearchWords = [];
var totalSearchWordsKeys = [];
var numberOfWords = [];
var queryRows = queryReport.rows();
while (queryRows.hasNext()) {
var queryRow = queryRows.next();
var searchIsExcluded = false;
// Checks if the query is excluded by an ad group level negative
if (negativesByGroup[queryRow["AdGroupId"]] !== undefined) {
for (var i=0; i<negativesByGroup[queryRow["AdGroupId"]].length; i++) {
if ( (negativesByGroup[queryRow["AdGroupId"]][i][1] == "exact" &&
queryRow["Query"] == negativesByGroup[queryRow["AdGroupId"]][i][0]) ||
(negativesByGroup[queryRow["AdGroupId"]][i][1] != "exact" &&
(" "+queryRow["Query"]+" ").indexOf(" "+negativesByGroup[queryRow["AdGroupId"]][i][0]+" ") > -1 )){
searchIsExcluded = true;
break;
}
}
}
// Checks if the query is excluded by a campaign level negative
if (!searchIsExcluded && negativesByCampaign[queryRow["CampaignId"]] !== undefined) {
for (var i=0; i<negativesByCampaign[queryRow["CampaignId"]].length; i++) {
if ( (negativesByCampaign[queryRow["CampaignId"]][i][1] == "exact" &&
queryRow["Query"] == negativesByCampaign[queryRow["CampaignId"]][i][0]) ||
(negativesByCampaign[queryRow["CampaignId"]][i][1]!= "exact" &&
(" "+queryRow["Query"]+" ").indexOf(" "+negativesByCampaign[queryRow["CampaignId"]][i][0]+" ") > -1 )){
searchIsExcluded = true;
break;
}
}
}
if (searchIsExcluded) {continue;}
// if the search is already excluded by the current negatives,
// we ignore it and go on to the next query
var currentWords = queryRow["Query"].split(" ");
var doneWords = [];
if (campaignSearchWords[queryRow["CampaignName"]] == undefined) {
campaignSearchWords[queryRow["CampaignName"]] = [];
}
var wordLength = currentWords.length;
if (wordLength > 6) {
wordLength = "7+";
}
if (numberOfWords[wordLength] == undefined) {
numberOfWords[wordLength] = [];
}
for (var i=0; i<statColumns.length; i++) {
if (numberOfWords[wordLength][statColumns[i]] > 0) {
numberOfWords[wordLength][statColumns[i]] += parseFloat(queryRow[statColumns[i]].replace(/,/g, ""));
} else {
numberOfWords[wordLength][statColumns[i]] = parseFloat(queryRow[statColumns[i]].replace(/,/g, ""));
}
}
// Splits the query into words and records the stats for each
for (var w=0;w<currentWords.length;w++) {
if (doneWords.indexOf(currentWords[w]) < 0) { //if this word hasn't been in the query yet
if (campaignSearchWords[queryRow["CampaignName"]][currentWords[w]] == undefined) {
campaignSearchWords[queryRow["CampaignName"]][currentWords[w]] = [];
}
if (totalSearchWords[currentWords[w]] == undefined) {
totalSearchWords[currentWords[w]] = [];
totalSearchWordsKeys.push(currentWords[w]);
}
for (var i=0; i<statColumns.length; i++) {
var stat = parseFloat(queryRow[statColumns[i]].replace(/,/g, ""));
if (campaignSearchWords[queryRow["CampaignName"]][currentWords[w]][statColumns[i]] > 0) {
campaignSearchWords[queryRow["CampaignName"]][currentWords[w]][statColumns[i]] += stat;
} else {
campaignSearchWords[queryRow["CampaignName"]][currentWords[w]][statColumns[i]] = stat;
}
if (totalSearchWords[currentWords[w]][statColumns[i]] > 0) {
totalSearchWords[currentWords[w]][statColumns[i]] += stat;
} else {
totalSearchWords[currentWords[w]][statColumns[i]] = stat;
}
}
doneWords.push(currentWords[w]);
}//end if
}//end for
}//end while
Logger.log("Finished analysing queries.");
//////////////////////////////////////////////////////////////////////////////
// Output the data into the spreadsheet
var campaignSearchWordsOutput = [];
var campaignSearchWordsFormat = [];
var totalSearchWordsOutput = [];
var totalSearchWordsFormat = [];
var wordLengthOutput = [];
var wordLengthFormat = [];
// Add headers
var calcStatNames = [];
for (var s=0; s<calculatedStats.length; s++) {
calcStatNames.push(calculatedStats[s][0]);
}
var statNames = statColumns.concat(calcStatNames);
campaignSearchWordsOutput.push(["Campaign","Word"].concat(statNames));
totalSearchWordsOutput.push(["Word"].concat(statNames));
wordLengthOutput.push(["Word count"].concat(statNames));
// Output the campaign level stats
for (var campaign in campaignSearchWords) {
for (var word in campaignSearchWords[campaign]) {
if (campaignSearchWords[campaign][word]["Impressions"] < impressionThreshold) {continue;}
if (campaignSearchWords[campaign][word]["Clicks"] < clickThreshold) {continue;}
if (campaignSearchWords[campaign][word]["Cost"] < costThreshold) {continue;}
if (campaignSearchWords[campaign][word]["ConvertedClicks"] < conversionThreshold) {continue;}
// skips words under the thresholds
var printline = [campaign, word];
for (var s=0; s<statColumns.length; s++) {
printline.push(campaignSearchWords[campaign][word][statColumns[s]]);
}
for (var s=0; s<calculatedStats.length; s++) {
var multiplier = calculatedStats[s][1];
var divisor = calculatedStats[s][2];
if (campaignSearchWords[campaign][word][divisor] > 0) {
printline.push(campaignSearchWords[campaign][word][multiplier] / campaignSearchWords[campaign][word][divisor]);
} else {
printline.push("-");
}
}
campaignSearchWordsOutput.push(printline);
campaignSearchWordsFormat.push(formatting);
}
} // end for
totalSearchWordsKeys.sort(function(a,b) {return totalSearchWords[b]["Cost"] - totalSearchWords[a]["Cost"];});
for (var i = 0; i<totalSearchWordsKeys.length; i++) {
var word = totalSearchWordsKeys[i];
if (totalSearchWords[word]["Impressions"] < impressionThreshold) {continue;}
if (totalSearchWords[word]["Clicks"] < clickThreshold) {continue;}
if (totalSearchWords[word]["Cost"] < costThreshold) {continue;}
if (totalSearchWords[word]["ConvertedClicks"] < conversionThreshold) {continue;}
// skips words under the thresholds
var printline = [word];
for (var s=0; s<statColumns.length; s++) {
printline.push(totalSearchWords[word][statColumns[s]]);
}
for (var s=0; s<calculatedStats.length; s++) {
var multiplier = calculatedStats[s][1];
var divisor = calculatedStats[s][2];
if (totalSearchWords[word][divisor] > 0) {
printline.push(totalSearchWords[word][multiplier] / totalSearchWords[word][divisor]);
} else {
printline.push("-");
}
}
totalSearchWordsOutput.push(printline);
totalSearchWordsFormat.push(formatting);
} // end for
for (var i = 1; i<8; i++) {
if (i < 7) {
var wordLength = i;
} else {
var wordLength = "7+";
}
var printline = [wordLength];
if (numberOfWords[wordLength] == undefined) {
printline.push([0,0,0,0,"-","-","-","-"]);
} else {
for (var s=0; s<statColumns.length; s++) {
printline.push(numberOfWords[wordLength][statColumns[s]]);
}
for (var s=0; s<calculatedStats.length; s++) {
var multiplier = calculatedStats[s][1];
var divisor = calculatedStats[s][2];
if (numberOfWords[wordLength][divisor] > 0) {
printline.push(numberOfWords[wordLength][multiplier] / numberOfWords[wordLength][divisor]);
} else {
printline.push("-");
}
}
}
wordLengthOutput.push(printline);
wordLengthFormat.push(formatting);
} // end for
// Finds available names for the new sheets
var campaignWordName = "Campaign Word Analysis";
var totalWordName = "Total Word Analysis";
var wordCountName = "Word Count Analysis";
var campaignWordSheet = SpreadsheetApp.openByUrl(spreadsheetUrl).getSheetByName(campaignWordName);
var totalWordSheet = SpreadsheetApp.openByUrl(spreadsheetUrl).getSheetByName(totalWordName);
var wordCountSheet = SpreadsheetApp.openByUrl(spreadsheetUrl).getSheetByName(wordCountName);
var i = 1;
while (campaignWordSheet != null || wordCountSheet != null || totalWordSheet != null) {
campaignWordName = "Campaign Word Analysis " + i;
totalWordName = "Total Word Analysis " + i;
wordCountName = "Word Count Analysis " + i;
campaignWordSheet = SpreadsheetApp.openByUrl(spreadsheetUrl).getSheetByName(campaignWordName);
totalWordSheet = SpreadsheetApp.openByUrl(spreadsheetUrl).getSheetByName(totalWordName);
wordCountSheet = SpreadsheetApp.openByUrl(spreadsheetUrl).getSheetByName(wordCountName);
i++;
}
campaignWordSheet = SpreadsheetApp.openByUrl(spreadsheetUrl).insertSheet(campaignWordName);
totalWordSheet = SpreadsheetApp.openByUrl(spreadsheetUrl).insertSheet(totalWordName);
wordCountSheet = SpreadsheetApp.openByUrl(spreadsheetUrl).insertSheet(wordCountName);
campaignWordSheet.getRange("R1C1").setValue("Analysis of Words in Search Query Report, By Campaign");
wordCountSheet.getRange("R1C1").setValue("Analysis of Search Query Performance by Words Count");
if (campaignNameContains == "") {
totalWordSheet.getRange("R1C1").setValue("Analysis of Words in Search Query Report, By Account");
} else {
totalWordSheet.getRange("R1C1").setValue("Analysis of Words in Search Query Report, Over All Campaigns Containing '" + campaignNameContains + "'");
}
campaignWordSheet.getRange("R2C1:R" + (campaignSearchWordsOutput.length+1) + "C" + campaignSearchWordsOutput[0].length).setValues(campaignSearchWordsOutput);
campaignWordSheet.getRange("R3C3:R" + (campaignSearchWordsOutput.length+1) + "C" + (formatting.length+2)).setNumberFormats(campaignSearchWordsFormat);
totalWordSheet.getRange("R2C1:R" + (totalSearchWordsOutput.length+1) + "C" + totalSearchWordsOutput[0].length).setValues(totalSearchWordsOutput);
totalWordSheet.getRange("R3C2:R" + (totalSearchWordsOutput.length+1) + "C" + (formatting.length+1)).setNumberFormats(totalSearchWordsFormat);
wordCountSheet.getRange("R2C1:R" + (wordLengthOutput.length+1) + "C" + wordLengthOutput[0].length).setValues(wordLengthOutput);
wordCountSheet.getRange("R3C2:R" + (wordLengthOutput.length+1) + "C" + (formatting.length+1)).setNumberFormats(wordLengthFormat);
Logger.log("Finished writing to spreadsheet.");
}
Contributing authors are invited to create content for Search Engine Land and are chosen for their expertise and contribution to the search community. Our contributors work under the oversight of the editorial staff and contributions are checked for quality and relevance to our readers. The opinions they express are their own.
Related stories
New on Search Engine Land
About the author
