Skip to content

Instantly share code, notes, and snippets.

@natzir

natzir/meta-queries.ipynb

Last active December 19, 2022 11:40
Show Gist options
  • Save natzir/436bcd2d694b79e6060341e49ac33d95 to your computer and use it in GitHub Desktop.
Save natzir/436bcd2d694b79e6060341e49ac33d95 to your computer and use it in GitHub Desktop.
Download ZIP
Display the source blob
Display the rendered blob
Raw
meta-queries.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Meta Queries: Check if your page metadata (titles and descriptions) contains your top performing query"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"@author: Natzir Turrado: Technical SEO / Data Scientist. <a href=\"https://twitter.com/natzir9\">Twitter > @natzir9</a>"
]
},
{
"cell_type": "markdown",
"metadata": {
"colab_type": "text",
"id": "VvfcpByztqkf"
},
"source": [
"Importing libraries"
]
},
{
"cell_type": "code",
"execution_count": 1,
"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\n",
"from bs4 import BeautifulSoup"
]
},
{
"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": 2,
"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": 3,
"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": 4,
"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-fbin06k9tqb2kr7ndpm5jgtm7ef1vf32.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/swEWNRSP2V2zuwieOPCc4h1NtimvOqxmLq8C9oJ-WRsCfu-d8pGfP8w\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": 5,
"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": [
"Transforming the JSON returned in a dict of list"
]
},
{
"cell_type": "code",
"execution_count": 6,
"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": [
"DataFrame of Search Console data\n"
]
},
{
"cell_type": "code",
"execution_count": 7,
"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>9352</td>\n",
" <td>38.422350</td>\n",
" <td>24340</td>\n",
" <td>2.28</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>3479</td>\n",
" <td>8.332335</td>\n",
" <td>41753</td>\n",
" <td>3.92</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>2294</td>\n",
" <td>24.419842</td>\n",
" <td>9394</td>\n",
" <td>2.23</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>2062</td>\n",
" <td>21.959531</td>\n",
" <td>9390</td>\n",
" <td>1.17</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>1785</td>\n",
" <td>47.714515</td>\n",
" <td>3741</td>\n",
" <td>1.08</td>\n",
" </tr>\n",
" <tr>\n",
" <td>5</td>\n",
" <td>https://www.zankyou.es/p/50-romanticas-frases-...</td>\n",
" <td>frases para invitaciones de boda</td>\n",
" <td>1695</td>\n",
" <td>12.287951</td>\n",
" <td>13794</td>\n",
" <td>3.54</td>\n",
" </tr>\n",
" <tr>\n",
" <td>6</td>\n",
" <td>https://www.zankyou.es/p/15-formas-super-roman...</td>\n",
" <td>como pedir matrimonio</td>\n",
" <td>1656</td>\n",
" <td>21.362229</td>\n",
" <td>7752</td>\n",
" <td>3.07</td>\n",
" </tr>\n",
" <tr>\n",
" <td>7</td>\n",
" <td>https://www.zankyou.es/p/como-organizar-tu-bod...</td>\n",
" <td>como organizar una boda</td>\n",
" <td>1442</td>\n",
" <td>10.883019</td>\n",
" <td>13250</td>\n",
" <td>4.43</td>\n",
" </tr>\n",
" <tr>\n",
" <td>8</td>\n",
" <td>https://www.zankyou.es/g/vestidos-de-novia-pro...</td>\n",
" <td>pronovias 2020</td>\n",
" <td>1395</td>\n",
" <td>4.193723</td>\n",
" <td>33264</td>\n",
" <td>4.26</td>\n",
" </tr>\n",
" <tr>\n",
" <td>9</td>\n",
" <td>https://www.zankyou.es/p/25-ideas-originales-p...</td>\n",
" <td>ideas para bodas</td>\n",
" <td>1335</td>\n",
" <td>12.117636</td>\n",
" <td>11017</td>\n",
" <td>1.98</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",
"3 https://www.zankyou.es/p/los-20-regalos-que-to... \n",
"4 https://www.zankyou.es/p/protocolo-y-etiqueta-... \n",
"5 https://www.zankyou.es/p/50-romanticas-frases-... \n",
"6 https://www.zankyou.es/p/15-formas-super-roman... \n",
"7 https://www.zankyou.es/p/como-organizar-tu-bod... \n",
"8 https://www.zankyou.es/g/vestidos-de-novia-pro... \n",
"9 https://www.zankyou.es/p/25-ideas-originales-p... \n",
"\n",
" query clicks ctr impressions position \n",
"0 zankyou 9352 38.422350 24340 2.28 \n",
"1 votos matrimoniales 3479 8.332335 41753 3.92 \n",
"2 ideas para pedir matrimonio 2294 24.419842 9394 2.23 \n",
"3 que regalar a una mujer 2062 21.959531 9390 1.17 \n",
"4 protocolo bodas 1785 47.714515 3741 1.08 \n",
"5 frases para invitaciones de boda 1695 12.287951 13794 3.54 \n",
"6 como pedir matrimonio 1656 21.362229 7752 3.07 \n",
"7 como organizar una boda 1442 10.883019 13250 4.43 \n",
"8 pronovias 2020 1395 4.193723 33264 4.26 \n",
"9 ideas para bodas 1335 12.117636 11017 1.98 "
]
},
"execution_count": 7,
"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"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Scrapping Titles and Meta Descriptions"
]
},
{
"cell_type": "code",
"execution_count": 8,
"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>meta</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>9352</td>\n",
" <td>38.422350</td>\n",
" <td>24340</td>\n",
" <td>2.28</td>\n",
" <td>Organiza la boda de tus sueños de principio a ...</td>\n",
" <td>Organiza tu boda con Zankyou: APP gratuita, li...</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>3479</td>\n",
" <td>8.332335</td>\n",
" <td>41753</td>\n",
" <td>3.92</td>\n",
" <td>Votos matrimoniales originales, románticos y d...</td>\n",
" <td>Los votos matrimoniales son una parte fundamen...</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>2294</td>\n",
" <td>24.419842</td>\n",
" <td>9394</td>\n",
" <td>2.23</td>\n",
" <td>15 formas súper románticas de pedir matrimonio...</td>\n",
" <td>Es una de las escenas que más nos gusta en una...</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>2062</td>\n",
" <td>21.959531</td>\n",
" <td>9390</td>\n",
" <td>1.17</td>\n",
" <td>Los 20 regalos que toda mujer sueña recibir al...</td>\n",
" <td>¿A quién no le gusta recibir un regalo inesper...</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>1785</td>\n",
" <td>47.714515</td>\n",
" <td>3741</td>\n",
" <td>1.08</td>\n",
" <td>Protocolo para bodas: 19 reglas básicas para p...</td>\n",
" <td>Hoy te damos unas normas básicas de protocolo ...</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",
" </tr>\n",
" <tr>\n",
" <td>95</td>\n",
" <td>https://www.zankyou.es/g/50-vestidos-de-fiesta...</td>\n",
" <td>vestidos de noche largos para boda</td>\n",
" <td>340</td>\n",
" <td>4.605798</td>\n",
" <td>7382</td>\n",
" <td>8.08</td>\n",
" <td>50 vestidos de fiesta para bodas: conviértete ...</td>\n",
" <td>¿Estás buscando vestidos de fiesta para bodas?...</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>339</td>\n",
" <td>15.908024</td>\n",
" <td>2131</td>\n",
" <td>3.79</td>\n",
" <td>50 frases para bodas: las más románticas para ...</td>\n",
" <td>Las invitaciones de tu gran día deben reflejar...</td>\n",
" </tr>\n",
" <tr>\n",
" <td>97</td>\n",
" <td>https://www.zankyou.es/p/vestidos-de-fiesta-en...</td>\n",
" <td>vestidos fiesta</td>\n",
" <td>339</td>\n",
" <td>6.645756</td>\n",
" <td>5101</td>\n",
" <td>3.59</td>\n",
" <td>Las 11 mejores tiendas de vestidos de fiesta e...</td>\n",
" <td>Además de la novia, las invitadas de una boda ...</td>\n",
" </tr>\n",
" <tr>\n",
" <td>98</td>\n",
" <td>https://www.zankyou.es/</td>\n",
" <td>zankyou weddings</td>\n",
" <td>337</td>\n",
" <td>37.907762</td>\n",
" <td>889</td>\n",
" <td>1.94</td>\n",
" <td>Organiza la boda de tus sueños de principio a ...</td>\n",
" <td>Organiza tu boda con Zankyou: APP gratuita, li...</td>\n",
" </tr>\n",
" <tr>\n",
" <td>99</td>\n",
" <td>https://www.zankyou.es/g/trajes-de-novio-2018-...</td>\n",
" <td>trajes de novio 2019</td>\n",
" <td>334</td>\n",
" <td>4.495894</td>\n",
" <td>7429</td>\n",
" <td>2.05</td>\n",
" <td>Trajes de novio 2019: 75 estilos para triunfar</td>\n",
" <td>Descubre todas las novedades en trajes de novi...</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>100 rows × 8 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/g/50-vestidos-de-fiesta... \n",
"96 https://www.zankyou.es/p/50-romanticas-frases-... \n",
"97 https://www.zankyou.es/p/vestidos-de-fiesta-en... \n",
"98 https://www.zankyou.es/ \n",
"99 https://www.zankyou.es/g/trajes-de-novio-2018-... \n",
"\n",
" query clicks ctr impressions \\\n",
"0 zankyou 9352 38.422350 24340 \n",
"1 votos matrimoniales 3479 8.332335 41753 \n",
"2 ideas para pedir matrimonio 2294 24.419842 9394 \n",
"3 que regalar a una mujer 2062 21.959531 9390 \n",
"4 protocolo bodas 1785 47.714515 3741 \n",
".. ... ... ... ... \n",
"95 vestidos de noche largos para boda 340 4.605798 7382 \n",
"96 frases para invitacion de boda 339 15.908024 2131 \n",
"97 vestidos fiesta 339 6.645756 5101 \n",
"98 zankyou weddings 337 37.907762 889 \n",
"99 trajes de novio 2019 334 4.495894 7429 \n",
"\n",
" position title \\\n",
"0 2.28 Organiza la boda de tus sueños de principio a ... \n",
"1 3.92 Votos matrimoniales originales, románticos y d... \n",
"2 2.23 15 formas súper románticas de pedir matrimonio... \n",
"3 1.17 Los 20 regalos que toda mujer sueña recibir al... \n",
"4 1.08 Protocolo para bodas: 19 reglas básicas para p... \n",
".. ... ... \n",
"95 8.08 50 vestidos de fiesta para bodas: conviértete ... \n",
"96 3.79 50 frases para bodas: las más románticas para ... \n",
"97 3.59 Las 11 mejores tiendas de vestidos de fiesta e... \n",
"98 1.94 Organiza la boda de tus sueños de principio a ... \n",
"99 2.05 Trajes de novio 2019: 75 estilos para triunfar \n",
"\n",
" meta \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 más nos gusta en una... \n",
"3 ¿A quién no le gusta recibir un regalo inesper... \n",
"4 Hoy te damos unas normas básicas de protocolo ... \n",
".. ... \n",
"95 ¿Estás buscando vestidos de fiesta para bodas?... \n",
"96 Las invitaciones de tu gran día deben reflejar... \n",
"97 Además de la novia, las invitadas de una boda ... \n",
"98 Organiza tu boda con Zankyou: APP gratuita, li... \n",
"99 Descubre todas las novedades en trajes de novi... \n",
"\n",
"[100 rows x 8 columns]"
]
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"def get_meta(url):\n",
" page = requests.get(url)\n",
" soup = BeautifulSoup(page.content,'html.parser')\n",
" title = soup.find('title').get_text()\n",
" meta = soup.select('meta[name=\"description\"]')[0].attrs[\"content\"] \n",
" return title, meta\n",
"\n",
"df['title'],df['meta'] = zip(*df['page'].apply(get_meta))\n",
"df"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Converting to lowercase to compare in the next step"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {},
"outputs": [],
"source": [
"df['title'] = [str(i).lower() for i in df['title']]\n",
"df['meta'] = [str(i).lower() for i in df['meta']]"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Checking if title and description contains the top performing query"
]
},
{
"cell_type": "code",
"execution_count": 10,
"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>meta</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>9352</td>\n",
" <td>38.422350</td>\n",
" <td>24340</td>\n",
" <td>2.28</td>\n",
" <td>organiza la boda de tus sueños de principio a ...</td>\n",
" <td>organiza tu boda con zankyou: app gratuita, li...</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>3479</td>\n",
" <td>8.332335</td>\n",
" <td>41753</td>\n",
" <td>3.92</td>\n",
" <td>votos matrimoniales originales, románticos y d...</td>\n",
" <td>los votos matrimoniales son una parte fundamen...</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>2294</td>\n",
" <td>24.419842</td>\n",
" <td>9394</td>\n",
" <td>2.23</td>\n",
" <td>15 formas súper románticas de pedir matrimonio...</td>\n",
" <td>es una de las escenas que más nos gusta en una...</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>2062</td>\n",
" <td>21.959531</td>\n",
" <td>9390</td>\n",
" <td>1.17</td>\n",
" <td>los 20 regalos que toda mujer sueña recibir al...</td>\n",
" <td>¿a quién no le gusta recibir un regalo inesper...</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>1785</td>\n",
" <td>47.714515</td>\n",
" <td>3741</td>\n",
" <td>1.08</td>\n",
" <td>protocolo para bodas: 19 reglas básicas para p...</td>\n",
" <td>hoy te damos unas normas básicas de protocolo ...</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",
" </tr>\n",
" <tr>\n",
" <td>95</td>\n",
" <td>https://www.zankyou.es/g/50-vestidos-de-fiesta...</td>\n",
" <td>vestidos de noche largos para boda</td>\n",
" <td>340</td>\n",
" <td>4.605798</td>\n",
" <td>7382</td>\n",
" <td>8.08</td>\n",
" <td>50 vestidos de fiesta para bodas: conviértete ...</td>\n",
" <td>¿estás buscando vestidos de fiesta para bodas?...</td>\n",
" <td>False</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>339</td>\n",
" <td>15.908024</td>\n",
" <td>2131</td>\n",
" <td>3.79</td>\n",
" <td>50 frases para bodas: las más románticas para ...</td>\n",
" <td>las invitaciones de tu gran día deben reflejar...</td>\n",
" <td>False</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <td>97</td>\n",
" <td>https://www.zankyou.es/p/vestidos-de-fiesta-en...</td>\n",
" <td>vestidos fiesta</td>\n",
" <td>339</td>\n",
" <td>6.645756</td>\n",
" <td>5101</td>\n",
" <td>3.59</td>\n",
" <td>las 11 mejores tiendas de vestidos de fiesta e...</td>\n",
" <td>además de la novia, las invitadas de una boda ...</td>\n",
" <td>False</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <td>98</td>\n",
" <td>https://www.zankyou.es/</td>\n",
" <td>zankyou weddings</td>\n",
" <td>337</td>\n",
" <td>37.907762</td>\n",
" <td>889</td>\n",
" <td>1.94</td>\n",
" <td>organiza la boda de tus sueños de principio a ...</td>\n",
" <td>organiza tu boda con zankyou: app gratuita, li...</td>\n",
" <td>False</td>\n",
" <td>False</td>\n",
" </tr>\n",
" <tr>\n",
" <td>99</td>\n",
" <td>https://www.zankyou.es/g/trajes-de-novio-2018-...</td>\n",
" <td>trajes de novio 2019</td>\n",
" <td>334</td>\n",
" <td>4.495894</td>\n",
" <td>7429</td>\n",
" <td>2.05</td>\n",
" <td>trajes de novio 2019: 75 estilos para triunfar</td>\n",
" <td>descubre todas las novedades en trajes de novi...</td>\n",
" <td>True</td>\n",
" <td>True</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>100 rows × 10 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/g/50-vestidos-de-fiesta... \n",
"96 https://www.zankyou.es/p/50-romanticas-frases-... \n",
"97 https://www.zankyou.es/p/vestidos-de-fiesta-en... \n",
"98 https://www.zankyou.es/ \n",
"99 https://www.zankyou.es/g/trajes-de-novio-2018-... \n",
"\n",
" query clicks ctr impressions \\\n",
"0 zankyou 9352 38.422350 24340 \n",
"1 votos matrimoniales 3479 8.332335 41753 \n",
"2 ideas para pedir matrimonio 2294 24.419842 9394 \n",
"3 que regalar a una mujer 2062 21.959531 9390 \n",
"4 protocolo bodas 1785 47.714515 3741 \n",
".. ... ... ... ... \n",
"95 vestidos de noche largos para boda 340 4.605798 7382 \n",
"96 frases para invitacion de boda 339 15.908024 2131 \n",
"97 vestidos fiesta 339 6.645756 5101 \n",
"98 zankyou weddings 337 37.907762 889 \n",
"99 trajes de novio 2019 334 4.495894 7429 \n",
"\n",
" position title \\\n",
"0 2.28 organiza la boda de tus sueños de principio a ... \n",
"1 3.92 votos matrimoniales originales, románticos y d... \n",
"2 2.23 15 formas súper románticas de pedir matrimonio... \n",
"3 1.17 los 20 regalos que toda mujer sueña recibir al... \n",
"4 1.08 protocolo para bodas: 19 reglas básicas para p... \n",
".. ... ... \n",
"95 8.08 50 vestidos de fiesta para bodas: conviértete ... \n",
"96 3.79 50 frases para bodas: las más románticas para ... \n",
"97 3.59 las 11 mejores tiendas de vestidos de fiesta e... \n",
"98 1.94 organiza la boda de tus sueños de principio a ... \n",
"99 2.05 trajes de novio 2019: 75 estilos para triunfar \n",
"\n",
" meta title_contains \\\n",
"0 organiza tu boda con zankyou: app gratuita, li... True \n",
"1 los votos matrimoniales son una parte fundamen... True \n",
"2 es una de las escenas que más nos gusta en una... False \n",
"3 ¿a quién no le gusta recibir un regalo inesper... False \n",
"4 hoy te damos unas normas básicas de protocolo ... False \n",
".. ... ... \n",
"95 ¿estás buscando vestidos de fiesta para bodas?... False \n",
"96 las invitaciones de tu gran día deben reflejar... False \n",
"97 además de la novia, las invitadas de una boda ... False \n",
"98 organiza tu boda con zankyou: app gratuita, li... False \n",
"99 descubre todas las novedades en trajes de novi... True \n",
"\n",
" meta_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 True \n",
"\n",
"[100 rows x 10 columns]"
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df['title_contains'] = [x[0] in x[1] for x in zip(df['query'], df['title'])]\n",
"df['meta_contains'] = [x[0] in x[1] for x in zip(df['query'], df['meta'])]\n",
"df"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Creating a dynamic grid to analyse the data"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {},
"outputs": [
{
"data": {
"application/vnd.jupyter.widget-view+json": {
"model_id": "dd95d23e6e064187997bf39c362b1144",
"version_major": 2,
"version_minor": 0
},
"text/plain": [
"QgridWidget(grid_options={'fullWidthRows': True, 'syncColumnCellResize': True, 'forceFitColumns': True, 'defau…"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"grid = qgrid.show_grid(df, show_toolbar=True)\n",
"grid\n"
]
}
],
"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