Cluster 1,000 keywords, for free

Powered by MeaningCloud, with this script you can input a range of data (1000 cells max) and it will automatically cluster keywords into relevant groups (20k request per month). 🚀

Check out the GIF to see it in action 🎥

---

How to add the Script to Google Sheets

1. Sign up to RapidAPI

Sign up to RapidAPI, an API marketplace, at https://rapidapi.com/auth/sign-up (you can even use your Google Account).

---

2. Subscribe to the Text Cluster API

Once you’re all set up on RapidAPI head to https://rapidapi.com/MeaningCloud/api/text-clustering/pricing and subscribe to the Basic Plan (Free). With this plan you get 20,000 requests per month for free, but every request thereafter costs $0.01. I’ve also added a cache (with the maximum expiration time set at 6 hours) to help with unnecessary requests.

If you are going to be using this API frequently, please remember to check your usage.

---

3. Get your API Key

The good thing about RapidAPI is that your API key can be used universally across any of the APIs in their marketplace. To copy your API key, go to https://rapidapi.com/developer/dashboard and head down to ‘My Apps’ and then ‘Security’. From there, you can copy your Application Key.

---

4. Copy the script below:

/**
 * Automatically assign keywords into clusters. 
 *
 * @param {"A2:A1000"} range - input the range of your keywords.  
 * @param {"en"} language - [OPTIONAL] input the target language ('en', 'es', 'it', 'fr', 'pt'). Default set to 'en'. 
 * @customfunction
 */


function cluster(range, language) {

	const sheet = SpreadsheetApp.getActiveSpreadsheet()

	const keywords = sheet.getRange(range).getValues();
	const arrLength = keywords.length - 1;

	// add keywords as line items for request
	const txt = keywords.toString().replace(/,/g, '\n');

	// create a cache key with first & last keywords
	const key = `${keywords[0]}&${keywords[arrLength]}&${arrLength}`;
	const cache = CacheService.getScriptCache();

	let json = cache.get(key);

	// if entry is not in the cache, request clusters  
	if (!json) {

		try {
			// RapidAPI Key https://rapidapi.com/MeaningCloud/api/text-clustering/
			const apiKey = '';
			const lang = language || 'en';

			const requestBody = {
				'txt': txt,
				'lang': lang
			};

			const options = {
				'method': 'POST',
				'contentType': 'application/x-www-form-urlencoded',
				'headers': {
					'x-rapidapi-key': apiKey,
					'x-rapidapi-host': 'meaningcloud-text-clustering-v1.p.rapidapi.com'
				},
				'payload': requestBody
			};

			const response = UrlFetchApp.fetch('https://meaningcloud-text-clustering-v1.p.rapidapi.com/clustering-1.1', options);
			const result = response.getContentText();

			// add response to cache
			cache.put(key, result, 21600);

			json = result;

		} catch (e) {
			return 'Request to API failed. Try clustering less keywords';
		}
	}

	const list = JSON.parse(json).cluster_list;


	// loop through response and create return array with cluster groups and keywords
	let rows = [],
		data,
		title;
	for (i = 0; i < list.length; i++) {
		data = list[i];
		title = data.title.toLowerCase();
		cluster = Object.values(data.document_list);

		for (j = 0; j < cluster.length; j++) {
			rows.push([title, cluster[j]]);
		}
	}

	rows.unshift(["Cluster Group", "Keywords"]);

	return rows;

}

---

5. Head over to Google Sheets

Or if you’re really smart, create a new sheet by going to: https://sheets.new

Select Script editor from the Tools menu.

Paste the script and replace line 31 with your API key:

  const apiKey = 'addapikeyhere';

Save it.

---

6. Add the formula to any cell in your sheet

=cluster("A2:A200", "en")

You can replace “A2:A200” with any keyword range in a single column. Anything above 1000 cells tends to timeout with this custom function, though I have ran it with 2000 cells successfully. For larger sites, it will be more effective to cluster keywords using this function on a page-by-page basis.

You can also specify a language (the default is set to the ‘en’). The languages supported by MeaningCloud are ‘en’, ‘es’, ‘it’, ‘fr’, and ‘pt’.

*keywords can be part of multiple cluster groups.

---

How does the clustering work?

Here’s the MeaningCloud explanation:

“It groups documents together not by applying a purely textual similitude, but according to their relevance with regard to the subjects present in the collection, and automatically assigns to each cluster a title or name that represents its prevailing subject. Also, it internally employs lemmatization technologies which enable to take into account all the variants of a term, and it can be configured to consider stopwords and other linguistic aspects.”

You can find out more information by visiting their website.

---

Are there better ways to cluster keywords?

In short, yes. This function provides a great, quick (and free) way to automatically group keywords but there are professional clustering tools that cluster keywords based on the similarity of their search results. That means if search results from keyword a match keyword b, they’re grouped together.

With this approach you’re seeing clusters through the lens of Google (sort of) and that makes the data more meaningful. Here’s a list of some fantastic tools on the market if you want to explore this further:

• Keyword Insights
• SE Ranking
• Serpstat
• Cluster AI
• Topvisor

---

More Scripts

• ‘Award-winning’ digital PR headlines with GPT-3
• Append your keywords with all locations, from any country
• Automatically generate title tags & meta descriptions with GPT-3
• Bulk check indexing and coverage reports in Google Sheets