This script is part of the Keywords in Sheets Free Add-On.
With this script you can input a range of keywords or a range of Google Search Console query data and the formula will group the data by the amount of words you specify 🚀
Check out the GIF to see it in action 🎥
How to add the Script to Google Sheets
1. Copy the script below:
/**
* Group Google Search Console data or a range of keywords by words
* @param {A2:E} range - your range
* @param {1} numWords - the numbers of words to group by
* @param {"words"} format - the format of your range ("gsc" or "words")
* @param {"clicks"} sort - the column to sort the data ("clicks","impressions","ctr","position")
* @customfunction
*/
function groupbywords(range, numWords, format, sort) {
// if the range is not an array, return error. If the range is empty, return error.
if (!Array.isArray(range) || range.length === 0 || !range) {
return "Please enter a valid range";
}
// if numWords is not defined, set it to 1
numWords = numWords || 1;
// if sort is not defined, set it to "clicks"
sort = sort || "clicks";
// remove header row
range = range.slice(1);
// if format is not defined or range is a single column array, set it to "words"
format = range[0].length === 1 ? "words" : format || "words";
// english stopwords from advertools https://advertools.readthedocs.io/en/master/advertools.stopwords.html
const stopWords = ["a", "about", "above", "across", "after", "afterwards", "again", "against",
"all", "almost", "alone", "along", "already", "also", "although", "always",
"am", "among", "amongst", "amount", "an", "and", "another", "any",
"anyhow", "anyone", "anything", "anyway", "anywhere", "are", "around",
"as", "at", "back", "be", "became", "because", "become", "becomes",
"becoming", "been", "before", "beforehand", "behind", "being", "below",
"beside", "besides", "between", "beyond", "both", "bottom", "but", "by",
"ca", "call", "can", "cannot", "could", "did", "do", "does", "doing",
"done", "down", "due", "during", "each", "eight", "either", "eleven",
"else", "elsewhere", "empty", "enough", "even", "ever", "every",
"everyone", "everything", "everywhere", "except", "few", "fifteen",
"fifty", "first", "five", "for", "former", "formerly", "forty", "four",
"from", "front", "full", "further", "get", "give", "go", "had", "has",
"have", "he", "hence", "her", "here", "hereafter", "hereby", "herein",
"hereupon", "hers", "herself", "him", "himself", "his", "how", "however",
"hundred", "i", "if", "in", "indeed", "into", "is", "it", "its", "itself",
"just", "keep", "last", "latter", "latterly", "least", "less", "made",
"make", "many", "may", "me", "meanwhile", "might", "mine", "more",
"moreover", "most", "mostly", "move", "much", "must", "my", "myself",
"name", "namely", "neither", "never", "nevertheless", "next", "nine", "no",
"nobody", "none", "noone", "nor", "not", "nothing", "now", "nowhere", "of",
"off", "often", "on", "once", "one", "only", "onto", "or", "other",
"others", "otherwise", "our", "ours", "ourselves", "out", "over", "own",
"part", "per", "perhaps", "please", "put", "quite", "rather", "re",
"really", "regarding", "same", "say", "see", "seem", "seemed", "seeming",
"seems", "serious", "several", "she", "should", "show", "side", "since",
"six", "sixty", "so", "some", "somehow", "someone", "something",
"sometime", "sometimes", "somewhere", "still", "such", "take", "ten",
"than", "that", "the", "their", "them", "themselves", "then", "thence",
"there", "thereafter", "thereby", "therefore", "therein", "thereupon",
"these", "they", "third", "this", "those", "though", "three", "through",
"throughout", "thru", "thus", "to", "together", "too", "top", "toward",
"towards", "twelve", "twenty", "two", "under", "unless", "until", "up",
"upon", "us", "used", "using", "various", "very", "via", "was", "we",
"well", "were", "what", "whatever", "when", "whence", "whenever", "where",
"whereafter", "whereas", "whereby", "wherein", "whereupon", "wherever",
"whether", "which", "while", "whither", "who", "whoever", "whole", "whom",
"whose", "why", "will", "with", "within", "without", "would", "yet", "you",
"your", "yours", "yourself", "yourselves"
];
// remove empty values, non-strings and remove stopwords
range = range.filter(function(d) {
return d[0] && typeof d[0] === "string" && stopWords.indexOf(d[0].toLowerCase()) === -1
});
// get first column from data if not a single range
let firstColumn = range[0].length > 1 ? range.map(function(d) {
return d[0]
}) : range;
// get n words from each row in first column
const words = firstColumn.map(function(d) {
return d.split(" ").filter(function(d) {
return d.length > 0
}).slice(0, numWords).join(" ")
}).filter(function(d, i, self) {
return self.indexOf(d) == i && d.split(" ").length == numWords;
});
// aggregate data by n words and push to newData array
var newData = [];
for (var i = 0; i < words.length; i++) {
var word = words[i];
// if word is 1 character or empty, skip
if (word.length == 1 || word == "") {
continue;
}
// filter range by word
let rows = range.filter(function(d) {
return d[0].indexOf(word) > -1
});
// if format is 'words' push to newData
if (format === "words") {
newData.push([word, rows.length]);
} else {
// else aggregate by column and push to newData
var row = [word];
for (var j = 1; j < range[0].length; j++) {
var sum = 0;
for (var k = 0; k < rows.length; k++) {
// if the row is empty, replace with 0
if (rows[k][j] === "") {
rows[k][j] = 0;
}
// sum rows 2-3
if (j == 1 || j == 2) {
sum += rows[k][j];
} else {
// average rows 4-5
sum += rows[k][j] / rows.length;
}
}
row.push(sum);
}
newData.push(row);
}
}
// if the format is words, sort by the count of words
if (format === "words") {
newData.sort(function(a, b) {
return b[1] - a[1]
});
// add header row
newData.unshift(["Words", "Count"]);
} else {
// else sort by specified gsc column
switch (sort) {
case "clicks":
newData.sort(function(a, b) {
return b[1] - a[1]
});
break;
case "impressions":
newData.sort(function(a, b) {
return b[2] - a[2]
});
break;
case "ctr":
newData.sort(function(a, b) {
return b[3] - a[3]
});
break;
case "position":
newData.sort(function(a, b) {
return a[4] - b[4]
});
break;
}
// add header row
newData.unshift(["Words", "Clicks", "Impressions", "CTR", "Avg. Position"]);
}
return newData;
}2. Head over to Google Sheets
Or if you’re really smart, create a new sheet by going to: https://sheets.new
Select Appscript from the Extensions menu.
Paste the script and save it.
3. Add the formula to any cell in your sheet
=groupbywords(A:E,2,"gsc","clicks")Replace A:E with a range of your own Google Search Console Query data.
Replace 2 with the number of words you want to group by.
Replace “gsc” with “words” if you want to format by word frequency.
Replace “clicks” with another column from your Google Search Console data you want to sort by. The sort options are:
- clicks (DESC)
- impressions (DESC)
- ctr (DESC)
- position (ASC)
Group Ahrefs exports and related keywords in Keywords in Sheets PRO
We’ve added some more formatting options if you’re a Keywords in Sheets PRO customer. You can group Ahrefs Keyword Explorer & Site Explorer -> Organic keywords exports.
Ahrefs Keyword Explorer
=groupbywords(A:E,2,"ahrefs keywords","volume")
Ahrefs Site Explorer
=groupbywords(A:E,2,"ahrefs site","volume")
Related Keywords
=groupbywords(A:E,2,"related keywords","volume")
Make to sure select the full range starting from the keywords column (Ahrefs keyword explorer includes an ID column, don’t select it).
We’ll also be adding the group by words function across all our Keywords in Sheets functions very soon.
Stopwords is currently only in english. We’ll be adding stopwords in all other languages very soon too.
Thanks for stopping by 👋
I’m Andrew Charlton, the Google Sheets nerd behind Keywords in Sheets. 🤓
Questions? Get in touch with me on social or comment below 👇