- Apr 23, 2012
- 2,842
- 5,850
Hey all,
So when I shared my Bulk Keyword Deduplication/Similarity Check Script - a lot of you ended up asking me for a decent Keyword Clustering Script. Well, here we go!
The Problem
Your spreadsheet is a jungle, your keywords are on a caffeine buzz, and no amount of 'vlookup' sorcery can tame them. Enter - my script - it's like a magical lasso for your keywords, clustering them into topics faster than you can say "Search Engine Optimization"!
Well, we all have access to a plethora of keywords. But they're all over the place. If you start at cell 1 and begin creating pages, by the time you reach cell 20 - you find you're at the first keyword or a variation of it.
Exports from
SEMRush, ahrefs or anywhere else suffer the same issue.
These keywords need to be grouped! And the current solutions are either too expensive, or they simply don't work in a nuance manner.
The Magic of the script
The script using topic modeling via BERTopic which uses transformer models and c-TF-IDF - it preprocesses your entry, performs topic modeling etc.
It then does three things -
1. It creates a cluster of your keywords
2. It creates an Excel Spreadsheet with your keywords on the left, and cluster on the right. Each group/cluster is color coded for easy reading.
3. It creates an additional Spreadsheet where it dumps all the Outliers - aka Keywords it couldn't group or cluster.
█████
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
Excel File the Script generated with Clustering - Check here
Outliers the Script Generated (Non-Clustered Keywords) - Check here
It grouped Pizza and Papa John's together
It found investment related How-Tos and got them together
It found "Start a Project" related How-Tos and put them in a different cluster
It put Huxley and Evolution in the same group
█████
How to use the script?
There are two ways you can use the script. Running it locally on your computer, or via Google Colab - I'll explain both below.
█████
Option 1 - Running it locally - on your computer
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. No headers.
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.
Bash:
pip install pandas bertopic openpyxl tqdm
Step 3: Edit the Config file (Optional)
You can edit
config.json and adjust parameters - but I'd recommend doing this only if you know what you're doing.
Step 4: 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:
Bash:
python deduplication.py
Step 5: Check the output
Once the script finishes running, it will generate two output files in the same directory
- clustered_keywords.xlsx - An Excel spreadsheet with color-coded groups of your keywords.
- outliers.xlsx - An Excel file with keywords, that couldn't be grouped - your outliers, basically.
That's it you're done!
█████
Option 2 - Running it on Google Colab
1. Open the Google Colab of the file here - Google Colab Link
2. Make sure you're logged in to your Gmail and click the Copy to Drive button (just below the file name at the top) - if you're unable to find it, just click File >> Save a Copy in Drive
If you do it right, a new Window will open with your file's copy from your Google Drive
3. Now, in the top menu, goto Runtime and click on Run All
//
That's it - your script is now ready to take your Keyword file and perform clustering.
Here comes the fun part -
1. Step 1 - This is where it will ask you to upload your
keywords.csv - just click on "Choose Files" and upload. 2. Step 2 - Is where you can make edits to the Config file. Although do it only if you know what you're doing.
3. Once that is done, scroll to the very bottom and watch as the script runs its magic, generates the necessary embeddings etc.
4. Once it is done - it will create two files - it will zip them and offer you one single download. 
- clustered_keywords.xlsx - An Excel spreadsheet with color-coded groups of your keywords.
- outliers.xlsx - An Excel file with keywords, that couldn't be grouped - your outliers, basically.
That's it you're done!
█████
How does it work?
1. Reads CSV Data
The script pulls data from a specified CSV file, where keywords are expected to be delineated. These keywords are then loaded into a Python list for processing.
2. Keyword Preprocessing
It employs a preprocessing step where each keyword is converted to lowercase and stripped of leading or trailing whitespace to ensure uniformity and improve the quality of the topic modeling.
3. Topic Modeling with BERTopic
Utilizing the BERTopic library, the script performs topic modeling. This process involves the transformation of preprocessed keywords into topics using unsupervised machine learning, specifically leveraging transformer-based embeddings to understand context and group similar keywords.
4. Logging
Throughout its operation, the script logs important information and errors. It uses both console and file logging handlers, with a rotating file handler to manage log size and retention.
5. Configuration Management
The script reads a JSON configuration file at the start, which dictates various parameters such as file paths, minimum cluster size, language for BERTopic, and other operational settings.
6. Excel Output
Post-clustering, the script generates an Excel Spreadsheet with separate sheets for categorized topics and outliers. The topics are color-coded for visual differentiation, and the workbook is formatted with adjustable column widths for readability.
The script creates two output files for you:
- clustered_keywords.xlsx - An Excel spreadsheet with color-coded groups of your keywords.
- outliers.xlsx - An Excel file with keywords, that couldn't be grouped - your outliers, basically.
7. Error Handling
Exception handling is integrated to ensure any errors encountered during processing are logged appropriately, providing traceability and aiding in troubleshooting.
