library(RSQLite)
library(DBI)
library(skimr)
library(dlookr)
library(DT)
library(readxl)
library(dplyr)
library(echarts4r)Análisis de clientes
ANALISIS DE CORRELACION Y REGRESIÓN LINEAL SIMPLE
Primeros pasos
Obtención de la variable objeto del análisis
sanoyfresco <- dbGetQuery(con, "SELECT * FROM tickets")Realizamos un muestreo representativo, ya que el dataset contiene cerca de 5 millones de filas.
# Aseguramos que las operaciones aleatorias se generen de manera consistente y reproducible
set.seed(42)
# Obtención de la muestra (5% del total de filas)
sample_size <- floor(0.05 * nrow(sanoyfresco))
# Guardamos el resultado de la muestra en una variable
sample_data <- sanoyfresco[sample(nrow(sanoyfresco), sample_size), ] EDA
diagnose(sample_data) %>% datatableskim(sample_data) %>% datatablediagnose_numeric(sample_data) %>% datatablediagnose_outlier(sample_data) %>% datatableDespués de revisar el dataset vemos varios outliers, pero se concluye que no son errores de entrada y tienen razón de ser, por lo que no se sustituyen y se mantienen.
ETL
# Transformamos la variable fecha de character a date
sample_data$fecha <- as.Date(sample_data$fecha)
# Verificamos la transformación
skim(sample_data$fecha) %>% datatableRevisando el dataset, vemos que no tenemos el nombre de las secciones, por lo que procedemos a su obtención haciendo una unión con la variable ‘secciones’
# Unimos con la tabla sample_data para incluir el nombre de cada sección
tabla_comb <- sample_data %>%
left_join(secciones, by = "id_seccion")
# Nos quedamos con las variables que necesitamos
tabla_final <- tabla_comb %>%
select(nombre_seccion, precio_unitario, precio_total, cantidad)
# Revisamos el dataset final
head(tabla_final) %>% datatableExportamos el resultado para analizarlo con una app de correlación creada para ver posibles correlaciones entre variables
Revisión de las correlaciones con la app data de correlación (desarrollo propio)
Vamos a incorporar en la app como variables númericas el precio unitario, la cantidad y el precio total y vamos a segmentar la correlación por la variable categórica nombre_seccion, para encontrar posibles correlaciones ocultas en los datos.
Podemos observar que aunque la correlación entre el precio total y la cantidad vendida es de un valor de 0.661, si posteriormente segregamos el cálculo por nombre de sección podemos ver CORRELACIONES OCULTAS EN LOS DATOS; por ejemplo se puede observar que las bebidas tienen una correlación perfecta de 1; esta será la base para el cálculo de la una regresión lineal simple en excel.
Entonces, a nivel general, sí (r = 0.661) -> (R2 = 0.661 * 0.661 = 0.436), siendo r el coeficiente de correlación entre las variables y R2 la calidad de la predicción de la variable independiente ‘cantidad’ sobre la variable dependiente ‘ventas’.
Para reafirmar esta conclusión, realizamos el modelo de regresión lineal y comprobamos los resultados
modelo <- lm(tabla_final$precio_total~tabla_final$cantidad)
summary(modelo)
Call:
lm(formula = tabla_final$precio_total ~ tabla_final$cantidad)
Residuals:
Min 1Q Median 3Q Max
-12.7557 -2.7400 -0.4267 2.5197 16.2343
Coefficients:
Estimate Std. Error t value Pr(>|t|)
(Intercept) -0.02610 0.01971 -1.324 0.185
tabla_final$cantidad 1.60321 0.00365 439.203 <2e-16 ***
---
Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
Residual standard error: 3.62 on 248783 degrees of freedom
Multiple R-squared: 0.4367, Adjusted R-squared: 0.4367
F-statistic: 1.929e+05 on 1 and 248783 DF, p-value: < 2.2e-16Como hemos dicho, el modelo arroja un R2 de 0.436 y existe significancia estadística (p-value < 0.05) de que la variable cantidad es buena predictora sobre la variable ventas.
Ahora bien, en el caso de calcular la predicción de ventas de la sección ‘bebidas’ con excel, lo que podemos observar es que si vendemos unas determinadas cantidades, tales como 15, 30, 45… el modelo predice estadísticamente con una altísima precisión, que vamos a obtener unas ventas de 14.25 €, 28.50 €, 42.75 €…
Estas predicciones se pueden replicar con las otras secciones para ver que tan bien predice la variable dependiente ‘ventas’, en base a la variable independiente ‘cantidad’.
ANALISIS DE SEGMENTACIÓN DE CLIENTES POR EL MÉTODO RFM
Introducción
Para realizar este análisis usaremos el dataset completo de la BBDD (+-5 millones de filas), ya que necesitamos todos los datos de los clientes para ver su Recencia (diferencia en días desde la fecha actual hasta la última venta que se le realizó), Frecuencia (veces que el cliente nos ha realizado una compra) y Valor Monetario (total de ventas por cliente).
En este análisis simulamos que la fecha actual es el 30/01/2024.
Consulta SQL a la BBDD
seg_clientes <- dbGetQuery(con, "
WITH T1 AS -- OBTENCION DE LAS VARIABLES NECESARIAS
(
SELECT
DATE (fecha) AS fecha,
id_cliente,
id_pedido,
precio_total
FROM
tickets
),
T2 AS -- OBTENCION DE LAS FECHAS PARA EL CALCULO DE LA RECENCIA
(
SELECT
*,
LAST_VALUE(fecha) OVER (PARTITION BY id_cliente ORDER BY fecha
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS ult_fecha,
strftime('%Y-%m-%d', '2024-01-30') AS fecha_actual
FROM
T1
),
T3 AS -- CALCULO DE LA RECENCIA
(
SELECT
id_cliente,
julianday(fecha_actual) - julianday(ult_fecha) AS recencia,
COUNT (DISTINCT id_pedido) AS frecuencia,
SUM (precio_total) AS valor_monetario
FROM
T2
GROUP BY
id_cliente,
julianday(fecha_actual) - julianday(ult_fecha)
),
T4 AS -- PRECALCULO DE LA PUNTUACION
(
SELECT
*,
ROW_NUMBER() OVER (ORDER BY recencia DESC) AS fila_r,
ROW_NUMBER() OVER (ORDER BY frecuencia ASC) AS fila_f,
ROW_NUMBER() OVER (ORDER BY valor_monetario ASC) AS fila_m,
COUNT(*) OVER () AS total_filas
FROM
T3
),
T5 AS -- PUNTUAMOS A LOS CLIENTES SEGUN SU RECENCIA, FRECUENCIA Y VALOR MONETARIO
(
SELECT
*,
FLOOR((fila_r - 1) * 9.0 / (total_filas - 1)) AS pt_recencia,
FLOOR((fila_f - 1) * 9.0 / (total_filas - 1)) AS pt_frecuencia,
FLOOR((fila_m - 1) * 9.0 / (total_filas - 1)) AS pt_vmonetario
FROM
T4
),
T6 AS -- SUMAMOS LOS PUNTOS PARA OBTENER EL RFM DE CADA CLIENTE
(
SELECT
*,
pt_recencia + pt_frecuencia + pt_vmonetario AS RFM
FROM
T5
),
T7 AS -- PRECALCULO DE LA PUNTUACION RFM
(
SELECT
*,
ROW_NUMBER() OVER (ORDER BY RFM ASC) AS fila_RFM,
COUNT (*) OVER () AS filas_total
FROM
T6
),
T8 AS -- PUNTUAMOS LOS RFM
(
SELECT
*,
FLOOR((fila_RFM - 1) * 9.0 / (filas_total - 1)) AS pt_RFM
FROM
T7
),
T9 AS -- REALIZAMOS LA SEGMENTACION DE CLIENTES SEGUN LA PUNTUACION DE RFM
(
SELECT
*,
CASE
WHEN pt_RFM IN (8,9,10) THEN 'TOP'
WHEN pt_RFM IN (5,6,7) THEN 'Leales'
WHEN pt_RFM IN (2,3,4) THEN 'Riesgo, ATENDER!'
ELSE 'Probable abandono' END AS segm_cliente,
COUNT (*) OVER () AS nro_reg
FROM
T8
),
T10 AS -- AGRUPACION Y obtención de los promedios
(
SELECT
segm_cliente,
nro_reg,
ROUND (AVG (recencia), 0) AS avg_recencia,
ROUND (AVG (frecuencia), 0) AS avg_frecuencia,
ROUND (AVG (valor_monetario), 2) AS avg_vmonetario,
COUNT (1) AS conteo
FROM
T9
GROUP BY
segm_cliente,
nro_reg
),
T11 AS -- CALCULO DEL PORCENTAJE SEGUN SEGMENTACION DE CLIENTE
(
SELECT
*,
ROUND (CAST (conteo AS float) / nro_reg * 100, 2) AS pct
FROM
T10
)
-- CONSULTA FINAL CON LOS DATOS NECESARIOS
SELECT
segm_cliente,
pct,
avg_recencia,
avg_frecuencia,
avg_vmonetario
FROM
T11
")Resultados gráficos
Finalmente realizamos dos gráficos, uno para observar la proporción de clientes según su segmentación y otro para ver los promedios obtenidos de cada segmentación de clientes
Conclusión del análisis
Podemos concluir que:
El segmento TOP de clientes lo constituye el 11,11 % del total y su comportamiento es el siguiente:
Compra un promedio de 40 veces en el periodo analizado (año 2023).
Gasta un promedio de 888,34 €.
El periodo que transcurre desde la última compra que realiza hasta la fecha actual es de una media de 54 días.
El segmento de los clientes a los que debemos prestar especial atención lo constituye el 33,33 % del total y su comportamiento es el siguiente:
Gasta un promedio de 81,51 €.
Han realizado un promedio de 5 compras en el año.
Recomendaciones
Crearemos una campaña de incentivos a modo de reenganche de los clientes en riesgo.
Además de revisar los clientes TOP y leales para maximizar nuestras ventas y beneficios.