Crea un Dashboard para tu CRM Gratis

La reportería permite entender nuestros resultados en ventas y objetivos de negocios. Con el CRM que diseñamos en publicaciones anteriores registramos la información, sin embargo, para entender mejor necesitaremos estadísticas y gráficos actualizados. De esta manera podremos cambiar nuestra estrategia y aprender de los experimentos que hagamos.

En esta publicación construiremos un reporte con el objetivo de:

Tener una visión del cumplimiento de metas a nivel mensual y anual
Ver las tendencias en el tiempo de las métricas clave para un área comercial
Construir una herramienta que permita comprender los resultados de la acciones o iniciativas que implementemos en el ciclo de ventas

Cuando terminemos contaremos con el siguiente reporte, donde:

Filtros que nos permiten seleccionar uno o varios años, meses o países
Estado de los indicadores según los filtros seleccionado
Indicadores para el mes en curso
Evolución de los indicadores en el tiempo y su tasa de conversión/cumplimiento

Para esto, seguiremos los siguientes pasos:

Definiremos métricas que queremos seguir en el Dashboard (cubierto en este post)
Prepararemos los datos: obtener información y procesarla para facilitar la construcción del Dashboard (cubierto en este post)
Implementaremos el Dashboard en Data Studio

Antes de comenzar, te dejamos los links al Dashboard y la a base de datos que utilizamos para construirlo:

Dashboard

Base de Datos

1. ¿Qué métricas debemos seguir?

Para definir las métricas de nuestro reporte, primero pensaremos en nuestro proceso comercial. Cuando construímos nuestro CRM colocamos las etapas en que podía estar un negocio. Estas son:

Nuevo Lead o Lead para cuando creamos la oportunidad
Primera Reunión para cuando presentamos nuestro producto
Negociación para indicar que estamos negociando precios
Cliente Ganado para cuando logramos cerrar el negocio
Negocio Perdido para cuando se cae el negocio que puede pasar entre cualquiera de las etapas anteriores

Con la información de nuestro CRM podemos identificar cuántos negocios tenemos en cada etapa, la conversión entre etapas, cómo vamos contra la meta que nos fijamos y si proyectamos que la vamos a cumplir.

Con esto, las métricas que mediremos en nuestro reporte serán:

Estado de nuestro embudo/funnel (negocios activos o no perdidos) en cantidad y dinero
Cantidad de leads
Cantidad de reuniones/demos
Cantidad de negocios cerrados en número y dinero
Y las tasas de conversión entre las etapas

Por último, también consideramos poder tener filtros para evaluar diferentes años, meses y países.

*Existen muchas más métricas que son útiles, pero tomamos estas como punto de partidad ya que nos entregan una perspectiva general y completa

2. Preparación de los Datos

Antes de construir el Dashboard procesaremos la data para dejarla en un formato amigable que facilite el cálculo de las métricas. Para ello, utilizaremos Google Sheets y dividiremos la preparación de los datos en 4 pasos:

Ingesta de datos desde nuestro CRM lo que por ahora será manual (copiando y pegando)
Creación de un "diccionario" y columnas auxiliares para ordenar la base y hacer la conversión a dólares
Creación de una base auxiliar para facilitar algunos cálculos
Creación de la base de metas la cual será la base principal desde la que construiremos el Dashboard

Antes de continuar te recomendamos que hagas una copia de la base que usaremos en tu Google Drive. De esta manera podrás seguirnos mejor.

Base de Datos

2.1 Ingesta de Datos

Comenzamos creando una planilla en Google Sheets y copiremos la data de nuestro CRM. Llamaremos esta hoja "Base".

La data debe contener al menos las columnas que definimos en las publicación sobre cómo crear tu CRM:

"Empresa"
"Pais"
"Encargado"
"Etapa"
"% Win Rate"
"Notas"
"MRR/Tamaño de la Oportunidad"
"Moneda" (solo si registramos en diferentes monedas)
"Fecha de Creación"
"Fecha Primera Reunión"
"Fecha Negociación"
"Fecha Negocio Ganado"
"Fecha Negocio Perdido"

2.2 Creación de Diccionario y Columnas Auxiliares

Luego, debemos crear 2 columnas auxiliares que nos ayudarán a:

Convertir todo a dólares
Ordenar las etapas de nuestro proceso comercial en el Dashboard (esto será más claro cuando construyamos el Dashboard)

Para lograr esto, comenzaremos creando una nueva hoja que llamaremos “Diccionario” y dentro de ella dos tablas:

Una para colocar los cambio a dólar de las diferentes monedas
Otra para los “códigos” de las etapas de nuestro proceso comercial

La primera tabla tendrá 3 columnas:

"Código": donde colocamos el código interno de cómo registramos las diferentes monedas en nuestro CRM
[Opcional] "Moneda": donde colocamos el símbolo bursátil de la moneda en caso de que queramos que se actualice automáticamente el tipo de cambio
"Tipo de Cambio: donde colocamos el tipo de cambio de manera manual o utilizando la fórmula "GOOGLEFINANCE" para calcularlo de manera automática

Para hacer que el tipo de cambio se actualice automáticamente con "GOOGLEFINANCE" debemos utilizar la fórmula de la siguiente manera:

GOOGLEFINANCE(“Currency: moneda_1:moneda_2")

Por ejemplo, en nuestra planilla y para el caso de Chile (CLP):

GOOGLEFINANCE("Currency:USD"&C4)

*Para USD colocamos un 1 manualmente ya que la fórmula nos arroja error en caso contrario

La segunda tabla tendrá 2 columnas:

"Etapa": donde colocamos el nombre de las etapas tal y como las registramos en nuestro CRM
"Código": donde simplemente anteponemos un número según el orden de las etapas en nuestro proceso comercial

Finalmente, creamos 2 columnas en la hoja "Base":

"MRR USD": para colocar el MRR de los negocios en dólares
"Código Etapa": para colocar el nuevo nombre de la etapa

Y utilizamos la fórmula “VLOOKUP” para buscar los valores en la hoja "Diccionario":

Para "MRR USD”, la sintaxis y un ejemplo para el primer registro de nuestra planilla serían:

“MRR en moneda original” /
VLOOKUP(“Pais”, “Tabla Tipo Cambio”, 3, FALSE)

G2/VLOOKUP(H2, Diccionario!$B$3:$D$8, 3, FALSE)

2. Para “Codigo Etapa", la sintaxis y un ejemplo para el primer registro de nuestra planilla serían:

VLOOKUP(“Etapa”, “Tabla Códigos Etapa”, 2, FALSE)

VLOOKUP(D2, Diccionario!$F$3:$G$7, 2, FALSE)

2.3 Creación de Base Auxiliar

Ahora creamos una nueva hoja y la llamamos "Base Auxiliar". El único propósito de esta hoja es facilitar la construcción de la base de metas.

En esta hoja debemos "despivotear" la data de hoja "Base", es decir, convertir todas las columnas de fecha en una sola columna para así facilitar el uso de fórmulas. La base "despivoteada" se verá así:

Para esto, primero colocamos los nombres de las columnas resultantes en la primera:

"Empresa"
"Pais"
"MRR/Tamaño de la Oportunidad"
"Moneda"
"Tipo de Evento"
"Fecha"

Luego, utilizamos la siguiente fórmula para "despivotear" la data de la hoja "Base":

ARRAYFORMULA(SPLIT(FLATTEN("columna Empresa" & "separador" &
"columna Pais" & "separador" &
"columna MRR/Tamaño Oportunidad" & "separador" &
"columna Moneda" & "separador" &
"columna Tipo de Evento" & "separador" &
"columnas de Fecha" & "separador"),"separador")),

En donde:

"columna Empresa": la columna que tiene el nombre de las empresas en la base original
"columna Pais": la columna que tiene los países en la base original
"columna MRR/Tamaño de Oportunidad": la columna que tiene el MRR de los negocios en la base original
"columna Moneda": la columna que tiene el tipo de moneda en que está el MRR de los negocios en la base original
"columna Tipo de Evento": los headers de las columnas de fecha de la base original. Estos son: Fecha Creacion, Fecha Primera Reunion, Fecha Negociacion, Fecha Ganado y Fecha Perdido
"columnas de Fecha": los valores de las columnas de fecha de la base original
"separador": es simplemente un string o texto que nos permite separar las variables. Es importante hacer una combinación que no aparezca en nuestros registros, por eso en el ejemplo utilizamos "/\"

Utilizando a fórmula anterior en nuestra planilla tendríamos:

=ARRAYFORMULA(SPLIT(FLATTEN(Base!$A$2:$A&"/\"&Base!$B$2:$B&
"/\"&Base!$G$2:$G&"/\"&Base!$H$2:$H&"/\"&Base!$I$1:$M$1&
"/\"&Base!I2:M),"/\"))

*En el siguiente link hay una explicación más detallada de la fórmula.

Terminamos creando algunas columnas auxiliares para facilitar la construcción de la base principal (en formato "Nombre" | "Formato"):

"MRR USD" | Dinero: conversión de MRR del negocio al dólares mediante la fórmula:

C2/VLOOKUP(D2,Diccionario!$B$3:$D$8,3,FALSE)

"Anno" | Número: año de la fecha de la etapa correspondiente (lo colocamos sin ñ ya que Data Studio no lee ese caractér). Podemos calcularlo con la fórmula:

YEAR(F2)

"Mes" | Número: mes de la fecha de la etapa correspondiente. Podemos calcularlo con la fórmula:

MONTH(F2)

"Periodo" | Número: periodo de la fecha de la etapa correspondiente. Podemos calcularlo con la fórmula:

H2*100+I2

"Codigo Pais" | Número: campo auxiliar para separar los países relevantes de los demás ("Otros"). Para esto podemos utilizar la siguiente fórmula:

IF(OR(B2="CL",B2="CO",B2="MX",B2="PE"),B2,"Otros")

Así obtendremos la siguiente base:

2.4 Construcción de Base de Metas

Finalmente, creamos una base Central donde haremos los cálculos necesarios para construir nuestro Dashboard. Para esto creamos una nueva hoja que llamaremos "Metas" donde también incorporaremos las metas que tengamos para cada indicador:

Las columnas que necesitamos son las siguientes (en formato "Nombre" | "Formato"):

"Periodo" | Número: periodo del registro en formato aaaamm (año y mes)
"Pais" | Texto: para el registro de los indicadores y metas por país. Aquí colocamos los países más importantes y dejamos como"Otros" el resto
"Anno" | Número: el año al que corresponde el periodo (lo colocamos sin ñ ya que Data Studio no lee ese caractér).
"Mes" | Número: el mes al que corresponde el periodo.
"Fecha" | Fecha: está es una columna auxiliar que nos ayudará a calcular los rangos de fecha en Data Studio. La podemos calcular con la fórmula "DATE("Anno", "Mes", 1)"
"Meta Lead" | Número: meta de Leads para el periodo y el país correspondientes
"Real Lead" | Número: cantidad de Leads generados en el periodo y país correspondientes
"Meta Reunion" | Número: meta de Reuniones para el periodo y el país correspondientes
"Real Reuniones" | Número: cantidad de Reuniones generados en el periodo y país correspondiente
"Meta MRR" | Dinero: meta de MRR para el periodo y el país correspondientes
"Real MRR" | Dinero: ventas generadas en el periodo y país correspondientes
"# Cierres" | Número: cantidad de cierres generados en el periodo y país correspondientes

El registro de metas se debe hacer de manera manual y en general no va a cambiar a menos que se modifiquen las metas para el año. El cálculo de los indicadores "Real Lead", "Real Reuniones", "Real MRR" y "# Cierres" es posible automatizarlo mediante las siguientes fórmulas:

Para "Real Lead", la sintaxis y un ejemplo para el primer registro de nuestra planilla serían:

COUNTIFS("Columna Fecha en Base Auxiliar", "Fecha Creacion", "Columna,
Periodo en Base Auxiliar", "Periodo",
"Columna Codigo Pais en Base Auxiliar", "Pais")

COUNTIFS('Base Auxiliar'!E:E,"Fecha Creacion",'Base Auxiliar'!$J:$J,$A2,'Base Auxiliar'!K:K,$B2)

Para "Real Reuniones", la sintaxis y un ejemplo para el primer registro de nuestra planilla serían:

COUNTIFS("Columna Fecha en Base Auxiliar",
"Fecha Primera Reunión", "Columna Periodo en Base Auxiliar",
"Periodo", "Columna Codigo Pais en Base Auxiliar", "Pais")

COUNTIFS('Base Auxiliar'!E:E,"Fecha Primera Reunion",'Base Auxiliar'!$J:$J,$A2,'Base Auxiliar'!K:K,$B2)

Para "Real MRR", la sintaxis y un ejemplo para el primer registro de nuestra planilla serían:

SUMIFS(Columna MRR USD en Base Auxiliar",
Columna Fecha en Base Auxiliar", "Fecha Ganado",
"Columna Periodo en Base Auxiliar", "Periodo",
"Columna Codigo Pais en Base Auxiliar", "Pais")

SUMIFS('Base Auxiliar'!$G:$G,'Base Auxiliar'!E:E,
"Fecha Ganado",'Base Auxiliar'!$J:$J,$A2,'Base Auxiliar'!K:K,$B2)

Para "# Cierres":

COUNTIFS("Columna Fecha en Base Auxiliar", "Fecha Ganado",
"Columna Periodo en Base Auxiliar", "Periodo", 
"Columna Codigo Pais en Base Auxiliar", "Pais")

COUNTIFS('Base Auxiliar'!E:E,"Fecha Ganado",'Base Auxiliar'!$J:$J,$A2,'Base Auxiliar'!K:K,$B2)

Con esto finalizamos la preparación de la data y podemos empezar a la construcción de nuestro Dashboard en Data Studio.

Conclusiones

En esta publicación definimos los indicadores relevantes para la gestión del proceso comercial y preparamos los datos para la construcción del Dashboard.

Los cálculos realizados en esta base se realizarán de manera automática en la medida que la información de la hoja "Base" este al día. La automatización del proceso de ingesta la abarcaremos en una siguiente publicación.

En la segunda parte de esta serie revisaremos cómo implementar el Dashboard que te mostramos al inicio utilizando Google Data Studio.

Te volvemos a dejar los links al Dashboard y la a base de datos que utilizamos para que no comiences desde 0:

Dashboard

Base de Datos

Y si quieres revisar otro contenido sobre cómo implementar tu CRM te recomendamos revisar los siguientes post:

Cualquier duda que tengas podemos conversarla en los comentarios o directamente si nos escribes!