Can you imagine a world without VLOOKUP? It’s a lifesaver helping us save hours of work & bringing down the data mapping work hours to a considerable extent.
But let’s face it, VLOOKUP isn’t enough. There are cases where VLOOKUP isn’t going to save the day.
As SEOs, we have to conduct mapping of various kinds. I am sharing some examples below.
- Target Page H1 to GSC Highest Impression Query Mapping
- Target Page URL Slug to Primary Keyword Mapping
These are just two basic examples but there are plenty of other cases where we need to do the mapping.
For the 1st example, let’s assume that you have a list of URLs that are basically Glossary information pages; the heading 1 text is a short tail keyword now you have mapped GSC’s highest impression/click gainer keyword next to it. But to understand the relevancy you have two options
- VLOOKUP & get the exact matches this is where the vote of confidence will be highest but let’s face it; it’s not going to happen for every URL. There will be outliers wherein H1 text exact match won’t have any search volume but the URL would rank for relevant queries.
- Get an Approximate match % to understand the query relevance to the H1 text so that you can confidently assume the query is a target keyword.
Now on Google Sheets, you don’t get the FuzzyMatch formula by default.
This is why I built this AppScript to generate this formula that can get me the match %
Below is the code that you need to add to your Google Sheets > Extension > AppScript > Save it > Run it
function fuzzyMatchScore(value1, value2) {
// Check if either value is undefined
if (typeof value1 === 'undefined' || typeof value2 === 'undefined') {
return 'Error: Both values must be defined';
}
// Convert values to lowercase for case-insensitive comparison
var str1 = value1.toLowerCase();
var str2 = value2.toLowerCase();
// Calculate Levenshtein distance for fuzzy matching
var matrix = [];
var i, j;
for (i = 0; i <= str1.length; i++) {
matrix[i] = [i];
}
for (j = 0; j <= str2.length; j++) {
matrix[0][j] = j;
}
for (i = 1; i <= str1.length; i++) {
for (j = 1; j <= str2.length; j++) {
var cost = (str1.charAt(i - 1) == str2.charAt(j - 1)) ? 0 : 1;
matrix[i][j] = Math.min(
matrix[i - 1][j] + 1,
matrix[i][j - 1] + 1,
matrix[i - 1][j - 1] + cost
);
}
}
// Calculate fuzzy match score
var maxLen = Math.max(str1.length, str2.length);
var similarity = 1 - (matrix[str1.length][str2.length] / maxLen);
return similarity;
}
Now we will learn how this formula works to save the day & reduce your workload.
In the above screenshot, you can see how easily we get the FuzzMatch Score in %
So let’s assume that your list had 500 Keywords, 250 of which you matched via VLOOKUP Exact Match and now with the FuzzyMatchScore formula you matched another 100 Keywords.
In the absence of Fuzzy Match, you could have had to manually map 250 Keywords but now you may have to map only 100 and this is how FuzzyMatch saves the day.
When the percentage is at its highest that is where you can assume the query relevance to be the highest.
Utilizing hacks like these is what makes our process more robust & we become more productive as a result of it.
Like what you read? You might want to read this blog that explains how to leverage GPT to build QUERY Formula which acts like a lite version of SQL
Kunjal Chawhan founder of Decode Digital Market, a Digital Marketer by profession, and a Digital Marketing Niche Blogger by passion, here to share my knowledge