[Free Script] Bulk Keyword Deduplication / Similarity Check

[banto123]

Thank you - the Skype messages have been overwhelming.

There's a bunch of questions. I'd appreciate it, if you can ask it here only.

I'll answer a few -

1. Yes - when you run the script the first time, it will download the model - which is a 500 MB Download
2. I tested on a MacOS with 10,000 keywords and it took about 4 minutes in total.
3. Yes, it will ask you in the terminal for your similarity threshold. If it confuses you - think of it like a percentage. So if you enter 0.90 - you're telling the script "Remove any keyword that is 90% similar to the others". If you enter "0.80" - you're asking for 80% or more - so on and so forth.
4. Yes, you can run multiple passes with the script.
5. No I don't intend to make a web version as of yet - it is resource intensive and may not be worth my time.

I also have a few requests on keyword clustering script. I am planning on implementing Jaccard Index - let me know if there is an interest for it.

Clustering will be great for SEO

 

[MonkeyJohn]

Hi @BlogPro - how is the clustering script testing going? Thanks

 

[ShamimRony]

Awosome

 

[spectrejoe]

Thanks for this <3

 

[C63]

Google Colab Notebook
https://colab.research.google.com/drive/1MJgxO1YVglrX85fqwEriBHSLgeC0uJl9?usp=sharing​

Saw a lot of you were having difficulty running this script locally, so created a Google Colab Notebook.​

​

Here's how you can use it ​

​

1. Open the Google Colab of the file here - https://colab.research.google.com/drive/1MJgxO1YVglrX85fqwEriBHSLgeC0uJl9?usp=sharing​

​

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 remove similar keywords. ​

Here comes the fun part - ​

​

1. Step 1 of the file will download and install all dependencies.​

2. Step 2 - this is where it will ask you to upload your keywords.csv - just click on "Choose Files" and upload. ​

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 ask you 2 questions​

​

1. Enter the similarity threshold (0 to 1, default: 0.8):​

​

Think of it like a percentage. So if you enter 0.90 - you're telling the script "Remove any keyword that is 90% or more similar to the others". If you enter "0.80" - you're asking for 80% or more - so on and so forth.​

​

2. Enter the output Excel file name (default: keywords_output.xlsx):​

​

Whatever you want the output file name to be. You can just hit enter.​

​

That's it you're done!​

​

To access your done files, simply click on the "File" icon (Fourth icon resembling a directory to the very left of your screen)​

​

And you should see keywords_output.xlsx and unique_keywords.csv there. Simply right-click and download. ​

​

Let me know if you have any questions or get stuck.​

WOW. This is amazing. work like a magic. Thank you so much. No words to thank you.

 

[C63]

Thank you - the Skype messages have been overwhelming.

There's a bunch of questions. I'd appreciate it, if you can ask it here only.

I'll answer a few -

1. Yes - when you run the script the first time, it will download the model - which is a 500 MB Download
2. I tested on a MacOS with 10,000 keywords and it took about 4 minutes in total.
3. Yes, it will ask you in the terminal for your similarity threshold. If it confuses you - think of it like a percentage. So if you enter 0.90 - you're telling the script "Remove any keyword that is 90% similar to the others". If you enter "0.80" - you're asking for 80% or more - so on and so forth.
4. Yes, you can run multiple passes with the script.
5. No I don't intend to make a web version as of yet - it is resource intensive and may not be worth my time.

I also have a few requests on keyword clustering script. I am planning on implementing Jaccard Index - let me know if there is an interest for it.

So grateful if possible to do as well.

 

[C63]

What should I do as the action to solve this error dear friend?

 

[BlogPro]

View attachment 253761

What should I do as the action to solve this error dear friend?

Nothing, it's just a warning, not an error - won't impact the script's workings at all.

 

[C63]

Nothing, it's just a warning, not an error - won't impact the script's workings at all.

How do I get final results after this error? I mean I tried to restart, but it give same again. Should I choose local method instead of this. this worked perfectly before but this is the second file im trying.

 

[taboulijaboulie]

Amazing, I was looking for exactly this

 

[BlogPro]

How do I get final results after this error? I mean I tried to restart, but it give same again. Should I choose local method instead of this. this worked perfectly before but this is the second file im trying.

Can you try once again? Looks like your Colab froze for some reason. If it worked once, it'll work again.

Let me know if you face the issue again please.

 

[C63]

Can you try once again? Looks like your Colab froze for some reason. If it worked once, it'll work again.

Let me know if you face the issue again please.

yes dear. I tried two times again. Even with a new gmail. But this error came in the last step after I entered 0.8

and yes, on the first time it worked perfectly without a single issue.

 

[IM Dude]

Here is a quick and dirty prompt for grouping semantically related keywords for chatgpt, I t outputs list of keywords into semantically related keywords, i remove onnce that dont want manually

Prompt:
Each of list item below is a keyword for blog post, group them together that are semantically related, make smaller groups as much as possible:
keyword1
keyword2
keyword3
...

 

[C63]

Here is a quick and dirty prompt for grouping semantically related keywords for chatgpt, I t outputs list of keywords into semantically related keywords, i remove onnce that dont want manually

Prompt:
Each of list item below is a keyword for blog post, group them together that are semantically related, make smaller groups as much as possible:
keyword1
keyword2
keyword3
...

Let me check this too

 

[IM Dude]

Let me check this too

Gpt4 take about 200 Longtail keywords at a time, so theres that to consider. If yoy give like 500 it will retutn too long prompt warning.

 

[BlogPro]

yes dear. I tried two times again. Even with a new gmail. But this error came in the last step after I entered 0.8

and yes, on the first time it worked perfectly without a single issue.

Please let me know the size of your second keyword list. I have tried but am unable to recreate the error.

You may DM me the CSV if you want me to take a look.

 

[IM Dude]

Umm... What was the point of this in this thread?

I don't mean to highjack the thread and wrote that on my post but removed it as its ai related and to the filtering on similar keywords.

 

[fg43085]

How do I get final results after this error? I mean I tried to restart, but it give same again. Should I choose local method instead of this. this worked perfectly before but this is the second file im trying.

I have noticed that if you have too much of keywords it does not work.

First, original file, had 9999 keywords
Then I tested with 20 000 and over 70 000 words and it did not work.

When I limit file up to 9999 keywords then it works.

 

[C63]

I have noticed that if you have too much of keywords it does not work.

First, original file, had 9999 keywords
Then I tested with 20 000 and over 70 000 words and it did not work.

When I limit file up to 9999 keywords then it works.

I see. I got nearly 20000 keywords. Then that's the reason

 

[IM Dude]

Hate to bother you, but package installation just don't work. I tried on 2 pcs that never had python before, installed latest python both time package installation failed with
ms visual c++ 14.0 missing error
Which is discontinued by MS. Installed the next version, still got the same error.
Can you do a fresh install on some rdp and post the instructions?