AnalíticaIntermedio10 min de lectura

Cómo detectar regresión de etapas en tu CRM de ventas

Detecta cuándo los negocios retroceden en tu pipeline, márcalos en Google Sheets y crea un resumen semanal para tu director de ventas.

Esta guía te muestra cómo detectar la regresión de etapas en Google Sheets usando el historial de etapas exportado de tu CRM, marcar cada retroceso con una fórmula y resumirlo en un dashboard que tu director de ventas pueda revisar en el standup del lunes. Cuando un negocio cae de Proposal a Discovery, esa es una señal que vale la pena investigar, y la mayoría de los dashboards de CRM no te la van a mostrar por su cuenta.

Lo que necesitarás

Un historial de etapas exportado de tu CRM (HubSpot Pipeline Activity, Salesforce OpportunityFieldHistory o equivalente) con al menos estas columnas: Deal ID, Stage Name, Stage Changed Date y Rep/Owner
Google Sheets con ese historial cargado, entre 5,000 y 80,000 filas dependiendo del período y la actividad de tu equipo
Familiaridad con VLOOKUP, QUERY y el ordenamiento manual de rangos
Una lista definida de etapas del pipeline en el orden correcto (si tus representantes usan cinco nombres distintos para "Discovery", resuelve eso primero)

Guía paso a paso

Exporta los datos correctos de tu CRM

Los reportes estándar de negocios en un CRM te dan una fila por negocio, mostrando en qué etapa está cada uno en este momento. Eso no sirve para lo que necesitamos aquí. Lo que necesitas es una fila por cada cambio de etapa: un historial que muestre cada vez que un negocio se movió, en qué dirección y cuándo. Son dos exportaciones muy distintas, y vale la pena confirmar que tienes la correcta antes de construir nada.

En HubSpot, esto está en Reports > Sales > Deal Stage History. En Salesforce, consulta el objeto OpportunityFieldHistory filtrado a Field = 'StageName'. En Pipedrive, exporta el "Pipeline change log" desde Reports. Todo CRM importante tiene este dato, pero la ruta para llegar a él varía.

Descarga como CSV con al menos estas columnas: deal ID, stage name, fecha del cambio de etapa y deal owner
Incluye el monto del negocio si está disponible: una regresión en un negocio de $180,000 es una conversación muy distinta a una de $3,000
Exporta al menos 6 meses de historial para análisis de tendencias; una sola semana no te dice casi nada sobre patrones
Espera entre 15,000 y 60,000 filas para un equipo de más de 10 representantes con 6 meses de datos

Pro Tip

Si tu exportación incluye tanto "From Stage" como "To Stage" como columnas separadas, usa ese formato. Te saltas dos pasos más adelante. Si solo obtienes la etapa actual por evento, calcularás la etapa anterior tú mismo en el Paso 4.

Audita y limpia los nombres de etapas

Antes de construir cualquier cosa, crea una tabla dinámica en tu columna de etapas y revisa cada valor distinto. Este es el paso donde descubrirás que "Demo", "Product Demo" y "Demo/Presentation" son la misma etapa ingresada de forma diferente por distintos representantes, y ahora cada fórmula de comparación las tratará como tres etapas separadas y perderá dos tercios de tus regresiones.

Crea una hoja de limpieza con dos columnas: "Raw Name" en la columna A y "Canonical Name" en la columna B. Luego agrega una columna de limpieza junto a tus datos sin procesar:

=IFERROR(VLOOKUP(TRIM(C2), Cleanup!$A:$B, 2, FALSE), C2): el IFERROR deja pasar cualquier nombre que ya coincida correctamente
El TRIM es indispensable; las exportaciones de CRM suelen traer espacios al inicio o al final que son invisibles en la celda pero rompen cualquier coincidencia exacta
Usa =UNIQUE(C2:C) en una columna auxiliar para extraer todos los nombres de etapas distintos antes de construir la tabla de limpieza
Una vez que la columna de limpieza se vea correcta, cópiala y usa Pegar especial > Solo valores sobre tu columna de etapas, luego elimina la columna auxiliar

Crea una tabla de referencia de orden de etapas

La regresión de etapas solo tiene sentido una vez que defines numéricamente qué significa "avanzar". Crea una nueva hoja llamada StageOrder con dos columnas: Stage y Order. Lista cada nombre de etapa canónico y asígnale un número entero. La dirección de los números es lo que probará tu lógica de regresión.

Stage	Order
Prospecting	1
Qualification	2
Discovery	3
Demo	4
Proposal	5
Negotiation	6
Closed Won	7
Closed Lost	99

Closed Lost recibe el valor 99, no 8. Asignarle 8 marcaría como regresión un negocio que pasa de Closed Lost a Negotiation, cuando en realidad es una reapertura, algo diferente que conviene rastrear por separado.

Usa solo números enteros, sin decimales, para que las comparaciones sean inequívocas
Si tu pipeline tiene rutas paralelas (por ejemplo, "Technical Evaluation" puede correr junto a "Proposal"), asigna el mismo número de orden a las etapas paralelas y decide en equipo si el movimiento entre rutas cuenta como regresión
Agrega una columna "Status" a StageOrder (Active/Deprecated) para gestionar etapas que fueron renombradas durante el año sin perder datos históricos
Mantén esta tabla actualizada cuando el administrador del CRM agregue nuevas etapas: una etapa faltante producirá un error de VLOOKUP capturado por IFERROR, lo que te indica que algo necesita corrección

Pro Tip

Bloquea la hoja StageOrder para que un representante o el administrador del CRM no pueda editar los números accidentalmente durante el análisis. Protégela desde Datos > Proteger hojas y rangos.

Ordena los datos y asigna los números de orden de etapa

Este es el eje sobre el que gira todo. Necesitas las filas ordenadas por Deal_ID de forma ascendente y luego por Changed_Date de forma ascendente. Eso coloca los eventos de cada negocio en orden cronológico, lo que te permite comparar cada fila con la anterior dentro del mismo negocio. Si el ordenamiento está mal, todas las marcas de regresión serán incorrectas.

En Google Sheets: Datos > Ordenar rango > Ordenar por Deal_ID (A-Z), luego agrega un segundo nivel de ordenamiento por Changed_Date (A-Z). Con 40,000 filas, esto toma aproximadamente 10-15 segundos.

Ahora agrega tres columnas auxiliares a tus datos:

Columna G (Stage_Order): orden numérico de la etapa de cada fila:

=IFERROR(VLOOKUP(C2, StageOrder!$A:$B, 2, FALSE), 0)

Columna H (Prev_Stage_Name): el nombre de la etapa de la fila anterior, pero solo cuando pertenece al mismo negocio:

=IF(A2=A1, C1, "")

Columna I (Prev_Stage_Order): la misma verificación de negocio para el orden numérico:

=IF(A2=A1, G1, "")

Arrastra las tres fórmulas desde la fila 2 hasta tu última fila de datos
Las filas donde cambia el Deal_ID (primer evento de un nuevo negocio) devuelven vacío en H e I, lo cual es correcto: no hay etapa anterior con la que comparar
Cualquier fila donde G devuelva 0 indica un nombre de etapa no reconocido; filtra por ceros y revisa tu tabla de limpieza del Paso 2

Pro Tip

Con más de 50,000 filas, estas columnas auxiliares tardan 20-40 segundos en recalcularse con cada edición. Después de la configuración inicial, copia las columnas G a I y usa Pegar especial > Solo valores para fijarlas. Vuelve a ejecutarlas cada semana cuando cargues datos nuevos.

Marca los eventos de regresión

Con los datos ordenados y los órdenes numéricos de etapa en su lugar, la marca de regresión se reduce a dos comparaciones: ¿hay una etapa anterior con la que comparar (es decir, la columna I no está vacía)? y ¿el número de orden actual es menor que el anterior? Un número de orden menor significa que el negocio retrocedió.

Columna J (Regression_Flag):

=IF(I2="", "First Entry", IF(G2<I2, "Regression", IF(G2=I2, "No Change", "Forward")))

Columna K (Regression_Path): la transición exacta de etapa, legible para la tabla del director:

=IF(J2="Regression", H2&" → "&C2, "")

Esto te dará entradas como "Proposal → Discovery" y "Negotiation → Demo", las transiciones que tu VP de Ventas querrá analizar en detalle.

Filtra la columna J por "Regression" y verifica manualmente 10-15 filas antes de confiar en el resultado a escala
Las entradas "No Change" aparecen cuando un negocio se guardó en el CRM sin cambiar de etapa (es común cuando los representantes editan otros campos como la fecha de cierre o el monto); son ruido, no regresiones
Presta atención a los negocios que pasan dos veces por la misma etapa: esto aparece como "Forward", luego "No Change" y luego "Regression" en secuencia, lo que suele indicar un problema de ingreso de datos que vale la pena reportar al administrador del CRM
Las filas "First Entry" quedan excluidas del análisis automáticamente porque la columna I está vacía; no necesitas IFERROR en las consultas posteriores

Construye resúmenes de regresión con QUERY

Con todas las regresiones marcadas en la columna J, las agregaciones que importan para los reportes de operaciones son: qué representantes tienen más regresiones, qué transiciones de etapa ocurren con mayor frecuencia y si la tendencia mensual está mejorando o empeorando. QUERY maneja más de 80,000 filas en menos de 2 segundos para estas agregaciones. No uses COUNTIFS aquí: se vuelve lento por encima de 30,000 filas en una hoja con otros cálculos activos.

Crea una hoja llamada Regression_Summary. Comienza con estas tres consultas:

Regresiones por representante:

=QUERY(Data!A:K, "SELECT E, COUNT(A) WHERE J='Regression' GROUP BY E ORDER BY COUNT(A) DESC LABEL E 'Rep', COUNT(A) 'Regression Count'", 1)

Rutas de regresión más comunes:

=QUERY(Data!A:K, "SELECT K, COUNT(A) WHERE J='Regression' GROUP BY K ORDER BY COUNT(A) DESC LIMIT 10 LABEL K 'Regression Path', COUNT(A) 'Count'", 1)

Tendencia mensual:

=QUERY(Data!A:K, "SELECT YEAR(D), MONTH(D), COUNT(A) WHERE J='Regression' GROUP BY YEAR(D), MONTH(D) ORDER BY YEAR(D) DESC, MONTH(D) DESC LABEL YEAR(D) 'Year', MONTH(D) 'Month', COUNT(A) 'Regressions'", 1)

Reemplaza Data!A:K con el nombre real de tu hoja y pestaña
Las funciones de fecha YEAR() y MONTH() de QUERY requieren que tu columna de fecha sea un valor de fecha real de Google Sheets, no una cadena de texto; si las fechas se importaron como texto (algo común en exportaciones de Salesforce), usa =DATEVALUE(D2) para convertirlas antes de ejecutar estas consultas
Agrega una columna de tasa de regresión junto a la tabla de representantes: las regresiones divididas entre el total de eventos de etapa por representante cuentan una historia más honesta que el conteo absoluto (un representante con 200 negocios y 20 regresiones tiene la misma tasa del 10% que uno con 30 negocios y 3 regresiones, pero los conteos absolutos parecen muy diferentes)
Si tu columna Changed_Date tiene formatos mixtos como "2024-01-15", "1/15/24" y "15 Jan 2024" en la misma columna (algo que ocurre cuando las exportaciones provienen de múltiples regiones del CRM), necesitas una normalización de fechas antes de que QUERY pueda filtrar por fecha

Pro Tip

Los formatos de fecha mixtos son un problema de limpieza aparte. Una combinación de IFERROR, DATEVALUE y REGEXEXTRACT puede analizar la mayoría de los formatos, pero reserva entre 30 y 60 minutos la primera vez que te encuentres con una columna muy mezclada.

Construye la pestaña del dashboard para el director

La hoja de resumen es para análisis. La pestaña del dashboard es lo que se proyecta el lunes en la mañana. Limítala a 3 paneles: cifras clave, un desglose por representante y las rutas de regresión principales. Cualquier cosa adicional convierte la hoja en algo que tu director deja de consultar.

Un dashboard que se actualiza automáticamente requiere que las fórmulas QUERY extraigan datos en vivo, así que no fijes los valores aquí como lo hiciste con las columnas auxiliares en el Paso 4.

Panel 1 (cifras clave):** Dos conteos QUERY con límites de fecha, uno para esta semana y otro para la semana anterior, más una celda de resta simple que muestre el delta. Formato condicional en rojo si las regresiones aumentaron, verde si bajaron.
Panel 2 (tabla de representantes del trimestre):** Usa el QUERY de representantes del Paso 6, filtrado desde la fecha de inicio del trimestre actual con DATE(YEAR(TODAY()), MONTH(TODAY())-MOD(MONTH(TODAY())-1, 3), 1) como límite inferior. Limita a 5 filas con LIMIT 5 para que la tabla tenga un tamaño fijo.
Panel 3 (rutas de regresión principales):** El QUERY de rutas limitado al top 5, con una nota sobre qué porcentaje del total de regresiones representa cada ruta. La columna de porcentaje es aritmética manual, pero vale la pena agregarla.
Agrega una celda "Última actualización" con =TEXT(NOW(), "MMM D, YYYY") para que cualquiera que abra el archivo sepa si los datos son recientes o tienen dos semanas de antigüedad
Bloquea la pestaña del dashboard desde Datos > Proteger hojas y rangos: una pulsación accidental en la celda equivocada romperá una fórmula QUERY y no será obvio por qué hasta que alguien note que los números dejaron de actualizarse

Conclusión

Lo que construiste es una capa de detección de regresiones que funciona con cualquier exportación de CRM: mapeo de orden de etapas, comparación fila a fila protegida por verificaciones de Deal_ID y agregaciones QUERY que escalan por encima de 80,000 filas sin ralentizarse. El dashboard del director te da una respuesta sólida a la pregunta "¿está mejorando la salud del pipeline?" en lugar de un encogimiento de hombros y una promesa de investigar.

La siguiente pregunta que la mayoría de los equipos hace después de ejecutar esto durante un mes es si pueden automatizar la actualización semanal de datos. Ahí es donde el proceso manual llega a su límite: la lógica de detección es sólida, pero alguien todavía tiene que descargar, limpiar, ordenar y volver a cargar la exportación cada semana. Elige un plan de ModelMonkey, funciona tanto en Google Sheets como en Excel.

Preguntas frecuentes

¿Cuál es la diferencia entre regresión de etapa y reapertura de un negocio?

La regresión de etapa es un negocio que retrocede dentro de un pipeline activo (de Proposal a Discovery). Una reapertura es un negocio marcado como Closed Lost que vuelve a cualquier etapa activa. Asignar el valor 99 a Closed Lost en tu tabla StageOrder evita que las reaperturas aparezcan como regresiones, ya que cualquier etapa activa (órdenes 1-6) es numéricamente menor que 99, y la fórmula lo lee como "Forward" en lugar de "Regression". Rastrea las reaperturas por separado filtrando las filas donde Prev_Stage_Name sea igual a "Closed Lost".

Mi CRM solo exporta la etapa actual del negocio, no el historial de etapas. ¿Puedo detectar regresiones de todas formas?

Sí, pero necesitarás 2 instantáneas tomadas en momentos distintos. Exporta la lista completa de negocios esta semana y de nuevo la semana siguiente. Usa VLOOKUP para comparar la etapa de la semana pasada con la exportación de esta semana por Deal ID. Donde el orden de etapa actual sea menor que el de la instantánea anterior, eso es una regresión. La desventaja es que solo detectarás regresiones que ocurrieron entre las dos fechas de exportación: varios retrocesos dentro de la misma semana se colapsan en una sola señal.

¿Cómo maneja la fórmula los negocios que saltan etapas hacia adelante y luego retroceden?

La comparación fila a fila del Paso 5 maneja esto correctamente porque compara cada evento con el evento inmediatamente anterior del mismo negocio, no con el primer evento original. Un negocio que pasa por Prospecting, luego Demo, luego Proposal y de regreso a Discovery marcará el movimiento final como una regresión de Proposal (orden 5) a Discovery (orden 3), que es exactamente correcto.

¿Por qué QUERY en lugar de COUNTIFS para las tablas de resumen?

Con 5,000 filas, COUNTIFS funciona bien. Por encima de 30,000 filas, COUNTIFS con múltiples criterios evalúa cada combinación de celdas en cada recálculo. En una hoja con otras fórmulas activas, eso lleva los tiempos de recálculo a más de 60 segundos, a veces mucho más. QUERY usa un motor similar a SQL que agrega datos significativamente más rápido: un desglose por representante con 80,000 filas corre en menos de 3 segundos. Si tu hoja empieza a trabarse, COUNTIFS suele ser el culpable.

¿Qué pasa cuando el administrador del CRM agrega una nueva etapa al pipeline?

Agrega la nueva etapa y su número de orden a tu tabla StageOrder de inmediato. Cualquier fila histórica con ese nombre de etapa anterior a la adición devolverá 0 desde el VLOOKUP (capturado por IFERROR y mostrado como indicador). Una vez que agregues la fila a StageOrder, esos ceros se resolverán correctamente en el siguiente recálculo. El caso más difícil es cuando se inserta una etapa entre dos existentes, por ejemplo, agregar "Technical Evaluation" entre Demo (orden 4) y Proposal (orden 5), porque todas las etapas superiores necesitan renumerarse para mantener el orden relativo.