fn-registry agent
4141 lines
104 KiB
Plaintext
4141 lines
104 KiB
Plaintext
{
|
|
"cells": [
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "a1b2c3d4",
|
|
"metadata": {},
|
|
"source": [
|
|
"# EDA: `psql_dcpublic.call_transactions`\n",
|
|
"\n",
|
|
"Exploracion de la tabla de transacciones del call center (Happy Robot / Aurgi).\n",
|
|
"\n",
|
|
"- **Fuente**: BigQuery via Metabase API (database_id=6)\n",
|
|
"- **Volumen**: ~24.7M filas (Nov 2022 - Abr 2026)\n",
|
|
"- **Regla**: NUNCA `SELECT *` sin LIMIT/WHERE. Siempre agregar en SQL."
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "b1c2d3e4",
|
|
"metadata": {},
|
|
"source": [
|
|
"## 1. Setup"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 1,
|
|
"id": "c1d2e3f4",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"name": "stdout",
|
|
"output_type": "stream",
|
|
"text": [
|
|
"Setup OK\n"
|
|
]
|
|
}
|
|
],
|
|
"source": [
|
|
"import os\n",
|
|
"import pandas as pd\n",
|
|
"import plotly.express as px\n",
|
|
"import plotly.graph_objects as go\n",
|
|
"from dotenv import load_dotenv\n",
|
|
"load_dotenv()\n",
|
|
"\n",
|
|
"import sys\n",
|
|
"sys.path.insert(0, os.path.join(os.environ[\"FN_REGISTRY_ROOT\"], \"python\", \"functions\"))\n",
|
|
"from metabase.client import MetabaseClient\n",
|
|
"\n",
|
|
"client = MetabaseClient(os.environ[\"METABASE_URL\"], os.environ[\"METABASE_API_KEY\"])\n",
|
|
"\n",
|
|
"def query_to_df(sql: str) -> pd.DataFrame:\n",
|
|
" \"\"\"Execute SQL against BigQuery via Metabase API, return DataFrame.\"\"\"\n",
|
|
" result = client.request('POST', '/api/dataset', json={\n",
|
|
" 'database': 6, 'type': 'native', 'native': {'query': sql}\n",
|
|
" })\n",
|
|
" cols = [c['name'] for c in result['data']['cols']]\n",
|
|
" return pd.DataFrame(result['data']['rows'], columns=cols)\n",
|
|
"\n",
|
|
"TABLE = 'psql_dcpublic.call_transactions'\n",
|
|
"print('Setup OK')"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "d1e2f3a4",
|
|
"metadata": {},
|
|
"source": [
|
|
"## 2. Metricas generales"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 2,
|
|
"id": "e1f2a3b4",
|
|
"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>0</th>\n",
|
|
" </tr>\n",
|
|
" </thead>\n",
|
|
" <tbody>\n",
|
|
" <tr>\n",
|
|
" <th>total_rows</th>\n",
|
|
" <td>24763236</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>min_date</th>\n",
|
|
" <td>2022-11-14T01:11:00+01:00</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>max_date</th>\n",
|
|
" <td>2026-04-16T22:00:00+02:00</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>n_campaigns</th>\n",
|
|
" <td>57</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>n_agents</th>\n",
|
|
" <td>411</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>n_resolutions</th>\n",
|
|
" <td>4474</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>n_channels</th>\n",
|
|
" <td>166</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>n_entry_services</th>\n",
|
|
" <td>136</td>\n",
|
|
" </tr>\n",
|
|
" </tbody>\n",
|
|
"</table>\n",
|
|
"</div>"
|
|
],
|
|
"text/plain": [
|
|
" 0\n",
|
|
"total_rows 24763236\n",
|
|
"min_date 2022-11-14T01:11:00+01:00\n",
|
|
"max_date 2026-04-16T22:00:00+02:00\n",
|
|
"n_campaigns 57\n",
|
|
"n_agents 411\n",
|
|
"n_resolutions 4474\n",
|
|
"n_channels 166\n",
|
|
"n_entry_services 136"
|
|
]
|
|
},
|
|
"execution_count": 2,
|
|
"metadata": {},
|
|
"output_type": "execute_result"
|
|
}
|
|
],
|
|
"source": [
|
|
"df_overview = query_to_df(f\"\"\"\n",
|
|
"SELECT\n",
|
|
" COUNT(*) AS total_rows,\n",
|
|
" MIN(date_time) AS min_date,\n",
|
|
" MAX(date_time) AS max_date,\n",
|
|
" COUNT(DISTINCT campaign_name) AS n_campaigns,\n",
|
|
" COUNT(DISTINCT agente) AS n_agents,\n",
|
|
" COUNT(DISTINCT description) AS n_resolutions,\n",
|
|
" COUNT(DISTINCT channel) AS n_channels,\n",
|
|
" COUNT(DISTINCT entry_service) AS n_entry_services\n",
|
|
"FROM {TABLE}\n",
|
|
"\"\"\")\n",
|
|
"\n",
|
|
"df_overview.T"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 3,
|
|
"id": "f1a2b3c4",
|
|
"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>null_count</th>\n",
|
|
" <th>field</th>\n",
|
|
" </tr>\n",
|
|
" </thead>\n",
|
|
" <tbody>\n",
|
|
" <tr>\n",
|
|
" <th>null_campaign</th>\n",
|
|
" <td>37578</td>\n",
|
|
" <td>campaign</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>null_resolution</th>\n",
|
|
" <td>231291</td>\n",
|
|
" <td>resolution</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>null_t_convers</th>\n",
|
|
" <td>0</td>\n",
|
|
" <td>t_convers</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>null_agente</th>\n",
|
|
" <td>0</td>\n",
|
|
" <td>agente</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>null_cliente</th>\n",
|
|
" <td>54758</td>\n",
|
|
" <td>cliente</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>null_channel</th>\n",
|
|
" <td>0</td>\n",
|
|
" <td>channel</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>null_entry_service</th>\n",
|
|
" <td>13899517</td>\n",
|
|
" <td>entry_service</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>null_call_url</th>\n",
|
|
" <td>13899526</td>\n",
|
|
" <td>call_url</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>null_customerName</th>\n",
|
|
" <td>13954273</td>\n",
|
|
" <td>customerName</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>null_salesforceId</th>\n",
|
|
" <td>13954273</td>\n",
|
|
" <td>salesforceId</td>\n",
|
|
" </tr>\n",
|
|
" </tbody>\n",
|
|
"</table>\n",
|
|
"</div>"
|
|
],
|
|
"text/plain": [
|
|
" null_count field\n",
|
|
"null_campaign 37578 campaign\n",
|
|
"null_resolution 231291 resolution\n",
|
|
"null_t_convers 0 t_convers\n",
|
|
"null_agente 0 agente\n",
|
|
"null_cliente 54758 cliente\n",
|
|
"null_channel 0 channel\n",
|
|
"null_entry_service 13899517 entry_service\n",
|
|
"null_call_url 13899526 call_url\n",
|
|
"null_customerName 13954273 customerName\n",
|
|
"null_salesforceId 13954273 salesforceId"
|
|
]
|
|
},
|
|
"execution_count": 3,
|
|
"metadata": {},
|
|
"output_type": "execute_result"
|
|
}
|
|
],
|
|
"source": [
|
|
"# Conteo de nulos por campo clave\n",
|
|
"df_nulls = query_to_df(f\"\"\"\n",
|
|
"SELECT\n",
|
|
" COUNTIF(campaign_name IS NULL) AS null_campaign,\n",
|
|
" COUNTIF(description IS NULL) AS null_resolution,\n",
|
|
" COUNTIF(t_convers IS NULL) AS null_t_convers,\n",
|
|
" COUNTIF(agente IS NULL) AS null_agente,\n",
|
|
" COUNTIF(cliente IS NULL) AS null_cliente,\n",
|
|
" COUNTIF(channel IS NULL) AS null_channel,\n",
|
|
" COUNTIF(entry_service IS NULL) AS null_entry_service,\n",
|
|
" COUNTIF(call_url IS NULL) AS null_call_url,\n",
|
|
" COUNTIF(customerName IS NULL) AS null_customerName,\n",
|
|
" COUNTIF(customerSalesforceId IS NULL) AS null_salesforceId\n",
|
|
"FROM {TABLE}\n",
|
|
"\"\"\")\n",
|
|
"\n",
|
|
"nulls = df_nulls.T.rename(columns={0: 'null_count'})\n",
|
|
"nulls['field'] = nulls.index.str.replace('null_', '')\n",
|
|
"nulls"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "a2b3c4d5",
|
|
"metadata": {},
|
|
"source": [
|
|
"## 3. Distribucion de campanas"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 4,
|
|
"id": "b2c3d4e5",
|
|
"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>campaign_name</th>\n",
|
|
" <th>calls</th>\n",
|
|
" </tr>\n",
|
|
" </thead>\n",
|
|
" <tbody>\n",
|
|
" <tr>\n",
|
|
" <th>0</th>\n",
|
|
" <td>Aurgi Emisión</td>\n",
|
|
" <td>11202320</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>1</th>\n",
|
|
" <td>Aurgi Presupuestos Emision</td>\n",
|
|
" <td>3559068</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>2</th>\n",
|
|
" <td>MotorTown Emisión</td>\n",
|
|
" <td>2224247</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>3</th>\n",
|
|
" <td>Aurgi</td>\n",
|
|
" <td>2196552</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>4</th>\n",
|
|
" <td>Aurgi Neumáticos</td>\n",
|
|
" <td>1498076</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>5</th>\n",
|
|
" <td>Motortown Presupuestos Emisión</td>\n",
|
|
" <td>1167890</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>6</th>\n",
|
|
" <td>Aurgi Perdidas</td>\n",
|
|
" <td>666204</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>7</th>\n",
|
|
" <td>MotorTown Perdidas</td>\n",
|
|
" <td>557750</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>8</th>\n",
|
|
" <td>Mutua Entrante</td>\n",
|
|
" <td>511411</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>9</th>\n",
|
|
" <td>MotorTown</td>\n",
|
|
" <td>438702</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>10</th>\n",
|
|
" <td>Aurgi Neumáticos Perdidas</td>\n",
|
|
" <td>345644</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>11</th>\n",
|
|
" <td>Entrante</td>\n",
|
|
" <td>112211</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>12</th>\n",
|
|
" <td>Mutua Entrante Perdidas</td>\n",
|
|
" <td>43290</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>13</th>\n",
|
|
" <td>Mutua Emisión INC Carglass</td>\n",
|
|
" <td>30698</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>14</th>\n",
|
|
" <td>MUTUA NO SHOW</td>\n",
|
|
" <td>21499</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>15</th>\n",
|
|
" <td>MUTUA Campaña de marcaje de lunas</td>\n",
|
|
" <td>20711</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>16</th>\n",
|
|
" <td>Aurgi-Llamadas Perdidas</td>\n",
|
|
" <td>12959</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>17</th>\n",
|
|
" <td>Mutua Encuestas</td>\n",
|
|
" <td>11934</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>18</th>\n",
|
|
" <td>Aurgi-Reparaciones 2025-09</td>\n",
|
|
" <td>9669</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>19</th>\n",
|
|
" <td>Aurgi-Reparaciones 2025-06</td>\n",
|
|
" <td>7864</td>\n",
|
|
" </tr>\n",
|
|
" </tbody>\n",
|
|
"</table>\n",
|
|
"</div>"
|
|
],
|
|
"text/plain": [
|
|
" campaign_name calls\n",
|
|
"0 Aurgi Emisión 11202320\n",
|
|
"1 Aurgi Presupuestos Emision 3559068\n",
|
|
"2 MotorTown Emisión 2224247\n",
|
|
"3 Aurgi 2196552\n",
|
|
"4 Aurgi Neumáticos 1498076\n",
|
|
"5 Motortown Presupuestos Emisión 1167890\n",
|
|
"6 Aurgi Perdidas 666204\n",
|
|
"7 MotorTown Perdidas 557750\n",
|
|
"8 Mutua Entrante 511411\n",
|
|
"9 MotorTown 438702\n",
|
|
"10 Aurgi Neumáticos Perdidas 345644\n",
|
|
"11 Entrante 112211\n",
|
|
"12 Mutua Entrante Perdidas 43290\n",
|
|
"13 Mutua Emisión INC Carglass 30698\n",
|
|
"14 MUTUA NO SHOW 21499\n",
|
|
"15 MUTUA Campaña de marcaje de lunas 20711\n",
|
|
"16 Aurgi-Llamadas Perdidas 12959\n",
|
|
"17 Mutua Encuestas 11934\n",
|
|
"18 Aurgi-Reparaciones 2025-09 9669\n",
|
|
"19 Aurgi-Reparaciones 2025-06 7864"
|
|
]
|
|
},
|
|
"execution_count": 4,
|
|
"metadata": {},
|
|
"output_type": "execute_result"
|
|
}
|
|
],
|
|
"source": [
|
|
"df_campaigns = query_to_df(f\"\"\"\n",
|
|
"SELECT\n",
|
|
" campaign_name,\n",
|
|
" COUNT(*) AS calls\n",
|
|
"FROM {TABLE}\n",
|
|
"WHERE campaign_name IS NOT NULL\n",
|
|
"GROUP BY campaign_name\n",
|
|
"ORDER BY calls DESC\n",
|
|
"LIMIT 20\n",
|
|
"\"\"\")\n",
|
|
"\n",
|
|
"df_campaigns['calls'] = df_campaigns['calls'].astype(int)\n",
|
|
"df_campaigns"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 5,
|
|
"id": "c2d3e4f5",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"data": {
|
|
"application/vnd.plotly.v1+json": {
|
|
"config": {
|
|
"plotlyServerURL": "https://plot.ly"
|
|
},
|
|
"data": [
|
|
{
|
|
"hovertemplate": "Llamadas=%{text}<br>Campana=%{y}<extra></extra>",
|
|
"legendgroup": "",
|
|
"marker": {
|
|
"color": "#636efa",
|
|
"pattern": {
|
|
"shape": ""
|
|
}
|
|
},
|
|
"name": "",
|
|
"orientation": "h",
|
|
"showlegend": false,
|
|
"text": {
|
|
"bdata": "AAAAAOJdZUEAAAAATidLQQAAAIA7+EBBAAAAACTCQEEAAAAA3Ns2QQAAAAAS0jFBAAAAALhUJEEAAAAAbAUhQQAAAADMNh9BAAAAALjGGkEAAAAAsBgVQQAAAAAwZftAAAAAAEAj5UAAAAAAgPrdQAAAAADA/tRAAAAAAMA51EAAAAAAgE/JQAAAAAAAT8dAAAAAAIDiwkAAAAAAALi+QA==",
|
|
"dtype": "f8"
|
|
},
|
|
"textposition": "outside",
|
|
"texttemplate": "%{text:,.0f}",
|
|
"type": "bar",
|
|
"x": {
|
|
"bdata": "EO+qAJxONgB38CEASIQhANzbFgAS0hEAXCoKALaCCACzzQcArrEGACxGBQBTtgEAGqkAAOp3AAD7UwAA51AAAJ8yAACeLgAAxSUAALgeAAA=",
|
|
"dtype": "i4"
|
|
},
|
|
"xaxis": "x",
|
|
"y": [
|
|
"Aurgi Emisión",
|
|
"Aurgi Presupuestos Emision",
|
|
"MotorTown Emisión",
|
|
"Aurgi",
|
|
"Aurgi Neumáticos",
|
|
"Motortown Presupuestos Emisión",
|
|
"Aurgi Perdidas",
|
|
"MotorTown Perdidas",
|
|
"Mutua Entrante",
|
|
"MotorTown",
|
|
"Aurgi Neumáticos Perdidas",
|
|
"Entrante",
|
|
"Mutua Entrante Perdidas",
|
|
"Mutua Emisión INC Carglass",
|
|
"MUTUA NO SHOW",
|
|
"MUTUA Campaña de marcaje de lunas",
|
|
"Aurgi-Llamadas Perdidas",
|
|
"Mutua Encuestas",
|
|
"Aurgi-Reparaciones 2025-09",
|
|
"Aurgi-Reparaciones 2025-06"
|
|
],
|
|
"yaxis": "y"
|
|
}
|
|
],
|
|
"layout": {
|
|
"barmode": "relative",
|
|
"height": 600,
|
|
"legend": {
|
|
"tracegroupgap": 0
|
|
},
|
|
"template": {
|
|
"data": {
|
|
"bar": [
|
|
{
|
|
"error_x": {
|
|
"color": "#2a3f5f"
|
|
},
|
|
"error_y": {
|
|
"color": "#2a3f5f"
|
|
},
|
|
"marker": {
|
|
"line": {
|
|
"color": "#E5ECF6",
|
|
"width": 0.5
|
|
},
|
|
"pattern": {
|
|
"fillmode": "overlay",
|
|
"size": 10,
|
|
"solidity": 0.2
|
|
}
|
|
},
|
|
"type": "bar"
|
|
}
|
|
],
|
|
"barpolar": [
|
|
{
|
|
"marker": {
|
|
"line": {
|
|
"color": "#E5ECF6",
|
|
"width": 0.5
|
|
},
|
|
"pattern": {
|
|
"fillmode": "overlay",
|
|
"size": 10,
|
|
"solidity": 0.2
|
|
}
|
|
},
|
|
"type": "barpolar"
|
|
}
|
|
],
|
|
"carpet": [
|
|
{
|
|
"aaxis": {
|
|
"endlinecolor": "#2a3f5f",
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"minorgridcolor": "white",
|
|
"startlinecolor": "#2a3f5f"
|
|
},
|
|
"baxis": {
|
|
"endlinecolor": "#2a3f5f",
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"minorgridcolor": "white",
|
|
"startlinecolor": "#2a3f5f"
|
|
},
|
|
"type": "carpet"
|
|
}
|
|
],
|
|
"choropleth": [
|
|
{
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
},
|
|
"type": "choropleth"
|
|
}
|
|
],
|
|
"contour": [
|
|
{
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
},
|
|
"colorscale": [
|
|
[
|
|
0,
|
|
"#0d0887"
|
|
],
|
|
[
|
|
0.1111111111111111,
|
|
"#46039f"
|
|
],
|
|
[
|
|
0.2222222222222222,
|
|
"#7201a8"
|
|
],
|
|
[
|
|
0.3333333333333333,
|
|
"#9c179e"
|
|
],
|
|
[
|
|
0.4444444444444444,
|
|
"#bd3786"
|
|
],
|
|
[
|
|
0.5555555555555556,
|
|
"#d8576b"
|
|
],
|
|
[
|
|
0.6666666666666666,
|
|
"#ed7953"
|
|
],
|
|
[
|
|
0.7777777777777778,
|
|
"#fb9f3a"
|
|
],
|
|
[
|
|
0.8888888888888888,
|
|
"#fdca26"
|
|
],
|
|
[
|
|
1,
|
|
"#f0f921"
|
|
]
|
|
],
|
|
"type": "contour"
|
|
}
|
|
],
|
|
"contourcarpet": [
|
|
{
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
},
|
|
"type": "contourcarpet"
|
|
}
|
|
],
|
|
"heatmap": [
|
|
{
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
},
|
|
"colorscale": [
|
|
[
|
|
0,
|
|
"#0d0887"
|
|
],
|
|
[
|
|
0.1111111111111111,
|
|
"#46039f"
|
|
],
|
|
[
|
|
0.2222222222222222,
|
|
"#7201a8"
|
|
],
|
|
[
|
|
0.3333333333333333,
|
|
"#9c179e"
|
|
],
|
|
[
|
|
0.4444444444444444,
|
|
"#bd3786"
|
|
],
|
|
[
|
|
0.5555555555555556,
|
|
"#d8576b"
|
|
],
|
|
[
|
|
0.6666666666666666,
|
|
"#ed7953"
|
|
],
|
|
[
|
|
0.7777777777777778,
|
|
"#fb9f3a"
|
|
],
|
|
[
|
|
0.8888888888888888,
|
|
"#fdca26"
|
|
],
|
|
[
|
|
1,
|
|
"#f0f921"
|
|
]
|
|
],
|
|
"type": "heatmap"
|
|
}
|
|
],
|
|
"histogram": [
|
|
{
|
|
"marker": {
|
|
"pattern": {
|
|
"fillmode": "overlay",
|
|
"size": 10,
|
|
"solidity": 0.2
|
|
}
|
|
},
|
|
"type": "histogram"
|
|
}
|
|
],
|
|
"histogram2d": [
|
|
{
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
},
|
|
"colorscale": [
|
|
[
|
|
0,
|
|
"#0d0887"
|
|
],
|
|
[
|
|
0.1111111111111111,
|
|
"#46039f"
|
|
],
|
|
[
|
|
0.2222222222222222,
|
|
"#7201a8"
|
|
],
|
|
[
|
|
0.3333333333333333,
|
|
"#9c179e"
|
|
],
|
|
[
|
|
0.4444444444444444,
|
|
"#bd3786"
|
|
],
|
|
[
|
|
0.5555555555555556,
|
|
"#d8576b"
|
|
],
|
|
[
|
|
0.6666666666666666,
|
|
"#ed7953"
|
|
],
|
|
[
|
|
0.7777777777777778,
|
|
"#fb9f3a"
|
|
],
|
|
[
|
|
0.8888888888888888,
|
|
"#fdca26"
|
|
],
|
|
[
|
|
1,
|
|
"#f0f921"
|
|
]
|
|
],
|
|
"type": "histogram2d"
|
|
}
|
|
],
|
|
"histogram2dcontour": [
|
|
{
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
},
|
|
"colorscale": [
|
|
[
|
|
0,
|
|
"#0d0887"
|
|
],
|
|
[
|
|
0.1111111111111111,
|
|
"#46039f"
|
|
],
|
|
[
|
|
0.2222222222222222,
|
|
"#7201a8"
|
|
],
|
|
[
|
|
0.3333333333333333,
|
|
"#9c179e"
|
|
],
|
|
[
|
|
0.4444444444444444,
|
|
"#bd3786"
|
|
],
|
|
[
|
|
0.5555555555555556,
|
|
"#d8576b"
|
|
],
|
|
[
|
|
0.6666666666666666,
|
|
"#ed7953"
|
|
],
|
|
[
|
|
0.7777777777777778,
|
|
"#fb9f3a"
|
|
],
|
|
[
|
|
0.8888888888888888,
|
|
"#fdca26"
|
|
],
|
|
[
|
|
1,
|
|
"#f0f921"
|
|
]
|
|
],
|
|
"type": "histogram2dcontour"
|
|
}
|
|
],
|
|
"mesh3d": [
|
|
{
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
},
|
|
"type": "mesh3d"
|
|
}
|
|
],
|
|
"parcoords": [
|
|
{
|
|
"line": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "parcoords"
|
|
}
|
|
],
|
|
"pie": [
|
|
{
|
|
"automargin": true,
|
|
"type": "pie"
|
|
}
|
|
],
|
|
"scatter": [
|
|
{
|
|
"fillpattern": {
|
|
"fillmode": "overlay",
|
|
"size": 10,
|
|
"solidity": 0.2
|
|
},
|
|
"type": "scatter"
|
|
}
|
|
],
|
|
"scatter3d": [
|
|
{
|
|
"line": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scatter3d"
|
|
}
|
|
],
|
|
"scattercarpet": [
|
|
{
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scattercarpet"
|
|
}
|
|
],
|
|
"scattergeo": [
|
|
{
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scattergeo"
|
|
}
|
|
],
|
|
"scattergl": [
|
|
{
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scattergl"
|
|
}
|
|
],
|
|
"scattermap": [
|
|
{
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scattermap"
|
|
}
|
|
],
|
|
"scattermapbox": [
|
|
{
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scattermapbox"
|
|
}
|
|
],
|
|
"scatterpolar": [
|
|
{
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scatterpolar"
|
|
}
|
|
],
|
|
"scatterpolargl": [
|
|
{
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scatterpolargl"
|
|
}
|
|
],
|
|
"scatterternary": [
|
|
{
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scatterternary"
|
|
}
|
|
],
|
|
"surface": [
|
|
{
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
},
|
|
"colorscale": [
|
|
[
|
|
0,
|
|
"#0d0887"
|
|
],
|
|
[
|
|
0.1111111111111111,
|
|
"#46039f"
|
|
],
|
|
[
|
|
0.2222222222222222,
|
|
"#7201a8"
|
|
],
|
|
[
|
|
0.3333333333333333,
|
|
"#9c179e"
|
|
],
|
|
[
|
|
0.4444444444444444,
|
|
"#bd3786"
|
|
],
|
|
[
|
|
0.5555555555555556,
|
|
"#d8576b"
|
|
],
|
|
[
|
|
0.6666666666666666,
|
|
"#ed7953"
|
|
],
|
|
[
|
|
0.7777777777777778,
|
|
"#fb9f3a"
|
|
],
|
|
[
|
|
0.8888888888888888,
|
|
"#fdca26"
|
|
],
|
|
[
|
|
1,
|
|
"#f0f921"
|
|
]
|
|
],
|
|
"type": "surface"
|
|
}
|
|
],
|
|
"table": [
|
|
{
|
|
"cells": {
|
|
"fill": {
|
|
"color": "#EBF0F8"
|
|
},
|
|
"line": {
|
|
"color": "white"
|
|
}
|
|
},
|
|
"header": {
|
|
"fill": {
|
|
"color": "#C8D4E3"
|
|
},
|
|
"line": {
|
|
"color": "white"
|
|
}
|
|
},
|
|
"type": "table"
|
|
}
|
|
]
|
|
},
|
|
"layout": {
|
|
"annotationdefaults": {
|
|
"arrowcolor": "#2a3f5f",
|
|
"arrowhead": 0,
|
|
"arrowwidth": 1
|
|
},
|
|
"autotypenumbers": "strict",
|
|
"coloraxis": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"colorscale": {
|
|
"diverging": [
|
|
[
|
|
0,
|
|
"#8e0152"
|
|
],
|
|
[
|
|
0.1,
|
|
"#c51b7d"
|
|
],
|
|
[
|
|
0.2,
|
|
"#de77ae"
|
|
],
|
|
[
|
|
0.3,
|
|
"#f1b6da"
|
|
],
|
|
[
|
|
0.4,
|
|
"#fde0ef"
|
|
],
|
|
[
|
|
0.5,
|
|
"#f7f7f7"
|
|
],
|
|
[
|
|
0.6,
|
|
"#e6f5d0"
|
|
],
|
|
[
|
|
0.7,
|
|
"#b8e186"
|
|
],
|
|
[
|
|
0.8,
|
|
"#7fbc41"
|
|
],
|
|
[
|
|
0.9,
|
|
"#4d9221"
|
|
],
|
|
[
|
|
1,
|
|
"#276419"
|
|
]
|
|
],
|
|
"sequential": [
|
|
[
|
|
0,
|
|
"#0d0887"
|
|
],
|
|
[
|
|
0.1111111111111111,
|
|
"#46039f"
|
|
],
|
|
[
|
|
0.2222222222222222,
|
|
"#7201a8"
|
|
],
|
|
[
|
|
0.3333333333333333,
|
|
"#9c179e"
|
|
],
|
|
[
|
|
0.4444444444444444,
|
|
"#bd3786"
|
|
],
|
|
[
|
|
0.5555555555555556,
|
|
"#d8576b"
|
|
],
|
|
[
|
|
0.6666666666666666,
|
|
"#ed7953"
|
|
],
|
|
[
|
|
0.7777777777777778,
|
|
"#fb9f3a"
|
|
],
|
|
[
|
|
0.8888888888888888,
|
|
"#fdca26"
|
|
],
|
|
[
|
|
1,
|
|
"#f0f921"
|
|
]
|
|
],
|
|
"sequentialminus": [
|
|
[
|
|
0,
|
|
"#0d0887"
|
|
],
|
|
[
|
|
0.1111111111111111,
|
|
"#46039f"
|
|
],
|
|
[
|
|
0.2222222222222222,
|
|
"#7201a8"
|
|
],
|
|
[
|
|
0.3333333333333333,
|
|
"#9c179e"
|
|
],
|
|
[
|
|
0.4444444444444444,
|
|
"#bd3786"
|
|
],
|
|
[
|
|
0.5555555555555556,
|
|
"#d8576b"
|
|
],
|
|
[
|
|
0.6666666666666666,
|
|
"#ed7953"
|
|
],
|
|
[
|
|
0.7777777777777778,
|
|
"#fb9f3a"
|
|
],
|
|
[
|
|
0.8888888888888888,
|
|
"#fdca26"
|
|
],
|
|
[
|
|
1,
|
|
"#f0f921"
|
|
]
|
|
]
|
|
},
|
|
"colorway": [
|
|
"#636efa",
|
|
"#EF553B",
|
|
"#00cc96",
|
|
"#ab63fa",
|
|
"#FFA15A",
|
|
"#19d3f3",
|
|
"#FF6692",
|
|
"#B6E880",
|
|
"#FF97FF",
|
|
"#FECB52"
|
|
],
|
|
"font": {
|
|
"color": "#2a3f5f"
|
|
},
|
|
"geo": {
|
|
"bgcolor": "white",
|
|
"lakecolor": "white",
|
|
"landcolor": "#E5ECF6",
|
|
"showlakes": true,
|
|
"showland": true,
|
|
"subunitcolor": "white"
|
|
},
|
|
"hoverlabel": {
|
|
"align": "left"
|
|
},
|
|
"hovermode": "closest",
|
|
"mapbox": {
|
|
"style": "light"
|
|
},
|
|
"paper_bgcolor": "white",
|
|
"plot_bgcolor": "#E5ECF6",
|
|
"polar": {
|
|
"angularaxis": {
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"ticks": ""
|
|
},
|
|
"bgcolor": "#E5ECF6",
|
|
"radialaxis": {
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"scene": {
|
|
"xaxis": {
|
|
"backgroundcolor": "#E5ECF6",
|
|
"gridcolor": "white",
|
|
"gridwidth": 2,
|
|
"linecolor": "white",
|
|
"showbackground": true,
|
|
"ticks": "",
|
|
"zerolinecolor": "white"
|
|
},
|
|
"yaxis": {
|
|
"backgroundcolor": "#E5ECF6",
|
|
"gridcolor": "white",
|
|
"gridwidth": 2,
|
|
"linecolor": "white",
|
|
"showbackground": true,
|
|
"ticks": "",
|
|
"zerolinecolor": "white"
|
|
},
|
|
"zaxis": {
|
|
"backgroundcolor": "#E5ECF6",
|
|
"gridcolor": "white",
|
|
"gridwidth": 2,
|
|
"linecolor": "white",
|
|
"showbackground": true,
|
|
"ticks": "",
|
|
"zerolinecolor": "white"
|
|
}
|
|
},
|
|
"shapedefaults": {
|
|
"line": {
|
|
"color": "#2a3f5f"
|
|
}
|
|
},
|
|
"ternary": {
|
|
"aaxis": {
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"ticks": ""
|
|
},
|
|
"baxis": {
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"ticks": ""
|
|
},
|
|
"bgcolor": "#E5ECF6",
|
|
"caxis": {
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"title": {
|
|
"x": 0.05
|
|
},
|
|
"xaxis": {
|
|
"automargin": true,
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"ticks": "",
|
|
"title": {
|
|
"standoff": 15
|
|
},
|
|
"zerolinecolor": "white",
|
|
"zerolinewidth": 2
|
|
},
|
|
"yaxis": {
|
|
"automargin": true,
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"ticks": "",
|
|
"title": {
|
|
"standoff": 15
|
|
},
|
|
"zerolinecolor": "white",
|
|
"zerolinewidth": 2
|
|
}
|
|
}
|
|
},
|
|
"title": {
|
|
"text": "Top 20 campanas por volumen de llamadas"
|
|
},
|
|
"xaxis": {
|
|
"anchor": "y",
|
|
"domain": [
|
|
0,
|
|
1
|
|
],
|
|
"title": {
|
|
"text": "Llamadas"
|
|
}
|
|
},
|
|
"yaxis": {
|
|
"anchor": "x",
|
|
"categoryorder": "total ascending",
|
|
"domain": [
|
|
0,
|
|
1
|
|
],
|
|
"title": {
|
|
"text": "Campana"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
},
|
|
"metadata": {},
|
|
"output_type": "display_data",
|
|
"transient": {}
|
|
}
|
|
],
|
|
"source": [
|
|
"fig = px.bar(\n",
|
|
" df_campaigns,\n",
|
|
" x='calls', y='campaign_name',\n",
|
|
" orientation='h',\n",
|
|
" title='Top 20 campanas por volumen de llamadas',\n",
|
|
" labels={'calls': 'Llamadas', 'campaign_name': 'Campana'},\n",
|
|
" text='calls'\n",
|
|
")\n",
|
|
"fig.update_layout(yaxis={'categoryorder': 'total ascending'}, height=600)\n",
|
|
"fig.update_traces(texttemplate='%{text:,.0f}', textposition='outside')\n",
|
|
"fig.show()"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "d2e3f4a5",
|
|
"metadata": {},
|
|
"source": [
|
|
"## 4. Resoluciones"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 6,
|
|
"id": "e2f3a4b5",
|
|
"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>resolution</th>\n",
|
|
" <th>calls</th>\n",
|
|
" </tr>\n",
|
|
" </thead>\n",
|
|
" <tbody>\n",
|
|
" <tr>\n",
|
|
" <th>0</th>\n",
|
|
" <td>Comunican</td>\n",
|
|
" <td>9133549</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>1</th>\n",
|
|
" <td>Contestador Automático</td>\n",
|
|
" <td>3836254</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>2</th>\n",
|
|
" <td>Otras causas</td>\n",
|
|
" <td>2945080</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>3</th>\n",
|
|
" <td>La llam.no puede alcanzar dest</td>\n",
|
|
" <td>1821210</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>4</th>\n",
|
|
" <td>No Contestan</td>\n",
|
|
" <td>1810221</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>5</th>\n",
|
|
" <td>CERRAR LEAD</td>\n",
|
|
" <td>1660102</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>6</th>\n",
|
|
" <td>Abandonada</td>\n",
|
|
" <td>1241413</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>7</th>\n",
|
|
" <td>Llamada No Atendida</td>\n",
|
|
" <td>508446</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>8</th>\n",
|
|
" <td>ACEPTADO</td>\n",
|
|
" <td>370223</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>9</th>\n",
|
|
" <td>No hay Ring Back</td>\n",
|
|
" <td>178985</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>10</th>\n",
|
|
" <td>Fuera Intervalo Rellamada</td>\n",
|
|
" <td>106189</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>11</th>\n",
|
|
" <td>ACEPTADA</td>\n",
|
|
" <td>61895</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>12</th>\n",
|
|
" <td>LLAM BASURA GESTIÓN CITA</td>\n",
|
|
" <td>60436</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>13</th>\n",
|
|
" <td>OTROS</td>\n",
|
|
" <td>59347</td>\n",
|
|
" </tr>\n",
|
|
" <tr>\n",
|
|
" <th>14</th>\n",
|
|
" <td>Error CTI</td>\n",
|
|
" <td>55814</td>\n",
|
|
" </tr>\n",
|
|
" </tbody>\n",
|
|
"</table>\n",
|
|
"</div>"
|
|
],
|
|
"text/plain": [
|
|
" resolution calls\n",
|
|
"0 Comunican 9133549\n",
|
|
"1 Contestador Automático 3836254\n",
|
|
"2 Otras causas 2945080\n",
|
|
"3 La llam.no puede alcanzar dest 1821210\n",
|
|
"4 No Contestan 1810221\n",
|
|
"5 CERRAR LEAD 1660102\n",
|
|
"6 Abandonada 1241413\n",
|
|
"7 Llamada No Atendida 508446\n",
|
|
"8 ACEPTADO 370223\n",
|
|
"9 No hay Ring Back 178985\n",
|
|
"10 Fuera Intervalo Rellamada 106189\n",
|
|
"11 ACEPTADA 61895\n",
|
|
"12 LLAM BASURA GESTIÓN CITA 60436\n",
|
|
"13 OTROS 59347\n",
|
|
"14 Error CTI 55814"
|
|
]
|
|
},
|
|
"execution_count": 6,
|
|
"metadata": {},
|
|
"output_type": "execute_result"
|
|
}
|
|
],
|
|
"source": [
|
|
"df_resolutions = query_to_df(f\"\"\"\n",
|
|
"SELECT\n",
|
|
" description AS resolution,\n",
|
|
" COUNT(*) AS calls\n",
|
|
"FROM {TABLE}\n",
|
|
"WHERE description IS NOT NULL\n",
|
|
"GROUP BY description\n",
|
|
"ORDER BY calls DESC\n",
|
|
"LIMIT 15\n",
|
|
"\"\"\")\n",
|
|
"\n",
|
|
"df_resolutions['calls'] = df_resolutions['calls'].astype(int)\n",
|
|
"df_resolutions"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 7,
|
|
"id": "f2a3b4c5",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"data": {
|
|
"application/vnd.plotly.v1+json": {
|
|
"config": {
|
|
"plotlyServerURL": "https://plot.ly"
|
|
},
|
|
"data": [
|
|
{
|
|
"hovertemplate": "Llamadas=%{text}<br>Resolucion=%{y}<extra></extra>",
|
|
"legendgroup": "",
|
|
"marker": {
|
|
"color": "#636efa",
|
|
"pattern": {
|
|
"shape": ""
|
|
}
|
|
},
|
|
"name": "",
|
|
"orientation": "h",
|
|
"showlegend": false,
|
|
"text": {
|
|
"bdata": "AAAAoL1rYUEAAAAAr0RNQQAAAAAceEZBAAAAABrKO0EAAAAALZ87QQAAAADGVDlBAAAAAEXxMkEAAAAAeAgfQQAAAAC8mBZBAAAAAEjZBUEAAAAA0Oz5QAAAAADgOO5AAAAAAICC7UAAAAAAYPrsQAAAAADAQOtA",
|
|
"dtype": "f8"
|
|
},
|
|
"textposition": "outside",
|
|
"texttemplate": "%{text:,.0f}",
|
|
"type": "bar",
|
|
"x": {
|
|
"bdata": "7V2LAF6JOgA48CwAGsobAC2fGwDGVBkARfESAB7CBwAvpgUAKbsCAM2eAQDH8QAAFOwAANPnAAAG2gAA",
|
|
"dtype": "i4"
|
|
},
|
|
"xaxis": "x",
|
|
"y": [
|
|
"Comunican",
|
|
"Contestador Automático",
|
|
"Otras causas",
|
|
"La llam.no puede alcanzar dest",
|
|
"No Contestan",
|
|
"CERRAR LEAD",
|
|
"Abandonada",
|
|
"Llamada No Atendida",
|
|
"ACEPTADO",
|
|
"No hay Ring Back",
|
|
"Fuera Intervalo Rellamada",
|
|
"ACEPTADA",
|
|
"LLAM BASURA GESTIÓN CITA",
|
|
"OTROS",
|
|
"Error CTI"
|
|
],
|
|
"yaxis": "y"
|
|
}
|
|
],
|
|
"layout": {
|
|
"barmode": "relative",
|
|
"height": 500,
|
|
"legend": {
|
|
"tracegroupgap": 0
|
|
},
|
|
"template": {
|
|
"data": {
|
|
"bar": [
|
|
{
|
|
"error_x": {
|
|
"color": "#2a3f5f"
|
|
},
|
|
"error_y": {
|
|
"color": "#2a3f5f"
|
|
},
|
|
"marker": {
|
|
"line": {
|
|
"color": "#E5ECF6",
|
|
"width": 0.5
|
|
},
|
|
"pattern": {
|
|
"fillmode": "overlay",
|
|
"size": 10,
|
|
"solidity": 0.2
|
|
}
|
|
},
|
|
"type": "bar"
|
|
}
|
|
],
|
|
"barpolar": [
|
|
{
|
|
"marker": {
|
|
"line": {
|
|
"color": "#E5ECF6",
|
|
"width": 0.5
|
|
},
|
|
"pattern": {
|
|
"fillmode": "overlay",
|
|
"size": 10,
|
|
"solidity": 0.2
|
|
}
|
|
},
|
|
"type": "barpolar"
|
|
}
|
|
],
|
|
"carpet": [
|
|
{
|
|
"aaxis": {
|
|
"endlinecolor": "#2a3f5f",
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"minorgridcolor": "white",
|
|
"startlinecolor": "#2a3f5f"
|
|
},
|
|
"baxis": {
|
|
"endlinecolor": "#2a3f5f",
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"minorgridcolor": "white",
|
|
"startlinecolor": "#2a3f5f"
|
|
},
|
|
"type": "carpet"
|
|
}
|
|
],
|
|
"choropleth": [
|
|
{
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
},
|
|
"type": "choropleth"
|
|
}
|
|
],
|
|
"contour": [
|
|
{
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
},
|
|
"colorscale": [
|
|
[
|
|
0,
|
|
"#0d0887"
|
|
],
|
|
[
|
|
0.1111111111111111,
|
|
"#46039f"
|
|
],
|
|
[
|
|
0.2222222222222222,
|
|
"#7201a8"
|
|
],
|
|
[
|
|
0.3333333333333333,
|
|
"#9c179e"
|
|
],
|
|
[
|
|
0.4444444444444444,
|
|
"#bd3786"
|
|
],
|
|
[
|
|
0.5555555555555556,
|
|
"#d8576b"
|
|
],
|
|
[
|
|
0.6666666666666666,
|
|
"#ed7953"
|
|
],
|
|
[
|
|
0.7777777777777778,
|
|
"#fb9f3a"
|
|
],
|
|
[
|
|
0.8888888888888888,
|
|
"#fdca26"
|
|
],
|
|
[
|
|
1,
|
|
"#f0f921"
|
|
]
|
|
],
|
|
"type": "contour"
|
|
}
|
|
],
|
|
"contourcarpet": [
|
|
{
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
},
|
|
"type": "contourcarpet"
|
|
}
|
|
],
|
|
"heatmap": [
|
|
{
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
},
|
|
"colorscale": [
|
|
[
|
|
0,
|
|
"#0d0887"
|
|
],
|
|
[
|
|
0.1111111111111111,
|
|
"#46039f"
|
|
],
|
|
[
|
|
0.2222222222222222,
|
|
"#7201a8"
|
|
],
|
|
[
|
|
0.3333333333333333,
|
|
"#9c179e"
|
|
],
|
|
[
|
|
0.4444444444444444,
|
|
"#bd3786"
|
|
],
|
|
[
|
|
0.5555555555555556,
|
|
"#d8576b"
|
|
],
|
|
[
|
|
0.6666666666666666,
|
|
"#ed7953"
|
|
],
|
|
[
|
|
0.7777777777777778,
|
|
"#fb9f3a"
|
|
],
|
|
[
|
|
0.8888888888888888,
|
|
"#fdca26"
|
|
],
|
|
[
|
|
1,
|
|
"#f0f921"
|
|
]
|
|
],
|
|
"type": "heatmap"
|
|
}
|
|
],
|
|
"histogram": [
|
|
{
|
|
"marker": {
|
|
"pattern": {
|
|
"fillmode": "overlay",
|
|
"size": 10,
|
|
"solidity": 0.2
|
|
}
|
|
},
|
|
"type": "histogram"
|
|
}
|
|
],
|
|
"histogram2d": [
|
|
{
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
},
|
|
"colorscale": [
|
|
[
|
|
0,
|
|
"#0d0887"
|
|
],
|
|
[
|
|
0.1111111111111111,
|
|
"#46039f"
|
|
],
|
|
[
|
|
0.2222222222222222,
|
|
"#7201a8"
|
|
],
|
|
[
|
|
0.3333333333333333,
|
|
"#9c179e"
|
|
],
|
|
[
|
|
0.4444444444444444,
|
|
"#bd3786"
|
|
],
|
|
[
|
|
0.5555555555555556,
|
|
"#d8576b"
|
|
],
|
|
[
|
|
0.6666666666666666,
|
|
"#ed7953"
|
|
],
|
|
[
|
|
0.7777777777777778,
|
|
"#fb9f3a"
|
|
],
|
|
[
|
|
0.8888888888888888,
|
|
"#fdca26"
|
|
],
|
|
[
|
|
1,
|
|
"#f0f921"
|
|
]
|
|
],
|
|
"type": "histogram2d"
|
|
}
|
|
],
|
|
"histogram2dcontour": [
|
|
{
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
},
|
|
"colorscale": [
|
|
[
|
|
0,
|
|
"#0d0887"
|
|
],
|
|
[
|
|
0.1111111111111111,
|
|
"#46039f"
|
|
],
|
|
[
|
|
0.2222222222222222,
|
|
"#7201a8"
|
|
],
|
|
[
|
|
0.3333333333333333,
|
|
"#9c179e"
|
|
],
|
|
[
|
|
0.4444444444444444,
|
|
"#bd3786"
|
|
],
|
|
[
|
|
0.5555555555555556,
|
|
"#d8576b"
|
|
],
|
|
[
|
|
0.6666666666666666,
|
|
"#ed7953"
|
|
],
|
|
[
|
|
0.7777777777777778,
|
|
"#fb9f3a"
|
|
],
|
|
[
|
|
0.8888888888888888,
|
|
"#fdca26"
|
|
],
|
|
[
|
|
1,
|
|
"#f0f921"
|
|
]
|
|
],
|
|
"type": "histogram2dcontour"
|
|
}
|
|
],
|
|
"mesh3d": [
|
|
{
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
},
|
|
"type": "mesh3d"
|
|
}
|
|
],
|
|
"parcoords": [
|
|
{
|
|
"line": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "parcoords"
|
|
}
|
|
],
|
|
"pie": [
|
|
{
|
|
"automargin": true,
|
|
"type": "pie"
|
|
}
|
|
],
|
|
"scatter": [
|
|
{
|
|
"fillpattern": {
|
|
"fillmode": "overlay",
|
|
"size": 10,
|
|
"solidity": 0.2
|
|
},
|
|
"type": "scatter"
|
|
}
|
|
],
|
|
"scatter3d": [
|
|
{
|
|
"line": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scatter3d"
|
|
}
|
|
],
|
|
"scattercarpet": [
|
|
{
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scattercarpet"
|
|
}
|
|
],
|
|
"scattergeo": [
|
|
{
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scattergeo"
|
|
}
|
|
],
|
|
"scattergl": [
|
|
{
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scattergl"
|
|
}
|
|
],
|
|
"scattermap": [
|
|
{
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scattermap"
|
|
}
|
|
],
|
|
"scattermapbox": [
|
|
{
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scattermapbox"
|
|
}
|
|
],
|
|
"scatterpolar": [
|
|
{
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scatterpolar"
|
|
}
|
|
],
|
|
"scatterpolargl": [
|
|
{
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scatterpolargl"
|
|
}
|
|
],
|
|
"scatterternary": [
|
|
{
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scatterternary"
|
|
}
|
|
],
|
|
"surface": [
|
|
{
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
},
|
|
"colorscale": [
|
|
[
|
|
0,
|
|
"#0d0887"
|
|
],
|
|
[
|
|
0.1111111111111111,
|
|
"#46039f"
|
|
],
|
|
[
|
|
0.2222222222222222,
|
|
"#7201a8"
|
|
],
|
|
[
|
|
0.3333333333333333,
|
|
"#9c179e"
|
|
],
|
|
[
|
|
0.4444444444444444,
|
|
"#bd3786"
|
|
],
|
|
[
|
|
0.5555555555555556,
|
|
"#d8576b"
|
|
],
|
|
[
|
|
0.6666666666666666,
|
|
"#ed7953"
|
|
],
|
|
[
|
|
0.7777777777777778,
|
|
"#fb9f3a"
|
|
],
|
|
[
|
|
0.8888888888888888,
|
|
"#fdca26"
|
|
],
|
|
[
|
|
1,
|
|
"#f0f921"
|
|
]
|
|
],
|
|
"type": "surface"
|
|
}
|
|
],
|
|
"table": [
|
|
{
|
|
"cells": {
|
|
"fill": {
|
|
"color": "#EBF0F8"
|
|
},
|
|
"line": {
|
|
"color": "white"
|
|
}
|
|
},
|
|
"header": {
|
|
"fill": {
|
|
"color": "#C8D4E3"
|
|
},
|
|
"line": {
|
|
"color": "white"
|
|
}
|
|
},
|
|
"type": "table"
|
|
}
|
|
]
|
|
},
|
|
"layout": {
|
|
"annotationdefaults": {
|
|
"arrowcolor": "#2a3f5f",
|
|
"arrowhead": 0,
|
|
"arrowwidth": 1
|
|
},
|
|
"autotypenumbers": "strict",
|
|
"coloraxis": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"colorscale": {
|
|
"diverging": [
|
|
[
|
|
0,
|
|
"#8e0152"
|
|
],
|
|
[
|
|
0.1,
|
|
"#c51b7d"
|
|
],
|
|
[
|
|
0.2,
|
|
"#de77ae"
|
|
],
|
|
[
|
|
0.3,
|
|
"#f1b6da"
|
|
],
|
|
[
|
|
0.4,
|
|
"#fde0ef"
|
|
],
|
|
[
|
|
0.5,
|
|
"#f7f7f7"
|
|
],
|
|
[
|
|
0.6,
|
|
"#e6f5d0"
|
|
],
|
|
[
|
|
0.7,
|
|
"#b8e186"
|
|
],
|
|
[
|
|
0.8,
|
|
"#7fbc41"
|
|
],
|
|
[
|
|
0.9,
|
|
"#4d9221"
|
|
],
|
|
[
|
|
1,
|
|
"#276419"
|
|
]
|
|
],
|
|
"sequential": [
|
|
[
|
|
0,
|
|
"#0d0887"
|
|
],
|
|
[
|
|
0.1111111111111111,
|
|
"#46039f"
|
|
],
|
|
[
|
|
0.2222222222222222,
|
|
"#7201a8"
|
|
],
|
|
[
|
|
0.3333333333333333,
|
|
"#9c179e"
|
|
],
|
|
[
|
|
0.4444444444444444,
|
|
"#bd3786"
|
|
],
|
|
[
|
|
0.5555555555555556,
|
|
"#d8576b"
|
|
],
|
|
[
|
|
0.6666666666666666,
|
|
"#ed7953"
|
|
],
|
|
[
|
|
0.7777777777777778,
|
|
"#fb9f3a"
|
|
],
|
|
[
|
|
0.8888888888888888,
|
|
"#fdca26"
|
|
],
|
|
[
|
|
1,
|
|
"#f0f921"
|
|
]
|
|
],
|
|
"sequentialminus": [
|
|
[
|
|
0,
|
|
"#0d0887"
|
|
],
|
|
[
|
|
0.1111111111111111,
|
|
"#46039f"
|
|
],
|
|
[
|
|
0.2222222222222222,
|
|
"#7201a8"
|
|
],
|
|
[
|
|
0.3333333333333333,
|
|
"#9c179e"
|
|
],
|
|
[
|
|
0.4444444444444444,
|
|
"#bd3786"
|
|
],
|
|
[
|
|
0.5555555555555556,
|
|
"#d8576b"
|
|
],
|
|
[
|
|
0.6666666666666666,
|
|
"#ed7953"
|
|
],
|
|
[
|
|
0.7777777777777778,
|
|
"#fb9f3a"
|
|
],
|
|
[
|
|
0.8888888888888888,
|
|
"#fdca26"
|
|
],
|
|
[
|
|
1,
|
|
"#f0f921"
|
|
]
|
|
]
|
|
},
|
|
"colorway": [
|
|
"#636efa",
|
|
"#EF553B",
|
|
"#00cc96",
|
|
"#ab63fa",
|
|
"#FFA15A",
|
|
"#19d3f3",
|
|
"#FF6692",
|
|
"#B6E880",
|
|
"#FF97FF",
|
|
"#FECB52"
|
|
],
|
|
"font": {
|
|
"color": "#2a3f5f"
|
|
},
|
|
"geo": {
|
|
"bgcolor": "white",
|
|
"lakecolor": "white",
|
|
"landcolor": "#E5ECF6",
|
|
"showlakes": true,
|
|
"showland": true,
|
|
"subunitcolor": "white"
|
|
},
|
|
"hoverlabel": {
|
|
"align": "left"
|
|
},
|
|
"hovermode": "closest",
|
|
"mapbox": {
|
|
"style": "light"
|
|
},
|
|
"paper_bgcolor": "white",
|
|
"plot_bgcolor": "#E5ECF6",
|
|
"polar": {
|
|
"angularaxis": {
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"ticks": ""
|
|
},
|
|
"bgcolor": "#E5ECF6",
|
|
"radialaxis": {
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"scene": {
|
|
"xaxis": {
|
|
"backgroundcolor": "#E5ECF6",
|
|
"gridcolor": "white",
|
|
"gridwidth": 2,
|
|
"linecolor": "white",
|
|
"showbackground": true,
|
|
"ticks": "",
|
|
"zerolinecolor": "white"
|
|
},
|
|
"yaxis": {
|
|
"backgroundcolor": "#E5ECF6",
|
|
"gridcolor": "white",
|
|
"gridwidth": 2,
|
|
"linecolor": "white",
|
|
"showbackground": true,
|
|
"ticks": "",
|
|
"zerolinecolor": "white"
|
|
},
|
|
"zaxis": {
|
|
"backgroundcolor": "#E5ECF6",
|
|
"gridcolor": "white",
|
|
"gridwidth": 2,
|
|
"linecolor": "white",
|
|
"showbackground": true,
|
|
"ticks": "",
|
|
"zerolinecolor": "white"
|
|
}
|
|
},
|
|
"shapedefaults": {
|
|
"line": {
|
|
"color": "#2a3f5f"
|
|
}
|
|
},
|
|
"ternary": {
|
|
"aaxis": {
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"ticks": ""
|
|
},
|
|
"baxis": {
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"ticks": ""
|
|
},
|
|
"bgcolor": "#E5ECF6",
|
|
"caxis": {
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"title": {
|
|
"x": 0.05
|
|
},
|
|
"xaxis": {
|
|
"automargin": true,
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"ticks": "",
|
|
"title": {
|
|
"standoff": 15
|
|
},
|
|
"zerolinecolor": "white",
|
|
"zerolinewidth": 2
|
|
},
|
|
"yaxis": {
|
|
"automargin": true,
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"ticks": "",
|
|
"title": {
|
|
"standoff": 15
|
|
},
|
|
"zerolinecolor": "white",
|
|
"zerolinewidth": 2
|
|
}
|
|
}
|
|
},
|
|
"title": {
|
|
"text": "Top 15 resoluciones por volumen"
|
|
},
|
|
"xaxis": {
|
|
"anchor": "y",
|
|
"domain": [
|
|
0,
|
|
1
|
|
],
|
|
"title": {
|
|
"text": "Llamadas"
|
|
}
|
|
},
|
|
"yaxis": {
|
|
"anchor": "x",
|
|
"categoryorder": "total ascending",
|
|
"domain": [
|
|
0,
|
|
1
|
|
],
|
|
"title": {
|
|
"text": "Resolucion"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
},
|
|
"metadata": {},
|
|
"output_type": "display_data",
|
|
"transient": {}
|
|
}
|
|
],
|
|
"source": [
|
|
"fig = px.bar(\n",
|
|
" df_resolutions,\n",
|
|
" x='calls', y='resolution',\n",
|
|
" orientation='h',\n",
|
|
" title='Top 15 resoluciones por volumen',\n",
|
|
" labels={'calls': 'Llamadas', 'resolution': 'Resolucion'},\n",
|
|
" text='calls'\n",
|
|
")\n",
|
|
"fig.update_layout(yaxis={'categoryorder': 'total ascending'}, height=500)\n",
|
|
"fig.update_traces(texttemplate='%{text:,.0f}', textposition='outside')\n",
|
|
"fig.show()"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 8,
|
|
"id": "a3b4c5d6",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"data": {
|
|
"application/vnd.plotly.v1+json": {
|
|
"config": {
|
|
"plotlyServerURL": "https://plot.ly"
|
|
},
|
|
"data": [
|
|
{
|
|
"coloraxis": "coloraxis",
|
|
"hovertemplate": "Resolucion: %{x}<br>Campana: %{y}<br>Llamadas: %{z}<extra></extra>",
|
|
"name": "0",
|
|
"type": "heatmap",
|
|
"x": [
|
|
"Abandonada",
|
|
"CERRAR LEAD",
|
|
"Comunican",
|
|
"Contestador Automático",
|
|
"La llam.no puede alcanzar dest",
|
|
"Llamada No Atendida",
|
|
"No Contestan",
|
|
"Otras causas"
|
|
],
|
|
"xaxis": "x",
|
|
"y": [
|
|
"Aurgi",
|
|
"Aurgi Emisión",
|
|
"Aurgi Neumáticos",
|
|
"Aurgi Perdidas",
|
|
"Aurgi Presupuestos Emision",
|
|
"MotorTown Emisión",
|
|
"MotorTown Perdidas",
|
|
"Motortown Presupuestos Emisión"
|
|
],
|
|
"yaxis": "y",
|
|
"z": {
|
|
"bdata": "AAAAANAcEkEAAAAAIOAKQQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAADAV+5AAAAAAAAAAAAAAAAAvqM2QQAAAADgsvhAAAAAAIRYF0EAAAAAI0VVQQAAAADKtz9BAAAAAK5KMEEAAAAAAAAAAAAAAACkuilBAAAAAOC4KUEAAAAAaGwdQQAAAADAVRdBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAHCGF0EAAAAAAAAAAAAAAACAbddAAAAAAECy1EAAAAAAgBjxQAAAAABQUg1BAAAAANAB+0AAAAAAwGvcQAAAAAAAAAAAAAAAAMCU+0AAAAAAgPvaQAAAAABAJOtAAAAAAFAhBkEAAAAASf04QQAAAABWASVBAAAAAAi1EkEAAAAAAAAAAAAAAACg7RRBAAAAAPDwDkEAAAAAwDzWQAAAAADgU/VAAAAAALxfLEEAAAAAiE0eQQAAAABQnRBBAAAAAAAAAAAAAAAAYDYLQQAAAACAzvxAAAAAAIDL20AAAAAAALDpQAAAAAAYRgZBAAAAAABh/EAAAAAAAJncQAAAAAAAAAAAAAAAANCY9kAAAAAAAOnaQAAAAACAtdFAAAAAAMB/7EAAAAAAUGIdQQAAAAAIBRBBAAAAAIB39kAAAAAAAAAAAAAAAAAAgP9AAAAAACAp90A=",
|
|
"dtype": "f8",
|
|
"shape": "8, 8"
|
|
}
|
|
}
|
|
],
|
|
"layout": {
|
|
"coloraxis": {
|
|
"colorbar": {
|
|
"title": {
|
|
"text": "Llamadas"
|
|
}
|
|
},
|
|
"colorscale": [
|
|
[
|
|
0,
|
|
"rgb(247,251,255)"
|
|
],
|
|
[
|
|
0.125,
|
|
"rgb(222,235,247)"
|
|
],
|
|
[
|
|
0.25,
|
|
"rgb(198,219,239)"
|
|
],
|
|
[
|
|
0.375,
|
|
"rgb(158,202,225)"
|
|
],
|
|
[
|
|
0.5,
|
|
"rgb(107,174,214)"
|
|
],
|
|
[
|
|
0.625,
|
|
"rgb(66,146,198)"
|
|
],
|
|
[
|
|
0.75,
|
|
"rgb(33,113,181)"
|
|
],
|
|
[
|
|
0.875,
|
|
"rgb(8,81,156)"
|
|
],
|
|
[
|
|
1,
|
|
"rgb(8,48,107)"
|
|
]
|
|
]
|
|
},
|
|
"height": 500,
|
|
"template": {
|
|
"data": {
|
|
"bar": [
|
|
{
|
|
"error_x": {
|
|
"color": "#2a3f5f"
|
|
},
|
|
"error_y": {
|
|
"color": "#2a3f5f"
|
|
},
|
|
"marker": {
|
|
"line": {
|
|
"color": "#E5ECF6",
|
|
"width": 0.5
|
|
},
|
|
"pattern": {
|
|
"fillmode": "overlay",
|
|
"size": 10,
|
|
"solidity": 0.2
|
|
}
|
|
},
|
|
"type": "bar"
|
|
}
|
|
],
|
|
"barpolar": [
|
|
{
|
|
"marker": {
|
|
"line": {
|
|
"color": "#E5ECF6",
|
|
"width": 0.5
|
|
},
|
|
"pattern": {
|
|
"fillmode": "overlay",
|
|
"size": 10,
|
|
"solidity": 0.2
|
|
}
|
|
},
|
|
"type": "barpolar"
|
|
}
|
|
],
|
|
"carpet": [
|
|
{
|
|
"aaxis": {
|
|
"endlinecolor": "#2a3f5f",
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"minorgridcolor": "white",
|
|
"startlinecolor": "#2a3f5f"
|
|
},
|
|
"baxis": {
|
|
"endlinecolor": "#2a3f5f",
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"minorgridcolor": "white",
|
|
"startlinecolor": "#2a3f5f"
|
|
},
|
|
"type": "carpet"
|
|
}
|
|
],
|
|
"choropleth": [
|
|
{
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
},
|
|
"type": "choropleth"
|
|
}
|
|
],
|
|
"contour": [
|
|
{
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
},
|
|
"colorscale": [
|
|
[
|
|
0,
|
|
"#0d0887"
|
|
],
|
|
[
|
|
0.1111111111111111,
|
|
"#46039f"
|
|
],
|
|
[
|
|
0.2222222222222222,
|
|
"#7201a8"
|
|
],
|
|
[
|
|
0.3333333333333333,
|
|
"#9c179e"
|
|
],
|
|
[
|
|
0.4444444444444444,
|
|
"#bd3786"
|
|
],
|
|
[
|
|
0.5555555555555556,
|
|
"#d8576b"
|
|
],
|
|
[
|
|
0.6666666666666666,
|
|
"#ed7953"
|
|
],
|
|
[
|
|
0.7777777777777778,
|
|
"#fb9f3a"
|
|
],
|
|
[
|
|
0.8888888888888888,
|
|
"#fdca26"
|
|
],
|
|
[
|
|
1,
|
|
"#f0f921"
|
|
]
|
|
],
|
|
"type": "contour"
|
|
}
|
|
],
|
|
"contourcarpet": [
|
|
{
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
},
|
|
"type": "contourcarpet"
|
|
}
|
|
],
|
|
"heatmap": [
|
|
{
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
},
|
|
"colorscale": [
|
|
[
|
|
0,
|
|
"#0d0887"
|
|
],
|
|
[
|
|
0.1111111111111111,
|
|
"#46039f"
|
|
],
|
|
[
|
|
0.2222222222222222,
|
|
"#7201a8"
|
|
],
|
|
[
|
|
0.3333333333333333,
|
|
"#9c179e"
|
|
],
|
|
[
|
|
0.4444444444444444,
|
|
"#bd3786"
|
|
],
|
|
[
|
|
0.5555555555555556,
|
|
"#d8576b"
|
|
],
|
|
[
|
|
0.6666666666666666,
|
|
"#ed7953"
|
|
],
|
|
[
|
|
0.7777777777777778,
|
|
"#fb9f3a"
|
|
],
|
|
[
|
|
0.8888888888888888,
|
|
"#fdca26"
|
|
],
|
|
[
|
|
1,
|
|
"#f0f921"
|
|
]
|
|
],
|
|
"type": "heatmap"
|
|
}
|
|
],
|
|
"histogram": [
|
|
{
|
|
"marker": {
|
|
"pattern": {
|
|
"fillmode": "overlay",
|
|
"size": 10,
|
|
"solidity": 0.2
|
|
}
|
|
},
|
|
"type": "histogram"
|
|
}
|
|
],
|
|
"histogram2d": [
|
|
{
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
},
|
|
"colorscale": [
|
|
[
|
|
0,
|
|
"#0d0887"
|
|
],
|
|
[
|
|
0.1111111111111111,
|
|
"#46039f"
|
|
],
|
|
[
|
|
0.2222222222222222,
|
|
"#7201a8"
|
|
],
|
|
[
|
|
0.3333333333333333,
|
|
"#9c179e"
|
|
],
|
|
[
|
|
0.4444444444444444,
|
|
"#bd3786"
|
|
],
|
|
[
|
|
0.5555555555555556,
|
|
"#d8576b"
|
|
],
|
|
[
|
|
0.6666666666666666,
|
|
"#ed7953"
|
|
],
|
|
[
|
|
0.7777777777777778,
|
|
"#fb9f3a"
|
|
],
|
|
[
|
|
0.8888888888888888,
|
|
"#fdca26"
|
|
],
|
|
[
|
|
1,
|
|
"#f0f921"
|
|
]
|
|
],
|
|
"type": "histogram2d"
|
|
}
|
|
],
|
|
"histogram2dcontour": [
|
|
{
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
},
|
|
"colorscale": [
|
|
[
|
|
0,
|
|
"#0d0887"
|
|
],
|
|
[
|
|
0.1111111111111111,
|
|
"#46039f"
|
|
],
|
|
[
|
|
0.2222222222222222,
|
|
"#7201a8"
|
|
],
|
|
[
|
|
0.3333333333333333,
|
|
"#9c179e"
|
|
],
|
|
[
|
|
0.4444444444444444,
|
|
"#bd3786"
|
|
],
|
|
[
|
|
0.5555555555555556,
|
|
"#d8576b"
|
|
],
|
|
[
|
|
0.6666666666666666,
|
|
"#ed7953"
|
|
],
|
|
[
|
|
0.7777777777777778,
|
|
"#fb9f3a"
|
|
],
|
|
[
|
|
0.8888888888888888,
|
|
"#fdca26"
|
|
],
|
|
[
|
|
1,
|
|
"#f0f921"
|
|
]
|
|
],
|
|
"type": "histogram2dcontour"
|
|
}
|
|
],
|
|
"mesh3d": [
|
|
{
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
},
|
|
"type": "mesh3d"
|
|
}
|
|
],
|
|
"parcoords": [
|
|
{
|
|
"line": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "parcoords"
|
|
}
|
|
],
|
|
"pie": [
|
|
{
|
|
"automargin": true,
|
|
"type": "pie"
|
|
}
|
|
],
|
|
"scatter": [
|
|
{
|
|
"fillpattern": {
|
|
"fillmode": "overlay",
|
|
"size": 10,
|
|
"solidity": 0.2
|
|
},
|
|
"type": "scatter"
|
|
}
|
|
],
|
|
"scatter3d": [
|
|
{
|
|
"line": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scatter3d"
|
|
}
|
|
],
|
|
"scattercarpet": [
|
|
{
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scattercarpet"
|
|
}
|
|
],
|
|
"scattergeo": [
|
|
{
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scattergeo"
|
|
}
|
|
],
|
|
"scattergl": [
|
|
{
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scattergl"
|
|
}
|
|
],
|
|
"scattermap": [
|
|
{
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scattermap"
|
|
}
|
|
],
|
|
"scattermapbox": [
|
|
{
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scattermapbox"
|
|
}
|
|
],
|
|
"scatterpolar": [
|
|
{
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scatterpolar"
|
|
}
|
|
],
|
|
"scatterpolargl": [
|
|
{
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scatterpolargl"
|
|
}
|
|
],
|
|
"scatterternary": [
|
|
{
|
|
"marker": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"type": "scatterternary"
|
|
}
|
|
],
|
|
"surface": [
|
|
{
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
},
|
|
"colorscale": [
|
|
[
|
|
0,
|
|
"#0d0887"
|
|
],
|
|
[
|
|
0.1111111111111111,
|
|
"#46039f"
|
|
],
|
|
[
|
|
0.2222222222222222,
|
|
"#7201a8"
|
|
],
|
|
[
|
|
0.3333333333333333,
|
|
"#9c179e"
|
|
],
|
|
[
|
|
0.4444444444444444,
|
|
"#bd3786"
|
|
],
|
|
[
|
|
0.5555555555555556,
|
|
"#d8576b"
|
|
],
|
|
[
|
|
0.6666666666666666,
|
|
"#ed7953"
|
|
],
|
|
[
|
|
0.7777777777777778,
|
|
"#fb9f3a"
|
|
],
|
|
[
|
|
0.8888888888888888,
|
|
"#fdca26"
|
|
],
|
|
[
|
|
1,
|
|
"#f0f921"
|
|
]
|
|
],
|
|
"type": "surface"
|
|
}
|
|
],
|
|
"table": [
|
|
{
|
|
"cells": {
|
|
"fill": {
|
|
"color": "#EBF0F8"
|
|
},
|
|
"line": {
|
|
"color": "white"
|
|
}
|
|
},
|
|
"header": {
|
|
"fill": {
|
|
"color": "#C8D4E3"
|
|
},
|
|
"line": {
|
|
"color": "white"
|
|
}
|
|
},
|
|
"type": "table"
|
|
}
|
|
]
|
|
},
|
|
"layout": {
|
|
"annotationdefaults": {
|
|
"arrowcolor": "#2a3f5f",
|
|
"arrowhead": 0,
|
|
"arrowwidth": 1
|
|
},
|
|
"autotypenumbers": "strict",
|
|
"coloraxis": {
|
|
"colorbar": {
|
|
"outlinewidth": 0,
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"colorscale": {
|
|
"diverging": [
|
|
[
|
|
0,
|
|
"#8e0152"
|
|
],
|
|
[
|
|
0.1,
|
|
"#c51b7d"
|
|
],
|
|
[
|
|
0.2,
|
|
"#de77ae"
|
|
],
|
|
[
|
|
0.3,
|
|
"#f1b6da"
|
|
],
|
|
[
|
|
0.4,
|
|
"#fde0ef"
|
|
],
|
|
[
|
|
0.5,
|
|
"#f7f7f7"
|
|
],
|
|
[
|
|
0.6,
|
|
"#e6f5d0"
|
|
],
|
|
[
|
|
0.7,
|
|
"#b8e186"
|
|
],
|
|
[
|
|
0.8,
|
|
"#7fbc41"
|
|
],
|
|
[
|
|
0.9,
|
|
"#4d9221"
|
|
],
|
|
[
|
|
1,
|
|
"#276419"
|
|
]
|
|
],
|
|
"sequential": [
|
|
[
|
|
0,
|
|
"#0d0887"
|
|
],
|
|
[
|
|
0.1111111111111111,
|
|
"#46039f"
|
|
],
|
|
[
|
|
0.2222222222222222,
|
|
"#7201a8"
|
|
],
|
|
[
|
|
0.3333333333333333,
|
|
"#9c179e"
|
|
],
|
|
[
|
|
0.4444444444444444,
|
|
"#bd3786"
|
|
],
|
|
[
|
|
0.5555555555555556,
|
|
"#d8576b"
|
|
],
|
|
[
|
|
0.6666666666666666,
|
|
"#ed7953"
|
|
],
|
|
[
|
|
0.7777777777777778,
|
|
"#fb9f3a"
|
|
],
|
|
[
|
|
0.8888888888888888,
|
|
"#fdca26"
|
|
],
|
|
[
|
|
1,
|
|
"#f0f921"
|
|
]
|
|
],
|
|
"sequentialminus": [
|
|
[
|
|
0,
|
|
"#0d0887"
|
|
],
|
|
[
|
|
0.1111111111111111,
|
|
"#46039f"
|
|
],
|
|
[
|
|
0.2222222222222222,
|
|
"#7201a8"
|
|
],
|
|
[
|
|
0.3333333333333333,
|
|
"#9c179e"
|
|
],
|
|
[
|
|
0.4444444444444444,
|
|
"#bd3786"
|
|
],
|
|
[
|
|
0.5555555555555556,
|
|
"#d8576b"
|
|
],
|
|
[
|
|
0.6666666666666666,
|
|
"#ed7953"
|
|
],
|
|
[
|
|
0.7777777777777778,
|
|
"#fb9f3a"
|
|
],
|
|
[
|
|
0.8888888888888888,
|
|
"#fdca26"
|
|
],
|
|
[
|
|
1,
|
|
"#f0f921"
|
|
]
|
|
]
|
|
},
|
|
"colorway": [
|
|
"#636efa",
|
|
"#EF553B",
|
|
"#00cc96",
|
|
"#ab63fa",
|
|
"#FFA15A",
|
|
"#19d3f3",
|
|
"#FF6692",
|
|
"#B6E880",
|
|
"#FF97FF",
|
|
"#FECB52"
|
|
],
|
|
"font": {
|
|
"color": "#2a3f5f"
|
|
},
|
|
"geo": {
|
|
"bgcolor": "white",
|
|
"lakecolor": "white",
|
|
"landcolor": "#E5ECF6",
|
|
"showlakes": true,
|
|
"showland": true,
|
|
"subunitcolor": "white"
|
|
},
|
|
"hoverlabel": {
|
|
"align": "left"
|
|
},
|
|
"hovermode": "closest",
|
|
"mapbox": {
|
|
"style": "light"
|
|
},
|
|
"paper_bgcolor": "white",
|
|
"plot_bgcolor": "#E5ECF6",
|
|
"polar": {
|
|
"angularaxis": {
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"ticks": ""
|
|
},
|
|
"bgcolor": "#E5ECF6",
|
|
"radialaxis": {
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"scene": {
|
|
"xaxis": {
|
|
"backgroundcolor": "#E5ECF6",
|
|
"gridcolor": "white",
|
|
"gridwidth": 2,
|
|
"linecolor": "white",
|
|
"showbackground": true,
|
|
"ticks": "",
|
|
"zerolinecolor": "white"
|
|
},
|
|
"yaxis": {
|
|
"backgroundcolor": "#E5ECF6",
|
|
"gridcolor": "white",
|
|
"gridwidth": 2,
|
|
"linecolor": "white",
|
|
"showbackground": true,
|
|
"ticks": "",
|
|
"zerolinecolor": "white"
|
|
},
|
|
"zaxis": {
|
|
"backgroundcolor": "#E5ECF6",
|
|
"gridcolor": "white",
|
|
"gridwidth": 2,
|
|
"linecolor": "white",
|
|
"showbackground": true,
|
|
"ticks": "",
|
|
"zerolinecolor": "white"
|
|
}
|
|
},
|
|
"shapedefaults": {
|
|
"line": {
|
|
"color": "#2a3f5f"
|
|
}
|
|
},
|
|
"ternary": {
|
|
"aaxis": {
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"ticks": ""
|
|
},
|
|
"baxis": {
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"ticks": ""
|
|
},
|
|
"bgcolor": "#E5ECF6",
|
|
"caxis": {
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"ticks": ""
|
|
}
|
|
},
|
|
"title": {
|
|
"x": 0.05
|
|
},
|
|
"xaxis": {
|
|
"automargin": true,
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"ticks": "",
|
|
"title": {
|
|
"standoff": 15
|
|
},
|
|
"zerolinecolor": "white",
|
|
"zerolinewidth": 2
|
|
},
|
|
"yaxis": {
|
|
"automargin": true,
|
|
"gridcolor": "white",
|
|
"linecolor": "white",
|
|
"ticks": "",
|
|
"title": {
|
|
"standoff": 15
|
|
},
|
|
"zerolinecolor": "white",
|
|
"zerolinewidth": 2
|
|
}
|
|
}
|
|
},
|
|
"title": {
|
|
"text": "Resoluciones por campana (top 8 x top 8)"
|
|
},
|
|
"width": 900,
|
|
"xaxis": {
|
|
"anchor": "y",
|
|
"domain": [
|
|
0,
|
|
1
|
|
],
|
|
"title": {
|
|
"text": "Resolucion"
|
|
}
|
|
},
|
|
"yaxis": {
|
|
"anchor": "x",
|
|
"autorange": "reversed",
|
|
"domain": [
|
|
0,
|
|
1
|
|
],
|
|
"title": {
|
|
"text": "Campana"
|
|
}
|
|
}
|
|
}
|
|
}
|
|
},
|
|
"metadata": {},
|
|
"output_type": "display_data",
|
|
"transient": {}
|
|
}
|
|
],
|
|
"source": [
|
|
"# Resoluciones por campana (top 8 campanas x top 8 resoluciones)\n",
|
|
"df_camp_res = query_to_df(f\"\"\"\n",
|
|
"WITH top_campaigns AS (\n",
|
|
" SELECT campaign_name\n",
|
|
" FROM {TABLE}\n",
|
|
" WHERE campaign_name IS NOT NULL\n",
|
|
" GROUP BY campaign_name\n",
|
|
" ORDER BY COUNT(*) DESC\n",
|
|
" LIMIT 8\n",
|
|
"),\n",
|
|
"top_resolutions AS (\n",
|
|
" SELECT description\n",
|
|
" FROM {TABLE}\n",
|
|
" WHERE description IS NOT NULL\n",
|
|
" GROUP BY description\n",
|
|
" ORDER BY COUNT(*) DESC\n",
|
|
" LIMIT 8\n",
|
|
")\n",
|
|
"SELECT\n",
|
|
" t.campaign_name,\n",
|
|
" t.description AS resolution,\n",
|
|
" COUNT(*) AS calls\n",
|
|
"FROM {TABLE} t\n",
|
|
"JOIN top_campaigns tc ON t.campaign_name = tc.campaign_name\n",
|
|
"JOIN top_resolutions tr ON t.description = tr.description\n",
|
|
"GROUP BY t.campaign_name, t.description\n",
|
|
"ORDER BY t.campaign_name, calls DESC\n",
|
|
"\"\"\")\n",
|
|
"\n",
|
|
"df_camp_res['calls'] = df_camp_res['calls'].astype(int)\n",
|
|
"\n",
|
|
"pivot = df_camp_res.pivot(index='campaign_name', columns='resolution', values='calls').fillna(0)\n",
|
|
"\n",
|
|
"fig = px.imshow(\n",
|
|
" pivot,\n",
|
|
" title='Resoluciones por campana (top 8 x top 8)',\n",
|
|
" labels=dict(x='Resolucion', y='Campana', color='Llamadas'),\n",
|
|
" aspect='auto',\n",
|
|
" color_continuous_scale='Blues'\n",
|
|
")\n",
|
|
"fig.update_layout(height=500, width=900)\n",
|
|
"fig.show()"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "b3c4d5e6",
|
|
"metadata": {},
|
|
"source": [
|
|
"## 5. Evolucion temporal"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "c3d4e5f6",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"# Llamadas por mes\n",
|
|
"df_monthly = query_to_df(f\"\"\"\n",
|
|
"SELECT\n",
|
|
" FORMAT_TIMESTAMP('%Y-%m', date_time) AS month,\n",
|
|
" COUNT(*) AS calls\n",
|
|
"FROM {TABLE}\n",
|
|
"WHERE date_time IS NOT NULL\n",
|
|
"GROUP BY month\n",
|
|
"ORDER BY month\n",
|
|
"\"\"\")\n",
|
|
"\n",
|
|
"df_monthly['calls'] = df_monthly['calls'].astype(int)\n",
|
|
"\n",
|
|
"fig = px.line(\n",
|
|
" df_monthly, x='month', y='calls',\n",
|
|
" title='Llamadas por mes',\n",
|
|
" labels={'calls': 'Llamadas', 'month': 'Mes'},\n",
|
|
" markers=True\n",
|
|
")\n",
|
|
"fig.update_layout(xaxis_tickangle=-45, height=400)\n",
|
|
"fig.show()"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "d3e4f5a6",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"# Llamadas por dia de la semana\n",
|
|
"df_dow = query_to_df(f\"\"\"\n",
|
|
"SELECT\n",
|
|
" EXTRACT(DAYOFWEEK FROM date_time) AS dow_num,\n",
|
|
" COUNT(*) AS calls\n",
|
|
"FROM {TABLE}\n",
|
|
"WHERE date_time IS NOT NULL\n",
|
|
"GROUP BY dow_num\n",
|
|
"ORDER BY dow_num\n",
|
|
"\"\"\")\n",
|
|
"\n",
|
|
"df_dow['calls'] = df_dow['calls'].astype(int)\n",
|
|
"df_dow['dow_num'] = df_dow['dow_num'].astype(int)\n",
|
|
"\n",
|
|
"dow_labels = {1: 'Dom', 2: 'Lun', 3: 'Mar', 4: 'Mie', 5: 'Jue', 6: 'Vie', 7: 'Sab'}\n",
|
|
"df_dow['day'] = df_dow['dow_num'].map(dow_labels)\n",
|
|
"\n",
|
|
"fig = px.bar(\n",
|
|
" df_dow, x='day', y='calls',\n",
|
|
" title='Llamadas por dia de la semana',\n",
|
|
" labels={'calls': 'Llamadas', 'day': 'Dia'},\n",
|
|
" text='calls'\n",
|
|
")\n",
|
|
"fig.update_traces(texttemplate='%{text:,.0f}', textposition='outside')\n",
|
|
"fig.update_layout(height=400)\n",
|
|
"fig.show()"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "e3f4a5b6",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"# Llamadas por hora del dia\n",
|
|
"df_hour = query_to_df(f\"\"\"\n",
|
|
"SELECT\n",
|
|
" EXTRACT(HOUR FROM date_time) AS hour,\n",
|
|
" COUNT(*) AS calls\n",
|
|
"FROM {TABLE}\n",
|
|
"WHERE date_time IS NOT NULL\n",
|
|
"GROUP BY hour\n",
|
|
"ORDER BY hour\n",
|
|
"\"\"\")\n",
|
|
"\n",
|
|
"df_hour['calls'] = df_hour['calls'].astype(int)\n",
|
|
"df_hour['hour'] = df_hour['hour'].astype(int)\n",
|
|
"\n",
|
|
"fig = px.bar(\n",
|
|
" df_hour, x='hour', y='calls',\n",
|
|
" title='Llamadas por hora del dia',\n",
|
|
" labels={'calls': 'Llamadas', 'hour': 'Hora'},\n",
|
|
" text='calls'\n",
|
|
")\n",
|
|
"fig.update_traces(texttemplate='%{text:,.0f}', textposition='outside')\n",
|
|
"fig.update_layout(height=400, xaxis=dict(dtick=1))\n",
|
|
"fig.show()"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "f3a4b5c6",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"# Heatmap: hora x dia de semana\n",
|
|
"df_hour_dow = query_to_df(f\"\"\"\n",
|
|
"SELECT\n",
|
|
" EXTRACT(DAYOFWEEK FROM date_time) AS dow_num,\n",
|
|
" EXTRACT(HOUR FROM date_time) AS hour,\n",
|
|
" COUNT(*) AS calls\n",
|
|
"FROM {TABLE}\n",
|
|
"WHERE date_time IS NOT NULL\n",
|
|
"GROUP BY dow_num, hour\n",
|
|
"ORDER BY dow_num, hour\n",
|
|
"\"\"\")\n",
|
|
"\n",
|
|
"df_hour_dow['calls'] = df_hour_dow['calls'].astype(int)\n",
|
|
"df_hour_dow['dow_num'] = df_hour_dow['dow_num'].astype(int)\n",
|
|
"df_hour_dow['hour'] = df_hour_dow['hour'].astype(int)\n",
|
|
"df_hour_dow['day'] = df_hour_dow['dow_num'].map(dow_labels)\n",
|
|
"\n",
|
|
"pivot_hd = df_hour_dow.pivot(index='day', columns='hour', values='calls').fillna(0)\n",
|
|
"# Reorder days\n",
|
|
"day_order = ['Lun', 'Mar', 'Mie', 'Jue', 'Vie', 'Sab', 'Dom']\n",
|
|
"pivot_hd = pivot_hd.reindex([d for d in day_order if d in pivot_hd.index])\n",
|
|
"\n",
|
|
"fig = px.imshow(\n",
|
|
" pivot_hd,\n",
|
|
" title='Heatmap: llamadas por hora y dia de semana',\n",
|
|
" labels=dict(x='Hora', y='Dia', color='Llamadas'),\n",
|
|
" aspect='auto',\n",
|
|
" color_continuous_scale='YlOrRd'\n",
|
|
")\n",
|
|
"fig.update_layout(height=350, width=900)\n",
|
|
"fig.show()"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "a4b5c6d7",
|
|
"metadata": {},
|
|
"source": [
|
|
"## 6. Duracion de conversacion (`t_convers`)"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "b4c5d6e7",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"# Distribucion general: con vs sin conversacion\n",
|
|
"df_convers_split = query_to_df(f\"\"\"\n",
|
|
"SELECT\n",
|
|
" CASE\n",
|
|
" WHEN t_convers IS NULL THEN 'NULL'\n",
|
|
" WHEN t_convers = '00:00:00' THEN 'Sin conversacion'\n",
|
|
" ELSE 'Con conversacion'\n",
|
|
" END AS tipo,\n",
|
|
" COUNT(*) AS calls\n",
|
|
"FROM {TABLE}\n",
|
|
"GROUP BY tipo\n",
|
|
"ORDER BY calls DESC\n",
|
|
"\"\"\")\n",
|
|
"\n",
|
|
"df_convers_split['calls'] = df_convers_split['calls'].astype(int)\n",
|
|
"df_convers_split"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "c4d5e6f7",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"fig = px.pie(\n",
|
|
" df_convers_split, values='calls', names='tipo',\n",
|
|
" title='Proporcion de llamadas con/sin conversacion',\n",
|
|
" hole=0.4\n",
|
|
")\n",
|
|
"fig.show()"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "d4e5f6a7",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"# Distribucion de duraciones para llamadas CON conversacion\n",
|
|
"df_duration_buckets = query_to_df(f\"\"\"\n",
|
|
"SELECT\n",
|
|
" CASE\n",
|
|
" WHEN TIME_DIFF(CAST(t_convers AS TIME), TIME '00:00:00', SECOND) <= 30 THEN '00-30s'\n",
|
|
" WHEN TIME_DIFF(CAST(t_convers AS TIME), TIME '00:00:00', SECOND) <= 60 THEN '31-60s'\n",
|
|
" WHEN TIME_DIFF(CAST(t_convers AS TIME), TIME '00:00:00', SECOND) <= 120 THEN '1-2min'\n",
|
|
" WHEN TIME_DIFF(CAST(t_convers AS TIME), TIME '00:00:00', SECOND) <= 300 THEN '2-5min'\n",
|
|
" WHEN TIME_DIFF(CAST(t_convers AS TIME), TIME '00:00:00', SECOND) <= 600 THEN '5-10min'\n",
|
|
" WHEN TIME_DIFF(CAST(t_convers AS TIME), TIME '00:00:00', SECOND) <= 1800 THEN '10-30min'\n",
|
|
" ELSE '30min+'\n",
|
|
" END AS duration_bucket,\n",
|
|
" COUNT(*) AS calls\n",
|
|
"FROM {TABLE}\n",
|
|
"WHERE t_convers IS NOT NULL\n",
|
|
" AND t_convers != '00:00:00'\n",
|
|
"GROUP BY duration_bucket\n",
|
|
"ORDER BY\n",
|
|
" CASE duration_bucket\n",
|
|
" WHEN '00-30s' THEN 1\n",
|
|
" WHEN '31-60s' THEN 2\n",
|
|
" WHEN '1-2min' THEN 3\n",
|
|
" WHEN '2-5min' THEN 4\n",
|
|
" WHEN '5-10min' THEN 5\n",
|
|
" WHEN '10-30min' THEN 6\n",
|
|
" WHEN '30min+' THEN 7\n",
|
|
" END\n",
|
|
"\"\"\")\n",
|
|
"\n",
|
|
"df_duration_buckets['calls'] = df_duration_buckets['calls'].astype(int)\n",
|
|
"df_duration_buckets"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "e4f5a6b7",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"fig = px.bar(\n",
|
|
" df_duration_buckets, x='duration_bucket', y='calls',\n",
|
|
" title='Distribucion de duracion (llamadas con conversacion)',\n",
|
|
" labels={'calls': 'Llamadas', 'duration_bucket': 'Duracion'},\n",
|
|
" text='calls'\n",
|
|
")\n",
|
|
"fig.update_traces(texttemplate='%{text:,.0f}', textposition='outside')\n",
|
|
"fig.update_layout(height=400)\n",
|
|
"fig.show()"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "f4a5b6c7",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"# Duracion media por campana (top 10 campanas)\n",
|
|
"df_dur_camp = query_to_df(f\"\"\"\n",
|
|
"WITH top_camps AS (\n",
|
|
" SELECT campaign_name\n",
|
|
" FROM {TABLE}\n",
|
|
" WHERE campaign_name IS NOT NULL\n",
|
|
" GROUP BY campaign_name\n",
|
|
" ORDER BY COUNT(*) DESC\n",
|
|
" LIMIT 10\n",
|
|
")\n",
|
|
"SELECT\n",
|
|
" t.campaign_name,\n",
|
|
" COUNT(*) AS total_calls,\n",
|
|
" COUNTIF(t_convers != '00:00:00' AND t_convers IS NOT NULL) AS connected_calls,\n",
|
|
" ROUND(AVG(\n",
|
|
" CASE WHEN t_convers != '00:00:00' AND t_convers IS NOT NULL\n",
|
|
" THEN TIME_DIFF(CAST(t_convers AS TIME), TIME '00:00:00', SECOND)\n",
|
|
" END\n",
|
|
" ), 1) AS avg_duration_secs\n",
|
|
"FROM {TABLE} t\n",
|
|
"JOIN top_camps tc ON t.campaign_name = tc.campaign_name\n",
|
|
"GROUP BY t.campaign_name\n",
|
|
"ORDER BY total_calls DESC\n",
|
|
"\"\"\")\n",
|
|
"\n",
|
|
"for col in ['total_calls', 'connected_calls']:\n",
|
|
" df_dur_camp[col] = df_dur_camp[col].astype(int)\n",
|
|
"df_dur_camp['avg_duration_secs'] = df_dur_camp['avg_duration_secs'].astype(float)\n",
|
|
"df_dur_camp['connect_rate'] = (df_dur_camp['connected_calls'] / df_dur_camp['total_calls'] * 100).round(1)\n",
|
|
"df_dur_camp"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "a5b6c7d8",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"fig = px.bar(\n",
|
|
" df_dur_camp.sort_values('avg_duration_secs', ascending=True),\n",
|
|
" x='avg_duration_secs', y='campaign_name',\n",
|
|
" orientation='h',\n",
|
|
" title='Duracion media de conversacion por campana (seg)',\n",
|
|
" labels={'avg_duration_secs': 'Segundos (media)', 'campaign_name': 'Campana'},\n",
|
|
" text='avg_duration_secs'\n",
|
|
")\n",
|
|
"fig.update_traces(texttemplate='%{text:.0f}s', textposition='outside')\n",
|
|
"fig.update_layout(height=500)\n",
|
|
"fig.show()"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "b5c6d7e8",
|
|
"metadata": {},
|
|
"source": [
|
|
"## 7. Agentes"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "c5d6e7f8",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"# Top 20 agentes por volumen\n",
|
|
"df_agents = query_to_df(f\"\"\"\n",
|
|
"SELECT\n",
|
|
" agente,\n",
|
|
" COUNT(*) AS calls,\n",
|
|
" COUNTIF(t_convers != '00:00:00' AND t_convers IS NOT NULL) AS connected,\n",
|
|
" ROUND(AVG(\n",
|
|
" CASE WHEN t_convers != '00:00:00' AND t_convers IS NOT NULL\n",
|
|
" THEN TIME_DIFF(CAST(t_convers AS TIME), TIME '00:00:00', SECOND)\n",
|
|
" END\n",
|
|
" ), 1) AS avg_dur_secs\n",
|
|
"FROM {TABLE}\n",
|
|
"WHERE agente IS NOT NULL AND agente != ''\n",
|
|
"GROUP BY agente\n",
|
|
"ORDER BY calls DESC\n",
|
|
"LIMIT 20\n",
|
|
"\"\"\")\n",
|
|
"\n",
|
|
"df_agents['calls'] = df_agents['calls'].astype(int)\n",
|
|
"df_agents['connected'] = df_agents['connected'].astype(int)\n",
|
|
"df_agents['avg_dur_secs'] = df_agents['avg_dur_secs'].astype(float)\n",
|
|
"df_agents['connect_rate'] = (df_agents['connected'] / df_agents['calls'] * 100).round(1)\n",
|
|
"df_agents"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "d5e6f7a8",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"fig = px.bar(\n",
|
|
" df_agents,\n",
|
|
" x='calls', y='agente',\n",
|
|
" orientation='h',\n",
|
|
" title='Top 20 agentes por volumen de llamadas',\n",
|
|
" labels={'calls': 'Llamadas', 'agente': 'Agente'},\n",
|
|
" text='calls'\n",
|
|
")\n",
|
|
"fig.update_layout(yaxis={'categoryorder': 'total ascending'}, height=600)\n",
|
|
"fig.update_traces(texttemplate='%{text:,.0f}', textposition='outside')\n",
|
|
"fig.show()"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "e5f6a7b8",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"# Distribucion de agentes por campana (top 5 agentes x top 5 campanas)\n",
|
|
"df_agent_camp = query_to_df(f\"\"\"\n",
|
|
"WITH top_agents AS (\n",
|
|
" SELECT agente\n",
|
|
" FROM {TABLE}\n",
|
|
" WHERE agente IS NOT NULL AND agente != ''\n",
|
|
" GROUP BY agente\n",
|
|
" ORDER BY COUNT(*) DESC\n",
|
|
" LIMIT 10\n",
|
|
"),\n",
|
|
"top_camps AS (\n",
|
|
" SELECT campaign_name\n",
|
|
" FROM {TABLE}\n",
|
|
" WHERE campaign_name IS NOT NULL\n",
|
|
" GROUP BY campaign_name\n",
|
|
" ORDER BY COUNT(*) DESC\n",
|
|
" LIMIT 8\n",
|
|
")\n",
|
|
"SELECT\n",
|
|
" t.agente,\n",
|
|
" t.campaign_name,\n",
|
|
" COUNT(*) AS calls\n",
|
|
"FROM {TABLE} t\n",
|
|
"JOIN top_agents ta ON t.agente = ta.agente\n",
|
|
"JOIN top_camps tc ON t.campaign_name = tc.campaign_name\n",
|
|
"GROUP BY t.agente, t.campaign_name\n",
|
|
"ORDER BY t.agente, calls DESC\n",
|
|
"\"\"\")\n",
|
|
"\n",
|
|
"df_agent_camp['calls'] = df_agent_camp['calls'].astype(int)\n",
|
|
"\n",
|
|
"fig = px.bar(\n",
|
|
" df_agent_camp,\n",
|
|
" x='agente', y='calls', color='campaign_name',\n",
|
|
" title='Distribucion de llamadas: top 10 agentes x top 8 campanas',\n",
|
|
" labels={'calls': 'Llamadas', 'agente': 'Agente', 'campaign_name': 'Campana'},\n",
|
|
" barmode='stack'\n",
|
|
")\n",
|
|
"fig.update_layout(height=500, xaxis_tickangle=-45)\n",
|
|
"fig.show()"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "f5a6b7c8",
|
|
"metadata": {},
|
|
"source": [
|
|
"## 8. Foco en Mutua\n",
|
|
"\n",
|
|
"Las campanas de Mutua son las que se relacionan con el proyecto Happy Robot.\n",
|
|
"Filtramos a campanas cuyo nombre contenga \"Mutua\"."
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "a6b7c8d9",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"# Campanas que contienen 'Mutua'\n",
|
|
"df_mutua_camps = query_to_df(f\"\"\"\n",
|
|
"SELECT\n",
|
|
" campaign_name,\n",
|
|
" COUNT(*) AS calls,\n",
|
|
" MIN(date_time) AS first_call,\n",
|
|
" MAX(date_time) AS last_call\n",
|
|
"FROM {TABLE}\n",
|
|
"WHERE campaign_name LIKE '%Mutua%'\n",
|
|
"GROUP BY campaign_name\n",
|
|
"ORDER BY calls DESC\n",
|
|
"\"\"\")\n",
|
|
"\n",
|
|
"df_mutua_camps['calls'] = df_mutua_camps['calls'].astype(int)\n",
|
|
"df_mutua_camps"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "b6c7d8e9",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"# Metricas generales Mutua\n",
|
|
"df_mutua_overview = query_to_df(f\"\"\"\n",
|
|
"SELECT\n",
|
|
" COUNT(*) AS total_calls,\n",
|
|
" COUNT(DISTINCT agente) AS n_agents,\n",
|
|
" COUNT(DISTINCT description) AS n_resolutions,\n",
|
|
" COUNTIF(t_convers != '00:00:00' AND t_convers IS NOT NULL) AS connected,\n",
|
|
" ROUND(AVG(\n",
|
|
" CASE WHEN t_convers != '00:00:00' AND t_convers IS NOT NULL\n",
|
|
" THEN TIME_DIFF(CAST(t_convers AS TIME), TIME '00:00:00', SECOND)\n",
|
|
" END\n",
|
|
" ), 1) AS avg_dur_secs,\n",
|
|
" MIN(date_time) AS first_call,\n",
|
|
" MAX(date_time) AS last_call\n",
|
|
"FROM {TABLE}\n",
|
|
"WHERE campaign_name LIKE '%Mutua%'\n",
|
|
"\"\"\")\n",
|
|
"\n",
|
|
"df_mutua_overview.T"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "c6d7e8f9",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"# Resoluciones en campanas Mutua\n",
|
|
"df_mutua_res = query_to_df(f\"\"\"\n",
|
|
"SELECT\n",
|
|
" description AS resolution,\n",
|
|
" COUNT(*) AS calls\n",
|
|
"FROM {TABLE}\n",
|
|
"WHERE campaign_name LIKE '%Mutua%'\n",
|
|
" AND description IS NOT NULL\n",
|
|
"GROUP BY description\n",
|
|
"ORDER BY calls DESC\n",
|
|
"LIMIT 15\n",
|
|
"\"\"\")\n",
|
|
"\n",
|
|
"df_mutua_res['calls'] = df_mutua_res['calls'].astype(int)\n",
|
|
"\n",
|
|
"fig = px.bar(\n",
|
|
" df_mutua_res,\n",
|
|
" x='calls', y='resolution',\n",
|
|
" orientation='h',\n",
|
|
" title='Resoluciones en campanas Mutua',\n",
|
|
" labels={'calls': 'Llamadas', 'resolution': 'Resolucion'},\n",
|
|
" text='calls',\n",
|
|
" color_discrete_sequence=['#e45756']\n",
|
|
")\n",
|
|
"fig.update_layout(yaxis={'categoryorder': 'total ascending'}, height=500)\n",
|
|
"fig.update_traces(texttemplate='%{text:,.0f}', textposition='outside')\n",
|
|
"fig.show()"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "d6e7f8a9",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"# Evolucion mensual Mutua\n",
|
|
"df_mutua_monthly = query_to_df(f\"\"\"\n",
|
|
"SELECT\n",
|
|
" FORMAT_TIMESTAMP('%Y-%m', date_time) AS month,\n",
|
|
" campaign_name,\n",
|
|
" COUNT(*) AS calls\n",
|
|
"FROM {TABLE}\n",
|
|
"WHERE campaign_name LIKE '%Mutua%'\n",
|
|
" AND date_time IS NOT NULL\n",
|
|
"GROUP BY month, campaign_name\n",
|
|
"ORDER BY month\n",
|
|
"\"\"\")\n",
|
|
"\n",
|
|
"df_mutua_monthly['calls'] = df_mutua_monthly['calls'].astype(int)\n",
|
|
"\n",
|
|
"fig = px.line(\n",
|
|
" df_mutua_monthly, x='month', y='calls', color='campaign_name',\n",
|
|
" title='Evolucion mensual - Campanas Mutua',\n",
|
|
" labels={'calls': 'Llamadas', 'month': 'Mes', 'campaign_name': 'Campana'},\n",
|
|
" markers=True\n",
|
|
")\n",
|
|
"fig.update_layout(xaxis_tickangle=-45, height=450)\n",
|
|
"fig.show()"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "e6f7a8b9",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"# Mutua: llamadas por hora del dia\n",
|
|
"df_mutua_hour = query_to_df(f\"\"\"\n",
|
|
"SELECT\n",
|
|
" EXTRACT(HOUR FROM date_time) AS hour,\n",
|
|
" COUNT(*) AS calls\n",
|
|
"FROM {TABLE}\n",
|
|
"WHERE campaign_name LIKE '%Mutua%'\n",
|
|
" AND date_time IS NOT NULL\n",
|
|
"GROUP BY hour\n",
|
|
"ORDER BY hour\n",
|
|
"\"\"\")\n",
|
|
"\n",
|
|
"df_mutua_hour['calls'] = df_mutua_hour['calls'].astype(int)\n",
|
|
"df_mutua_hour['hour'] = df_mutua_hour['hour'].astype(int)\n",
|
|
"\n",
|
|
"fig = px.bar(\n",
|
|
" df_mutua_hour, x='hour', y='calls',\n",
|
|
" title='Mutua: llamadas por hora del dia',\n",
|
|
" labels={'calls': 'Llamadas', 'hour': 'Hora'},\n",
|
|
" text='calls',\n",
|
|
" color_discrete_sequence=['#e45756']\n",
|
|
")\n",
|
|
"fig.update_traces(texttemplate='%{text:,.0f}', textposition='outside')\n",
|
|
"fig.update_layout(height=400, xaxis=dict(dtick=1))\n",
|
|
"fig.show()"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "f6a7b8c9",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"# Mutua: duracion de conversaciones\n",
|
|
"df_mutua_dur = query_to_df(f\"\"\"\n",
|
|
"SELECT\n",
|
|
" CASE\n",
|
|
" WHEN TIME_DIFF(CAST(t_convers AS TIME), TIME '00:00:00', SECOND) <= 30 THEN '00-30s'\n",
|
|
" WHEN TIME_DIFF(CAST(t_convers AS TIME), TIME '00:00:00', SECOND) <= 60 THEN '31-60s'\n",
|
|
" WHEN TIME_DIFF(CAST(t_convers AS TIME), TIME '00:00:00', SECOND) <= 120 THEN '1-2min'\n",
|
|
" WHEN TIME_DIFF(CAST(t_convers AS TIME), TIME '00:00:00', SECOND) <= 300 THEN '2-5min'\n",
|
|
" WHEN TIME_DIFF(CAST(t_convers AS TIME), TIME '00:00:00', SECOND) <= 600 THEN '5-10min'\n",
|
|
" WHEN TIME_DIFF(CAST(t_convers AS TIME), TIME '00:00:00', SECOND) <= 1800 THEN '10-30min'\n",
|
|
" ELSE '30min+'\n",
|
|
" END AS duration_bucket,\n",
|
|
" COUNT(*) AS calls\n",
|
|
"FROM {TABLE}\n",
|
|
"WHERE campaign_name LIKE '%Mutua%'\n",
|
|
" AND t_convers IS NOT NULL\n",
|
|
" AND t_convers != '00:00:00'\n",
|
|
"GROUP BY duration_bucket\n",
|
|
"ORDER BY\n",
|
|
" CASE duration_bucket\n",
|
|
" WHEN '00-30s' THEN 1\n",
|
|
" WHEN '31-60s' THEN 2\n",
|
|
" WHEN '1-2min' THEN 3\n",
|
|
" WHEN '2-5min' THEN 4\n",
|
|
" WHEN '5-10min' THEN 5\n",
|
|
" WHEN '10-30min' THEN 6\n",
|
|
" WHEN '30min+' THEN 7\n",
|
|
" END\n",
|
|
"\"\"\")\n",
|
|
"\n",
|
|
"df_mutua_dur['calls'] = df_mutua_dur['calls'].astype(int)\n",
|
|
"\n",
|
|
"fig = px.bar(\n",
|
|
" df_mutua_dur, x='duration_bucket', y='calls',\n",
|
|
" title='Mutua: distribucion de duracion de conversaciones',\n",
|
|
" labels={'calls': 'Llamadas', 'duration_bucket': 'Duracion'},\n",
|
|
" text='calls',\n",
|
|
" color_discrete_sequence=['#e45756']\n",
|
|
")\n",
|
|
"fig.update_traces(texttemplate='%{text:,.0f}', textposition='outside')\n",
|
|
"fig.update_layout(height=400)\n",
|
|
"fig.show()"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "a7b8c9d0",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"# Mutua: top agentes\n",
|
|
"df_mutua_agents = query_to_df(f\"\"\"\n",
|
|
"SELECT\n",
|
|
" agente,\n",
|
|
" COUNT(*) AS calls,\n",
|
|
" COUNTIF(t_convers != '00:00:00' AND t_convers IS NOT NULL) AS connected,\n",
|
|
" ROUND(AVG(\n",
|
|
" CASE WHEN t_convers != '00:00:00' AND t_convers IS NOT NULL\n",
|
|
" THEN TIME_DIFF(CAST(t_convers AS TIME), TIME '00:00:00', SECOND)\n",
|
|
" END\n",
|
|
" ), 1) AS avg_dur_secs\n",
|
|
"FROM {TABLE}\n",
|
|
"WHERE campaign_name LIKE '%Mutua%'\n",
|
|
" AND agente IS NOT NULL AND agente != ''\n",
|
|
"GROUP BY agente\n",
|
|
"ORDER BY calls DESC\n",
|
|
"LIMIT 15\n",
|
|
"\"\"\")\n",
|
|
"\n",
|
|
"df_mutua_agents['calls'] = df_mutua_agents['calls'].astype(int)\n",
|
|
"df_mutua_agents['connected'] = df_mutua_agents['connected'].astype(int)\n",
|
|
"df_mutua_agents['avg_dur_secs'] = df_mutua_agents['avg_dur_secs'].astype(float)\n",
|
|
"df_mutua_agents['connect_rate'] = (df_mutua_agents['connected'] / df_mutua_agents['calls'] * 100).round(1)\n",
|
|
"\n",
|
|
"fig = px.bar(\n",
|
|
" df_mutua_agents,\n",
|
|
" x='calls', y='agente',\n",
|
|
" orientation='h',\n",
|
|
" title='Mutua: top 15 agentes por volumen',\n",
|
|
" labels={'calls': 'Llamadas', 'agente': 'Agente'},\n",
|
|
" text='calls',\n",
|
|
" color_discrete_sequence=['#e45756']\n",
|
|
")\n",
|
|
"fig.update_layout(yaxis={'categoryorder': 'total ascending'}, height=500)\n",
|
|
"fig.update_traces(texttemplate='%{text:,.0f}', textposition='outside')\n",
|
|
"fig.show()"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "b7c8d9e0",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"# Mutua: tasa de conexion por campana Mutua\n",
|
|
"df_mutua_connect = query_to_df(f\"\"\"\n",
|
|
"SELECT\n",
|
|
" campaign_name,\n",
|
|
" COUNT(*) AS total,\n",
|
|
" COUNTIF(t_convers != '00:00:00' AND t_convers IS NOT NULL) AS connected,\n",
|
|
" ROUND(COUNTIF(t_convers != '00:00:00' AND t_convers IS NOT NULL) * 100.0 / COUNT(*), 1) AS connect_pct\n",
|
|
"FROM {TABLE}\n",
|
|
"WHERE campaign_name LIKE '%Mutua%'\n",
|
|
"GROUP BY campaign_name\n",
|
|
"ORDER BY total DESC\n",
|
|
"\"\"\")\n",
|
|
"\n",
|
|
"df_mutua_connect['total'] = df_mutua_connect['total'].astype(int)\n",
|
|
"df_mutua_connect['connected'] = df_mutua_connect['connected'].astype(int)\n",
|
|
"df_mutua_connect['connect_pct'] = df_mutua_connect['connect_pct'].astype(float)\n",
|
|
"df_mutua_connect"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "c7d8e9f0",
|
|
"metadata": {},
|
|
"source": [
|
|
"## 9. Resumen\n",
|
|
"\n",
|
|
"### Hallazgos clave\n",
|
|
"\n",
|
|
"**Volumen general:**\n",
|
|
"- ~24.7M transacciones entre Nov 2022 y Abr 2026\n",
|
|
"- Dominada por campanas de emision (Aurgi Emision ~11M, Aurgi Presupuestos ~3.5M, MotorTown Emision ~2.2M)\n",
|
|
"\n",
|
|
"**Resoluciones:**\n",
|
|
"- Las resoluciones mas frecuentes revelan el mix de contactabilidad vs resultados reales\n",
|
|
"- \"Comunican\" y \"Contestador Automatico\" representan una gran proporcion (intentos sin exito)\n",
|
|
"\n",
|
|
"**Patrones temporales:**\n",
|
|
"- Actividad concentrada en dias laborables (Lun-Vie)\n",
|
|
"- Picos de actividad en horario de manana (9-14h)\n",
|
|
"- Estacionalidad mensual visible\n",
|
|
"\n",
|
|
"**Duracion de conversaciones:**\n",
|
|
"- Gran proporcion de llamadas sin conversacion (t_convers = 00:00:00)\n",
|
|
"- Las conversaciones efectivas se concentran entre 1-5 minutos\n",
|
|
"- Variacion significativa de duracion entre campanas\n",
|
|
"\n",
|
|
"**Foco Mutua (Happy Robot):**\n",
|
|
"- Mutua Entrante es la campana principal (~511K llamadas)\n",
|
|
"- Patrones de resolucion y duracion especificos a analizar en profundidad\n",
|
|
"- Base para evaluar el impacto de Happy Robot en automatizacion de llamadas\n",
|
|
"\n",
|
|
"### Proximos pasos\n",
|
|
"\n",
|
|
"1. Analisis detallado de tasas de abandono y contactabilidad por campana\n",
|
|
"2. Segmentacion de Mutua por tipo de resolucion y agente\n",
|
|
"3. Baseline de metricas pre-Happy Robot para medir impacto\n",
|
|
"4. Cruzar con datos de `customerSalesforceId` y `customerAnjanaId` para trazabilidad"
|
|
]
|
|
}
|
|
],
|
|
"metadata": {
|
|
"kernelspec": {
|
|
"display_name": "Python 3 (ipykernel)",
|
|
"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.13.5"
|
|
}
|
|
},
|
|
"nbformat": 4,
|
|
"nbformat_minor": 5
|
|
}
|