A petición de mi colega Josep Lopez de www.globalitytic.com he desarrollado un Power BI para ver el estado de los clientes en función de las ventas del año en curso, el anterior, y el anterior a ese.
Definición de estado cliente
En base a esta tabla
Estado cliente | Explicación |
Nuevo | Ha comprado el último año pero no los dos anteriores |
Fidelizado | Ha comprado el último año y el anterior, pero no antes |
Perdido | No ha comprado en el último año pero si en el anterior |
Preferente | Ha comprado todos los años |
A recuperar | No ha comprado el último año pero si los dos anteriores |
Recuperado | Ha comprado el último año, el anterior no, pero antes sí. |
Por supuesto estos criterios son subjetivos pero se trata de ver como los podemos implementar con Power BI usando varias técnicas.
Partimos de A3ERP Ventas totales detalladas base que puede descargar para seguir el ejemplo.
Medidas a crear
Partimos de que tenemos una medida que nos da la suma de las ventas
EUR = sum(Ventas[Neto])
Tenemos que calcular las ventas del último año y los dos anteriores creando las siguientes medidas
I-1 = Calculate([EUR]; DATESINPERIOD(Calendario[Date];now();-1;YEAR))
I-2 = Calculate([EUR]; DATESINPERIOD(Calendario[Date];now()-365;-1;YEAR))
I-3 = Calculate([EUR]; DATESINPERIOD(Calendario[Date];now()-730;-1;YEAR))
Como ve, uso el calculate para aplicar a EUR un filtro, partiendo siempre de la fecha de hoy NOW()
Restamos un año, o dos o tres. Las he bautizado como I-1, I-2 o I-3 para simplificar la notación.
Ahora debemos traducir la tabla anterior a formulas
Estado cliente | Explicación | Formula |
Nuevo | Ha comprado el último año pero no los dos anteriores | I-1>0 AND I-2=0 AND I-3=0 |
Fidelizado | Ha comprado el último año y el anterior, pero no antes | I-1>0 AND I-2>0 AND I-3=0 |
Perdido | No ha comprado en el último año pero si en el anterior | I-1=0 AND I-2>0 AND I-3=0 |
Preferente | Ha comprado todos los años | I-1>0 AND I-2>0 AND I-3>0 |
A recuperar | No ha comprado el último año pero si los dos anteriores | I-1=0 AND I-2>0 AND I-3>0 |
Recuperado | Ha comprado el último año, el anterior no, pero antes sí. | I-1>0 AND I-2=0 AND I-3>0 |
Solo nos falta una manera de implementar estos cálculos en una medida.
Para ello usaremos la función SWITCH, que evalúa una expresión, y según los resultados nos permite dar valores de respuesta.
Pero nadie dice que los resultados no puedan ser a su vez una expresión que se evalúa.
Creamos pues la medida:
Estado Cliente = SWITCH(TRUE();[I-1]>0 && [I-2]=0 && [I-3]=0;”NUEVO”;[I-1]>0 && [I-2]>0 && [I-3]=0;”FIEL”;[I-1]=0 && [I-2]>0 && [I-3]=0;”PERDIDO”;[I-1]>0 && [I-2]>0 && [I-3]>0;”PREFERENTE”;[I-1]=0 && [I-2]>0 && [I-3]>0;”A RECUPERAR”;[I-1]>0 && [I-2]=0 && [I-3]>0;”RECUPERADO”;”MUY PERDIDO”)
Empezamos diciendo SWITCH(TRUE() o sea la expresión siempre se evalúa, y a partir de allí montamos parejas de expresiones y resultados, por ejemplo:
[I-1]>0 && [I-2]=0 && [I-3]=0;”NUEVO”;
Si se cumple [I-1]>0 && [I-2]=0 && [I-3]=0, la respuesta es NUEVO
Nótese que para poner el AND hay que escribir &&
Al final, hay el caso que no se cumpla ninguna condición, o sea un ELSE, ponemos MUY PERDIDO
Tabla de resultados
Con esto ya podemos crear una tabla de clientes
Y obtendremos algo así
He puesto el importe de cada año y el estado cliente para poder verificar que los cálculos son correctos.
Por supuesto podemos ordenar por estado, o quitar los importes, pero lo que no podemos es filtrar por estado: las tablas NO permiten filtros como en Excel.
Y si intenta poner un segmentador y añadir el estado no podrá pues Estado cliente es una medida, NO un campo.
Segmentador por estado
Vamos a usar un truco: crearemos una tabla de clientes y le añadiremos el campo estado.
Vamos a Modelado, Nueva tabla
Y ponemos la formula
Clientes = SUMMARIZE(Ventas;Ventas[Cliente])
Summarize es una función que totaliza los datos de una tabla por uno o varios campos.
Esto nos crea una tabla con los nombres de los clientes
Y un paso importante
Ahora tenemos que ir a relaciones y relacionar el Cliente de esta tabla con el cliente de ventas.
Ahora necesitamos una columna con el estado. Pues añadimos una nueva columna y copiamos la fórmula del estado:
Estado = SWITCH(TRUE();[I-1]>0 && [I-2]=0 && [I-3]=0;”NUEVO”;[I-1]>0 && [I-2]>0 && [I-3]=0;”FIEL”;[I-1]=0 && [I-2]>0 && [I-3]=0;”PERDIDO”;[I-1]>0 && [I-2]>0 && [I-3]>0;”PREFERENTE”;[I-1]=0 && [I-2]>0 && [I-3]>0;”A RECUPERAR”;[I-1]>0 && [I-2]=0 && [I-3]>0;”RECUPERADO”;”MUY PERDIDO”)
Por supuesto no se puede llamar igual que la de ventas, así que la he nombrado como estado.
Volvemos a nuestra tabla, quitamos el Estado cliente de ventas, que de echo podemos eliminar y añadimos el Estado.
Y ahora podemos añadir un segmentador por estado:
Lo que nos ira muy bien para analizar el Estado de los clientes
NOTAS:
Por supuesto los periodos los debe ajustar usted a su gusto cambiando los valores de DATESINPERIOD en las primeras medidas.
Note el importante y curioso uso del SWITCH que también explique en ABC de clientes.
Tambien podiamos haber usado las medidas
I-1 = Calculate([EUR]; DATESBETWEEN(Calendario[Date];NOW()-365;now()))
I-2 = Calculate([EUR]; DATESBETWEEN(Ventas[Fecha];NOW()-730;NOW()-365))
I-3 = Calculate([EUR]; DATESBETWEEN(Ventas[Fecha];NOW()-1095;NOW()-730))
Para especificar el intervalo de años con DATESBETWEEN
Apreciado Domenec
Genial este artículo, muchas gracias por tu ayuda una vez más.
Hola Domenec,
He descargado el archivo pero presenta fallas, el tema es buenisimo.
me ayudas,
gracias
Estimado, no tengo palabras para agradecerle este aporte. Estudié y llevé a cabo todos los pasos al pie de la letra y cumplí el objetivo.
Saludos.
Muy interesante!! estudiare la incorporación del tema a nuestro a3erp. Veremos a ver que pasa. Muchas gracias.
Estimado, se puede implementar , para una revisión mes a mes?
Del ejemplo al desarrollarlo todos me salen “muy perdidos” todas las formulas Dax se mantienen a pesar que estamos finalizando año 2020 o hay que modificar como cuales?
Estimados me sale este mensaje de error! la tabla de años anteriores salen sin datos. como solucionarlo.
Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)
https://jumpshare.com/v/gVBfITnbeY9Giaczruuj
saludos
Rick