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.

Chat with SearchBot

google-adwords-bigA9-1920

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:

Screen Shot 2015-05-01 at 16.44.53

Then the script would say:

Screen Shot 2015-05-01 at 16.44.08

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:

revcost image

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.");
}


Opinions expressed in this article are those of the guest author and not necessarily Search Engine Land. Staff authors are listed here.


About the author

Daniel Gilbert
Contributor
Daniel Gilbert is the CEO at Brainlabs, the best paid media agency in the world (self-declared). He has started and invested in a number of big data and technology startups since leaving Google in 2010.

Get the must-read newsletter for search marketers.