Introducción al análisis RFM de clientes con SQL Server y Power BI

El sistema RFM es un método de Marketing empresarial que consiste en la segmentación de clientes y se basa en el valor que aportan a la empresa, dependiendo de la volumetría de la cartera de clientes de una empresa y del presupuesto que pueda disponer el departamento de marketing normalmente nos interesa saber que grupo de clientes suelen tener una mejor respuesta a determinadas campañas de email, teléfono o de redes sociales.

El análisis RFM se basa en el principio de Pareto que indica que un 20% de los clientes teóricamente generan el 80 % de los ingresos, evidentemente esto llevado a la práctica no es del todo real, pero nos da una idea del valor que tiene para una empresa hacer una buena segmentación de sus clientes.

El primer paso del RFM es el cálculo de 3 parámetros bien diferenciados:

• Recency:

Es el parámetro más importante de todos, debido a que estadísticamente presenta el mayor porcentaje de respuesta a ofertas si se compara a los clientes que han comprado recientemente contra los que llevan un tiempo sin hacerlo, se trata del intervalo de días transcurrido desde la última compra que ha hecho un cliente.

• Frequency: 

La frecuencia suele ser el segundo parámetro más importante y como su nombre indica se trata del número de compras que ha hecho un cliente en un período de tiempo. Cuanta más alta sea la frecuencia de compra más altas son las probabilidades de que un cliente responda a una oferta.

• Monetary Value:

El importe total que un cliente ha gastado en un intervalo de tiempo determinado, normalmente un cliente que ha gastado más suele estar más predispuesto a realizar otra compra que uno que ha gastado menos.

Hay que tener en cuenta que cada empresa es un mundo, no es lo mismo vender neumáticos de coche, que vender accesorios de moda o productos tecnológicos, en cada caso el paradigma de estudio es radicalmente distinto y normalmente no se va a poder extrapolar.

La norma general del análisis RFM es hacer 5 partes iguales para cada parámetro llamados quintiles, también los hay que usan deciles (10 partes) y como tiempo de análisis se suelen analizar 2 años, aunque hay empresas que usan rangos incluso de 10 años con excelentes resultados.

Para hacer 5 partes iguales podemos utilizar la función NTILE de sql server, para el parámetro recency puede ser una buena idea ya que sería una distribución por fecha, pero que pasa con la frecuencia cuando normalmente la mayoría de los clientes tienen una sola compra y que eso pueda llegar a representar incluso el 60% de los clientes, esto provocaría que tengamos en el quintil 1, 2 y 3 clientes posiblemente con solo una compra, lo cual hace desvirtuar los resultados.

Una manera de afrontar estas casuísticas y que recomiendan algunos autores es intentar enfocar una distribución aproximada de 50%(1) – 30%(2) – 15%(3) – 4%(4) – 1%(5) que es la que vamos a ejemplificar con una BBDD de ejemplo adventureworks 2017.

Muchas veces nos interesa crear una puntuación como forma de englobar a los 3 parámetros, en el código de ejemplo podremos ver que hay varias maneras de calcular estos Scores:

1. Concatenando los parámetros recency + frequency + money, tendríamos un máximo 555 y como vemos tendría más valor la R, después la F y por último la M.
2. Multiplicando R*F*M esto da a cada parámetro el mismo valor, lo cual puede ser buena idea en determinados casos, el valor máximo sería 125.
3. Otra opción es (3*R+2*F+M)*3.3 con lo que tendríamos un máximo de 99
4. También se puede sumar los 3 parámetros y dividirlos entre tres, con lo tendríamos un score max de 5 en el cual cada parámetro tendría el mismo valor.

En cualquier caso, tendremos que elegir nuestra forma de calcular la puntuación y eso siempre va a depender de la naturaleza de nuestro negocio y sobre todo lo más importante va a ser ajustar la puntuación a nuestros datos de partida.

El siguiente paso será crear una vista en nuestra que posteriormente explotaremos en Power BI, el siguiente código nos puede dar una idea de como debemos enfocar un análisis RFM y aplicarlo de forma sencilla..

USE [AdventureWorks2017]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create view [Sales].[vRFM_Customers] as


SELECT customerid
  ,Country
  ,Country_Code
  ,rfm_recency
  ,rfm_frequency
  ,rfm_monetary
  ,(rfm_recency * rfm_frequency * rfm_monetary) rfm_Score_Classic
  ,cast(round(((3*rfm_recency) + (2*rfm_frequency) + rfm_monetary)*3.3,0) as int) rfm_Score_New
  ,cast(cast(rfm_recency as varchar)+''+cast(rfm_frequency as varchar)+''+cast(rfm_monetary as varchar)as int) rfm_Score_Concat
  ,ceiling(cast(rfm_recency + rfm_frequency + rfm_monetary AS FLOAT) / cast(3 AS FLOAT)) rfm_avg
FROM (
  SELECT customerid,
      Country,
      Country_Code,
    case when last_order_date>FORMAT(dateadd(DAY, - 15, cast('2014-06-30' as date)), 'yyyyMMdd') then 5
    when last_order_date<=FORMAT(dateadd(DAY, - 15, cast('2014-06-30' as date)), 'yyyyMMdd') and last_order_date>FORMAT(dateadd(DAY, - 45, cast('2014-06-30' as date)), 'yyyyMMdd') then 4
    when last_order_date<=FORMAT(dateadd(DAY, - 45, cast('2014-06-30' as date)), 'yyyyMMdd') and last_order_date>FORMAT(dateadd(MONTH, - 3, cast('2014-06-30' as date)), 'yyyyMMdd') then 3
    when last_order_date<=FORMAT(dateadd(MONTH, - 3, cast('2014-06-30' as date)), 'yyyyMMdd') and last_order_date>FORMAT(dateadd(MONTH, - 6, cast('2014-06-30' as date)), 'yyyyMMdd') then 2
    when last_order_date<=FORMAT(dateadd(MONTH, - 6, cast('2014-06-30' as date)), 'yyyyMMdd') and last_order_date>FORMAT(dateadd(MONTH, - 24, cast('2014-06-30' as date)), 'yyyyMMdd') then 1 
      end as rfm_recency
    ,case when count_order=1 then 1
    when count_order=2  then 2
    when count_order=3 then 3
    when count_order between 4 and 6 then 4
    when count_order>=7 then 5
      end AS rfm_frequency
    ,case when avg_amount<150 then 1
    when avg_amount>=150 and avg_amount<2000 then 2
    when avg_amount>=2000 and avg_amount<2650 then 3
    when avg_amount>=2650 and avg_amount<3500 then 4
    when avg_amount>=3500  then 5 end AS rfm_monetary
  FROM (
    SELECT customerid,Te.[Name] Country
    ,Te.CountryRegionCode Country_Code
      ,max(OrderDate) AS last_order_date
      ,count(*) AS count_order
      ,avg([TotalDue]) AS avg_amount
    FROM [Sales].[SalesOrderHeader] Sa
    inner join [Sales].[SalesTerritory] Te
    on Sa.[TerritoryID]=Te.[TerritoryID]
    WHERE OrderDate > FORMAT(dateadd(YEAR, - 2, cast('2014-06-30' as date)), 'yyyyMMdd')
    GROUP BY customerid,Te.[Name],Te.CountryRegionCode
    ) a
  ) b;
GO

Por último, podemos ver unas visualizaciones en Power BI que nos pueden dar una idea de a que clientes y en qué zonas deberíamos poner el foco de nuestras campañas, en este caso estamos haciendo comparaciones geográficas pero podemos hacerlas por marca, sexo, edad y cualquier dato categórico referente a un cliente que podamos explotar.

En la siguiente captura podemos ver una distribución de los parámetros por categoría y puntuaciones por país para cada score.

En este caso hemos trasladado nuestros resultados a otro panel un poco más visual con un mapamundi, un treemap y un cluster map por parámetro, que nos da una idea de donde tenemos un score más alto y que países dominan cada parámetro.

Como hemos visto, el RFM es un método rápido y fácil de obtener resultados que normalmente debe ser usado con mesura, ya que podemos ver que nos puede dar buenos resultados en un espacio muy corto de tiempo lo cual nos puede tentar a repetir el proceso, por lo cual igual estamos bombardeando con mailing o llamadas a nuestros mejores clientes, causando fatiga en ellos lo cual igual provoca el resultado contrario, por otra parte también nos puede llevar a olvidarnos de otros clientes por lo tanto hay que saber usarlo y establecer unos patrones concretos para cada grupo de clientes.

En definitiva, se puede ver que es una herramienta más para conocer a nuestros clientes y que se puede utilizar casi independientemente del tamaño de la empresa, y como vemos no lleva tiempo alguno su implantación.

Un saludo y espero que os haya gustado