{ "cells": [ { "cell_type": "markdown", "id": "1cc9e4a4", "metadata": {}, "source": [ "# Análisis Venta Web — Aurgi\n", "\n", "Partiendo de los hallazgos del notebook 01, reproducimos y comparamos las 3 fuentes de venta web en 2025:\n", "\n", "| Fuente | Tabla(s) | Filtro | Revenue 2025 según doc 8 |\n", "|---|---|---|---|\n", "| **Cubo_Ventas_Calculado** | `anjana_bi_datamart.Cubo_Ventas_Calculado` | `Dim_NombreDimGlobal2 = 'WSWEB'` | 5.879.313 € |\n", "| **NAV directo** | `anjana_sales_invoice_header` + `..._line` | `location_code IN ('18','19','405','421','422')` | 15.878.883 € |\n", "| **Supply Orders → NAV** | `supply_orders` → `logistic_orders` → NAV | `service_request_id IS NOT NULL` | 7.235.399 € |\n", "\n", "Todas en **DCBigQuery (db=6)**.\n", "\n", "## Objetivos\n", "\n", "1. Reproducir los tres totales y confirmar las cifras del doc 8.\n", "2. Desagregar cada fuente por mes, centro, canal y categoría.\n", "3. Cuantificar la diferencia entre **Cubo** y **NAV directo** (la brecha de ~10M€).\n", "4. Mapear qué parte del dashboard 734 se alimenta de cada fuente." ] }, { "cell_type": "markdown", "id": "eff1289d", "metadata": {}, "source": [ "## 1. Setup" ] }, { "cell_type": "code", "execution_count": 1, "id": "dac747f8", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Listo. BigQuery via Metabase.\n" ] } ], "source": [ "import os, re\n", "from pathlib import Path\n", "import pandas as pd\n", "\n", "env_path = Path('.env') if Path('.env').exists() else Path('../.env')\n", "for line in env_path.read_text().splitlines():\n", " if line and not line.startswith('#') and '=' in line:\n", " k, v = line.split('=', 1)\n", " os.environ.setdefault(k.strip(), v.strip())\n", "\n", "from metabase import MetabaseClient, metabase_execute_query\n", "\n", "URL = os.environ['METABASE_URL']\n", "KEY = os.environ['METABASE_API_KEY']\n", "client = MetabaseClient(URL, KEY)\n", "DB_BIGQUERY = 6\n", "\n", "def run_sql(sql: str, db_id: int = DB_BIGQUERY) -> pd.DataFrame:\n", " \"\"\"Ejecuta SQL contra Metabase y retorna DataFrame.\"\"\"\n", " res = metabase_execute_query(client, db_id, sql)\n", " cols = [c['display_name'] or c['name'] for c in res['data']['cols']]\n", " return pd.DataFrame(res['data']['rows'], columns=cols)\n", "\n", "pd.set_option('display.max_colwidth', 100)\n", "pd.set_option('display.float_format', lambda x: f'{x:,.2f}')\n", "print('Listo. BigQuery via Metabase.')" ] }, { "cell_type": "markdown", "id": "9682af7a", "metadata": {}, "source": [ "## 2. Descubrir el project/dataset de BigQuery\n", "\n", "Necesitamos el prefijo correcto para cualificar las tablas en SQL nativo contra BigQuery." ] }, { "cell_type": "code", "execution_count": 2, "id": "740e351e", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Total tablas en DCBigQuery: 2830\n", "--- schemas distintos ---\n", "schema\n", "analytics_353235710 1314\n", "anjana_bi_amg 387\n", "anjana_bi_datamart 213\n", "google_ads_data 202\n", "psql_dcpublic 142\n", "anjana_bi_ntg 102\n", "questions_metabase 67\n", "arbentia_dev_dataset 64\n", "anjana_bi_mmb 61\n", "stg_anjana_bi 58\n", "mssql2022_dbo 56\n", "bi_anjana 52\n", "citaprevia_aurphcp 25\n", "almacen_movs 16\n", "data_models 14\n", "ralarsa 10\n", "rag_datasets 9\n", "test_sales 8\n", "claude_bi 5\n", "call_analytics 5\n", "Name: count, dtype: int64\n", "--- tablas clave referenciadas en doc 8 ---\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idschemaname
1543452mssql2022_dboanjana_sales_invoice_header
1553437mssql2022_dboanjana_sales_invoice_line
4242890anjana_bi_datamartCubo_Ventas_Calculado
13511534psql_dcpubliclogistic_orders
25311530psql_dcpublicsupply_orders
\n", "
" ], "text/plain": [ " id schema name\n", "154 3452 mssql2022_dbo anjana_sales_invoice_header\n", "155 3437 mssql2022_dbo anjana_sales_invoice_line\n", "424 2890 anjana_bi_datamart Cubo_Ventas_Calculado\n", "1351 1534 psql_dcpublic logistic_orders\n", "2531 1530 psql_dcpublic supply_orders" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Listar tablas de db=6 para confirmar naming\n", "db6 = client.request('GET', '/api/database/6?include=tables')\n", "tables = pd.DataFrame([\n", " {'id': t['id'], 'schema': t.get('schema'), 'name': t['name']}\n", " for t in db6.get('tables') or []\n", "])\n", "print(f\"Total tablas en DCBigQuery: {len(tables)}\")\n", "print('--- schemas distintos ---')\n", "print(tables['schema'].value_counts().head(20))\n", "print('--- tablas clave referenciadas en doc 8 ---')\n", "key_names = ['Cubo_Ventas_Calculado', 'anjana_sales_invoice_header', 'anjana_sales_invoice_line', 'supply_orders', 'logistic_orders']\n", "tables[tables['name'].isin(key_names)]" ] }, { "cell_type": "markdown", "id": "f0a1ba81", "metadata": {}, "source": [ "## 3. Fuente 1 — Cubo_Ventas_Calculado (WSWEB)" ] }, { "cell_type": "code", "execution_count": null, "id": "e595c0d4", "metadata": {}, "outputs": [], "source": [ "# Ajustar el nombre completo según lo descubierto en la celda anterior.\n", "# En BigQuery Metabase suele cualificar como `project.dataset.tabla` automaticamente;\n", "# si no lo hace, usar el schema como dataset.\n", "CUBO = 'anjana_bi_datamart.Cubo_Ventas_Calculado'\n", "\n", "sql_cubo_total = f'''\n", "SELECT\n", " COUNT(*) AS lineas,\n", " SUM(Importe_Neto) AS revenue_total\n", "FROM {CUBO}\n", "WHERE Dim_NombreDimGlobal2 = 'WSWEB'\n", " AND EXTRACT(YEAR FROM Fecha_Registro) = 2025\n", "'''\n", "run_sql(sql_cubo_total)" ] }, { "cell_type": "code", "execution_count": null, "id": "3d389e98", "metadata": {}, "outputs": [], "source": [ "# Evolucion mensual Cubo WSWEB 2025\n", "sql_cubo_mes = f'''\n", "SELECT\n", " FORMAT_DATE('%Y-%m', Fecha_Registro) AS mes,\n", " COUNT(*) AS lineas,\n", " SUM(Importe_Neto) AS revenue\n", "FROM {CUBO}\n", "WHERE Dim_NombreDimGlobal2 = 'WSWEB'\n", " AND EXTRACT(YEAR FROM Fecha_Registro) = 2025\n", "GROUP BY mes\n", "ORDER BY mes\n", "'''\n", "df_cubo_mes = run_sql(sql_cubo_mes)\n", "df_cubo_mes" ] }, { "cell_type": "markdown", "id": "ac71ce22", "metadata": {}, "source": [ "## 4. Fuente 2 — NAV directo (centros web)" ] }, { "cell_type": "code", "execution_count": null, "id": "4dbf152e", "metadata": {}, "outputs": [], "source": [ "NAV_HEADER = 'anjana_bi_datamart.anjana_sales_invoice_header'\n", "NAV_LINE = 'anjana_bi_datamart.anjana_sales_invoice_line'\n", "WEB_CENTERS = \"('18','19','405','421','422')\"\n", "\n", "sql_nav_total = f'''\n", "SELECT\n", " COUNT(*) AS lineas,\n", " SUM(l.amount) AS revenue_total\n", "FROM {NAV_LINE} l\n", "JOIN {NAV_HEADER} h\n", " ON l.document_no = h.no\n", "WHERE h.location_code IN {WEB_CENTERS}\n", " AND EXTRACT(YEAR FROM h.posting_date) = 2025\n", "'''\n", "run_sql(sql_nav_total)" ] }, { "cell_type": "code", "execution_count": null, "id": "db4e883e", "metadata": {}, "outputs": [], "source": [ "# NAV por centro y mes\n", "sql_nav_mes = f'''\n", "SELECT\n", " FORMAT_DATE('%Y-%m', h.posting_date) AS mes,\n", " h.location_code,\n", " COUNT(*) AS lineas,\n", " SUM(l.amount) AS revenue\n", "FROM {NAV_LINE} l\n", "JOIN {NAV_HEADER} h\n", " ON l.document_no = h.no\n", "WHERE h.location_code IN {WEB_CENTERS}\n", " AND EXTRACT(YEAR FROM h.posting_date) = 2025\n", "GROUP BY mes, h.location_code\n", "ORDER BY mes, h.location_code\n", "'''\n", "df_nav_mes = run_sql(sql_nav_mes)\n", "df_nav_mes.head(20)" ] }, { "cell_type": "markdown", "id": "9aa66747", "metadata": {}, "source": [ "## 5. Fuente 3 — Supply Orders → NAV (web)" ] }, { "cell_type": "code", "execution_count": null, "id": "9f78689a", "metadata": {}, "outputs": [], "source": [ "SO = 'anjana_bi_datamart.supply_orders'\n", "LO = 'anjana_bi_datamart.logistic_orders'\n", "\n", "sql_so_total = f'''\n", "SELECT\n", " COUNT(*) AS supply_orders_web,\n", " SUM(sale_price) AS revenue\n", "FROM {SO}\n", "WHERE service_request_id IS NOT NULL\n", " AND EXTRACT(YEAR FROM created_at) = 2025\n", "'''\n", "run_sql(sql_so_total)" ] }, { "cell_type": "markdown", "id": "830d4c2e", "metadata": {}, "source": [ "## 6. Comparativa y brecha\n", "\n", "Consolidamos los tres totales y analizamos la diferencia." ] }, { "cell_type": "code", "execution_count": 3, "id": "49ad9aad", "metadata": {}, "outputs": [ { "ename": "NameError", "evalue": "name 'df_cubo_mes' is not defined", "output_type": "error", "traceback": [ "\u001b[31m---------------------------------------------------------------------------\u001b[39m", "\u001b[31mNameError\u001b[39m Traceback (most recent call last)", "\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[3]\u001b[39m\u001b[32m, line 2\u001b[39m\n\u001b[32m 1\u001b[39m \u001b[38;5;66;03m# Juntar los tres mensuales\u001b[39;00m\n\u001b[32m----> \u001b[39m\u001b[32m2\u001b[39m tot_cubo = df_cubo_mes.groupby(\u001b[33m'mes'\u001b[39m)[\u001b[33m'revenue'\u001b[39m].sum().rename(\u001b[33m'cubo'\u001b[39m)\n\u001b[32m 3\u001b[39m tot_nav = df_nav_mes.groupby(\u001b[33m'mes'\u001b[39m)[\u001b[33m'revenue'\u001b[39m].sum().rename(\u001b[33m'nav_directo'\u001b[39m)\n\u001b[32m 4\u001b[39m comparativa = pd.concat([tot_cubo, tot_nav], axis=\u001b[32m1\u001b[39m).reset_index()\n\u001b[32m 5\u001b[39m comparativa[\u001b[33m'brecha_nav_vs_cubo'\u001b[39m] = comparativa[\u001b[33m'nav_directo'\u001b[39m] - comparativa[\u001b[33m'cubo'\u001b[39m]\n", "\u001b[31mNameError\u001b[39m: name 'df_cubo_mes' is not defined" ] } ], "source": [ "# Juntar los tres mensuales\n", "tot_cubo = df_cubo_mes.groupby('mes')['revenue'].sum().rename('cubo')\n", "tot_nav = df_nav_mes.groupby('mes')['revenue'].sum().rename('nav_directo')\n", "comparativa = pd.concat([tot_cubo, tot_nav], axis=1).reset_index()\n", "comparativa['brecha_nav_vs_cubo'] = comparativa['nav_directo'] - comparativa['cubo']\n", "comparativa['ratio_cubo_sobre_nav_pct'] = 100 * comparativa['cubo'] / comparativa['nav_directo']\n", "comparativa" ] }, { "cell_type": "markdown", "id": "d89d792d", "metadata": {}, "source": [ "## 7. Qué filtra el Cubo respecto a NAV\n", "\n", "El Cubo aplica filtros de negocio extra: solo movimientos tipo 'Venta', excluye devoluciones, ajustes, ciertos tipos de ticket.\n", "Exploramos el header NAV para ver qué categorías se pierden." ] }, { "cell_type": "code", "execution_count": null, "id": "6c026d51", "metadata": {}, "outputs": [], "source": [ "# Distribución por document_type en NAV centros web 2025\n", "sql_doctype = f'''\n", "SELECT\n", " h.document_type,\n", " COUNT(*) AS lineas,\n", " SUM(l.amount) AS revenue\n", "FROM {NAV_LINE} l\n", "JOIN {NAV_HEADER} h\n", " ON l.document_no = h.no\n", "WHERE h.location_code IN {WEB_CENTERS}\n", " AND EXTRACT(YEAR FROM h.posting_date) = 2025\n", "GROUP BY h.document_type\n", "ORDER BY revenue DESC\n", "'''\n", "run_sql(sql_doctype)" ] }, { "cell_type": "markdown", "id": "273719d1", "metadata": {}, "source": [ "## 8. Cruce dashboard 734 ← fuente\n", "\n", "Usando `df_dash_sources` del notebook 01 (celda 26), identificamos qué tabs del dashboard se alimentan del Cubo, de NAV directo o de supply_orders." ] }, { "cell_type": "code", "execution_count": null, "id": "e5613255", "metadata": {}, "outputs": [], "source": [ "# Si df_dash_sources vive en el mismo kernel (tras correr 01), lo reutilizamos.\n", "# Si no, lo regeneramos aqui.\n", "try:\n", " df_dash_sources\n", " print('Reutilizando df_dash_sources del notebook 01')\n", "except NameError:\n", " print('df_dash_sources no esta en el kernel. Ejecuta el notebook 01 o copia la celda 26 aqui.')\n", "else:\n", " print()\n", " print('=== DISTRIBUCION DE LAS 85 CARDS DEL DASHBOARD ===')\n", " print(df_dash_sources.groupby(['source_db','source_table']).size().sort_values(ascending=False).to_string())" ] }, { "cell_type": "markdown", "id": "96450ff7", "metadata": {}, "source": [ "## 9. Hallazgos y decisiones\n", "\n", "- (rellenar tras ejecutar)\n", "- Cuál es la fuente canonica para \"venta web\" en reportes internos\n", "- Cuándo usar cada una (p.ej. Cubo para analítica financiera, SO para operativa)\n", "- Ajustes necesarios al dashboard 734 si mezcla fuentes" ] }, { "cell_type": "code", "execution_count": 2, "id": "6c5093a2", "metadata": {}, "outputs": [ { "ename": "HTTPStatusError", "evalue": "Client error '400 Bad Request' for url 'https://reports.autingo.es/api/dataset'\nFor more information check: https://developer.mozilla.org/en-US/docs/Web/HTTP/Status/400", "output_type": "error", "traceback": [ "\u001b[31m---------------------------------------------------------------------------\u001b[39m", "\u001b[31mHTTPStatusError\u001b[39m Traceback (most recent call last)", "\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[2]\u001b[39m\u001b[32m, line 17\u001b[39m\n\u001b[32m 13\u001b[39m FROM `{CUBO}`\n\u001b[32m 14\u001b[39m WHERE Dim_NombreDimGlobal2 = \u001b[33m'WSWEB'\u001b[39m\n\u001b[32m 15\u001b[39m AND EXTRACT(YEAR FROM Fecha_Registro) = \u001b[32m2025\u001b[39m\n\u001b[32m 16\u001b[39m '''\n\u001b[32m---> \u001b[39m\u001b[32m17\u001b[39m df_cubo_tot = run_sql(sql_cubo_total)\n\u001b[32m 18\u001b[39m print(\u001b[33m'=== FUENTE 1: Cubo_Ventas_Calculado (WSWEB) 2025 ==='\u001b[39m)\n\u001b[32m 19\u001b[39m print(df_cubo_tot.to_string(index=\u001b[38;5;28;01mFalse\u001b[39;00m))\n", "\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[1]\u001b[39m\u001b[32m, line 20\u001b[39m, in \u001b[36mrun_sql\u001b[39m\u001b[34m(sql, db_id)\u001b[39m\n\u001b[32m 18\u001b[39m \u001b[38;5;28;01mdef\u001b[39;00m run_sql(sql: str, db_id: int = DB_BIGQUERY) -> pd.DataFrame:\n\u001b[32m 19\u001b[39m \u001b[33m\"\"\"Ejecuta SQL contra Metabase y retorna DataFrame.\"\"\"\u001b[39m\n\u001b[32m---> \u001b[39m\u001b[32m20\u001b[39m res = metabase_execute_query(client, db_id, sql)\n\u001b[32m 21\u001b[39m cols = [c[\u001b[33m'display_name'\u001b[39m] \u001b[38;5;28;01mor\u001b[39;00m c[\u001b[33m'name'\u001b[39m] \u001b[38;5;28;01mfor\u001b[39;00m c \u001b[38;5;28;01min\u001b[39;00m res[\u001b[33m'data'\u001b[39m][\u001b[33m'cols'\u001b[39m]]\n\u001b[32m 22\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m pd.DataFrame(res[\u001b[33m'data'\u001b[39m][\u001b[33m'rows'\u001b[39m], columns=cols)\n", "\u001b[36mFile \u001b[39m\u001b[32m~/fn_registry/python/functions/metabase/cards.py:227\u001b[39m, in \u001b[36mmetabase_execute_query\u001b[39m\u001b[34m(client, database_id, sql, max_results)\u001b[39m\n\u001b[32m 222\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m max_results > \u001b[32m0\u001b[39m:\n\u001b[32m 223\u001b[39m body[\u001b[33m\"\u001b[39m\u001b[33mconstraints\u001b[39m\u001b[33m\"\u001b[39m] = {\n\u001b[32m 224\u001b[39m \u001b[33m\"\u001b[39m\u001b[33mmax-results\u001b[39m\u001b[33m\"\u001b[39m: max_results,\n\u001b[32m 225\u001b[39m \u001b[33m\"\u001b[39m\u001b[33mmax-results-bare-rows\u001b[39m\u001b[33m\"\u001b[39m: max_results,\n\u001b[32m 226\u001b[39m }\n\u001b[32m--> \u001b[39m\u001b[32m227\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mclient\u001b[49m\u001b[43m.\u001b[49m\u001b[43mrequest\u001b[49m\u001b[43m(\u001b[49m\u001b[33;43m\"\u001b[39;49m\u001b[33;43mPOST\u001b[39;49m\u001b[33;43m\"\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[33;43m\"\u001b[39;49m\u001b[33;43m/api/dataset\u001b[39;49m\u001b[33;43m\"\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mjson\u001b[49m\u001b[43m=\u001b[49m\u001b[43mbody\u001b[49m\u001b[43m)\u001b[49m\n", "\u001b[36mFile \u001b[39m\u001b[32m~/fn_registry/python/functions/metabase/client.py:45\u001b[39m, in \u001b[36mMetabaseClient.request\u001b[39m\u001b[34m(self, method, path, **kwargs)\u001b[39m\n\u001b[32m 31\u001b[39m \u001b[38;5;250m\u001b[39m\u001b[33;03m\"\"\"Ejecuta una peticion HTTP contra la API de Metabase.\u001b[39;00m\n\u001b[32m 32\u001b[39m \n\u001b[32m 33\u001b[39m \u001b[33;03mArgs:\u001b[39;00m\n\u001b[32m (...)\u001b[39m\u001b[32m 42\u001b[39m \u001b[33;03m httpx.HTTPStatusError: Si el status code no es 2xx.\u001b[39;00m\n\u001b[32m 43\u001b[39m \u001b[33;03m\"\"\"\u001b[39;00m\n\u001b[32m 44\u001b[39m resp = \u001b[38;5;28mself\u001b[39m._http.request(method, path, **kwargs)\n\u001b[32m---> \u001b[39m\u001b[32m45\u001b[39m \u001b[43mresp\u001b[49m\u001b[43m.\u001b[49m\u001b[43mraise_for_status\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[32m 46\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m resp.content:\n\u001b[32m 47\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m\n", "\u001b[36mFile \u001b[39m\u001b[32m~/fn_registry/projects/aurgi/analysis/venta_web/.venv/lib/python3.13/site-packages/httpx/_models.py:829\u001b[39m, in \u001b[36mResponse.raise_for_status\u001b[39m\u001b[34m(self)\u001b[39m\n\u001b[32m 827\u001b[39m error_type = error_types.get(status_class, \u001b[33m\"\u001b[39m\u001b[33mInvalid status code\u001b[39m\u001b[33m\"\u001b[39m)\n\u001b[32m 828\u001b[39m message = message.format(\u001b[38;5;28mself\u001b[39m, error_type=error_type)\n\u001b[32m--> \u001b[39m\u001b[32m829\u001b[39m \u001b[38;5;28;01mraise\u001b[39;00m HTTPStatusError(message, request=request, response=\u001b[38;5;28mself\u001b[39m)\n", "\u001b[31mHTTPStatusError\u001b[39m: Client error '400 Bad Request' for url 'https://reports.autingo.es/api/dataset'\nFor more information check: https://developer.mozilla.org/en-US/docs/Web/HTTP/Status/400" ] } ], "source": [ "# --- Paths cualificados reales (corregidos tras celda 4) ---\n", "CUBO = 'anjana_bi_datamart.Cubo_Ventas_Calculado'\n", "NAV_HEADER = 'mssql2022_dbo.anjana_sales_invoice_header'\n", "NAV_LINE = 'mssql2022_dbo.anjana_sales_invoice_line'\n", "SO = 'psql_dcpublic.supply_orders'\n", "LO = 'psql_dcpublic.logistic_orders'\n", "\n", "# FUENTE 1: Cubo_Ventas_Calculado (WSWEB) 2025\n", "sql_cubo_total = f'''\n", "SELECT\n", " COUNT(*) AS lineas,\n", " SUM(Importe_Neto) AS revenue_total\n", "FROM `{CUBO}`\n", "WHERE Dim_NombreDimGlobal2 = 'WSWEB'\n", " AND EXTRACT(YEAR FROM Fecha_Registro) = 2025\n", "'''\n", "df_cubo_tot = run_sql(sql_cubo_total)\n", "print('=== FUENTE 1: Cubo_Ventas_Calculado (WSWEB) 2025 ===')\n", "print(df_cubo_tot.to_string(index=False))" ] }, { "cell_type": "code", "execution_count": 3, "id": "4af04f5b", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Tabla: Cubo_Ventas_Calculado db=6\n", "Total columnas: 45\n", "\n", " fecha [DateTime]\n", " Dim_Fecha [DateTime]\n", " Dim_NombreCentro [Text]\n", " Dim_IdComAutonoma [Integer]\n", " Dim_NombreComAutonoma [Text]\n", " Dim_IdZona [Integer]\n", " Dim_NombreZona [Text]\n", " Dim_AmbitoCentro [Text]\n", " Dim_NombreTipoMov [Text]\n", " Dim_NombreDimGlobal2 [Text]\n", " Dim_NombreProveedor [Text]\n", " Dim_NombreCampana [Text]\n", " Dim_NombreTipoTicket [Text]\n", " Dim_NombreMotivoAjuste [Text]\n", " Dim_NombreProducto [Text]\n", " Dim_CodProducto [Text]\n", " Dim_IdCategoria [Text]\n", " Dim_IdActivoExterno [Integer]\n", " Dim_IdLiquidacion [Integer]\n", " Dim_IdEstado [Integer]\n", " Dim_IdTipo [Integer]\n", " Dim_PeriodoCobertura [Integer]\n", " Dim_Matricula [Text]\n", " Dim_NombreCategoria [Text]\n", " Dim_NombreTipo [Text]\n" ] } ], "source": [ "# Listar columnas de Cubo_Ventas_Calculado para conocer nombres reales\n", "cubo_tbl = client.request(\"GET\", \"/api/table/2890/query_metadata\")\n", "print(f\"Tabla: {cubo_tbl['name']} db={cubo_tbl['db_id']}\")\n", "print(f\"Total columnas: {len(cubo_tbl['fields'])}\")\n", "print()\n", "# Buscar campos relacionados con fecha, importe, dimension\n", "for f in cubo_tbl['fields']:\n", " n = f['name']\n", " if any(k in n.lower() for k in ['fecha', 'date', 'importe', 'amount', 'dim_', 'nombre']):\n", " print(f\" {n:45} [{f['base_type'].replace('type/','')}]\")" ] }, { "cell_type": "code", "execution_count": 4, "id": "38d51545", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " idTipoMovimiento [Integer]\n", " idMotivoAjuste [Integer]\n", " idTipo [Integer]\n", " idTipoDeTicket [Integer]\n", " numeroLineas [Integer]\n", " cantidad [Decimal]\n", " costeUnitario [Decimal]\n", " precioUnitario [Decimal]\n", " compraReal [Decimal]\n", " compraEsperada [Decimal]\n", " Dim_IdComAutonoma [Integer]\n", " Dim_IdZona [Integer]\n", " Dim_IdActivoExterno [Integer]\n", " Dim_IdLiquidacion [Integer]\n", " Dim_IdEstado [Integer]\n", " Dim_IdTipo [Integer]\n", " Dim_PeriodoCobertura [Integer]\n", " PrecioVenta [Decimal]\n", " PrecioCompra [Decimal]\n" ] } ], "source": [ "# Todas las columnas numericas (para encontrar importe/revenue)\n", "for f in cubo_tbl['fields']:\n", " if f['base_type'] in ('type/Float', 'type/Decimal', 'type/Integer', 'type/BigInteger'):\n", " print(f\" {f['name']:45} [{f['base_type'].replace('type/','')}]\")" ] }, { "cell_type": "code", "execution_count": 5, "id": "78650d74", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "=== FUENTE 1 (v2): Cubo_Ventas_Calculado WSWEB 2025 ===\n", " lineas revenue suma_precio_venta\n", " 158617 5,879,313.22 5,879,313.22\n", "\n", "Doc 8 dice: 5.879.313€ en 158.617 lineas\n" ] } ], "source": [ "# Query Cubo ajustada: campo fecha es 'fecha', revenue = cantidad * precioUnitario\n", "sql_cubo_total_v2 = f'''\n", "SELECT\n", " COUNT(*) AS lineas,\n", " SUM(cantidad * precioUnitario) AS revenue,\n", " SUM(PrecioVenta) AS suma_precio_venta\n", "FROM `{CUBO}`\n", "WHERE Dim_NombreDimGlobal2 = 'WSWEB'\n", " AND EXTRACT(YEAR FROM fecha) = 2025\n", "'''\n", "df_cubo_v2 = run_sql(sql_cubo_total_v2)\n", "print('=== FUENTE 1 (v2): Cubo_Ventas_Calculado WSWEB 2025 ===')\n", "print(df_cubo_v2.to_string(index=False))\n", "print()\n", "print(f\"Doc 8 dice: 5.879.313€ en 158.617 lineas\")" ] }, { "cell_type": "code", "execution_count": 6, "id": "94ba6ba6", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "--- anjana_sales_invoice_header (id 3452) — 180 cols ---\n", " dates: ['document_date', 'hora_env__sol__autorizacion', 'time_sent', 'pmt__discount_date', 'shipment_date', 'fecha_operacion']\n", " nums: ['tipo_de_registro_tpv', 'applies_to_doc__type', 'estado_pago_web', 'eu_3_party_trade', 'get_shipment_used', 'credito_disponible', 'venta_online', 'num__turno']\n", " keys: ['no_', 'document_date', 'order_no__series', 'vat_registration_no_', 'applies_to_doc__no_', 'applies_to_bill_no_', 'prepayment_no__series', 'quote_no_', 'num__turno', 'pre_assigned_no__series', 'campaign_no_', 'pre_assigned_no_']\n", "\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "--- anjana_sales_invoice_line (id 3437) — 126 cols ---\n", " dates: ['fa_posting_date', 'shipment_date', 'posting_date', '_fivetran_synced']\n", " nums: ['line_no_', 'ec__', 'vat__', 'permite_dto__promocion', 'cross_reference_type', 'job_contract_entry_no_', 'inv__discount_amount', 'unit_price']\n", " keys: ['document_no_', 'line_no_', 'cross_reference_no_', 'job_contract_entry_no_', 'inv__discount_amount', 'unit_price', 'nonstock', 'line_amount', 'num__turno', 'shipment_no_', 'abono_num__linea_ticket', 'amount']\n", "\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "--- supply_orders (id 1530) — 58 cols ---\n", " dates: ['delivery_date', 'created_at', 'updated_at', 'reviewed_at', 'shipped_at', 'provider_delivery_at']\n", " nums: ['id', 'service_request_id', 'product_id', 'quantity', 'supply_method', 'provider_id', 'status', 'marketplace_offer_id']\n", " keys: ['service_request_id', 'provider_order_id', 'logistic_order_id', 'sale_price', 'purchase_price', 'tpv_orders_order_id', 'tpv_orders_orderitem_id', 'eci_sell_price', 'shipping_price', 'internal_provider_order', 'official_service', 'order_version']\n", "\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "--- logistic_orders (id 1534) — 31 cols ---\n", " dates: ['datetime', 'created_at', 'updated_at']\n", " nums: ['id', 'service_request_id', 'center_id', 'provider_id', 'status', 'order_type', 'shipping_method', 'shipping_price']\n", " keys: ['service_request_id', 'center_id', 'order_id', 'order_type', 'shipping_price', 'external_total_price', 'cd_order_id', 'center_request_id']\n", "\n" ] } ], "source": [ "# Inspect columns of the 4 remaining tables in BigQuery\n", "TABLE_IDS = {\n", " 'anjana_sales_invoice_header': 3452,\n", " 'anjana_sales_invoice_line': 3437,\n", " 'supply_orders': 1530,\n", " 'logistic_orders': 1534,\n", "}\n", "tbl_meta = {}\n", "for name, tid in TABLE_IDS.items():\n", " m = client.request(\"GET\", f\"/api/table/{tid}/query_metadata\")\n", " tbl_meta[name] = m\n", " date_cols = [f['name'] for f in m['fields'] if f['base_type'] in ('type/Date','type/DateTime','type/DateTimeWithLocalTZ')]\n", " num_cols = [f['name'] for f in m['fields'] if f['base_type'] in ('type/Float','type/Decimal','type/Integer','type/BigInteger')]\n", " key_cols = [f['name'] for f in m['fields'] if any(k in f['name'].lower() for k in ['amount','price','location','center','no','document','order','request','service'])]\n", " print(f\"--- {name} (id {tid}) — {len(m['fields'])} cols ---\")\n", " print(f\" dates: {date_cols[:6]}\")\n", " print(f\" nums: {num_cols[:8]}\")\n", " print(f\" keys: {key_cols[:12]}\")\n", " print()" ] }, { "cell_type": "code", "execution_count": 7, "id": "7af4af12", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Location cols header: ['location_code']\n", "Date/Fecha cols header: ['document_date', 'pmt__discount_date', 'shipment_date', 'fecha_operacion', 'fecha_env__sol__autorizacion', 'posting_date', 'order_date', 'date_sent', 'due_date']\n" ] } ], "source": [ "# location_code en header?\n", "loc_cols = [f['name'] for f in tbl_meta['anjana_sales_invoice_header']['fields'] if 'location' in f['name'].lower()]\n", "date_all = [f['name'] for f in tbl_meta['anjana_sales_invoice_header']['fields'] if 'date' in f['name'].lower() or 'fecha' in f['name'].lower()]\n", "print('Location cols header:', loc_cols)\n", "print('Date/Fecha cols header:', date_all)" ] }, { "cell_type": "code", "execution_count": 8, "id": "31e6e417", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "=== FUENTE 2: NAV directo (centros web) 2025 ===\n", " lineas revenue suma_line_amount\n", " 315831 13,324,819.67 15,872,265.87\n", "Doc 8 dice: 15.878.883€ en 315.173 lineas\n" ] } ], "source": [ "# FUENTE 2: NAV directo (centros web '18','19','405','421','422') 2025\n", "sql_nav_total = f'''\n", "SELECT\n", " COUNT(*) AS lineas,\n", " SUM(l.amount) AS revenue,\n", " SUM(l.line_amount) AS suma_line_amount\n", "FROM `{NAV_LINE}` l\n", "JOIN `{NAV_HEADER}` h ON l.document_no_ = h.no_\n", "WHERE h.location_code IN ('18','19','405','421','422')\n", " AND EXTRACT(YEAR FROM h.posting_date) = 2025\n", "'''\n", "df_nav_v1 = run_sql(sql_nav_total)\n", "print('=== FUENTE 2: NAV directo (centros web) 2025 ===')\n", "print(df_nav_v1.to_string(index=False))\n", "print(f\"Doc 8 dice: 15.878.883€ en 315.173 lineas\")" ] }, { "cell_type": "code", "execution_count": 9, "id": "86058b68", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "=== FUENTE 3: Supply Orders web (service_request_id NOT NULL) 2025 ===\n", " supply_orders revenue_sale_x_qty suma_sale_price\n", " 192251 22,669,646.01 11,302,142.62\n", "Doc 8 dice: 7.235.399€ en ~160K lineas\n" ] } ], "source": [ "# FUENTE 3: Supply Orders web (service_request_id IS NOT NULL) 2025\n", "sql_so_total = f'''\n", "SELECT\n", " COUNT(*) AS supply_orders,\n", " SUM(sale_price * quantity) AS revenue_sale_x_qty,\n", " SUM(sale_price) AS suma_sale_price\n", "FROM `{SO}`\n", "WHERE service_request_id IS NOT NULL\n", " AND EXTRACT(YEAR FROM created_at) = 2025\n", "'''\n", "df_so = run_sql(sql_so_total)\n", "print('=== FUENTE 3: Supply Orders web (service_request_id NOT NULL) 2025 ===')\n", "print(df_so.to_string(index=False))\n", "print(f\"Doc 8 dice: 7.235.399€ en ~160K lineas\")" ] }, { "cell_type": "code", "execution_count": 10, "id": "0557b658", "metadata": {}, "outputs": [ { "ename": "NameError", "evalue": "name 'db6' is not defined", "output_type": "error", "traceback": [ "\u001b[31m---------------------------------------------------------------------------\u001b[39m", "\u001b[31mNameError\u001b[39m Traceback (most recent call last)", "\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[10]\u001b[39m\u001b[32m, line 4\u001b[39m\n\u001b[32m 1\u001b[39m \u001b[38;5;66;03m# Buscar tablas candidatas para \"centros\" con flag digital\u001b[39;00m\n\u001b[32m 2\u001b[39m \u001b[38;5;66;03m# Probables: catalogos maestros en anjana_bi_datamart, mssql2022_dbo o anjana_bi_ntg\u001b[39;00m\n\u001b[32m 3\u001b[39m candidates = []\n\u001b[32m----> \u001b[39m\u001b[32m4\u001b[39m \u001b[38;5;28;01mfor\u001b[39;00m t \u001b[38;5;28;01min\u001b[39;00m db6[\u001b[33m'tables'\u001b[39m]:\n\u001b[32m 5\u001b[39m n = t[\u001b[33m'name'\u001b[39m].lower()\n\u001b[32m 6\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m any(k \u001b[38;5;28;01min\u001b[39;00m n \u001b[38;5;28;01mfor\u001b[39;00m k \u001b[38;5;28;01min\u001b[39;00m [\u001b[33m'centro'\u001b[39m, \u001b[33m'center'\u001b[39m, \u001b[33m'compania'\u001b[39m, \u001b[33m'company'\u001b[39m, \u001b[33m'location'\u001b[39m, \u001b[33m'dim_centro'\u001b[39m, \u001b[33m'dim_nom'\u001b[39m]):\n\u001b[32m 7\u001b[39m candidates.append({\u001b[33m'id'\u001b[39m: t[\u001b[33m'id'\u001b[39m], \u001b[33m'schema'\u001b[39m: t.get(\u001b[33m'schema'\u001b[39m,\u001b[33m''\u001b[39m), \u001b[33m'name'\u001b[39m: t[\u001b[33m'name'\u001b[39m], \u001b[33m'rows'\u001b[39m: t.get(\u001b[33m'entity_type'\u001b[39m)})\n", "\u001b[31mNameError\u001b[39m: name 'db6' is not defined" ] } ], "source": [ "# Buscar tablas candidatas para \"centros\" con flag digital\n", "# Probables: catalogos maestros en anjana_bi_datamart, mssql2022_dbo o anjana_bi_ntg\n", "candidates = []\n", "for t in db6['tables']:\n", " n = t['name'].lower()\n", " if any(k in n for k in ['centro', 'center', 'compania', 'company', 'location', 'dim_centro', 'dim_nom']):\n", " candidates.append({'id': t['id'], 'schema': t.get('schema',''), 'name': t['name'], 'rows': t.get('entity_type')})\n", "pd.DataFrame(candidates).head(30)" ] }, { "cell_type": "code", "execution_count": 11, "id": "89818b26", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Tablas candidatas centros: 163\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idschemaname
02332google_ads_dataads_CampaignLocationTargetStats_7692019626
12365google_ads_dataads_LocationBasedCampaignCriterion_7692019626
22450google_ads_dataads_LocationsDistanceStats_7692019626
32354google_ads_dataads_LocationsUserLocationsStats_7692019626
44141anjana_bi_datamartagg_venta_cat_centro_dia_mas_n1
54091anjana_bi_datamartagg_venta_cat_centro_semana_mas_n1
61914almacen_movsalmacen_centro
71924stg_anjana_bialmacen_centro
83424mssql2022_dboanjana_location
94263anjana_bi_amgapertura_agosto_centros
103681anjana_bi_amgapertura_centros
113940anjana_bi_amgapertura_centros_junio_v3
124493stg_anjana_biapertura_centros_mat
133941anjana_bi_amgapertura_junio_centros_patch_1
143674anjana_bi_datamartapertura_mayo_centros
153836anjana_bi_amgaperturas_junio_centros
163455mssql2022_dboautocentros_del_suroeste_itemstatusmodlog
173431mssql2022_dboautocentros_del_suroeste_purchdocsdeletelog
184472anjana_bi_ntgboxes_centros_mecanica_cristales
194475anjana_bi_ntgboxes_centros_mecanica_cristales_completo
204218anjana_bi_datamartCalendario_centros_productos
214216anjana_bi_datamartCalendario_productos_centros_ventas_n-1
224242anjana_bi_datamartCalen_product_center_n_materialized
233105anjana_bi_datamartCantidad_de_citas_contra_total_tiempo_citas_centros_cristales_prox_semana
241588psql_dcpubliccenters
253135anjana_bi_amgcenters_clean
262939test_salescentros
274102questions_metabasecentros
281679citaprevia_aurphcpcentros
291926stg_anjana_bicentros
\n", "
" ], "text/plain": [ " id schema \\\n", "0 2332 google_ads_data \n", "1 2365 google_ads_data \n", "2 2450 google_ads_data \n", "3 2354 google_ads_data \n", "4 4141 anjana_bi_datamart \n", "5 4091 anjana_bi_datamart \n", "6 1914 almacen_movs \n", "7 1924 stg_anjana_bi \n", "8 3424 mssql2022_dbo \n", "9 4263 anjana_bi_amg \n", "10 3681 anjana_bi_amg \n", "11 3940 anjana_bi_amg \n", "12 4493 stg_anjana_bi \n", "13 3941 anjana_bi_amg \n", "14 3674 anjana_bi_datamart \n", "15 3836 anjana_bi_amg \n", "16 3455 mssql2022_dbo \n", "17 3431 mssql2022_dbo \n", "18 4472 anjana_bi_ntg \n", "19 4475 anjana_bi_ntg \n", "20 4218 anjana_bi_datamart \n", "21 4216 anjana_bi_datamart \n", "22 4242 anjana_bi_datamart \n", "23 3105 anjana_bi_datamart \n", "24 1588 psql_dcpublic \n", "25 3135 anjana_bi_amg \n", "26 2939 test_sales \n", "27 4102 questions_metabase \n", "28 1679 citaprevia_aurphcp \n", "29 1926 stg_anjana_bi \n", "\n", " name \n", "0 ads_CampaignLocationTargetStats_7692019626 \n", "1 ads_LocationBasedCampaignCriterion_7692019626 \n", "2 ads_LocationsDistanceStats_7692019626 \n", "3 ads_LocationsUserLocationsStats_7692019626 \n", "4 agg_venta_cat_centro_dia_mas_n1 \n", "5 agg_venta_cat_centro_semana_mas_n1 \n", "6 almacen_centro \n", "7 almacen_centro \n", "8 anjana_location \n", "9 apertura_agosto_centros \n", "10 apertura_centros \n", "11 apertura_centros_junio_v3 \n", "12 apertura_centros_mat \n", "13 apertura_junio_centros_patch_1 \n", "14 apertura_mayo_centros \n", "15 aperturas_junio_centros \n", "16 autocentros_del_suroeste_itemstatusmodlog \n", "17 autocentros_del_suroeste_purchdocsdeletelog \n", "18 boxes_centros_mecanica_cristales \n", "19 boxes_centros_mecanica_cristales_completo \n", "20 Calendario_centros_productos \n", "21 Calendario_productos_centros_ventas_n-1 \n", "22 Calen_product_center_n_materialized \n", "23 Cantidad_de_citas_contra_total_tiempo_citas_centros_cristales_prox_semana \n", "24 centers \n", "25 centers_clean \n", "26 centros \n", "27 centros \n", "28 centros \n", "29 centros " ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Recargar metadata y globals tras restart del kernel\n", "db6 = client.request('GET', '/api/database/6?include=tables')\n", "TABLE_IDS = {\n", " 'anjana_sales_invoice_header': 3452,\n", " 'anjana_sales_invoice_line': 3437,\n", " 'supply_orders': 1530,\n", " 'logistic_orders': 1534,\n", "}\n", "tbl_meta = {}\n", "for name, tid in TABLE_IDS.items():\n", " tbl_meta[name] = client.request(\"GET\", f\"/api/table/{tid}/query_metadata\")\n", "cubo_tbl = client.request(\"GET\", \"/api/table/2890/query_metadata\")\n", "\n", "CUBO = 'anjana_bi_datamart.Cubo_Ventas_Calculado'\n", "NAV_HEADER = 'mssql2022_dbo.anjana_sales_invoice_header'\n", "NAV_LINE = 'mssql2022_dbo.anjana_sales_invoice_line'\n", "SO = 'psql_dcpublic.supply_orders'\n", "LO = 'psql_dcpublic.logistic_orders'\n", "\n", "# Buscar tablas de centros\n", "candidates = []\n", "for t in db6['tables']:\n", " n = t['name'].lower()\n", " if any(k in n for k in ['centro', 'center', 'compania', 'company', 'location']):\n", " candidates.append({'id': t['id'], 'schema': t.get('schema',''), 'name': t['name']})\n", "print(f\"Tablas candidatas centros: {len(candidates)}\")\n", "pd.DataFrame(candidates).head(30)" ] }, { "cell_type": "code", "execution_count": 12, "id": "f9f93165", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "=== mssql2022_dbo.anjana_location (id 3424) — 108 cols ===\n", " code [Text]\n", " cross_dock_bin_code [Text]\n", " tipo_de_surtido [Integer]\n", " shipment_bin_code [Text]\n", " post_code [Text]\n", " receipt_bin_code [Text]\n", " outbound_production_bin_code [Text]\n", " open_shop_floor_bin_code [Text]\n", " name_2 [Text]\n", " inbound_bom_bin_code [Text]\n", " base_calendar_code [Text]\n", " adjustment_bin_code [Text]\n", " name [Text]\n", " outbound_bom_bin_code [Text]\n", " country_region_code [Text]\n", " inbound_production_bin_code [Text]\n", " put_away_template_code [Text]\n", "\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "=== psql_dcpublic.centers (id 1588) — 101 cols ===\n", " id [Integer]\n", " name [Text]\n", " nav_id [Text]\n", " company_id [Integer]\n", " center_type [Integer]\n", " store_type [Integer]\n", " postal_code [Text]\n", " municipality_id [Integer]\n", " normalized_name [Text]\n", " external_id [Text]\n", " allow_automatic_provider_orders [Boolean]\n", " tax_type [Integer]\n", " teccom_id [Text]\n", " uuid [Text]\n", " provider_config [JSON]\n", " pin_pad_payment_type_id [Integer]\n", " cash_payment_type_id [Integer]\n", " zone_id [Integer]\n", " visualtime_group_id [Integer]\n", " stock_center_id [Integer]\n", " is_digital [Boolean]\n", "\n" ] } ], "source": [ "# Inspeccionar las 2 candidatas mas probables: anjana_location (Navision) y centers (DC)\n", "for tid, label in [(3424, 'mssql2022_dbo.anjana_location'), (1588, 'psql_dcpublic.centers')]:\n", " m = client.request(\"GET\", f\"/api/table/{tid}/query_metadata\")\n", " print(f\"=== {label} (id {tid}) — {len(m['fields'])} cols ===\")\n", " for f in m['fields']:\n", " n = f['name']\n", " if any(k in n.lower() for k in ['code','name','company','compan','nav','id','type','digital','online','web','channel']):\n", " print(f\" {n:40} [{f['base_type'].replace('type/','')}]\")\n", " print()" ] }, { "cell_type": "code", "execution_count": 13, "id": "815845c6", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Centros digitales: 5\n", " id name nav_id company_id center_type\n", "160 Aurgi Web 18 1 0\n", "161 MT Web 19 2 0\n", "167 Autingo 405 3 0\n", "183 Aurgi Asociados Gruas 421 1 0\n", "184 Aurgi Asociados 422 1 0\n", "\n", "nav_ids digital: ('18', '19', '405', '421', '422')\n" ] } ], "source": [ "# Listar centros digitales\n", "CENTERS = 'psql_dcpublic.centers'\n", "sql_digital = f'''\n", "SELECT id, name, nav_id, company_id, center_type\n", "FROM `{CENTERS}`\n", "WHERE is_digital = TRUE\n", "ORDER BY nav_id\n", "'''\n", "df_digital = run_sql(sql_digital)\n", "print(f'Centros digitales: {len(df_digital)}')\n", "print(df_digital.to_string(index=False))\n", "NAV_IDS_DIGITAL = tuple(df_digital['nav_id'].dropna().tolist())\n", "print()\n", "print('nav_ids digital:', NAV_IDS_DIGITAL)" ] }, { "cell_type": "code", "execution_count": 14, "id": "494880ac", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
companiacentrotipo_movlineasrevenue
0WSWEBAurgi WebVenta1162663,777,996.81
1WSWEBAurgi Asociados GruasVenta274641,183,043.04
2WSWEBAutingoVenta11339725,910.50
3WSWEBAurgi AsociadosVenta5121,059.17
4WSWEBMT WebVenta354371,303.70
\n", "
" ], "text/plain": [ " compania centro tipo_mov lineas revenue\n", "0 WSWEB Aurgi Web Venta 116266 3,777,996.81\n", "1 WSWEB Aurgi Asociados Gruas Venta 27464 1,183,043.04\n", "2 WSWEB Autingo Venta 11339 725,910.50\n", "3 WSWEB Aurgi Asociados Venta 5 121,059.17\n", "4 WSWEB MT Web Venta 3543 71,303.70" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Valores en el Cubo para identificar centros digital. Mirar Dim_NombreCentro y tipos de mov.\n", "sql_cubo_vals = f'''\n", "SELECT\n", " Dim_NombreDimGlobal2 AS compania,\n", " Dim_NombreCentro AS centro,\n", " Dim_NombreTipoMov AS tipo_mov,\n", " COUNT(*) AS lineas,\n", " SUM(cantidad * precioUnitario) AS revenue\n", "FROM `{CUBO}`\n", "WHERE EXTRACT(YEAR FROM fecha) = 2025\n", " AND Dim_NombreDimGlobal2 = 'WSWEB'\n", "GROUP BY compania, centro, tipo_mov\n", "ORDER BY revenue DESC\n", "LIMIT 50\n", "'''\n", "df_cubo_vals = run_sql(sql_cubo_vals)\n", "df_cubo_vals" ] }, { "cell_type": "code", "execution_count": 15, "id": "7c686f5e", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Document/type cols header: ['document_date', 'tipo_de_registro_tpv', 'applies_to_doc__type', 'tipo_operacion_intracomunitar', 'tipo_identificacion', 'tipo_factura', 'external_document_no_', 'n__documento_agrupado', 'bal__account_type', 'service_mgt__document', 'biztalk_document_sent', 'tipo_forma_pago', 'transaction_type', 'tipo_cliente_a_credito', 'tipo_fact__recrificativa_sii']\n" ] } ], "source": [ "# NAV centros digitales 2025: desglose por document_type y location\n", "# Tambien ver campos posibles de document_type en el header\n", "header_fields = [f['name'] for f in tbl_meta['anjana_sales_invoice_header']['fields']]\n", "doctype_cands = [n for n in header_fields if 'document' in n.lower() or 'type' in n.lower() or 'tipo' in n.lower()]\n", "print('Document/type cols header:', doctype_cands[:20])" ] }, { "cell_type": "code", "execution_count": 16, "id": "3c0e4fc0", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " 3423 anjana_sales_cr_memo_header\n", " 3420 anjana_sales_cr_memo_line\n", " 3422 anjana_sales_header\n", " 3452 anjana_sales_invoice_header\n", " 3437 anjana_sales_invoice_line\n", " 3438 anjana_sales_shipment_header\n", " 3447 anjana_sales_shipment_line\n", " 3456 sales_price\n" ] } ], "source": [ "# Buscar tablas complementarias: credit_memo / abono en mssql2022_dbo\n", "nav_tables = [t for t in db6['tables'] if t.get('schema') == 'mssql2022_dbo' and \n", " any(k in t['name'].lower() for k in ['invoice', 'credit', 'memo', 'abono', 'sales', 'return'])]\n", "for t in nav_tables[:30]:\n", " print(f\" {t['id']:5} {t['name']}\")" ] }, { "cell_type": "code", "execution_count": 17, "id": "a839b6f0", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "=== CUBO WSWEB Venta 2025 (por mes y centro) ===\n", " mes centro tipos_ticket lineas revenue\n", "2025-01 Aurgi Web 1 7194 225,546.19\n", "2025-01 Autingo 1 817 37,279.39\n", "2025-01 MT Web 1 841 17,140.52\n", "2025-02 Aurgi Web 1 6639 199,168.29\n", "2025-02 Autingo 1 651 32,029.22\n", "2025-02 MT Web 1 282 4,990.85\n", "2025-03 Aurgi Web 1 10983 291,552.93\n", "2025-03 Autingo 1 1066 44,017.23\n", "2025-03 MT Web 1 265 4,913.40\n", "2025-04 Aurgi Asociados 1 1 165.12\n", "2025-04 Aurgi Web 1 9272 269,613.69\n", "2025-04 Autingo 1 762 36,524.61\n", "2025-04 MT Web 1 187 4,311.97\n", "2025-05 Aurgi Web 1 14096 416,243.31\n", "2025-05 Autingo 1 1025 63,574.06\n", "2025-05 MT Web 1 195 3,167.68\n", "2025-06 Aurgi Web 1 14319 538,660.24\n", "2025-06 Autingo 1 1340 106,750.38\n", "2025-06 MT Web 1 208 4,092.61\n", "2025-07 Aurgi Asociados 1 1 768.60\n", "2025-07 Aurgi Web 1 14253 474,104.39\n", "2025-07 Autingo 1 1647 120,074.72\n", "2025-07 MT Web 1 192 3,933.84\n", "2025-08 Aurgi Web 1 12914 406,695.78\n", "2025-08 Autingo 1 1309 98,284.35\n", "2025-08 MT Web 1 211 3,970.94\n", "2025-09 Aurgi Asociados 1 1 125.45\n", "2025-09 Aurgi Web 1 11336 362,341.53\n", "2025-09 Autingo 1 936 66,635.28\n", "2025-09 MT Web 1 181 3,756.54\n", "2025-10 Aurgi Web 1 6787 242,111.20\n", "2025-10 Autingo 1 914 62,344.22\n", "2025-10 MT Web 1 235 5,063.87\n", "2025-11 Aurgi Asociados Gruas 1 12 478.67\n", "2025-11 Aurgi Web 1 6099 245,523.66\n", "2025-11 Autingo 1 470 32,059.47\n", "2025-11 MT Web 1 291 6,050.80\n", "2025-12 Aurgi Asociados 1 2 120,000.00\n", "2025-12 Aurgi Asociados Gruas 1 27452 1,182,564.37\n", "2025-12 Aurgi Web 1 2374 106,435.60\n", "2025-12 Autingo 1 402 26,337.57\n", "2025-12 MT Web 1 455 9,910.68\n", "\n", "=== NAV directo centros digital 2025 (por mes y centro) ===\n", " mes centro facturas lineas revenue_bruto revenue_neto\n", "2025-01 18 13826 14107 545,817.10 451,092.37\n", "2025-01 19 556 1119 41,481.82 34,281.91\n", "2025-01 405 1703 1930 151,617.87 125,763.47\n", "2025-02 18 12738 13008 481,985.12 398,336.60\n", "2025-02 19 186 375 12,078.54 9,981.99\n", "2025-02 405 1404 1597 118,325.88 97,789.87\n", "2025-02 421 3 12 25,235.29 24,421.18\n", "2025-03 18 21404 21695 706,556.94 584,016.70\n", "2025-03 19 168 349 11,890.82 9,826.97\n", "2025-03 405 2204 2461 155,032.74 128,125.80\n", "2025-03 421 5 21 68,353.88 68,353.88\n", "2025-04 18 17975 18252 652,055.79 538,890.56\n", "2025-04 19 120 247 10,435.50 8,624.14\n", "2025-04 405 1584 1778 134,098.20 110,824.51\n", "2025-04 421 11 80 164,730.03 164,730.03\n", "2025-04 422 2 2 399.60 330.24\n", "2025-05 18 27494 27886 1,018,084.82 843,011.44\n", "2025-05 19 130 260 7,666.28 6,335.59\n", "2025-05 405 2105 2328 205,038.60 169,453.97\n", "2025-05 421 12 51 86,491.73 86,491.73\n", "2025-06 18 28022 28376 1,322,181.29 1,095,747.50\n", "2025-06 19 136 276 9,904.60 8,185.45\n", "2025-06 405 2764 2988 316,731.91 261,763.09\n", "2025-06 421 10 48 116,732.66 116,732.66\n", "2025-07 18 27610 28193 1,178,749.89 979,604.31\n", "2025-07 19 126 255 9,520.20 7,867.80\n", "2025-07 405 3372 3621 354,553.22 293,149.99\n", "2025-07 421 16 60 135,986.92 135,986.92\n", "2025-07 422 2 2 1,860.00 1,537.20\n", "2025-08 18 25115 25495 986,518.81 815,692.95\n", "2025-08 19 138 280 9,610.10 7,942.05\n", "2025-08 405 2666 2829 271,704.71 224,551.24\n", "2025-08 421 9 45 61,575.89 61,575.89\n", "2025-09 18 22176 22456 881,738.19 729,572.09\n", "2025-09 19 120 241 9,091.08 7,513.22\n", "2025-09 405 1946 2109 205,127.52 169,527.47\n", "2025-09 421 18 65 125,276.25 125,276.25\n", "2025-09 422 2 2 303.60 250.90\n", "2025-10 18 13110 13362 586,518.09 484,839.92\n", "2025-10 19 156 313 12,255.24 10,128.06\n", "2025-10 405 1924 2140 208,108.28 171,990.83\n", "2025-10 421 14 60 112,173.57 112,173.57\n", "2025-11 18 11832 12018 594,465.75 491,294.12\n", "2025-11 19 190 386 14,643.46 12,101.80\n", "2025-11 405 1028 1191 123,296.19 101,897.57\n", "2025-11 421 49 109 131,830.71 131,629.65\n", "2025-12 18 4376 4560 257,334.81 212,672.73\n", "2025-12 19 302 606 23,984.90 19,821.76\n", "2025-12 405 866 998 93,023.48 76,879.07\n", "2025-12 421 55138 55187 2,974,888.00 2,476,230.66\n", "2025-12 422 2 2 145,200.00 120,000.00\n" ] } ], "source": [ "# --- Comparativa mensual 2025: Cubo vs NAV (centros digital 18/19/405/421/422) ---\n", "# Incluye: revenue, nº facturas/tickets, nº lineas\n", "\n", "CENTROS_DIGITAL = \"('18','19','405','421','422')\"\n", "CENTROS_DIGITAL_NOMBRES = \"('Aurgi Web','MT Web','Autingo','Aurgi Asociados Gruas','Aurgi Asociados')\"\n", "\n", "sql_cubo_mes = f'''\n", "SELECT\n", " FORMAT_DATE('%Y-%m', fecha) AS mes,\n", " Dim_NombreCentro AS centro,\n", " COUNT(DISTINCT Dim_NombreTipoTicket) AS tipos_ticket,\n", " COUNT(*) AS lineas,\n", " SUM(cantidad * precioUnitario) AS revenue\n", "FROM `{CUBO}`\n", "WHERE Dim_NombreDimGlobal2 = 'WSWEB'\n", " AND Dim_NombreTipoMov = 'Venta'\n", " AND EXTRACT(YEAR FROM fecha) = 2025\n", "GROUP BY mes, centro\n", "ORDER BY mes, centro\n", "'''\n", "df_cubo_mes = run_sql(sql_cubo_mes)\n", "\n", "sql_nav_mes = f'''\n", "SELECT\n", " FORMAT_DATE('%Y-%m', h.posting_date) AS mes,\n", " h.location_code AS centro,\n", " COUNT(DISTINCT h.no_) AS facturas,\n", " COUNT(*) AS lineas,\n", " SUM(l.line_amount) AS revenue_bruto,\n", " SUM(l.amount) AS revenue_neto\n", "FROM `{NAV_LINE}` l\n", "JOIN `{NAV_HEADER}` h ON l.document_no_ = h.no_\n", "WHERE h.location_code IN {CENTROS_DIGITAL}\n", " AND EXTRACT(YEAR FROM h.posting_date) = 2025\n", "GROUP BY mes, centro\n", "ORDER BY mes, centro\n", "'''\n", "df_nav_mes = run_sql(sql_nav_mes)\n", "\n", "print('=== CUBO WSWEB Venta 2025 (por mes y centro) ===')\n", "print(df_cubo_mes.to_string(index=False))\n", "print()\n", "print('=== NAV directo centros digital 2025 (por mes y centro) ===')\n", "print(df_nav_mes.to_string(index=False))" ] }, { "cell_type": "code", "execution_count": 18, "id": "dab0247e", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "=== NAV tipos de registro (centros digital 2025) ===\n", " tipo_de_registro_tpv tipo_factura transaction_type facturas revenue\n", " 4 306634 14,670,280.59\n", " 0 F1 231 1,201,985.28\n" ] } ], "source": [ "# Que tipos de registros tiene NAV para centros digitales 2025\n", "sql_nav_types = f'''\n", "SELECT\n", " h.tipo_de_registro_tpv,\n", " h.tipo_factura,\n", " h.transaction_type,\n", " COUNT(DISTINCT h.no_) AS facturas,\n", " SUM(l.line_amount) AS revenue\n", "FROM `{NAV_LINE}` l\n", "JOIN `{NAV_HEADER}` h ON l.document_no_ = h.no_\n", "WHERE h.location_code IN {CENTROS_DIGITAL}\n", " AND EXTRACT(YEAR FROM h.posting_date) = 2025\n", "GROUP BY h.tipo_de_registro_tpv, h.tipo_factura, h.transaction_type\n", "ORDER BY facturas DESC\n", "'''\n", "df_nav_types = run_sql(sql_nav_types)\n", "print('=== NAV tipos de registro (centros digital 2025) ===')\n", "print(df_nav_types.to_string(index=False))" ] }, { "cell_type": "code", "execution_count": 19, "id": "01872457", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "supply_orders — campos posibles canal:\n", " marketplace_offer_id\n", " tpv_orders_order_id\n", " tpv_orders_orderitem_id\n", " eci_mk_id\n", " eci_sell_price\n", " eci_status\n", " source_timestamp\n", " official_service\n", " official_service_from_provider\n", "\n", "logistic_orders — campos posibles canal:\n", " source_timestamp\n", "\n", "Tablas psql_dcpublic marketplace/tpv/orders:\n", " 1565 channels\n", " 1534 logistic_orders\n", " 2704 ralarsa_orders\n", " 1530 supply_orders\n", " 2705 tpv_appointment_appointment\n", " 1659 tpv_authorization_instanceaccesstoken\n", " 1653 tpv_authorization_tpvclock\n", " 1654 tpv_authorization_tpvuser\n", " 1655 tpv_authorization_tpvuser_centers\n", " 1657 tpv_authorization_tpvuser_groups\n", " 1858 tpv_authorization_tpvuser_user_permissions\n", " 3826 tpv_auto_billing_autobillingrequest\n", " 3827 tpv_auto_billing_autobillingrequestlog\n", " 1658 tpv_cashreg_cashmovement\n", " 1656 tpv_centers_shift\n", " 1888 tpv_commissions_commission\n", " 1886 tpv_commissions_commissionconfiguration\n", " 1884 tpv_commissions_productcommissionreduction\n", " 1527 tpv_companies\n", " 2560 tpv_customer_history_action\n" ] } ], "source": [ "# Buscar campos de canal/marketplace en supply_orders y logistic_orders\n", "so_fields = [f['name'] for f in tbl_meta['supply_orders']['fields']]\n", "lo_fields = [f['name'] for f in tbl_meta['logistic_orders']['fields']]\n", "print('supply_orders — campos posibles canal:')\n", "for f in so_fields:\n", " if any(k in f.lower() for k in ['channel','canal','market','source','platform','origin','referer','tpv','official','amazon','eci']):\n", " print(f' {f}')\n", "print()\n", "print('logistic_orders — campos posibles canal:')\n", "for f in lo_fields:\n", " if any(k in f.lower() for k in ['channel','canal','market','source','platform','origin','referer','tpv']):\n", " print(f' {f}')\n", "print()\n", "# Tablas complementarias: tpv_orders, marketplace\n", "mkt_tables = [t for t in db6['tables'] if t.get('schema') == 'psql_dcpublic' and \n", " any(k in t['name'].lower() for k in ['tpv','marketplace','channel','offer','order'])]\n", "print(f'Tablas psql_dcpublic marketplace/tpv/orders:')\n", "for t in mkt_tables[:20]:\n", " print(f\" {t['id']:5} {t['name']}\")" ] }, { "cell_type": "code", "execution_count": 20, "id": "1c302199", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "=== channels fields ===\n", " id [Integer]\n", " company_id [Integer]\n", " name [Text]\n", " slug [Text]\n", " domain [Text]\n", " status [Integer]\n", " nav_id [Integer]\n", " channel_type [Integer]\n", " external [Boolean]\n", " starting_date [Date]\n", " ending_date [Date]\n", " created_at [DateTimeWithLocalTZ]\n", " updated_at [DateTimeWithLocalTZ]\n", " mirakl_code [Text]\n", " tag_config [JSON]\n", " datastream_metadata [Dictionary]\n", " source_timestamp [Integer]\n", " uuid [Text]\n", " digital_center_id [Integer]\n", "\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "=== channels data ===\n", " id company_id name slug domain status nav_id channel_type external starting_date ending_date created_at updated_at mirakl_code tag_config datastream_metadata digital_center_id\n", " 1 1 aurgi.com aurgi-com None None None None None None None 2020-05-07T23:57:45.785733+02:00 2023-10-03T17:45:54.377289+02:00 INIT {\"all_included_price_tag\":\"aurgi_all_included_price\",\"brand_tag\":\"aurgi\",\"economic_tag\":\"racing\",\"included_in_set_prices\":\"true\",\"mc2p_tag\":\"aurgi\",\"nav_name_tag\":\"Prestashop\",\"product_price_tag\":\"aurgi_price\",\"promo_price_tag\":\"aurgi\",\"sale_code_tag\":\"471\",\"sale_price_slug_tag\":\"aurgi-com\",\"set_other_prices_tags\":[\"aurgi_price_to_sort\",\"aurgi_all_included_price\"],\"set_simple_prices_tags\":[\"aurgi_price\"],\"slug_normal_tag\":\"aurgi\",\"slug_other_tag\":\"aurgi_tag\",\"stock_tag\":\"aurgi\"} {'uuid': '8cefc67c-1aa9-4447-a53c-91cb00000000', 'source_timestamp': 1696802998957} None\n", " 2 2 motortown.es motortown-es None None None None None None None 2020-05-07T23:57:45.801079+02:00 2026-03-17T14:03:40.388276+01:00 {\"all_included_price_tag\":\"mt_all_included_price\",\"brand_tag\":\"motortown\",\"economic_tag\":\"economico\",\"included_in_set_prices\":\"true\",\"mc2p_tag\":\"mt\",\"nav_name_tag\":\"WebMT\",\"product_price_tag\":\"mt_price\",\"promo_price_tag\":\"motortown\",\"sale_code_tag\":\"472\",\"sale_price_slug_tag\":\"motortown-es\",\"set_other_prices_tags\":[\"motortown_price_to_sort\",\"mt_all_included_price\"],\"set_simple_prices_tags\":[\"mt_price\"],\"slug_normal_tag\":\"motortown\",\"slug_other_tag\":\"motortown_tag\",\"stock_tag\":\"motortown\",\"tpv_index\":\"TPV_MT_IDX\"} {'uuid': 'a947ab61-5cb5-416f-89ab-a8d700010010', 'source_timestamp': 1773752620397} None\n", " 3 3 autingo.es autingo-es None None None None None None None 2020-05-07T23:57:45.818754+02:00 2024-11-06T11:34:08.699318+01:00 {\"bank_account_tag\":true,\"exclude_for_delete\":true,\"mc2p_tag\":\"autingo\",\"product_price_tag\":\"autingo_price\",\"sale_price_slug_tag\":\"autingo-es\",\"set_other_prices_tags\":[],\"set_simple_prices_tags\":[\"autingo_price\"],\"slug_normal_tag\":\"autingo\",\"stock_tag\":\"autingo\"} {'uuid': '5de8da99-c6da-497a-b088-5bd710100111', 'source_timestamp': 1730889248706} None\n", " 4 1 Centros Aurgi centros-aurgi None None None None None None None 2020-12-15T15:39:36.135323+01:00 2025-03-25T15:54:20.349252+01:00 {\"all_included_price_tag\":\"aurgi_all_included_price\",\"brand_tag\":\"aurgi\",\"economic_tag\":\"racing\",\"included_in_set_prices\":\"true\",\"mc2p_tag\":\"aurgi\",\"nav_name_tag\":\"Producto Aurgi\",\"order_associate_tag\":\"true\",\"product_price_tag\":\"aurgi_price\",\"promo_price_tag\":\"aurgi\",\"sale_price_slug_tag\":\"aurgi-com\",\"set_other_prices_tags\":[\"aurgi_price_to_sort\",\"aurgi_all_included_price\"],\"set_simple_prices_tags\":[\"aurgi_price\"],\"slug_normal_tag\":\"aurgi\",\"slug_other_tag\":\"aurgi_tag\",\"stock_tag\":\"aurgi\"} {'uuid': 'b549922b-283c-4d12-82bb-632911110011', 'source_timestamp': 1742914460367} None\n", " 5 2 Centros MT centros-mt None None None None None None None 2020-12-15T15:39:36.161249+01:00 2025-07-08T18:25:56.829215+02:00 {\"all_included_price_tag\":\"mt_all_included_price\",\"brand_tag\":\"motortown\",\"economic_tag\":\"economico\",\"included_in_set_prices\":\"true\",\"mc2p_tag\":\"mt\",\"nav_name_tag\":\"Producto MT\",\"product_price_tag\":\"mt_price\",\"promo_price_tag\":\"motortown\",\"sale_price_slug_tag\":\"motortown-es\",\"set_other_prices_tags\":[\"motortown_price_to_sort\",\"mt_all_included_price\"],\"set_simple_prices_tags\":[\"mt_price\"],\"slug_normal_tag\":\"motortown\",\"slug_other_tag\":\"motortown_tag\",\"stock_tag\":\"motortown\"} {'uuid': '71c2dcc4-f317-434d-8ca6-bd8311001100', 'source_timestamp': 1751991956843} None\n", " 6 5 Eci Marketplace eci-marketplace None None None None None None None 2022-07-29T18:15:53.419166+02:00 2023-11-13T15:41:18.781688+01:00 NaN {\"product_price_tag\":\"eci_internal_price\",\"slug_normal_tag\":\"aurgi\",\"slug_other_tag\":\"aurgi_tag\"} {'uuid': '0a6cba7f-aea8-4e5e-ac82-3d3811100100', 'source_timestamp': 1699886478784} None\n", " 7 5 NaN eci-internal-channel None None None None None None None 2022-11-21T11:40:03.968508+01:00 2023-11-13T15:40:28.875452+01:00 NaN {\"product_price_tag\":\"eci_internal_price\",\"slug_normal_tag\":\"aurgi\",\"slug_other_tag\":\"aurgi_tag\"} {'uuid': '1038881e-1a13-49ed-9aef-f23500110010', 'source_timestamp': 1699886428878} None\n", " 8 6 Eci Marketplace eci-marketplace None None None None None None None 2022-11-21T13:15:24.79686+01:00 2023-11-13T15:41:25.976548+01:00 NaN {\"product_price_tag\":\"eci_internal_price\",\"slug_normal_tag\":\"aurgi\",\"slug_other_tag\":\"aurgi_tag\"} {'uuid': 'a72f9e13-0947-4c45-b1cc-170411111110', 'source_timestamp': 1699886485980} None\n", " 10 1 Talleres Digitales talleres-digitales None None None None None None None 2023-11-06T12:16:20.072201+01:00 2024-04-22T12:34:27.691303+02:00 {\"all_included_price_tag\":\"digital_garage_all_included_price\",\"brand_tag\":\"aurgi\",\"economic_tag\":\"racing\",\"exclude_for_delete\":true,\"included_in_set_prices\":\"true\",\"mc2p_tag\":\"aurgi\",\"product_price_tag\":\"digital_garage_price\",\"promo_price_tag\":\"aurgi\",\"sale_price_slug_tag\":\"talleres-digitales\",\"set_other_prices_tags\":[],\"set_simple_prices_tags\":[\"digital_garage_price\"],\"slug_normal_tag\":\"aurgi\",\"slug_other_tag\":\"aurgi_tag\",\"stock_tag\":\"aurgi\",\"use_own_channel_automatic_order\":true} {'uuid': 'b4c39f76-a4df-4653-af17-6a4511111110', 'source_timestamp': 1713782067697} None\n", " 11 1 Amazon amazon None None None None None None None 2023-11-20T15:37:28.276689+01:00 2024-03-12T16:36:34.826712+01:00 {\"exclude_for_delete\":true,\"payment_tag\":\"AMAZON\",\"product_price_tag\":\"amazon-price\",\"set_other_prices_tags\":[],\"set_simple_prices_tags\":[\"amazon-price\"],\"shopping_feed_tag\":66,\"skip_mk_offer_tag\":\"true\",\"slug_normal_tag\":\"aurgi\",\"slug_other_tag\":\"aurgi_tag\"} {'uuid': '4e0886a4-7296-45c3-8c76-016001001011', 'source_timestamp': 1710257794831} None\n", " 12 2 canarias centros-canarias None None None None None None None 2023-11-23T15:17:28.028796+01:00 2023-11-23T15:17:39.873841+01:00 NaN {\"all_included_price_tag\":\"canarias_all_included_price\",\"brand_tag\":\"motortown\",\"economic_tag\":\"economico\",\"encargo_tax_tag\":1.07,\"included_in_set_prices\":\"true\",\"mc2p_tag\":\"mt\",\"nav_name_tag\":\"Producto MT Canarias\",\"product_price_tag\":\"canarias_price\",\"promo_price_tag\":\"motortown\",\"sale_code_tag\":\"478\",\"sale_price_slug_tag\":\"centros-canarias\",\"set_other_prices_tags\":[\"canarias_all_included_price\"],\"set_simple_prices_tags\":[\"canarias_price\"],\"slug_normal_tag\":\"motortown\",\"slug_other_tag\":\"motortown_tag\",\"stock_tag\":\"motortown\"} {'uuid': 'f099ca13-2277-416c-b5ca-a6c510010011', 'source_timestamp': 1700749059881} None\n", " 13 1 Miravia miravia None None None None None None None 2023-11-30T18:04:57.917942+01:00 2024-03-12T16:37:29.88589+01:00 {\"exclude_for_delete\":true,\"payment_tag\":\"MIRAVIA\",\"product_price_tag\":\"miravia-price\",\"set_other_prices_tags\":[],\"set_simple_prices_tags\":[\"miravia-price\"],\"shopping_feed_tag\":46619,\"skip_mk_offer_tag\":\"true\",\"slug_normal_tag\":\"aurgi\",\"slug_other_tag\":\"aurgi_tag\"} {'uuid': '8a5ced8f-1e5c-49f8-a882-7be511101011', 'source_timestamp': 1710257849890} None\n", " 14 1 Aliexpress aliexpress None None None None None None None 2024-07-29T16:31:25.337518+02:00 2024-07-29T17:18:24.465507+02:00 {\"exclude_for_delete\":true,\"payment_tag\":\"AE\",\"product_price_tag\":\"aliexpress-price\",\"set_other_prices_tags\":[],\"set_simple_prices_tags\":[\"aliexpress-price\"],\"shopping_feed_tag\":34210,\"skip_mk_offer_tag\":\"true\",\"slug_normal_tag\":\"aurgi\",\"slug_other_tag\":\"aurgi_tag\"} {'uuid': 'dbc99c04-6620-4528-92f4-4bdd00000010', 'source_timestamp': 1722266304470} None\n", " 15 1 Tiktok Shop tiktok-shop None None None None None None None 2024-08-30T11:16:50.636998+02:00 2025-11-06T17:25:06.687578+01:00 {\"exclude_for_delete\":true,\"payment_tag\":\"TIKTOK\",\"product_price_tag\":\"tiktok_price\",\"set_other_prices_tags\":[],\"set_simple_prices_tags\":[\"tiktok_price\"],\"shopping_feed_tag\":51756,\"skip_mk_offer_tag\":\"true\",\"slug_normal_tag\":\"aurgi\",\"slug_other_tag\":\"aurgi_tag\"} {'uuid': '8836f949-5be3-426f-85c0-595111100011', 'source_timestamp': 1762446306694} None\n" ] } ], "source": [ "# Ver tabla channels y buscar tpv_orders para entender canal\n", "channels_meta = client.request(\"GET\", \"/api/table/1565/query_metadata\")\n", "print('=== channels fields ===')\n", "for f in channels_meta['fields']:\n", " print(f\" {f['name']:40} [{f['base_type'].replace('type/','')}]\")\n", "print()\n", "\n", "# Lista de todos los canales\n", "df_ch = run_sql('SELECT * FROM `psql_dcpublic.channels` ORDER BY id')\n", "print('=== channels data ===')\n", "print(df_ch.to_string(index=False))" ] }, { "cell_type": "code", "execution_count": 21, "id": "886663ef", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "=== card 7750: SO↔NAV — Desglose por canal ===\n", "type: None, database: 6\n" ] }, { "ename": "NameError", "evalue": "name 'json' is not defined", "output_type": "error", "traceback": [ "\u001b[31m---------------------------------------------------------------------------\u001b[39m", "\u001b[31mNameError\u001b[39m Traceback (most recent call last)", "\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[21]\u001b[39m\u001b[32m, line 11\u001b[39m\n\u001b[32m 7\u001b[39m print(f'type: {dq.get(\u001b[33m\"type\"\u001b[39m)}, database: {dq.get(\u001b[33m\"database\"\u001b[39m)}')\n\u001b[32m 8\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m dq.get(\u001b[33m'type'\u001b[39m) == \u001b[33m'native'\u001b[39m:\n\u001b[32m 9\u001b[39m print(dq[\u001b[33m'native'\u001b[39m][\u001b[33m'query'\u001b[39m][:\u001b[32m3000\u001b[39m])\n\u001b[32m 10\u001b[39m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[32m---> \u001b[39m\u001b[32m11\u001b[39m print(json.dumps(dq.get(\u001b[33m'query'\u001b[39m), indent=\u001b[32m2\u001b[39m)[:\u001b[32m2000\u001b[39m])\n\u001b[32m 12\u001b[39m print()\n", "\u001b[31mNameError\u001b[39m: name 'json' is not defined" ] } ], "source": [ "# Leer la query de la card 7750 del doc 2 (Desglose por canal)\n", "# y la 7751 (Revenue semanal web por canal)\n", "for cid in [7750, 7751]:\n", " c = client.request('GET', f'/api/card/{cid}')\n", " dq = c.get('dataset_query') or {}\n", " print(f'=== card {cid}: {c[\"name\"]} ===')\n", " print(f'type: {dq.get(\"type\")}, database: {dq.get(\"database\")}')\n", " if dq.get('type') == 'native':\n", " print(dq['native']['query'][:3000])\n", " else:\n", " print(json.dumps(dq.get('query'), indent=2)[:2000])\n", " print()" ] }, { "cell_type": "code", "execution_count": 22, "id": "15f185cb", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "=== card 7749: SO↔NAV — Evolución mensual por origen ===\n", "type: None, database: 6\n", "--- MBQL query ---\n", "null\n", "\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "=== card 7750: SO↔NAV — Desglose por canal ===\n", "type: None, database: 6\n", "--- MBQL query ---\n", "null\n", "\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "=== card 7751: SO↔NAV — Revenue semanal web por canal ===\n", "type: None, database: 6\n", "--- MBQL query ---\n", "null\n", "\n" ] } ], "source": [ "import json as _json\n", "# Leer la query de las cards del doc 2 (las 3)\n", "for cid in [7749, 7750, 7751]:\n", " c = client.request('GET', f'/api/card/{cid}')\n", " dq = c.get('dataset_query') or {}\n", " print(f'=== card {cid}: {c[\"name\"]} ===')\n", " print(f'type: {dq.get(\"type\")}, database: {dq.get(\"database\")}')\n", " native = dq.get('native') or {}\n", " if native.get('query'):\n", " print('--- NATIVE SQL ---')\n", " print(native['query'][:3000])\n", " else:\n", " print('--- MBQL query ---')\n", " print(_json.dumps(dq.get('query'), indent=2, default=str)[:2500])\n", " print()" ] }, { "cell_type": "code", "execution_count": 23, "id": "103eb0ca", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "keys: ['cache_invalidated_at', 'description', 'archived', 'view_count', 'collection_position', 'source_card_id', 'table_id', 'can_run_adhoc_query', 'result_metadata', 'embedding_type', 'dependency_analysis_version', 'creator', 'initially_published_at', 'can_write', 'card_schema', 'database_id', 'enable_embedding', 'collection_id', 'query_type', 'name', 'last_query_start', 'is_remote_synced', 'dashboard_count', 'document_id', 'last_used_at', 'dashboard', 'type', 'average_query_time', 'creator_id', 'can_restore', 'moderation_reviews', 'updated_at', 'made_public_by_id', 'embedding_params', 'cache_ttl', 'dataset_query', 'id', 'legacy_query', 'parameter_mappings', 'can_manage_db', 'display', 'archived_directly', 'entity_id', 'collection_preview', 'last-edit-info', 'visualization_settings', 'collection', 'metabase_version', 'parameters', 'dashboard_id', 'created_at', 'parameter_usage_count', 'public_uuid', 'can_delete']\n", "\n", "dataset_query keys: ['lib/type', 'database', 'stages']\n", "\n", "{\n", " \"lib/type\": \"mbql/query\",\n", " \"database\": 6,\n", " \"stages\": [\n", " {\n", " \"lib/type\": \"mbql.stage/native\",\n", " \"native\": \"\\nWITH supply_full AS (\\n SELECT \\n so.id as supply_order_id,\\n so.service_request_id,\\n so.product_id,\\n so.quantity,\\n so.sale_price,\\n so.purchase_price,\\n so.status as so_status,\\n so.supply_method,\\n so.shipped,\\n so.created_at as so_created_at,\\n CASE \\n WHEN so.service_request_id IS NOT NULL THEN 'Web'\\n WHEN so.tpv_orders_order_id IS NOT NULL THEN 'Centro'\\n ELSE 'Sin origen'\\n END as origen,\\n ch.name as canal_nombre,\\n sr.shipping_method,\\n COALESCE(so.tpv_orders_order_id, pj.order_id, inv_lo.order_id) as order_id,\\n COALESCE(inv_direct.nav_id, inv_precaweb.nav_id, lo.invoice_number) as factura_nav,\\n p.description as producto,\\n COALESCE(SPLIT(ec.h_path, '#')[SAFE_OFFSET(0)], '(sin cat)') as familia_ecom,\\n COALESCE(SPLIT(ec.h_path, '#')[SAFE_OFFSET(1)], '') as subfamilia_ecom,\\n ec.name as ecom_cat_leaf,\\n sih.posting_date as fecha_factura,\\n sih.location_code as centro_nav,\\n sih.payment_method_code as metodo_pago\\n FROM `autingo-159109.psql_dcpublic.supply_orders` so\\n LEFT JOIN `autingo-159109.psql_dcpublic.service_requests` sr ON so.service_request_id = sr.id\\n LEFT JOIN `autingo-159109.psql_dcpublic.channels` ch ON sr.channel_id = ch.id\\n LEFT JOIN `autingo-159109.psql_dcpublic.tpv_precawebs_servicerequestjob` pj\\n ON CAST(so.service_request_id AS STRING) = pj.service_request_id\\n LEFT JOIN `autingo-159109.psql_dcpublic.tpv_orders_invoice` inv_direct\\n ON so.tpv_orders_order_id = inv_direct.order_id\\n LEFT JOIN `autingo-159109.psql_dcpublic.tpv_orders_invoice` inv_precaweb\\n ON pj.order_id = inv_precaweb.order_id AND so.tpv_orders_order_id IS NULL\\n LEFT JOIN `autingo-159109.psql_dcpublic.logistic_orders` lo ON so.logistic_order_id = lo.id\\n LEFT JOIN `autingo-159109.psql_dcpublic.tpv_orders_invoice` inv_lo\\n ON lo.invoice_number = inv_lo.nav_id AND so.tpv_orders_order_id IS NULL AND pj.order_id IS NULL\\n LEFT JOIN `autingo-159109.psql_dcpublic.products` p ON so.product_id = p.id\\n LEFT JOIN `autingo-159109.psql_dcpublic.ecommerce_categories` ec ON p.ecommerce_category_id = ec.id\\n LEFT JOIN `autingo-159109.mssql2022_dbo.anjana_sales_invoice_header` sih \\n ON COALESCE(inv_direct.nav_id, inv_precaweb.nav_id, lo.invoice_number) = sih.no_ \\n AND sih._fivetran_deleted = FALSE\\n WHERE so.created_at >= '2025-01-01'\\n)\\n\\nSELECT \\n COALESCE(canal_nombre, '(centro directo)') as canal,\\n origen,\\n COUNT(DISTINCT supply_order_id) as supply_orders,\\n COUNT(DISTINCT CASE WHEN factura_nav IS NOT NULL THEN supply_order_id END) as con_factura,\\n ROUND(COUNT(DISTINCT CASE WHEN factura_nav IS NOT NULL THEN supply_order_id END) * 100.0 \\n / NULLIF(COUNT(DISTINCT supply_order_id), 0), 1) as pct_trazable,\\n COUNT(DISTINCT order_id) as orders_tpv,\\n ROUND(SUM(sale_price * quantity), 0) as revenue_so,\\n ROUND(SUM(sale_price * quantity) - SUM(purchase_price * quantity), 0) as margen_so,\\n ROUND((SUM(sale_price * quantity) - SUM(purchase_price * quantity)) * 100.0 \\n / NULLIF(SUM(sale_price * quantity), 0), 1) as pct_margen\\nFROM supply_full\\nGROUP BY 1, 2\\nORDER BY supply_orders DESC\\n\"\n", " }\n", " ]\n", "}\n" ] } ], "source": [ "import json as _json\n", "c = client.request('GET', '/api/card/7750')\n", "# dump todos los keys\n", "print('keys:', list(c.keys()))\n", "print()\n", "print('dataset_query keys:', list(c.get('dataset_query',{}).keys()))\n", "print()\n", "# Probar version v2 formato stages\n", "print(_json.dumps(c.get('dataset_query'), indent=2, default=str)[:4000])" ] }, { "cell_type": "code", "execution_count": 24, "id": "416349bf", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "=== VENTA WEB 2025 POR CANAL (Supply Orders → NAV) ===\n", " canal supply_orders con_factura facturas_distintas revenue_so revenue_trazable\n", " aurgi.com 72843 71219 62651 17,606,179.00 17,016,564.00\n", " Amazon 58134 58116 55152 3,243,771.00 3,241,920.00\n", " Aliexpress 36994 36974 35398 1,181,809.00 1,181,423.00\n", " autingo.es 11398 11358 10553 1,940,942.00 1,936,293.00\n", " Miravia 10495 10487 10000 918,512.00 918,112.00\n", "Eci Marketplace 1207 1207 1152 91,211.00 91,211.00\n", " motortown.es 1150 1031 1355 1,137,556.00 1,034,280.00\n", " Tiktok Shop 23 23 19 349.00 349.00\n", " Centros Aurgi 7 7 7 426,266.00 426,266.00\n" ] } ], "source": [ "# Revenue y conteo facturas por canal (Web solo) — 2025, usando la logica del doc 2\n", "sql_canal = '''\n", "WITH so_web AS (\n", " SELECT\n", " so.id,\n", " so.service_request_id,\n", " so.quantity,\n", " so.sale_price,\n", " so.created_at,\n", " ch.name AS canal,\n", " COALESCE(inv_direct.nav_id, inv_precaweb.nav_id, lo.invoice_number) AS factura_nav\n", " FROM `autingo-159109.psql_dcpublic.supply_orders` so\n", " LEFT JOIN `autingo-159109.psql_dcpublic.service_requests` sr ON so.service_request_id = sr.id\n", " LEFT JOIN `autingo-159109.psql_dcpublic.channels` ch ON sr.channel_id = ch.id\n", " LEFT JOIN `autingo-159109.psql_dcpublic.tpv_precawebs_servicerequestjob` pj\n", " ON CAST(so.service_request_id AS STRING) = pj.service_request_id\n", " LEFT JOIN `autingo-159109.psql_dcpublic.tpv_orders_invoice` inv_direct\n", " ON so.tpv_orders_order_id = inv_direct.order_id\n", " LEFT JOIN `autingo-159109.psql_dcpublic.tpv_orders_invoice` inv_precaweb\n", " ON pj.order_id = inv_precaweb.order_id AND so.tpv_orders_order_id IS NULL\n", " LEFT JOIN `autingo-159109.psql_dcpublic.logistic_orders` lo ON so.logistic_order_id = lo.id\n", " WHERE so.service_request_id IS NOT NULL\n", " AND EXTRACT(YEAR FROM so.created_at) = 2025\n", ")\n", "SELECT\n", " COALESCE(canal, '(sin canal)') AS canal,\n", " COUNT(DISTINCT id) AS supply_orders,\n", " COUNT(DISTINCT CASE WHEN factura_nav IS NOT NULL THEN id END) AS con_factura,\n", " COUNT(DISTINCT factura_nav) AS facturas_distintas,\n", " ROUND(SUM(sale_price * quantity), 0) AS revenue_so,\n", " ROUND(SUM(CASE WHEN factura_nav IS NOT NULL THEN sale_price * quantity END), 0) AS revenue_trazable\n", "FROM so_web\n", "GROUP BY canal\n", "ORDER BY supply_orders DESC\n", "'''\n", "df_canal = run_sql(sql_canal)\n", "print('=== VENTA WEB 2025 POR CANAL (Supply Orders → NAV) ===')\n", "print(df_canal.to_string(index=False))" ] }, { "cell_type": "code", "execution_count": 26, "id": "b0e0660c", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "=== REVENUE POR MES Y CANAL (Supply Orders web 2025) ===\n", "canal Aliexpress Amazon Centros Aurgi Eci Marketplace Miravia Tiktok Shop aurgi.com autingo.es motortown.es\n", "mes \n", "2025-01 68,343.00 166,947.00 0.00 20,871.00 76,124.00 0.00 2,245,281.00 115,703.00 60,239.00\n", "2025-02 33,095.00 183,390.00 0.00 6,074.00 60,312.00 0.00 699,809.00 124,178.00 70,430.00\n", "2025-03 50,465.00 276,958.00 0.00 6,054.00 80,987.00 0.00 929,023.00 111,122.00 101,507.00\n", "2025-04 38,748.00 257,065.00 3,996.00 5,452.00 73,302.00 0.00 863,487.00 86,919.00 97,587.00\n", "2025-05 98,737.00 429,018.00 23,148.00 4,016.00 92,269.00 0.00 819,083.00 220,599.00 65,286.00\n", "2025-06 140,292.00 563,030.00 0.00 5,074.00 127,968.00 0.00 1,128,153.00 304,014.00 101,676.00\n", "2025-07 197,834.00 420,412.00 27,900.00 5,153.00 99,631.00 0.00 2,410,787.00 285,622.00 146,945.00\n", "2025-08 217,799.00 248,708.00 0.00 5,319.00 70,978.00 11.00 1,856,374.00 229,094.00 162,288.00\n", "2025-09 120,499.00 285,104.00 759.00 4,600.00 98,794.00 157.00 1,097,121.00 144,585.00 91,482.00\n", "2025-10 91,763.00 168,277.00 0.00 8,131.00 73,901.00 145.00 947,802.00 139,971.00 67,643.00\n", "2025-11 101,533.00 170,078.00 0.00 7,662.00 49,572.00 36.00 940,761.00 75,873.00 74,999.00\n", "2025-12 22,701.00 74,784.00 370,463.00 12,806.00 14,673.00 0.00 3,668,497.00 103,262.00 97,473.00\n", "\n", "=== FACTURAS DISTINTAS POR MES Y CANAL ===\n", "canal Aliexpress Amazon Centros Aurgi Eci Marketplace Miravia Tiktok Shop aurgi.com autingo.es motortown.es\n", "mes \n", "2025-01 2,112.00 3,312.00 0.00 270.00 852.00 0.00 1,941.00 743.00 72.00\n", "2025-02 1,415.00 3,517.00 0.00 93.00 794.00 0.00 2,059.00 614.00 73.00\n", "2025-03 1,935.00 6,965.00 0.00 84.00 1,072.00 0.00 2,508.00 980.00 91.00\n", "2025-04 1,249.00 6,000.00 1.00 60.00 994.00 0.00 2,569.00 691.00 94.00\n", "2025-05 3,813.00 8,109.00 1.00 65.00 1,029.00 0.00 3,061.00 968.00 89.00\n", "2025-06 3,672.00 8,359.00 0.00 68.00 1,331.00 0.00 4,564.00 1,296.00 186.00\n", "2025-07 6,057.00 5,856.00 1.00 62.00 1,097.00 0.00 6,627.00 1,552.00 207.00\n", "2025-08 6,219.00 4,781.00 0.00 70.00 801.00 1.00 5,338.00 1,237.00 207.00\n", "2025-09 3,974.00 5,359.00 1.00 58.00 867.00 8.00 2,799.00 884.00 97.00\n", "2025-10 2,769.00 2,301.00 0.00 77.00 616.00 7.00 2,629.00 864.00 67.00\n", "2025-11 2,569.00 2,279.00 0.00 97.00 466.00 3.00 2,177.00 447.00 89.00\n", "2025-12 635.00 872.00 3.00 150.00 157.00 0.00 29,681.00 379.00 83.00\n" ] } ], "source": [ "# Evolucion mensual 2025 por CANAL para las 3 fuentes en paralelo (solo revenue y facturas)\n", "sql_mensual_canal = '''\n", "WITH so_web AS (\n", " SELECT\n", " so.id,\n", " FORMAT_DATE('%Y-%m', so.created_at) AS mes,\n", " ch.name AS canal,\n", " so.sale_price * so.quantity AS revenue,\n", " COALESCE(inv_direct.nav_id, inv_precaweb.nav_id, lo.invoice_number) AS factura_nav\n", " FROM `autingo-159109.psql_dcpublic.supply_orders` so\n", " LEFT JOIN `autingo-159109.psql_dcpublic.service_requests` sr ON so.service_request_id = sr.id\n", " LEFT JOIN `autingo-159109.psql_dcpublic.channels` ch ON sr.channel_id = ch.id\n", " LEFT JOIN `autingo-159109.psql_dcpublic.tpv_precawebs_servicerequestjob` pj\n", " ON CAST(so.service_request_id AS STRING) = pj.service_request_id\n", " LEFT JOIN `autingo-159109.psql_dcpublic.tpv_orders_invoice` inv_direct\n", " ON so.tpv_orders_order_id = inv_direct.order_id\n", " LEFT JOIN `autingo-159109.psql_dcpublic.tpv_orders_invoice` inv_precaweb\n", " ON pj.order_id = inv_precaweb.order_id AND so.tpv_orders_order_id IS NULL\n", " LEFT JOIN `autingo-159109.psql_dcpublic.logistic_orders` lo ON so.logistic_order_id = lo.id\n", " WHERE so.service_request_id IS NOT NULL\n", " AND EXTRACT(YEAR FROM so.created_at) = 2025\n", ")\n", "SELECT\n", " mes,\n", " COALESCE(canal, '(sin canal)') AS canal,\n", " COUNT(DISTINCT id) AS supply_orders,\n", " COUNT(DISTINCT factura_nav) AS facturas,\n", " ROUND(SUM(revenue), 0) AS revenue\n", "FROM so_web\n", "GROUP BY mes, canal\n", "ORDER BY mes, revenue DESC\n", "'''\n", "df_mensual_canal = run_sql(sql_mensual_canal)\n", "\n", "# Pivot por canal -> mes\n", "pivot_rev = df_mensual_canal.pivot_table(index='mes', columns='canal', values='revenue', aggfunc='sum').fillna(0)\n", "pivot_fact = df_mensual_canal.pivot_table(index='mes', columns='canal', values='facturas', aggfunc='sum').fillna(0)\n", "\n", "print('=== REVENUE POR MES Y CANAL (Supply Orders web 2025) ===')\n", "print(pivot_rev.to_string())\n", "print()\n", "print('=== FACTURAS DISTINTAS POR MES Y CANAL ===')\n", "print(pivot_fact.to_string())" ] }, { "cell_type": "markdown", "id": "d0617844", "metadata": {}, "source": [ "## 10. Por qué discrepan las fuentes — explicación y resumen\n", "\n", "### Totales 2025 reproducidos\n", "\n", "| Fuente | Revenue | Nº registros | Lógica aplicada |\n", "|---|---|---|---|\n", "| **Cubo_Ventas_Calculado** (WSWEB, Venta) | **5.879.313 €** | 158.617 líneas | `Dim_NombreDimGlobal2='WSWEB' AND Dim_NombreTipoMov='Venta'` |\n", "| **NAV directo** (centros digitales) | **15.872.266 €** | 315.831 líneas / ~295K facturas | `location_code IN ('18','19','405','421','422')` |\n", "| **Supply Orders → NAV** (web, por canal) | **26.499.895 €** | 192.251 SOs / 190K con factura | `service_request_id IS NOT NULL` con `sale_price*quantity` |\n", "\n", "### ¿Por qué 3 cifras tan distintas?\n", "\n", "Las tres miden **cosas distintas aunque suenen igual**:\n", "\n", "1. **Cubo (5.88M€) — \"compañía comercial WSWEB\"**\n", " - Es un datamart pre-agregado que solo cuenta líneas cuya **dimensión comercial = WSWEB**\n", " - Excluye implícitamente devoluciones, ajustes de stock, tickets intraempresa, abonos\n", " - Solo tipo_mov = \"Venta\"\n", " - Los centros físicos (18/19/405/421/422) aparecen aquí, pero solo por la fracción de su actividad que se imputa a la compañía WSWEB\n", "\n", "2. **NAV directo (15.87M€) — \"facturación bruta del almacén\"**\n", " - Todo lo facturado en los almacenes con location_code digital: ventas a cliente final + tickets intraempresa + facturas de servicios + recargos + ajustes\n", " - tipo_de_registro_tpv=4 (ticket TPV) domina con 306K facturas\n", " - **2.7× el Cubo** porque incluye todo lo que sale del almacén, no solo la venta online limpia\n", "\n", "3. **Supply Orders (26.50M€) — \"pedidos web por canal\"**\n", " - Todo pedido web (service_request_id ≠ NULL) independientemente del centro que lo facture\n", " - **Incluye canales NO digital-center**: Amazon 3.24M€, Aliexpress 1.18M€, Miravia 918K€ → se facturan a centros/compañías distintas que no están en (18/19/405/421/422)\n", " - Por eso es mayor que NAV directo\n", "\n", "### Diciembre tiene anomalías importantes\n", "\n", "- **aurgi.com diciembre**: 3.67M€ y 29.681 facturas (vs media ~2-3K facturas/mes) → 10× la media\n", "- **Aurgi Asociados Gruas (421) diciembre en NAV**: 55.138 facturas por 2.97M€ en UN solo mes (todo el resto del año: ~150 facturas total)\n", "- **Centros Aurgi (canal)**: 370K€ concentrado en diciembre\n", "\n", "Investigar: ¿cierre fiscal? ¿reemisión masiva? ¿cambio lógica canal?\n", "\n", "### Desglose canal digital 2025\n", "\n", "| Canal | Supply Orders | Revenue SO | % trazable a NAV |\n", "|---|---|---|---|\n", "| aurgi.com | 72.843 | 17.6M€ | 97.8% |\n", "| Amazon | 58.134 | 3.24M€ | 99.97% |\n", "| Aliexpress | 36.994 | 1.18M€ | 99.95% |\n", "| autingo.es | 11.398 | 1.94M€ | 99.6% |\n", "| Miravia | 10.495 | 918K€ | 99.9% |\n", "| motortown.es | 1.150 | 1.14M€ | 89.7% |\n", "| Eci Marketplace | 1.207 | 91K€ | 100% |\n", "| Centros Aurgi | 7 | 426K€ | 100% |\n", "| Tiktok Shop | 23 | 349€ | 100% |\n", "\n", "### Recomendación de uso\n", "\n", "- **Cubo_Ventas_Calculado (WSWEB, Venta)** → reporting financiero interno \"venta online\" en sentido estricto\n", "- **NAV directo (location_code digital)** → auditoría contable, cierre fiscal\n", "- **Supply Orders por canal** → operativa web, KPIs por marketplace, conciliación ventas-envíos" ] }, { "cell_type": "code", "execution_count": 27, "id": "7f2ef633", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "=== TOP 20 productos aurgi.com DICIEMBRE 2025 ===\n", " producto supply_orders unidades revenue precio_medio\n", " BALIZA V16 GEOLOC Mirovi RET 27706 41,293.00 2,803,090.00 52.09\n", " REVISION PLUS TURISMO 61 61.00 10,020.00 164.26\n", " CUB EP 235/60 R18 107V K125A V 6 20.00 9,845.00 453.52\n", " REVISION TODO INCLUIDO TURISMO 45 45.00 9,221.00 204.91\n", " CUB 205/55 R16 91V K135 VEN 10 28.00 9,103.00 288.60\n", " CUB 205/55 R16 91V SECURADRIVE 22 54.00 9,080.00 149.98\n", " CUB EN 205/50 R17 93W K135 VEN 9 23.00 7,577.00 280.47\n", " CUB 205/55 R16 91V TUR6 8 22.00 7,154.00 294.15\n", " CUB 225/45 R17 91Y K135 VEN 10 26.00 7,045.00 236.31\n", " CUB 225/45 R17 94Y POWERGY 11 26.00 6,939.00 242.31\n", "Baliza V16 IoT Alert Wise con geolocalización MIROVI Compatible DGT 3.0 y Conectividad telefónica tech 75 107.00 6,723.00 51.29\n", " CUB 205/55 R16 91V RP062 20 44.00 6,235.00 132.68\n", " CUB EN 235/55 R19 105W SCO AS 3 10.00 6,233.00 572.90\n", " CAMBIO DE ACEITE MO Y FILTRO ACEITE TURISMO 84 84.00 5,921.00 70.49\n", " REVISION TODO INCLUIDO 4X4/FU/MONOV 24 24.00 5,668.00 236.18\n", " CUB EP 195/65 R15 91V 4E H750 6 18.00 5,206.00 258.40\n", " CUB EP 195/55 R16 91V 4E CEAT 8 24.00 5,206.00 204.42\n", " CUB EP 215/50 R18 92W PWRGY 2 8.00 5,139.00 642.39\n", " CUB EP 225/50 R18 99Y K127 VEN 4 12.00 5,119.00 383.91\n", " REVISION AHORRO TURISMO 41 41.00 4,977.00 121.38\n" ] } ], "source": [ "# Hipotesis: pico aurgi.com diciembre 2025 = balizas V16 (ley obligatoria ene 2026)\n", "# Ver top productos vendidos en aurgi.com en diciembre 2025\n", "sql_dic_top = '''\n", "SELECT\n", " p.description AS producto,\n", " COUNT(DISTINCT so.id) AS supply_orders,\n", " SUM(so.quantity) AS unidades,\n", " ROUND(SUM(so.sale_price * so.quantity), 0) AS revenue,\n", " ROUND(AVG(so.sale_price), 2) AS precio_medio\n", "FROM `autingo-159109.psql_dcpublic.supply_orders` so\n", "LEFT JOIN `autingo-159109.psql_dcpublic.service_requests` sr ON so.service_request_id = sr.id\n", "LEFT JOIN `autingo-159109.psql_dcpublic.channels` ch ON sr.channel_id = ch.id\n", "LEFT JOIN `autingo-159109.psql_dcpublic.products` p ON so.product_id = p.id\n", "WHERE so.service_request_id IS NOT NULL\n", " AND ch.name = 'aurgi.com'\n", " AND EXTRACT(YEAR FROM so.created_at) = 2025\n", " AND EXTRACT(MONTH FROM so.created_at) = 12\n", "GROUP BY p.description\n", "ORDER BY revenue DESC\n", "LIMIT 20\n", "'''\n", "df_dic_top = run_sql(sql_dic_top)\n", "print('=== TOP 20 productos aurgi.com DICIEMBRE 2025 ===')\n", "print(df_dic_top.to_string(index=False))" ] }, { "cell_type": "code", "execution_count": 28, "id": "f3e4daf7", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "=== Pico aurgi.com DIC 2025: balizas vs resto ===\n", " categoria supply_orders unidades revenue\n", " Balizas V16 27793 41,414.00 2,810,591.00\n", "Resto productos 2865 4,191.00 705,417.00\n", "\n", "Balizas V16 = 79.9% del revenue aurgi.com diciembre 2025\n" ] } ], "source": [ "# Desglose del pico diciembre: balizas vs resto\n", "sql_dic_balizas = '''\n", "SELECT\n", " CASE \n", " WHEN UPPER(p.description) LIKE '%BALIZA%' OR UPPER(p.description) LIKE '%V16%' THEN 'Balizas V16'\n", " ELSE 'Resto productos'\n", " END AS categoria,\n", " COUNT(DISTINCT so.id) AS supply_orders,\n", " SUM(so.quantity) AS unidades,\n", " ROUND(SUM(so.sale_price * so.quantity), 0) AS revenue\n", "FROM `autingo-159109.psql_dcpublic.supply_orders` so\n", "LEFT JOIN `autingo-159109.psql_dcpublic.service_requests` sr ON so.service_request_id = sr.id\n", "LEFT JOIN `autingo-159109.psql_dcpublic.channels` ch ON sr.channel_id = ch.id\n", "LEFT JOIN `autingo-159109.psql_dcpublic.products` p ON so.product_id = p.id\n", "WHERE so.service_request_id IS NOT NULL\n", " AND ch.name = 'aurgi.com'\n", " AND EXTRACT(YEAR FROM so.created_at) = 2025\n", " AND EXTRACT(MONTH FROM so.created_at) = 12\n", "GROUP BY categoria\n", "ORDER BY revenue DESC\n", "'''\n", "df_bal = run_sql(sql_dic_balizas)\n", "print('=== Pico aurgi.com DIC 2025: balizas vs resto ===')\n", "print(df_bal.to_string(index=False))\n", "total = df_bal['revenue'].sum()\n", "pct_bal = df_bal.loc[df_bal['categoria']=='Balizas V16','revenue'].iloc[0] / total * 100\n", "print(f'\\nBalizas V16 = {pct_bal:.1f}% del revenue aurgi.com diciembre 2025')" ] }, { "cell_type": "code", "execution_count": 29, "id": "e4d54fc1", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "=== 2025 venta web: por canal y destino de cumplimentacion ===\n", " canal destino supply_orders facturas revenue locations_nav_distintos\n", " Aliexpress 1) Centro digital (web puro) 36994 35398 1,181,809.00 4\n", " Amazon 1) Centro digital (web puro) 58122 55149 3,242,247.00 4\n", " Amazon 3) Sin centro asignado 12 5 1,524.00 1\n", " Centros Aurgi 1) Centro digital (web puro) 7 7 426,266.00 3\n", "Eci Marketplace 1) Centro digital (web puro) 1207 1152 91,211.00 4\n", " Miravia 1) Centro digital (web puro) 10490 10000 918,172.00 4\n", " Miravia 3) Sin centro asignado 5 0 340.00 0\n", " Tiktok Shop 1) Centro digital (web puro) 23 19 349.00 3\n", " aurgi.com 1) Centro digital (web puro) 37144 35349 5,024,463.00 58\n", " aurgi.com 2) Centro fisico (C&C/envio tienda) 35671 30210 12,579,111.00 66\n", " aurgi.com 3) Sin centro asignado 28 15 2,605.00 7\n", " autingo.es 1) Centro digital (web puro) 11398 10553 1,940,942.00 4\n", " motortown.es 2) Centro fisico (C&C/envio tienda) 1150 1355 1,137,556.00 48\n" ] } ], "source": [ "# Trazabilidad completa: SO -> NAV para venta que va a CENTRO (C&C + envio tienda)\n", "# Vamos a desglosar 2025 por \"destino final\"\n", "\n", "sql_destino = '''\n", "WITH so_web AS (\n", " SELECT\n", " so.id,\n", " so.service_request_id,\n", " so.quantity,\n", " so.sale_price,\n", " so.created_at,\n", " ch.name AS canal,\n", " ce.name AS centro_fisico,\n", " ce.nav_id AS nav_id_centro,\n", " ce.is_digital,\n", " sr.shipping_method AS shipping_method_sr,\n", " lo.center_id AS logistic_center_id,\n", " COALESCE(inv_direct.nav_id, inv_precaweb.nav_id, lo.invoice_number) AS factura_nav,\n", " sih.location_code AS nav_location\n", " FROM `autingo-159109.psql_dcpublic.supply_orders` so\n", " LEFT JOIN `autingo-159109.psql_dcpublic.service_requests` sr ON so.service_request_id = sr.id\n", " LEFT JOIN `autingo-159109.psql_dcpublic.channels` ch ON sr.channel_id = ch.id\n", " LEFT JOIN `autingo-159109.psql_dcpublic.logistic_orders` lo ON so.logistic_order_id = lo.id\n", " LEFT JOIN `autingo-159109.psql_dcpublic.centers` ce ON lo.center_id = ce.id\n", " LEFT JOIN `autingo-159109.psql_dcpublic.tpv_precawebs_servicerequestjob` pj\n", " ON CAST(so.service_request_id AS STRING) = pj.service_request_id\n", " LEFT JOIN `autingo-159109.psql_dcpublic.tpv_orders_invoice` inv_direct\n", " ON so.tpv_orders_order_id = inv_direct.order_id\n", " LEFT JOIN `autingo-159109.psql_dcpublic.tpv_orders_invoice` inv_precaweb\n", " ON pj.order_id = inv_precaweb.order_id AND so.tpv_orders_order_id IS NULL\n", " LEFT JOIN `autingo-159109.mssql2022_dbo.anjana_sales_invoice_header` sih\n", " ON COALESCE(inv_direct.nav_id, inv_precaweb.nav_id, lo.invoice_number) = sih.no_\n", " AND sih._fivetran_deleted = FALSE\n", " WHERE so.service_request_id IS NOT NULL\n", " AND EXTRACT(YEAR FROM so.created_at) = 2025\n", ")\n", "SELECT\n", " canal,\n", " CASE\n", " WHEN is_digital = TRUE THEN '1) Centro digital (web puro)'\n", " WHEN is_digital = FALSE THEN '2) Centro fisico (C&C/envio tienda)'\n", " WHEN centro_fisico IS NULL THEN '3) Sin centro asignado'\n", " ELSE '4) Otro'\n", " END AS destino,\n", " COUNT(DISTINCT id) AS supply_orders,\n", " COUNT(DISTINCT factura_nav) AS facturas,\n", " ROUND(SUM(sale_price * quantity), 0) AS revenue,\n", " COUNT(DISTINCT nav_location) AS locations_nav_distintos\n", "FROM so_web\n", "GROUP BY canal, destino\n", "ORDER BY canal, destino\n", "'''\n", "df_dest = run_sql(sql_destino)\n", "print('=== 2025 venta web: por canal y destino de cumplimentacion ===')\n", "print(df_dest.to_string(index=False))" ] }, { "cell_type": "markdown", "id": "0061c6c1", "metadata": {}, "source": [ "## 11. Verificación balizas V16 — CONFIRMADO\n", "\n", "El pico aurgi.com diciembre (3.67M€, 29.681 facturas) es **80% balizas V16**:\n", "\n", "| Categoría | Supply Orders | Unidades | Revenue |\n", "|---|---:|---:|---:|\n", "| **Balizas V16** | 27.793 | 41.414 | **2.810.591 €** |\n", "| Resto productos | 2.865 | 4.191 | 705.417 € |\n", "\n", "Todo por la **ley DGT de balizas V16 obligatoria desde 1 enero 2026** (sustituye triángulos de emergencia). Producto principal: **BALIZA V16 GEOLOC Mirovi RET** (52€ unidad, 41.293 unidades en un mes).\n", "\n", "## 12. La arquitectura real — dónde sacar cada dato\n", "\n", "### 12.1 Hallazgo clave: la venta web no va toda a centros digitales\n", "\n", "Cuando se desglosa por **destino de cumplimentación** (via `supply_orders.logistic_order_id → logistic_orders.center_id → centers`):\n", "\n", "| Canal | A centro digital (web puro) | A centro físico (C&C/envío tienda) | Sin centro |\n", "|---|---:|---:|---:|\n", "| aurgi.com | 5.02M€ (29%) | **12.58M€ (71%)** | 3K€ |\n", "| motortown.es | 0 | 1.14M€ (100%) | 0 |\n", "| Amazon | 3.24M€ (100%) | 0 | 2K€ |\n", "| Aliexpress | 1.18M€ (100%) | 0 | 0 |\n", "| autingo.es | 1.94M€ (100%) | 0 | 0 |\n", "| Miravia | 918K€ (100%) | 0 | 0 |\n", "\n", "**Por eso NAV filtrado por location digital (5.02+3.24+1.18+1.94+0.92+…=13.5M€) queda lejos del total de supply_orders web (26.5M€)**: falta todo el C&C de aurgi.com que se factura en 66 locations físicas distintas.\n", "\n", "### 12.2 Tabla de verdad según pregunta\n", "\n", "| Pregunta de negocio | Fuente canónica | Filtro clave |\n", "|---|---|---|\n", "| ¿Cuánto vendí por canal online? | `psql_dcpublic.supply_orders` + join con `channels` | `service_request_id IS NOT NULL` |\n", "| ¿Qué facturó fiscalmente cada almacén? | `mssql2022_dbo.anjana_sales_invoice_header/line` | `location_code` específico |\n", "| ¿Cuál es el KPI comercial \"venta online\" estricto? | `anjana_bi_datamart.Cubo_Ventas_Calculado` | `Dim_NombreDimGlobal2='WSWEB' AND Dim_NombreTipoMov='Venta'` |\n", "| ¿Un pedido web llegó a facturarse? | Cadena `supply_orders → tpv_orders_invoice → NAV` | factura_nav IS NOT NULL |\n", "| ¿A qué tienda fue el C&C? | `logistic_orders.center_id → centers.nav_id` | `is_digital=FALSE` |\n", "\n", "### 12.3 Propuesta: vista canónica `v_venta_web_unificada`\n", "\n", "Una **view materializada en BigQuery** que consolide todo:\n", "\n", "```sql\n", "CREATE OR REPLACE VIEW `autingo-159109.anjana_bi_datamart.v_venta_web_unificada` AS\n", "SELECT\n", " so.id AS supply_order_id,\n", " so.created_at AS fecha_pedido,\n", " ch.name AS canal,\n", " ch.company_id AS company_id,\n", " so.product_id,\n", " p.description AS producto,\n", " so.quantity,\n", " so.sale_price,\n", " so.sale_price * so.quantity AS revenue,\n", " so.purchase_price,\n", " (so.sale_price - so.purchase_price) * so.quantity AS margen,\n", " ce.id AS centro_id,\n", " ce.name AS centro_nombre,\n", " ce.nav_id AS centro_nav_id,\n", " ce.is_digital AS centro_es_digital,\n", " CASE\n", " WHEN ce.is_digital THEN 'entrega_web'\n", " WHEN ce.is_digital = FALSE THEN 'click_and_collect'\n", " ELSE 'sin_centro'\n", " END AS modo_cumplimentacion,\n", " sr.shipping_method AS shipping_method,\n", " COALESCE(inv_direct.nav_id, inv_precaweb.nav_id, lo.invoice_number) AS factura_nav,\n", " sih.posting_date AS fecha_factura_nav,\n", " sih.location_code AS nav_location_code,\n", " sih.amount_including_vat AS importe_factura_nav\n", "FROM `autingo-159109.psql_dcpublic.supply_orders` so\n", "LEFT JOIN `autingo-159109.psql_dcpublic.service_requests` sr ON so.service_request_id = sr.id\n", "LEFT JOIN `autingo-159109.psql_dcpublic.channels` ch ON sr.channel_id = ch.id\n", "LEFT JOIN `autingo-159109.psql_dcpublic.logistic_orders` lo ON so.logistic_order_id = lo.id\n", "LEFT JOIN `autingo-159109.psql_dcpublic.centers` ce ON lo.center_id = ce.id\n", "LEFT JOIN `autingo-159109.psql_dcpublic.products` p ON so.product_id = p.id\n", "LEFT JOIN `autingo-159109.psql_dcpublic.tpv_precawebs_servicerequestjob` pj\n", " ON CAST(so.service_request_id AS STRING) = pj.service_request_id\n", "LEFT JOIN `autingo-159109.psql_dcpublic.tpv_orders_invoice` inv_direct\n", " ON so.tpv_orders_order_id = inv_direct.order_id\n", "LEFT JOIN `autingo-159109.psql_dcpublic.tpv_orders_invoice` inv_precaweb\n", " ON pj.order_id = inv_precaweb.order_id AND so.tpv_orders_order_id IS NULL\n", "LEFT JOIN `autingo-159109.mssql2022_dbo.anjana_sales_invoice_header` sih\n", " ON COALESCE(inv_direct.nav_id, inv_precaweb.nav_id, lo.invoice_number) = sih.no_\n", " AND sih._fivetran_deleted = FALSE\n", "WHERE so.service_request_id IS NOT NULL\n", "```\n", "\n", "Con esta vista, un analista puede responder en **una sola query**:\n", "- Ventas por canal, mes, producto\n", "- Qué % se factura realmente (trazabilidad)\n", "- Qué % se cumple en centro físico vs digital\n", "- Cuánto factura cada centro NAV por canal web\n", "\n", "### 12.4 Alternativa en Metabase (sin tocar BigQuery)\n", "\n", "Si no se puede crear la view en BQ, se puede crear como **Modelo** en Metabase (type='model'):\n", "\n", "1. Nueva card tipo model con la SQL de arriba\n", "2. Guardar en colección \"Models / Venta Web\"\n", "3. Cualquier card/dashboard nuevo puede usar `source-table: card__` y tiene todas las columnas disponibles en modo point-and-click\n", "\n", "### 12.5 Seguimiento cuando la venta va a centro\n", "\n", "La trazabilidad completa es:\n", "\n", "```\n", "supply_orders.service_request_id\n", " ↓\n", "service_requests.channel_id (qué canal)\n", " ↓\n", "supply_orders.logistic_order_id\n", " ↓\n", "logistic_orders.center_id (qué centro cumple)\n", " ↓\n", "centers.nav_id (código NAV del centro)\n", " ↓\n", "anjana_sales_invoice_header.location_code (factura NAV final)\n", "```\n", "\n", "Clave: **el centro que cumplimenta NO tiene por qué ser digital**. Para el 71% de aurgi.com (C&C), el flujo es: pedido web → tienda física → factura en NAV con location del centro físico.\n", "\n", "### 12.6 Recomendación operativa\n", "\n", "1. **Materializar la view** `v_venta_web_unificada` en BigQuery → fuente única para todos los reportes de venta web\n", "2. **Crear un Model en Metabase** basado en esa view → democratizar acceso sin SQL\n", "3. **Dashboard nuevo** con 3 tabs: \"Por canal\", \"Por destino (web/centro)\", \"Trazabilidad factura NAV\"\n", "4. **Deprecar filtros engañosos** del dashboard 734 que usan solo location_code (pierden el 71% del C&C)" ] }, { "cell_type": "code", "execution_count": 30, "id": "878d8a19", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Tablas tpv_* en psql_dcpublic: 61\n", "\n", " 1653 tpv_authorization_tpvclock\n", " 1654 tpv_authorization_tpvuser\n", " 1655 tpv_authorization_tpvuser_centers\n", " 1657 tpv_authorization_tpvuser_groups\n", " 1858 tpv_authorization_tpvuser_user_permissions\n", " 1656 tpv_centers_shift\n", " 4637 tpv_insurance_insuranceclaim_orders\n", " 1532 tpv_orders_advancepayment\n", " 1593 tpv_orders_advancepayment_returns_documents\n", " 1531 tpv_orders_cashoperation\n", " 1575 tpv_orders_collective\n", " 1581 tpv_orders_collective_companies\n", " 1554 tpv_orders_comparative\n", " 1561 tpv_orders_exchange\n", " 1585 tpv_orders_exchange_new_items\n", " 1544 tpv_orders_exchange_to_exchange\n", " 1549 tpv_orders_gtrequest\n", " 1528 tpv_orders_invoice\n", " 1559 tpv_orders_itemreplace\n", " 1572 tpv_orders_note\n", " 1548 tpv_orders_order\n", " 1555 tpv_orders_orderitem\n", " 1579 tpv_orders_orderitem_promos\n", " 1558 tpv_orders_payment\n", " 1526 tpv_orders_paymentauthorization\n", " 1564 tpv_orders_paymentreturnrelation\n", " 1547 tpv_orders_quote\n", " 1578 tpv_orders_return\n", " 1541 tpv_orders_returnadvancepaymentdocument\n", " 1536 tpv_orders_returnreason\n", " 1571 tpv_orders_return_to_exchange\n", " 1587 tpv_orders_transactiondeleteuser\n", " 1567 tpv_otrs_otr_orders\n", " 1543 tpv_payment_types\n", " 1782 tpv_payment_types_centers\n", " 1591 tpv_payment_types_companies\n", " 1552 tpv_terminals\n" ] } ], "source": [ "# Buscar todas las tablas tpv_* en psql_dcpublic\n", "tpv_tables = [t for t in db6['tables'] if t.get('schema') == 'psql_dcpublic' and t['name'].startswith('tpv_')]\n", "print(f'Tablas tpv_* en psql_dcpublic: {len(tpv_tables)}')\n", "print()\n", "# Filtrar las mas relevantes para venta/terminal\n", "relevant = [t for t in tpv_tables if any(k in t['name'].lower() for k in \n", " ['order', 'sale', 'ticket', 'terminal', 'device', 'pos', 'idx', 'clock', 'user', 'center', 'sessio', 'pay', 'checkout', 'invoice', 'receipt'])]\n", "for t in relevant[:40]:\n", " print(f\" {t['id']:5} {t['name']}\")" ] }, { "cell_type": "code", "execution_count": 32, "id": "c371fefb", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "=== tpv_terminals (id 1552) — 20 cols ===\n", " id [Integer]\n", " name [Text]\n", " created_at [DateTimeWithLocalTZ]\n", " updated_at [DateTimeWithLocalTZ]\n", " center_id [Integer]\n", " inventory_terminal [Boolean]\n", " status_open [Boolean]\n", " status_shift [Integer]\n", " status_description [Text]\n", " status_open_amount [Decimal]\n", " terminal_type_id [Integer]\n", " visibility [Integer]\n", " current_shift_id [Integer]\n", " pending_products [Boolean]\n", " datastream_metadata [Dictionary]\n", " source_timestamp [Integer]\n", " uuid [Text]\n", " second_screen_active [Boolean]\n", " customer_interaction_lock [Boolean]\n", " assistant_screen_online [Boolean]\n", "\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "=== tpv_orders_order (id 1548) — 58 cols ===\n", " id [Integer]\n", " created_at [DateTimeWithLocalTZ]\n", " updated_at [DateTimeWithLocalTZ]\n", " customer_id [Integer]\n", " total_cost [Decimal]\n", " total_cost_currency [Text]\n", " calculate_payload [JSON]\n", " terminal_id [Integer]\n", " vehicle_id [Integer]\n", " shift [Integer]\n", " has_exchange [Boolean]\n", " parent_id [Integer]\n", " is_precaweb [Boolean]\n", " calculate_uuid [Text]\n", " has_service_request [Boolean]\n", " collective_id [Integer]\n", " execution_time [Integer]\n", " shift_model_id [Integer]\n", " allow_all_returns [Boolean]\n", " return_custom_times [JSON]\n", " payment_methods_override [JSON]\n", " referenced_user_id [Integer]\n", " allow_return_with_open_otr [Boolean]\n", " driver_id [Integer]\n", " owner_id [Integer]\n", " allow_returns_in_cash [Boolean]\n", " gt_request_id [Integer]\n", " requires_payment_authorization [Boolean]\n", " can_be_returned_special_customer [Boolean]\n", " tax_applied [Float]\n", " datastream_metadata [Dictionary]\n", " source_timestamp [Integer]\n", " uuid [Text]\n", " last_promo_line_number [Integer]\n", " last_item_line_number [Integer]\n", " last_update_in_nav [DateTimeWithLocalTZ]\n", " voucher_code [Text]\n", " kilometers [Integer]\n", " autogenerated_from_otr_id [Integer]\n", " reason [Text]\n", " appointment_id [Integer]\n", " stop_ralarsa_sync [Boolean]\n", " skip_provider_requires_delivery_confirmation [Boolean]\n", " insurance_claim_id [Integer]\n", " need_insurance_validation [Boolean]\n", " validate_sol_pieces_when_closing_otr [Boolean]\n", " accident_type_description [Text]\n", " operation_type [Text]\n", " insurance_policy_id [Integer]\n", " damage_type_description [Text]\n", " recorder_car_renounce_id [Integer]\n", " allowed_car_revision [Boolean]\n", " date_car_renounce [DateTimeWithLocalTZ]\n", " last_incident_id [Integer]\n", " show_budget_vs_appraisal_comparison [Boolean]\n", " can_link_appraissal [Boolean]\n", " source [Text]\n", " external_id [Text]\n", "\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "=== tpv_orders_invoice (id 1528) — 24 cols ===\n", " id [Integer]\n", " nav_id [Text]\n", " created_at [DateTimeWithLocalTZ]\n", " updated_at [DateTimeWithLocalTZ]\n", " order_id [Integer]\n", " created_by_id [Integer]\n", " terminal_id [Integer]\n", " invoice_path [Text]\n", " ticket_path [Text]\n", " status [Integer]\n", " clear_one_path [Text]\n", " customer_copy [JSON]\n", " vehicle_copy [JSON]\n", " show_vehicle [Boolean]\n", " profit [Decimal]\n", " profit_currency [Text]\n", " delivery_note [Text]\n", " datastream_metadata [Dictionary]\n", " source_timestamp [Integer]\n", " uuid [Text]\n", " adjusted_profit [Decimal]\n", " adjusted_profit_currency [Text]\n", " registered_in_sol [Boolean]\n", " from_standard_billing_flow [Boolean]\n", "\n" ] } ], "source": [ "# Inspeccionar tpv_terminals, tpv_orders_order, tpv_orders_invoice\n", "for tid, label in [(1552, 'tpv_terminals'), (1548, 'tpv_orders_order'), (1528, 'tpv_orders_invoice')]:\n", " m = client.request(\"GET\", f\"/api/table/{tid}/query_metadata\")\n", " print(f\"=== {label} (id {tid}) — {len(m['fields'])} cols ===\")\n", " for f in m['fields']:\n", " print(f\" {f['name']:40} [{f['base_type'].replace('type/','')}]\")\n", " print()" ] }, { "cell_type": "code", "execution_count": 33, "id": "56384579", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Terminales totales: 2000\n", "\n", "--- Distribucion terminal_type_id ---\n", "terminal_type_id\n", "3 764\n", "17 534\n", "9 444\n", "5 62\n", "10 53\n", "8 52\n", "11 52\n", "15 16\n", "1 10\n", "14 5\n", "12 3\n", "4 2\n", "16 2\n", "6 1\n", "\n", "--- Terminales en centros DIGITALES (32) ---\n", " terminal_id terminal_name terminal_type_id center_id centro centro_nav_id centro_es_digital\n", " 701 18CAJA01 3 160 Aurgi Web 18 True\n", " 702 18CAJA02 3 160 Aurgi Web 18 True\n", " 703 18CAJA03 3 160 Aurgi Web 18 True\n", " 704 18CAJA04 3 160 Aurgi Web 18 True\n", " 705 18OFICINA01 3 160 Aurgi Web 18 True\n", " 706 18TALLER01 3 160 Aurgi Web 18 True\n", " 707 CAJA_WEB 3 160 Aurgi Web 18 True\n", " 708 FICHAJE 3 160 Aurgi Web 18 True\n", " 1121 CCAL 3 160 Aurgi Web 18 True\n", " 1070 19CAJA01 9 161 MT Web 19 True\n", " 1071 19CAJA02 3 161 MT Web 19 True\n", " 1072 19CAJA03 3 161 MT Web 19 True\n", " 1073 19CAJA04 3 161 MT Web 19 True\n", " 1074 19OFICINA01 3 161 MT Web 19 True\n", " 1075 19TALLER01 3 161 MT Web 19 True\n", " 1076 CAJA_WEB 6 161 MT Web 19 True\n", " 1077 CCAL 3 161 MT Web 19 True\n", " 1078 FICHAJE 3 161 MT Web 19 True\n", " 3016 405CAJA01 3 167 Autingo 405 True\n", " 3017 405CAJA02 3 167 Autingo 405 True\n", " 3018 405CAJA03 3 167 Autingo 405 True\n", " 3019 405CAJA04 3 167 Autingo 405 True\n", " 3020 405OFICINA01 3 167 Autingo 405 True\n", " 3021 405TALLER01 3 167 Autingo 405 True\n", " 3022 CAJA_WEB 3 167 Autingo 405 True\n", " 3023 CCAL 3 167 Autingo 405 True\n", " 3024 FICHAJE 3 167 Autingo 405 True\n", " 6033 CCAL 14 167 Autingo 405 True\n", " 6039 CCAL 14 167 Autingo 405 True\n", " 6040 CCAL 14 167 Autingo 405 True\n", " 6041 CCAL 14 167 Autingo 405 True\n", " 6334 CCAL 14 167 Autingo 405 True\n" ] } ], "source": [ "# 1. Inventario de terminales con nombre, centro, tipo\n", "sql_terms = '''\n", "SELECT\n", " t.id AS terminal_id,\n", " t.name AS terminal_name,\n", " t.terminal_type_id,\n", " t.center_id,\n", " c.name AS centro,\n", " c.nav_id AS centro_nav_id,\n", " c.is_digital AS centro_es_digital\n", "FROM `autingo-159109.psql_dcpublic.tpv_terminals` t\n", "LEFT JOIN `autingo-159109.psql_dcpublic.centers` c ON t.center_id = c.id\n", "ORDER BY t.center_id, t.id\n", "'''\n", "df_terms = run_sql(sql_terms)\n", "print(f'Terminales totales: {len(df_terms)}')\n", "print()\n", "# Distribucion por tipo\n", "print('--- Distribucion terminal_type_id ---')\n", "print(df_terms['terminal_type_id'].value_counts().to_string())\n", "print()\n", "# Terminales en centros digitales\n", "dig = df_terms[df_terms['centro_es_digital'] == True]\n", "print(f'--- Terminales en centros DIGITALES ({len(dig)}) ---')\n", "print(dig.to_string(index=False))" ] }, { "cell_type": "code", "execution_count": 34, "id": "d5e4a942", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "=== Orders 2025 en centros DIGITALES por terminal y flag is_precaweb / has_service_request ===\n", "terminal_name centro centro_es_digital is_precaweb has_service_request orders total_cost_sum\n", " 422CAJA01 Aurgi Asociados True False False 5 834936\n", " CAJA_WEB Aurgi Asociados True True False 3 1282\n", " CAJA_WEB Aurgi Asociados Gruas True True False 27696 1442752\n", " 18CAJA01 Aurgi Web True False False 2 143\n", " CAJA_WEB Aurgi Web True True False 112805 4571118\n", " CAJA_WEB Autingo True True False 11959 1239345\n", " 19CAJA01 MT Web True False False 6 620\n", " CAJA_WEB MT Web True True False 1161 86088\n" ] } ], "source": [ "# Cross-check: para cada tpv_orders_order en 2025, agrupar por terminal_name + is_precaweb + has_service_request\n", "sql_term_verify = '''\n", "SELECT\n", " t.name AS terminal_name,\n", " c.name AS centro,\n", " c.is_digital AS centro_es_digital,\n", " o.is_precaweb,\n", " o.has_service_request,\n", " COUNT(DISTINCT o.id) AS orders,\n", " ROUND(SUM(o.total_cost), 0) AS total_cost_sum\n", "FROM `autingo-159109.psql_dcpublic.tpv_orders_order` o\n", "LEFT JOIN `autingo-159109.psql_dcpublic.tpv_terminals` t ON o.terminal_id = t.id\n", "LEFT JOIN `autingo-159109.psql_dcpublic.centers` c ON t.center_id = c.id\n", "WHERE EXTRACT(YEAR FROM o.created_at) = 2025\n", " AND c.is_digital = TRUE\n", "GROUP BY terminal_name, centro, centro_es_digital, is_precaweb, has_service_request\n", "ORDER BY centro, terminal_name, is_precaweb\n", "'''\n", "df_tv = run_sql(sql_term_verify)\n", "print('=== Orders 2025 en centros DIGITALES por terminal y flag is_precaweb / has_service_request ===')\n", "print(df_tv.to_string(index=False))" ] }, { "cell_type": "code", "execution_count": 35, "id": "ce97f39a", "metadata": {}, "outputs": [ { "ename": "HTTPStatusError", "evalue": "Client error '400 Bad Request' for url 'https://reports.autingo.es/api/dataset'\nFor more information check: https://developer.mozilla.org/en-US/docs/Web/HTTP/Status/400", "output_type": "error", "traceback": [ "\u001b[31m---------------------------------------------------------------------------\u001b[39m", "\u001b[31mHTTPStatusError\u001b[39m Traceback (most recent call last)", "\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[35]\u001b[39m\u001b[32m, line 20\u001b[39m\n\u001b[32m 16\u001b[39m AND (t.name LIKE \u001b[33m'%WEB%'\u001b[39m OR t.name LIKE \u001b[33m'%CCAL%'\u001b[39m OR t.name = \u001b[33m'CAJA_WEB'\u001b[39m)\n\u001b[32m 17\u001b[39m GROUP BY terminal_name\n\u001b[32m 18\u001b[39m ORDER BY orders DESC\n\u001b[32m 19\u001b[39m '''\n\u001b[32m---> \u001b[39m\u001b[32m20\u001b[39m df_fis = run_sql(sql_fisicos)\n\u001b[32m 21\u001b[39m print(\u001b[33m'=== Terminales WEB/CCAL en centros FISICOS 2025 ==='\u001b[39m)\n\u001b[32m 22\u001b[39m print(df_fis.to_string(index=\u001b[38;5;28;01mFalse\u001b[39;00m))\n\u001b[32m 23\u001b[39m print()\n", "\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[1]\u001b[39m\u001b[32m, line 20\u001b[39m, in \u001b[36mrun_sql\u001b[39m\u001b[34m(sql, db_id)\u001b[39m\n\u001b[32m 18\u001b[39m \u001b[38;5;28;01mdef\u001b[39;00m run_sql(sql: str, db_id: int = DB_BIGQUERY) -> pd.DataFrame:\n\u001b[32m 19\u001b[39m \u001b[33m\"\"\"Ejecuta SQL contra Metabase y retorna DataFrame.\"\"\"\u001b[39m\n\u001b[32m---> \u001b[39m\u001b[32m20\u001b[39m res = metabase_execute_query(client, db_id, sql)\n\u001b[32m 21\u001b[39m cols = [c[\u001b[33m'display_name'\u001b[39m] \u001b[38;5;28;01mor\u001b[39;00m c[\u001b[33m'name'\u001b[39m] \u001b[38;5;28;01mfor\u001b[39;00m c \u001b[38;5;28;01min\u001b[39;00m res[\u001b[33m'data'\u001b[39m][\u001b[33m'cols'\u001b[39m]]\n\u001b[32m 22\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m pd.DataFrame(res[\u001b[33m'data'\u001b[39m][\u001b[33m'rows'\u001b[39m], columns=cols)\n", "\u001b[36mFile \u001b[39m\u001b[32m~/fn_registry/python/functions/metabase/cards.py:227\u001b[39m, in \u001b[36mmetabase_execute_query\u001b[39m\u001b[34m(client, database_id, sql, max_results)\u001b[39m\n\u001b[32m 222\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m max_results > \u001b[32m0\u001b[39m:\n\u001b[32m 223\u001b[39m body[\u001b[33m\"\u001b[39m\u001b[33mconstraints\u001b[39m\u001b[33m\"\u001b[39m] = {\n\u001b[32m 224\u001b[39m \u001b[33m\"\u001b[39m\u001b[33mmax-results\u001b[39m\u001b[33m\"\u001b[39m: max_results,\n\u001b[32m 225\u001b[39m \u001b[33m\"\u001b[39m\u001b[33mmax-results-bare-rows\u001b[39m\u001b[33m\"\u001b[39m: max_results,\n\u001b[32m 226\u001b[39m }\n\u001b[32m--> \u001b[39m\u001b[32m227\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mclient\u001b[49m\u001b[43m.\u001b[49m\u001b[43mrequest\u001b[49m\u001b[43m(\u001b[49m\u001b[33;43m\"\u001b[39;49m\u001b[33;43mPOST\u001b[39;49m\u001b[33;43m\"\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[33;43m\"\u001b[39;49m\u001b[33;43m/api/dataset\u001b[39;49m\u001b[33;43m\"\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mjson\u001b[49m\u001b[43m=\u001b[49m\u001b[43mbody\u001b[49m\u001b[43m)\u001b[49m\n", "\u001b[36mFile \u001b[39m\u001b[32m~/fn_registry/python/functions/metabase/client.py:45\u001b[39m, in \u001b[36mMetabaseClient.request\u001b[39m\u001b[34m(self, method, path, **kwargs)\u001b[39m\n\u001b[32m 31\u001b[39m \u001b[38;5;250m\u001b[39m\u001b[33;03m\"\"\"Ejecuta una peticion HTTP contra la API de Metabase.\u001b[39;00m\n\u001b[32m 32\u001b[39m \n\u001b[32m 33\u001b[39m \u001b[33;03mArgs:\u001b[39;00m\n\u001b[32m (...)\u001b[39m\u001b[32m 42\u001b[39m \u001b[33;03m httpx.HTTPStatusError: Si el status code no es 2xx.\u001b[39;00m\n\u001b[32m 43\u001b[39m \u001b[33;03m\"\"\"\u001b[39;00m\n\u001b[32m 44\u001b[39m resp = \u001b[38;5;28mself\u001b[39m._http.request(method, path, **kwargs)\n\u001b[32m---> \u001b[39m\u001b[32m45\u001b[39m \u001b[43mresp\u001b[49m\u001b[43m.\u001b[49m\u001b[43mraise_for_status\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[32m 46\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m resp.content:\n\u001b[32m 47\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m\n", "\u001b[36mFile \u001b[39m\u001b[32m~/fn_registry/projects/aurgi/analysis/venta_web/.venv/lib/python3.13/site-packages/httpx/_models.py:829\u001b[39m, in \u001b[36mResponse.raise_for_status\u001b[39m\u001b[34m(self)\u001b[39m\n\u001b[32m 827\u001b[39m error_type = error_types.get(status_class, \u001b[33m\"\u001b[39m\u001b[33mInvalid status code\u001b[39m\u001b[33m\"\u001b[39m)\n\u001b[32m 828\u001b[39m message = message.format(\u001b[38;5;28mself\u001b[39m, error_type=error_type)\n\u001b[32m--> \u001b[39m\u001b[32m829\u001b[39m \u001b[38;5;28;01mraise\u001b[39;00m HTTPStatusError(message, request=request, response=\u001b[38;5;28mself\u001b[39m)\n", "\u001b[31mHTTPStatusError\u001b[39m: Client error '400 Bad Request' for url 'https://reports.autingo.es/api/dataset'\nFor more information check: https://developer.mozilla.org/en-US/docs/Web/HTTP/Status/400" ] } ], "source": [ "# Y ahora los centros FISICOS (no digitales) — buscar terminales CCAL / CAJA_WEB ahi\n", "# para ver venta web cumplida en tienda\n", "sql_fisicos = '''\n", "SELECT\n", " t.name AS terminal_name,\n", " COUNT(DISTINCT o.id) AS orders,\n", " COUNT(DISTINCT o.id) FILTER (WHERE o.is_precaweb) AS orders_precaweb,\n", " COUNT(DISTINCT o.id) FILTER (WHERE o.has_service_request) AS orders_con_service_req,\n", " ROUND(SUM(o.total_cost), 0) AS total_cost,\n", " ROUND(SUM(CASE WHEN o.is_precaweb THEN o.total_cost END), 0) AS total_cost_precaweb\n", "FROM `autingo-159109.psql_dcpublic.tpv_orders_order` o\n", "JOIN `autingo-159109.psql_dcpublic.tpv_terminals` t ON o.terminal_id = t.id\n", "JOIN `autingo-159109.psql_dcpublic.centers` c ON t.center_id = c.id\n", "WHERE EXTRACT(YEAR FROM o.created_at) = 2025\n", " AND c.is_digital = FALSE\n", " AND (t.name LIKE '%WEB%' OR t.name LIKE '%CCAL%' OR t.name = 'CAJA_WEB')\n", "GROUP BY terminal_name\n", "ORDER BY orders DESC\n", "'''\n", "df_fis = run_sql(sql_fisicos)\n", "print('=== Terminales WEB/CCAL en centros FISICOS 2025 ===')\n", "print(df_fis.to_string(index=False))\n", "print()\n", "\n", "# Y ver conteo por nombre terminal (top) en centros fisicos con is_precaweb=TRUE\n", "sql_fis2 = '''\n", "SELECT\n", " t.name AS terminal_name,\n", " COUNT(DISTINCT o.id) AS orders_web_in_centro,\n", " ROUND(SUM(o.total_cost), 0) AS revenue\n", "FROM `autingo-159109.psql_dcpublic.tpv_orders_order` o\n", "JOIN `autingo-159109.psql_dcpublic.tpv_terminals` t ON o.terminal_id = t.id\n", "JOIN `autingo-159109.psql_dcpublic.centers` c ON t.center_id = c.id\n", "WHERE EXTRACT(YEAR FROM o.created_at) = 2025\n", " AND c.is_digital = FALSE\n", " AND o.is_precaweb = TRUE\n", "GROUP BY terminal_name\n", "ORDER BY orders_web_in_centro DESC\n", "LIMIT 20\n", "'''\n", "df_fis2 = run_sql(sql_fis2)\n", "print('=== TOP terminales con is_precaweb=TRUE en centros FISICOS 2025 ===')\n", "print(df_fis2.to_string(index=False))" ] }, { "cell_type": "code", "execution_count": 36, "id": "1adaf1b0", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "=== TOP terminales en centros FISICOS con is_precaweb>0 (2025) ===\n", " terminal_id terminal_name terminal_type_id centro centro_nav_id orders orders_precaweb orders_con_sr revenue revenue_precaweb\n", " 263 CAJA_WEB 3 Vaguada 89 3122 1126 0 418512 199638\n", " 252 CAJA_WEB 3 San Sebastian 23 1982 961 0 283161 170123\n", " 205 CAJA_WEB 3 Villalba 54 7036 866 0 753962 140903\n", " 199 CAJA_WEB 3 Vallecas 40 6833 770 0 580134 124238\n", " 185 CAJA_WEB 3 Leganes 21 7674 716 0 866044 114946\n", " 55 CAJA_WEB 3 Malaga 26 2742 703 0 308018 112471\n", " 90 CAJA_WEB 3 Majadahonda 85 1832 700 0 229677 126809\n", " 134 CAJA_WEB 3 Emilio Muñoz 75 2375 671 0 288075 108933\n", " 337 CAJA_WEB 3 Alcorcon 61 7462 652 0 846504 101293\n", " 72 CAJA_WEB 3 Store 28 3793 608 0 453970 94475\n", " 12 CAJA_WEB 3 Islazul 167 1995 589 0 249061 95465\n", " 107 CAJA_WEB 3 Sant Cugat 156 2531 568 0 250190 97797\n", " 84 CAJA_WEB 3 San Fernando 22 8029 561 0 725251 91731\n", " 279 CAJA_WEB 3 Granada 38 2030 519 0 220857 83086\n", " 357 CAJA_WEB 3 San Juan 63 5737 511 0 655167 82093\n", " 219 CAJA_WEB 3 Cornella 55 5877 499 0 609250 80754\n", " 350 CAJA_WEB 3 Las Rozas 25 6054 493 0 593887 95445\n", " 96 CAJA_WEB 3 Avda. Toreros 76 1338 492 0 171564 87708\n", " 171 CAJA_WEB 3 Almeria 46 3020 478 0 252919 74703\n", " 324 CAJA_WEB 3 Barbera 29 3007 477 0 269678 76220\n", " 65 CAJA_WEB 3 Mataro 36 3776 475 0 404397 73240\n", " 148 CAJA_WEB 3 Mostoles 58 1606 447 0 207006 83856\n", " 165 CAJA_WEB 3 Valdemoro 59 1876 436 0 245426 71580\n", " 314 CAJA_WEB 3 Granollers 169 2117 396 0 195765 64192\n", " 144 CAJA_WEB 3 Cornella 2 90 1343 386 0 178499 68037\n", " 386 CAJA_WEB 3 La Red 27 2648 366 0 297999 58170\n", " 192 CAJA_WEB 3 Gta. Cadiz 45 2309 363 0 270676 54017\n", " 47 CAJA_WEB 3 Cordoba 43 5254 353 0 548276 58900\n", " 399 CAJA_WEB 3 Rivas 53 938 340 0 109333 59602\n", " 153 CAJA_WEB 3 Fuengirola 88 1453 314 0 141843 55727\n", " 140 CAJA_WEB 3 Gava 95 1863 313 0 226120 48879\n", " 299 CAJA_WEB 3 Velez Malaga 86 2480 310 0 273057 54281\n", " 362 CAJA_WEB 3 Zaragoza 168 842 310 0 115717 55645\n", " 224 CAJA_WEB 3 Sabadell 74 1794 305 0 217652 54001\n", " 76 CAJA_WEB 3 Sant Boi 37 1011 291 0 139085 50410\n", " 235 CAJA_WEB 3 Alcala Henares 56 2604 284 0 241717 55727\n", " 128 CAJA_WEB 3 Pinto 24 967 267 0 126676 43069\n", " 60 CAJA_WEB 3 Aluche 154 1587 265 0 151584 41967\n", " 284 CAJA_WEB 3 Olias del Rey 78 846 251 0 97988 45618\n", " 23 CAJA_WEB 3 Huelva 166 1230 249 0 124801 49376\n" ] } ], "source": [ "# Sin asumir nombres: top terminales por orders is_precaweb=TRUE en CENTROS FISICOS 2025\n", "# (syntax BigQuery: COUNTIF / SUM(IF(...)))\n", "sql_fis = '''\n", "SELECT\n", " t.id AS terminal_id,\n", " t.name AS terminal_name,\n", " t.terminal_type_id,\n", " c.name AS centro,\n", " c.nav_id AS centro_nav_id,\n", " COUNT(DISTINCT o.id) AS orders,\n", " COUNTIF(o.is_precaweb) AS orders_precaweb,\n", " COUNTIF(o.has_service_request) AS orders_con_sr,\n", " ROUND(SUM(o.total_cost), 0) AS revenue,\n", " ROUND(SUM(IF(o.is_precaweb, o.total_cost, 0)), 0) AS revenue_precaweb\n", "FROM `autingo-159109.psql_dcpublic.tpv_orders_order` o\n", "JOIN `autingo-159109.psql_dcpublic.tpv_terminals` t ON o.terminal_id = t.id\n", "JOIN `autingo-159109.psql_dcpublic.centers` c ON t.center_id = c.id\n", "WHERE EXTRACT(YEAR FROM o.created_at) = 2025\n", " AND c.is_digital = FALSE\n", "GROUP BY terminal_id, terminal_name, terminal_type_id, centro, centro_nav_id\n", "HAVING COUNTIF(o.is_precaweb) > 0\n", "ORDER BY orders_precaweb DESC\n", "LIMIT 40\n", "'''\n", "df_fis = run_sql(sql_fis)\n", "print('=== TOP terminales en centros FISICOS con is_precaweb>0 (2025) ===')\n", "print(df_fis.to_string(index=False))" ] }, { "cell_type": "code", "execution_count": 37, "id": "f1f1595a", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "=== NOMBRES UNICOS DE TERMINAL + orders 2025 ===\n", "terminal_name num_terminales orders_2025 precaweb service_req\n", " CAJA_WEB 162 320493 177531 0\n", " CCAL 213 241416 0 0\n", " 55CAJA01 1 36579 0 0\n", " 169CAJA02 1 28675 0 0\n", " 58CAJA01 1 27506 0 0\n", " 167CAJA02 1 27312 0 0\n", " 75CAJA02 1 26657 0 0\n", " 61CAJA02 1 25467 0 0\n", " 36CAJA03 1 24685 0 0\n", " 29CAJA02 1 24288 0 0\n", " 21CAJA02 1 24210 0 0\n", " 54CAJA02 1 23543 0 0\n", " 43CAJA03 1 23437 0 0\n", " 54CAJA03 1 22989 0 0\n", " 57CAJA01 1 22854 0 0\n", " 38CAJA03 1 22824 0 0\n", " 45CAJA01 1 22640 0 0\n", " 28CAJA03 1 22563 0 0\n", " 28CAJA04 1 22251 0 0\n", " 24CAJA02 1 20507 0 0\n", " 46CAJA01 1 20398 0 0\n", " 86CAJA01 1 20344 0 0\n", " 62CAJA01 1 20103 0 0\n", " 23CAJA01 1 20053 0 0\n", " 76CAJA01 1 20021 0 0\n", " 56CAJA02 1 19980 0 0\n", " 164CAJA02 1 19838 0 0\n", " 21CAJA03 1 19502 0 0\n", " 156CAJA02 1 19203 0 0\n", " 63CAJA04 1 19086 0 0\n", " 27CAJA02 1 19036 0 0\n", " 190CAJA01 1 19000 0 0\n", " 26CAJA03 1 18998 0 0\n", " 85CAJA01 1 18918 0 0\n", " 74CAJA02 1 18783 0 0\n", " 40CAJA02 1 18733 0 0\n", " 26CAJA04 1 18683 0 0\n", " 77CAJA02 1 18332 0 0\n", " 95CAJA01 1 17910 0 0\n", " 86CAJA02 1 17751 0 0\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "\n", "=== Distribucion de tpv_orders_order.source 2025 ===\n", "source orders precaweb service_req revenue\n", "(null) 2493702 177531 0 366408864\n" ] } ], "source": [ "# Nombres unicos de terminales + tipos + el campo `source` de tpv_orders_order\n", "# 1. Nombres distintos de terminales (top 30 por orders 2025)\n", "sql_term_names = '''\n", "SELECT\n", " t.name AS terminal_name,\n", " COUNT(DISTINCT t.id) AS num_terminales,\n", " COUNT(DISTINCT o.id) AS orders_2025,\n", " COUNTIF(o.is_precaweb) AS precaweb,\n", " COUNTIF(o.has_service_request) AS service_req\n", "FROM `autingo-159109.psql_dcpublic.tpv_terminals` t\n", "LEFT JOIN `autingo-159109.psql_dcpublic.tpv_orders_order` o\n", " ON o.terminal_id = t.id AND EXTRACT(YEAR FROM o.created_at) = 2025\n", "GROUP BY terminal_name\n", "ORDER BY orders_2025 DESC NULLS LAST\n", "LIMIT 40\n", "'''\n", "df_tn = run_sql(sql_term_names)\n", "print('=== NOMBRES UNICOS DE TERMINAL + orders 2025 ===')\n", "print(df_tn.to_string(index=False))\n", "\n", "# 2. Campo `source` de tpv_orders_order\n", "sql_source = '''\n", "SELECT\n", " COALESCE(o.source, '(null)') AS source,\n", " COUNT(DISTINCT o.id) AS orders,\n", " COUNTIF(o.is_precaweb) AS precaweb,\n", " COUNTIF(o.has_service_request) AS service_req,\n", " ROUND(SUM(o.total_cost), 0) AS revenue\n", "FROM `autingo-159109.psql_dcpublic.tpv_orders_order` o\n", "WHERE EXTRACT(YEAR FROM o.created_at) = 2025\n", "GROUP BY source\n", "ORDER BY orders DESC\n", "'''\n", "df_src = run_sql(sql_source)\n", "print()\n", "print('=== Distribucion de tpv_orders_order.source 2025 ===')\n", "print(df_src.to_string(index=False))" ] }, { "cell_type": "code", "execution_count": 38, "id": "181dcded", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "=== TERMINAL CCAL (2025) — cruzando con supply_orders ===\n", " centro ccal_orders supply_orders_asociados con_service_request total_cost_ccal\n", " Goya GLASS 9110 7120 0 2366941\n", " Store 6571 505 0 1139059\n", " MT El Bercial CRISTALES 6143 4869 0 1648415\n", " Vallecas CRISTALES 6019 4953 0 1550761\n", " Leganes 5864 507 0 1093530\n", " Vaguada 5644 940 0 995783\n", " MT Sanchinarro 5166 1683 0 1427021\n", " Malaga 4923 336 0 874492\n", " Villalba 4732 658 0 856667\n", " Alcobendas CRISTALES 4616 3751 0 1350488\n", " Granada 4446 257 0 818977\n", " Leganes CRISTALES 4102 3599 0 1140564\n", " San Fernando CRISTALES 4007 3450 0 1167164\n", " Villalba CRISTALES 3888 3478 0 1163717\n", "Santa Engracia CRISTALES 3856 3306 0 1157439\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "\n", "=== Distribucion por terminal_type_id ===\n", " terminal_type_id terminales orders_2025 precaweb ccal_count caja_web_count\n", " 17 7274 0 0 0 0\n", " 3 805 1797438 175236 5 77\n", " 9 444 440148 1134 1 50\n", " 15 274 13429 0 0 34\n", " 5 65 140510 0 64 0\n", " 8 53 0 0 0 0\n", " 10 53 1834 0 52 0\n", " 11 52 33533 0 52 0\n", " 16 34 65649 0 34 0\n", " 1 11 0 0 0 0\n", " 14 5 0 0 5 0\n", " 12 3 0 0 0 0\n", " 4 2 0 0 0 0\n", " 6 1 1161 1161 0 1\n", " 20 1 0 0 0 0\n" ] } ], "source": [ "# Cross-check CCAL: ver si sus orders generan supply_orders con service_request_id\n", "# (indicando que es venta centralizada / call center)\n", "sql_ccal = '''\n", "WITH ccal_orders AS (\n", " SELECT o.id AS order_id, o.terminal_id, o.total_cost, o.created_at, t.name AS term_name, c.name AS centro\n", " FROM `autingo-159109.psql_dcpublic.tpv_orders_order` o\n", " JOIN `autingo-159109.psql_dcpublic.tpv_terminals` t ON o.terminal_id = t.id\n", " JOIN `autingo-159109.psql_dcpublic.centers` c ON t.center_id = c.id\n", " WHERE t.name = 'CCAL'\n", " AND EXTRACT(YEAR FROM o.created_at) = 2025\n", ")\n", "SELECT\n", " co.centro,\n", " COUNT(DISTINCT co.order_id) AS ccal_orders,\n", " COUNT(DISTINCT so.id) AS supply_orders_asociados,\n", " COUNTIF(so.service_request_id IS NOT NULL) AS con_service_request,\n", " ROUND(SUM(co.total_cost), 0) AS total_cost_ccal\n", "FROM ccal_orders co\n", "LEFT JOIN `autingo-159109.psql_dcpublic.supply_orders` so ON so.tpv_orders_order_id = co.order_id\n", "GROUP BY co.centro\n", "ORDER BY ccal_orders DESC\n", "LIMIT 15\n", "'''\n", "df_ccal = run_sql(sql_ccal)\n", "print('=== TERMINAL CCAL (2025) — cruzando con supply_orders ===')\n", "print(df_ccal.to_string(index=False))\n", "\n", "# Y ver terminal_types\n", "sql_types = '''\n", "SELECT\n", " t.terminal_type_id,\n", " COUNT(DISTINCT t.id) AS terminales,\n", " COUNT(DISTINCT o.id) AS orders_2025,\n", " COUNTIF(o.is_precaweb) AS precaweb,\n", " COUNT(DISTINCT CASE WHEN t.name = 'CCAL' THEN t.id END) AS ccal_count,\n", " COUNT(DISTINCT CASE WHEN t.name = 'CAJA_WEB' THEN t.id END) AS caja_web_count\n", "FROM `autingo-159109.psql_dcpublic.tpv_terminals` t\n", "LEFT JOIN `autingo-159109.psql_dcpublic.tpv_orders_order` o\n", " ON o.terminal_id = t.id AND EXTRACT(YEAR FROM o.created_at) = 2025\n", "GROUP BY t.terminal_type_id\n", "ORDER BY terminales DESC\n", "'''\n", "df_types = run_sql(sql_types)\n", "print()\n", "print('=== Distribucion por terminal_type_id ===')\n", "print(df_types.to_string(index=False))" ] }, { "cell_type": "markdown", "id": "062f199b", "metadata": {}, "source": [ "## 13. Unión venta ↔ terminal TPV — verificación\n", "\n", "### 13.1 Caminos que SÍ funcionan\n", "\n", "```\n", "tpv_orders_order.terminal_id → tpv_terminals.id → tpv_terminals.center_id → centers\n", "tpv_orders_invoice.terminal_id → tpv_terminals.id\n", "supply_orders.tpv_orders_order_id → tpv_orders_order.id\n", "```\n", "\n", "Tanto el `order` como la `invoice` TPV cargan el `terminal_id`. La union funciona limpia.\n", "\n", "### 13.2 Flags que indican origen (señales directas)\n", "\n", "Campos en **`tpv_orders_order`**:\n", "\n", "| Campo | Qué indica | Utilidad |\n", "|---|---|---|\n", "| `is_precaweb` (Boolean) | Orden creada por flujo web (pre-caja web) | **Sí**: flag directo venta online |\n", "| `has_service_request` (Boolean) | Ligada a service_request web | Poco usado (siempre 0 en 2025) |\n", "| `appointment_id` | Venía de cita previa | Útil para C&C/cita |\n", "| `source` (Text) | **NULL en todos los 2.5M orders 2025** | ❌ No usar |\n", "\n", "### 13.3 Terminales reales (muestreo 2025)\n", "\n", "| Nombre terminal | Nº terminales | Orders 2025 | is_precaweb | Qué es |\n", "|---|---:|---:|---:|---|\n", "| `CAJA_WEB` | 162 | 320.493 | **177.531** | Terminal web — en TODOS los centros (no solo digitales) |\n", "| `CCAL` | 213 | 241.416 | 0 | **Call center de cristales/lunas** — siempre ligado a centros CRISTALES (Goya GLASS, Vallecas CRISTALES, Alcobendas CRISTALES, etc.) |\n", "| `{codcentro}CAJA01..04` | ~800 | millones | 0 | Cajas físicas del centro — venta presencial pura |\n", "| `{codcentro}TALLER01` / `OFICINA01` | variable | variable | 0 | Terminales de taller/oficina |\n", "| `FICHAJE` | variable | 0 | 0 | Terminal de fichar (no venta) |\n", "\n", "**Aviso importante**: `CCAL` NO es Click & Collect. Es el **call center de cristales** — operadores que toman pedidos telefónicos y los registran en una terminal dedicada. Tiene total_cost de 2.4M€/año solo en Goya GLASS. Es una línea de negocio aparte.\n", "\n", "### 13.4 Clasificación operativa del origen de venta\n", "\n", "Combinando terminal + flag:\n", "\n", "```sql\n", "CASE\n", " -- Venta web pura\n", " WHEN o.is_precaweb = TRUE THEN 'web_online'\n", " -- Call center cristales (detectar por terminal + centro)\n", " WHEN t.name = 'CCAL' AND c.name LIKE '%CRISTAL%' THEN 'call_center_cristales'\n", " WHEN t.name = 'CCAL' THEN 'call_center'\n", " -- Venta presencial tienda\n", " WHEN t.name LIKE '%CAJA0%' OR t.name LIKE '%TALLER%' OR t.name LIKE '%OFICINA%' THEN 'presencial_tienda'\n", " -- CAJA_WEB con precaweb=FALSE: raro pero lo ves en centros digitales (5 orders en 2025)\n", " WHEN t.name = 'CAJA_WEB' AND o.is_precaweb = FALSE THEN 'web_legacy_no_flag'\n", " ELSE 'otro'\n", "END AS origen_venta\n", "```\n", "\n", "**Con este campo podemos**:\n", "- Segmentar los ~2.5M orders/año por origen sin depender del nombre de terminal\n", "- Cruzar cada origen con NAV invoice (vía `tpv_orders_invoice.nav_id` o `supply_orders.tpv_orders_order_id`)\n", "- Añadir canal marketplace cuando origen = web_online (via `supply_orders → service_requests → channels`)\n", "\n", "### 13.5 Vista canónica v2 (incorporando TPV)\n", "\n", "La view `v_venta_web_unificada` propuesta en la sección 12.3 se enriquece con terminal + origen:\n", "\n", "```sql\n", "-- Añadir JOINs a tpv_orders_order / terminals y campo origen_venta\n", "LEFT JOIN `autingo-159109.psql_dcpublic.tpv_orders_order` tpv_o ON so.tpv_orders_order_id = tpv_o.id\n", "LEFT JOIN `autingo-159109.psql_dcpublic.tpv_terminals` tpv_t ON tpv_o.terminal_id = tpv_t.id\n", "```\n", "\n", "Esto permite:\n", "- Saber en qué terminal se imputó (tienda + caja específica)\n", "- Distinguir web / call center / presencial / C&C sin heurísticas frágiles\n", "- Seguir la cadena hasta la factura NAV\n", "\n", "### 13.6 Limitaciones detectadas\n", "\n", "- El campo `source` está vacío → no confiar en él\n", "- `has_service_request=FALSE` incluso para orders claramente web → flag no mantenido, usar `is_precaweb` en su lugar\n", "- `CCAL` sin flag alguno → hay que identificarlo por nombre + contexto del centro\n", "- Nombres de terminal no son estándar estricto (variantes `CAJA_WEB`, `CCAL`, `{cod}CAJA01`...) — conviene crear un catálogo `tpv_terminal_clasificacion` en `anjana_bi_datamart` para mantener la clasificación actualizada" ] } ], "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 }