335 lines
18 KiB
JavaScript
335 lines
18 KiB
JavaScript
import { auth } from "../lib/auth";
|
|
import { ownerFilter } from "../lib/authz";
|
|
import { getCohorte, getMotivosCierre, getSystemUsers } from "../lib/queries";
|
|
import { agg, EJECUTIVOS_VENTA } from "../lib/derive";
|
|
import LoginPrompt from "../components/LoginPrompt";
|
|
import OwnerControl from "../components/OwnerControl";
|
|
import ErrorNotice from "../components/ErrorNotice";
|
|
import Panel from "../components/pipeline/Panel";
|
|
import FilterChips from "../components/FilterChips";
|
|
import CohMatriz from "../components/nuevas/CohMatriz";
|
|
import MesesControl from "../components/nuevas/MesesControl";
|
|
import FilterableRow from "../components/FilterableRow";
|
|
import { DYN, money, money2 } from "../lib/constants";
|
|
import { parseFiltParam, applyFilt } from "../lib/filterState";
|
|
|
|
export const dynamic = "force-dynamic";
|
|
|
|
const COH_DIMS = [
|
|
{ k: "owner", lbl: "Ejecutivo" },
|
|
{ k: "origen", lbl: "Origen" },
|
|
{ k: "tipo", lbl: "Tipo de oportunidad" },
|
|
{ k: "linea", lbl: "Línea de negocio" },
|
|
{ k: "campaign", lbl: "Campaña" },
|
|
];
|
|
const COH_METRICAS = [
|
|
{ k: "pct", lbl: "% Ganada", rgb: "30,113,184", fmt: (v) => (v == null ? "—" : `${v}%`), calc: (a) => (a.length ? Math.round((a.filter((o) => o.estado === "Ganada").length / a.length) * 100) : null) },
|
|
{ k: "pctP", lbl: "% Perdida", rgb: "178,58,46", fmt: (v) => (v == null ? "—" : `${v}%`), calc: (a) => (a.length ? Math.round((a.filter((o) => o.estado === "Perdida").length / a.length) * 100) : null) },
|
|
{ k: "n", lbl: "Creadas", rgb: "30,113,184", fmt: (v) => (v == null ? "—" : `${v}`), calc: (a) => a.length || null },
|
|
{ k: "mrr", lbl: "MRR ganado", rgb: "30,113,184", fmt: (v) => (v == null ? "—" : money(v)), calc: (a) => { const s = a.filter((o) => o.estado === "Ganada").reduce((x, o) => x + o.val, 0); return a.length ? s : null; } },
|
|
];
|
|
const DIM_LABELS = { owner: "Ejecutivo", origen: "Origen", tipo: "Tipo", linea: "Línea", campaign: "Campaña", mesCohorte: "Mes de creación", motivo: "Motivo de cierre", competidor: "Competidor" };
|
|
|
|
const MESLBL = {
|
|
"01": "Ene", "02": "Feb", "03": "Mar", "04": "Abr", "05": "May", "06": "Jun",
|
|
"07": "Jul", "08": "Ago", "09": "Sep", "10": "Oct", "11": "Nov", "12": "Dic",
|
|
};
|
|
const mesLbl = (m) => (m ? `${MESLBL[m.slice(5, 7)] || m.slice(5, 7)} ${m.slice(0, 4)}` : "—");
|
|
const COH_TIPOS = {
|
|
venta: { lbl: "Venta y expansión", tipos: ["Venta - Cliente Nuevo", "Up-Selling", "Cross-Selling", "Plan de Cuenta"] },
|
|
renov: { lbl: "Renovaciones", tipos: ["Contrato vencido", "Próximo a Vencer en 30/60/90"] },
|
|
todas: { lbl: "Todas", tipos: null },
|
|
};
|
|
const ESTADOS = [
|
|
{ k: "Ganada", color: "#2e8b57" },
|
|
{ k: "Perdida", color: "var(--pierde)" },
|
|
{ k: "Abierta", color: "#1e71b8" },
|
|
];
|
|
|
|
function BarraCohorte({ mes, rows }) {
|
|
const tot = rows.length || 1;
|
|
return (
|
|
<FilterableRow pairs={[["mesCohorte", mes]]} className="row" style={{ display: "flex", alignItems: "center", gap: 8, marginBottom: 6 }} title={`Ver solo ${mesLbl(mes)}`}>
|
|
<span style={{ width: 70, fontSize: 12 }}>{mesLbl(mes)}</span>
|
|
<div style={{ flex: 1, display: "flex", height: 22, borderRadius: 4, overflow: "hidden", background: "#eef2f5" }}>
|
|
{ESTADOS.map(({ k, color }) => {
|
|
const n = rows.filter((o) => o.estado === k).length;
|
|
if (!n) return null;
|
|
const pct = (n / tot) * 100;
|
|
return (
|
|
<div
|
|
key={k}
|
|
title={`${k}: ${n} de ${rows.length} (${Math.round(pct)}%)`}
|
|
style={{
|
|
width: `${pct}%`, background: color, display: "flex", alignItems: "center",
|
|
justifyContent: "center", color: "#fff", fontSize: 11, fontWeight: 700,
|
|
}}
|
|
>
|
|
{pct > 11 ? `${n} (${Math.round(pct)}%)` : ""}
|
|
</div>
|
|
);
|
|
})}
|
|
</div>
|
|
<span style={{ width: 36, textAlign: "right", fontSize: 12 }}>{rows.length}</span>
|
|
</FilterableRow>
|
|
);
|
|
}
|
|
|
|
export default async function NuevasPage({ searchParams }) {
|
|
const session = await auth();
|
|
if (!session?.email) return <LoginPrompt />;
|
|
|
|
const sp = await searchParams;
|
|
const { owner, tipos: tiposParam } = sp;
|
|
const mesesSel = typeof sp.meses === "string" && sp.meses ? sp.meses.split(",") : null;
|
|
const scope = ownerFilter(session, owner);
|
|
const cohTipos = COH_TIPOS[tiposParam] ? tiposParam : "venta";
|
|
const filt = parseFiltParam(sp.f);
|
|
const cohd = COH_DIMS.some((d) => d.k === sp.cohd) ? sp.cohd : "owner";
|
|
const cohm = COH_METRICAS.some((m) => m.k === sp.cohm) ? sp.cohm : "pct";
|
|
|
|
let arrAll, allUsers;
|
|
try {
|
|
[arrAll, allUsers] = await Promise.all([
|
|
getCohorte(session.accessToken, scope),
|
|
session.isAdmin ? getSystemUsers(session.accessToken) : Promise.resolve([]),
|
|
]);
|
|
} catch (error) {
|
|
return <ErrorNotice error={error} />;
|
|
}
|
|
const owners = allUsers.filter((u) => EJECUTIVOS_VENTA.includes(u.name));
|
|
const tiposFiltro = COH_TIPOS[cohTipos].tipos;
|
|
const arrTipo = tiposFiltro ? arrAll.filter((o) => tiposFiltro.includes(o.tipo)) : arrAll;
|
|
const mesesDisp = [...new Set(arrTipo.map((o) => o.mesCohorte).filter(Boolean))].sort();
|
|
const mesesActivos = mesesSel ? mesesDisp.filter((m) => mesesSel.includes(m)) : mesesDisp;
|
|
const mesesEfectivos = mesesActivos.length ? mesesActivos : mesesDisp;
|
|
const arrMes = arrTipo.filter((o) => mesesEfectivos.includes(o.mesCohorte));
|
|
|
|
// Motivo y competidor de cierre solo existen para las perdidas (opportunityclose).
|
|
let arrEnr = arrMes;
|
|
try {
|
|
const perdidasIds = arrMes.filter((o) => o.estado === "Perdida").map((o) => o.id);
|
|
const motivos = await getMotivosCierre(perdidasIds, session.accessToken);
|
|
arrEnr = arrMes.map((o) =>
|
|
o.estado === "Perdida"
|
|
? { ...o, motivo: motivos[o.id]?.motivo || "Sin registrar", competidor: motivos[o.id]?.competidor || "Sin registrar" }
|
|
: o
|
|
);
|
|
} catch (error) {
|
|
return <ErrorNotice error={error} />;
|
|
}
|
|
const arr = applyFilt(arrEnr, filt);
|
|
|
|
const linkFor = (t) => {
|
|
const params = new URLSearchParams();
|
|
if (owner) params.set("owner", owner);
|
|
if (sp.meses) params.set("meses", sp.meses);
|
|
params.set("tipos", t);
|
|
return `/nuevas?${params.toString()}`;
|
|
};
|
|
|
|
const meses = [...new Set(arr.map((o) => o.mesCohorte).filter(Boolean))].sort();
|
|
const g = arr.filter((o) => o.estado === "Ganada");
|
|
const p = arr.filter((o) => o.estado === "Perdida");
|
|
const ab = arr.filter((o) => o.estado === "Abierta");
|
|
const cerr = g.length + p.length;
|
|
const pct = (x) => (arr.length ? Math.round((x / arr.length) * 100) : 0);
|
|
const mrrG = g.reduce((a, o) => a + o.val, 0);
|
|
|
|
const cohAgg = (key) => agg(arr, key, (o) => (o.estado === "Ganada" ? o.val : 0));
|
|
|
|
const cerradas = arr.filter((o) => o.diasACierre != null);
|
|
const tramos = [
|
|
{ lbl: "Mismo día", max: 0 }, { lbl: "1 a 7 días", max: 7 }, { lbl: "8 a 30 días", max: 30 },
|
|
{ lbl: "31 a 90 días", max: 90 }, { lbl: "Más de 90 días", max: Infinity },
|
|
];
|
|
let prev = -1;
|
|
const latRows = tramos.map((t) => {
|
|
const n = cerradas.filter((o) => o.diasACierre > prev && o.diasACierre <= t.max).length;
|
|
prev = t.max;
|
|
return { k: t.lbl, n };
|
|
});
|
|
const maxLat = Math.max(...latRows.map((r) => r.n), 1);
|
|
const medianaDias = (() => {
|
|
const a = cerradas.map((o) => o.diasACierre).sort((x, y) => x - y);
|
|
return a.length ? a[Math.floor(a.length / 2)] : 0;
|
|
})();
|
|
const viejas = arr.filter((o) => o.diasAbierta != null && o.diasAbierta > 90);
|
|
|
|
const detalle = [...arr].sort((a, b) => (b.estado === "Ganada" ? b.val : b.est) - (a.estado === "Ganada" ? a.val : a.est)).slice(0, 300);
|
|
|
|
return (
|
|
<div style={{ padding: 24 }}>
|
|
<div style={{ display: "flex", justifyContent: "space-between", alignItems: "center", flexWrap: "wrap", gap: 12 }}>
|
|
<h2>Cohorte de Oportunidades</h2>
|
|
<div style={{ display: "flex", gap: 12, flexWrap: "wrap", alignItems: "flex-end" }}>
|
|
<MesesControl opciones={mesesDisp.map((m) => ({ k: m, lbl: mesLbl(m) }))} seleccion={mesesEfectivos} />
|
|
<OwnerControl owners={owners} current={scope.locked ? [session.email] : scope.emails} locked={scope.locked} lockedLabel={session.email} />
|
|
</div>
|
|
</div>
|
|
|
|
<div className="seg-tabs" style={{ marginBottom: 16, flexWrap: "wrap" }}>
|
|
{Object.entries(COH_TIPOS).map(([k, v]) => (
|
|
<a key={k} href={linkFor(k)}>
|
|
<button className={cohTipos === k ? "on" : ""} type="button">{v.lbl}</button>
|
|
</a>
|
|
))}
|
|
</div>
|
|
|
|
{arr.length === 0 ? (
|
|
<p>No hay oportunidades creadas en el período y alcance seleccionados.</p>
|
|
) : (
|
|
<>
|
|
<div className="kpis">
|
|
<div className="kpi"><span className="n">{arr.length}</span><span className="l">Oportunidades creadas (cohorte)</span></div>
|
|
<div className="kpi"><span className="n" style={{ color: "var(--gana)" }}>{g.length}</span><span className="l">Hoy Ganadas · {pct(g.length)}%</span></div>
|
|
<div className="kpi"><span className="n pierde">{p.length}</span><span className="l">Hoy Perdidas · {pct(p.length)}%</span></div>
|
|
<div className="kpi"><span className="n">{ab.length}</span><span className="l">Todavía Abiertas · {pct(ab.length)}%</span></div>
|
|
<div className="kpi"><span className="n">{cerr ? `${Math.round((g.length / cerr) * 100)}%` : "n/d"}</span><span className="l">Efectividad (gana ÷ resueltas)</span></div>
|
|
<div className="kpi"><span className="n">{money(mrrG)}</span><span className="l">MRR ganado por esta cohorte</span></div>
|
|
</div>
|
|
|
|
<div className="callout info">
|
|
Está página analiza el <b>"Cohorte"</b>, es decir el comportamiento de las oportunidades según su{" "}
|
|
<b>"Fecha de Creación"</b>. Ayuda a entender que pasó con las oportunidades que se crearon cada mes.
|
|
<br /><br />
|
|
De las <b>{arr.length}</b> oportunidades creadas en los últimos 6 meses, hoy {g.length} están ganadas, {p.length}{" "}
|
|
perdidas y {ab.length} siguen abiertas. Las cohortes recientes tienen más abiertas solo porque son
|
|
jóvenes — mirá "% resuelto" y la latencia antes de comparar. Plan de Cuenta y los leads de
|
|
formularios/bots convierten poco <b>por diseño</b> (los genera telemarketing en volumen); el error a
|
|
buscar ahí es lentitud para calificar, no baja conversión.
|
|
</div>
|
|
|
|
<FilterChips dimLabels={DIM_LABELS} />
|
|
|
|
<div className="panel">
|
|
<h3>Desenlace por cohorte de creación (estado de hoy)</h3>
|
|
<p className="hint">Cada barra es el 100% de lo creado ese mes</p>
|
|
{meses.map((m) => <BarraCohorte key={m} mes={m} rows={arr.filter((o) => o.mesCohorte === m)} />)}
|
|
</div>
|
|
|
|
<div style={{ marginTop: 18 }}>
|
|
<CohMatriz arr={arr} meses={meses} dims={COH_DIMS} metricas={COH_METRICAS} activeDimKey={cohd} activeMetKey={cohm} mesLbl={mesLbl} />
|
|
</div>
|
|
|
|
<div className="grid2" style={{ marginTop: 18 }}>
|
|
<div className="panel">
|
|
<h3>Latencia de decisión</h3>
|
|
<p className="hint">
|
|
Días entre crear y cerrar, de las {cerradas.length} ya resueltas
|
|
</p>
|
|
{latRows.map((r) => (
|
|
<div className="row static" key={r.k}>
|
|
<div className="bl"><span className="nm">{r.k}</span></div>
|
|
<div className="bar-cell"><div className="bar-fill" style={{ width: `${(r.n / maxLat) * 100}%` }} /></div>
|
|
<span className="v">{r.n}</span>
|
|
<span className="c">{cerradas.length ? Math.round((r.n / cerradas.length) * 100) : 0}%</span>
|
|
</div>
|
|
))}
|
|
<p className="hint" style={{ marginTop: 10 }}>
|
|
Mediana: <b>{medianaDias} días</b> para resolver.
|
|
{viejas.length > 0 && (
|
|
<> Y hay <b>{viejas.length}</b> todavía abiertas con más de 90 días de vida ({money(viejas.reduce((a, o) => a + o.est, 0))} estimado).</>
|
|
)}
|
|
</p>
|
|
</div>
|
|
<Panel title="Por Origen de la Oportunidad" hint="Barra = creadas · monto = MRR ya ganado" rows={cohAgg("origen")} top={8} dimKey="origen" />
|
|
</div>
|
|
|
|
<div className="grid2" style={{ marginTop: 18 }}>
|
|
<Panel title="Por Tipo de Oportunidad" hint="Barra = creadas · monto = MRR ya ganado" rows={cohAgg("tipo")} dimKey="tipo" />
|
|
<Panel title="Por Ejecutivo" hint="Barra = creadas · monto = MRR ya ganado" rows={cohAgg("owner")} dimKey="owner" />
|
|
</div>
|
|
<div className="grid2" style={{ marginTop: 18 }}>
|
|
<Panel title="Por Línea de Negocio" hint="Barra = creadas · monto = MRR ya ganado" rows={cohAgg("linea")} dimKey="linea" />
|
|
<Panel title="Por Campaña de Origen" hint="Barra = creadas · monto = MRR ya ganado" rows={cohAgg("campaign")} top={7} dimKey="campaign" />
|
|
</div>
|
|
|
|
<div className="grid2" style={{ marginTop: 18 }}>
|
|
<Panel title="Motivo de cierre" hint="Solo perdidas de la cohorte · barra = cantidad · monto = MRR estimado" rows={agg(p, "motivo", (o) => o.est)} top={8} dimKey="motivo" />
|
|
<Panel title="Competidor" hint="Solo perdidas de la cohorte · barra = cantidad · monto = MRR estimado" rows={agg(p.filter((o) => o.competidor && o.competidor !== "Ninguno"), "competidor", (o) => o.est)} top={8} dimKey="competidor" />
|
|
</div>
|
|
|
|
<div className="tw fit" style={{ marginTop: 18 }}>
|
|
<div className="tw-title">
|
|
<h3>Cohortes en números</h3>
|
|
|
|
</div>
|
|
<div className="ts" style={{ maxHeight: "none" }}>
|
|
<table>
|
|
<thead>
|
|
<tr>
|
|
<th>Cohorte</th><th className="num">Creadas</th><th className="num">Ganadas</th><th className="num">% Gana</th>
|
|
<th className="num">Perdidas</th><th className="num">% Pierde</th><th className="num">Abiertas</th>
|
|
<th className="num">% Resuelto</th><th className="num">MRR ganado</th><th className="num">Edad prom.</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody>
|
|
{meses.map((m) => {
|
|
const c = arr.filter((o) => o.mesCohorte === m);
|
|
const cg = c.filter((o) => o.estado === "Ganada"), cp = c.filter((o) => o.estado === "Perdida"), ca = c.filter((o) => o.estado === "Abierta");
|
|
const cpct = (x) => (c.length ? `${Math.round((x / c.length) * 100)}%` : "—");
|
|
const cerrC = cg.length + cp.length;
|
|
const edad = c.length ? Math.round(c.reduce((s, o) => s + (o.diasACierre ?? o.diasAbierta ?? 0), 0) / c.length) : 0;
|
|
return (
|
|
<FilterableRow as="tr" pairs={[["mesCohorte", m]]} key={m} title={`Ver solo ${mesLbl(m)}`}>
|
|
<td><b>{mesLbl(m)}</b></td>
|
|
<td className="num">{c.length}</td>
|
|
<td className="num" style={{ color: "var(--gana)", fontWeight: 700 }}>{cg.length}</td>
|
|
<td className="num">{cpct(cg.length)}</td>
|
|
<td className="num" style={{ color: "var(--pierde)", fontWeight: 700 }}>{cp.length}</td>
|
|
<td className="num">{cpct(cp.length)}</td>
|
|
<td className="num">{ca.length}</td>
|
|
<td className="num">{c.length ? `${Math.round((cerrC / c.length) * 100)}%` : "—"}</td>
|
|
<td className="num">{money(cg.reduce((s, o) => s + o.val, 0))}</td>
|
|
<td className="num">{edad} d</td>
|
|
</FilterableRow>
|
|
);
|
|
})}
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
</div>
|
|
|
|
<div className="panel" style={{ paddingBottom: 6, marginTop: 18 }}>
|
|
<h3>Detalle de la cohorte</h3>
|
|
<p className="hint">Las 300 de mayor monto</p>
|
|
</div>
|
|
<div className="tw">
|
|
<div className="ts">
|
|
<table>
|
|
<thead>
|
|
<tr>
|
|
<th>Cliente / Oportunidad</th><th>Estado</th><th>Ejecutivo</th><th>Tipo</th><th>Origen</th>
|
|
<th className="num">MRR</th><th>Creada</th><th className="num">Días</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody>
|
|
{detalle.map((o) => (
|
|
<tr key={o.id}>
|
|
<td className="dual-cel">
|
|
<a className="opp" href={`${DYN}${o.id}`} target="_blank" rel="noopener" title={o.name}>{o.name}</a>
|
|
<span className="sub">{o.cliente || "—"}</span>
|
|
</td>
|
|
<td>
|
|
<span className={o.estado === "Ganada" ? "gana" : o.estado === "Perdida" ? "pierde" : ""}>{o.estado}</span>
|
|
</td>
|
|
<td className="elip">{o.owner}</td>
|
|
<td className="elip" title={o.tipo || ""}>{o.tipo || "—"}</td>
|
|
<td className="elip" title={o.origen || ""}>{o.origen || "—"}</td>
|
|
<td className="num">{money2(o.estado === "Ganada" ? o.val : o.est)}</td>
|
|
<td>{o.creada || "—"}</td>
|
|
<td className="num" title={o.diasACierre != null ? "Días que tardó en cerrarse" : "Días que lleva abierta"}>
|
|
{o.diasACierre != null ? o.diasACierre : o.diasAbierta != null ? `${o.diasAbierta}…` : "—"}
|
|
</td>
|
|
</tr>
|
|
))}
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
</div>
|
|
</>
|
|
)}
|
|
</div>
|
|
);
|
|
}
|