Del Dato en Bruto al Insight de Negocio con SQL

En este proyecto desarrollamos un caso práctico de análisis de datos con SQL para mostrar cómo la analítica impulsa decisiones estratégicas. Partimos de una base de datos relacional con entidades clave —ventas, productos, tiendas y canales— y trazamos un recorrido de la limpieza a un sistema de recomendaciones. Primero aseguramos la calidad de la información: validamos granularidad, corregimos inconsistencias y consolidamos la facturación real a nivel tienda–producto–canal–fecha. Esa fase nos permitió definir una única fuente de verdad para reporting financiero y comercial. Luego diseñamos un modelo relacional con claves primarias y foráneas, integrando ventas con catálogo de productos, tiendas y canales de distribución, para habilitar consultas escalables y confiables. Con el modelo, generamos métricas clave: número de pedidos, cobertura geográfica, diversidad de catálogo y evolución histórica. Con estos indicadores analizamos el rendimiento: identificamos los tres canales con mayor facturación, medimos tendencias mensuales, construimos rankings de los 50 principales clientes y estudiamos la evolución por país y trimestre. En producto detectamos artículos de mayor margen, señalamos descuentos por encima del percentil 90 y calculamos el surtido mínimo que mantiene el 90 % de la facturación. En paralelo, segmentamos clientes según pedidos y facturación, estimamos potencial de desarrollo con benchmarks y localizamos cuentas inactivas para campañas de reactivación. Por último, desarrollamos un recomendador item–item que detecta patrones de compra conjunta y genera sugerencias personalizadas, fomentando ventas cruzadas y fidelización. El resultado es un marco de inteligencia comercial que conecta datos con decisiones operativas y estratégicas, mejorando foco comercial, rentabilidad y crecimiento sostenible. Demostramos que, con una estrategia bien diseñada en SQL y una cultura de datos, es posible convertir información dispersa en ventaja competitiva tangible y medible.

Caso Práctico SQL: De Datos Técnicos a Situaciones de Negocio

Este proyecto parte de una base de datos relacional que refleja las operaciones de ventas de una compañía. Antes de entrar en las consultas SQL y su interpretación como casos de negocio, presentamos el diagrama entidad–relación (ER) que describe las tablas, claves y relaciones principales.

El modelo integra entidades clave: productos, tiendas, canales, ventas y un sistema de recomendaciones. Esto asegura que las consultas SQL posteriores puedan responder preguntas de negocio sobre rentabilidad, segmentación de clientes, eficiencia de surtido y oportunidades de cross-selling.

SPRINT 1 - TAREA 1
Situación de negocio: validar la calidad de los datos y consolidar ventas en una tabla agregada para asegurar un reporting confiable.

Script SQL:
-- Importamos la bbdd a partir de dump: caso.sql

-- La activamos como la bbdd por defecto

use caso;



-- Revisamos el contenido de las 4 tablas

select * from canales;
select * from productos;
select * from tiendas;
select * from ventas;



-- ¿Cuantos registros tiene la tabla ventas?

select count(*) from ventas;



-- Revisamos el tipo de los datos

show columns from ventas;


-- ¿Está al nivel que necesitamos? tienda - producto - canal – fecha (no tiene que haber duplicados)

select count(*) as granularidad
from ventas
group by id_tienda, id_prod, id_canal, fecha
having granularidad <> 1;


-- Si hay duplicados los mostramos para identificar algún caso concreto

select distinct count(*) as granularidad, id_tienda, id_prod, id_canal, fecha
from ventas
group by id_tienda, id_prod, id_canal, fecha
having granularidad <> 1
order by id_tienda, id_prod, id_canal, fecha;

-- Revisamos un par de ellos

select *
from ventas
where
id_tienda in (1115, 1133)
and id_prod in (127110, 122140)
and id_canal = 5
and fecha in ('22/12/2016', '15/01/2018');

-- Creamos una nueva tabla de ventas_agr agregada a ese nivel, y además:
-- Cambiamos la fecha a tipo date y
-- Creamos un campo facturación como la multiplicación de la cantidad por el precio_oferta

select * from ventas;
Update table ventas_agr
SELECT
fecha, id_prod, id_tienda, id_canal,
cantidad, precio_oficial, precio_oferta,
cantidad * precio_oferta AS facturacion
FROM (
SELECT
STR_TO_DATE(fecha, '%d/%m/%Y') AS fecha,
id_prod, id_tienda, id_canal,
SUM(cantidad) AS cantidad,
AVG(precio_oficial) AS precio_oficial,
AVG(precio_oferta) AS precio_oferta
FROM ventas
GROUP BY 1,2,3,4
) subconsulta;

INSERT INTO ventas_agr (fecha, id_prod, id_tienda, id_canal, cantidad, precio_oficial, precio_oferta, facturacion)
SELECT
STR_TO_DATE(fecha, '%d/%m/%Y') AS fecha,
id_prod,
id_tienda,
id_canal,
SUM(cantidad) AS cantidad,
AVG(precio_oficial) AS precio_oficial,
AVG(precio_oferta) AS precio_oferta,
SUM(cantidad * precio_oferta) AS facturacion
FROM ventas
GROUP BY 1,2,3,4;



-- Revisamos la nueva tabla creada

select * from ventas_agr;



-- Revisamos cuantos registros tiene la nueva tabla

select count(*) from ventas_agr;

SPRINT 1 - TAREA 2
Situación de negocio: validar la calidad de los datos y consolidar ventas en una tabla agregada para asegurar un reporting confiable.

Script SQL:

-- Hacemos un join con la tabla productos para comprobar que aunque no esté relacionada se pueden hacer igualmente las consultas

select *
from productos as p
inner join ventas_agr as va
on p.id_prod = va.id_prod;

-- Pero para hacerlo bien, modificamos la tabla ventas_agr para:
-- Incluir un nuevo campo clave incremental llamado id_venta
-- Que id_prod sea un FK con su tabla y campo correspondiente
-- Que id_tienda sea un FK con su tabla y campo correspondiente
-- Que id_canal sea un FK con su tabla y campo correspondiente

alter table ventas_agr add id_venta int auto_increment primary key;
alter table ventas_agr add foreign key(id_prod) references productos(id_prod) on delete cascade,
add foreign key(id_tienda) references tiendas(id_tienda) on delete cascade,
add foreign key(id_canal) references canales(id_canal) on delete cascade;
alter table ventas_agr add id_venta int auto_increment primary key;

select * from ventas_agr;


-- Volvemos a crear el diagrama ER para ver cómo se relaciona ahora la nueva tabla (ver imagen)

-- Creamos una vista sobre la tabla ventas_agr que incluya el pedido
-- (Consideramos que será el mismo pedido cuando se haya hecho en la misma fecha, por la misma tienda y con el mismo canal)

create view ventas_agr_con_pedido as
with maestro_pedidos as (select fecha, id_tienda, id_canal, row_number() over() as id_pedido
from ventas_agr
group by fecha, id_tienda, id_canal)
select va.id_venta, mp.id_pedido, va.fecha, va.id_prod, va.id_tienda, va.id_canal, va.cantidad, va.precio_oficial, va.precio_oferta, va.facturacion
from ventas_agr as va
left join maestro_pedidos as mp
on (va.fecha = mp.fecha) and (mp.id_tienda = va.id_tienda) and (mp.id_canal = va.id_canal);


select * from ventas;

SPRINT 2 - TAREA 1
Situación de negocio: extraer métricas clave (pedidos, productos, tiendas, canales) que reflejen la dimensión real de la operación.

Script SQL:
-- ¿Cuántos pedidos tenemos en nuestro histórico?

select max(id_pedido) from ventas_agr_con_pedido;

-- ¿Desde qué día a qué dia tenemos datos?

select min(str_to_date(fecha,'%d/%m/%Y')) as primer_registro, max(str_to_date(fecha,'%d/%m/%Y')) as ultimo_registro from ventas;

-- ¿Cuántos productos distintos tenemos en nuestro catálogo?

select count(distinct(id_prod)) from productos;

-- ¿A cuántas tiendas distintas distribuimos?

select count(distinct(id_tienda)) from tiendas;

-- ¿A través de qué canales nos pueden hacer pedidos?

select distinct canal from canales;

SPRINT 2 - TAREA 2

Situación de negocio: extraer métricas clave (pedidos, productos, tiendas, canales) que reflejen la dimensión real de la operación.

Script SQL:
---------------------------------------------

-- Cuales son los 3 canales en los que más facturamos

select c.canal, round(sum(facturacion),2) as top_3_canales from ventas_agr as va
left join canales as c
on va.id_canal = c.id_canal
group by c.canal, va.id_canal
order by top_3_canales desc
limit 3;


-- Cual ha sido la evolución mensual de la facturación por canal en los últimos 12 meses completos

select canal, month(fecha) as mes, round(sum(facturacion),2) as facturacion_canal
from ventas_agr as v
left join canales c
on v.id_canal= c.id_canal
where fecha between '2017-07-01' and '2018-06-30'
group by canal, mes
order by canal, mes;

-- Localizamos el nombre de nuestros 50 mejores clientes (tiendas con mayor facturación)

Select nombre_tienda, round(sum(facturacion),2) as Facturacion_top_50 from ventas_agr as va
left join tiendas as t
on va.id_tienda = t.id_tienda
group by nombre_tienda
order by Facturacion_top_50 desc
limit 50;

-- Analizamos la evolución de la facturación de cada país por trimestre desde 2017

Select pais, year(fecha) as año, quarter(fecha) as trimestre, round(sum(facturacion),2) as facturacion
from ventas_agr as va
left join tiendas as t
on va.id_tienda = t.id_tienda
where fecha between '2017-01-01' and '2018-06-30'
group by pais, año, trimestre
order by año, trimestre, facturacion desc;

SPRINT 3 - TAREA 1

Situación de negocio: identificar los productos más rentables, controlar descuentos y optimizar el surtido manteniendo la facturación.

Script SQL:
---------------------------------------------
use caso;

-- Encontramos los 20 productos en los que sacamos mayor margen ((precio - coste) / coste * 100) en cada línea

with tabla_margen as (
select *, round((precio-coste)/coste*100,2) as margen
from productos)
select *
from(select id_prod, linea, producto, margen, row_number() over(partition by linea order by margen desc) as ranking
from tabla_margen) as ranking
where ranking <= 20;

-- Encontramos aquellos productos (su identificador) en los que estemos haciendo descuentos (en porcentaje) superiores al valor de descuento que deja por debajo al 90% de los descuentos

with tabla_descuentos as
(select *, round(((precio_oficial_medio-precio_oferta_medio)/precio_oficial_medio)*100,2) as descuento from (select id_prod, avg(precio_oficial) precio_oficial_medio, avg(precio_oferta) precio_oferta_medio
from ventas agr
group by id_prod) as nivel_producto)
select * from (select id_prod, descuento, cume_dist() over(order by descuento) as distr_acum from tabla_descuentos) as distr_acum
where distr_acum >= 0.9
order by descuento desc;

SPRINT 3 - TAREA 2

Situación de negocio: identificar los productos más rentables, controlar descuentos y optimizar el surtido manteniendo la facturación.

Script SQL:
---------------------------------------------

-- ¿Cuántos productos diferentes estamos vendiendo?

select count(distinct id_prod) as Total_Surtido
from productos;

-- ¿Con qué productos necesitaríamos quedarnos para mantener el 90% de la facturación actual?

with tabla_acum_facturacion as
(select *, round((facturacion_prod_acumulado/facturacion_prod_total),2) as facturacion_prod_acum_porc
from
(select id_prod, sum(Facturacion_Surtido) over(order by Facturacion_Surtido desc) as facturacion_prod_acumulado,
sum(Facturacion_Surtido) over() as facturacion_prod_total
from
(select id_prod, round(sum(facturacion),2) as Facturacion_Surtido from ventas_agr
group by id_prod
order by Facturacion_Surtido desc) as tabla_fact_prod) as tabla_temporal)
select id_prod, facturacion_prod_acumulado, facturacion_prod_acum_porc from tabla_acum_facturacion
where facturacion_prod_acum_porc <= 0.9
order by facturacion_prod_acum_porc asc;

-- Y por tanto ¿qué productos concretos podríamos eliminar y seguir manteniendo el 90% de la facturación?

with a_mantener as
(with tabla_acum_facturacion as
(select *, round((facturacion_prod_acumulado/facturacion_prod_total),2) as facturacion_prod_acum_porc
from
(select id_prod, sum(Facturacion_Surtido) over(order by Facturacion_Surtido desc) as facturacion_prod_acumulado,
sum(Facturacion_Surtido) over() as facturacion_prod_total
from
(select id_prod, round(sum(facturacion),2) as Facturacion_Surtido from ventas_agr
group by id_prod
order by Facturacion_Surtido desc) as tabla_fact_prod) as tabla_temporal)
select id_prod, facturacion_prod_acumulado, facturacion_prod_acum_porc from tabla_acum_facturacion
where facturacion_prod_acum_porc <= 0.9
order by facturacion_prod_acum_porc asc)
select distinct v.id_prod
from ventas_agr as v
left join a_mantener as m
on v.id_prod = m.id_prod
where m.id_prod is null;

SPRINT 3 - TAREA 3

Situación de negocio: identificar los productos más rentables, controlar descuentos y optimizar el surtido manteniendo la facturación.

Script SQL:
---------------------------------------------

-- ¿Qué líneas de producto diferentes estamos vendiendo?

select distinct linea from productos;


-- ¿Cual es la contribución (en porcentaje) de cada línea al total de facturación?

with ventas_linea as (
select linea, sum(facturacion) as facturacion_linea
from ventas_agr as va
left join productos as p
on va.id_prod = p.id_prod
group by p.linea
order by facturacion_linea desc)
select linea, facturacion_linea, round(facturacion_linea/sum(facturacion_linea) over(),2) as pct_linea
from ventas_linea;

-- ¿Podríamos prescindir de alguna línea de productos sin que afecte mucho a la facturación?

Podemos contestar a esta pregunta con el resultado de la consulta anterior.


-- Dentro de la línea que más facture ¿hay algún producto concreto que esté en tendencia?
-- Definimos tendencia como el crecimiento de Q2-2018 sobre Q1-2018
'Personal Accessories'
with producto_trimestre as (
select linea, producto, quarter(fecha) as trimestre, round(sum(facturacion),2) as facturacion
from ventas_agr as va
left join productos as p
on va.id_prod = p.id_prod
where linea = 'Personal Accessories' and fecha between '2018-01-01' and '2018-06-30'
group by producto, trimestre
order by producto, trimestre)
select producto, crecimiento from (
select linea, producto, trimestre, facturacion,
facturacion/lag(facturacion)
over(partition by producto order by trimestre) as
crecimiento from producto_trimestre) as subconsulta
where crecimiento is not null
order by crecimiento desc;

SPRINT 4 - TAREA 1

Situación de negocio: segmentar clientes según valor, calcular su potencial de crecimiento y diseñar estrategias de reactivación.

Script SQL:
---------------------------------------------

-- Segmentación de clientes:
-- Creamos una matriz de 4 segmentos en base al número de pedidos y la facturación de cada cliente (tienda)
-- Cada eje dividirá entre los que están por encima y por debajo de la media
-- Guardamos la consulta como una vista para ejecutarla frecuentemente

create view v_matriz_segmentacion as
with pedidos_fact_tienda as (
select nombre_tienda as cliente, count(id_pedido) as numero_pedidos, sum(facturacion) as facturacion_tienda from ventas_agr_con_pedido as tm
join tiendas as td on tm.id_tienda = td.id_tienda
group by cliente),
medias as (
select avg(numero_pedidos) as media_pedidos, avg(facturacion_tienda) media_facturacion from pedidos_fact_tienda)
select *,
case
when numero_pedidos <= media_pedidos and facturacion_tienda <= media_facturacion then 'P- F-'
when numero_pedidos > media_pedidos and facturacion_tienda <= media_facturacion then 'P+ F-'
when numero_pedidos <= media_pedidos and facturacion_tienda > media_facturacion then 'P- F+'
when numero_pedidos > media_pedidos and facturacion_tienda > media_facturacion then 'P+ F+'
else 'Error'
end as segmentacion
from pedidos_fact_tienda, medias;

-- Calculamos cuantos clientes tenemos en cada segmento de la matriz

select segmentacion, count(*) as clientes
from v_matriz_segmentacion
group by segmentacion;

-- Potencial de desarrollo:
-- Segmentamos las tiendas por su tipo, y calculamos el P75 de la facturación
-- Para cada tienda que esté por debajo del P75 calculamos su potencial de desarrollo (diferencia entre la facturación P75 y su facturación)

with nivel_tienda as (
select td.id_tienda, tipo, facturacion
from tiendas as td
inner join (select id_tienda, round(sum(facturacion),0) as facturacion
from ventas_agr
group by id_tienda) as va
on va.id_tienda = td.id_tienda),
P75_ideales as (
select tipo, facturacion as ideal from (select *, row_number() over(partition by tipo order by percentil) as ranking from (select *, round(((percent_rank() over(partition by tipo order by facturacion))*100),0) as percentil
from nivel_tienda) as percentil
where percentil >= 75.00) as ranking
where ranking = 1)
select nt.id_tienda, nt.tipo, facturacion, ideal,
case when (ideal-facturacion) >0 then round((ideal-facturacion),0)
when (ideal-facturacion) <=0 then 0
else 9999
end as potencial
from nivel_tienda as nt inner join P75_ideales as P75 on nt.tipo = P75.tipo
order by potencial desc;


-- Reactivación de clientes
-- Identificamos qué clientes que llevan más de 3 meses sin comprar (versus la última fecha disponible)

with ultima_fecha_total as (
select max(fecha) as ult_fecha_total from ventas_agr),
ultima_fecha_tienda as (
select id_tienda, max(fecha) as ult_fecha_tienda
from ventas_agr
group by id_tienda)
select * from (
select uft.*, datediff(uftt.ult_fecha_total, uft.ult_fecha_tienda) as dias_sin_comprar
from ultima_fecha_tienda as uft, ultima_fecha_total as uftt
) as dias_sin_comprar
where dias_sin_comprar >= 90;

SPRINT 5 - TAREA 1

Situación de negocio: implementar un sistema de recomendación de productos para incrementar ventas cruzadas y fidelización.

Script SQL:
---------------------------------------------

-- Generamos un sistema de recomendación item-item
-- Que localice aquellos productos que son comprados frecuentemente en el mismo pedido
-- Y recomiende a cada tienda según su propio historial de productos comprados

-- Creamos una tabla con el maestro de recomendaciones item-item

create table recomendador
select v1.id_prod as antecedente, v2.id_prod as consecuente, count(v1.id_pedido) as frecuencia
from ventas_agr_con_pedido as v1
inner join ventas_agr_con_pedido as v2
on v1.id_pedido = v2.id_pedido
and v1.id_prod != v2.id_prod
and v1.id_prod < v2.id_prod
group by v1.id_prod, v2.id_prod;

select * from recomendador
order by antecedente, frecuencia desc;

-- Creamos una consulta que genere las recomendaciones para cada cliente concreto
-- Y que sea capaz de eliminar los productos ya comprados por ese cliente concreto

with inputs_clientes as (
select id_prod, id_tienda
from ventas_agr
where id_tienda = '1201'),
productos_recomendados as (
select consecuente, sum(frecuencia) as frecuencia
from inputs_clientes as ic
left join recomendador as r
on r.antecedente = ic.id_prod
group by consecuente
order by frecuencia desc
)
select consecuente as recomendacion, frecuencia
from productos_recomendados as r
left join inputs_clientes as i
on r.consecuente = i.id_prod
where i.id_prod is null;

Conclusión
Gracias al modelo de datos y a las consultas SQL, se logra transformar la información transaccional en decisiones de negocio estratégicas: desde la validación de datos y análisis de rentabilidad hasta la segmentación avanzada y la recomendación personalizada.