SEO_Visibility_Dashboard.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-12T09:26:24.747028Z",
     "start_time": "2020-10-12T09:26:24.727826Z"
    }
   },
   "outputs": [],
   "source": [
    "#export .xls from SEMrush for a period / day, save to desktop\n",
    "#https://www.semrush.com/tracking/landscape/3317965.html?domain_1=expert.nl&domain_2=coolblue.nl&domain_3=wehkamp.nl&domain_4=mediamarkt.nl&domain_5=bcc.nl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-12T09:29:20.819755Z",
     "start_time": "2020-10-12T09:29:20.803795Z"
    }
   },
   "outputs": [],
   "source": [
    "#import all libraries, widgets and ignore warnings\n",
    "import win32com.client as win32\n",
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "import matplotlib as mpl\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "from ipywidgets import interactive, widgets, interact, interact_manual\n",
    "import ipywidgets as widgets\n",
    "from IPython.core.display import display, HTML\n",
    "from pivottablejs import pivot_ui\n",
    "\n",
    "import warnings\n",
    "warnings.simplefilter('ignore')"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# CAN THIS BE AUTOMATED? ⇩"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#convert .xls to .xlsx\n",
    "fname = \"C:\\\\Users\\\\{USER_NAME}\\\\{DOWNLOAD_LOCATION}\\\\{exportSEMrush.xls}\"\n",
    "excel = win32.gencache.EnsureDispatch('Excel.Application')\n",
    "wb = excel.Workbooks.Open(fname)\n",
    "#fileformet 51 == xlsx format\n",
    "wb.SaveAs(\"C:\\\\Users\\\\{USER_NAME}}\\\\Desktop\\\\PythonProjects\\\\reporting\\\\rankings_converted\\\\20200930\", \n",
    "          FileFormat = 51)\n",
    "wb.Close()\n",
    "excel.Application.Quit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-09T14:40:47.577258Z",
     "start_time": "2020-10-09T14:40:47.329920Z"
    }
   },
   "outputs": [],
   "source": [
    "#read .xlsx into dataframe\n",
    "df_20200930 = pd.read_excel(\"./rankings_converted/20200930.xlsx\", header=7)\n",
    "#replace empty landing pages\n",
    "df_20200930.replace(r'\\s+',np.nan,regex=True).replace('',np.nan, inplace=True)\n",
    "#add date to columns, date should be changed manually\n",
    "df_20200930['Datum'] = '2020-9-30'\n",
    "#to avoid a warning\n",
    "pd.options.mode.chained_assignment = None \n",
    "#date to datetime64 dtype\n",
    "df_20200930['Datum'] =  pd.to_datetime(df_20200930['Datum'], format='%Y%m%d', infer_datetime_format=True)\n",
    "#rename columns\n",
    "df_20200930.rename(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'}, inplace=True)\n",
    "#define which columns will be used\n",
    "use_cols_20200930 = df_20200930[['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']]\n",
    "#check if it works\n",
    "df_20200930.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#save to new .xlsx file\n",
    "use_cols_20200930.to_excel('./use_cols/20200930.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-12T09:29:30.946206Z",
     "start_time": "2020-10-12T09:29:24.380349Z"
    }
   },
   "outputs": [],
   "source": [
    "#combine all files from directory\n",
    "files = [file for file in os.listdir('./use_cols/')]\n",
    "workfile = pd.DataFrame()\n",
    "for file in files:\n",
    "    df = pd.read_excel('./use_cols/'+file)\n",
    "    workfile = pd.concat([workfile, df])\n",
    "#check results\n",
    "workfile.info()"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# OPTIONAL ⇩"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-12T09:29:55.616832Z",
     "start_time": "2020-10-12T09:29:55.175001Z"
    }
   },
   "outputs": [],
   "source": [
    "#categorize all keywords from txt file\n",
    "tags = pd.read_csv('./workfile/tags.txt', sep=';')\n",
    "#merge tags on keywords, check and export to new .csv (workfile)\n",
    "output = workfile.merge(tags, on='Tags')\n",
    "output.head()\n",
    "output.to_csv('./workfile/output.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CREATE VISIBILITY REPORT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-12T09:29:59.894290Z",
     "start_time": "2020-10-12T09:29:59.405237Z"
    }
   },
   "outputs": [],
   "source": [
    "#open workfile\n",
    "df = pd.read_csv('./workfile/output.csv',) \n",
    "df.drop('Unnamed: 0', axis=1, inplace=True) #df.drop('Unnamed: 0.1', axis=1, inplace=True) <<-- somehow this gives errors...\n",
    "#replace empty landing pages\n",
    "df.replace(r'\\s+',np.nan,regex=True).replace('',np.nan, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-12T09:30:00.283631Z",
     "start_time": "2020-10-12T09:30:00.049260Z"
    }
   },
   "outputs": [],
   "source": [
    "#create visibility_dataframe for each website\n",
    "#VIS_EXPERT\n",
    "df_vis_expert = pd.read_excel('./visibility_competitors/vis_expert.xlsx')\n",
    "df_vis_calc = df.merge(df_vis_expert,on='Rankings_Expert', how='outer')\n",
    "df_vis_calc['Visibility_Expert'] = df_vis_calc.Visibility_Expert.astype(float)\n",
    "\n",
    "#VIS_BCC\n",
    "df_vis_bcc = pd.read_excel('./visibility_competitors/vis_bcc.xlsx')\n",
    "df_vis_calc2 = df_vis_calc.merge(df_vis_bcc,on='Rankings_BCC', how='outer')\n",
    "df_vis_calc2['Visibility_BCC'] = df_vis_calc2.Visibility_BCC.astype(float)\n",
    "\n",
    "#VIS_Coolblue\n",
    "df_vis_coolblue = pd.read_excel('./visibility_competitors/vis_coolblue.xlsx')\n",
    "df_vis_calc3 = df_vis_calc2.merge(df_vis_coolblue,on='Rankings_Coolblue', how='outer')\n",
    "df_vis_calc3['Visibility_Coolblue'] = df_vis_calc3.Visibility_Coolblue.astype(float)\n",
    "\n",
    "#VIS_Mediamarkt\n",
    "df_vis_mediamarkt = pd.read_excel('./visibility_competitors/vis_mediamarkt.xlsx')\n",
    "df_vis_calc4 = df_vis_calc3.merge(df_vis_mediamarkt,on='Rankings_Mediamarkt', how='outer')\n",
    "df_vis_calc4['Visibility_Mediamarkt'] = df_vis_calc4.Visibility_Mediamarkt.astype(float)\n",
    "\n",
    "#VIS_Wehkamp\n",
    "df_vis_wehkamp = pd.read_excel('./visibility_competitors/vis_wehkamp.xlsx')\n",
    "df_vis_calc5 = df_vis_calc4.merge(df_vis_wehkamp,on='Rankings_Wehkamp', how='outer')\n",
    "df_vis_calc5['Visibility_Wehkamp'] = df_vis_calc5.Visibility_Wehkamp.astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-12T09:30:00.715489Z",
     "start_time": "2020-10-12T09:30:00.648654Z"
    }
   },
   "outputs": [],
   "source": [
    "#rename file and do some editing\n",
    "df_workfile = df_vis_calc5.dropna(subset=['Keyword'])\n",
    "df_workfile.rename(columns={'Tag2':'Categorie'}, inplace=True)\n",
    "#reset index\n",
    "df_workfile.reset_index()\n",
    "#check if it works\n",
    "df_workfile.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-12T09:30:01.815982Z",
     "start_time": "2020-10-12T09:30:01.665550Z"
    }
   },
   "outputs": [],
   "source": [
    "#create visibilty_dataframe for all websites and export to .csv\n",
    "visibility = df_workfile[['Datum','Keyword','Tags','Categorie','Visibility_Expert','Visibility_Coolblue','Visibility_Mediamarkt','Visibility_Wehkamp','Visibility_BCC']]\n",
    "visibility.fillna(0, inplace=True)\n",
    "visibility.to_csv('./workfile/visibility.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-12T09:30:02.479769Z",
     "start_time": "2020-10-12T09:30:02.288357Z"
    }
   },
   "outputs": [],
   "source": [
    "#we are gonna use the following export later on:\n",
    "ranking = df_workfile[['Datum','Keyword','Search_Volume','Tags','Categorie','Rankings_Expert','Rankings_Coolblue','Rankings_Mediamarkt','Rankings_Wehkamp','Rankings_BCC']]\n",
    "ranking.fillna(0, inplace=True)\n",
    "ranking.to_csv('./workfile/ranking.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-12T09:30:03.034008Z",
     "start_time": "2020-10-12T09:30:02.935953Z"
    }
   },
   "outputs": [],
   "source": [
    "#new dataframe\n",
    "dataset = pd.read_csv('./workfile/visibility.csv')\n",
    "#with some edits\n",
    "dataset.fillna(0, inplace=True)\n",
    "# vis.drop('Unnamed: 0', axis=1, inplace=True)\n",
    "dataset.groupby(['Categorie']).mean().reset_index()\n",
    "dataset['Datum'] =  pd.to_datetime(visibility['Datum'], format='%Y%m%d', infer_datetime_format=True)\n",
    "dataset.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# NOW THE FUN PART"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-12T09:30:04.501500Z",
     "start_time": "2020-10-12T09:30:04.231709Z"
    }
   },
   "outputs": [],
   "source": [
    "#plot is like it's hot\n",
    "pivot = dataset.groupby(['Datum']).mean()\n",
    "pivot.plot(kind='line',title='Visibility Overtime')\n",
    "plt.xlabel('Period', fontsize=14)\n",
    "plt.ylabel('Search Visibility in Google', fontsize=14)\n",
    "plt.style.use('Solarize_Light2')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# NOW EVEN MORE FUN! INTERACTIVE PLOTTING ^_^"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-12T09:30:29.030966Z",
     "start_time": "2020-10-12T09:30:28.706299Z"
    }
   },
   "outputs": [],
   "source": [
    "#create a widget\n",
    "tags = widgets.Dropdown(\n",
    "    options=['All'] + list(dataset['Categorie'].unique()),\n",
    "    value='All',\n",
    "    description='Category:',\n",
    "        \n",
    ")\n",
    "\n",
    "def plotit(tags):\n",
    "    if 0 > 1:\n",
    "        print('Nothing to show')  \n",
    "    else: \n",
    "        dataset2 = dataset.copy()\n",
    "        if tags != 'All':\n",
    "            dataset2 = dataset2[dataset2.Categorie == tags]\n",
    "            plt.style.use('Solarize_Light2')\n",
    "            plt.show()\n",
    "        \n",
    "        if len(dataset2) > 0:\n",
    "            dataset2.groupby(['Datum']).mean().plot(kind='line',figsize=(14,4))\n",
    "            plt.xlabel('Period', fontsize=14)\n",
    "            plt.ylabel('Search Visibility', fontsize=14)\n",
    "            plt.title('Search Visibility in Google for {}'.format(tags), fontsize=20)\n",
    "            plt.style.use('Solarize_Light2')\n",
    "            plt.show()\n",
    "        else: \n",
    "            print('Nothing to show')\n",
    "            \n",
    "#get a 100% width view for the full experience on this one (optional)\n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))\n",
    "\n",
    "interactive(plotit, tags=tags)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CREATE RANKING REPORT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-12T09:31:44.102103Z",
     "start_time": "2020-10-12T09:31:44.000638Z"
    }
   },
   "outputs": [],
   "source": [
    "#get the data in place\n",
    "rnk = pd.read_csv('./workfile/ranking.csv') # <<-- remember we created this file earlier? ^_^\n",
    "rnk.fillna(0, inplace=True)\n",
    "rnk['Datum'] =  pd.to_datetime(rnk['Datum'], format='%Y%m%d', infer_datetime_format=True)\n",
    "rnk[rnk.eq(0)] = np.nan\n",
    "rnk.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-12T09:31:45.486546Z",
     "start_time": "2020-10-12T09:31:45.162379Z"
    }
   },
   "outputs": [],
   "source": [
    "#create a widget\n",
    "tags = widgets.Dropdown(\n",
    "    options=['All'] + list(rnk['Categorie'].unique()),\n",
    "    value='All',\n",
    "    description='Category:',        \n",
    ")\n",
    "\n",
    "def plotit(tags):\n",
    "    if 0 > 1:\n",
    "        print('Nothing to show')  \n",
    "    else: \n",
    "        dataset2 = rnk.copy()\n",
    "        if tags != 'All':\n",
    "            dataset2 = dataset2[dataset2.Categorie == tags]\n",
    "            plt.style.use('Solarize_Light2')\n",
    "            plt.show()\n",
    "        \n",
    "        if len(dataset2) > 0:\n",
    "            dataset2.drop('Search_Volume', axis=1, inplace=True)\n",
    "            dataset2.groupby(['Datum']).mean().plot(kind='line',figsize=(14,4))\n",
    "            plt.xlabel('Period', fontsize=14)\n",
    "            plt.ylabel('Rankings in Google', fontsize=14)\n",
    "            plt.gca().invert_yaxis()\n",
    "            plt.title('Rankings in Google for category {}'.format(tags), fontsize=20)\n",
    "            plt.style.use('Solarize_Light2')\n",
    "            plt.show()\n",
    "        else: \n",
    "            print('Nothing to show')\n",
    "\n",
    "#get a 100% width view for the full experience on this one (optional)\n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))\n",
    "\n",
    "interactive(plotit, tags=tags)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-12T09:31:46.924487Z",
     "start_time": "2020-10-12T09:31:46.587282Z"
    }
   },
   "outputs": [],
   "source": [
    "## WE CAN DO THE SAME FOR ALL EACH KEYWORD ##\n",
    "kws = widgets.Dropdown(\n",
    "    options=['All'] + list(rnk['Keyword'].unique()),\n",
    "    value='All',\n",
    "    description='Keyword:',        \n",
    ")\n",
    "\n",
    "def plotit2(kws):\n",
    "    if 0 > 1:\n",
    "        print('Nothing to show')  \n",
    "    else: \n",
    "        dataset2 = rnk.copy()\n",
    "        if kws != 'All':\n",
    "            dataset2 = dataset2[dataset2.Keyword == kws]\n",
    "            plt.style.use('Solarize_Light2')\n",
    "            plt.show()\n",
    "        \n",
    "        if len(dataset2) > 0:\n",
    "            dataset2.drop('Search_Volume', axis=1, inplace=True)\n",
    "            dataset2.groupby(['Datum']).mean().plot(kind='line',figsize=(14,4))\n",
    "            plt.xlabel('Period', fontsize=14)\n",
    "            plt.ylabel('Rankings in Google', fontsize=14)\n",
    "            plt.gca().invert_yaxis()\n",
    "            plt.title('Rankings in Google for keyword [{}]'.format(kws), fontsize=20)\n",
    "            plt.style.use('Solarize_Light2')\n",
    "            plt.show()\n",
    "        else: \n",
    "            print('Nothing to show') \n",
    "            \n",
    "#get a 100% width view for the full experience on this one (optiional)\n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))\n",
    "\n",
    "interactive(plotit, tags=tags)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# PIVOT INTO ACTIONABLE AND INTERACTIVE TABLE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-12T09:31:48.406162Z",
     "start_time": "2020-10-12T09:31:48.322370Z"
    }
   },
   "outputs": [],
   "source": [
    "#do some data tweaks\n",
    "pivot_df = pd.read_csv('./workfile/ranking.csv')\n",
    "pivot_df[pivot_df.eq(0)] = np.nan\n",
    "pivot_df[\"Search_Volume\"] = pivot_df[\"Search_Volume\"].astype(int)\n",
    "pivot_df['Datum'] =  pd.to_datetime(pivot_df['Datum'], format='%Y%m%d', infer_datetime_format=True)\n",
    "pivot_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-12T09:32:45.987622Z",
     "start_time": "2020-10-12T09:32:45.961692Z"
    }
   },
   "outputs": [],
   "source": [
    "rankings = pivot_df[['Datum', 'Keyword', 'Categorie', 'Search_Volume', 'Tags', 'Rankings_BCC']]\n",
    "rankings.sort_values(by='Datum')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-12T09:52:58.967065Z",
     "start_time": "2020-10-12T09:52:58.634993Z"
    }
   },
   "outputs": [],
   "source": [
    "#create interactive pivot (to see in new tab: go to http://localhost:{PORT}/files/{YOUR_PROJECT_LOCATION}/pivottablejs.html)\n",
    "pivot_ui(rankings,      \n",
    "    cols= [\"Datum\"],\n",
    "    rows= [\"Search_Volume\"],\n",
    "    rowOrder= \"value_a_to_z\",\n",
    "    rendererOptions= {\n",
    "        \"c3\": { \"data\": {\"colors\": {\n",
    "            \"Liberal\": '#dc3912', \"Conservative\": '#3366cc', \"NDP\": '#ff9900',\n",
    "            \"Green\":'#109618', 'Bloc Quebecois': '#990099'\n",
    "        }}}\n",
    "    }\n",
    ")"
   ]
  }
 ],
 "metadata": {
  "hide_input": false,
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}