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: Collect and clean the exports from SEMrush (or any other ranking export file) and create a useful work file to continue Add a visibility calculation to the data and plot this over categories Plot rankings overtime per category and keyword 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. win32com.client win32 os pandas pd numpy np re matplotlib mpl matplotlib.pyplot plt %matplotlib inline ipywidgets interactive, widgets, interact, interact_manual ipywidgets widgets IPython.core.display display, HTML pivottablejs pivot_ui warnings warnings.simplefilter( ) #import all libraries and widgets import as import import as import as import import as import as from import import as from import from import #and let’s ignore warnings import 'ignore' 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 😊. df_20200930 = pd.read_excel( , header= ) df_20200930.replace( ,np.nan,regex= ).replace( ,np.nan, inplace= ) df_20200930[Date] = pd.options.mode.chained_assignment = df_20200930[Date] = pd.to_datetime(df_20200930[Date], format= , infer_datetime_format= ) df_20200930.rename(columns={ : , : , : , : , : , : , : , : , : , : , : , : }, inplace= ) use_cols_20200930 = df_20200930[[ , , , , , , , , , , , , , , ]] df_20200930.head() #read .xlsx into dataframe "./rankings_converted/20200930.xlsx" 7 #replace empty landing pages r'\s+' True '' True #add date to columns, date should be changed manually '2020-9-30' #to avoid a warning None #date to datetime64 dtype '%Y%m%d' True #create more readable names for the columns 'tags' 'Tags' '*.expert.nl/*_20200930' 'Rankings_Expert' '*.expert.nl/*_20200930_landing' 'Expert_url' '*.bcc.nl/*_20200930' 'Rankings_BCC' '*.bcc.nl/*_20200930_landing' 'BCC_url' '*.coolblue.nl/*_20200930' 'Rankings_Coolblue' '*.coolblue.nl/*_20200930_landing' 'Coolblue_url' '*.mediamarkt.nl/*_20200930' 'Rankings_Mediamarkt' '*.mediamarkt.nl/*_20200930_landing' 'Mediamarkt_url' '*.wehkamp.nl/*_20200930' 'Rankings_Wehkamp' '*.wehkamp.nl/*_20200930_landing' 'Wehkamp_url' 'Search Volume' 'Search_Volume' True #define which columns will be used 'Datum' 'Keyword' 'Tags' 'Rankings_Expert' 'Expert_url' 'Rankings_BCC' 'BCC_url' 'Rankings_Coolblue' 'Coolblue_url' 'Rankings_Mediamarkt' 'Mediamarkt_url' 'Rankings_Wehkamp' 'Wehkamp_url' 'CPC' 'Search_Volume' #check if it works After that we can save this to a .xlsx file with customized columns. For these files I have created a new folder: [./use_cols]. use_cols_20200930.to_excel( ) #save to new .xlsx file './use_cols/20200930.xlsx' 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. data_20200129 = pd.read_excel( ) data_20200205 = pd.read_excel( ) data_20200212 = pd.read_excel( ) data_20200219 = pd.read_excel( ) data_20200226 = pd.read_excel( ) data_20200304 = pd.read_excel( ) data_20200311 = pd.read_excel( ) data_20200318 = pd.read_excel( ) data_20200325 = pd.read_excel( ) data_20200401 = pd.read_excel( ) data_20200408 = pd.read_excel( ) data_20200415 = pd.read_excel( ) data_20200422 = pd.read_excel( ) data_20200429 = pd.read_excel( ) data_20200506 = pd.read_excel( ) data_20200513 = pd.read_excel( ) data_20200520 = pd.read_excel( ) data_20200527 = pd.read_excel( ) data_20200603 = pd.read_excel( ) data_20200610 = pd.read_excel( ) data_20200617 = pd.read_excel( ) data_20200624 = pd.read_excel( ) data_20200701 = pd.read_excel( ) data_20200708 = pd.read_excel( ) data_20200715 = pd.read_excel( ) data_20200722 = pd.read_excel( ) data_20200729 = pd.read_excel( ) data_20200805 = pd.read_excel( ) data_20200812 = pd.read_excel( ) data_20200819 = pd.read_excel( ) data_20200826 = pd.read_excel( ) data_20200902 = pd.read_excel( ) data_20200909 = pd.read_excel( ) data_20200916 = pd.read_excel( ) data_20200923 = pd.read_excel( ) data_20200930 = pd.read_excel( ) #gather all files './use_cols/20200129.xlsx' './use_cols/20200205.xlsx' './use_cols/20200212.xlsx' './use_cols/20200219.xlsx' './use_cols/20200226.xlsx' './use_cols/20200304.xlsx' './use_cols/20200311.xlsx' './use_cols/20200318.xlsx' './use_cols/20200325.xlsx' './use_cols/20200401.xlsx' './use_cols/20200408.xlsx' './use_cols/20200415.xlsx' './use_cols/20200422.xlsx' './use_cols/20200429.xlsx' './use_cols/20200506.xlsx' './use_cols/20200513.xlsx' './use_cols/20200520.xlsx' './use_cols/20200527.xlsx' './use_cols/20200603.xlsx' './use_cols/20200610.xlsx' './use_cols/20200617.xlsx' './use_cols/20200624.xlsx' './use_cols/20200701.xlsx' './use_cols/20200708.xlsx' './use_cols/20200715.xlsx' './use_cols/20200722.xlsx' './use_cols/20200729.xlsx' './use_cols/20200805.xlsx' './use_cols/20200812.xlsx' './use_cols/20200819.xlsx' './use_cols/20200826.xlsx' './use_cols/20200902.xlsx' './use_cols/20200909.xlsx' './use_cols/20200916.xlsx' './use_cols/20200923.xlsx' './use_cols/20200930.xlsx' The sad part for me is that we don’t use SEMrush API and therefore this needs to be updated manually per week. Boehhh! files = [file file os.listdir( )] workfile = pd.DataFrame() file files: df = pd.read_excel( +file) workfile = pd.concat([workfile, df]) workfile.info() #combine all files from directory for in './use_cols/' for in './use_cols/' #check results 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 tags = pd.read_csv( , sep= ) output = workfile.merge(tags, on= ) output.head() output.to_csv( , index= ) #categorize all keywords from txt file './workfile/tags.txt' ';' #merge tags on keywords, check and export to new .csv (which will be a work file) 'Tags' './workfile/output.csv' False 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. df = pd.read_csv( ,) df.drop( , axis= , inplace= ) df.replace( ,np.nan,regex= ).replace( ,np.nan, inplace= ) #open work file and get rid of empty fields './workfile/output.csv' 'Unnamed: 0' 1 True #replace empty landing pages r'\s+' True '' True 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 df_vis_expert = pd.read_excel( ) df_vis_calc = df.merge(df_vis_expert,on= , how= ) df_vis_calc[ ] = df_vis_calc.Visibility_Expert.astype(float) df_vis_bcc = pd.read_excel( ) df_vis_calc2 = df_vis_calc.merge(df_vis_bcc,on= , how= ) df_vis_calc2[ ] = df_vis_calc2.Visibility_BCC.astype(float) df_vis_coolblue = pd.read_excel( ) df_vis_calc3 = df_vis_calc2.merge(df_vis_coolblue,on= , how= ) df_vis_calc3[ ] = df_vis_calc3.Visibility_Coolblue.astype(float) df_vis_mediamarkt = pd.read_excel( ) df_vis_calc4 = df_vis_calc3.merge(df_vis_mediamarkt,on= , how= ) df_vis_calc4[ ] = df_vis_calc4.Visibility_Mediamarkt.astype(float) df_vis_wehkamp = pd.read_excel( ) df_vis_calc5 = df_vis_calc4.merge(df_vis_wehkamp,on= , how= ) df_vis_calc5[ ] = df_vis_calc5.Visibility_Wehkamp.astype(float) df_workfile = df_vis_calc5.dropna(subset=[ ]) df_workfile.rename(columns={ : }, inplace= ) df_workfile.reset_index() df_workfile.head() visibility = df_workfile[[ , , , , , , , , ]] visibility.fillna( , inplace= ) visibility.to_csv( , index= ) #create visibility_dataframe for each website ##visibility_Expert './visibility_competitors/vis_expert.xlsx' 'Rankings_Expert' 'outer' 'Visibility_Expert' ##visibility_BCC './visibility_competitors/vis_bcc.xlsx' 'Rankings_BCC' 'outer' 'Visibility_BCC' ##visibility_Coolblue './visibility_competitors/vis_coolblue.xlsx' 'Rankings_Coolblue' 'outer' 'Visibility_Coolblue' ##visibility_Mediamarkt './visibility_competitors/vis_mediamarkt.xlsx' 'Rankings_Mediamarkt' 'outer' 'Visibility_Mediamarkt' ##visibility_Wehkamp './visibility_competitors/vis_wehkamp.xlsx' 'Rankings_Wehkamp' 'outer' 'Visibility_Wehkamp' #rename file and do some editing 'Keyword' 'Tag2' 'Categorie' True #reset index #check if it works #create visibilty_dataframe for all websites and export to .csv 'Datum' 'Keyword' 'Tags' 'Categorie' 'Visibility_Expert' 'Visibility_Coolblue' 'Visibility_Mediamarkt' 'Visibility_Wehkamp' 'Visibility_BCC' 0 True './workfile/visibility.csv' False 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. ranking = df_workfile[[ , , , , , , , , , ]] ranking.fillna( , inplace= ) ranking.to_csv( , index= ) #we are going to use the following export later on 'Datum' 'Keyword' 'Search_Volume' 'Tags' 'Categorie' 'Rankings_Expert' 'Rankings_Coolblue' 'Rankings_Mediamarkt' 'Rankings_Wehkamp' 'Rankings_BCC' 0 True './workfile/ranking.csv' False A final tweak and we are ready for some plotting! Just to keep things organized a new DataFrame is created for plotting visibility. dataset = pd.read_csv( ) dataset.fillna( , inplace= ) dataset.groupby([ ]).mean().reset_index() dataset[ ] = pd.to_datetime(visibility[ ], format= , infer_datetime_format= ) vis.info() #new dataframe './workfile/visibility.csv' #with some edits 0 True 'Categorie' 'Datum' 'Datum' '%Y%m%d' True Plot it like it's hot! pivot = dataset.groupby([ ]).mean() pivot.plot(kind= ,title= ) plt.xlabel( , fontsize= ) plt.ylabel( , fontsize= ) plt.style.use( ) plt.show() 'Datum' 'line' 'Visibility Overtime' 'Period' 14 'Visibility in Google Organic Search' 14 'Solarize_Light2' 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( options=[ ] + list(dataset[ ].unique()), value= , description= , ) > : print( ) : dataset2 = dataset.copy() tags != : dataset2 = dataset2[dataset2.Categorie == tags] plt.style.use( ) plt.show() len(dataset2) > : dataset2.groupby([ ]).mean().plot(kind= ,figsize=( , )) plt.xlabel( , fontsize= ) plt.ylabel( , fontsize= ) plt.title( .format(tags), fontsize= ) plt.style.use( ) plt.show() : print( ) display(HTML( )) interactive(plotit, tags=tags) 'All' 'Category' 'All' 'Category:' : def plotit (tags) if 0 1 'Nothing to show' else if 'All' 'Solarize_Light2' if 0 'Datum' 'line' 14 4 'Period' 14 'Visibility in Google Organic Search' 14 'Search Visibility in Google for {}' 20 'Solarize_Light2' else 'Nothing to show' #personally, I like showing this full-width with some styling "<style>.container { width:100% !important; }</style>" #and call the interactive widget Let me add some screenshots of the output. 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( ) rnk.fillna( , inplace= ) rnk[ ] = pd.to_datetime(rnk[ ], format= , infer_datetime_format= ) rnk[rnk.eq( )] = np.nan rnk.info() './workfile/ranking.csv' 0 True 'Datum' 'Datum' '%Y%m%d' True 0 Also, with keyword rankings, a similar widget can be very useful. tags = widgets.Dropdown( options=[ ] + list(rnk[ ].unique()), value= , description= , ) > : print( ) : dataset2 = rnk.copy() tags != : dataset2 = dataset2[dataset2.Categorie == tags] plt.show() len(dataset2) > : dataset2.drop( , axis= , inplace= ) dataset2.groupby([ ]).mean().plot(kind= ,figsize=( , )) plt.xlabel( , fontsize= ) plt.ylabel( , fontsize= ) plt.gca().invert_yaxis() plt.title( .format(tags), fontsize= ) plt.style.use( ) plt.show() : print( ) interactive(plotit, tags=tags) 'All' 'Categorie' 'All' 'Category:' : def plotit (tags) if 0 1 'Nothing to show' else if 'All' if 0 'Search_Volume' 1 True 'Datum' 'line' 14 4 'Period' 14 'Rankings in Google' 14 'Rankings in Google for category {}' 20 'Solarize_Light2' else 'Nothing to show' Again, here are some examples of the output. If you notice some improvements or drops in rankings per category, a deep dive per keyword can be very interesting! kws = widgets.Dropdown( options=[ ] + list(rnk[ ].unique()), value= , description= , ) > : print( ) : dataset2 = rnk.copy() kws != : dataset2 = dataset2[dataset2.Keyword == kws] plt.show() len(dataset2) > : dataset2.drop( , axis= , inplace= ) dataset2.groupby([ ]).mean().plot(kind= ,figsize=( , )) plt.xlabel( , fontsize= ) plt.ylabel( , fontsize= ) plt.gca().invert_yaxis() plt.title( .format(kws), fontsize= ) plt.style.use( ) plt.show() : print( ) interactive(plotit2, kws=kws) 'All' 'Keyword' 'All' 'Keyword:' : def plotit2 (kws) if 0 1 'Nothing to show' else if 'All' if 0 'Search_Volume' 1 True 'Datum' 'line' 14 4 'Period' 14 'Rankings in Google' 14 'Rankings in Google for keyword [{}]' 20 'Solarize_Light2' else 'Nothing to show' 2 screenshots of rankings per keyword overtime. 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 isthat 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( ) pivot_df[pivot_df.eq( )] = np.nan pivot_df[ ] = pivot_df[ ].astype(int) pivot_df[ ] = pd.to_datetime(pivot_df[ ], format= , infer_datetime_format= ) './workfile/ranking.csv' 0 "Search_Volume" "Search_Volume" 'Datum' 'Datum' '%Y%m%d' True 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=[ , , ],columns = [ ],values=[ ]) rankings = pivot_df[[ , , , , , ]] rankings.sort_values(by= ) 'Keyword' 'Categorie' 'Search_Volume' 'Datum' 'Rankings_BCC' 'Datum' 'Keyword' 'Categorie' 'Search_Volume' 'Tags' 'Rankings_BCC' 'Datum' 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 Playing a bit with some filters it is clear that some fluctuations are going on for the which can lead to actionable things to work or focus on! washing machine category of BCC 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. Understand the data / rankings better Get used to work with Python and visualization Customize visualization Instant speed 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 from my Github >> . https://github.com/marcvh80/seo-dashboard Also published at https://medium.com/how-to-create-your-own-actionable-seo-dashboard/how-to-create-your-own-actionable-seo-dashboard-with-python-86d5767dbd90
