How To (Intelligently) Steal Your Keyword Strategy

When I am looking for a new niche, I always do the same thing.

First I brain storm a list of actual niches by observing the world around me.

Whether that’s in your house, the street, while watching a movie, in a mall or listening to music – niches are everywhere.

For example right now I can easily think of-

  • Power tools
  • Electric scooters
  • Drones
  • Video blogging

Once I have a target list of niches, I do some Google searches to see what kind of sites exist for a topic.

The best way to find affiliate type sites is to do searches with 2x types of keyword modifier-

Best Modifier

  • Best Power Tools
  • Best Electric Scooters
  • Best Drones
  • Best video blogging

Cheap Modifier

  • Cheap Power Tools
  • Cheap Electric Scooters
  • Cheap Drones
  • Cheap video blogging

With a list of 5x sites that shows up regularly for those searches…

…We can begin to steal our way to an intelligent keyword strategy!

How To (Intelligently) Steal Your Competitors Best Keywords

The easiest way to do this is with my personal Google sheet.

This is something I had developed to help me build intelligent keyword strategies in minutes rather than days-

This sheet will automatically spit out a personalized keyword strategy based on your competitors current search rankings in less than 5 minutes.

It does a bunch of cool things-

  • Automatically find’s the best keywords with high search volumes & low competition
  • Tell you if its possible to win a featured snippet
  • Calculates the intent of the keyword to help you focus on $$$ keywords

In fact:

With this free spreadsheet and the one I am going to give you tomorrow…

You’ll be able to create intelligent keyword & backlink strategies in minutes!

How To Setup The Keyword Theft Sheet

It only takes a few minutes to find all of the best keywords in your niche.

First take a look at this SAMPLE sheet and familiarize yourself with it.

Note: The SAMPLE sheet is view only, you need to make a copy of the blank template below to use it.

There are 4x tabs at the bottom to navigate through-

  1. Start Here – Enter your competitor domains here
  2. Data – Enter the data you export here
  3. Keyword Analysis – Automatically finds the best keywords for you to select
  4. Final Selections – Shows you final keyword selections

Just take a moment to browse around and click on the checkboxes and things to see how it works.

When you are ready make a copy of the Google Sheet below-

Steal Your Keywords Sheet – MAKE A COPY OF ME

Do that by going to File > Make A Copy.

make a copy

Next:

You are going to take out the 100% free 14 day SEMRush trial.

This is where we are going to get all of the world class data to plug into the sheet without spending any cash! (mwahaha)

UPDATE: The Ahrefs & SEO Powersuite versions of the sheets have problems at the moment. We are working on a fix, in the mean time enjoy the free SEMRush data.

How To Setup The Sheet For Your Niche

Setting up the sheet is easy and really just boils down to a 2 step process-

  1. Import the data
  2. Make our keyword selections

It really doesn’t get any easier than that and the sheet will do all the heavy lifting in the background for us.

FOLLOW THIS VIDEO TO SETUP THE SHEET

Step #1 – Start Here Tab

Go to the first tab “Start Here” and enter the domains of your competitors (without http or www) in column A-

Then click on the links to automatically open the right report that you need.

Make sure you are on the Organic Research > Positions report-

And then on the right side, scroll down a bit and click on the export button and select the first 3,000 rows to export-

Repeat this for each of the competitors until you have exported all of the data.

Step #2 – Loading The Data

Head over to tab #2 – Data and import each of the files you exported in step #1.

The quickest way to do that is-

  1. Open one of the files from step #1
  2. Delete the first row of data
  3. Highlight all of the remaining data and copy it
  4. Then paste it into Tab #2 of the google sheet
  5. Repeat the process adding each report you exported in step #1 to tab #2

This is how you should do it-

Once you have loaded in the data from all of your competitors, you are ready for…

Step #3 – Keyword Analysis

Now head over to the 3rd tab #3 – Keyword Analysis.

This sheet has already found the easiest to rank for keywords with the highest search volumes for you-

It also includes some extra helpful columns like if there is opportunity for a featured snippet.

It will also tell you if it’s a tyre kicker keyword (ignore these) or a commercial based keyword.

Just work through the sheet and tick the little tick box when you find keywords you like!

Step #4 – Review Your Final Selections

Last but not least jump over to the 4th tab #4 – Final Selections and review your final keyword choices.

Once you have done that, your personalized keyword strategy is complete!

Wrapping It Up

Make sure you get that setup right now and that you tick the boxes to make your final keyword selections.

Because I am going to show you how to steal all of your competitors best backlinks for those keywords VERY soon!

And once you have done that…

…you will have an intelligent SEO plan of attack based on cold hard data!

If you have any questions, please ask below and I will help you out!

And if you want to take things to the next level I highly suggest you check out my step by step 90 Day SEO strategy to grow your search traffic.

Link Building

Link building you will be proud of.

Learn more

SEO Agency

We take full control of your traffic.

Learn more

Learn Portal

Free SEO tutorials to increase your traffic.

Learn more

What Are Your Thoughts?

352 Responses

  4. Thanks! This looks awesome! If I make a copy of the blank sheet, will it also work if I open it in Excel instead? Thanks! Ralph

    Reply

  8. LOVE this…Can i use more than 5 competitor’s sites?Is there a way to clear the Select column in Tab #3

    Reply

  10. Tab 3 gives me gibberish :)#N/A No FALSE#NA cell contents are:=IFERROR(__xludf.DUMMYFUNCTION(“QUERY(A2:D500,””select A,B,C,D WHERE A IS NOT NULL ORDER BY C ASC LIMIT 300″”,0)”),”#N/A”)

    Reply

    1. Which sheet?Which tool?Does the columns of your sheet on tab 2 match the columns on the sample sheet in tab 2?

      Reply

  11. I made a copy of the blank file, clicked on the ‘view the full tutorial’ and there’s no sound. ?

    Reply

  14. I love you Matthew, my brother… You are such an amazing person. I have stopped visiting other blogs after landing on this page. I want to say many things but it is not a good place but yes you are marvelous. I have watched your PBN videos and you told us to not use Gmail for backlinks. Right.1. Should we use google spreadsheet or Microsoft Excel?2. How can I do if I want to attach a spreadsheet as you do? If someone wants to download it, they have to share that post. 3. How I can set up the comment box (facebook account – like & share) like you do? I can understand you are a very busy person and you do not have leisure time to reply but when you get leisure time (2019), please help me. Take your time buddy. I can wait for you and now I am a regular reader of your blog. I am not allowed to use a third party website but still, I take the risk for your blog because I learn something. one click on your website equal to 2-3 learning. .. Awesome Awesome & Awesome.

    Reply

    1. I use Google for lots of things but NOTHING to do with PBN’s!And I havent tested if this works in excel so let me know hahaThat is done with a social locker

      Reply

  15. Great tool but I don’t see any quick or easy way to remove branded searches from the competitor data.How do you clean the data to remove all of the branded searches the competitors pages rank for?

    Reply

  16. Hi Matt, Steps 1 and Steps 2 are no problem but when I try and complete Step 3 the all the sheet says is N/A. The function says: “=IFERROR(__xludf.DUMMYFUNCTION(“QUERY(‘#3 – Keyword Analysis’!E2:K1000,””select E,F,G,H,I, J Where K=””&TRUE&”” ORDER BY G,F LIMIT 100″”,0)”),”#N/A”)”. Got any idea how I can fix this?

    Reply

    1. Which sheet?Which tool?Does the columns of your sheet on tab 2 match the columns on the sample sheet in tab 2?

      Reply

  17. Hey Matthew, Appreciate your work, Thanks for sharing the blank sheet, but for some reason it’s only view access for me 🙁

    Reply

  18. Excellent Matthew, thanks a lot!I’m using it and it seems that the ranking keywords function is only available for the US.Entering other (European domains) just give zero results. Maybe it’s good to add that to the instructions. best regards, Robbert

    Reply

  19. Thanks Matthew was trying to work out to do this in libre office but you have saved me the pain, looking forward to the other sheet that collects all those beautiful back links to give to my va

    Reply

  20. Thanks for the awesome kw guide. If we would like the 3rd tab (kw analysis) to show lower volume say 100, how should we change the query ?Because we focus more on local seo, and the volume will be much lower.

    Reply

  22. Is the analysis tab supposed to have 300 keywords maximum? I added 5,000 to the second tab, and tab 3 shows 300 keywords analyzed. Thanks

    Reply

  23. Hey, thanks for sharing. Got a question: How is this different than just using ahrefs to analyze a competitor’s backlinks?

    Reply

  27. I do not see the File link. I tried various ways of downloading, copying the formula, clearing the cache, refreshing and rebooting. I clicked the red link:When you are ready make a copy of the Google Sheet below-Do that by going to File > Make A Copy.Same problem on both the keyword page and the links page. I sent you an email with a png of what i see on my screen.

    Reply

  31. This is an incredible resource! I did all of this manually on my own in the past so when I used the spreadsheet my mind exploded haha thanks, Matt!

    Reply

  32. Thanks Matt, this looks killer as always! You probably have enough clout man, you should see if they’ll make you an approved app so we can get their partner API key thing without having to go all in on an API plan.PS. Love the styling, Google sheets wizardry is one thing, but you don’t see brand consistent sheets that often!a

    Reply

    1. Yeah I wanted to but they were shy about it. Thanks, styling is nothing fancy just a few headers!

      Reply

  34. Guys, is the sheet working alright for everyone? After pasting the data in sheet two, there no keywords being processed in sheet 3. It stays blank.Awesome tool, Matt! Incredible idea.

    Reply

    1. Sorry Jordan, try making a copy now and make sure it’s from the link in bold not from the sample sheets.

      Reply

  36. Hello,Thank you for such an interesting strategy. I’ve loaded a total of 8000 keyword into the sheet but just around 200 are being copied to the #3 sheet. How can I solve this?Thank you.

    Reply

  38. Hi Mathew Woodward, what if we blog about politics and the content produced isnt important pass that day. My niche doesnt suppor ever green content. Thanks

    Reply

    1. Then you can only look at keywords that had 2017 or 2018 in them and predict what they will be with 2019 in them

      Reply

  39. Hey,I followed all the steps but it seems it’s not doing the keyword analysis nor the final selection.Both Tabs 3 and 4 have an error #N/A=QUERY(A2:D500,”select A,B,C,D WHERE A IS NOT NULL ORDER BY C ASC LIMIT 300″,0)Thanks for sharing and save us time

    Reply

    1. Which sheet?Which tool?Does the columns of your sheet on tab 2 match the columns on the sample sheet in tab 2?

      Reply

  41. I am really amazed about the the stuff that you come out with. Originally I came across your blog by accident. I have been following you since. The best thing I learned from you is writing thorough blog posts and I must admit that I stole this strategy of yours… But this has been the best thing I’ve ever done. A blog post I wrote a while ago using this strategy has produced a thousand dollars in affiliate commissions and I am really happy with that! Thanks.

    Reply

  43. Hi Matt,I already have an account. There seems to be no way of linking the spreadsheet?CheersLyndon

    Reply

  47. Good morning Matthew, awesome sheet!Am I being thick, can’t actually make a copy of the sheet…

    Reply

    1. Try it now… Make sure you are making a copy of the blank sheet (link in bold) not the sample sheets in the tabs 🙂

      Reply

    1. Hey Tony, you can’t make a copy of the sample sheets but you can make a copy of the blank sheet (link in bold a bit further down).

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *