Skip to content

Instantly share code, notes, and snippets.

@natzir

natzir/Booster.ipynb

Last active Dec 12, 2019
Star
Embed
What would you like to do?
Learn more about clone URLs
Download ZIP
Raw
Booster.ipynb
{
"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