Keywordmap: How to bring structure into your SEO strategy

If your site has more than one handful of landingspages and search terms, you and your team can quickly lose track. The solution: A thought-out keyword map enriched with helpful data. Complementing a keyword monitoring makes it a powerful tool for controlling the SEO activities. Here you can find help and suggestions for your own implementation – and an example to download!

Why is a keywordmap useful?

At the end of the day, SEO is all about search terms: The goal is to have a suitable, high-quality and meaningful landing page for every search query. Soon, however, questions arise:

  • Do we already have a landing page?
  • Where should editors internally link to what linktext?
  • Have certain rankings improved or deteriorated?
  • Do certain pages work better than others?
  • For what keywords is still room for improvement ?

Therefore it makes sense to structure the topic and build a keyword map.

Why not just use the keyword functions of SEO tools?

There are some SEO tools that list all of the keywords your site is targeting. However, they are always colorful: they often focus on only successful keywords or pages. Or the actual losers fall into the mass of irrelevant keywords. For an overall picture, this is simply too much data.

Better there are the tools that allow you to create your own keywordset. But ultimately, it is only a is keyword set created – which landing page associated with the keyword, do not see you often. In addition, you can not sort by topics, sales or traffic. A prioritization or grouping of these keywords is therefore not possible. With dozens of keywords you can then easily overlook the most important keywords in the monitoring. It is therefore useful to have a solution that you can design yourself – such as an Excel spreadsheet.

Minimum requirements for a keyword map

Ultimately, the keyword map should include at least two information: the keyword and its URL. Note that each keyword can only be assigned to one URL. Otherwise, multiple rankings arise which adversely affect the positioning.

Which keywords are purely – and which are not?

If we landing pages and optimize snippets , we define in the keyword research is always primary and secondary keywords.

  • Mainkeywords are used at the front of the title, in the description, in the H1, and in the text.
  • Beside keywords are also in the title and the description, in an intermediate heading and also in the text.

Our goal is to focus on optimization. However, the distinction between main and secondary keywords is also important for the later evaluation: If I later analyze only the rankings of the main keywords, I can be sure that the keyword is prominently represented. The more similar the conditions are, the better the conclusions can later be drawn from the developments.

Keywords that do not appear in the title should be obvious or ignored. After all, the chances of good rankings are significantly lower in these search queries.

You should also not forget that the Map an overview should be. You should limit yourself to one to three keywords per URL. If you use all the keywords, you can use the keyword function in an SEO tool of your choice. Notes therefore the keyword either in the plural or in the singular – and consider only really important longtail keywords.

Pro-Tip: With the serplorer immediately find the most traffic-strong keywords for each URL (or also: Keywordmapping for lazy)

Those who want to get started immediately, or which side is too large for a manual revision, which was the (paid) Tool serplorer recommended. As well as, Serplorer stores the data back to the actual search queries from the Search Console. The nice thing is, that the data here again differently prepared. This is great because of a function: the URL view. Here all keywords are grouped by URLs (in Webmaster Tools see you only either the URL or the keywords):


And here the magic happens:

  1. Period to the maximum period (more than 90 days, yay!).
  2. Click on “Export” at the top right of the table.
  3. Filter the keyword column for blank lines (these are the aggregated clicks for the URL).
  4. Select all filtered rows and delete them all freezing. Also delete the filter.
  5. In Excel, click “Remove duplicates” under “Data” and select only the “URL” column.
  6. Voila, you already have a passable keyword map with all URLs that have SEO traffic, their most important keyword, clicks, impressions, and position.


It is also possible to limit the export to “Web” search results (without the image search).

Complement with data for prioritization

But two lines are of course somewhat mau. You should therefore your table column expand column of data for evaluation – either by = VLOOKUP () or Power Query (well explained by Stephan Walcher in the Website Boosting output 39 ). Such data could include:

  • From Google Search Console:
    • Clicks (last 90 days)
    • Impressions (of the last 90 days)
    • Average position (last 90 days)
    • Click rate
  • From the keyword Planner:
    • Monthly search volume
    • Estimated price
  • From your own data:
    • Category or subject area
    • author
    • Position before the first optimization
    • Date of last optimization
  • Even KPIs to user behavior from Analytics can help to assess the quality of search results (at least based on the URL)
    • Length of stay
    • Bouncerate
    • Sales / conversions

There are no limits to data and creativity. A few good additional proposals also offers Rand Fishkin in his whiteboard Friday about Keyword Maps .

Pro Tip: Query search volumes using the SeoTools for Excel from the SEMrush API

Who gets its search volume directly from the Keyword Planner, knows the problems:

  1. The query is limited to a few hundred keywords
  2. The results are summarized recently, for example, you only get results for singular and the search query in the plural is completely underlined
  3. It. Costs. Great. A lot of. Time!

To get around this, you can use two tools: The (since version 6.0 chargeable) function = Download String () from the SeoTools for Excel and the API of SEMrush (also with costs). My solution is not the most elegant formula, but it works (if you get the API Key copying and your keyword in cell A2 is):

= IFERROR (VALUE (RIGHT (Download String (CONCATENATE ( “ semrushapikeyeinfügen & export_columns = Nq & database = de & phrase =”; A2 )); LENGTH (Download String (CONCATENATE ( “http: / / semrushapikeyeinfügen & export_columns = Nq & database = de & phrase = “; A2 ))) – 15)); 0)

Note: The search volume on SEMrush are still exact match . It means they do not aggregate similar keywords fortunately not like the keyword planner. This means that the current data from the keyword planner is not tapped here. For who wants it today?

To pursue ranking development

It will be quite exciting when we integrate the ranking development for the keywords into the keyword map. For longer-term observations, the average positions from SearchConsole with the data directly from the search are the best choice. Finally, these data take into account all locations, all devices, and all browser histories.

We also use the SEMrush API, which allows us to retrieve the actual daily ranking data of the keywords we monitor directly in Excel. These day-to-day data are especially worth a relaunch gold. After three days (when the data in the Search Console start) or after a week (as in most tools), the child has long since fallen into the well.

Sample keyword map for download


So that it is not only abstract, but also understandable and transferable, I have created a little dummy for you. It contains the data from Search Console and Google Analytics. There is already a first small ranking development. You only have to replace the export data with yours and define your own keywords together with the URL (path). Already your first rudimentary keywordmap! Have fun with it!


Leave a Reply

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