5 Consultas SQL de gran utilidad para PrestaShop

En anteriores ocasiones, hemos remarcado la utilidad de PrestaShop a la hora de poder gestionar los productos y analizar debidamente cada una de las áreas del e-Commerce. Una de las ventajas de usar PrestaShop es la posibilidad de llevar a cabo consultas SQL a la base de datos de la tienda, pudiendo obtener información de gran relevancia así como mejorar el rendimiento de los trabajos realizados de forma diaria. 

Acceder a estas consultas es muy sencillo y basta con entrar en Parámetros Avanzados / Base de datos, dentro del panel de nuestra tienda PrestaShop. Hoy te traemos 5 consultas SQL de gran utilidad a la hora de trabajar para un e-Commerce:

1- Consulta SQL para obtener los clientes con más pedidos en PrestaShop

SELECT 
    c.id_customer, 
    CONCAT(c.firstname, ' ', c.lastname) AS cliente, 
    COUNT(o.id_order) AS total_pedidos,
    SUM(o.total_paid_tax_incl) AS total_gastado
FROM 
    ps_customer c
JOIN 
    ps_orders o ON o.id_customer = c.id_customer
WHERE 
    o.current_state IN (2, 3, 4)
GROUP BY 
    c.id_customer
ORDER BY 
    total_pedidos DESC
LIMIT 10;

Paso a paso:

1. Selección de columnas:
   • Muestra el ID del cliente (id_customer).
   • Combina el nombre y apellido del cliente (firstname y lastname) como cliente.
   • Calcula el total de pedidos realizados por cada cliente (total_pedidos).
   • Calcula la suma total gastada por cada cliente, incluyendo impuestos (total_gastado).
2. JOIN: Une las tablas ps_customer y ps_orders mediante el id_customer para relacionar los clientes con sus pedidos.
3. WHERE: Filtra los pedidos según su estado (current_state) para incluir solo aquellos que estén en los estados con ID 2, 3 o 4 (por ejemplo, completados o pagados).
4. GROUP BY: Agrupa los resultados por cliente (id_customer) para calcular el número de pedidos y el monto total gastado de cada uno.
5. ORDER BY: Ordena los clientes en función del número de pedidos realizados (total_pedidos) en orden descendente.
6. LIMIT: Limita el resultado a los 10 clientes con mayor cantidad de pedidos.

Resultado de ejemplo:

Esta consulta es útil para identificar a los clientes más activos en tu tienda PrestaShop, basándose en el número de pedidos completados. Permite segmentar campañas de marketing dirigidas a estos clientes, optimizar la fidelización mediante descuentos o promociones exclusivas, y mejorar la atención al cliente al ofrecer un servicio más personalizado. Además, proporciona datos clave para analizar el rendimiento de la tienda y ajustar estrategias de ventas, asegurando que se mantenga una base de clientes leales y satisfechos.

2- Consulta SQL para obtener los productos con más pedidos en PrestaShop

SELECT 
    p.id_product AS product_id, 
    pl.name AS product_name,
    COALESCE(SUM(od.product_quantity) - (
        SELECT COALESCE(SUM(ord.product_quantity), 0)
        FROM ps_order_return_detail ord
        LEFT JOIN ps_order_return orr ON ord.id_order_return = orr.id_order_return
        WHERE ord.id_order_detail = od.id_order_detail
        AND orr.state IN (4) 
    ), 0) AS total_sold
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product AND pl.id_lang = 1) 
LEFT JOIN ps_order_detail od ON p.id_product = od.product_id
LEFT JOIN ps_orders o ON od.id_order = o.id_order
WHERE o.valid = 1
GROUP BY p.id_product, pl.name
ORDER BY total_sold DESC;

Paso a paso:

1. 1. Selección de columnas:
      • product_id: Obtiene el ID del producto (id_product) de la tabla ps_product.
      • product_name: Obtiene el nombre del producto (name) desde la tabla ps_product_lang para el idioma especificado (en este caso, pl.id_lang = 1).
      • total_sold: Calcula la cantidad total vendida de cada producto, descontando las devoluciones. Se hace con COALESCE(SUM(od.product_quantity) - (subconsulta), 0):
        • SUM(od.product_quantity): Suma las cantidades de productos vendidas.
        • Subconsulta: Resta la cantidad de productos devueltos para cada pedido (ps_order_return_detail) si el estado de la devolución (orr.state) es 4 (por ejemplo, ‘devuelto’).
   2. Subconsulta (en el cálculo de total_sold):
      • La subconsulta calcula la cantidad de productos devueltos para cada detalle de pedido (od.id_order_detail).
      • Se hace un LEFT JOIN entre las tablas ps_order_return_detail y ps_order_return para verificar las devoluciones de cada pedido, filtrando por el estado 4 (probablemente “devuelto”).
      • COALESCE(SUM(ord.product_quantity), 0): Devuelve la cantidad de productos devueltos o 0 si no hay devoluciones.
   3. JOIN:
      • LEFT JOIN ps_product_lang pl: Se une la tabla ps_product_lang para obtener el nombre del producto en el idioma especificado.
      • LEFT JOIN ps_order_detail od: Se une la tabla ps_order_detail para obtener los detalles de los pedidos asociados a los productos.
      • LEFT JOIN ps_orders o: Se une la tabla ps_orders para asegurar que solo se incluyan los pedidos válidos (o.valid = 1).
   4. WHERE: Filtra los resultados para incluir solo los pedidos válidos (o.valid = 1).
   5. GROUP BY: Agrupa los resultados por producto (p.id_product y pl.name) para sumar las cantidades vendidas y calcular el total de productos vendidos (total_sold).
   6. ORDER BY: Ordena los productos por la cantidad total vendida (total_sold) de mayor a menor.

   Resultado esperado

Resultado de ejemplo:

Esta consulta es útil para identificar los productos más vendidos en tu tienda, lo que te permite enfocar tus esfuerzos de marketing y reposición de inventario en los productos más populares. También proporciona información valiosa sobre qué artículos tienen mayor demanda, ayudando a mejorar la estrategia de ventas y optimizar la gestión de stock.

3- Consulta SQL para obtener los productos con contenido vacío en PrestaShop

SELECT 
    p.id_product, 
    pl.name, 
    pl.description_short, 
    pl.description
FROM 
    ps_product p
JOIN 
    ps_product_lang pl ON p.id_product = pl.id_product
WHERE 
    pl.description_short IS NULL 
    OR pl.description_short = '' 
    OR pl.description IS NULL 
    OR pl.description = '';

Paso a paso:

1. Selección de columnas: Muestra el id_product, el nombre del producto (name), la descripción corta (description_short) y la descripción completa (description).
2. JOIN: Une las tablas ps_product y ps_product_lang para acceder a la información de los productos y sus descripciones en los diferentes idiomas.
3. WHERE: Filtra los productos donde la descripción corta o la descripción completa están vacías o nulas, es decir, productos que no tienen estas descripciones completadas.

Resultado de ejemplo:

Esta consulta es útil para identificar productos que no tienen descripciones completas en tu tienda, lo que te permite asegurarte de que todos los productos tengan la información adecuada para los clientes. Esto es esencial para mejorar la experiencia de compra y optimizar el SEO de la tienda, ya que los productos sin descripciones podrían generar una menor conversión o visibilidad en los motores de búsqueda.

4- Consulta SQL para obtener las categorías con contenido vacío en PrestaShop

SELECT 
    cl.id_category, 
    cl.name, 
    cl.description
FROM 
    ps_category c
JOIN 
    ps_category_lang cl ON c.id_category = cl.id_category
WHERE 
    cl.description IS NULL 
    OR cl.description = '';

Paso a paso:

1. Selección de columnas: Muestra el id_category, el nombre de la categoría (name) y la descripción (description).
2. JOIN: Une las tablas ps_category y ps_category_lang para acceder a la información de las categorías en diferentes idiomas.
3. WHERE: Filtra las categorías donde la descripción está vacía o es nula, es decir, aquellas que no tienen descripción asignada.

Resultado de ejemplo:

Esta consulta es útil para identificar las categorías de productos que carecen de descripciones. Tener descripciones completas en todas las categorías es crucial para mejorar la experiencia del usuario y optimizar el SEO del sitio, ya que las categorías sin descripciones pueden generar confusión para los clientes y un menor posicionamiento en los motores de búsqueda.

5- Consulta SQL para obtener los productos con descuento específico en PrestaShop

SELECT 
    p.id_product AS 'ID del Producto',
    pl.name AS 'Nombre del Producto',
    p.wholesale_price * 1.21 AS 'Precio con IVA',
    sp.reduction AS 'Valor Descuento',
    CASE 
        WHEN sp.reduction_type = 'percentage' THEN sp.reduction * 100
        ELSE (sp.reduction / p.wholesale_price) * 100
    END AS 'Porcentaje_descuento',
    sp.reduction_type AS 'Tipo de descuento'
FROM 
    ps_product p
LEFT JOIN 
    ps_product_lang pl ON p.id_product = pl.id_product AND pl.id_lang = 1
LEFT JOIN 
    ps_specific_price sp ON p.id_product = sp.id_product
WHERE 
    p.active = 1 
    AND sp.reduction > 0
ORDER BY 
    p.id_product;

Paso a paso:

1. Selección de columnas: Muestra el id_product, el nombre del producto (name), el precio con IVA (asumido al 21%), el valor del descuento (reduction), el porcentaje de descuento calculado (dependiendo del tipo de descuento), y el tipo de descuento (porcentaje o valor absoluto).
2. JOIN: Se unen las tablas ps_product, ps_product_lang (para obtener el nombre del producto en el idioma 1) y ps_specific_price (para obtener la información del descuento aplicado). Si la tienda no cuenta con más de un idioma instalado, no es necesario agregar la restricción del idioma en esta línea de la consulta. En cambio, si cuentas con más de un idioma, revisa las IDs de estos antes de aplicarlos en consultas SQL. En alguna tienda el idioma español puede tener asignada una ID distinta de 1. 
3. WHERE: Filtra los productos activos y aquellos que tienen algún descuento (mayor a 0). 
4. ORDER BY: Ordena los productos por su id_product.

Resultado de ejemplo:

Esta consulta es útil para obtener un listado detallado de los productos con descuento en la tienda PrestaShop, incluyendo el precio con IVA y el porcentaje o valor del descuento. Ayuda a gestionar promociones y descuentos, permitiendo a los administradores ver fácilmente qué productos están siendo promocionados y cómo se aplican los descuentos, lo que facilita la toma de decisiones para futuras campañas comerciales.

Recuerda que estas consultas podrían no funcionar en algunas tiendas de forma directa, especialmente si el nombre de las tablas es distinto, existen varios idiomas en la tienda, los estados de los pedidos son distintos o cambian las IDs. Revisa cada paso realizado en función de las particularidades de tu tienda y, cualquier duda, contacta con nosotros a través del correo [email protected]