Visualise_Search_Console_Coverage_Reports.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Visualise Search Console Coverage Reports.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "authorship_tag": "ABX9TyNdEs+mcPgkAzhjiEfHDzDp",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/searchsolved/Twittorials/blob/master/Visualise_Search_Console_Coverage_Reports.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "l7pb60AQVvBx"
      },
      "source": [
        "# Visualise Search Console Coverage Reports\n",
        "Spot issues fast by visualising your Search Console Coverage Reports. Especially useful to visualise low quality folder paths at a glance (crawled, not indexed).\n",
        "\n",
        "## How to use:\n",
        "Run all cells and upload a Search Console Coverage Report in *Excel* format. \n",
        "\n",
        "### Options:\n",
        "Option to set a starting folder depth (Useful to step through cctld folders)\n",
        "\n",
        "### Output\n",
        "Exports a Treemap and Sunburst Chart from the coverage report\n",
        "\n",
        "*   Interactive Treemap .html file\n",
        "*   Interactive Sunburst .html file\n",
        "\n",
        "https://twitter.com/LeeFootSEO <--- Follow for more scripts like this."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FKje1EZxWwF-"
      },
      "source": [
        "# set optional folder depth offset # default = 1\n",
        "folder_start = 1"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "BIw9qOYhW4I1"
      },
      "source": [
        "# Custom Title for Reports\n",
        "title = \"Coverage Report\""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "drHLv6oXWCaE"
      },
      "source": [
        "!pip install pandas\n",
        "!pip install pip install plotly==5.0.0"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0M7lRgg2VwwE"
      },
      "source": [
        "import re\n",
        "import urllib.parse as urlparse\n",
        "from collections import Counter\n",
        "from glob import glob\n",
        "\n",
        "import matplotlib.pyplot as plt\n",
        "import nltk\n",
        "import pandas as pd\n",
        "import plotly.express as px\n",
        "from nltk.corpus import stopwords\n",
        "from wordcloud import WordCloud\n",
        "from google.colab import files"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "NycXY0zMWFWB"
      },
      "source": [
        "# upload any Search Console Coverage Report - Must be an Excel file export - csv not accepted!\n",
        "uploaded = files.upload()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lo3ZakizWZZ1"
      },
      "source": [
        "filename = list(uploaded.keys())[0]  # get the filename from the upload\n",
        "df = pd.read_excel(filename, 'Table')  # choose the right sheet from Excel\n",
        "nltk.download('stopwords')\n",
        "print(df.head())"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8RSp39utXAYf"
      },
      "source": [
        "df['total_urls'] = 1  # used to count in the grouping\n",
        "df[\"protocol\"], df[\"domain\"], df[\"path\"], df[\"query\"], df[\"fragment\"] = zip(*df[\"URL\"].map(urlparse.urlsplit))\n",
        "\n",
        "# make temp df to split with expand=true and then merge back by the index.\n",
        "df['path'] = df['path'].apply(lambda x: x.replace(\"//\", \"/\"))\n",
        "df2 = df['path'].str.split('/', expand=True)\n",
        "df2 = df2.fillna(\"missing_value\")\n",
        "df = pd.merge(df, df2, left_index=True, right_index=True)\n",
        "next_folder = folder_start + 1"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zjXnsiSQXGLl"
      },
      "source": [
        "# concat 0 + 1 path to merge and group by each depth. e.g. level1  + level 2 together.\n",
        "df['level_1+2'] = \"/\" + df[next_folder]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "e2hX9njak7AY"
      },
      "source": [
        "cnt = Counter()\n",
        "english_stopwords = set(stopwords.words('english'))\n",
        "\n",
        "for path in df['level_1+2']:\n",
        "  words = re.split(\"[-/]\", path)\n",
        "  for word in words:\n",
        "    if len(word) > 0 and word not in english_stopwords and not word.isdigit():\n",
        "      cnt[word] += 1\n",
        "\n",
        "cnt.most_common(25)\n",
        "\n",
        "word_cloud = [x[0] for x in cnt.most_common(25)]\n",
        "word_cloud_obj = WordCloud(max_words=25, background_color=\"white\").generate(\" \".join(word_cloud))\n",
        "\n",
        "plt.imshow(word_cloud_obj, interpolation='bilinear')\n",
        "plt.axis(\"off\")\n",
        "plt.savefig('word_cloud.png', dpi=300)\n",
        "plt.show()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "UZzDJZmjXI5d"
      },
      "source": [
        "try:\n",
        "    treemap_fig = px.treemap(df, path=[px.Constant(title), folder_start, next_folder, 'URL'],values='total_urls', color_discrete_sequence=px.colors.qualitative.Antique)\n",
        "except ValueError:\n",
        "    print(\"Not all URLs can go to a url depth of\", folder_start, \"using default depths instead.\")\n",
        "    folder_start = 1\n",
        "    next_folder = 2\n",
        "    treemap_fig = px.treemap(df, path=[px.Constant(title), folder_start, next_folder, 'URL'], values='total_urls', color_discrete_sequence=px.colors.qualitative.Antique)\n",
        "    pass\n",
        "\n",
        "treemap_fig.show()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wwUkOCzwXKpM"
      },
      "source": [
        "# plot a sun burst\n",
        "try:\n",
        "    sunburst_fig = px.sunburst(df, path=[px.Constant(title), folder_start, next_folder, 'URL'],values='total_urls', color_discrete_sequence=px.colors.qualitative.Antique)\n",
        "except ValueError:\n",
        "    print(\"Not all URLs can go to a url depth of\", folder_start, \"using default depths instead.\")\n",
        "    folder_start = 1\n",
        "    next_folder = 2\n",
        "    sun_burst_fig = px.treemap(df, path=[px.Constant(\"Coverage Report\", ), folder_start, next_folder, 'URL'], values='total_urls', color_discrete_sequence=px.colors.qualitative.Antique)\n",
        "    pass\n",
        "\n",
        "sunburst_fig.show()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YIBR3KgIZe3I"
      },
      "source": [
        "Remember to Click ''Allow Downloading of Multiple Files' in Chrome when Prompted!"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WC-ZkUzJXLR0"
      },
      "source": [
        "# download the sunburst .html chart\n",
        "sunburst_fig.write_html(\"coverage_report_sunburst.html\")\n",
        "files.download(\"coverage_report_sunburst.html\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6auYjJkuZtMg"
      },
      "source": [
        "# download the treemap .html chart\n",
        "treemap_fig.write_html(\"coverage_report_treemap.html\")\n",
        "files.download(\"coverage_report_treemap.html\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "58dkq79YmcZs"
      },
      "source": [
        "# download the word_cloud chart\n",
        "files.download(\"word_cloud.png\")"
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}