Análisis empresa sector alimentación

En este proyecto se pretende dar información a un negocio dedicado a la compra-venta de clientes alimentarios y que inició su andadura comercial en Abril de 2020; los últimos datos a analizar son del 05/06/2022.

1 ANALISIS DESCRIPTIVO

Se realizarán tareas de ETL, creación de nuevas variables de negocio y visualización de insights.

Vamos a realizar el proceso de ETL directamente en la BBDD de SQL Server.

1.1 Conexión a BBDD

Carga de librerias necesarias

library(tidyverse)
library(dplyr)
library(skimr)
library(DT)
library(dlookr)
library(ggplot2)
library(DBI)
library(odbc)
library(plotly)
library(echarts4r)
library(sqldf)
library(gtsummary)
library(flextable)
library(pROC)
library(scales)
library(cluster)
library(kableExtra)
library(htmlwidgets)
library(ggforce)
library(car)

Conexión a la BBDD

connect_to_db <- function(driver, server, database) {
  connection_string <- paste0("Driver={", driver, "};Server=", server,
                              ";Database=", database, ";Trusted_Connection=yes;
                              ")
  dbConnect(odbc::odbc(), .connection_string = connection_string)
}


con <- connect_to_db("ODBC Driver 17 for SQL Server", "LAPTOP-P7645H6F", 
                      "BD_SQL")

1.2 Diagnóstico y tratamiento de las tablas de la BBDD

Obtención y visualización de las tablas de la BBDD

info_tablas <- dbGetQuery(con, "SELECT TABLE_NAME
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_TYPE = 'BASE TABLE'
  ")
  
datatable(info_tablas, 
            options = list(
            pageLength = 5,      
            autoWidth = TRUE,   
            dom = 'Bfrtip',      
            buttons = I('colvis'),  
            stripe = TRUE,       
            hover = TRUE,        
            condensed = TRUE,    
            initComplete = JS(
              "function(settings, json) {",
              "  $(this.api().table().header()).css({",
              "    'background-color': 'grey',",
              "    'color': 'white'",
              "  });",
              "  $(this.api().table().header()).find('th').css('font-weight', 'bold');",
              "}"
            )  
          )
)

Visualización de la informacion de las tablas de la BBDD para obtener las tablas que necesitamos

Después de visualizar las tablas, seleccionamos aquellas que van a ser objeto de análisis y comenzamos a realizar las transformaciones oportunas; para ello usaremos:

• Transformaciones iniciales usando el conector odbc para realizar manipulaciones de tablas directamente desde el servidor.

• Otras transformaciones con las librerias dplyr y sqldf.

Antes de realizar cualquier transformación, realizamos una exploración inicial para observar cualquier incidencia en los datos de cada tabla, como serían posibles valores nulos y valores atípicos:

1- Seleccionamos y almacenamos en variables aquellas tablas que serán objeto de transformaciones posteriores

empleados <- dbGetQuery(con, "SELECT * FROM RRHH.empleados" )
proveedores <- dbGetQuery(con, "SELECT * FROM Produccion.Proveedores")
categorias <- dbGetQuery(con, "SELECT * FROM Produccion.Categorias")
productos <- dbGetQuery(con, "SELECT * FROM Produccion.Productos")
clientes <- dbGetQuery(con, "SELECT * FROM Ventas.Clientes")
transportistas <- dbGetQuery(con, "SELECT * FROM Ventas.Transportistas")
pedidos <- dbGetQuery(con, "SELECT * FROM Ventas.Ordenes")
detalle_pdos <- dbGetQuery(con, "SELECT * FROM Ventas.DetalleOrden")

2- Comenzamos la fase exploratoria inicial con cada tabla, para ello haremos los mismos pasos con cada tabla, con el objetivo de ver posibles incidencias en las variables, tanto categóricas como numéricas:

TABLA ‘empleados’

• Diagnóstico variables categóricas

skim_summary <- skim(empleados)

# Filtramos las variables no numéricas

non_numeric_vars <- skim_summary %>%
  filter(skim_type != "numeric") %>%
  pull(skim_variable)

# Creamos un resumen para cada variable no numérica

resumen_no_numericas <- lapply(non_numeric_vars, function(var) {
  empleado_var <- empleados[[var]]
  skim(data.frame(empleado_var))
})

# Nombramos los resúmenes por variable

names(resumen_no_numericas) <- non_numeric_vars

# Creamos un dataframe solo con las variables no numéricas

empleados_non_numeric <- empleados[ , non_numeric_vars, drop = FALSE]

# Obtenemos el resumen para estas variables

skim_non_numeric_summary <- skim(empleados_non_numeric)

# Impresión de variables

skim_non_numeric_summary

Data summary

Name	empleados_non_numeric
Number of rows	9
Number of columns	12
_______________________
Column type frequency:
character	10
Date	2
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	empty	n_unique	whitespace
apellido	0	1.00	4	9	0	9	0
nombre	0	1.00	3	8	0	8	0
titulo	0	1.00	17	24	0	4	0
titulocortesia	0	1.00	3	4	0	3	0
direccion	0	1.00	17	35	0	9	0
ciudad	0	1.00	6	8	0	5	0
region	4	0.56	2	2	0	1	0
codigopostal	0	1.00	5	5	0	9	0
pais	0	1.00	3	11	0	2	0
telefono	0	1.00	13	14	0	9	0

Variable type: Date

skim_variable	n_missing	complete_rate	min	max	median	n_unique
fecha_nac	0	1	1957-09-19	1986-01-27	1978-01-09	9
fecha_contrato	0	1	2020-04-01	2022-11-15	2021-10-17	8

Diagnóstico variables numéricas

# Selección de variables con la librería DlookR

# Obtenemos el análisis numérico

diagnostico <- diagnose_numeric(empleados)

# Añadimos y renombramos columnas

diagnostico_ordenado <- diagnostico %>%
  dplyr::select(variables, mean, min, Q1, median, Q3, max, zero, minus, outlier) %>%
  mutate(
    mediana_vs_media = abs(median - mean),
    pct_dif = abs(median - mean) / median * 100,
    media = mean,
    mediana = median,
    ceros = zero,
    negativos = minus,
    atipicos = outlier
  )

# Selección de columnas

diagnostico_ordenado <- diagnostico_ordenado %>% 
  dplyr::select(variables, media, min, Q1, mediana, Q3, max, ceros, negativos, atipicos, mediana_vs_media, pct_dif)

# Obtenemos el análisis de valores atípicos

diagnostico_outliers <- diagnose_outlier(empleados)

# Renombramos las columnas

diagnostico_outliers <- diagnostico_outliers %>% 
  mutate(
    num_atipicos = outliers_cnt,
    ratio_atipicos = outliers_ratio,
    media_con_atip = with_mean,
    media_sin_atip = without_mean
  )

# Seleccionamos columnas

diagnostico_outliers <- diagnostico_outliers %>% 
  dplyr::select(variables, num_atipicos, ratio_atipicos, media_con_atip, media_sin_atip)

# Unimos ambas tablas basadas en la columna `variables`

tabla_num_dlookr <- left_join(diagnostico_ordenado, diagnostico_outliers, by = "variables")

###################################


# Selección de variables con la librería Skimr

# Obtenemos el resumen completo

resumen_completo <- skim(empleados)

# Filtramos para obtener solo las variables de tipo numerico

resumen_numeric <- resumen_completo %>%
  filter(skim_type == "numeric")

# Selección de variables numericas y modificaciones

tabla_num_skim <- resumen_numeric %>% 
  dplyr::select(skim_variable, n_missing, numeric.hist) %>% 
  mutate(variables = skim_variable,
         nulos = n_missing
         )
# Selección final

tabla_num_skim <- tabla_num_skim %>% 
  dplyr::select(variables, nulos, numeric.hist)

# Union de tablas; de la libreria dlookr con la de la libreria skimr

tabla_final_num <- tabla_num_dlookr %>% 
  left_join(tabla_num_skim, by= "variables" )

# Reordenación de tabla final

tabla_final_num <- tabla_final_num %>% 
  dplyr::select(variables, numeric.hist, nulos, atipicos, ceros, negativos, media, min, Q1, mediana, Q3, max, mediana_vs_media, pct_dif, num_atipicos, ratio_atipicos, media_con_atip, media_sin_atip)

# VISUALIZACION FINAL VARIABLES NUMERICAS

# Ver en formato tabla

tabla_final_num %>%  flextable() 

variables	numeric.hist	nulos	atipicos	ceros	negativos	media	min	Q1	mediana	Q3	max	mediana_vs_media	pct_dif	num_atipicos	ratio_atipicos	media_con_atip	media_sin_atip
id_empleado	▇▇▃▇▇	0	0	0	0	5.00	1	3	5	7	9	0.00	0.000000	0	0	5.00	5.00
id_jefe	▂▅▅▁▇	1	0	0	0	3.25	1	2	3	5	5	0.25	8.333333	0	0	3.25	3.25

Repetimos el proceso con el resto de las tablas, con el fin de visualizar los datos clave para la limpieza y transformación de las variables de interés.

TABLA ‘proveedores’

Variables categóricas

Data summary

Name	proveedores_non_numeric
Number of rows	29
Number of columns	10
_______________________
Column type frequency:
character	10
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	empty	n_unique	whitespace
nombreproveedor	0	1.00	15	15	0	29	0
nombrecontacto	0	1.00	11	24	0	29	0
titulocontacto	0	1.00	5	35	0	15	0
direccion	0	1.00	13	58	0	29	0
ciudad	0	1.00	4	13	0	29	0
region	20	0.31	2	8	0	8	0
codigopostal	0	1.00	5	5	0	29	0
pais	0	1.00	3	12	0	16	0
telefono	0	1.00	8	15	0	29	0
fax	16	0.45	8	15	0	13	0

Variables numéricas

variables	numeric.hist	nulos	atipicos	ceros	negativos	media	min	Q1	mediana	Q3	max	mediana_vs_media	pct_dif	num_atipicos	ratio_atipicos	media_con_atip	media_sin_atip
id_proveedor	▇▇▇▇▇	0	0	0	0	15	1	8	15	22	29	0	0	0	0	15	15

TABLA ‘categorias’

Variables categóricas

Data summary

Name	categorias_non_numeric
Number of rows	8
Number of columns	2
_______________________
Column type frequency:
character	2
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	empty	n_unique	whitespace
nombrecategoria	0	1	6	17	0	8	0
descripcion	0	1	6	56	0	8	0

Variables numéricas

variables	numeric.hist	nulos	atipicos	ceros	negativos	media	min	Q1	mediana	Q3	max	mediana_vs_media	pct_dif	num_atipicos	ratio_atipicos	media_con_atip	media_sin_atip
id_categoria	▇▃▇▃▇	0	0	0	0	4.5	1	2.75	4.5	6.25	8	0	0	0	0	4.5	4.5

TABLA ‘productos’

Variables categóricas

Data summary

Name	productos_non_numeric
Number of rows	77
Number of columns	2
_______________________
Column type frequency:
character	1
logical	1
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	empty	n_unique	whitespace
nombreproducto	0	1	14	14	0	77	0

Variable type: logical

skim_variable	n_missing	complete_rate	mean	count
descontinuado	0	1	0.1	FAL: 69, TRU: 8

Variables numéricas

variables	numeric.hist	nulos	atipicos	ceros	negativos	media	min	Q1	mediana	Q3	max	mediana_vs_media	pct_dif	num_atipicos	ratio_atipicos	media_con_atip	media_sin_atip
id_producto	▇▇▇▇▇	0	0	0	0	39.000000	1.0	20.00	39.0	58.00	77.0	0.0000000	0.000000	0	0.000000	39.000000	39.000000
id_proveedor	▇▇▅▆▅	0	0	0	0	13.649351	1.0	7.00	13.0	20.00	29.0	0.6493506	4.995005	0	0.000000	13.649351	13.649351
id_categoria	▇▅▆▂▆	0	0	0	0	4.116883	1.0	2.00	4.0	6.00	8.0	0.1168831	2.922078	0	0.000000	4.116883	4.116883
preciounitario	▇▁▁▁▁	0	4	0	0	28.866364	2.5	13.25	19.5	33.25	263.5	9.3663636	48.032634	4	5.194805	28.866364	22.704384

TABLA ‘clientes’

Data summary

Name	clientes_non_numeric
Number of rows	91
Number of columns	10
_______________________
Column type frequency:
character	10
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	empty	n_unique	whitespace
nombrecliente	0	1.00	13	13	0	91	0
nombrecontacto	0	1.00	7	25	0	90	0
titulocontacto	0	1.00	5	29	0	12	0
direccion	0	1.00	12	48	0	91	0
ciudad	0	1.00	4	15	0	68	0
region	59	0.35	2	13	0	18	0
codigopostal	0	1.00	5	5	0	90	0
pais	0	1.00	3	11	0	21	0
telefono	0	1.00	8	17	0	91	0
fax	22	0.76	9	17	0	69	0

Variables numéricas

variables	numeric.hist	nulos	atipicos	ceros	negativos	media	min	Q1	mediana	Q3	max	mediana_vs_media	pct_dif	num_atipicos	ratio_atipicos	media_con_atip	media_sin_atip
id_cliente	▇▇▇▇▇	0	0	0	0	46	1	23.5	46	68.5	91	0	0	0	0	46	46

TABLA ‘transportistas’

Data summary

Name	transportistas_non_numeri…
Number of rows	3
Number of columns	2
_______________________
Column type frequency:
character	2
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	empty	n_unique	whitespace
compañia	0	1	19	19	0	3	0
telefono	0	1	14	14	0	3	0

Variables numéricas

variables	numeric.hist	nulos	atipicos	ceros	negativos	media	min	Q1	mediana	Q3	max	mediana_vs_media	pct_dif	num_atipicos	ratio_atipicos	media_con_atip	media_sin_atip
id_transportista	▇▁▇▁▇	0	0	0	0	2	1	1.5	2	2.5	3	0	0	0	0	2	2

TABLA ‘pedidos’

Data summary

Name	pedidos_non_numeric
Number of rows	830
Number of columns	9
_______________________
Column type frequency:
character	6
Date	3
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	empty	n_unique	whitespace
nombretransporte	0	1.00	11	17	0	241	0
direcciontransporte	0	1.00	12	48	0	241	0
ciudadtransporte	0	1.00	4	15	0	70	0
regiontransporte	507	0.39	2	13	0	19	0
codpostaltransporte	0	1.00	5	5	0	241	0
paistransporte	0	1.00	3	11	0	21	0

Variable type: Date

skim_variable	n_missing	complete_rate	min	max	median	n_unique
fecha_orden	0	1.00	2020-07-04	2022-05-06	2021-09-09	480
fecha_requerida	0	1.00	2020-07-24	2022-06-11	2021-10-07	454
fecha_transporte	21	0.97	2020-07-10	2022-05-06	2021-09-09	387

Variables numéricas

variables	numeric.hist	nulos	atipicos	ceros	negativos	media	min	Q1	mediana	Q3	max	mediana_vs_media	pct_dif	num_atipicos	ratio_atipicos	media_con_atip	media_sin_atip
id_orden	▇▇▇▇▇	0	0	0	0	10,662.500000	10,248.00	10,455.25	10,662.50	10,869.75	11,077.00	0.000000000	0.0000000	0	0.000000	10,662.500000	10,662.500000
id_cliente	▆▇▆▇▆	0	0	0	0	46.413253	1.00	24.00	46.00	68.00	91.00	0.413253012	0.8983761	0	0.000000	46.413253	46.413253
id_empleado	▆▇▁▃▅	0	0	0	0	4.403614	1.00	2.00	4.00	7.00	9.00	0.403614458	10.0903614	0	0.000000	4.403614	4.403614
id_transportista	▆▁▇▁▆	0	0	0	0	2.007229	1.00	1.00	2.00	3.00	3.00	0.007228916	0.3614458	0	0.000000	2.007229	2.007229
flete	▇▁▁▁▁	0	66	0	0	78.244205	0.02	13.38	41.36	91.43	1,007.64	36.884204819	89.1784449	66	7.951807	78.244205	51.397736

TABLA ‘detalle_pdos’

Variables categóricas, no hay

Variables numéricas

variables	numeric.hist	nulos	atipicos	ceros	negativos	media	min	Q1	mediana	Q3	max	mediana_vs_media	pct_dif	num_atipicos	ratio_atipicos	media_con_atip	media_sin_atip
id_orden	▇▇▇▇▇	0	0	0	0	10,659.37587007	10,248	10,451	10,657.0	10,862.5	11,077.00	2.37587007	0.02229399	0	0.000000	10,659.37587007	10,659.37587007
id_producto	▆▇▇▇▇	0	0	0	0	40.79303944	1	22	41.0	60.0	77.00	0.20696056	0.50478185	0	0.000000	40.79303944	40.79303944
preciounitario	▇▁▁▁▁	0	98	0	0	26.21851972	2	12	18.4	32.0	263.50	7.81851972	42.49195501	98	4.547564	26.21851972	21.33788527
cantidad	▇▃▁▁▁	0	87	0	0	23.81299304	1	10	20.0	30.0	130.00	3.81299304	19.06496520	87	4.037123	23.81299304	21.29013540
descuento	▇▁▁▁▁	0	0	1,317	0	0.05616705	0	0	0.0	0.1	0.25	0.05616705	Inf	0	0.000000	0.05616705	0.05616705

Una vez revisadas las variables de las tablas, se concluye que:

• con respecto a las variables categóricas no se van a realizar ninguna labor de limpieza de datos.

• con respecto a las variables numéricas, se realizarán transformaciones para suavizar el efecto de los valores atípicos de las variables preciounitario de la tabla ‘productos’, flete de la tabla ‘pedidos’ y preciounitario y cantidad de la tabla ‘detalle_pdos’

Visualizamos cuales transformaciones son las más adecuadas para las variables númericas detalladas

productos %>% 
  plot_normality(preciounitario)

Realizamos las transformaciones

sh_log_preciounit <- shapiro.test(log(productos$preciounitario)) 
sh_sqrt_preciounit <- shapiro.test(sqrt(productos$preciounitario))

Resultados

resultados <- data.frame(
  Transformación = c("Logarítmica", "Raíz cuadrada"),
  Estadístico = c(sh_log_preciounit$statistic, sh_sqrt_preciounit$statistic),
  `Valor p` = c(sh_log_preciounit$p.value, sh_sqrt_preciounit$p.value)
)

kable(resultados, digits = 4, caption = "Resultados de las Pruebas de Normalidad de Shapiro-Wilk")

Resultados de las Pruebas de Normalidad de Shapiro-Wilk

Transformación	Estadístico	Valor.p
Logarítmica	0.9808	0.2948
Raíz cuadrada	0.8155	0.0000

Realizamos la misma operación para todas las variables que queremos transformar

Resultados de las Pruebas de Normalidad de Shapiro-Wilk

Transformación	Estadístico	Valor.p
Logarítmica	0.9655	0
Raíz cuadrada	0.9018	0

Resultados de las Pruebas de Normalidad de Shapiro-Wilk

Transformación	Estadístico	Valor.p
Logarítmica	0.9855	0
Raíz cuadrada	0.8378	0

Resultados de las Pruebas de Normalidad de Shapiro-Wilk

Transformación	Estadístico	Valor.p
Logarítmica	0.9721	0
Raíz cuadrada	0.9739	0

CONCLUSIONES PARA LAS TRANSFORMACIONES:

• De la tabla ‘clientes’-> variable preciounitario -> transformación logarítmica., la cual es la única de todas las transformaciones en la que existe normalidad.

• De la tabla ‘pedidos’->variable flete -> transformación logarítmica, al tener un valor estadístico más alto.

• De la tabla ‘detalle_pdos’-> la variable preciounitario -> transformación logarítmica, al tener un valor estadístico más alto.

• De la tabla ‘detalle_pdos’-> la variable cantidad -> transformación de raíz cuadrada, al tener un valor estadístico más alto.

1.3 Creación de las tablas objeto de análisis (transformaciones)

1.3.1 Tabla detallada ‘detalle_pdos’

Para la creación de la tabla de ‘detalle_pdos’ tenemos que combinar varias tablas, además creamos un índice para evitar que se borren duplicados cara a futuros análisis con PowerBI y dos nuevas variables como son subtotal_pdo y total_pdo

WITH tabla1 AS
  (
    SELECT ROW_NUMBER() OVER (ORDER BY T1.id_orden) AS indice,
           T1.id_orden,
           T4.id_empleado,
           CONCAT (T4.nombre,' ', T4.apellido) AS nomb_empleado,
           T1.id_producto,
           T2.nombreproducto,
           T5.id_categoria,
           T5.nombrecategoria,
           T5.descripcion AS descrip_categoria,
           T3.id_cliente,
           T7.nombrecliente,
           T1.preciounitario AS pcu,
           T2.preciounitario AS pvu,
           T1.cantidad,
           T1.descuento,
           T2.preciounitario * T1.cantidad * (1 - T1.descuento) AS subtotal_pdo,  
           T6.nombreproveedor,
           T6.ciudad AS ciudad_proveedor,
           T6.pais AS pais_proveedor
    FROM Ventas.DetalleOrden T1
    LEFT JOIN Produccion.Productos T2
    ON T1.id_producto = T2.id_producto
    LEFT JOIN Ventas.Ordenes T3
    ON T1.id_orden = T3.id_orden
    LEFT JOIN RRHH.Empleados T4
    ON T3.id_empleado = T4.id_empleado
    LEFT JOIN Produccion.Categorias T5
    ON T2.id_categoria = T5.id_categoria
    LEFT JOIN Produccion.Proveedores T6
    ON T2.id_proveedor = T6.id_proveedor
    LEFT JOIN Ventas.Clientes T7
    ON T3.id_cliente = T7.id_cliente
  )
SELECT 
  *,
  SUM (subtotal_pdo) OVER (PARTITION BY id_orden) AS total_pdo
FROM
  tabla1

1.3.2 Tabla resumen ‘pedidos’

Para la creación de la tabla de pedidos tenemos que combinar varias tablas

WITH tabla1 AS
  (
    SELECT T1.id_orden,
           CONCAT (T4.nombre,' ', T4.apellido) AS nomb_empleado,
           YEAR(T1.fecha_orden) AS año,
           MONTH (T1.fecha_orden) AS mes,
           T1.fecha_orden AS fecha_pedido,
           T1.fecha_requerida AS fecha_entrega,
           T1.flete,
           T2.nombrecliente,
           T2.ciudad AS ciudad_cliente,
           T2.pais AS pais_cliente,
           T3.compañia AS transportista
    FROM ventas.Ordenes T1
    LEFT JOIN ventas.Clientes T2
    ON T1.id_cliente = T2.id_cliente
    LEFT JOIN ventas.Transportistas T3
    ON T1.id_transportista = T3.id_transportista
    LEFT JOIN RRHH.Empleados T4
    ON T1.id_empleado = T4.id_empleado
  )
SELECT 
  *
FROM 
  tabla1

Mostramos las primeras filas del resultado

1.4 Manipulaciones finales para calidad de datos:

Resumen de datos de la tabla ‘pedidos’

Data summary

Name	pedidos_non_numeric
Number of rows	830
Number of columns	7
_______________________
Column type frequency:
character	5
Date	2
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	empty	n_unique	whitespace
nomb_empleado	0	1	9	15	0	9	0
nombrecliente	0	1	13	13	0	89	0
ciudad_cliente	0	1	4	15	0	68	0
pais_cliente	0	1	3	11	0	21	0
transportista	0	1	19	19	0	3	0

Variable type: Date

skim_variable	n_missing	complete_rate	min	max	median	n_unique
fecha_pedido	0	1	2020-07-04	2022-05-06	2021-09-09	480
fecha_entrega	0	1	2020-07-24	2022-06-11	2021-10-07	454

Variables numéricas

variables	numeric.hist	nulos	atipicos	ceros	negativos	media	min	Q1	mediana	Q3	max	mediana_vs_media	pct_dif	num_atipicos	ratio_atipicos	media_con_atip	media_sin_atip
id_orden	▇▇▇▇▇	0	0	0	0	10,662.500000	10,248.00	10,455.25	10,662.50	10,869.75	11,077.00	0.0000000	0.000000000	0	0.000000	10,662.500000	10,662.500000
año	▃▁▇▁▅	0	0	0	0	2,021.142169	2,020.00	2,021.00	2,021.00	2,022.00	2,022.00	0.1421687	0.007034571	0	0.000000	2,021.142169	2,021.142169
mes	▇▅▂▃▆	0	0	0	0	6.046988	1.00	3.00	5.00	9.00	12.00	1.0469880	20.939759036	0	0.000000	6.046988	6.046988
flete	▇▁▁▁▁	0	66	0	0	78.244205	0.02	13.38	41.36	91.43	1,007.64	36.8842048	89.178444921	66	7.951807	78.244205	51.397736

Los rangos máximos de la variables ‘flete’, como se mencionó con anterioridad, son elevados y se revisarán por posibles valores atípicos.

Resumen de datos de la tabla ‘detalle_pdos’

Variables categóricas, no hay

Variables numéricas

variables	numeric.hist	nulos	atipicos	ceros	negativos	media	min	Q1	mediana	Q3	max	mediana_vs_media	pct_dif	num_atipicos	ratio_atipicos	media_con_atip	media_sin_atip
indice	▇▇▇▇▇	0	0	0	0	1,078.00000000	1.0	539.5	1,078.00	1,616.5	2,155.00	0.00000000	0.00000000	0	0.000000	1,078.00000000	1,078.00000000
id_orden	▇▇▇▇▇	0	0	0	0	10,659.37587007	10,248.0	10,451.0	10,657.00	10,862.5	11,077.00	2.37587007	0.02229399	0	0.000000	10,659.37587007	10,659.37587007
id_empleado	▆▇▁▃▃	0	0	0	0	4.33317865	1.0	2.0	4.00	7.0	9.00	0.33317865	8.32946636	0	0.000000	4.33317865	4.33317865
id_producto	▆▇▇▇▇	0	0	0	0	40.79303944	1.0	22.0	41.00	60.0	77.00	0.20696056	0.50478185	0	0.000000	40.79303944	40.79303944
id_categoria	▇▅▇▂▆	0	0	0	0	4.13549884	1.0	2.0	4.00	6.0	8.00	0.13549884	3.38747100	0	0.000000	4.13549884	4.13549884
id_cliente	▅▇▅▇▅	0	0	0	0	47.26589327	1.0	24.0	47.00	69.5	91.00	0.26589327	0.56573036	0	0.000000	47.26589327	47.26589327
pcu	▇▁▁▁▁	0	98	0	0	26.21851972	2.0	12.0	18.40	32.0	263.50	7.81851972	42.49195501	98	4.547564	26.21851972	21.33788527
pvu	▇▁▁▁▁	0	77	0	0	27.94868677	2.5	12.5	19.45	34.0	263.50	8.49868677	43.69504769	77	3.573086	27.94868677	23.17764196
cantidad	▇▃▁▁▁	0	87	0	0	23.81299304	1.0	10.0	20.00	30.0	130.00	3.81299304	19.06496520	87	4.037123	23.81299304	21.29013540
descuento	▇▁▁▁▁	0	0	1,317	0	0.05616705	0.0	0.0	0.00	0.1	0.25	0.05616705	Inf	0	0.000000	0.05616705	0.05616705
subtotal_pdo	▇▁▁▁▁	0	168	0	0	628.16842947	6.0	157.5	360.00	728.5	15,810.00	268.16842947	74.49123041	168	7.795824	628.16842947	420.17410393
total_pdo	▇▁▁▁▁	0	147	0	0	1,985.06199142	12.5	670.0	1,423.00	2,346.3	16,387.50	562.06199142	39.49838309	147	6.821346	1,985.06199142	1,545.51819920

Los rangos máximos de las variables ‘pcu’,‘pvu’ y ‘cantidad’ , como se mencionó con anterioridad, son elevados y se revisarán por posibles valores atípicos.

1.4.1 Revisión de la variable ‘flete’ de la tabla resumen ‘pedidos’

skim(pedidos)

Data summary

Name	pedidos
Number of rows	830
Number of columns	11
_______________________
Column type frequency:
character	5
Date	2
numeric	4
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	empty	n_unique	whitespace
nomb_empleado	0	1	9	15	0	9	0
nombrecliente	0	1	13	13	0	89	0
ciudad_cliente	0	1	4	15	0	68	0
pais_cliente	0	1	3	11	0	21	0
transportista	0	1	19	19	0	3	0

Variable type: Date

skim_variable	n_missing	complete_rate	min	max	median	n_unique
fecha_pedido	0	1	2020-07-04	2022-05-06	2021-09-09	480
fecha_entrega	0	1	2020-07-24	2022-06-11	2021-10-07	454

Variable type: numeric

skim_variable	n_missing	complete_rate	mean	sd	p0	p25	p50	p75	p100	hist
id_orden	0	1	10662.50	239.74	10248.00	10455.25	10662.50	10869.75	11077.00	▇▇▇▇▇
año	0	1	2021.14	0.70	2020.00	2021.00	2021.00	2022.00	2022.00	▃▁▇▁▅
mes	0	1	6.05	3.66	1.00	3.00	5.00	9.00	12.00	▇▅▂▃▆
flete	0	1	78.24	116.78	0.02	13.38	41.36	91.43	1007.64	▇▁▁▁▁

pedidos %>%
  e_charts(x = "") %>% 
  e_boxplot(flete, itemStyle = list(color = "lightblue")) %>%
  e_flip_coords() %>%  
  e_theme("infographic") %>% 
  e_tooltip(
    trigger = "item",
    formatter = htmlwidgets::JS("function(params) {
      var value = params.value;
      return 'Mínimo: ' + value[0] + '<br/>' 
           + 'Q1: ' + value[4] + '<br/>'
           + 'Mediana: ' + value[3] + '<br/>'
           + 'Q3: ' + value[2] + '<br/>'
           + 'Máximo: ' + value[1];
    }") 
  ) %>%
  e_legend(show = FALSE) %>%  
  e_color(c("darkblue", "darkred"))  

A nivel general vemos muchos valores atípicos de la variable flete; a continuación vamos a segregar la variable por empleados para ver aún más detalle de los valores atípicos

Realizamos la transformacion de la variable flete detallada en el punto de CONCLUSIONES PARA LAS TRANSFORMACIONES y la añadimos al dataset

pedidos$flete_log <- log(pedidos$flete)


skim(pedidos)

Data summary

Name	pedidos
Number of rows	830
Number of columns	12
_______________________
Column type frequency:
character	5
Date	2
numeric	5
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	empty	n_unique	whitespace
nomb_empleado	0	1	9	15	0	9	0
nombrecliente	0	1	13	13	0	89	0
ciudad_cliente	0	1	4	15	0	68	0
pais_cliente	0	1	3	11	0	21	0
transportista	0	1	19	19	0	3	0

Variable type: Date

skim_variable	n_missing	complete_rate	min	max	median	n_unique
fecha_pedido	0	1	2020-07-04	2022-05-06	2021-09-09	480
fecha_entrega	0	1	2020-07-24	2022-06-11	2021-10-07	454

Variable type: numeric

skim_variable	n_missing	complete_rate	mean	sd	p0	p25	p50	p75	p100	hist
id_orden	0	1	10662.50	239.74	10248.00	10455.25	10662.50	10869.75	11077.00	▇▇▇▇▇
año	0	1	2021.14	0.70	2020.00	2021.00	2021.00	2022.00	2022.00	▃▁▇▁▅
mes	0	1	6.05	3.66	1.00	3.00	5.00	9.00	12.00	▇▅▂▃▆
flete	0	1	78.24	116.78	0.02	13.38	41.36	91.43	1007.64	▇▁▁▁▁
flete_log	0	1	3.45	1.58	-3.91	2.59	3.72	4.52	6.92	▁▁▃▇▃

Comparamos la variable original con la variable transformada

Vemos una mejora de la normalidad en los datos de flete_log comparándola con su original, ya que se reduce el efecto de los valores atípicos.

1.4.2 Revisión de la variable ‘pcu’ de la tabla detallada ‘detalle_pdos’

Existen algunos precios atipicos en algunos proveedores, el departamento de compras revisará en consecuencia.

No obstante, realizamos la transformación de la variable pcu en el punto de CONCLUSIONES PARA LAS TRANSFORMACIONES; también realizamos la transformación de la variable pvu y ambas las añadimos al dataset

detalle_pdos$pcu_log <- log(detalle_pdos$pcu)
detalle_pdos$pvu_log <- log(detalle_pdos$pvu)


skim(detalle_pdos)

Data summary

Name	detalle_pdos
Number of rows	2155
Number of columns	22
_______________________
Column type frequency:
character	8
numeric	14
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	empty	n_unique	whitespace
nomb_empleado	0	1	9	15	0	9	0
nombreproducto	0	1	14	14	0	77	0
nombrecategoria	0	1	6	17	0	8	0
descrip_categoria	0	1	6	56	0	8	0
nombrecliente	0	1	13	13	0	89	0
nombreproveedor	0	1	15	15	0	29	0
ciudad_proveedor	0	1	4	13	0	29	0
pais_proveedor	0	1	3	12	0	16	0

Variable type: numeric

skim_variable	n_missing	complete_rate	mean	sd	p0	p25	p50	p75	p100	hist
indice	0	1	1078.00	622.24	1.00	539.50	1078.00	1616.50	2155.00	▇▇▇▇▇
id_orden	0	1	10659.38	241.38	10248.00	10451.00	10657.00	10862.50	11077.00	▇▇▇▇▇
id_empleado	0	1	4.33	2.49	1.00	2.00	4.00	7.00	9.00	▆▇▁▃▃
id_producto	0	1	40.79	22.16	1.00	22.00	41.00	60.00	77.00	▆▇▇▇▇
id_categoria	0	1	4.14	2.38	1.00	2.00	4.00	6.00	8.00	▇▅▇▂▆
id_cliente	0	1	47.27	25.85	1.00	24.00	47.00	69.50	91.00	▅▇▅▇▅
pcu	0	1	26.22	29.83	2.00	12.00	18.40	32.00	263.50	▇▁▁▁▁
pvu	0	1	27.95	31.61	2.50	12.50	19.45	34.00	263.50	▇▁▁▁▁
cantidad	0	1	23.81	19.02	1.00	10.00	20.00	30.00	130.00	▇▃▁▁▁
descuento	0	1	0.06	0.08	0.00	0.00	0.00	0.10	0.25	▇▁▁▁▁
subtotal_pdo	0	1	628.17	1031.40	6.00	157.50	360.00	728.50	15810.00	▇▁▁▁▁
total_pdo	0	1	1985.06	2099.97	12.50	670.00	1423.00	2346.30	16387.50	▇▁▁▁▁
pcu_log	0	1	2.95	0.77	0.69	2.48	2.91	3.47	5.57	▁▅▇▂▁
pvu_log	0	1	3.01	0.76	0.92	2.53	2.97	3.53	5.57	▁▅▇▂▁

Comparación de variables originales con variables transformadas

Vemos una mejora de la normalidad en los datos tanto de pcu_log como de pvu_log comparando ambas variables con sus originales, ya que se reduce el efecto de los valores atípicos.

1.4.3 Revisión de la variable ‘cantidad’ de la tabla detallada ‘detalle_pdos’

Dentro de la distribución normal de las cantidades vendidas, aparecen cantidades vendidas más elevadas que consideramos atipicas.

Realizamos la transformación de la variable cantidad descritas en el punto de CONCLUSIONES PARA LAS TRANSFORMACIONES y la añadimos al dataset

detalle_pdos$cantidad_sqrt <- sqrt(detalle_pdos$cantidad)

skim(detalle_pdos)

Data summary

Name	detalle_pdos
Number of rows	2155
Number of columns	23
_______________________
Column type frequency:
character	8
numeric	15
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	empty	n_unique	whitespace
nomb_empleado	0	1	9	15	0	9	0
nombreproducto	0	1	14	14	0	77	0
nombrecategoria	0	1	6	17	0	8	0
descrip_categoria	0	1	6	56	0	8	0
nombrecliente	0	1	13	13	0	89	0
nombreproveedor	0	1	15	15	0	29	0
ciudad_proveedor	0	1	4	13	0	29	0
pais_proveedor	0	1	3	12	0	16	0

Variable type: numeric

skim_variable	n_missing	complete_rate	mean	sd	p0	p25	p50	p75	p100	hist
indice	0	1	1078.00	622.24	1.00	539.50	1078.00	1616.50	2155.00	▇▇▇▇▇
id_orden	0	1	10659.38	241.38	10248.00	10451.00	10657.00	10862.50	11077.00	▇▇▇▇▇
id_empleado	0	1	4.33	2.49	1.00	2.00	4.00	7.00	9.00	▆▇▁▃▃
id_producto	0	1	40.79	22.16	1.00	22.00	41.00	60.00	77.00	▆▇▇▇▇
id_categoria	0	1	4.14	2.38	1.00	2.00	4.00	6.00	8.00	▇▅▇▂▆
id_cliente	0	1	47.27	25.85	1.00	24.00	47.00	69.50	91.00	▅▇▅▇▅
pcu	0	1	26.22	29.83	2.00	12.00	18.40	32.00	263.50	▇▁▁▁▁
pvu	0	1	27.95	31.61	2.50	12.50	19.45	34.00	263.50	▇▁▁▁▁
cantidad	0	1	23.81	19.02	1.00	10.00	20.00	30.00	130.00	▇▃▁▁▁
descuento	0	1	0.06	0.08	0.00	0.00	0.00	0.10	0.25	▇▁▁▁▁
subtotal_pdo	0	1	628.17	1031.40	6.00	157.50	360.00	728.50	15810.00	▇▁▁▁▁
total_pdo	0	1	1985.06	2099.97	12.50	670.00	1423.00	2346.30	16387.50	▇▁▁▁▁
pcu_log	0	1	2.95	0.77	0.69	2.48	2.91	3.47	5.57	▁▅▇▂▁
pvu_log	0	1	3.01	0.76	0.92	2.53	2.97	3.53	5.57	▁▅▇▂▁
cantidad_sqrt	0	1	4.53	1.82	1.00	3.16	4.47	5.48	11.40	▃▇▅▁▁

Comparación de variable original con la variable transformada

Vemos una mejora de la normalidad en la variable cantidad_sqrt, ya que se reduce el efecto de los valores atípicos.

1.5 Creación de nuevas variables

Se crean nuevas variables que enriqueceran el análisis posterior.

Creamos nuevas variables en la tabla detallada ‘detalle_pdos’

new_var_detalle_pdos <- sqldf("
                              SELECT
                                *,
                                CASE WHEN descuento > 0 THEN 1 ELSE 0 END AS descuento_bin
                              FROM
                                detalle_pdos
                              ")

totales_x_cliente <- sqldf("
                              SELECT 
                                nombrecliente,
                                SUM (total_pdo) AS total_x_cliente
                              FROM 
                                detalle_pdos
                              GROUP BY
                                nombrecliente
                              ")
head(totales_x_cliente)
NA   nombrecliente total_x_cliente
NA 1 Cliente AHPOP        53524.01
NA 2 Cliente AHXHT        13258.02
NA 3 Cliente AZJED        98310.83
NA 4 Cliente BSVAR         4587.30
NA 5 Cliente CCFIZ         8854.15
NA 6 Cliente CCKOT        58554.18

Una vez tengamos todas las variables que queremos estudiar, las guardamos en formato .csv para poder trabajarlas dentro de un informe de PowerBi

1.6 Análisis avanzado: insights relevantes con PowerBI

1.6.1 Usando la matriz de correlación (tabla detalle_pdos)

Buscamos la fuerza de las correlaciones entre las variables numericas de la tabla ‘detalle_pdos’, para ello seleccionaremos las variables ‘log_cantidad’, ‘beneficio’ y ‘aplica_descuento’; usamos una matriz de correlacion en PowerBI, la cual puede ser filtrada por empleados, clientes, productos y además por el gráfico de cantidades vendidas por categoria, obteniendo valiosísimos insights de negocio:

Cuadro base sin filtrar

Filtrado por empleado

Filtrado por cliente

Filtrado por producto

Filtrado por empleado y cliente

La matriz de correlación intenta explicar como de fuertes son las relaciones entre las variables; los resultados oscilan entre 1 (correlación positiva fuerte) y -1 (correlación negativa fuerte), siendo 0 cuando NO hay correlación.

A continuación, se muestra un análisis de ventas y fletes creados con PowerBI que ofrece información detallada aplicando técnicas de inteligencia de tiempo.

Elegimos la medida de total de ventas y obtenemos todos los cálculos de inteligencia de tiempo

Ahora elegimos el promedio de ventas

Por último, elegimos el total de fletes cobrados a clientes

1.7 Análisis avanzado: insights relevantes (segmentación de clientes K-means)

Para clusterizar a los clientes, primero debemos de hallar su frecuencia de compra

frecuencia <- sqldf("
                     WITH tabla1 AS
                       (
                        SELECT 
                          *,
                          COUNT(*) OVER (PARTITION BY nombrecliente) AS frec_compra
                        FROM
                          pedidos
                       )
                     SELECT 
                       T1.nombrecliente,
                       T1.frec_compra,
                       T2.total_x_cliente
                     FROM 
                       tabla1 T1
                       INNER JOIN totales_x_cliente T2
                       ON T1.nombrecliente = T2.nombrecliente
                     GROUP BY 
                       T1.nombrecliente,
                       T1.frec_compra,
                       T2.total_x_cliente
                       
                   ")

head(frecuencia)
NA   nombrecliente frec_compra total_x_cliente
NA 1 Cliente AHPOP           9        53524.01
NA 2 Cliente AHXHT           6        13258.02
NA 3 Cliente AZJED          15        98310.83
NA 4 Cliente BSVAR           4         4587.30
NA 5 Cliente CCFIZ           7         8854.15
NA 6 Cliente CCKOT          10        58554.18

Realizamos las transformaciones oportunas para hallar el número óptimo de cluster para este estudio

datos_frec_compra <- data.frame(frec_compra = frecuencia$frec_compra)


datos_frec_compra_scaled <- scale(datos_frec_compra)


k_values <- 1:10
wcss <- numeric(length(k_values))


for (k in k_values) {
  kmeans_result <- kmeans(datos_frec_compra_scaled, centers = k, nstart = 25)
  wcss[k] <- kmeans_result$tot.withinss
}


wcss_df <- data.frame(K = k_values, WCSS = wcss)

wcss_df %>%
  e_charts(K) %>%  # Usar K como eje x
  e_line(WCSS) %>%  # Crear la línea para WCSS
  e_title("Prueba del Codo" , left = "center") %>%  # Título
  e_x_axis(name = "Número de Clusters (K)", nameLocation = "middle") %>%  # Etiqueta del eje x
  e_y_axis(name = "WCSS", , nameLocation = "middle")  %>% # Etiqueta del eje y
  e_legend(show = FALSE) 

RESULTADO: número óptimo de cluster = 3

Ahora que ya sabemos los clusteres óptimos hallaremos el centroide de cada cluster y graficaremos todo para visualizar el resultado obtenido

set.seed(123)
kmeans_result <- kmeans(frecuencia[, c("frec_compra", "total_x_cliente")], centers = 3)


frecuencia <- frecuencia %>%
  mutate(cluster = as.factor(kmeans_result$cluster))


centroides <- frecuencia %>%
  group_by(cluster) %>%
  summarise(
    frec_compra_centroide = mean(frec_compra),
    total_centroide = mean(total_x_cliente)
  )

ggplot(frecuencia) +
  aes(x = frec_compra, y = total_x_cliente, color = as.factor(cluster)) + 
  geom_mark_hull(aes(fill = as.factor(cluster)), concavity = 5, expand = 0.03) + 
  geom_point(size = 1.5) + 
  geom_point(data = centroides, aes(x = frec_compra_centroide, y = total_centroide), 
             color = "blue", shape = 18, size = 4) + 
  theme_minimal() +
  labs(color = "Cluster", fill = "Cluster")

En el gráfico podemos ver como se agrupa por medio de la clusterización el comportamiento de compra de cada cliente, según la frecuencia de compra con respecto al total de ventas de cada cliente.