Visualise Search Console Data with Python and Pandas
Imagine you have a bunch of keywords freshly downloaded from Google Search Console, and you would really like to visualise their performance metrics in a way that is easy to understand and that can be send straight to the client. Plus, ideally you would really like to have not only an one-off solution, but the one which can be used over and over again.
While going through several Lynda courses on data analysis and data visualisation, I found The Data Science of Marketing course quite useful. In a very similar example Chris DallaVilla shows how to create a heatmap using Python’s Pandas library in his exploratory data analysis example. I then applied it to the Search Console data that everyone working in SEO has plenty of, and here is the result.
So, in order to do so, we will first need to import pandas — a library that provides data structures and data analysis tools. We are going to import it as pd which will make it easy for us to refer it to.
We are also going to type in matplotlib inline magic function that will help us to create visualizations within the notebook. It’s a command that essentially allows for this notebook to list out the data visualizations inline right here on the screen on the notebook.
Next step — loading our keyword data saved in a csv file. We will first create a variable name “SearchConsoleKeywords”, and assign that to the function pd.read_csv and then the path specifically to that data. To quickly see your data just type in your variable name in the next line:
Now we are going to install seaborn package for data modeling. We are going to refer to seaborn package as sns so that we don’t need to type out seaborn every time.
Let’s visualise our data now. We are going to do a KDE (Kernel density estimation) plot on this data using the variable name for our data.
We will need to do a bit of a pivot table or transform our data a little bit to allow for us to create that specific visual. So we are going to type in sns.distplot and go into copy in our variable name for our data, and look at the subset of CTR like we did before.
Now we want to pivot this data, because what we really want is to find out where the most impressions and where the most CTRs are right now. We are going to set up a new variable called MyData, and pivot this data.
We can finally visualise our data with the heatmap function.
We can now easily identify keywords with high number of received impressions and low CTR like “bbq chicken” and keywords with high CTR such as “easy banana cake” and “family friendly fish pie”. We can also see that keyword “foolproof sponge cake” has a relatively high CTR despite of receiving not even the half of the impressions if compared to the previous two.