kpi-impact-sim 0.1.0

A business-ready engine for KPI impact simulation using Ridge Regression and interpretable feature contributions.

import pyodbc import pandas as pd import datetime import time import win32com.client as win32 import warnings

warnings.filterwarnings("ignore", category=UserWarning, module="pandas.io.sql")

def consultar_y_enviar_alerta(): try: ahora = datetime.datetime.now() print(f"\n⏳ {ahora} - Ejecutando consulta y generando alerta...")

    conn = pyodbc.connect(
        "DRIVER={ODBC Driver 17 for SQL Server};"
        "SERVER=172.17.248.31;"
        "DATABASE=WSP_BLINDAJE_PARTNER;"
        "Trusted_Connection=yes;"
    )


    sql = f"EXEC SP_TKSWSP @FechaInicio = '01-08-2025', @FechaFin = '01-01-2026'"

    df = pd.read_sql(sql, conn)
    df["APELLIDOS Y NOMBRES"] = df["APELLIDOS Y NOMBRES"].apply(lambda x: x.split("_", 1)[-1] if pd.notnull(x) else x)
    df['TMO Asesor'] = pd.to_datetime(df['TMO Asesor'], errors='coerce').dt.time
    df['TMO Asesor'] = pd.to_timedelta(df['TMO Asesor'].astype(str))
    df['DIA'] = df['DIA'].astype(int)

    df_filtrado = df[(df['Periodo'] == '202508') & (df['BaseName'].str.contains('CONTACTADOS_Bitel', case=False, na=False))]

    max_dia = df_filtrado['DIA'].max()
    print(f"✔ MAX DIA_GEST encontrado:", max_dia)

    df_filtrado2 = df[(df['Periodo'] == '202508') & (df['DIA'] == max_dia) & (df['BaseName'].str.contains('CONTACTADOS_Bitel', case=False, na=False))]

    max_tramo = df_filtrado2['HORA'].max()
    print(f"✔ MAX TRAMO encontrado:", max_tramo)

    df_filtradoH = df[(df['Periodo'] == '202508') & (df['DIA'] == max_dia) & (df['HORA'] <= max_tramo) & (df['BaseName'].str.contains('CONTACTADOS_Bitel', case=False, na=False)) & (df['ESTADO DE GESTIÓN'] == 'ACTIVO')]
    df_filtradoD1 = df[(df['Periodo'] == '202508') & (df['DIA'] == max_dia -1) & (df['HORA'] <= max_tramo) & (df['BaseName'].str.contains('CONTACTADOS_Bitel', case=False, na=False)) & (df['ESTADO DE GESTIÓN'] == 'ACTIVO')]
    df_filtradoD7 = df[(df['Periodo'] == '202508') & (df['DIA'] == max_dia - 7) & (df['HORA'] <= max_tramo) & (df['BaseName'].str.contains('CONTACTADOS_Bitel', case=False, na=False)) & (df['ESTADO DE GESTIÓN'] == 'ACTIVO')]

    df_filtradoHC = df[(df['Periodo'] == '202508') & (df['DIA'] == max_dia) & (df['HORA'] <= max_tramo) & (df['BaseName'].str.contains('CONTACTADOS_Bitel', case=False, na=False))]
    df_filtradoD1C = df[(df['Periodo'] == '202508') & (df['DIA'] == max_dia -1) & (df['HORA'] <= max_tramo) & (df['BaseName'].str.contains('CONTACTADOS_Bitel', case=False, na=False))]
    df_filtradoD7C = df[(df['Periodo'] == '202508') & (df['DIA'] == max_dia - 7) & (df['HORA'] <= max_tramo) & (df['BaseName'].str.contains('CONTACTADOS_Bitel', case=False, na=False))]

    df_on = df_filtradoH
    df_d7 = df_filtradoD7
    df_d1 = df_filtradoD1

    df_onC = df_filtradoHC
    df_d7C = df_filtradoD7C
    df_d1C = df_filtradoD1C

    def formatear_timedelta(td):
        if pd.isnull(td):
            return "00:00:00"
        total_seconds = int(td.total_seconds())
        horas = total_seconds // 3600
        minutos = (total_seconds % 3600) // 60
        segundos = total_seconds % 60
        return f"{horas:02}:{minutos:02}:{segundos:02}"

    def tramo_mayor_tmg(df):
        # Filtra solo los gestionados
        df_filtrado = df[df["Q Gestionado"] > 0]
        tmg_por_tramo = (
            df_filtrado.groupby("HORA")["TMO Asesor"]
            .mean()
            .sort_values(ascending=False)
        )
        if not tmg_por_tramo.empty:
            tramo = tmg_por_tramo.index[0]
            tmg = formatear_timedelta(tmg_por_tramo.iloc[0])
            return f"{tramo} ({tmg})"
        else:
            return "Sin datos"


    def asesores_tmg(df, top=True):
        orden = False if top else True
        tmg_por_asesor = (
            df[df["Q Gestionado"] > 0]
            .groupby("APELLIDOS Y NOMBRES")["TMO Asesor"]
            .mean()
            .sort_values(ascending=orden)
            .head(2)
        )
        return [
            f"{asesor} ({formatear_timedelta(tmg)})"
            for asesor, tmg in tmg_por_asesor.items()
        ]


    TMG_actual = df_on["TMO Asesor"].mean()
    tramo_TMG_critico = tramo_mayor_tmg(df_on)
    asesores_mayor_TMG = asesores_tmg(df_on, top=True)
    asesores_menor_TMG = asesores_tmg(df_on, top=False)

    def calcular_indicador(df, numerador, denominador, objetivo):
        total = df[denominador].sum()
        actual = (df[numerador].sum() / total * 100) if total else 0
        alerta = "Alta ✅" if actual >= objetivo else "Baja ⚠️"
        return actual, alerta

    contact_actual, contact_alerta = calcular_indicador(df_onC, "Q Atendido", "Q Tickets", 20)
    efectivo_actual, efectivo_alerta = calcular_indicador(df_onC, "Q Contacto Efectivo", "Q Gestionado", 40)
    retencion_actual, retencion_alerta = calcular_indicador(df_onC, "Q Retenido", "Q Gestionado", 20)

    def peor_tramo(df, col):
        tramo_porc = (
            df.groupby("HORA")[[col, "Q Gestionado"]]
            .sum()
            .query("`Q Gestionado` > 1")
            .assign(porc=lambda x: x[col] / x["Q Gestionado"] * 100)
            .sort_values("porc")
        )

        if not tramo_porc.empty:
            peor_tramo = tramo_porc.index[0]
            valor = tramo_porc.iloc[0]["porc"]
            return f"{peor_tramo} ({valor:.2f}%)"
        else:
            return "Sin datos"

    tramo_contact_critico = peor_tramo(df_onC, "Q Atendido")
    tramo_efectivo_critico = peor_tramo(df_on, "Q Contacto Efectivo")
    tramo_retencion_critico = peor_tramo(df_on, "Q Retenido")

    def peores_asesores(df, col):
        asesores = (
            df.groupby("APELLIDOS Y NOMBRES")[[col, "Q Gestionado"]]
            .sum()
            .query("`Q Gestionado` > 1")
            .assign(porc=lambda x: x[col] / x["Q Gestionado"] * 100)
            .sort_values("porc")
            .head(2)
        )

        return [
            f"{asesor} ({row['porc']:.2f}%)"
            for asesor, row in asesores.iterrows()
        ]

    peores_efectivo = peores_asesores(df_on, "Q Contacto Efectivo")
    peores_retencion = peores_asesores(df_on, "Q Retenido")

    def calcular_kpis(df):
        base = df["Q Tickets"].sum()
        gestionado = df["Q Gestionado"].sum()
        contacto = df["Q Atendido"].sum()
        contacto_efectivo = df["Q Contacto Efectivo"].sum()
        contacto_noefectivo = df["Q Contacto No Efectivo"].sum()
        no_conforme = df["Q No Conforme"].sum()
        conforme = df["Q Conforme"].sum()
        retenido = df["Q Retenido"].sum()
        no_retenido = df["Q No Retenido"].sum()
        df_gestionados = df[df["Q Gestionado"] > 0]
        tmg = df_gestionados["TMO Asesor"].mean()

        return {
            "Base": base,
            "Gestionado": gestionado,
            "Contacto Efectivo": contacto_efectivo,
            "Contacto no Efectivo": contacto_noefectivo,
            "No Conforme": no_conforme,
            "Conforme": conforme,
            "Retenido": retenido,
            "No Retenido": no_retenido,
            "% Contacto / Gestionado": (contacto / base * 100) if gestionado else 0,
            "% Contacto Efectivo / Gestionado": (contacto_efectivo / gestionado * 100) if gestionado else 0,
            "% Contacto No Efectivo / Gestionado": (contacto_noefectivo / gestionado * 100) if gestionado else 0,
            "% No Retenido / Gestionado": (no_retenido / gestionado * 100) if gestionado else 0,
            "% Efectividad / CE": (retenido / contacto_efectivo * 100) if contacto_efectivo else 0,
            "% Efectividad / Gestionado": (retenido / gestionado * 100) if gestionado else 0,
            "Tmg": tmg
        }

    def formatear_timedelta(td):
        if pd.isnull(td):
            return "00:00:00"
        total_seconds = int(td.total_seconds())
        horas = total_seconds // 3600
        minutos = (total_seconds % 3600) // 60
        segundos = total_seconds % 60
        return f"{horas:02}:{minutos:02}:{segundos:02}"

    kpis_on = calcular_kpis(df_onC)
    kpis_d7 = calcular_kpis(df_d7C)
    kpis_d1 = calcular_kpis(df_d1C)

    table_rows = ""
    for kpi, value_on in kpis_on.items():
        value_d7 = kpis_d7[kpi]
        value_d1 = kpis_d1[kpi]
        var_d7 = value_d7
        var_d1 = value_d1
        vs_d1 = value_on - value_d1
        vs_d7 = value_on - value_d7
        if kpi == "Tmg":
            table_rows += f"""
            <tr>
                <td>{kpi}</td>
                <td style='text-align:center;'>{formatear_timedelta(value_on)}</td>
                <td style='text-align:center;'>{formatear_timedelta(value_d1)}</td>
                <td style='text-align:center;'>{formatear_timedelta(value_d7)}</td>
                <td style='text-align:center; color: #2E86C1;'>{formatear_timedelta(vs_d1)}</td>
                <td style='text-align:center; color: #2E86C1;'>{formatear_timedelta(vs_d7)}</td>
            </tr>
            """
        elif "%" in kpi:
            table_rows += f"""
            <tr>
                <td>{kpi}</td>
                <td style='text-align:center;'>{value_on:.2f}%</td>
                <td style='text-align:center;'>{var_d1:.2f}%</td>
                <td style='text-align:center;'>{var_d7:.2f}%</td>
                <td style='text-align:center; color: #2E86C1;'>{vs_d1:.2f}%</td>
                <td style='text-align:center; color: #2E86C1;'>{vs_d7:.2f}%</td>
            </tr>
            """
        else:
            table_rows += f"""
            <tr>
                <td>{kpi}</td>
                <td style='text-align:center;'>{value_on:.0f}</td>
                <td style='text-align:center;'>{var_d1:.0f}</td>
                <td style='text-align:center;'>{var_d7:.0f}</td>
                <td style='text-align:center; color: #2E86C1;'>{vs_d1:.0f}</td>
                <td style='text-align:center; color: #2E86C1;'>{vs_d7:.0f}</td>
            </tr>
            """

    html_table = f"""
    <table style="border-collapse: collapse; width: 60%; font-size: 11px; font-family: Arial, sans-serif;">
    <tr style="background-color: #D6EAF8;">
    <th style="text-align: center;">KPI's</th>
    <th style="text-align: center;">ON<br>{max_dia}</th>
    <th style="text-align: center;">Var(+/-)<br>D-1</th>
    <th style="text-align: center;">Var(+/-)<br>D-7</th>
    <th style="text-align: center; color: #2E86C1;">Var(+/-)<br>Vs D-1</th>
    <th style="text-align: center; color: #2E86C1;">Var(+/-)<br>Vs D-7</th>
    </tr>
    {table_rows}
    </table>
    """


    html_body = f"""
    <h2 style="color:#2E86C1;">WSP CONTACTADOS BITEL</h2>


    <!-- Alerta Contactabilidad -->
    <div style="border:1px solid #dcdcdc; border-left: 6px solid #e67e22; background-color: #fcf3e6; padding:10px; margin-bottom:15px;">
        <h3 style="color:#e67e22;">Alerta | Contactabilidad: {contact_alerta}</h3>
        <p>🎯 <b>Objetivo:</b> 20%</p>
        <p>📈 <b>Actual:</b> {contact_actual:.2f}%</p>
    </div>

    <!-- Alerta TMG -->
    <div style="border:1px solid #dcdcdc; border-left: 6px solid #2E86C1; background-color: #f4faff; padding:10px; margin-bottom:15px;">
        <h3 style="color:#2E86C1;">Alerta | TMG</h3>
        <p>📈 <b>Actual:</b> {formatear_timedelta(TMG_actual)}</p>
        <p>🕑 <b>Tramo crítico:</b> {tramo_TMG_critico}</p>
        <p>👥 <b>Asesores con mayor TMG:</b> {', '.join(asesores_mayor_TMG)}</p>
        <p>👥 <b>Asesores con menor TMG:</b> {', '.join(asesores_menor_TMG)}</p>
    </div>

    <!-- Alerta Contactos Efectivos -->
    <div style="border:1px solid #dcdcdc; border-left: 6px solid #28B463; background-color: #eafaf1; padding:10px; margin-bottom:15px;">
        <h3 style="color:#28B463;">Alerta | Contactos Efectivos: {efectivo_alerta}</h3>
        <p>🎯 <b>Objetivo:</b> 40%</p>
        <p>📈 <b>Actual:</b> {efectivo_actual:.2f}%</p>
        <p>🕑 <b>Tramo crítico:</b> {tramo_efectivo_critico}</p>
        <p>👥 <b>Peores asesores:</b> {', '.join(peores_efectivo)}</p>
    </div>

    <!-- Alerta Retención -->
    <div style="border:1px solid #dcdcdc; border-left: 6px solid #C0392B; background-color: #fdecea; padding:10px; margin-bottom:15px;">
        <h3 style="color:#C0392B;">Alerta | Retención: {retencion_alerta}</h3>
        <p>🎯 <b>Objetivo:</b> 20%</p>
        <p>📈 <b>Actual:</b> {retencion_actual:.2f}%</p>
        <p>🕑 <b>Tramo crítico:</b> {tramo_retencion_critico}</p>
        <p>👥 <b>Peores asesores:</b> {', '.join(peores_retencion)}</p>
    </div>

    <hr>
    {html_table}
    """


    outlook = win32.Dispatch('outlook.application')
    mail = outlook.CreateItem(0)
    mail.To = "[e-mail]"
    mail.CC = "[e-mail]; [e-mail]; [e-mail]; [e-mail]; [e-mail]; [e-mail]; [e-mail]"
    mail.BCC = "[e-mail]; [e-mail]; [e-mail]; [e-mail]; [e-mail]; [e-mail]; [e-mail]; [e-mail]"
    mail.Subject = f"CONTROL KPIs- WSP CONTACTADOS BITEL - PARTNER (hasta Tramo {max_tramo})"
    mail.HTMLBody = html_body
    mail.Send()
    print("📧 Correo con alertas y tabla KPI’s enviado ✅")


except Exception as e:
    print("❌ Error al ejecutar la alerta:", e)

    del df, df_filtrado, df_filtrado2, df_filtradoH, df_filtradoD1, df_filtradoD7, df_on, df_d7, df_d1
    gc.collect()

while True: ahora = datetime.datetime.now() hora_actual = ahora.hour

if 11 <= hora_actual < 22:
    consultar_y_enviar_alerta()
    print("⏳ Esperando 1 hora para la próxima alerta...")
    time.sleep(60 * 60)
else:
    print("⏸ Fuera del horario de envío (10:00-22:00). Esperando 10 minutos...")
    time.sleep(600)