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

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

knitr::opts_chunk$set(echo = TRUE)

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

Obtención de las tablas de la BBDD:

SELECT TABLE_NAME
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_TYPE = 'BASE TABLE'
Displaying records 1 - 10
TABLE_NAME
PaisCosto
PaisIngresos
Empleados
Proveedores
Categorias
Productos
Clientes
Transportistas
Ordenes
DetalleOrden

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


SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE
  FROM INFORMATION_SCHEMA.COLUMNS
Displaying records 1 - 10
TABLE_NAME COLUMN_NAME DATA_TYPE IS_NULLABLE
PaisCosto pais nvarchar NO
PaisCosto periodo nvarchar YES
PaisCosto costo money YES
PaisIngresos pais nvarchar NO
PaisIngresos periodo nvarchar YES
PaisIngresos ingreso money YES
fctVentas fecha_orden date NO
fctVentas id_orden int NO
fctVentas id_cliente int YES
fctVentas id_producto int NO 
SELECT * FROM RRHH.Empleados
9 records
id_empleado apellido nombre titulo titulocortesia fecha_nac fecha_contrato direccion ciudad region codigopostal pais telefono id_jefe
1 García María Director Ejecutivo Sra. 1968-12-08 2020-05-01 7890 - 20th Ave. E., Apt. 2A Seattle WA 10003 USA (206) 555-0101 NA
2 Martínez Juan Vicepresidente de Ventas Dr. 1972-02-19 2020-08-14 9012 W. Capital Way Tacoma WA 10001 USA (206) 555-0100 1
3 López Ana Gerente de Ventas Sra. 1983-08-30 2020-04-01 2345 Moss Bay Blvd. Kirkland WA 10007 USA (206) 555-0103 2
4 Fernández José Representante de Ventas Sr. 1957-09-19 2021-05-03 5678 Old Redmond Rd. Redmond WA 10009 USA (206) 555-0104 3
5 Díaz Luis Gerente de Ventas Sr. 1975-03-04 2021-10-17 8901 Garrett Hill Londres NA 10004 Reino Unido (71) 234-5678 2
6 González Laura Representante de Ventas Sra. 1983-07-02 2021-10-17 3456 Coventry House, Miner Rd. Londres NA 10005 Reino Unido (71) 345-6789 5
7 Ruíz Carlos Representante de Ventas Sr. 1980-05-29 2022-01-02 6789 Edgeham Hollow, Winchester Way Londres NA 10002 Reino Unido (71) 123-4567 5
8 Hernández María Representante de Ventas Sra. 1978-01-09 2022-03-05 4567 - 11th Ave. N.E. Seattle WA 10006 USA (206) 555-0102 3
9 Pérez Patricia Representante de Ventas Sra. 1986-01-27 2022-11-15 1234 Houndstooth Rd. Londres NA 10008 Reino Unido (71) 456-7890 5

Despúes de visualizar las tablas, comenzamos a realizar las transformaciones con las tablas necesarias para el analisis; para ello usaremos: - Transformaciones iniciales usando el conector odbc para realizar manipulaciones de tablas directamente desde el servidor. - transformaciones intermedias con la libreria dplyr. - Transformaciones finales con sqldf.

  WITH T1 AS
    (
  SELECT id_empleado, 
         CONCAT(nombre, ' ', apellido) AS nombre_empleado, 
         titulo,
         DATEDIFF(YEAR, fecha_nac, '20220506') AS edad_empleado,
         DATEDIFF(DAY, fecha_contrato, '20220506') AS dias_antig_empleado,
         ciudad AS ciudad_empleado,
         pais AS pais_empleado
  FROM RRHH.Empleados
    )
  SELECT id_empleado,
         nombre_empleado,
         titulo,
         edad_empleado,
         CASE WHEN dias_antig_empleado < 0 THEN 1 
         ELSE dias_antig_empleado END AS antiguedad_rev,
         ciudad_empleado,
         pais_empleado
  FROM T1
  ORDER BY antiguedad_rev DESC
empleados_x_antiguedad

Visualizamos la antiguedad de los empleados de mayor a menor agrupados por el pais del empleado:

library(ggplot2)
library(plotly)
## 
## Adjuntando el paquete: 'plotly'
## The following object is masked from 'package:ggplot2':
## 
##     last_plot
## The following object is masked from 'package:stats':
## 
##     filter
## The following object is masked from 'package:graphics':
## 
##     layout
p_empleados_x_antiguedad <- ggplot(empleados_x_antiguedad) + 
  aes(x = antiguedad_rev, y = reorder(nombre_empleado, antiguedad_rev), fill = titulo) + 
  geom_bar(stat = "summary", fun = "sum") +
  scale_fill_hue(direction = 1) +
  theme_minimal() +
  facet_wrap(vars(pais_empleado)) +
  labs(x = "Antigüedad", title = "Antigüedad de Empleados por País y Título") +
  theme(
    axis.text.y = element_text(size = 8),       
    legend.position = "none",                     
    axis.title.y = element_blank(),               
    plot.title = element_text(hjust = 0.2)       
  )


plotly_empleados_x_antiguedad <- ggplotly(p_empleados_x_antiguedad)


plotly_empleados_x_antiguedad

Visualización múltiple de la antiguedad del empleado, por empleados, titulo y edad, ordenados por la antiguedad del empleado:

library(ggplot2)
library(plotly)

p_empleados_x_antiguedad2 <- ggplot(empleados_x_antiguedad) +
  aes(x = antiguedad_rev, y = reorder(nombre_empleado, antiguedad_rev), fill = titulo) +
  geom_bar(stat = "summary", fun = "sum") +
  scale_fill_hue(direction = 1) +
  theme_minimal() +
  facet_wrap(vars(edad_empleado)) +
  labs(x = "Antigüedad", title = "Antigüedad de Empleados por País, Título y Edad") +
  theme(
    legend.position = "none",         
    axis.title.y = element_blank()    
  )



plotly_empleados_x_antiguedad2 <- ggplotly(p_empleados_x_antiguedad2)

plotly_empleados_x_antiguedad2

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

SELECT T1.id_producto,
       T1.nombreproducto,
       T2.nombrecategoria,
       T2.descripcion AS desc_categ,
       T3.nombreproveedor,
       T3.ciudad AS ciudad_proveedor,
       T3.pais AS pais_proveedor
FROM Produccion.Productos T1
LEFT JOIN Produccion.Categorias T2
ON T1.id_categoria = T2.id_categoria
LEFT JOIN Produccion.Proveedores T3
ON T1.id_proveedor = T3.id_proveedor

productos
SELECT T1.id_orden,
       CONCAT (T4.nombre,' ', T4.apellido) AS nomb_empleado,
       T1.fecha_orden AS fecha_pedido,
       T1.fecha_requerida AS fecha_entrega,
       T1.flete,
       T1.ciudadtransporte AS ciudad_envio,
       T1.paistransporte AS pais_envio,
       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
library(dplyr)
## 
## Adjuntando el paquete: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
glimpse(pedidos)
## Rows: 830
## Columns: 11
## $ id_orden       <int> 10248, 10249, 10250, 10251, 10252, 10253, 10254, 10255,…
## $ nomb_empleado  <chr> "Luis Díaz", "Laura González", "José Fernández", "Ana L…
## $ fecha_pedido   <date> 2020-07-04, 2020-07-05, 2020-07-08, 2020-07-08, 2020-0…
## $ fecha_entrega  <date> 2020-08-01, 2020-08-16, 2020-08-05, 2020-08-05, 2020-0…
## $ flete          <dbl> 32.38, 11.61, 65.83, 41.34, 51.30, 58.17, 22.98, 148.33…
## $ ciudad_envio   <chr> "Reims", "Münster", "Rio de Janeiro", "Lyon", "Charlero…
## $ pais_envio     <chr> "Francia", "Alemania", "Brasil", "Francia", "Bélgica", …
## $ nombrecliente  <chr> "Cliente ENQZT", "Cliente FAPSM", "Cliente IBVRG", "Cli…
## $ ciudad_cliente <chr> "Reims", "Münster", "Rio de Janeiro", "Lyon", "Charlero…
## $ pais_cliente   <chr> "Francia", "Alemania", "Brasil", "Francia", "Bélgica", …
## $ transportista  <chr> "Transportista ZHISN", "Transportista GVSUA", "Transpor…
SELECT T1.id_orden,
       T1.id_producto,
       T1.preciounitario AS pcu,
       T2.preciounitario AS pvu,
       T1.cantidad,
       T1.descuento
FROM Ventas.DetalleOrden T1
LEFT JOIN Produccion.Productos T2
ON T1.id_producto = T2.id_producto

detalle_orden

MANIPULACION INTERMEDIA ENTRE DOS VARIABLES:

library(dplyr)

# Suponiendo que ya has obtenido ambos dataframes: 
# 'detalle_orden' y 'productos' son los resultados de las consultas
detalle_pdos <- detalle_orden %>%
  left_join(productos, by = "id_producto")

# Visualiza los primeros registros de la unión
detalle_pdos

MANIPULACIONES FINALES PARA CALIDAD DE DATOS:

summary(pedidos) # El rango máximo de la variable 'flete' es muy alto y se revisará por posibles valores atípicos.
##     id_orden     nomb_empleado       fecha_pedido        fecha_entrega       
##  Min.   :10248   Length:830         Min.   :2020-07-04   Min.   :2020-07-24  
##  1st Qu.:10455   Class :character   1st Qu.:2021-02-24   1st Qu.:2021-03-25  
##  Median :10662   Mode  :character   Median :2021-09-09   Median :2021-10-07  
##  Mean   :10662                      Mean   :2021-08-08   Mean   :2021-09-05  
##  3rd Qu.:10870                      3rd Qu.:2022-02-04   3rd Qu.:2022-03-05  
##  Max.   :11077                      Max.   :2022-05-06   Max.   :2022-06-11  
##      flete         ciudad_envio        pais_envio        nombrecliente     
##  Min.   :   0.02   Length:830         Length:830         Length:830        
##  1st Qu.:  13.38   Class :character   Class :character   Class :character  
##  Median :  41.36   Mode  :character   Mode  :character   Mode  :character  
##  Mean   :  78.24                                                           
##  3rd Qu.:  91.43                                                           
##  Max.   :1007.64                                                           
##  ciudad_cliente     pais_cliente       transportista     
##  Length:830         Length:830         Length:830        
##  Class :character   Class :character   Class :character  
##  Mode  :character   Mode  :character   Mode  :character  
##                                                          
##                                                          
## 
summary(detalle_pdos) # Los rangos máximos de las variables 'pcu','pvu' y 'cantidad' son elevados y se revisarán por posibles valores atípicos.
##     id_orden      id_producto         pcu              pvu        
##  Min.   :10248   Min.   : 1.00   Min.   :  2.00   Min.   :  2.50  
##  1st Qu.:10451   1st Qu.:22.00   1st Qu.: 12.00   1st Qu.: 12.50  
##  Median :10657   Median :41.00   Median : 18.40   Median : 19.45  
##  Mean   :10659   Mean   :40.79   Mean   : 26.22   Mean   : 27.95  
##  3rd Qu.:10862   3rd Qu.:60.00   3rd Qu.: 32.00   3rd Qu.: 34.00  
##  Max.   :11077   Max.   :77.00   Max.   :263.50   Max.   :263.50  
##     cantidad        descuento       nombreproducto     nombrecategoria   
##  Min.   :  1.00   Min.   :0.00000   Length:2155        Length:2155       
##  1st Qu.: 10.00   1st Qu.:0.00000   Class :character   Class :character  
##  Median : 20.00   Median :0.00000   Mode  :character   Mode  :character  
##  Mean   : 23.81   Mean   :0.05617                                        
##  3rd Qu.: 30.00   3rd Qu.:0.10000                                        
##  Max.   :130.00   Max.   :0.25000                                        
##   desc_categ        nombreproveedor    ciudad_proveedor   pais_proveedor    
##  Length:2155        Length:2155        Length:2155        Length:2155       
##  Class :character   Class :character   Class :character   Class :character  
##  Mode  :character   Mode  :character   Mode  :character   Mode  :character  
##                                                                             
##                                                                             
## 
library(ggplot2)

ggplot(pedidos) +
  aes(x = flete, fill = nomb_empleado) +
  geom_density() +
  scale_fill_hue(direction = 1) +
  theme_minimal() +
  facet_wrap(vars(nomb_empleado)) +
  labs( title = "Flete facturado por Empleado") +
  theme(legend.position = "none",
        plot.title = element_text(hjust = 0.5)       
  )

library(sqldf)
## Cargando paquete requerido: gsubfn
## Cargando paquete requerido: proto
## Cargando paquete requerido: RSQLite
rev_flete <- sqldf("
                     WITH inicial AS
                       (
                           SELECT
                           T1.nomb_empleado,
                           T1.nombrecliente,
                           T1.flete,
                           AVG (T1.flete) OVER() AS media_flete,
                           AVG (T1.flete) OVER (PARTITION BY T1.nombrecliente) AS media_flete_x_cliente
                           FROM pedidos T1
                           GROUP BY T1.nomb_empleado,
                                    T1.nombrecliente
                       ), 
                         tabla1 AS
                       (
                          SELECT *,
                                 flete - media_flete_x_cliente AS diferencia
                          FROM inicial
                          ORDER BY diferencia ASC
                       )
                      SELECT nomb_empleado,
                             media_flete,
                             SUM(diferencia) OVER (PARTITION BY nomb_empleado) dif_x_empleado
                      FROM tabla1
                      GROUP BY nomb_empleado,
                               media_flete
                      ORDER BY dif_x_empleado ASC
                  ")
rev_flete

Podemos concluir que los empleados concentran los fletes facturados en importes pequeños y facturan algunos fletes elevados (atípicos); incluso así, podemos observar que el total de flete que factura cada empleado tiene un desvio negativo con respecto a la media de flete general.

library(ggplot2)

ggplot(detalle_pdos) +
  aes(x = "", y = pcu, fill = nombreproveedor) +
  geom_boxplot() +
  scale_fill_hue(direction = 1) +
  coord_flip() +
  theme_minimal() +
  theme(legend.position = "none", plot.title = element_text(hjust = 0.5)) +
  facet_wrap(vars(nombreproveedor)) +  
  labs(title = "Precio unitario de producto por Proveedor")

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

Añadimos nuevas variables a los dataset ‘pedidos’ y ‘detalle_pdos’


-- Precalculos en SQL con nuevas variables a graficar

WITH inicial AS (
    SELECT T1.id_orden,
           CONCAT(T4.nombre, ' ', T4.apellido) AS nomb_empleado,
           T1.fecha_orden AS fecha_pedido,
           T1.fecha_requerida AS fecha_entrega,
           T1.flete,
           T1.ciudadtransporte AS ciudad_envio,
           T1.paistransporte AS pais_envio,
           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
), acum AS
(
SELECT *,
       SUM(flete) OVER (ORDER BY fecha_pedido) AS imp_flete_acum,
       AVG(flete) OVER (ORDER BY fecha_pedido) AS avg_flete_acum,
       SUM(flete) OVER (PARTITION BY nombrecliente ORDER BY fecha_pedido) AS flete_acum_cliente,
       AVG(flete) OVER (PARTITION BY nombrecliente ORDER BY fecha_pedido) AS avg_flete_cliente,
       COUNT(id_orden) OVER (PARTITION BY nombrecliente ORDER BY fecha_pedido) AS pedidos_acum_cliente,
       SUM(flete) OVER (PARTITION BY pais_envio ORDER BY fecha_pedido) AS flete_acum_pais,
       SUM(flete) OVER (PARTITION BY ciudad_cliente ORDER BY fecha_pedido) AS flete_acum_ciudad,
       LAG(fecha_pedido, 0) OVER (PARTITION BY nombrecliente ORDER BY fecha_pedido) AS fecha_pdo_anterior
FROM inicial
)
SELECT 
  *,
  DATEDIFF (DAY, fecha_pdo_anterior, fecha_pedido) AS dias_entre_pdos
FROM 
  acum

acumulados