What is your challenge?

Talk directly to a digital expert at  +49 (0)30 611016610

I want news!

Contact
Eingerollte beige Schatzkarte
SEO

Create a successful keyword mapping

Home Blog
Nora Plato 25/02/21

Just as the map used to be the way to the treasure for the pirates, keyword mapping is the map to a successful website for the SEO pirates. We explain here what you should pay attention to in your keyword mapping and which tools can support you.


What awaits you:

  1. Why is a keyword mapping important?
  2. How do I create a keyword mapping?
  3. Using VLookup in the keyword mapping
  4. Set meta data for the page
  5. Conclusion

1. Why is keyword mapping important?

The term keyword mapping is for many with a question mark, which is why we first clarify what it is. We are moving here in the onpage optimization. A keyword mapping is a document in which each URL of your website is assigned to a keyword. A URL may of course have several keywords, but a keyword should not be assigned to two different URLs at the same time.

To illustrate it a bit, think of each URL as an island. On the islands are different paths that represent your keywords. An island can have several paths, but a path (keyword) must not be assigned to two different islands (URLs). Because paths usually do not cross water, this is easy to remember. If the keywords were assigned to multiple URLs, keyword cannibalization would occur. Google then no longer knows for which keyword the corresponding URL really ranks, i.e. whether the path to island 1 or island 2 is meant.

Keyword research = keyword mapping?
No, a keyword mapping is not a keyword research! However, keyword research is part of keyword mapping. The research involves a basic search for appropriate keywords for your website. Here you can search for general keywords that are applicable to your website or niche keywords. Always put yourself in the shoes of potential users here. What do I google to find my website? Keyword mapping, on the other hand, goes one step further and assigns the keywords to the corresponding URLs for which they are to be optimized. This is why a thorough keyword mapping is so important. It creates an overview for which keyword the corresponding page should rank and is also an important basis for content creation later on.

2. How do I create a keyword mapping?

To create a keyword mapping as clearly as possible, Google Sheets is recommended. Here you can work with the function “VLookup“.
To make the whole thing understandable, let’s work with a fictitious example: The provider “Surfing Abenteuer” wants to optimize its website and creates its keyword mapping after a keyword research. Surfing Abenteuer is a German provider and offers various courses, vacations and equipment related to surfing. The site has many subpages, each with their own URLs. These would be for example:

  • https://surfing-abenteuer.com
  • https://surfing-abenteuer/surfer-notes
  • https://surfing-abenteuer/surfer-beaches
  • https://surfing-abenteuer/surfer-blog
  • https://surfing-abenteuer/surfer-shop
  • https://surfing-abenteuer/surfer-shop/surfboards
  • https://surfing-abenteuer/surfer-shop/surf-equipement

In a Google Sheets table, the individual columns are now labeled differently. The best way to do this is to use the following breakdown: page type, click depth, URL, topic, keywords, SV (search volume) and ranking. The whole thing looks like this:

Create keyword mapping, screenshot from Google Sheets

Since subpages are often thematically very similar, you should pay attention and really assign the appropriate keywords to the corresponding URL.
Another important clue is the search volume: pay attention to how many people search for the term on average per month. After all, what good is it to you and your potential customers if you optimize your site for a term that nobody searches for?

To determine the search volume for a keyword, you can use tools such as the Keyword Planner in Google Ads. You can determine the search volume and add it manually in a column next to the keyword. For our example surfing adventure it looks like this:

Keyword mapping with search volume, screenshot from Google Sheets

Manchmal ist es gar nicht so einfach, eine Vielzahl an Keywords zusammenzustellen – besonders wenn sich die Seiten nur minimal voneinander unterscheiden. Oftmals gehen dann schnell die Ideen für neue Keywords aus. Deshalb haben wir dir hier diezusammengetragen, die dein Keyword-Mapping erleichtern können.

Sometimes it is not so easy to compile a large number of keywords – especially if the pages differ only minimally from each other. Often, you quickly run out of ideas for new keywords. That’s why we’ve gathered the 9 best keyword tools that can make your keyword mapping easier.

3. Use VLookup in keyword mapping

Instead of manually assigning the position and ranking URL to each keyword, we recommend the following to facilitate your work enormously: the function “VLookup” in Google Sheets (or MS Excel). If the page has already existed for some time, it is also useful to see where the page ranks for the corresponding keyword in Google.

Illustration, source: unsplash.com

Search Analytics for Sheets:

We use the Search Analytics for Sheets add-on for this purpose. This add-on is connected to Google Search Console and Google Analytics and can import data from these sources. To do this, simply click on “Add-ons” in your Google Sheets. It is important that you are in the document with the same account that you use for the add-on. Make sure that in the category “Group By” you first enter “Query” (which means keyword) and then “Page”. We recommend a date range between three to six months. By clicking on “Request Data” a second tab will appear next to your keyword mapping.

Search Analytics for Sheets, screenshot from Google Sheets

Now, unfortunately, it gets a little more complicated, but once the formula is correct, the rest is a breeze.

Insert VLookup:
The finished formula should look like this in the end – don’t worry, we will guide you step by step to the right result.

VLookup formula, screenshot from Google Sheets
  • Create a column named “Ranking” next to the “Search volume” column
  • Open the formula with =VLookup(
  • Then click in the cell with the keyword. In the Surfing Adventures table, this is the cell that says “Surfing” in bold. In our table it is I2. The goal is to find out the ranking for this keyword. So =VLookup(I2
  • Then you go to the new tab that is created by Search Analytics in Sheets (in our case it is called “RankingsJan”) and select all relevant columns. Important: The Query column must be the first column!
  • The formula now looks like this: =VLookup(I2;RankingsJan!A:F;
  • In order to tell the formula that the ranking of the respective keyword should be inserted here, we enter the number of the column named “Position”.
  • In our tab “RankingsJan” the column “Position” is number 6, so it has the index 6.
create VLookup, screenshot from Google Sheets
  • Our formula is now: =VLookup(I2;RankingsJan!A:F;6;
  • Finally add the attribute “False” (because the table is not sorted) and close the parenthesis.
    And now our formula is ready: =VLookup(I2;RankingsJan!A:F;6; FALSE)

With this, the ranking can now be traced. It is best to pull down the formula once the entire column.

4. Set meta data for the page

In order to give your page an SEO advantage, it is recommended to enter meta data for each page – i.e. each URL. The meta data consists of the meta title and the meta description. These are displayed in the so-called SERPs. SERPs are the pages that Google displays in response to a search query.

A clear SERP is also a real pleasure for search engines. In addition, users can see directly what the corresponding page is about. As a guideline for a meta title: no longer than 55 characters. For the Meta Description you should not use more than 150 characters. A good SERP also always contains the main keyword. For our surfing provider, the following would be a good example:

Surfing and Fun – Surfing Adventure
Learn everything about surfing here! ✓ Low cost surf camps ✓ Learn to surf fast ✓ Money back guarantee → Check out now!


Conclusion

Keyword mapping is not only important at the beginning, it also serves to maintain the website in the further course. In addition, it helps you to always keep an eye on the keywords for which the page should rank when creating content. This in turn makes it easier for you to write the texts accordingly. Furthermore, keyword mapping prevents pages from being optimized for the same keywords and thus cannibalizing each other in the worst case. We hope we could support you in creating the keyword mapping for your website! If you have any questions, please feel free to contact us.

Nora Plato
Nora Plato Working Student SEO

Do you have questions about the blog entry or are you looking for professional support in this or any other area?
Then feel free to send us an e-mail via our contact form.

Contact

More Blog Posts

Funnel Darstellung
Full funnel strategy: A marketing guide
Entscheidung im Ringkampf
Agency vs. freelancer: Why a (reliable!) agency is the better partner