Site Migration QA Automation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1>Speed Up Your Site Launch, Redesign, or Migration QA with Python</h1>\n",
    "\n",
    "<p>When it comes to fast-paced agency life, both time and efficiency are crucial to tackling day to day tasks no matter how big or small. One of the most time consuming tasks that can me done in the SEO world is quality assurance of pages or sites when they launch. Wheter you are working with a small handfull of pages, or a full site launch, this is a breakdown of how to expedite the process of making sure your content is in the right place!</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Required Libraries \n",
    "import re\n",
    "import urllib\n",
    "import requests\n",
    "import pandas as pd\n",
    "from bs4 import BeautifulSoup as bs\n",
    "from tqdm.notebook import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Required External Resources:\n",
    "# Screaming Frog\n",
    "# *Optional* Google PageSpeed Insights API Key (https://developers.google.com/speed/docs/insights/v5/get-started)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2>What we will be covering</h2>\n",
    "<h3>Crawling and Indexing</h3>\n",
    "<br>\n",
    "        <li>HTTPS</li>\n",
    "        <li>Canonical Tags and Noindex Tags</li>\n",
    "        <li>robots.txt</li>\n",
    "        <li>XML Sitemap</li>\n",
    "\n",
    "<h3>Meta Data</h3>\n",
    "<br>\n",
    "    <li>Titles</li>\n",
    "    <li>Descriptions</li>\n",
    "\n",
    "<h3>Site Speed</h3>\n",
    "<br>\n",
    "    <li>PageSpeed Score\n",
    "    <li>First Contentful Paint</li>\n",
    "    <li>Largest Contentful Paint</li>\n",
    "    <li>Speed Index</li>\n",
    "    <li>Time to Interactive</li>\n",
    "\n",
    "<h3>(っ◔◡◔)っ ♥ Putting it all into a easy to digest report ♥</h3>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2>Let's talk about some of the functions we will be using</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check for XML Sitemap\n",
    "def sitemap_check(url):\n",
    "    r = requests.get(url)\n",
    "    if r.status_code == 200:\n",
    "        sitemap = bs(r.content, 'lxml')\n",
    "        links = [element.text for element in sitemap.find_all('loc')]\n",
    "        return links\n",
    "    else:\n",
    "        print('No Sitemap Detected')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check for robots.txt file\n",
    "def find_bot(domain):\n",
    "    r = requests.get(domain)\n",
    "    if r.status_code == 200:\n",
    "        return r.text\n",
    "    else:\n",
    "        return('No Robots.txt found')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check live page for HTTPs\n",
    "def https_check(url):\n",
    "    check = re.search(r'(https)', url)\n",
    "    if check is None:\n",
    "        return 'HTTP'\n",
    "    else:\n",
    "        return 'HTTPS'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check to see if noindex tags are present on a page\n",
    "def no_index_check(url):\n",
    "    soup = bs(url.content, 'lxml')\n",
    "    tag = soup.find_all('meta', {'name':'robots'})\n",
    "    noindex = ''.join([r_tag.get('content') for r_tag in tag])\n",
    "    if 'noindex' in noindex:\n",
    "        return '<noindex> tag found'\n",
    "    else:\n",
    "        return '<noindex> tag not found'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check to see status of canonical tag, if it is there and if it is self referring or not\n",
    "def canonical_check(url):\n",
    "    canonical = ''.join([c_tag.get('href') for c_tag in bs(url.content, 'lxml').\\\n",
    "                         find_all('link', {'rel': 'canonical'})])\n",
    "    if len(canonical) > 1: \n",
    "        if canonical == r.url:\n",
    "            return 'Self-referring canonical'\n",
    "        else:\n",
    "            return 'Canonical found, not self-referring'\n",
    "    else:\n",
    "        return 'No canonical present'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def redirect_check(url, **kwargs):\n",
    "    if len(re.findall(r'(\\d)', (str(url.history)))) >= 1:\n",
    "        return 'Redirect present'\n",
    "    else:\n",
    "        return 'No redirect present'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The actual server call function\n",
    "def speed_test_url(url, device):\n",
    "    params = {\n",
    "        \"?url\": url,\n",
    "        'strategy': device,\n",
    "        #'key': key (API key recommended for larger sites)\n",
    "        }\n",
    "    data = urllib.parse.urlencode(params, doseq=True)\n",
    "    main_call = urllib.parse.urljoin(service_url, data)\n",
    "    main_call = main_call.replace(r'%3F', r'?')\n",
    "    return main_call"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2>Crawling / Indexing</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sitemap is present!\n",
      "Robots.txt is present!\n",
      "\n",
      "User-agent: *\n",
      "Disallow: /wp-admin/\n",
      "Allow: /wp-admin/admin-ajax.php\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Checking for presence of sitemap and robots.txt\n",
    "\n",
    "sitemap_present = sitemap_check('https://aclion.com/sitemap.xml')\n",
    "if len(sitemap_present) > 1:\n",
    "    print('Sitemap is present!')\n",
    "else:\n",
    "    print('Sitemap is NOT present!!!')\n",
    "    \n",
    "robots_present = find_bot('https://aclion.com/robots.txt')\n",
    "\n",
    "if len(robots_present) > 1:\n",
    "    print('Robots.txt is present!')\n",
    "    print(robots_present)\n",
    "else:\n",
    "    print('Robots is NOT present!!!')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a2b6d5c172704078bce6c515f5e562c5",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(FloatProgress(value=0.0, max=286.0), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "# QA for Crawling/Indexing properties\n",
    "\n",
    "# Parse links from XML Sitemap\n",
    "sitemap_list = [sitemap_check(i) for i in sitemap_check('https://www.aclion.com/sitemap.xml')]\n",
    "frame = []\n",
    "for n in range(0, len(sitemap_list)):\n",
    "    for i in sitemap_list[n]:\n",
    "        frame.append(i)\n",
    "\n",
    "# Build DataFrame from XML Sitemap Links        \n",
    "sitemap = pd.DataFrame(frame, columns=['Url'])\n",
    "\n",
    "frame = []\n",
    "for crawl in tqdm(sitemap.Url): \n",
    "    r = requests.get(crawl)\n",
    "    data = {\n",
    "        'Url': r.url,\n",
    "        'Http Status': https_check(r.url),\n",
    "        'Status Code': r.status_code,\n",
    "        'Redirect Status': redirect_check(r),\n",
    "        'Noindex Tag': no_index_check(r),\n",
    "        'Canonical Status': canonical_check(r)\n",
    "    }\n",
    "    frame.append(data)\n",
    "    \n",
    "c_and_i_data = pd.DataFrame(frame) # Store Raw Data\n",
    "c_and_i_overview = pd.concat([c_and_i_data.groupby(count).count()['Url'] for count in c_and_i_data.columns[1:]]) # Give an overview on page stats"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2>Meta Data</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Meta Data QA ###\n",
    "\n",
    "''' \n",
    "Requirements: \n",
    "\n",
    "Screaming Frog export of site using export internal html\n",
    "\n",
    "'''\n",
    "# Pass a dictionary to rename columns for ease of code\n",
    "columns = {'Meta Description 1': 'metaDescription', \n",
    "          'Meta Description 1 Length': 'descriptionLength', \n",
    "          'Meta Description 1 Pixel Width': 'descriptionPixelWidth',\n",
    "          'Indexability Status': 'indexabilityStatus', \n",
    "          'Title 1': 'metaTitle', \n",
    "          'Title 1 Length': 'titleLength', \n",
    "          'Title 1 Pixel Width': 'titlePixelWidth'}\n",
    "\n",
    "# Data Read-In & Formatting\n",
    "df = pd.read_excel(r'internal_html_aclion.xlsx') # path to internal html SF pull\n",
    "df = df[~df['Indexability'].str.contains('Non')]\n",
    "df = df.rename(columns=columns)\n",
    "df = df[['Address'] + [v[1] for v in columns.items()]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Check Meta Descriptions ##\n",
    "\n",
    "# Over 160 characters (Description)\n",
    "df['des_overOneSixty'] = df.descriptionLength.apply(lambda check: True if check >= 160 else False)\n",
    "des_over = df[df['des_overOneSixty']==True] \n",
    "\n",
    "# Under 70 characters (Description)\n",
    "df['des_underSeventy'] = df.descriptionLength.apply(lambda check: True if check <= 70 else False)\n",
    "thinDescription = df[df['des_underSeventy']==True] \n",
    "\n",
    "# Missing Data (Description)\n",
    "df['des_missingMeta'] = df['metaDescription'].isnull()\n",
    "missingDescription = df[df['des_missingMeta']==True] \n",
    "\n",
    "#Duplicate Data (Description)\n",
    "df_des = pd.DataFrame(df.groupby(df['metaDescription'].tolist()).size()).rename(columns={0: 'Occurance'})\n",
    "df_des = df_des[df_des['Occurance'] > 1]\n",
    "duplicate = df_des.index.tolist()\n",
    "df['duplicateDescription'] = df['metaDescription'].apply(lambda check: True if check in duplicate else False)\n",
    "dupDescription = df[df['duplicateDescription']==True] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Check Meta Titles ##\n",
    "\n",
    "# Over 60 characters (Title)\n",
    "df['tle_overSixty'] = df.titleLength.apply(lambda check: True if check >= 60 else False)\n",
    "tle_over = df[df['tle_overSixty']==True] \n",
    "\n",
    "# Under 30 characters (Title)\n",
    "df['tle_underThirty'] = df.titleLength.apply(lambda check: True if check <= 30 else False)\n",
    "thinTitle = df[df['tle_underThirty']==True] \n",
    "\n",
    "# Missing Data (Title)\n",
    "df['tle_missingMeta'] = df['metaTitle'].isnull()\n",
    "missingTitle = df[df['tle_missingMeta']==True]\n",
    "\n",
    "# Duplicate Data (Title)\n",
    "df_tle = pd.DataFrame(df.groupby(df['metaTitle'].tolist()).size()).rename(columns={0: 'Occurance'})\n",
    "df_tle = df_tle[df_tle['Occurance'] > 1]\n",
    "duplicate = df_tle.index.tolist()\n",
    "df['duplicateTitle'] = df['metaTitle'].apply(lambda check: True if check in duplicate else False)\n",
    "dupTitle = df[df['duplicateTitle']==True] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Collect the results #\n",
    "\n",
    "meta_data_results = {'Meta Tiltes over character Count':tle_over.shape[0],\n",
    "                     'Thin Title':thinTitle.shape[0], 'Missing Titles':missingTitle.shape[0], \n",
    "                     'Duplicate Titles':dupTitle.shape[0],\n",
    "                     'Description Over':des_over.shape[0],'Thin Description':thinDescription.shape[0],\n",
    "                     'Missing Description':missingDescription.shape[0],\n",
    "                     'Duplicate Description':dupDescription.shape[0]}\n",
    "\n",
    "meta_data = pd.DataFrame(meta_data_results, index=[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2>Site Speed Checking</h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Page Speed Testing ##\n",
    "\n",
    "check = \"captchaResult\"\n",
    "service_url = \"https://www.googleapis.com/pagespeedonline/v5/runPagespeed/\"\n",
    "#key = \"api key\"\n",
    "diagnostics = []\n",
    "seo = []\n",
    "\n",
    "for crawl in tqdm(df['Address'][0:10].to_list()):\n",
    "    # Call the PageSpeed Insights API \n",
    "    call = requests.get(speed_test_url(url=crawl, device='desktop'))\n",
    "    response = call.json()\n",
    "    # test to see if you get a valid response\n",
    "    try:\n",
    "        error = response['error']\n",
    "        continue\n",
    "    except KeyError as e:\n",
    "        pass\n",
    "    \n",
    "    # Parse out web metrics from API response\n",
    "    web_diagnostics = {\n",
    "    'Page': crawl,\n",
    "    'Number of Resources Requested': response[\"lighthouseResult\"]['audits']['diagnostics']\\\n",
    "    ['details']['items'][0]['numRequests'], \n",
    "    'Page Size (MB)': round(response[\"lighthouseResult\"]['audits']['diagnostics']\\\n",
    "                            ['details']['items'][0]['totalByteWeight']/1e+6, 2), \n",
    "    'Number of Scripts': response[\"lighthouseResult\"]['audits']['diagnostics']\\\n",
    "    ['details']['items'][0]['numScripts'],\n",
    "    'Number of Stylesheets': response[\"lighthouseResult\"]['audits']['diagnostics']\\\n",
    "    ['details']['items'][0]['numStylesheets'],\n",
    "    'Number of Fonts': response[\"lighthouseResult\"]['audits']['diagnostics']\\\n",
    "    ['details']['items'][0]['numFonts'],\n",
    "    'DOM Size': response[\"lighthouseResult\"]['audits']['dom-size']['details']['items'][0]['value']}\n",
    "    \n",
    "    # Parse out speed metrics from API response\n",
    "    seo_vitals = {\n",
    "        'Page': crawl,\n",
    "        'Google Score': int(response['lighthouseResult']['categories']['performance']['score']*100),\n",
    "        'FCP': float(response[\"lighthouseResult\"]['audits']['first-contentful-paint']['displayValue'].replace('\\xa0s', '')), \n",
    "        'LCP': float(response[\"lighthouseResult\"]['audits']['largest-contentful-paint']['displayValue'].replace('\\xa0s', '')), \n",
    "        'FID': response[\"lighthouseResult\"]['audits']['metrics']['details']['items'][0]['maxPotentialFID'], \n",
    "        'CLS': round(response[\"lighthouseResult\"]['audits']['metrics']['details']['items'][0]['cumulativeLayoutShift'], 3),\n",
    "        'Speed Index': float(response[\"lighthouseResult\"]['audits']['speed-index']['displayValue'].replace('\\xa0s', '')),\n",
    "        'Time to Interactive': float(response[\"lighthouseResult\"]['audits']['interactive']['displayValue'].replace('\\xa0s', ''))\n",
    "    }\n",
    "\n",
    "    diagnostics.append(web_diagnostics)\n",
    "    seo.append(seo_vitals)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.ExcelWriter('site_launch_qa.xlsx') as writer:\n",
    "    c_and_i_overview.to_excel(writer, sheet_name='Crawl&Index Overview')\n",
    "    c_and_i_data.to_excel(writer, sheet_name='Crawl&Index Data', index=False)\n",
    "    meta_data.to_excel(writer, sheet_name='Meta Data Overview', index=False)\n",
    "    tle_over[['Address', 'metaTitle', 'titleLength']].to_excel(writer, sheet_name='Titles Over', index=False)\n",
    "    thinTitle[['Address', 'metaTitle', 'titleLength']].to_excel(writer, sheet_name='Titles Under', index=False)\n",
    "    missingTitle[['Address', 'metaTitle', 'titleLength']].to_excel(writer, sheet_name='Missing Titles', index=False)\n",
    "    dupTitle[['Address', 'metaTitle', 'titleLength']].to_excel(writer, sheet_name='Duplicate Titles', index=False)\n",
    "    des_over[['Address', 'metaDescription', 'descriptionLength']].to_excel(writer, sheet_name='Description Over', index=False)\n",
    "    thinDescription[['Address', 'metaDescription', 'descriptionLength']].to_excel(writer, sheet_name='Description Under', index=False)\n",
    "    missingDescription[['Address', 'metaDescription', 'descriptionLength']].to_excel(writer, sheet_name='Missing Description', index=False)\n",
    "    dupDescription[['Address', 'metaDescription', 'descriptionLength']].to_excel(writer, sheet_name='Duplicate Description', index=False)\n",
    "    pd.DataFrame(seo).to_excel(writer, sheet_name='Site Speed', index=False)\n",
    "    pd.DataFrame(diagnostics).to_excel(writer, sheet_name='Web Metrics', index=False)"
   ]
  }
 ],
 "metadata": {
  "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}