{ | |
"cells": [ | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"## Booster\n", | |
"<br>\n", | |
"This notebook checks for every url in Search Console data if:\n", | |
"<ul>\n", | |
" <li>Your you titles and descriptions contains your top performing query </li>\n", | |
" <li>Your titles and descriptions have the correct pixel width </li>\n", | |
" <li>Your internal and external anchor text contains your top performing query </li>\n", | |
" <li>Your page CTR is below the average</li>\n", | |
"<ul>\n", | |
"<br>\n", | |
" @author: Natzir Turrado: Technical SEO / Data Scientist. <a href=\"https://twitter.com/natzir9\">Twitter > @natzir9</a>" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"<img src='https://www.analistaseo.es/wp-content/uploads/2019/11/booster-search-console.png'>" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": { | |
"colab_type": "text", | |
"id": "VvfcpByztqkf" | |
}, | |
"source": [ | |
"Importing libraries" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 5, | |
"metadata": { | |
"colab": {}, | |
"colab_type": "code", | |
"id": "DljFdN6rtcyu" | |
}, | |
"outputs": [], | |
"source": [ | |
"import pandas as pd\n", | |
"import numpy as np\n", | |
"import re\n", | |
"import datetime\n", | |
"from IPython.display import display\n", | |
"import qgrid\n", | |
"from collections import defaultdict\n", | |
"from dateutil import relativedelta\n", | |
"from ipywidgets import interact\n", | |
"import httplib2\n", | |
"from apiclient import errors\n", | |
"from apiclient.discovery import build\n", | |
"from oauth2client.client import OAuth2WebServerFlow\n", | |
"import requests" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": { | |
"colab_type": "text", | |
"id": "JAVsB-I2t_AZ" | |
}, | |
"source": [ | |
"Insert here your Google **CLIENT_ID**, **CLIENT_SECRET** & your Search Console **SITE PROPERTY**. \n", | |
"<ul><li><a href=\"https://console.developers.google.com/flows/enableapi?apiid=webmasters&credential=client_key\">Create your API credentintials</a></li></ul>" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 6, | |
"metadata": { | |
"colab": {}, | |
"colab_type": "code", | |
"id": "OCLNELb0tvrb" | |
}, | |
"outputs": [], | |
"source": [ | |
"CLIENT_ID = ''\n", | |
"CLIENT_SECRET = ''\n", | |
"site = ''\n", | |
"\n", | |
"site_no_slash = re.sub('/$','',site)" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": { | |
"colab_type": "text", | |
"id": "YEI-GX8Iu00E" | |
}, | |
"source": [ | |
"Insert here the **date range** (last 3 month of SC data by default)\n", | |
"\n" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 7, | |
"metadata": { | |
"colab": {}, | |
"colab_type": "code", | |
"id": "inb3cm4Bu1N4" | |
}, | |
"outputs": [], | |
"source": [ | |
"end_date = datetime.date.today()\n", | |
"start_date = end_date - relativedelta.relativedelta(months=3)" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": { | |
"colab_type": "text", | |
"id": "8H3oHaYgv6FC" | |
}, | |
"source": [ | |
"Google Search Console API call. \n", | |
"<ul><li><a href=\"https://developers.google.com/webmaster-tools/search-console-api-original/v3/quickstart/quickstart-python\">Quickstart: Run a Search Console App in Python</a></li>\n", | |
"<li><a href=\"https://developers.google.com/apis-explorer/#p/webmasters/v3/\">Search Console API Explorer</a></li>\n", | |
"</ul>" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 8, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"name": "stdout", | |
"output_type": "stream", | |
"text": [ | |
"Go to the following link in your browser: https://accounts.google.com/o/oauth2/v2/auth?client_id=34080392064-3n7skj9umb29hjhftm6ft1u0lmhh59qq.apps.googleusercontent.com&redirect_uri=urn%3Aietf%3Awg%3Aoauth%3A2.0%3Aoob&scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fwebmasters.readonly&access_type=offline&response_type=code\n", | |
"Enter verification code: 4/swEPV--aIIX_Upe5fS4iKDFXzRnB8ryFNkkXmcDUcM4CXVggBf23AH4\n" | |
] | |
} | |
], | |
"source": [ | |
"OAUTH_SCOPE = 'https://www.googleapis.com/auth/webmasters.readonly'\n", | |
"REDIRECT_URI = 'urn:ietf:wg:oauth:2.0:oob'\n", | |
"\n", | |
"# Run through the OAuth flow and retrieve credentials\n", | |
"flow = OAuth2WebServerFlow(CLIENT_ID, CLIENT_SECRET, OAUTH_SCOPE, redirect_uri=REDIRECT_URI)\n", | |
"authorize_url = flow.step1_get_authorize_url()\n", | |
"print ('Go to the following link in your browser: ' + authorize_url)\n", | |
"code = input('Enter verification code: ').strip()\n", | |
"credentials = flow.step2_exchange(code)\n", | |
"\n", | |
"# Create an httplib2.Http object and authorize it with our credentials\n", | |
"http = httplib2.Http()\n", | |
"http = credentials.authorize(http)\n", | |
"\n", | |
"webmasters_service = build('webmasters', 'v3', http=http)" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 9, | |
"metadata": { | |
"colab": { | |
"base_uri": "https://localhost:8080/", | |
"height": 73 | |
}, | |
"colab_type": "code", | |
"id": "OlSOcxRsvqFZ", | |
"outputId": "9dff2389-3d69-499b-f4b2-abcfb70a82b8" | |
}, | |
"outputs": [ | |
{ | |
"name": "stdout", | |
"output_type": "stream", | |
"text": [ | |
"Enter device category: MOBILE, DESKTOP or TABLET (leave it blank for all devices): \n" | |
] | |
} | |
], | |
"source": [ | |
"def execute_request(service, property_uri, request):\n", | |
" return service.searchanalytics().query(siteUrl=property_uri, body=request).execute()\n", | |
"\n", | |
"request = {\n", | |
" 'startDate': datetime.datetime.strftime(start_date,\"%Y-%m-%d\"),\n", | |
" 'endDate': datetime.datetime.strftime(end_date,'%Y-%m-%d'),\n", | |
" 'dimensions': ['page','query'],\n", | |
" 'rowLimit': 100 #up to 25.000 urls\n", | |
"}\n", | |
"\n", | |
"#Adding a device filter to request\n", | |
"device_category = input('Enter device category: MOBILE, DESKTOP or TABLET (leave it blank for all devices): ').strip()\n", | |
"if device_category:\n", | |
" request['dimensionFilterGroups'] = [{'filters':[{'dimension':'device','expression':device_category}]}]\n", | |
"\n", | |
"#Request to SC API\n", | |
"response = execute_request(webmasters_service, site, request)" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": { | |
"colab_type": "text", | |
"id": "nWGKp7SIwTIN" | |
}, | |
"source": [ | |
"<strong>Parsing the JSON returned</strong>" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 10, | |
"metadata": { | |
"colab": {}, | |
"colab_type": "code", | |
"id": "SE-VkmsfwXMA" | |
}, | |
"outputs": [], | |
"source": [ | |
"scDict = defaultdict(list)\n", | |
"\n", | |
"for row in response['rows']:\n", | |
" scDict['page'].append(row['keys'][0] or 0)\n", | |
" scDict['query'].append(row['keys'][1] or 0)\n", | |
" scDict['clicks'].append(row['clicks'] or 0)\n", | |
" scDict['ctr'].append(row['ctr'] or 0)\n", | |
" scDict['impressions'].append(row['impressions'] or 0)\n", | |
" scDict['position'].append(row['position'] or 0)" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": { | |
"colab_type": "text", | |
"id": "i1iD74RjwhMS" | |
}, | |
"source": [ | |
"<strong>DataFrame of Search Console data<strong>\n" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 11, | |
"metadata": { | |
"colab": { | |
"base_uri": "https://localhost:8080/", | |
"height": 71 | |
}, | |
"colab_type": "code", | |
"id": "q8JlB1bWwhRG", | |
"outputId": "2cc1cc78-41fa-47c9-b4a8-01e339cf7354" | |
}, | |
"outputs": [ | |
{ | |
"data": { | |
"text/html": [ | |
"<div>\n", | |
"<style scoped>\n", | |
" .dataframe tbody tr th:only-of-type {\n", | |
" vertical-align: middle;\n", | |
" }\n", | |
"\n", | |
" .dataframe tbody tr th {\n", | |
" vertical-align: top;\n", | |
" }\n", | |
"\n", | |
" .dataframe thead th {\n", | |
" text-align: right;\n", | |
" }\n", | |
"</style>\n", | |
"<table border=\"1\" class=\"dataframe\">\n", | |
" <thead>\n", | |
" <tr style=\"text-align: right;\">\n", | |
" <th></th>\n", | |
" <th>page</th>\n", | |
" <th>query</th>\n", | |
" <th>clicks</th>\n", | |
" <th>ctr</th>\n", | |
" <th>impressions</th>\n", | |
" <th>position</th>\n", | |
" </tr>\n", | |
" </thead>\n", | |
" <tbody>\n", | |
" <tr>\n", | |
" <td>0</td>\n", | |
" <td>https://www.zankyou.es/</td>\n", | |
" <td>zankyou</td>\n", | |
" <td>9353</td>\n", | |
" <td>38.391758</td>\n", | |
" <td>24362</td>\n", | |
" <td>2.29</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>1</td>\n", | |
" <td>https://www.zankyou.es/p/trucos-para-escribir-...</td>\n", | |
" <td>votos matrimoniales</td>\n", | |
" <td>3474</td>\n", | |
" <td>8.311005</td>\n", | |
" <td>41800</td>\n", | |
" <td>3.93</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>2</td>\n", | |
" <td>https://www.zankyou.es/p/15-formas-super-roman...</td>\n", | |
" <td>ideas para pedir matrimonio</td>\n", | |
" <td>2289</td>\n", | |
" <td>24.449904</td>\n", | |
" <td>9362</td>\n", | |
" <td>2.23</td>\n", | |
" </tr>\n", | |
" </tbody>\n", | |
"</table>\n", | |
"</div>" | |
], | |
"text/plain": [ | |
" page \\\n", | |
"0 https://www.zankyou.es/ \n", | |
"1 https://www.zankyou.es/p/trucos-para-escribir-... \n", | |
"2 https://www.zankyou.es/p/15-formas-super-roman... \n", | |
"\n", | |
" query clicks ctr impressions position \n", | |
"0 zankyou 9353 38.391758 24362 2.29 \n", | |
"1 votos matrimoniales 3474 8.311005 41800 3.93 \n", | |
"2 ideas para pedir matrimonio 2289 24.449904 9362 2.23 " | |
] | |
}, | |
"execution_count": 11, | |
"metadata": {}, | |
"output_type": "execute_result" | |
} | |
], | |
"source": [ | |
"df = pd.DataFrame(data = scDict)\n", | |
"\n", | |
"df['clicks'] = df['clicks'].astype('int')\n", | |
"df['ctr'] = df['ctr']*100\n", | |
"df['impressions'] = df['impressions'].astype('int')\n", | |
"df['position'] = df['position'].round(2)\n", | |
"\n", | |
"df.sort_values('clicks',inplace=True,ascending=False)\n", | |
"df.head(3)" | |
] | |
}, | |
{ | |
"attachments": {}, | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"<strong>Create an export of your backlinks (ahrefs > backlinks > export)</strong>\n", | |
"<ul>\n", | |
" <li>Export your dofollow and actives backlinks with its anchor text</li>\n", | |
" <li>Name the columns \"Link URL\" and \"Link Anchor\"</li>\n", | |
" <li>Save the excel file with the name \"backlinks.xlsx\" and store it in this work space</li>\n", | |
"</ul>" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 12, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"data": { | |
"text/html": [ | |
"<div>\n", | |
"<style scoped>\n", | |
" .dataframe tbody tr th:only-of-type {\n", | |
" vertical-align: middle;\n", | |
" }\n", | |
"\n", | |
" .dataframe tbody tr th {\n", | |
" vertical-align: top;\n", | |
" }\n", | |
"\n", | |
" .dataframe thead th {\n", | |
" text-align: right;\n", | |
" }\n", | |
"</style>\n", | |
"<table border=\"1\" class=\"dataframe\">\n", | |
" <thead>\n", | |
" <tr style=\"text-align: right;\">\n", | |
" <th></th>\n", | |
" <th>Link URL</th>\n", | |
" <th>Link Anchor</th>\n", | |
" </tr>\n", | |
" </thead>\n", | |
" <tbody>\n", | |
" <tr>\n", | |
" <td>0</td>\n", | |
" <td>http://t.zankyou.es/</td>\n", | |
" <td>http://t.zankyou.es/</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>2</td>\n", | |
" <td>http://www.zankyou.es/</td>\n", | |
" <td>Empresa recomendada por Zankyou Bodas</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>3</td>\n", | |
" <td>http://www.zankyou.es/</td>\n", | |
" <td>Lista de Bodas</td>\n", | |
" </tr>\n", | |
" </tbody>\n", | |
"</table>\n", | |
"</div>" | |
], | |
"text/plain": [ | |
" Link URL Link Anchor\n", | |
"0 http://t.zankyou.es/ http://t.zankyou.es/\n", | |
"2 http://www.zankyou.es/ Empresa recomendada por Zankyou Bodas\n", | |
"3 http://www.zankyou.es/ Lista de Bodas" | |
] | |
}, | |
"execution_count": 12, | |
"metadata": {}, | |
"output_type": "execute_result" | |
} | |
], | |
"source": [ | |
"df2 = pd.read_excel('backlinks.xlsx')\n", | |
"df2.dropna(inplace=True)\n", | |
"df2.head(3)" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"<strong>DataFrame of Backlinks</strong>" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 13, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"data": { | |
"text/html": [ | |
"<div>\n", | |
"<style scoped>\n", | |
" .dataframe tbody tr th:only-of-type {\n", | |
" vertical-align: middle;\n", | |
" }\n", | |
"\n", | |
" .dataframe tbody tr th {\n", | |
" vertical-align: top;\n", | |
" }\n", | |
"\n", | |
" .dataframe thead th {\n", | |
" text-align: right;\n", | |
" }\n", | |
"</style>\n", | |
"<table border=\"1\" class=\"dataframe\">\n", | |
" <thead>\n", | |
" <tr style=\"text-align: right;\">\n", | |
" <th></th>\n", | |
" <th>Link URL</th>\n", | |
" <th>Backlink Anchor</th>\n", | |
" </tr>\n", | |
" </thead>\n", | |
" <tbody>\n", | |
" <tr>\n", | |
" <td>0</td>\n", | |
" <td>http://t.zankyou.es/</td>\n", | |
" <td>['http://t.zankyou.es/']</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>1</td>\n", | |
" <td>http://www.zankyou.es/</td>\n", | |
" <td>['empresa recomendada por zankyou bodas', 'lis...</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>2</td>\n", | |
" <td>http://www.zankyou.es/?_ga=1.202118099.3739417...</td>\n", | |
" <td>['aqui para ver su magazine de bodas']</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>3</td>\n", | |
" <td>http://www.zankyou.es/banquetes-bodas</td>\n", | |
" <td>['catering', 'espacio donde celebreis vuestra ...</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>4</td>\n", | |
" <td>http://www.zankyou.es/belleza-novias-bodas</td>\n", | |
" <td>['look nupcial']</td>\n", | |
" </tr>\n", | |
" </tbody>\n", | |
"</table>\n", | |
"</div>" | |
], | |
"text/plain": [ | |
" Link URL \\\n", | |
"0 http://t.zankyou.es/ \n", | |
"1 http://www.zankyou.es/ \n", | |
"2 http://www.zankyou.es/?_ga=1.202118099.3739417... \n", | |
"3 http://www.zankyou.es/banquetes-bodas \n", | |
"4 http://www.zankyou.es/belleza-novias-bodas \n", | |
"\n", | |
" Backlink Anchor \n", | |
"0 ['http://t.zankyou.es/'] \n", | |
"1 ['empresa recomendada por zankyou bodas', 'lis... \n", | |
"2 ['aqui para ver su magazine de bodas'] \n", | |
"3 ['catering', 'espacio donde celebreis vuestra ... \n", | |
"4 ['look nupcial'] " | |
] | |
}, | |
"execution_count": 13, | |
"metadata": {}, | |
"output_type": "execute_result" | |
} | |
], | |
"source": [ | |
"df_backlinks = df2.groupby('Link URL')['Link Anchor'].apply(list).reset_index(name='Backlink Anchor')\n", | |
"df_backlinks['Backlink Anchor'] = [str(i).lower() for i in df_backlinks['Backlink Anchor']]\n", | |
"df_backlinks['Backlink Anchor'] = df_backlinks['Backlink Anchor'].str.normalize('NFKD').str.encode('ascii', errors='ignore').str.decode('utf-8')\n", | |
"df_backlinks.head(5)" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"<strong>Create an export of your internal anchor text</strong>\n", | |
"<ul>\n", | |
" <li>Crawl your site an get the destination url with its anchor text and alt text</li>\n", | |
" <li>Name the columns \"Destination\", \"Alt Text\" and \"Anchor\" </li>\n", | |
" <li>Create an excel file named \"inlinks.xlsx\" and save it in the same work space as this file</li>\n", | |
"</ul>" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 14, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"data": { | |
"text/html": [ | |
"<div>\n", | |
"<style scoped>\n", | |
" .dataframe tbody tr th:only-of-type {\n", | |
" vertical-align: middle;\n", | |
" }\n", | |
"\n", | |
" .dataframe tbody tr th {\n", | |
" vertical-align: top;\n", | |
" }\n", | |
"\n", | |
" .dataframe thead th {\n", | |
" text-align: right;\n", | |
" }\n", | |
"</style>\n", | |
"<table border=\"1\" class=\"dataframe\">\n", | |
" <thead>\n", | |
" <tr style=\"text-align: right;\">\n", | |
" <th></th>\n", | |
" <th>Anchor Text</th>\n", | |
" <th>URL</th>\n", | |
" <th>Count</th>\n", | |
" </tr>\n", | |
" </thead>\n", | |
" <tbody>\n", | |
" <tr>\n", | |
" <td>0</td>\n", | |
" <td></td>\n", | |
" <td>https://www.zankyou.es/magazine</td>\n", | |
" <td>44835</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>1</td>\n", | |
" <td>Política de cookies</td>\n", | |
" <td>https://www.zankyou.es/porque-zankyou/cookies</td>\n", | |
" <td>37238</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>2</td>\n", | |
" <td></td>\n", | |
" <td>https://www.zankyou.es/lista-bodas</td>\n", | |
" <td>37158</td>\n", | |
" </tr>\n", | |
" </tbody>\n", | |
"</table>\n", | |
"</div>" | |
], | |
"text/plain": [ | |
" Anchor Text URL Count\n", | |
"0 https://www.zankyou.es/magazine 44835\n", | |
"1 Política de cookies https://www.zankyou.es/porque-zankyou/cookies 37238\n", | |
"2 https://www.zankyou.es/lista-bodas 37158" | |
] | |
}, | |
"execution_count": 14, | |
"metadata": {}, | |
"output_type": "execute_result" | |
} | |
], | |
"source": [ | |
"df3 = pd.read_excel('inlinks.xlsx',na_filter=False)\n", | |
"df3.head(3)" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"<strong>DataFrame of Inlinks</strong>" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 15, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"data": { | |
"text/html": [ | |
"<div>\n", | |
"<style scoped>\n", | |
" .dataframe tbody tr th:only-of-type {\n", | |
" vertical-align: middle;\n", | |
" }\n", | |
"\n", | |
" .dataframe tbody tr th {\n", | |
" vertical-align: top;\n", | |
" }\n", | |
"\n", | |
" .dataframe thead th {\n", | |
" text-align: right;\n", | |
" }\n", | |
"</style>\n", | |
"<table border=\"1\" class=\"dataframe\">\n", | |
" <thead>\n", | |
" <tr style=\"text-align: right;\">\n", | |
" <th></th>\n", | |
" <th>URL</th>\n", | |
" <th>Inlink Anchor</th>\n", | |
" </tr>\n", | |
" </thead>\n", | |
" <tbody>\n", | |
" <tr>\n", | |
" <td>0</td>\n", | |
" <td>https://www.zankyou.es/</td>\n", | |
" <td>['zankyou', 'portal de bodas zankyou | espana'...</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>1</td>\n", | |
" <td>https://www.zankyou.es/David Sanz Events</td>\n", | |
" <td>['david sanz events']</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>2</td>\n", | |
" <td>https://www.zankyou.es/Renfe</td>\n", | |
" <td>['renfe']</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>3</td>\n", | |
" <td>https://www.zankyou.es/accesorios-novia</td>\n", | |
" <td>['accesorios novia', '', 'accesorios novia(471...</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>4</td>\n", | |
" <td>https://www.zankyou.es/accesorios-novia/a-coruna</td>\n", | |
" <td>['a coruna', 'accesorios novia(8)', 'buscar en...</td>\n", | |
" </tr>\n", | |
" </tbody>\n", | |
"</table>\n", | |
"</div>" | |
], | |
"text/plain": [ | |
" URL \\\n", | |
"0 https://www.zankyou.es/ \n", | |
"1 https://www.zankyou.es/David Sanz Events \n", | |
"2 https://www.zankyou.es/Renfe \n", | |
"3 https://www.zankyou.es/accesorios-novia \n", | |
"4 https://www.zankyou.es/accesorios-novia/a-coruna \n", | |
"\n", | |
" Inlink Anchor \n", | |
"0 ['zankyou', 'portal de bodas zankyou | espana'... \n", | |
"1 ['david sanz events'] \n", | |
"2 ['renfe'] \n", | |
"3 ['accesorios novia', '', 'accesorios novia(471... \n", | |
"4 ['a coruna', 'accesorios novia(8)', 'buscar en... " | |
] | |
}, | |
"execution_count": 15, | |
"metadata": {}, | |
"output_type": "execute_result" | |
} | |
], | |
"source": [ | |
"df_inlinks = df3.groupby('URL')['Anchor Text'].apply(list).reset_index(name='Inlink Anchor')\n", | |
"df_inlinks['Inlink Anchor'] = [str(i).lower() for i in df_inlinks['Inlink Anchor']]\n", | |
"df_inlinks['Inlink Anchor'] = df_inlinks['Inlink Anchor'].str.normalize('NFKD').str.encode('ascii', errors='ignore').str.decode('utf-8')\n", | |
"df_inlinks.head(5)" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"<strong>Create an export of your titles and meta description</strong>\n", | |
"<ul>\n", | |
" <li>Crawl your site an get the titles and meta description</li>\n", | |
" <li>Name the columns \"Adress\", \"Title\", \"Title Pixel Width\", \"Meta Description\", \"Meta Description Pixel Width\", \"Inlinks\", \"Unique Inlinks\", and \"% of Total\" </li>\n", | |
" <li>Create an excel file named \"meta.xlsx\" and save it in the same work space as this file</li>\n", | |
"</ul>" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 16, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"data": { | |
"text/html": [ | |
"<div>\n", | |
"<style scoped>\n", | |
" .dataframe tbody tr th:only-of-type {\n", | |
" vertical-align: middle;\n", | |
" }\n", | |
"\n", | |
" .dataframe tbody tr th {\n", | |
" vertical-align: top;\n", | |
" }\n", | |
"\n", | |
" .dataframe thead th {\n", | |
" text-align: right;\n", | |
" }\n", | |
"</style>\n", | |
"<table border=\"1\" class=\"dataframe\">\n", | |
" <thead>\n", | |
" <tr style=\"text-align: right;\">\n", | |
" <th></th>\n", | |
" <th>Address</th>\n", | |
" <th>Title</th>\n", | |
" <th>Title Pixel Width</th>\n", | |
" <th>Meta Description</th>\n", | |
" <th>Meta Description Pixel Width</th>\n", | |
" <th>Inlinks</th>\n", | |
" <th>Unique Inlinks</th>\n", | |
" <th>% of Total</th>\n", | |
" </tr>\n", | |
" </thead>\n", | |
" <tbody>\n", | |
" <tr>\n", | |
" <td>0</td>\n", | |
" <td>https://www.zankyou.es/</td>\n", | |
" <td>Organiza la boda de tus suenos de principio a ...</td>\n", | |
" <td>544</td>\n", | |
" <td>Organiza tu boda con Zankyou: APP gratuita, li...</td>\n", | |
" <td>785</td>\n", | |
" <td>114820</td>\n", | |
" <td>38207</td>\n", | |
" <td>100.00</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>1</td>\n", | |
" <td>https://www.zankyou.es/sorteo?itm_source=zanky...</td>\n", | |
" <td>Sorteo</td>\n", | |
" <td>59</td>\n", | |
" <td>Juega y Gana! Sorteo Zankyou</td>\n", | |
" <td>202</td>\n", | |
" <td>38205</td>\n", | |
" <td>38205</td>\n", | |
" <td>99.99</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>2</td>\n", | |
" <td>https://www.zankyou.es/porque-zankyou/cookies</td>\n", | |
" <td>Cookies</td>\n", | |
" <td>71</td>\n", | |
" <td>Cookies</td>\n", | |
" <td>51</td>\n", | |
" <td>38206</td>\n", | |
" <td>38205</td>\n", | |
" <td>99.99</td>\n", | |
" </tr>\n", | |
" </tbody>\n", | |
"</table>\n", | |
"</div>" | |
], | |
"text/plain": [ | |
" Address \\\n", | |
"0 https://www.zankyou.es/ \n", | |
"1 https://www.zankyou.es/sorteo?itm_source=zanky... \n", | |
"2 https://www.zankyou.es/porque-zankyou/cookies \n", | |
"\n", | |
" Title Title Pixel Width \\\n", | |
"0 Organiza la boda de tus suenos de principio a ... 544 \n", | |
"1 Sorteo 59 \n", | |
"2 Cookies 71 \n", | |
"\n", | |
" Meta Description \\\n", | |
"0 Organiza tu boda con Zankyou: APP gratuita, li... \n", | |
"1 Juega y Gana! Sorteo Zankyou \n", | |
"2 Cookies \n", | |
"\n", | |
" Meta Description Pixel Width Inlinks Unique Inlinks % of Total \n", | |
"0 785 114820 38207 100.00 \n", | |
"1 202 38205 38205 99.99 \n", | |
"2 51 38206 38205 99.99 " | |
] | |
}, | |
"execution_count": 16, | |
"metadata": {}, | |
"output_type": "execute_result" | |
} | |
], | |
"source": [ | |
"df_meta = pd.read_excel('meta.xlsx')\n", | |
"df_meta['Meta Description'] = df_meta['Meta Description'].str.normalize('NFKD').str.encode('ascii', errors='ignore').str.decode('utf-8')\n", | |
"df_meta['Title'] = df_meta['Title'].str.normalize('NFKD').str.encode('ascii', errors='ignore').str.decode('utf-8')\n", | |
"df_meta.head(3)" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"<strong>Mergin SC + Meta into a DataFrame</strong>" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 17, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"data": { | |
"text/html": [ | |
"<div>\n", | |
"<style scoped>\n", | |
" .dataframe tbody tr th:only-of-type {\n", | |
" vertical-align: middle;\n", | |
" }\n", | |
"\n", | |
" .dataframe tbody tr th {\n", | |
" vertical-align: top;\n", | |
" }\n", | |
"\n", | |
" .dataframe thead th {\n", | |
" text-align: right;\n", | |
" }\n", | |
"</style>\n", | |
"<table border=\"1\" class=\"dataframe\">\n", | |
" <thead>\n", | |
" <tr style=\"text-align: right;\">\n", | |
" <th></th>\n", | |
" <th>page</th>\n", | |
" <th>query</th>\n", | |
" <th>clicks</th>\n", | |
" <th>ctr</th>\n", | |
" <th>impressions</th>\n", | |
" <th>position</th>\n", | |
" <th>Title</th>\n", | |
" <th>Title Pixel Width</th>\n", | |
" <th>Meta Description</th>\n", | |
" <th>Meta Description Pixel Width</th>\n", | |
" <th>Inlinks</th>\n", | |
" <th>Unique Inlinks</th>\n", | |
" <th>% of Total</th>\n", | |
" </tr>\n", | |
" </thead>\n", | |
" <tbody>\n", | |
" <tr>\n", | |
" <td>0</td>\n", | |
" <td>https://www.zankyou.es/</td>\n", | |
" <td>zankyou</td>\n", | |
" <td>9353</td>\n", | |
" <td>38.391758</td>\n", | |
" <td>24362</td>\n", | |
" <td>2.29</td>\n", | |
" <td>Organiza la boda de tus suenos de principio a ...</td>\n", | |
" <td>544</td>\n", | |
" <td>Organiza tu boda con Zankyou: APP gratuita, li...</td>\n", | |
" <td>785</td>\n", | |
" <td>114820</td>\n", | |
" <td>38207</td>\n", | |
" <td>100.00</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>1</td>\n", | |
" <td>https://www.zankyou.es/p/trucos-para-escribir-...</td>\n", | |
" <td>votos matrimoniales</td>\n", | |
" <td>3474</td>\n", | |
" <td>8.311005</td>\n", | |
" <td>41800</td>\n", | |
" <td>3.93</td>\n", | |
" <td>Votos matrimoniales originales, romanticos y d...</td>\n", | |
" <td>481</td>\n", | |
" <td>Los votos matrimoniales son una parte fundamen...</td>\n", | |
" <td>921</td>\n", | |
" <td>84</td>\n", | |
" <td>28</td>\n", | |
" <td>0.07</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>2</td>\n", | |
" <td>https://www.zankyou.es/p/15-formas-super-roman...</td>\n", | |
" <td>ideas para pedir matrimonio</td>\n", | |
" <td>2289</td>\n", | |
" <td>24.449904</td>\n", | |
" <td>9362</td>\n", | |
" <td>2.23</td>\n", | |
" <td>15 formas super romanticas de pedir matrimonio...</td>\n", | |
" <td>622</td>\n", | |
" <td>Es una de las escenas que mas nos gusta en una...</td>\n", | |
" <td>870</td>\n", | |
" <td>1066</td>\n", | |
" <td>406</td>\n", | |
" <td>1.06</td>\n", | |
" </tr>\n", | |
" </tbody>\n", | |
"</table>\n", | |
"</div>" | |
], | |
"text/plain": [ | |
" page \\\n", | |
"0 https://www.zankyou.es/ \n", | |
"1 https://www.zankyou.es/p/trucos-para-escribir-... \n", | |
"2 https://www.zankyou.es/p/15-formas-super-roman... \n", | |
"\n", | |
" query clicks ctr impressions position \\\n", | |
"0 zankyou 9353 38.391758 24362 2.29 \n", | |
"1 votos matrimoniales 3474 8.311005 41800 3.93 \n", | |
"2 ideas para pedir matrimonio 2289 24.449904 9362 2.23 \n", | |
"\n", | |
" Title Title Pixel Width \\\n", | |
"0 Organiza la boda de tus suenos de principio a ... 544 \n", | |
"1 Votos matrimoniales originales, romanticos y d... 481 \n", | |
"2 15 formas super romanticas de pedir matrimonio... 622 \n", | |
"\n", | |
" Meta Description \\\n", | |
"0 Organiza tu boda con Zankyou: APP gratuita, li... \n", | |
"1 Los votos matrimoniales son una parte fundamen... \n", | |
"2 Es una de las escenas que mas nos gusta en una... \n", | |
"\n", | |
" Meta Description Pixel Width Inlinks Unique Inlinks % of Total \n", | |
"0 785 114820 38207 100.00 \n", | |
"1 921 84 28 0.07 \n", | |
"2 870 1066 406 1.06 " | |
] | |
}, | |
"execution_count": 17, | |
"metadata": {}, | |
"output_type": "execute_result" | |
} | |
], | |
"source": [ | |
"result = pd.merge(df, df_meta, left_on = 'page' ,right_on ='Address', how='left')\n", | |
"result.drop('Address', axis=1, inplace=True)\n", | |
"result.head(3)" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"<strong>Checking if title and description contains the top performing query</strong>" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 18, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"data": { | |
"text/html": [ | |
"<div>\n", | |
"<style scoped>\n", | |
" .dataframe tbody tr th:only-of-type {\n", | |
" vertical-align: middle;\n", | |
" }\n", | |
"\n", | |
" .dataframe tbody tr th {\n", | |
" vertical-align: top;\n", | |
" }\n", | |
"\n", | |
" .dataframe thead th {\n", | |
" text-align: right;\n", | |
" }\n", | |
"</style>\n", | |
"<table border=\"1\" class=\"dataframe\">\n", | |
" <thead>\n", | |
" <tr style=\"text-align: right;\">\n", | |
" <th></th>\n", | |
" <th>page</th>\n", | |
" <th>query</th>\n", | |
" <th>clicks</th>\n", | |
" <th>ctr</th>\n", | |
" <th>impressions</th>\n", | |
" <th>position</th>\n", | |
" <th>Title</th>\n", | |
" <th>Title Pixel Width</th>\n", | |
" <th>Meta Description</th>\n", | |
" <th>Meta Description Pixel Width</th>\n", | |
" <th>Inlinks</th>\n", | |
" <th>Unique Inlinks</th>\n", | |
" <th>% of Total</th>\n", | |
" <th>title_contains</th>\n", | |
" <th>meta_contains</th>\n", | |
" </tr>\n", | |
" </thead>\n", | |
" <tbody>\n", | |
" <tr>\n", | |
" <td>0</td>\n", | |
" <td>https://www.zankyou.es/</td>\n", | |
" <td>zankyou</td>\n", | |
" <td>9353</td>\n", | |
" <td>38.391758</td>\n", | |
" <td>24362</td>\n", | |
" <td>2.29</td>\n", | |
" <td>organiza la boda de tus suenos de principio a ...</td>\n", | |
" <td>544</td>\n", | |
" <td>organiza tu boda con zankyou: app gratuita, li...</td>\n", | |
" <td>785</td>\n", | |
" <td>114820</td>\n", | |
" <td>38207</td>\n", | |
" <td>100.00</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>1</td>\n", | |
" <td>https://www.zankyou.es/p/trucos-para-escribir-...</td>\n", | |
" <td>votos matrimoniales</td>\n", | |
" <td>3474</td>\n", | |
" <td>8.311005</td>\n", | |
" <td>41800</td>\n", | |
" <td>3.93</td>\n", | |
" <td>votos matrimoniales originales, romanticos y d...</td>\n", | |
" <td>481</td>\n", | |
" <td>los votos matrimoniales son una parte fundamen...</td>\n", | |
" <td>921</td>\n", | |
" <td>84</td>\n", | |
" <td>28</td>\n", | |
" <td>0.07</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>2</td>\n", | |
" <td>https://www.zankyou.es/p/15-formas-super-roman...</td>\n", | |
" <td>ideas para pedir matrimonio</td>\n", | |
" <td>2289</td>\n", | |
" <td>24.449904</td>\n", | |
" <td>9362</td>\n", | |
" <td>2.23</td>\n", | |
" <td>15 formas super romanticas de pedir matrimonio...</td>\n", | |
" <td>622</td>\n", | |
" <td>es una de las escenas que mas nos gusta en una...</td>\n", | |
" <td>870</td>\n", | |
" <td>1066</td>\n", | |
" <td>406</td>\n", | |
" <td>1.06</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" </tr>\n", | |
" </tbody>\n", | |
"</table>\n", | |
"</div>" | |
], | |
"text/plain": [ | |
" page \\\n", | |
"0 https://www.zankyou.es/ \n", | |
"1 https://www.zankyou.es/p/trucos-para-escribir-... \n", | |
"2 https://www.zankyou.es/p/15-formas-super-roman... \n", | |
"\n", | |
" query clicks ctr impressions position \\\n", | |
"0 zankyou 9353 38.391758 24362 2.29 \n", | |
"1 votos matrimoniales 3474 8.311005 41800 3.93 \n", | |
"2 ideas para pedir matrimonio 2289 24.449904 9362 2.23 \n", | |
"\n", | |
" Title Title Pixel Width \\\n", | |
"0 organiza la boda de tus suenos de principio a ... 544 \n", | |
"1 votos matrimoniales originales, romanticos y d... 481 \n", | |
"2 15 formas super romanticas de pedir matrimonio... 622 \n", | |
"\n", | |
" Meta Description \\\n", | |
"0 organiza tu boda con zankyou: app gratuita, li... \n", | |
"1 los votos matrimoniales son una parte fundamen... \n", | |
"2 es una de las escenas que mas nos gusta en una... \n", | |
"\n", | |
" Meta Description Pixel Width Inlinks Unique Inlinks % of Total \\\n", | |
"0 785 114820 38207 100.00 \n", | |
"1 921 84 28 0.07 \n", | |
"2 870 1066 406 1.06 \n", | |
"\n", | |
" title_contains meta_contains \n", | |
"0 True True \n", | |
"1 True True \n", | |
"2 False False " | |
] | |
}, | |
"execution_count": 18, | |
"metadata": {}, | |
"output_type": "execute_result" | |
} | |
], | |
"source": [ | |
"result['Title'] = [str(i).lower() for i in result['Title']]\n", | |
"result['Meta Description'] = [str(i).lower() for i in result['Meta Description']]\n", | |
"result['title_contains'] = [x[0] in x[1] for x in zip(result['query'], result['Title'])]\n", | |
"result['meta_contains'] = [x[0] in x[1] for x in zip(result['query'], result['Meta Description'])]\n", | |
"result.head(3)" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"<strong>Checking if Title Pixel Width and Meta Description Pixel Width have the correct size</strong>" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 19, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"data": { | |
"text/html": [ | |
"<div>\n", | |
"<style scoped>\n", | |
" .dataframe tbody tr th:only-of-type {\n", | |
" vertical-align: middle;\n", | |
" }\n", | |
"\n", | |
" .dataframe tbody tr th {\n", | |
" vertical-align: top;\n", | |
" }\n", | |
"\n", | |
" .dataframe thead th {\n", | |
" text-align: right;\n", | |
" }\n", | |
"</style>\n", | |
"<table border=\"1\" class=\"dataframe\">\n", | |
" <thead>\n", | |
" <tr style=\"text-align: right;\">\n", | |
" <th></th>\n", | |
" <th>page</th>\n", | |
" <th>query</th>\n", | |
" <th>clicks</th>\n", | |
" <th>ctr</th>\n", | |
" <th>impressions</th>\n", | |
" <th>position</th>\n", | |
" <th>Title</th>\n", | |
" <th>Title Pixel Width</th>\n", | |
" <th>Meta Description</th>\n", | |
" <th>Meta Description Pixel Width</th>\n", | |
" <th>Inlinks</th>\n", | |
" <th>Unique Inlinks</th>\n", | |
" <th>% of Total</th>\n", | |
" <th>title_contains</th>\n", | |
" <th>meta_contains</th>\n", | |
" <th>title_width_ok</th>\n", | |
" <th>description_width_ok</th>\n", | |
" </tr>\n", | |
" </thead>\n", | |
" <tbody>\n", | |
" <tr>\n", | |
" <td>0</td>\n", | |
" <td>https://www.zankyou.es/</td>\n", | |
" <td>zankyou</td>\n", | |
" <td>9353</td>\n", | |
" <td>38.391758</td>\n", | |
" <td>24362</td>\n", | |
" <td>2.29</td>\n", | |
" <td>organiza la boda de tus suenos de principio a ...</td>\n", | |
" <td>544</td>\n", | |
" <td>organiza tu boda con zankyou: app gratuita, li...</td>\n", | |
" <td>785</td>\n", | |
" <td>114820</td>\n", | |
" <td>38207</td>\n", | |
" <td>100.00</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>1</td>\n", | |
" <td>https://www.zankyou.es/p/trucos-para-escribir-...</td>\n", | |
" <td>votos matrimoniales</td>\n", | |
" <td>3474</td>\n", | |
" <td>8.311005</td>\n", | |
" <td>41800</td>\n", | |
" <td>3.93</td>\n", | |
" <td>votos matrimoniales originales, romanticos y d...</td>\n", | |
" <td>481</td>\n", | |
" <td>los votos matrimoniales son una parte fundamen...</td>\n", | |
" <td>921</td>\n", | |
" <td>84</td>\n", | |
" <td>28</td>\n", | |
" <td>0.07</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>2</td>\n", | |
" <td>https://www.zankyou.es/p/15-formas-super-roman...</td>\n", | |
" <td>ideas para pedir matrimonio</td>\n", | |
" <td>2289</td>\n", | |
" <td>24.449904</td>\n", | |
" <td>9362</td>\n", | |
" <td>2.23</td>\n", | |
" <td>15 formas super romanticas de pedir matrimonio...</td>\n", | |
" <td>622</td>\n", | |
" <td>es una de las escenas que mas nos gusta en una...</td>\n", | |
" <td>870</td>\n", | |
" <td>1066</td>\n", | |
" <td>406</td>\n", | |
" <td>1.06</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>True</td>\n", | |
" </tr>\n", | |
" </tbody>\n", | |
"</table>\n", | |
"</div>" | |
], | |
"text/plain": [ | |
" page \\\n", | |
"0 https://www.zankyou.es/ \n", | |
"1 https://www.zankyou.es/p/trucos-para-escribir-... \n", | |
"2 https://www.zankyou.es/p/15-formas-super-roman... \n", | |
"\n", | |
" query clicks ctr impressions position \\\n", | |
"0 zankyou 9353 38.391758 24362 2.29 \n", | |
"1 votos matrimoniales 3474 8.311005 41800 3.93 \n", | |
"2 ideas para pedir matrimonio 2289 24.449904 9362 2.23 \n", | |
"\n", | |
" Title Title Pixel Width \\\n", | |
"0 organiza la boda de tus suenos de principio a ... 544 \n", | |
"1 votos matrimoniales originales, romanticos y d... 481 \n", | |
"2 15 formas super romanticas de pedir matrimonio... 622 \n", | |
"\n", | |
" Meta Description \\\n", | |
"0 organiza tu boda con zankyou: app gratuita, li... \n", | |
"1 los votos matrimoniales son una parte fundamen... \n", | |
"2 es una de las escenas que mas nos gusta en una... \n", | |
"\n", | |
" Meta Description Pixel Width Inlinks Unique Inlinks % of Total \\\n", | |
"0 785 114820 38207 100.00 \n", | |
"1 921 84 28 0.07 \n", | |
"2 870 1066 406 1.06 \n", | |
"\n", | |
" title_contains meta_contains title_width_ok description_width_ok \n", | |
"0 True True True True \n", | |
"1 True True True True \n", | |
"2 False False False True " | |
] | |
}, | |
"execution_count": 19, | |
"metadata": {}, | |
"output_type": "execute_result" | |
} | |
], | |
"source": [ | |
"result['title_width_ok'] = result['Title Pixel Width'] < 545\n", | |
"result['description_width_ok'] = result['Meta Description Pixel Width'] < 1010\n", | |
"result.head(3)" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"<strong>Checking if CTR is below the average</strong>" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 20, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"data": { | |
"text/html": [ | |
"<div>\n", | |
"<style scoped>\n", | |
" .dataframe tbody tr th:only-of-type {\n", | |
" vertical-align: middle;\n", | |
" }\n", | |
"\n", | |
" .dataframe tbody tr th {\n", | |
" vertical-align: top;\n", | |
" }\n", | |
"\n", | |
" .dataframe thead th {\n", | |
" text-align: right;\n", | |
" }\n", | |
"</style>\n", | |
"<table border=\"1\" class=\"dataframe\">\n", | |
" <thead>\n", | |
" <tr style=\"text-align: right;\">\n", | |
" <th></th>\n", | |
" <th>page</th>\n", | |
" <th>query</th>\n", | |
" <th>clicks</th>\n", | |
" <th>ctr</th>\n", | |
" <th>impressions</th>\n", | |
" <th>position</th>\n", | |
" <th>Title</th>\n", | |
" <th>Title Pixel Width</th>\n", | |
" <th>Meta Description</th>\n", | |
" <th>Meta Description Pixel Width</th>\n", | |
" <th>Inlinks</th>\n", | |
" <th>Unique Inlinks</th>\n", | |
" <th>% of Total</th>\n", | |
" <th>title_contains</th>\n", | |
" <th>meta_contains</th>\n", | |
" <th>title_width_ok</th>\n", | |
" <th>description_width_ok</th>\n", | |
" <th>ctr_ok</th>\n", | |
" </tr>\n", | |
" </thead>\n", | |
" <tbody>\n", | |
" <tr>\n", | |
" <td>0</td>\n", | |
" <td>https://www.zankyou.es/</td>\n", | |
" <td>zankyou</td>\n", | |
" <td>9353</td>\n", | |
" <td>38.391758</td>\n", | |
" <td>24362</td>\n", | |
" <td>2.29</td>\n", | |
" <td>organiza la boda de tus suenos de principio a ...</td>\n", | |
" <td>544</td>\n", | |
" <td>organiza tu boda con zankyou: app gratuita, li...</td>\n", | |
" <td>785</td>\n", | |
" <td>114820</td>\n", | |
" <td>38207</td>\n", | |
" <td>100.00</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>1</td>\n", | |
" <td>https://www.zankyou.es/p/trucos-para-escribir-...</td>\n", | |
" <td>votos matrimoniales</td>\n", | |
" <td>3474</td>\n", | |
" <td>8.311005</td>\n", | |
" <td>41800</td>\n", | |
" <td>3.93</td>\n", | |
" <td>votos matrimoniales originales, romanticos y d...</td>\n", | |
" <td>481</td>\n", | |
" <td>los votos matrimoniales son una parte fundamen...</td>\n", | |
" <td>921</td>\n", | |
" <td>84</td>\n", | |
" <td>28</td>\n", | |
" <td>0.07</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>False</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>2</td>\n", | |
" <td>https://www.zankyou.es/p/15-formas-super-roman...</td>\n", | |
" <td>ideas para pedir matrimonio</td>\n", | |
" <td>2289</td>\n", | |
" <td>24.449904</td>\n", | |
" <td>9362</td>\n", | |
" <td>2.23</td>\n", | |
" <td>15 formas super romanticas de pedir matrimonio...</td>\n", | |
" <td>622</td>\n", | |
" <td>es una de las escenas que mas nos gusta en una...</td>\n", | |
" <td>870</td>\n", | |
" <td>1066</td>\n", | |
" <td>406</td>\n", | |
" <td>1.06</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" </tr>\n", | |
" </tbody>\n", | |
"</table>\n", | |
"</div>" | |
], | |
"text/plain": [ | |
" page \\\n", | |
"0 https://www.zankyou.es/ \n", | |
"1 https://www.zankyou.es/p/trucos-para-escribir-... \n", | |
"2 https://www.zankyou.es/p/15-formas-super-roman... \n", | |
"\n", | |
" query clicks ctr impressions position \\\n", | |
"0 zankyou 9353 38.391758 24362 2.29 \n", | |
"1 votos matrimoniales 3474 8.311005 41800 3.93 \n", | |
"2 ideas para pedir matrimonio 2289 24.449904 9362 2.23 \n", | |
"\n", | |
" Title Title Pixel Width \\\n", | |
"0 organiza la boda de tus suenos de principio a ... 544 \n", | |
"1 votos matrimoniales originales, romanticos y d... 481 \n", | |
"2 15 formas super romanticas de pedir matrimonio... 622 \n", | |
"\n", | |
" Meta Description \\\n", | |
"0 organiza tu boda con zankyou: app gratuita, li... \n", | |
"1 los votos matrimoniales son una parte fundamen... \n", | |
"2 es una de las escenas que mas nos gusta en una... \n", | |
"\n", | |
" Meta Description Pixel Width Inlinks Unique Inlinks % of Total \\\n", | |
"0 785 114820 38207 100.00 \n", | |
"1 921 84 28 0.07 \n", | |
"2 870 1066 406 1.06 \n", | |
"\n", | |
" title_contains meta_contains title_width_ok description_width_ok ctr_ok \n", | |
"0 True True True True True \n", | |
"1 True True True True False \n", | |
"2 False False False True True " | |
] | |
}, | |
"execution_count": 20, | |
"metadata": {}, | |
"output_type": "execute_result" | |
} | |
], | |
"source": [ | |
"result['ctr_ok'] = result['ctr'] >= result['ctr'].mean()\n", | |
"result.head(3)" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"<strong>Mergin Result + Inlinks + Backlinks</strong>" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 21, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"data": { | |
"text/html": [ | |
"<div>\n", | |
"<style scoped>\n", | |
" .dataframe tbody tr th:only-of-type {\n", | |
" vertical-align: middle;\n", | |
" }\n", | |
"\n", | |
" .dataframe tbody tr th {\n", | |
" vertical-align: top;\n", | |
" }\n", | |
"\n", | |
" .dataframe thead th {\n", | |
" text-align: right;\n", | |
" }\n", | |
"</style>\n", | |
"<table border=\"1\" class=\"dataframe\">\n", | |
" <thead>\n", | |
" <tr style=\"text-align: right;\">\n", | |
" <th></th>\n", | |
" <th>page</th>\n", | |
" <th>query</th>\n", | |
" <th>clicks</th>\n", | |
" <th>ctr</th>\n", | |
" <th>impressions</th>\n", | |
" <th>position</th>\n", | |
" <th>Title</th>\n", | |
" <th>Title Pixel Width</th>\n", | |
" <th>Meta Description</th>\n", | |
" <th>Meta Description Pixel Width</th>\n", | |
" <th>Inlinks</th>\n", | |
" <th>Unique Inlinks</th>\n", | |
" <th>% of Total</th>\n", | |
" <th>title_contains</th>\n", | |
" <th>meta_contains</th>\n", | |
" <th>title_width_ok</th>\n", | |
" <th>description_width_ok</th>\n", | |
" <th>ctr_ok</th>\n", | |
" <th>Inlink Anchor</th>\n", | |
" <th>Backlink Anchor</th>\n", | |
" </tr>\n", | |
" </thead>\n", | |
" <tbody>\n", | |
" <tr>\n", | |
" <td>0</td>\n", | |
" <td>https://www.zankyou.es/</td>\n", | |
" <td>zankyou</td>\n", | |
" <td>9353</td>\n", | |
" <td>38.391758</td>\n", | |
" <td>24362</td>\n", | |
" <td>2.29</td>\n", | |
" <td>organiza la boda de tus suenos de principio a ...</td>\n", | |
" <td>544</td>\n", | |
" <td>organiza tu boda con zankyou: app gratuita, li...</td>\n", | |
" <td>785</td>\n", | |
" <td>114820</td>\n", | |
" <td>38207</td>\n", | |
" <td>100.00</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>['zankyou', 'portal de bodas zankyou | espana'...</td>\n", | |
" <td>['espagne', 'spagna', 'espanha', 'spanien', 'e...</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>1</td>\n", | |
" <td>https://www.zankyou.es/p/trucos-para-escribir-...</td>\n", | |
" <td>votos matrimoniales</td>\n", | |
" <td>3474</td>\n", | |
" <td>8.311005</td>\n", | |
" <td>41800</td>\n", | |
" <td>3.93</td>\n", | |
" <td>votos matrimoniales originales, romanticos y d...</td>\n", | |
" <td>481</td>\n", | |
" <td>los votos matrimoniales son una parte fundamen...</td>\n", | |
" <td>921</td>\n", | |
" <td>84</td>\n", | |
" <td>28</td>\n", | |
" <td>0.07</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>False</td>\n", | |
" <td>['', 'como escribir los votos matrimoniales ma...</td>\n", | |
" <td>['votos matrimoniales']</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>2</td>\n", | |
" <td>https://www.zankyou.es/p/15-formas-super-roman...</td>\n", | |
" <td>ideas para pedir matrimonio</td>\n", | |
" <td>2289</td>\n", | |
" <td>24.449904</td>\n", | |
" <td>9362</td>\n", | |
" <td>2.23</td>\n", | |
" <td>15 formas super romanticas de pedir matrimonio...</td>\n", | |
" <td>622</td>\n", | |
" <td>es una de las escenas que mas nos gusta en una...</td>\n", | |
" <td>870</td>\n", | |
" <td>1066</td>\n", | |
" <td>406</td>\n", | |
" <td>1.06</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>['pedida de mano', '', 'pedidas de mano', 'pro...</td>\n", | |
" <td>['15 formas super romanticas de pedir matrimon...</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>3</td>\n", | |
" <td>https://www.zankyou.es/p/los-20-regalos-que-to...</td>\n", | |
" <td>que regalar a una mujer</td>\n", | |
" <td>2065</td>\n", | |
" <td>21.984457</td>\n", | |
" <td>9393</td>\n", | |
" <td>1.17</td>\n", | |
" <td>los 20 regalos que toda mujer suena recibir al...</td>\n", | |
" <td>632</td>\n", | |
" <td>a quien no le gusta recibir un regalo inespera...</td>\n", | |
" <td>823</td>\n", | |
" <td>36</td>\n", | |
" <td>25</td>\n", | |
" <td>0.07</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>['', 'los 20 regalos que toda mujer suena reci...</td>\n", | |
" <td>['los 20 regalos que toda mujer suena con reci...</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>4</td>\n", | |
" <td>https://www.zankyou.es/p/protocolo-y-etiqueta-...</td>\n", | |
" <td>protocolo bodas</td>\n", | |
" <td>1787</td>\n", | |
" <td>47.691487</td>\n", | |
" <td>3747</td>\n", | |
" <td>1.08</td>\n", | |
" <td>protocolo para bodas: 19 reglas basicas para p...</td>\n", | |
" <td>518</td>\n", | |
" <td>hoy te damos unas normas basicas de protocolo ...</td>\n", | |
" <td>821</td>\n", | |
" <td>135</td>\n", | |
" <td>37</td>\n", | |
" <td>0.10</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>['', 'protocolo para bodas: 19 reglas basicas ...</td>\n", | |
" <td>['protocolo para bodas: 19 reglas basicas para...</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>95</td>\n", | |
" <td>https://www.zankyou.es/</td>\n", | |
" <td>zankyou weddings</td>\n", | |
" <td>341</td>\n", | |
" <td>38.100559</td>\n", | |
" <td>895</td>\n", | |
" <td>1.94</td>\n", | |
" <td>organiza la boda de tus suenos de principio a ...</td>\n", | |
" <td>544</td>\n", | |
" <td>organiza tu boda con zankyou: app gratuita, li...</td>\n", | |
" <td>785</td>\n", | |
" <td>114820</td>\n", | |
" <td>38207</td>\n", | |
" <td>100.00</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>['zankyou', 'portal de bodas zankyou | espana'...</td>\n", | |
" <td>['espagne', 'spagna', 'espanha', 'spanien', 'e...</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>96</td>\n", | |
" <td>https://www.zankyou.es/p/50-romanticas-frases-...</td>\n", | |
" <td>frases para invitacion de boda</td>\n", | |
" <td>340</td>\n", | |
" <td>15.954951</td>\n", | |
" <td>2131</td>\n", | |
" <td>3.79</td>\n", | |
" <td>50 frases para bodas: las mas romanticas para ...</td>\n", | |
" <td>555</td>\n", | |
" <td>las invitaciones de tu gran dia deben reflejar...</td>\n", | |
" <td>902</td>\n", | |
" <td>838</td>\n", | |
" <td>252</td>\n", | |
" <td>0.66</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>True</td>\n", | |
" <td>False</td>\n", | |
" <td>['50 frases de amor para las invitaciones de t...</td>\n", | |
" <td>['50 frases para bodas: las mas romanticas par...</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>97</td>\n", | |
" <td>https://www.zankyou.es/p/nombres-de-aniversari...</td>\n", | |
" <td>aniversarios de boda</td>\n", | |
" <td>340</td>\n", | |
" <td>2.289408</td>\n", | |
" <td>14851</td>\n", | |
" <td>5.41</td>\n", | |
" <td>aniversarios de boda: sus nombres y significad...</td>\n", | |
" <td>550</td>\n", | |
" <td>desde las bodas de papel a las de hueso, pasan...</td>\n", | |
" <td>959</td>\n", | |
" <td>11</td>\n", | |
" <td>9</td>\n", | |
" <td>0.02</td>\n", | |
" <td>True</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>True</td>\n", | |
" <td>False</td>\n", | |
" <td>NaN</td>\n", | |
" <td>NaN</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>98</td>\n", | |
" <td>https://www.zankyou.es/p/vestidos-de-fiesta-en...</td>\n", | |
" <td>vestidos fiesta</td>\n", | |
" <td>340</td>\n", | |
" <td>6.649716</td>\n", | |
" <td>5113</td>\n", | |
" <td>3.59</td>\n", | |
" <td>las 11 mejores tiendas de vestidos de fiesta e...</td>\n", | |
" <td>593</td>\n", | |
" <td>ademas de la novia, las invitadas de una boda ...</td>\n", | |
" <td>955</td>\n", | |
" <td>572</td>\n", | |
" <td>222</td>\n", | |
" <td>0.58</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>True</td>\n", | |
" <td>False</td>\n", | |
" <td>['', 'las 11 mejores tiendas de vestidos de fi...</td>\n", | |
" <td>['tiendas de vestidos de fiesta en madrid', 'v...</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>99</td>\n", | |
" <td>https://www.zankyou.es/p/como-organizar-una-or...</td>\n", | |
" <td>que hacer en una despedida de soltera</td>\n", | |
" <td>336</td>\n", | |
" <td>26.270524</td>\n", | |
" <td>1279</td>\n", | |
" <td>2.65</td>\n", | |
" <td>como organizar una despedida de soltera origin...</td>\n", | |
" <td>538</td>\n", | |
" <td>si tienes una despedida de soltera a la vuelta...</td>\n", | |
" <td>913</td>\n", | |
" <td>391</td>\n", | |
" <td>111</td>\n", | |
" <td>0.29</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>['', 'como organizar una despedida de soltera ...</td>\n", | |
" <td>['como organizar una original despedida de sol...</td>\n", | |
" </tr>\n", | |
" </tbody>\n", | |
"</table>\n", | |
"<p>100 rows × 20 columns</p>\n", | |
"</div>" | |
], | |
"text/plain": [ | |
" page \\\n", | |
"0 https://www.zankyou.es/ \n", | |
"1 https://www.zankyou.es/p/trucos-para-escribir-... \n", | |
"2 https://www.zankyou.es/p/15-formas-super-roman... \n", | |
"3 https://www.zankyou.es/p/los-20-regalos-que-to... \n", | |
"4 https://www.zankyou.es/p/protocolo-y-etiqueta-... \n", | |
".. ... \n", | |
"95 https://www.zankyou.es/ \n", | |
"96 https://www.zankyou.es/p/50-romanticas-frases-... \n", | |
"97 https://www.zankyou.es/p/nombres-de-aniversari... \n", | |
"98 https://www.zankyou.es/p/vestidos-de-fiesta-en... \n", | |
"99 https://www.zankyou.es/p/como-organizar-una-or... \n", | |
"\n", | |
" query clicks ctr impressions \\\n", | |
"0 zankyou 9353 38.391758 24362 \n", | |
"1 votos matrimoniales 3474 8.311005 41800 \n", | |
"2 ideas para pedir matrimonio 2289 24.449904 9362 \n", | |
"3 que regalar a una mujer 2065 21.984457 9393 \n", | |
"4 protocolo bodas 1787 47.691487 3747 \n", | |
".. ... ... ... ... \n", | |
"95 zankyou weddings 341 38.100559 895 \n", | |
"96 frases para invitacion de boda 340 15.954951 2131 \n", | |
"97 aniversarios de boda 340 2.289408 14851 \n", | |
"98 vestidos fiesta 340 6.649716 5113 \n", | |
"99 que hacer en una despedida de soltera 336 26.270524 1279 \n", | |
"\n", | |
" position Title \\\n", | |
"0 2.29 organiza la boda de tus suenos de principio a ... \n", | |
"1 3.93 votos matrimoniales originales, romanticos y d... \n", | |
"2 2.23 15 formas super romanticas de pedir matrimonio... \n", | |
"3 1.17 los 20 regalos que toda mujer suena recibir al... \n", | |
"4 1.08 protocolo para bodas: 19 reglas basicas para p... \n", | |
".. ... ... \n", | |
"95 1.94 organiza la boda de tus suenos de principio a ... \n", | |
"96 3.79 50 frases para bodas: las mas romanticas para ... \n", | |
"97 5.41 aniversarios de boda: sus nombres y significad... \n", | |
"98 3.59 las 11 mejores tiendas de vestidos de fiesta e... \n", | |
"99 2.65 como organizar una despedida de soltera origin... \n", | |
"\n", | |
" Title Pixel Width Meta Description \\\n", | |
"0 544 organiza tu boda con zankyou: app gratuita, li... \n", | |
"1 481 los votos matrimoniales son una parte fundamen... \n", | |
"2 622 es una de las escenas que mas nos gusta en una... \n", | |
"3 632 a quien no le gusta recibir un regalo inespera... \n", | |
"4 518 hoy te damos unas normas basicas de protocolo ... \n", | |
".. ... ... \n", | |
"95 544 organiza tu boda con zankyou: app gratuita, li... \n", | |
"96 555 las invitaciones de tu gran dia deben reflejar... \n", | |
"97 550 desde las bodas de papel a las de hueso, pasan... \n", | |
"98 593 ademas de la novia, las invitadas de una boda ... \n", | |
"99 538 si tienes una despedida de soltera a la vuelta... \n", | |
"\n", | |
" Meta Description Pixel Width Inlinks Unique Inlinks % of Total \\\n", | |
"0 785 114820 38207 100.00 \n", | |
"1 921 84 28 0.07 \n", | |
"2 870 1066 406 1.06 \n", | |
"3 823 36 25 0.07 \n", | |
"4 821 135 37 0.10 \n", | |
".. ... ... ... ... \n", | |
"95 785 114820 38207 100.00 \n", | |
"96 902 838 252 0.66 \n", | |
"97 959 11 9 0.02 \n", | |
"98 955 572 222 0.58 \n", | |
"99 913 391 111 0.29 \n", | |
"\n", | |
" title_contains meta_contains title_width_ok description_width_ok \\\n", | |
"0 True True True True \n", | |
"1 True True True True \n", | |
"2 False False False True \n", | |
"3 False False False True \n", | |
"4 False False True True \n", | |
".. ... ... ... ... \n", | |
"95 False False True True \n", | |
"96 False False False True \n", | |
"97 True False False True \n", | |
"98 False False False True \n", | |
"99 False False True True \n", | |
"\n", | |
" ctr_ok Inlink Anchor \\\n", | |
"0 True ['zankyou', 'portal de bodas zankyou | espana'... \n", | |
"1 False ['', 'como escribir los votos matrimoniales ma... \n", | |
"2 True ['pedida de mano', '', 'pedidas de mano', 'pro... \n", | |
"3 True ['', 'los 20 regalos que toda mujer suena reci... \n", | |
"4 True ['', 'protocolo para bodas: 19 reglas basicas ... \n", | |
".. ... ... \n", | |
"95 True ['zankyou', 'portal de bodas zankyou | espana'... \n", | |
"96 False ['50 frases de amor para las invitaciones de t... \n", | |
"97 False NaN \n", | |
"98 False ['', 'las 11 mejores tiendas de vestidos de fi... \n", | |
"99 True ['', 'como organizar una despedida de soltera ... \n", | |
"\n", | |
" Backlink Anchor \n", | |
"0 ['espagne', 'spagna', 'espanha', 'spanien', 'e... \n", | |
"1 ['votos matrimoniales'] \n", | |
"2 ['15 formas super romanticas de pedir matrimon... \n", | |
"3 ['los 20 regalos que toda mujer suena con reci... \n", | |
"4 ['protocolo para bodas: 19 reglas basicas para... \n", | |
".. ... \n", | |
"95 ['espagne', 'spagna', 'espanha', 'spanien', 'e... \n", | |
"96 ['50 frases para bodas: las mas romanticas par... \n", | |
"97 NaN \n", | |
"98 ['tiendas de vestidos de fiesta en madrid', 'v... \n", | |
"99 ['como organizar una original despedida de sol... \n", | |
"\n", | |
"[100 rows x 20 columns]" | |
] | |
}, | |
"execution_count": 21, | |
"metadata": {}, | |
"output_type": "execute_result" | |
} | |
], | |
"source": [ | |
"result = pd.merge(result, df_inlinks, left_on = 'page' ,right_on ='URL', how='left')\n", | |
"result = pd.merge(result, df_backlinks, left_on = 'page' ,right_on ='Link URL', how='left')\n", | |
"result.drop('URL', axis=1, inplace=True)\n", | |
"result.drop('Link URL', axis=1, inplace=True)\n", | |
"result" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"<strong>Checking if internal and external anchors contains your top performing query</strong>" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 22, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"data": { | |
"text/html": [ | |
"<div>\n", | |
"<style scoped>\n", | |
" .dataframe tbody tr th:only-of-type {\n", | |
" vertical-align: middle;\n", | |
" }\n", | |
"\n", | |
" .dataframe tbody tr th {\n", | |
" vertical-align: top;\n", | |
" }\n", | |
"\n", | |
" .dataframe thead th {\n", | |
" text-align: right;\n", | |
" }\n", | |
"</style>\n", | |
"<table border=\"1\" class=\"dataframe\">\n", | |
" <thead>\n", | |
" <tr style=\"text-align: right;\">\n", | |
" <th></th>\n", | |
" <th>page</th>\n", | |
" <th>query</th>\n", | |
" <th>clicks</th>\n", | |
" <th>ctr</th>\n", | |
" <th>impressions</th>\n", | |
" <th>position</th>\n", | |
" <th>Title</th>\n", | |
" <th>Title Pixel Width</th>\n", | |
" <th>Meta Description</th>\n", | |
" <th>Meta Description Pixel Width</th>\n", | |
" <th>...</th>\n", | |
" <th>% of Total</th>\n", | |
" <th>title_contains</th>\n", | |
" <th>meta_contains</th>\n", | |
" <th>title_width_ok</th>\n", | |
" <th>description_width_ok</th>\n", | |
" <th>ctr_ok</th>\n", | |
" <th>Inlink Anchor</th>\n", | |
" <th>Backlink Anchor</th>\n", | |
" <th>backlinks_contains</th>\n", | |
" <th>inlinks_contains</th>\n", | |
" </tr>\n", | |
" </thead>\n", | |
" <tbody>\n", | |
" <tr>\n", | |
" <td>0</td>\n", | |
" <td>https://www.zankyou.es/</td>\n", | |
" <td>zankyou</td>\n", | |
" <td>9353</td>\n", | |
" <td>38.391758</td>\n", | |
" <td>24362</td>\n", | |
" <td>2.29</td>\n", | |
" <td>organiza la boda de tus suenos de principio a ...</td>\n", | |
" <td>544</td>\n", | |
" <td>organiza tu boda con zankyou: app gratuita, li...</td>\n", | |
" <td>785</td>\n", | |
" <td>...</td>\n", | |
" <td>100.00</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>['zankyou', 'portal de bodas zankyou | espana'...</td>\n", | |
" <td>['espagne', 'spagna', 'espanha', 'spanien', 'e...</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>1</td>\n", | |
" <td>https://www.zankyou.es/p/trucos-para-escribir-...</td>\n", | |
" <td>votos matrimoniales</td>\n", | |
" <td>3474</td>\n", | |
" <td>8.311005</td>\n", | |
" <td>41800</td>\n", | |
" <td>3.93</td>\n", | |
" <td>votos matrimoniales originales, romanticos y d...</td>\n", | |
" <td>481</td>\n", | |
" <td>los votos matrimoniales son una parte fundamen...</td>\n", | |
" <td>921</td>\n", | |
" <td>...</td>\n", | |
" <td>0.07</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>False</td>\n", | |
" <td>['', 'como escribir los votos matrimoniales ma...</td>\n", | |
" <td>['votos matrimoniales']</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>2</td>\n", | |
" <td>https://www.zankyou.es/p/15-formas-super-roman...</td>\n", | |
" <td>ideas para pedir matrimonio</td>\n", | |
" <td>2289</td>\n", | |
" <td>24.449904</td>\n", | |
" <td>9362</td>\n", | |
" <td>2.23</td>\n", | |
" <td>15 formas super romanticas de pedir matrimonio...</td>\n", | |
" <td>622</td>\n", | |
" <td>es una de las escenas que mas nos gusta en una...</td>\n", | |
" <td>870</td>\n", | |
" <td>...</td>\n", | |
" <td>1.06</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>['pedida de mano', '', 'pedidas de mano', 'pro...</td>\n", | |
" <td>['15 formas super romanticas de pedir matrimon...</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>3</td>\n", | |
" <td>https://www.zankyou.es/p/los-20-regalos-que-to...</td>\n", | |
" <td>que regalar a una mujer</td>\n", | |
" <td>2065</td>\n", | |
" <td>21.984457</td>\n", | |
" <td>9393</td>\n", | |
" <td>1.17</td>\n", | |
" <td>los 20 regalos que toda mujer suena recibir al...</td>\n", | |
" <td>632</td>\n", | |
" <td>a quien no le gusta recibir un regalo inespera...</td>\n", | |
" <td>823</td>\n", | |
" <td>...</td>\n", | |
" <td>0.07</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>['', 'los 20 regalos que toda mujer suena reci...</td>\n", | |
" <td>['los 20 regalos que toda mujer suena con reci...</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>4</td>\n", | |
" <td>https://www.zankyou.es/p/protocolo-y-etiqueta-...</td>\n", | |
" <td>protocolo bodas</td>\n", | |
" <td>1787</td>\n", | |
" <td>47.691487</td>\n", | |
" <td>3747</td>\n", | |
" <td>1.08</td>\n", | |
" <td>protocolo para bodas: 19 reglas basicas para p...</td>\n", | |
" <td>518</td>\n", | |
" <td>hoy te damos unas normas basicas de protocolo ...</td>\n", | |
" <td>821</td>\n", | |
" <td>...</td>\n", | |
" <td>0.10</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>['', 'protocolo para bodas: 19 reglas basicas ...</td>\n", | |
" <td>['protocolo para bodas: 19 reglas basicas para...</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" <td>...</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>95</td>\n", | |
" <td>https://www.zankyou.es/</td>\n", | |
" <td>zankyou weddings</td>\n", | |
" <td>341</td>\n", | |
" <td>38.100559</td>\n", | |
" <td>895</td>\n", | |
" <td>1.94</td>\n", | |
" <td>organiza la boda de tus suenos de principio a ...</td>\n", | |
" <td>544</td>\n", | |
" <td>organiza tu boda con zankyou: app gratuita, li...</td>\n", | |
" <td>785</td>\n", | |
" <td>...</td>\n", | |
" <td>100.00</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>['zankyou', 'portal de bodas zankyou | espana'...</td>\n", | |
" <td>['espagne', 'spagna', 'espanha', 'spanien', 'e...</td>\n", | |
" <td>True</td>\n", | |
" <td>False</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>96</td>\n", | |
" <td>https://www.zankyou.es/p/50-romanticas-frases-...</td>\n", | |
" <td>frases para invitacion de boda</td>\n", | |
" <td>340</td>\n", | |
" <td>15.954951</td>\n", | |
" <td>2131</td>\n", | |
" <td>3.79</td>\n", | |
" <td>50 frases para bodas: las mas romanticas para ...</td>\n", | |
" <td>555</td>\n", | |
" <td>las invitaciones de tu gran dia deben reflejar...</td>\n", | |
" <td>902</td>\n", | |
" <td>...</td>\n", | |
" <td>0.66</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>True</td>\n", | |
" <td>False</td>\n", | |
" <td>['50 frases de amor para las invitaciones de t...</td>\n", | |
" <td>['50 frases para bodas: las mas romanticas par...</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>97</td>\n", | |
" <td>https://www.zankyou.es/p/nombres-de-aniversari...</td>\n", | |
" <td>aniversarios de boda</td>\n", | |
" <td>340</td>\n", | |
" <td>2.289408</td>\n", | |
" <td>14851</td>\n", | |
" <td>5.41</td>\n", | |
" <td>aniversarios de boda: sus nombres y significad...</td>\n", | |
" <td>550</td>\n", | |
" <td>desde las bodas de papel a las de hueso, pasan...</td>\n", | |
" <td>959</td>\n", | |
" <td>...</td>\n", | |
" <td>0.02</td>\n", | |
" <td>True</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>True</td>\n", | |
" <td>False</td>\n", | |
" <td>NaN</td>\n", | |
" <td>NaN</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>98</td>\n", | |
" <td>https://www.zankyou.es/p/vestidos-de-fiesta-en...</td>\n", | |
" <td>vestidos fiesta</td>\n", | |
" <td>340</td>\n", | |
" <td>6.649716</td>\n", | |
" <td>5113</td>\n", | |
" <td>3.59</td>\n", | |
" <td>las 11 mejores tiendas de vestidos de fiesta e...</td>\n", | |
" <td>593</td>\n", | |
" <td>ademas de la novia, las invitadas de una boda ...</td>\n", | |
" <td>955</td>\n", | |
" <td>...</td>\n", | |
" <td>0.58</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>True</td>\n", | |
" <td>False</td>\n", | |
" <td>['', 'las 11 mejores tiendas de vestidos de fi...</td>\n", | |
" <td>['tiendas de vestidos de fiesta en madrid', 'v...</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <td>99</td>\n", | |
" <td>https://www.zankyou.es/p/como-organizar-una-or...</td>\n", | |
" <td>que hacer en una despedida de soltera</td>\n", | |
" <td>336</td>\n", | |
" <td>26.270524</td>\n", | |
" <td>1279</td>\n", | |
" <td>2.65</td>\n", | |
" <td>como organizar una despedida de soltera origin...</td>\n", | |
" <td>538</td>\n", | |
" <td>si tienes una despedida de soltera a la vuelta...</td>\n", | |
" <td>913</td>\n", | |
" <td>...</td>\n", | |
" <td>0.29</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>True</td>\n", | |
" <td>['', 'como organizar una despedida de soltera ...</td>\n", | |
" <td>['como organizar una original despedida de sol...</td>\n", | |
" <td>False</td>\n", | |
" <td>False</td>\n", | |
" </tr>\n", | |
" </tbody>\n", | |
"</table>\n", | |
"<p>100 rows × 22 columns</p>\n", | |
"</div>" | |
], | |
"text/plain": [ | |
" page \\\n", | |
"0 https://www.zankyou.es/ \n", | |
"1 https://www.zankyou.es/p/trucos-para-escribir-... \n", | |
"2 https://www.zankyou.es/p/15-formas-super-roman... \n", | |
"3 https://www.zankyou.es/p/los-20-regalos-que-to... \n", | |
"4 https://www.zankyou.es/p/protocolo-y-etiqueta-... \n", | |
".. ... \n", | |
"95 https://www.zankyou.es/ \n", | |
"96 https://www.zankyou.es/p/50-romanticas-frases-... \n", | |
"97 https://www.zankyou.es/p/nombres-de-aniversari... \n", | |
"98 https://www.zankyou.es/p/vestidos-de-fiesta-en... \n", | |
"99 https://www.zankyou.es/p/como-organizar-una-or... \n", | |
"\n", | |
" query clicks ctr impressions \\\n", | |
"0 zankyou 9353 38.391758 24362 \n", | |
"1 votos matrimoniales 3474 8.311005 41800 \n", | |
"2 ideas para pedir matrimonio 2289 24.449904 9362 \n", | |
"3 que regalar a una mujer 2065 21.984457 9393 \n", | |
"4 protocolo bodas 1787 47.691487 3747 \n", | |
".. ... ... ... ... \n", | |
"95 zankyou weddings 341 38.100559 895 \n", | |
"96 frases para invitacion de boda 340 15.954951 2131 \n", | |
"97 aniversarios de boda 340 2.289408 14851 \n", | |
"98 vestidos fiesta 340 6.649716 5113 \n", | |
"99 que hacer en una despedida de soltera 336 26.270524 1279 \n", | |
"\n", | |
" position Title \\\n", | |
"0 2.29 organiza la boda de tus suenos de principio a ... \n", | |
"1 3.93 votos matrimoniales originales, romanticos y d... \n", | |
"2 2.23 15 formas super romanticas de pedir matrimonio... \n", | |
"3 1.17 los 20 regalos que toda mujer suena recibir al... \n", | |
"4 1.08 protocolo para bodas: 19 reglas basicas para p... \n", | |
".. ... ... \n", | |
"95 1.94 organiza la boda de tus suenos de principio a ... \n", | |
"96 3.79 50 frases para bodas: las mas romanticas para ... \n", | |
"97 5.41 aniversarios de boda: sus nombres y significad... \n", | |
"98 3.59 las 11 mejores tiendas de vestidos de fiesta e... \n", | |
"99 2.65 como organizar una despedida de soltera origin... \n", | |
"\n", | |
" Title Pixel Width Meta Description \\\n", | |
"0 544 organiza tu boda con zankyou: app gratuita, li... \n", | |
"1 481 los votos matrimoniales son una parte fundamen... \n", | |
"2 622 es una de las escenas que mas nos gusta en una... \n", | |
"3 632 a quien no le gusta recibir un regalo inespera... \n", | |
"4 518 hoy te damos unas normas basicas de protocolo ... \n", | |
".. ... ... \n", | |
"95 544 organiza tu boda con zankyou: app gratuita, li... \n", | |
"96 555 las invitaciones de tu gran dia deben reflejar... \n", | |
"97 550 desde las bodas de papel a las de hueso, pasan... \n", | |
"98 593 ademas de la novia, las invitadas de una boda ... \n", | |
"99 538 si tienes una despedida de soltera a la vuelta... \n", | |
"\n", | |
" Meta Description Pixel Width ... % of Total title_contains \\\n", | |
"0 785 ... 100.00 True \n", | |
"1 921 ... 0.07 True \n", | |
"2 870 ... 1.06 False \n", | |
"3 823 ... 0.07 False \n", | |
"4 821 ... 0.10 False \n", | |
".. ... ... ... ... \n", | |
"95 785 ... 100.00 False \n", | |
"96 902 ... 0.66 False \n", | |
"97 959 ... 0.02 True \n", | |
"98 955 ... 0.58 False \n", | |
"99 913 ... 0.29 False \n", | |
"\n", | |
" meta_contains title_width_ok description_width_ok ctr_ok \\\n", | |
"0 True True True True \n", | |
"1 True True True False \n", | |
"2 False False True True \n", | |
"3 False False True True \n", | |
"4 False True True True \n", | |
".. ... ... ... ... \n", | |
"95 False True True True \n", | |
"96 False False True False \n", | |
"97 False False True False \n", | |
"98 False False True False \n", | |
"99 False True True True \n", | |
"\n", | |
" Inlink Anchor \\\n", | |
"0 ['zankyou', 'portal de bodas zankyou | espana'... \n", | |
"1 ['', 'como escribir los votos matrimoniales ma... \n", | |
"2 ['pedida de mano', '', 'pedidas de mano', 'pro... \n", | |
"3 ['', 'los 20 regalos que toda mujer suena reci... \n", | |
"4 ['', 'protocolo para bodas: 19 reglas basicas ... \n", | |
".. ... \n", | |
"95 ['zankyou', 'portal de bodas zankyou | espana'... \n", | |
"96 ['50 frases de amor para las invitaciones de t... \n", | |
"97 NaN \n", | |
"98 ['', 'las 11 mejores tiendas de vestidos de fi... \n", | |
"99 ['', 'como organizar una despedida de soltera ... \n", | |
"\n", | |
" Backlink Anchor backlinks_contains \\\n", | |
"0 ['espagne', 'spagna', 'espanha', 'spanien', 'e... True \n", | |
"1 ['votos matrimoniales'] True \n", | |
"2 ['15 formas super romanticas de pedir matrimon... False \n", | |
"3 ['los 20 regalos que toda mujer suena con reci... False \n", | |
"4 ['protocolo para bodas: 19 reglas basicas para... False \n", | |
".. ... ... \n", | |
"95 ['espagne', 'spagna', 'espanha', 'spanien', 'e... True \n", | |
"96 ['50 frases para bodas: las mas romanticas par... False \n", | |
"97 NaN False \n", | |
"98 ['tiendas de vestidos de fiesta en madrid', 'v... False \n", | |
"99 ['como organizar una original despedida de sol... False \n", | |
"\n", | |
" inlinks_contains \n", | |
"0 True \n", | |
"1 True \n", | |
"2 False \n", | |
"3 False \n", | |
"4 False \n", | |
".. ... \n", | |
"95 False \n", | |
"96 False \n", | |
"97 False \n", | |
"98 False \n", | |
"99 False \n", | |
"\n", | |
"[100 rows x 22 columns]" | |
] | |
}, | |
"execution_count": 22, | |
"metadata": {}, | |
"output_type": "execute_result" | |
} | |
], | |
"source": [ | |
"def check_isin(check, checklist):\n", | |
" try:\n", | |
" return check in checklist\n", | |
" except TypeError:\n", | |
" return False\n", | |
"\n", | |
"result['backlinks_contains'] = [check_isin(c, l) for c, l in zip(result['query'], result['Backlink Anchor'])]\n", | |
"result['inlinks_contains'] = [check_isin(c, l) for c, l in zip(result['query'], result['Inlink Anchor'])]\n", | |
"result" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"Creating a dynamic grid to analyse the data" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 23, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"data": { | |
"application/vnd.jupyter.widget-view+json": { | |
"model_id": "cb063ac5dfdd4f56a24999c0fbb186b6", | |
"version_major": 2, | |
"version_minor": 0 | |
}, | |
"text/plain": [ | |
"QgridWidget(grid_options={'fullWidthRows': True, 'syncColumnCellResize': True, 'forceFitColumns': True, 'defau…" | |
] | |
}, | |
"metadata": {}, | |
"output_type": "display_data" | |
} | |
], | |
"source": [ | |
"df_final = result[['page','query','clicks','ctr','impressions','position','Title','Meta Description','title_contains','meta_contains','title_width_ok','description_width_ok','ctr_ok','backlinks_contains','inlinks_contains']].copy()\n", | |
"grid = qgrid.show_grid(df_final, show_toolbar=True)\n", | |
"grid\n" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": null, | |
"metadata": {}, | |
"outputs": [], | |
"source": [] | |
} | |
], | |
"metadata": { | |
"colab": { | |
"name": "bubbles.ipynb", | |
"provenance": [] | |
}, | |
"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.3" | |
} | |
}, | |
"nbformat": 4, | |
"nbformat_minor": 1 | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment