fn-registry agent
225 lines
6.5 KiB
Plaintext
225 lines
6.5 KiB
Plaintext
{
|
|
"cells": [
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "2b3d1ae8",
|
|
"metadata": {},
|
|
"source": [
|
|
"# 00 — Resultados ejecutados (vía Metabase, sin ADC)\n",
|
|
"\n",
|
|
"Resultados de la ejecución del script `run_via_metabase.py` (BigQuery `autingo-159109.psql_dcpublic`).\n",
|
|
"\n",
|
|
"Ventana: 90 días Q0, 60 días para detectar regeneración.\n",
|
|
"Centros call_center excluidos del cómputo: 159 (CALL CENTER AURGI), 162 (CALL CENTER)."
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": 1,
|
|
"id": "c39f6e2c",
|
|
"metadata": {},
|
|
"outputs": [
|
|
{
|
|
"data": {
|
|
"text/plain": [
|
|
"{'window_days': 90,\n",
|
|
" 'A_quote_cc_eur': 6392965.08,\n",
|
|
" 'B_mismo_cliente_eur': 6923203.81,\n",
|
|
" 'C_total_centros_eur': 29635811.36,\n",
|
|
" 'A_sobre_C': 0.2157,\n",
|
|
" 'B_sobre_C': 0.2336,\n",
|
|
" 'lift_B_vs_A': 1.08,\n",
|
|
" 'centros_activos': 139}"
|
|
]
|
|
},
|
|
"execution_count": 1,
|
|
"metadata": {},
|
|
"output_type": "execute_result"
|
|
}
|
|
],
|
|
"source": [
|
|
"import json\n",
|
|
"from pathlib import Path\n",
|
|
"import pandas as pd\n",
|
|
"import matplotlib.pyplot as plt\n",
|
|
"\n",
|
|
"BASE = Path('../data/results').resolve()\n",
|
|
"\n",
|
|
"def load(name):\n",
|
|
" return pd.read_csv(BASE / f'{name}.csv')\n",
|
|
"\n",
|
|
"totales = json.loads((BASE / 'totales_globales.json').read_text())\n",
|
|
"totales"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "33adaf14",
|
|
"metadata": {},
|
|
"source": [
|
|
"## Q1 — Tasa de conversión por origen del usuario que generó la quote"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "24471a70",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"load('01_conversion_origen')"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "5847ffda",
|
|
"metadata": {},
|
|
"source": [
|
|
"**Lectura:** \n",
|
|
"- Call center genera 62.8K quotes / 90d, convierte 47.1% (29.6K facturas con mismo `order_id`).\n",
|
|
"- Otros usuarios generan 477K quotes / 90d, convierten 57.4% (273.8K facturas).\n",
|
|
"- Brecha de ~10pp es esperable: el call_center genera quote en frío (cliente no presente), el TPV de centro genera quote casi siempre con el cliente ya en mostrador."
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "3484eeb6",
|
|
"metadata": {},
|
|
"source": [
|
|
"## Q2 — 3 KPI por centro\n",
|
|
"\n",
|
|
"- **A** = € facturados desde quotes creados por call_center (mismo order_id).\n",
|
|
"- **B** = € facturados a los mismos clientes (`customer_id` + `vehicle_id`) en centros físicos NO call_center.\n",
|
|
"- **C** = € facturados totales del centro (todos los clientes).\n",
|
|
"- `lift_B_vs_A`: 1.0 = solo factura el quote inicial; >1 = el centro factura más al cliente que sólo el quote."
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "f2bc0458",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"df = load('02_kpi_3_por_centro')\n",
|
|
"df.head(15)"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "201ac95f",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"# Top 15 centros por valor A (más facturado vía call_center)\n",
|
|
"top = df.sort_values('A_quote_cc_eur', ascending=False).head(15).iloc[::-1]\n",
|
|
"fig, ax = plt.subplots(figsize=(10, 7))\n",
|
|
"y = range(len(top))\n",
|
|
"ax.barh(y, top.A_quote_cc_eur, label='A (cc -> factura)')\n",
|
|
"ax.barh(y, (top.B_mismo_cliente_eur - top.A_quote_cc_eur).clip(lower=0),\n",
|
|
" left=top.A_quote_cc_eur, label='B-A (mismo cliente extra)')\n",
|
|
"ax.set_yticks(list(y)); ax.set_yticklabels(top.center_name)\n",
|
|
"ax.set_xlabel('€ facturados (90d)')\n",
|
|
"ax.legend(); ax.set_title('Top 15 centros — quotes call_center -> factura')\n",
|
|
"plt.tight_layout(); plt.show()"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "291fa4af",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"print('TOTALES 90d (excluye centros call_center 159/162):')\n",
|
|
"for k, v in totales.items():\n",
|
|
" print(f' {k:25} {v}')"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "3d161868",
|
|
"metadata": {},
|
|
"source": [
|
|
"## Q3 — Centros que MÁS regeneran el presupuesto\n",
|
|
"\n",
|
|
"Definición operativa: para un par `(customer_id, vehicle_id)` cuyo primer presupuesto (Q0) lo abrió el call_center, hay un Q1+ posterior con **distinto `order_id`** abierto en un terminal del centro físico dentro de 60 días."
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "7831e9fe",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"regen = load('03_regen_por_centro')\n",
|
|
"regen"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "83758dc9",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"top_regen = regen.head(15).iloc[::-1]\n",
|
|
"fig, ax = plt.subplots(figsize=(10, 6))\n",
|
|
"y = range(len(top_regen))\n",
|
|
"ax.barh(y, top_regen.q0_regenerados_aqui)\n",
|
|
"ax.set_yticks(list(y)); ax.set_yticklabels(top_regen.center_name)\n",
|
|
"ax.set_xlabel('# Q0 (de call_center) regenerados en este centro')\n",
|
|
"ax.set_title('Top centros regeneradores de presupuesto call_center (90d Q0, 60d window)')\n",
|
|
"plt.tight_layout(); plt.show()"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "b0f6c55c",
|
|
"metadata": {},
|
|
"source": [
|
|
"## Q4 — ¿Regenerar perjudica la conversión propia del Q0?\n",
|
|
"\n",
|
|
"Conversión del Q0 = el invoice se genera contra el MISMO order_id del Q0 (no contra el order_id regenerado en centro). Si regeneran, ese flujo cae."
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "code",
|
|
"execution_count": null,
|
|
"id": "423b68cd",
|
|
"metadata": {},
|
|
"outputs": [],
|
|
"source": [
|
|
"load('04_regen_vs_conversion')"
|
|
]
|
|
},
|
|
{
|
|
"cell_type": "markdown",
|
|
"id": "4956e2d1",
|
|
"metadata": {},
|
|
"source": [
|
|
"**Lectura:**\n",
|
|
"- 35.5K Q0 sin regeneración convierten al **63.1%** (sobre el order_id original).\n",
|
|
"- 18.5K Q0 con regeneración convierten al **38.7%** sobre el order_id original.\n",
|
|
"- Los 'regenerados' no se 'pierden' necesariamente — el cliente puede haberse facturado vía un order_id distinto (el del centro). Esa parte está capturada en el KPI **B** del cuadro anterior.\n",
|
|
"- Aprox **34.2% de los Q0 call_center** entran en patrón de regeneración (18488 / 53995)."
|
|
]
|
|
}
|
|
],
|
|
"metadata": {
|
|
"kernelspec": {
|
|
"display_name": "Python 3",
|
|
"language": "python",
|
|
"name": "python3"
|
|
},
|
|
"language_info": {
|
|
"name": "python",
|
|
"version": "3.12"
|
|
}
|
|
},
|
|
"nbformat": 4,
|
|
"nbformat_minor": 5
|
|
}
|