Optimización de Negocio con PostgreSQL y Análisis de Datos: Caso ElectroTienda

🏪 Contexto del Negocio

ElectroTienda es una cadena de tiendas de electrónica con presencia tanto física como online. La empresa busca optimizar sus operaciones y mejorar la rentabilidad, pero se enfrenta a los siguientes retos clave:

• 📉 Estancamiento en ventas en algunas categorías de productos.

• 📦 Exceso de stock: algunos productos permanecen demasiado tiempo en inventario.

• 💰 Falta de visibilidad sobre la rentabilidad de los clientes.

• 📢 Necesidad de mejorar la efectividad de sus campañas de marketing.

Estructura de la Base de Datos:

Diagrama entidad-realción:

Antes de comenzar con el análisis y posterior optimización se creará un diagrama de entidad-relación para comprender de una manera clara y visual los datos a tratar.

Para la creación de este diagrama se usará el programa pgadmin 4.

Figure1. Diagrama ERD de la base de datos

La base de datos está compuesta por las tablas: productos, líneas_venta, ventas y clientes.
El proceso comienza con un cliente, quien puede realizar múltiples ventas. Cada venta puede incluir varias líneas de venta, y cada una de estas líneas vincula un producto específico con la venta correspondiente. La tabla líneas_venta actúa como nexo entre las tablas de ventas y productos, y además almacena información detallada sobre cada producto vendido en una venta, como la cantidad, el precio, y posibles descuentos aplicados.

1.1 Análisis de la fidelidad del cliente:

La fidelidad del cliente es crucial para el éxito empresarial porque genera ingresos recurrentes, reduce costos de adquisición de nuevos clientes y fortalece la reputación de la marca

Los parámetros clave que aportan información sobre la fidelidad del cliente en este caso son:

• Frecuencia de compra: número de ventas realizadas por un cliente en un período determinado.

• Antigüedad del cliente: tiempo transcurrido desde la primera compra registrada.

• Valor total de las compras: suma del importe gastado por el cliente en todas sus compras.

• Intervalo entre compras: tiempo promedio entre una compra y la siguiente, que indica regularidad.

• Repetición de productos o categorías: grado en que un cliente compra los mismos productos o tipos de productos.

• Descuentos utilizados: frecuencia con la que el cliente compra con o sin descuentos, lo que puede reflejar compromiso más allá del precio.

• Número de devoluciones o reclamaciones: bajo nivel de incidencias puede indicar satisfacción y lealtad.

Teniendo en cuenta estos factores se procederá a la clasificación de los clientes en 4 categorías:

1. Cliente_VIP: ≥5 compras Y ≥€2000 gastados Y última compra ≤30 días.
2. Cliente_Frequente: 3-4 compras Y ≥€500 gastados Y última compra ≤90 días.
3. Cliente_Ocasional: 1-2 compras Y última compra ≤180 días.
4. Cliente_Inactivo: última compra >180 días (independientemente de otras
   métricas)

Query SQL para la clasificación:

SELECT c.nombre,
       c.email,
       Min(v.fecha_venta) as fecha_primera_compra,
       Max(v.fecha_venta) as fecha_ultima_compra,
       Count(v.venta_id) as num_total_compras,
       SUM(v.importe_total) as importe_total_gastado,
       current_date - Max(v.fecha_venta) as dias_ultima_compra,
	   CASE
	   	WHEN Count(v.venta_id) >= 5 AND SUM(v.importe_total) >= 20000 AND (current_date - Max(v.fecha_venta)) <= 30 THEN 'CLIENTE_VIP'
		WHEN (Count(v.venta_id) BETWEEN 3 AND 4) AND SUM(v.importe_total) >= 500 AND (current_date - Max(v.fecha_venta)) <= 90 THEN 'CLIENTE_FRECUENTE'
		WHEN (Count(v.venta_id) BETWEEN 1 AND 2) AND (current_date - Max(v.fecha_venta)) <= 180 THEN 'CLIENTE_OCASIONAL'
		WHEN (current_date - Max(v.fecha_venta)) > 180 THEN 'CLIENTE_INACTIVO'
		ELSE 'No Clasificado'
	   END AS "Fidelidad"
	   FROM ventas v
join   clientes c
ON     v.cliente_id = c.cliente_id
GROUP BY (c.nombre,c.email)
ORDER BY importe_total_gastado DESC

Esta clasificación permite a ElectroTienda priorizar, personalizar y optimizar decisiones en marketing, inventario y estrategia de ventas, lo que repercute directamente en más ventas, menos stock sobrante, mayor rentabilidad y campañas más eficientes.

La clasificación de clientes permite a ElectroTienda diseñar estrategias personalizadas que aumenten la efectividad y reduzcan el gasto. Por ejemplo, puede ofrecer beneficios exclusivos a los VIPs para fidelizarlos, cupones a los Frecuentes para impulsar nuevas compras, promociones limitadas a los Ocasionales para reactivarlos, y campañas específicas de recuperación para los Inactivos.

1.2 Ranking de productos por rentabilidad

Problema: El equipo de compras necesita identificar los productos más importantes del 
negocio. 

La clasificación por importancia se basa en dos criterios: el beneficio total dentro de cada categoría y el margen porcentual. Primero, se calcula el beneficio total de cada producto vendido y se ordena dentro de su categoría utilizando la función ROW_NUMBER(), lo que permite asignar una posición según el nivel de beneficio obtenido. Al mismo tiempo, se calcula el margen porcentual dividiendo el beneficio entre los ingresos totales y multiplicando por 100.

A partir de estos dos valores, se asigna una clasificación. Un producto se considera:

 "ESTRELLA" si ocupa la posición 1, 2 o 3  de beneficio en su categoría y además tiene un margen >= 20 %.

"IMPORTANTE" si el producto está entre las posiciones 1 y 5 de beneficio dentro de su categoría.

"REGULAR" si el margen >= 15 %, independientemente de su posición en el ranking. 

"REVISAR" si el margen es < 15 %.

Esta clasificación permite identificar con claridad qué productos son prioritarios para el negocio en términos de rentabilidad.

WITH results AS (
  SELECT 
    nombre_producto,
    categoria,
    marca, 
    COUNT(producto_id) AS total_unidades, 
    SUM(precio_venta) AS ingresos_totales, 
    SUM(precio_venta) - SUM(precio_coste) AS beneficio_total,
    ROUND((SUM(precio_venta) - SUM(precio_coste)) / NULLIF(SUM(precio_venta), 0) * 100, 2) AS margen_porcentual
  FROM productos
  -- Filtrar solo productos vendidos en 2024 si hubiera tabla de ventas
  GROUP BY nombre_producto, categoria, marca
),
ranking_por_categoria AS (
  SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY categoria ORDER BY beneficio_total DESC) AS posicion
  FROM results
)
SELECT 
  *,
  CASE 
    WHEN posicion IN (1, 2, 3) AND margen_porcentual >= 20 THEN 'ESTRELLA'
    WHEN posicion IN (1, 2, 3, 4, 5) THEN 'IMPORTANTE'
    WHEN margen_porcentual >= 15 THEN 'REGULAR'
    WHEN margen_porcentual < 15 THEN 'REVISAR'
    ELSE 'NOCLASIFICADO'
  END AS clasificacion
FROM ranking_por_categoria
ORDER BY categoria, beneficio_total DESC;

Resultado:

El resultado de esta consulta ofrece información estratégica clave para la empresa, con varias ventajas prácticas:

• Identificación de productos estrella: Detecta los productos más rentables y populares dentro de cada categoría, lo que permite priorizar su promoción y disponibilidad en stock.

• Optimización del catálogo: La clasificación ayuda a detectar productos con bajo margen o bajo rendimiento (“REVISAR”), lo que facilita decisiones sobre eliminación, renegociación con proveedores o ajustes de precio.

• Toma de decisiones basada en datos: Ofrece una base objetiva para campañas de marketing, gestión de inventario y planificación de compras, evitando decisiones intuitivas o sin fundamento.

• Mejora de márgenes y beneficios: Al destacar productos con alto margen y volumen, se puede centrar la estrategia comercial en ellos para aumentar la rentabilidad global del negocio.

• Segmentación clara por categoría: Facilita análisis comparativos dentro de cada tipo de producto, teniendo en cuenta las particularidades de cada segmento.

En resumen, este ranking proporciona una herramienta eficaz de inteligencia comercial que apoya la rentabilidad, eficiencia operativa y planificación estratégica.