How to create your own actionable SEO dashboard with Python

Marc van Herrikhuijzen
Oct 12 · 9 min read

For most SEO specialists it is mandatory to keep track on search visibility and keyword rankings in search engines. There are a lot of tools who provides this, such as AWR Cloud, STAT, Ahrefs, SEMrush and many more. This data can be overwhelming when you have a lot of keywords to track and want to be able to compare current rankings with rankings in the past for you and your main competitors.

Also, this amount of information (together with server speed) can impact the fun of analyzing when the information you want is not instantly loaded.

Python can be a good way to analyze a lot of data, make some custom calculations on this and plot it just the way you want!

In this article I will write a step-by-step process for creating your own dashboard using Python’s Pandas and (in my case) SEMrush exports. As said, there are a lot of keyword tracking tools, so any other export is fine as well but needs some customization tweaks.

Global idea and working of the dashboard

The dashboard is set up in 4 main steps:

1) Collect and clean the exports from SEMrush (or any other ranking export file) and create a useful work file to continue

2) Add a visibility calculation to the data and plot this over categories

3) Plot rankings overtime per category and keyword

4) Create a pivot dashboard for specific insights

The result will be good understanding of ranking and visibility fluctuations, find issues per category and even per keyword group. This can give you a very specific idea where to optimize.

Let’s get started

For my company we track 571 keywords at SEMrush over 88 categories (tags). For this article I have collected exports from SEMrush on a weekly base for mobile rankings. I also track the rankings of the 4 main industry competitors.

1: Collect and clean the exports from SEMrush (or any other ranking export file) and create a useful work file to continue

Now that we have some background information, open a Jupyter Notebook (recommended because of the visualizations) and import the main libraries to use.

#import all libraries and widgets

Download weekly reports from SEMrush and save to desktop. Because the download will be in .xls format and NOT in .xlsx format, an additional step is required to put the data into a Pandas DataFrame. So, let’s convert the .xls download file, name it the date of export and save it into a folder to work in.

#convert .xls to .xlsx

Now it is time to ask Pandas for help and make some fine tweaks to the data. Notice the ‘header=7’ in the script below. This looks weird, and I think it is weird. But that is how SEMrush creates the exports. Perfectly fine. But weird 😊.

#read .xlsx into dataframe

After that we can save this to a .xlsx file with customized columns. For these files I have created a new folder: [./use_cols].

#save to new .xlsx file

We need to do this for all periods. So, just go find and replace the dates when editing and saving. If someone have any clue on how to automate this process, please let me know!

Because we need to work with all this configured Excel files it is time to gather all the DataFrames and combine all files from the folder.

#combine all files from directory

And let’s now forget to categorize the keywords! In a subfolder ([./workfile]) I created a .txt file with the tags of the keywords. Example of the tags.txt can be found here.

#categorize all keywords from txt file

2: Add a visibility calculation to the data and plot this over categories

Now that we have our work file it is time for some fun stuff and get actual plots and useful insights.

#open work file and get rid of empty fields

For a visibility report some calculations needs to be done. When ranking in organic search for position one you have well deserved a 100% visibility score. This will be less when ranking lower up till position 30 where the visibility percentage stops. An example can be downloaded here.

#create visibility_dataframe for each website

Notice that we also have created a DataFrame with visibility scores for all websites and categories. In step 3 we are going to plot rankings per categories as well. So why not create a DataFrame for that purpose right now with a very similar line of code.

#we are going to use the following export later on

A final tweak and we are ready for some plotting! Just to keep things organized a new DataFrame is created for plotting visibility.

#new dataframe

Plot it like it’s hot!

pivot = dataset.groupby(['Datum']).mean()
visibility overtime seo dashboard
visibility overtime seo dashboard

This is already a nice-looking chart, but this is a visibility score for all categories. Let’s create an interactive widget to filter the data per category!

tags = widgets.Dropdown(

Let me add some screenshots of the output.

search visibility in google for home cinema
search visibility in google for home cinema
search visibility in google for home cinema
search visibility in google for home cinema

3: Plot rankings overtime per category and keyword

The Search Visibility per category can pinpoint categories where action can be required (or just to show of how good things are going 😊). But, especially in the case of ‘action required’, it needs some more information. Which keywords are losing its rankings? Over what time period?

In the previous step we already created a dataset for the rankings. It is time to get this data into a DataFrame and make some final tweaks.

rnk = pd.read_csv('./workfile/ranking.csv')

Also, with keyword rankings, a similar widget can be very useful.

tags = widgets.Dropdown(

Again, here are some examples of the output.

rankings in google for category wasmachine
rankings in google for category wasmachine
rankings in google for category gaming
rankings in google for category gaming

If you notice some improvements or drops in rankings per category, a deep dive per keyword can be very interesting!

kws = widgets.Dropdown(

2 screenshots of rankings per keyword overtime.

rankings in google for keyword speakers
rankings in google for keyword speakers
rankings in google for keyword chromecast
rankings in google for keyword chromecast

4: Create a pivot dashboard for specific insights

The final step is to use the pivot method to create nice overviews for a keyword group per website. The fun part of this is that it gives very specific information about keyword groups. For example: in which period did my keywords lose rankings? Or: in what time period an improvement shows after optimization X or Y? This can be a useful reporting method when a feature has been released or a linkbuild campaign has rolled out. Or… whatever you want to keep track on.

pivot_df = pd.read_csv('./workfile/ranking.csv')

Because, in this case, I am mostly interested in the performance of the rankings of BCC, I only need to call rankings for this website.

piv = pivot_df.pivot_table(index=['Keyword','Categorie','Search_Volume'],columns = ['Datum'],values=['Rankings_BCC'])

Finally, we can create an interactive pivot table inline. There is an option to pop out the pivot table to have a new window pop up with a very nice overview of the information you have collected. The URL of this location will be something like >> http://localhost:{YOURPORT}/files/{YOUR_LOCATION}/reporting/pivottablejs.html.

pivot table rankings washing machine
pivot table rankings washing machine

Playing a bit with some filters it is clear that some fluctuations are going on for the washing machine category of BCC which can lead to actionable things to work or focus on!

To wrap up

If you are using any kind of rank tracker to monetize keyword rankings in Google it can be very useful to get instant actionable information and keep track of the effect of changes that had been made. Python in combination with the Pandas and Matplotlib libraries can help with this. A lot of rank trackers already can show this kind of information, but I think it is more fun to create this with Python. The most important reason for me is multilingual.

1) Understand the data / rankings better

2) Get used to work with Python and visualization

3) Customize visualization

4) Instant speed

5) Make a shareable dashboard for stakeholders

The last point is an interesting one because not everyone in your company has access to third party SEO tools (in my case SEMrush). When putting this script and pivot table to the public (or local) web, it is possible to have instant access for everyone with a dashboard URL. And because SEO can be very difficult for some people to understand sharing performance and effect of optimizations helps creating support by different stakeholders and the board.

The full script can be downloaded on my Github
>> https://github.com/marcvh80/seo-dashboard

How to create your own actionable SEO dashboard with Python

Automate and visualition of search visibility and rank tracking

Marc van Herrikhuijzen

Written by

Curious SEO strategist! Love data science, technical SEO and getting things done.

How to create your own actionable SEO dashboard with Python

For SEOs it is mandatory to keep track on search visibility and keyword rankings in search engines. In this article I will write a step-by-step guide for creating your own dashboard using Python.

Marc van Herrikhuijzen

Written by

Curious SEO strategist! Love data science, technical SEO and getting things done.

How to create your own actionable SEO dashboard with Python

For SEOs it is mandatory to keep track on search visibility and keyword rankings in search engines. In this article I will write a step-by-step guide for creating your own dashboard using Python.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store