🏢 Caso Práctico Real: Ingeniero de Datos y Aseguramiento de Ingresos (Telco)

1. 📌 Planteamiento del Problema de Negocio (Escenario Real)

Contexto de la Empresa

Una compañía de Telecomunicaciones móvil y fija con operaciones regionales (Chile, Perú, Colombia) ofrece planes móviles pospago y bolsas de datos adicionales.

La Fuga de Ingresos Detectada (The Leakage Issue)

Durante el cierre financiero mensual, el área de Contabilidad reporta una discrepancia no explicada de $45.000.000 CLP entre el volumen de datos de alta velocidad consumido en las antenas/nodos de red y lo efectivamente cobrado en el sistema de facturación (Billing).

flowchart TD
    A["📡 Red Móvil (HSS/PCRF/CDRs)<br>Tráfico Real de Datos"] -->|Falta de Sincronización| D{"❌ Fuga de Ingresos<br>($45M CLP / mes)"}
    B["💼 CRM de Clientes<br>Planes y Estados (Activo/Baja)"] -->|Error en API / Cola de Mensajes| D
    C["💳 Sistema de Facturación<br>Cargos Emitidos"] -->|Subfacturación| D
    D --> E["🔧 Ingeniero de Datos y RA<br>Pipeline Automatizado de Conciliación"]

Causas Raíz Identificadas:

1. Tráfico Fantasma (Ghost Traffic): 1.420 líneas dadas de baja o suspendidas por morosidad en el CRM siguen navegando en la red porque el aprovisionador de red (HLR/PCRF) no procesó la orden de corte debido a timeouts en la API.
2. Subtarificación de Bolsas Adicionales: Clientes que superaron su cuota de gigas contratada consumieron bolsas de emergencia automáticas que el sistema de facturación no liquidó en el ciclo de corte.
3. Ineficiencia Operativa: El equipo de Control de Gestión realiza este cruce manualmente los lunes descargando 3 Excels de 500.000 filas, demorando 14 horas/hombre semanales con alto margen de error humano.

2. ⚙️ Procedimientos Técnicos y Operativos Implementados

Como Ingeniero de Datos y Aseguramiento de Ingresos, se implementa una solución integral estructurada en 5 etapas:

[1. Ingesta ETL] ──> [2. Motor de Cruce] ──> [3. Alertas & Tickets] ──> [4. Power BI Dashboard] ──> [5. Documentación SOP]

Etapa 1: Ingesta Automatizada y Normalización (ETL)

• Frecuencia: Diaria a las 02:00 AM (Job programado desatendido).
• Fuentes de Datos:
• Base de datos transaccional del CRM (PostgreSQL / Oracle).
• Data Lake / Servidor SFTP de registros de tráfico de red (CDRs en formato parquet/csv).
• Base de datos del ERP de Facturación (SAP / NetSuite / Billing Engine).

Etapa 2: Motor de Reconciliación Automatizada (Python + SQL)

A continuación, el script productivo desarrollado para cruzar las 3 fuentes y detectar fugas:

import os
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from datetime import datetime, timedelta
import pandas as pd
import sqlalchemy as sa

# 1. Conexiones a bases de datos
ENGINE_DW = sa.create_engine("postgresql://user:pass@dw-telco.internal:5432/revenue_db")

def ejecutar_conciliacion_diaria():
    fecha_proceso = (datetime.now() - timedelta(days=1)).strftime('%Y-%m-%d')
    print(f"[{datetime.now()}] Iniciando conciliación para la fecha: {fecha_proceso}")

    # 2. Query de Reconciliación de 3 vías (Red vs CRM vs Facturación)
    query = f"""
    WITH ConsumoRed AS (
        SELECT 
            numero_linea,
            SUM(gigas_consumidos) AS total_gb_red,
            MAX(ultima_conexion) AS ultima_actividad_red
        FROM raw_cdrs_trafico
        WHERE fecha_evento = '{fecha_proceso}'
        GROUP BY numero_linea
    ),
    EstadoCRM AS (
        SELECT 
            numero_linea,
            cliente_id,
            pais,
            plan_nombre,
            estado_linea, -- 'ACTIVO', 'SUSPENDIDO', 'BAJA'
            limite_gb_plan,
            tarifa_base_clp,
            precio_gb_adicional_clp
        FROM dim_clientes_lineas
    ),
    FacturacionDia AS (
        SELECT 
            numero_linea,
            SUM(monto_cargo_adicional) AS cobro_adicional_facturado
        FROM fact_cargos_diarios
        WHERE fecha_cargo = '{fecha_proceso}'
        GROUP BY numero_linea
    )
    SELECT 
        c.pais,
        c.cliente_id,
        c.numero_linea,
        c.plan_nombre,
        c.estado_linea,
        COALESCE(r.total_gb_red, 0) AS gb_consumidos_red,
        c.limite_gb_plan,
        COALESCE(f.cobro_adicional_facturado, 0) AS cobrado_en_factura,

        -- Regla 1: Tráfico en línea cancelada/suspendida
        CASE 
            WHEN c.estado_linea IN ('SUSPENDIDO', 'BAJA') AND COALESCE(r.total_gb_red, 0) > 0.05 
            THEN 'FUGA_LINEA_NO_HABILITADA'

            -- Regla 2: Sobreconsumo no facturado
            WHEN COALESCE(r.total_gb_red, 0) > c.limite_gb_plan 
                 AND COALESCE(f.cobro_adicional_facturado, 0) < ((COALESCE(r.total_gb_red, 0) - c.limite_gb_plan) * c.precio_gb_adicional_clp)
            THEN 'FUGA_SUBFACTURACION_DATOS'

            ELSE 'CORRECTO'
        END AS clasificacion_fuga,

        -- Cálculo económico del impacto
        CASE 
            WHEN c.estado_linea IN ('SUSPENDIDO', 'BAJA') AND COALESCE(r.total_gb_red, 0) > 0 
            THEN (COALESCE(r.total_gb_red, 0) * c.precio_gb_adicional_clp)

            WHEN COALESCE(r.total_gb_red, 0) > c.limite_gb_plan 
            THEN ((COALESCE(r.total_gb_red, 0) - c.limite_gb_plan) * c.precio_gb_adicional_clp) - COALESCE(f.cobro_adicional_facturado, 0)

            ELSE 0
        END AS monto_fuga_estimado_clp

    FROM EstadoCRM c
    LEFT JOIN ConsumoRed r ON c.numero_linea = r.numero_linea
    LEFT JOIN FacturacionDia f ON c.numero_linea = f.numero_linea;
    """

    df_resultado = pd.read_sql(query, ENGINE_DW)

    # 3. Filtrar únicamente las inconsistencias
    df_fugas = df_resultado[df_resultado['clasificacion_fuga'] != 'CORRECTO'].copy()

    # Guardar en base de datos para consumo de Power BI
    df_resultado.to_sql("fact_conciliacion_aseguramiento", ENGINE_DW, if_exists="append", index=False)

    print(f"Procesadas {len(df_resultado):,} líneas. Fugas detectadas: {len(df_fugas):,}")
    return df_fugas

if __name__ == "__main__":
    ejecutar_conciliacion_diaria()

3. 📊 Salidas y Entregables Concretos (Outputs)

Output 1: Reporte Operativo de Discrepancias (CSV / Base de Datos)

El pipeline genera una tabla limpia con los registros accionables para auditoría y corrección inmediata:

Output 2: Alerta Automática de Incidencia a Equipos Clave (Payload JSON / Webhook)

Cuando el script detecta un volumen de fuga superior al umbral crítico (> $5.000.000 CLP diarios), envía un payload a la API de ServiceNow / Jira Service Desk para aislar el problema de red:

{
  "incident_type": "REVENUE_LEAKAGE_CRITICAL",
  "source_system": "Revenue_Assurance_Bot",
  "timestamp": "2026-08-15T03:15:00Z",
  "affected_lines_count": 1420,
  "estimated_daily_loss_clp": 8750000,
  "assigned_group": "Telecom_Core_Network_Provisioning",
  "priority": "P1_HIGH",
  "summary": "Desincronización PCRF vs CRM: 1.420 líneas suspendidas continúan consumiendo datos de red.",
  "recommended_action": "Ejecutar script de purga de sesiones activas en HSS/PCRF Nodo Huechuraba."
}

Output 3: Dashboard Operacional en Power BI

A. Arquitectura del Modelo de Datos (Star Schema):

• Tabla de Hechos: Fact_Conciliacion_Aseguramiento (Millones de registros de consumo y cruce).
• Tablas de Dimensiones: Dim_Calendario, Dim_Paises, Dim_Planes, Dim_Tipos_Fuga.

B. Métricas DAX Clave Implementadas:

// 1. Fuga Total Acumulada ($)
Monto Fuga Total CLP = 
SUM(Fact_Conciliacion_Aseguramiento[Monto_Fuga_Estimado_CLP])

// 2. Tasa de Fuga Operacional (%)
% Fuga sobre Facturacion = 
DIVIDE(
    [Monto Fuga Total CLP],
    SUM(Fact_Conciliacion_Aseguramiento[Cobrado_En_Factura]) + [Monto Fuga Total CLP],
    0
)

// 3. Eficacia de Recuperación / SLA de Corrección
% Discrepancias Resueltas < 24h = 
DIVIDE(
    CALCULATE(COUNTROWS(Fact_Conciliacion_Aseguramiento), Fact_Conciliacion_Aseguramiento[Horas_Resolucion] <= 24),
    COUNTROWS(Fact_Conciliacion_Aseguramiento),
    0
)

C. Distribución Visual del Dashboard:

1. Header: Tarjetas KPI con semáforos (Fuga del mes vs. Meta tolerable < 0.5%, Líneas afectadas, Ahorro recuperado).
2. Gráfico Central: Tendencia diaria de fugas desglosadas por tipo (Subfacturación, Línea No Habilitada, Error de Tarifa).
3. Selector Regional: Filtro interactivo por país (Chile / Perú / Colombia).
4. Tabla de Acción Rápida (Drill-Down): Exportable con un clic para el equipo de Facturación con los IDs y números de línea afectados.

Output 4: Documentación de Gobernanza (Ficha Técnica de KPI y SOP)

Ficha Técnica de Respaldo del KPI

• Identificador: KPI-RA-TELCO-01
• Nombre: Tasa de Fuga de Ingresos por Tráfico No Liquidado.
• Propietario: Subgerencia de Aseguramiento de Ingresos y Control de Gestión.
• Periodicidad: Diaria (Corte acumulado mensual al día 28 de cada mes).
• Fórmula Auditable: (Total_Ingresos_Esperados - Total_Ingresos_Facturados) / Total_Ingresos_Esperados.

Procedimiento Operativo Estándar (SOP - Troubleshooting)

1. Si falla el pipeline de las 02:00 AM:
2. Verificar en el log /var/log/ra_pipeline.log si hubo caída de la VPN hacia el SFTP de CDRs.
3. Ejecutar contingencia manual: python conciliacion.py --fecha YYYY-MM-DD --forzar-recarga.
4. Si las discrepancias aumentan en más de 20% vs. día anterior:
5. Validar con el equipo Comercial si hubo lanzamiento de un nuevo plan o cambio en la tabla de tarifas no notificado.

4. 📈 Impacto y Retorno de Inversión (ROI) del Proyecto

🏢 Real-World Case Study: Data Engineer & Revenue Assurance (Telco)

1. 📌 Business Problem & Context

Company Background

A multinational mobile and fixed telecommunications operator serving regional markets across South America (Chile, Peru, Colombia).

The Revenue Leakage Issue

During the monthly financial close, accounting identified an unexplained discrepancy of $45,000,000 CLP (~$48,000 USD) between data consumed across network towers and amounts billed by the billing engine.

flowchart TD
    A["📡 Core Network (HSS/PCRF/CDRs)<br>Real Traffic"] -->|De-sync / API Timeouts| D{"❌ Revenue Leakage<br>($45M CLP / month)"}
    B["💼 Customer CRM<br>Plans & Line Status"] -->|Queue Desync| D
    C["💳 Billing Engine<br>Charges Invoiced"] -->|Underbilling| D
    D --> E["🔧 Data Engineer & RA<br>Automated Reconciliation Pipeline"]

Root Causes

1. Ghost Traffic: 1,420 suspended or canceled lines continued accessing data services due to HLR/PCRF provisioning timeouts.
2. Unbilled Add-on Bundles: Automatic emergency top-up packages were consumed but skipped during the billing cycle.
3. Manual Inefficiencies: Previous manual weekly audit took 14 man-hours with high risk of human oversight.

2. ⚙️ Automated 3-Way Reconciliation Architecture (Python + SQL)

# Daily unattended ETL & automated reconciliation query
WITH ConsumoRed AS (
    SELECT numero_linea, SUM(gigas_consumidos) AS total_gb_red
    FROM raw_cdrs_trafico
    WHERE fecha_evento = '{fecha_proceso}'
    GROUP BY numero_linea
),
EstadoCRM AS (
    SELECT numero_linea, cliente_id, estado_linea, limite_gb_plan, precio_gb_adicional_clp
    FROM dim_clientes_lineas
),
FacturacionDia AS (
    SELECT numero_linea, SUM(monto_cargo_adicional) AS cobro_adicional_facturado
    FROM fact_cargos_diarios
    WHERE fecha_cargo = '{fecha_proceso}'
    GROUP BY numero_linea
)
SELECT 
    c.cliente_id, c.numero_linea, c.estado_linea,
    COALESCE(r.total_gb_red, 0) AS gb_consumidos_red,
    CASE 
        WHEN c.estado_linea IN ('SUSPENDIDO', 'BAJA') AND COALESCE(r.total_gb_red, 0) > 0.05 
        THEN 'FUGA_LINEA_NO_HABILITADA'
        WHEN COALESCE(r.total_gb_red, 0) > c.limite_gb_plan 
             AND COALESCE(f.cobro_adicional_facturado, 0) < ((COALESCE(r.total_gb_red, 0) - c.limite_gb_plan) * c.precio_gb_adicional_clp)
        THEN 'FUGA_SUBFACTURACION_DATOS'
        ELSE 'CORRECTO'
    END AS clasificacion_fuga
FROM EstadoCRM c
LEFT JOIN ConsumoRed r ON c.numero_linea = r.numero_linea
LEFT JOIN FacturacionDia f ON c.numero_linea = f.numero_linea;

3. 📈 Key Results and ROI

• Execution Time: Reduced from 14 hours/week to 8 minutes/day (98% reduction).
• Leakage Detection: Moved from 30-day retroactive post-close to real-time daily detection (<24 hrs).
• Recovered Revenue: Recovered ~$38.5M CLP / month (~$41,000 USD/month).