- Apr 23, 2012
- 2,845
- 5,856
Hey,
So been a while since I shared something here. Nonetheless, here goes -
The Problem
A lot of us deal with large caches of keywords on a daily basis - either through scraping or Semrush or Ahrefs. As we get these keywords, there's some similarity between them. We often have keywords like
Now imagine assigning each of these keywords to a separate page on your website - it would be tantamount to keyword cannibalization. Or if you're scraping - this would be akin to a lot of wasted resource.
//
The Magic of the script
To somewhat tackle this, I had written a quick script a while ago here - however, it used recursion and was a huge pain in the ass for large sets of keywords.
And since this was a recurring issue, I decided to tackle it once and for all. This one uses Agglomerative Clustering and the
It then does three things
1. It removes the duplicate keywords that match the similarity threshold
2. Creates a new CSV file with just the unique keywords
3. Creates a new Excel file with three columns - Removed Keywords, Their Corresponding Retained Keyword, Similarity between the three
//
Example
I gave it 10,000 keywords - with everybody's favorite topics NFT - On an old Macbook Pro it took 6 minutes to get me 8597 unique keywords, removed 1402 keywords
Original Keywords I gave the script - Check here
Deduplicated Keywords the script returned - Check here
Excel File the Script generated - Check Here
//
How to use the script?
You can download the script through the below link or by clicking here - click the green "Code" button and download as Zip
Step 1: Prepare your keyword list
Create a CSV file containing your list of keywords - Name this file
Step 2: Install the required packages
Make sure you have Python 3.6 or higher installed on your system. Then, install the required packages by running the following command in your terminal or command prompt - please navigate to the directory where the script is present. If you're using something like VSCode things become easier.
The
Step 3: Run the script
Open your terminal or command prompt and navigate to the directory where the deduplication.py script is located. Run the script with the following command:
Step 4: Set the similarity threshold
During the execution of the script, you'll be prompted to enter the similarity threshold. The default value is 0.8. If the similarity between two keywords is higher than the threshold, they will be considered part of the same cluster and one of them will be removed.
I personally prefer 0.9 - but I have added the option for you to enter the threshold so you can play around with those numbers till you get the desired output.
Step 5: Specify the output file name
You'll also be prompted to enter the name of the output Excel file. The default name is keywords_output.xlsx. You can choose a different name if you prefer. Something like nft-final.xlsx
Step 6: Check the output files
Once the script finishes running, it will generate two output files in the same directory -
//
How does it work?
Google Colab Version Here
So been a while since I shared something here. Nonetheless, here goes -
The Problem
A lot of us deal with large caches of keywords on a daily basis - either through scraping or Semrush or Ahrefs. As we get these keywords, there's some similarity between them. We often have keywords like
best nft to buy, best nft to buy today, best nft's to buy right now and best nft to buy nowNow imagine assigning each of these keywords to a separate page on your website - it would be tantamount to keyword cannibalization. Or if you're scraping - this would be akin to a lot of wasted resource.
//
The Magic of the script
To somewhat tackle this, I had written a quick script a while ago here - however, it used recursion and was a huge pain in the ass for large sets of keywords.
And since this was a recurring issue, I decided to tackle it once and for all. This one uses Agglomerative Clustering and the
paraphrase-distilroberta-base-v1 and computes similarity to resolve the issue.It then does three things
1. It removes the duplicate keywords that match the similarity threshold
2. Creates a new CSV file with just the unique keywords
3. Creates a new Excel file with three columns - Removed Keywords, Their Corresponding Retained Keyword, Similarity between the three
//
Example
I gave it 10,000 keywords - with everybody's favorite topics NFT - On an old Macbook Pro it took 6 minutes to get me 8597 unique keywords, removed 1402 keywords
Original Keywords I gave the script - Check here
Deduplicated Keywords the script returned - Check here
Excel File the Script generated - Check Here
//
How to use the script?
You can download the script through the below link or by clicking here - click the green "Code" button and download as Zip
Step 1: Prepare your keyword list
Create a CSV file containing your list of keywords - Name this file
keywords.csv. Each keyword should be on a separate line.Step 2: Install the required packages
Make sure you have Python 3.6 or higher installed on your system. Then, install the required packages by running the following command in your terminal or command prompt - please navigate to the directory where the script is present. If you're using something like VSCode things become easier.
pip install -r requirements.txt
The
requirements.txt is included in the below Github package.Step 3: Run the script
Open your terminal or command prompt and navigate to the directory where the deduplication.py script is located. Run the script with the following command:
python deduplication.py
Step 4: Set the similarity threshold
During the execution of the script, you'll be prompted to enter the similarity threshold. The default value is 0.8. If the similarity between two keywords is higher than the threshold, they will be considered part of the same cluster and one of them will be removed.
I personally prefer 0.9 - but I have added the option for you to enter the threshold so you can play around with those numbers till you get the desired output.
Step 5: Specify the output file name
You'll also be prompted to enter the name of the output Excel file. The default name is keywords_output.xlsx. You can choose a different name if you prefer. Something like nft-final.xlsx
Step 6: Check the output files
Once the script finishes running, it will generate two output files in the same directory -
unique_keywords.csv & keywords_output.xlsx (or the name you entered) //
How does it work?
- Reads keywords from a CSV file: The script reads your list of keywords from a CSV file, storing the keywords in a list.
- Generate keyword embeddings: The script leverages the
SentenceTransformerlibrary to create embeddings for each keyword. Sentence transformers are pre-trained neural network models that can convert text into numerical vectors (embeddings) that capture semantic information. The script uses the "paraphrase-distilroberta-base-v1" model for this purpose.
- Compute similarity matrix: The script calculates a similarity matrix for the keyword embeddings using the cosine similarity metric. Cosine similarity values range from 0 to 1. In this case, 0 would mean not similar at all - 1 would mean extremely similar. Try staying in the higher ranges.
- Perform Agglomerative Clustering: Based on the similarity matrix, the script applies the Agglomerative Clustering algorithm to group similar keywords together. This algorithm creates a hierarchical clustering structure, merging pairs of clusters iteratively until the specified similarity threshold is reached.
- Set similarity threshold: You can define a similarity threshold (default is 0.8) during the execution of the script. If the similarity between two keywords is higher than this threshold, they will be considered part of the same cluster and one of them will be removed.
- Generate output files:The script creates two output files for you:
unique_keywords.csv- Contains the list of unique keywords after removing similar ones.
keywords_output.xlsx (or the name you entered)- An Excel file with color-coded information on removed keywords, retained keywords, and their similarity. Removed keywords are highlighted in yellow.
Google Colab Version Here
Last edited by a moderator:
