SEO Strategy

One of the major challenges for large scale SEO, and even for medium scale, is determining which keywords to target. This case study shows how we built a keyword universe of 15.000 unique keywords, in 21 categories. We created a data model using the search results, backlink data, and keyword data, and Google analytics data. With this data model we could pinpoint the keywords and pages that had most potential for the clients SEO strategy.

Why this approach

Why a multidimensional data model?

There are a few important dimensions in SEO: keywords, rankings, landing pages, and backlinks. Unfortunately, data comes from different sources. Therefore, data has to be gathered from various sources, cleaned and combined in a multidimensional data model. Subsequently this model can be used to analyse and determine the optimal strategy.

Why so many keywords?

Google search results are can only correct by approximation, because they vary by context. Taking larger datasets enables the application of statistical methods to increase robustness of the analysis. It also allows for finding and confirming the patterns in the data. These patterns can for example be the keyword group that is consistently underperforming. Also, the large dataset allows to explore the long tail and identify niches you might have missed.

The process

Step 1: Generate keywords by permutation

Big data starts with big keywords. Which in our case consists of a ‘keyword universe’ of over 15.000 keywords. The keywords are generated in a two-step process. First, keyword research identifies keywords and groups them together in semantically similar categories. Then keywords are generated by recursively combining all keywords in certain groups with each other, which results in all relevant permutations of words the categories. During this process, the keywords are automatically tagged to enable granular analysis later on. A small excerpt from the keywords that gives inside in how they are generated could look as follows:


Step 2: Build data model

Keywords, link data, and search results are combined into a data model using PowerPivot for Excel. This tool combines the familiar user interface of pivot tables in Excel with the power of a relational database. Keyword have dimensions such as volume, CPC and keyword tags. You can model traffic with click through rates per position. For each domain, subdomain, and URL, add data on the number of links, they have, and other metrics, such as trust flow or domain authority (Moz). If available, data from Google analytics can be added to analyse conversion rates and include it in the strategy. The screenshot below gives an idea of the data model used for the analysis:


3.       Create interactive pivot tables

Now that the data model is finished, we build pivot tables to answer the strategic questions. These tables act as a user friendly dynamic dashboard that we ourselves, and also the client can easily use to explore the data and answer questions. The powerpivot Pivot tables are quite flexible and allow for designing custom metrics that can answer almost any question about the data. A simple table might look like this: 3  

With filters to select keyword groups to analyse:

4.       Analyse the results

When the data model and interface is built, the tool is used to answer the clients’ questions. This is often: Which keywords should we target, which pages should we optimise, and where are competitors strong. Below examples of the charts (for a small subset of the data) used to present the analysis: 6

5.       Create your strategy

The final step is to create strategy from the conclusions. Prioritise keywords, and map landing pages to them. Estimate how much resources you will need to rank, based on competitor data you analysed. This strategy is the start of a great SEO campaign.


Using our advanced SEO strategy analysis process, we analyse the entire keyword landscape and look at all the important metrics to make sure your SEO strategy is the best it can be. After all, with so much at stake, in a project that can take years, with a strategy that is hard to change, you might as well start out with the best chance you have. 5

Leave a Reply