La extensiones que he encontrado a la fecha de este artículo, nos incrementan las posibilidades, pero siguen siendo limitadas, porque al fin y al cabo las necesidades de obtener información a partir de los datos están íntimamente relacionadas con la actividad, y la empresa que los requiere. Y las que ofrecen mayores posibilidades se fundamentan en la creación de consultas empleando el lenguaje SQL, por lo que aunque faciliten la labor, si no conocemos algo del lenguaje y de la multitud de tablas que componen Magento y sus extensiones instaladas, de poco nos servirán más allá de los ejemplos de los que disponga la extensión.

Por lo que finalmente decidí realizar consultas empleando phpMyAdmin. A continuación se mostrarán varias consultas creadas que funcionan a la perfección con Magento 1.8.1, y considero que no deben mostrar problemas con versiones anteriores hasta las 1.5, y posteriores hasta la 1.9.

Por otra parte, intentaré explicar las consultas para que aquellos que, al igual que yo, sean neófitos en SQL puedan orientarse para necesidades propias. Por cierto, para quién también sea neófito en phpMyAdmin, decir que las consultas las ejecutamos en la pestaña que encontraremos que se titula SQL.

Las consultas que veremos en este artículo son las siguientes:

1. Consulta para generar un reporte de los primeros 500 clientes por volumen de compras.
2. Consulta para Cálculo de valor de la duración como cliente de los 100 primeros clientes.
3. Consulta de Historial de pedidos del cliente incluyendo los artículos.
4. Consulta de Historial de pedidos del cliente incluyendo los artículos y siendo el estado del pedido completado.
5. Consulta para obtener los correos electrónicos de los clientes que compraron productos con un determinado conjunto de atributos.
6. Consulta para obtener correos, nombre y apellidos de los clientes que han comprado una determinada referencia en un periodo de tiempo.
7. Consulta para obtener las cantidad pedida de cada artículo de la tienda.

Consulta para generar un reporte de los primeros 500 clientes por volumen de compras

Esta consulta nos muestra una tabla con los 500 primeros clientes por volumen de compras, indicando el importe comprado a lo largo de su vida como cliente, el número de pedidos realizados, y la fecha y hora del último pedido.

Para ello y como veremos en la traducción de la sentencia emplea dos tablas de la base de datos de Magento.

SELECT
    CONCAT('$', FORMAT(SUM(sales_flat_order.`grand_total`), 2)) AS 'Ventas Acumuladas',
    COUNT(sales_flat_order.entity_id) AS 'Pedidos',
    customer_entity.email AS 'Email',
    MAX(sales_flat_order.created_at) AS 'Fecha del pedido más reciente'
FROM `customer_entity`
LEFT JOIN sales_flat_order ON customer_entity.entity_id = sales_flat_order.customer_id
GROUP BY customer_entity.entity_id
ORDER BY SUM(sales_flat_order.`grand_total`) DESC
LIMIT 500

Voy a intentar traducir la consulta SQL. Viene a decir:

SELECCIONA

La SUMA de los valores de la COLUMNA grand_total de la TABLA sales_flat_order, le das formato redondeando a dos decImales, y al presentar los datos vas a llamar a esta columna "Ventas Acumuladas".

Los valores de la COLUMNA entity_id de la TABLA sales_flat_order y los CUENTAS, y al presentar los datos vas a llamar a esta columna "Pedidos".

El valor de la COLUMNA email de la tabla customer_entity, y al presentar los datos vas a llamar a esta columna "Email".

El MÁXIMO valor de la COLUMNA create_at de la TABLA customer_entity, y al presentar los datos vas a llamar a esta columna "Fecha del pedido más reciente".

DESDE la tabla customer_entity

DESDE/CON la tabla customer_entity

COMBINANDO la tabla sales_flat_order sobre el valor de la COLUMNA entity_id de la tabla customer_entity y que sea igual al valor de la COLUMNA customer_id de la tabla sales_flat_order

AGRUPA por el valor de la COLUMNA entity_id de la tabla customer_entity

ORDENA por la SUMA del valor de la columna grand_total de la TABLA sales_flat_order, de forma DESCENDENTE

LIMITA los resultadosa obtenidos a 500

Como es de suponer podemos cambiar 500 por el número que nos sea más adecuado.

Consulta para Cálculo de valor de la duración como cliente de los 100 primeros clientes

Nos muestra una tabla con el mail, nombre, apellidos y total de los pedidos de los 100 primeros clientes por volumen de compra ordenada de mayor a menor.

En esta consulta también se emplean dos tablas de la base de datos.

SELECT DISTINCT customer_email, customer_firstname, customer_lastname, SUM( subtotal_invoiced ) AS Total
FROM `sales_flat_order`
GROUP BY customer_email
ORDER BY SUM( subtotal_invoiced ) DESC
LIMIT 100

La traducción sería:

SELECCIONA valores DISTINTOS de las COLUMNAS customer_email, customer_firstname, customer_lastname, la SUMA de subtotal_invoice, y este último al presentar los datos los llamas TOTAL.

DESDE la tabla sales_flat_order

AGRUPA los valores por el valor de la COLUMNA customer_email

ORDENA POR la SUMA de los valores de la COLUMNA subtotal_invoiced de forma DESCENDENTE

LIMITA los resultados a 100

Consulta de Historial de pedidos del cliente incluyendo los artículos

Nos muestra una tabla con todos los valores de todas la columnas de las dos tablas involucradas.

Este informe realmente es poco útil por la cantidad de columnas que muestra y porque una gran parte de ellas se encuentran repetidas. Pero nos sirve para orientarnos en la búsqueda de un informe que nos muestre el historial de pedidos de un cliente, o de todos.

select e.*,sfoi.* from sales_flat_order e

left join sales_flat_order_item sfoi on (e.entity_id=sfoi.order_id)

where e.customer_email = '[your_customer_id]';

La traducción sería:

SELECCIONA todas la COLUMNAS de la TABLA sales_flat_order que llamamos e, y todas las COLUMNAS de la TABLA sales_flat_order_items que llamamos sfoi

COMBINA con la tabla sales_flat_order_item, que llamamos sfoi, CUANDO el valor de la COLUMNA entity_id de la TABLA "e" (sales_falt_order) sea igual al valor de la COLUMNA order_id de la TABLA sales_flat_order_item, DONDE el valor de la COLUMNA customer_email de la tabla sales_flat_order sea igual al valor especificado. Es decir, your_customer_id es el valor de una dirección email de uno de nuestros clientes.

En la próxima consulta veremos un listado que tendrá más utilidad, no obstante hay que reconocer el mérito de esta consulta pues nos facilita y ofrece una guía para obtener el listado que finalmente más nos convenga.

Consulta de Historial de pedidos del cliente incluyendo los artículos y siendo el estado del pedido completado

Este listado nos muestra una tabla con el detalle de todos los pedidos de todos los clientes.

La consulta nos muestra los campos que hemos seleccionado de la tabla, si bien dichos campos pueden modificarse fácilmente, tan solo debemos ver la estructura de las dos tablas seleecionadas y ver los nombres de las columnas.

Empleamos dos tablas de la base de datos: sales_flat_order y sales_flat_order_item.

select e.entity_id,e.base_subtotal,e.base_tax_amount,e.discount_amount,e.subtotal,e.weight,e.customer_email,e.customer_firstname,

e.customer_lastname,sfoi.sku,sfoi.name,sfoi.qty_ordered,sfoi.price,sfoi.row_total,sfoi.row_total_incl_tax from sales_flat_order e

left join sales_flat_order_item sfoi on (e.entity_id=sfoi.order_id)

where e.customer_email LIKE "%@%" AND e.status="complete";

La traducción es:

SELECCIONA los valores de las COLUMNAS entity_id, base_subtotal, base_tax_amount,discount_amount, subtotal, weight, customer_email, customer_firstname, customer_lastname de la TABLA sales_falt_order que llamamos "e", y los valores de las COLUMNAS sku, name, qty_ordered, price, row_total, row_total_incl_tax de la TABLA sales_flat_order_item que luego llamamos "sfoi".

COMBINA la TABLA sales_flat_order_item que llamamos "sfoi" SOBRE la TABLA sales_flat_order cuando el valor de la COLUMNA entity_id SEA IGUAL al valor de la COLUMNA order_id de la TABLA sales_flat_order_item

DONDE el valor de la COLUMNA customer_email de la TABLA sales_flat_order SEA COMO %@% (% es el comodín de antes de la @ y de después) Y el valor de la COLUMNA status SEA IGUAL a complete.

Si quisiéramos obtener la consulta de todos los pedidos detallados y con estado Completo de un cliente cambiamos la última línea por la siguiente:

where e.customer_email = '[email del cliente]' AND e.status="complete";

Del mismo modo podemos suprimir del AND en adelante para no filtrar por el estado del pedido, o bien podemos añadir más AND para añadir más filtros.

Consulta para obtener los correos electrónicos de los clientes que compraron productos con un determinado conjunto de atributos

Esta consulta nos muestra una tabla con las direcciones de email de los clientes que han comprado algún producto que pertenece a un determinado conjunto de atributos.

Presenta el interés de que se trata de una consulta que tiene otra consulta anidada y que emplea tres tablas.

SELECT
    DISTINCT(o.customer_email)
FROM
    sales_flat_order o
    LEFT JOIN
        sales_flat_order_item oi
    ON
        o.entity_id = oi.order_id
WHERE
  oi.product_id IN
     (SELECT
         entity_id
      FROM
          catalog_product_entity
      WHERE
          attribute_set_id = 5
      )

Donde sustituiremos el 5 por el id del conjunto de atributos. Para conocer el id del conjunto de atributos iremos a la tabla eav_attribute_set.

La traducción sería algo similar a:

 SELECCIONA valores DISTINTOS de la COLUMNA customer_email de la TABLA sales_flat_order que más adelante llamaremos "o"

DESDE/CON la TABLA sales_flat_order que llamamos "o"

COMBINA de la TABLA sales_flat_order_item que llamamos "oi" los valores de la COLUMNA order_id que SEAN IGUALES al valor de la COLUMNA entity_id de la tabla sales_flat_order que hemos llamado "o"

DONDE (aquí empieza la consulta anidada) el valor de la COLUMNA product_id de la TABLA que hemos llamado "oi" ESTÁ DENTRO de la SELECCIÓN del valor de la COLUMNA entity_id de la TABLA catalog_product_entity DONDE el valor de la COLUMNA attribute_set_id ES IGUAL a 5

Vamos a intentar traducir la traducción que es algo engorrosa. Le estamos diciendo a MySQL que seleccione valores distintos, o lo que vendría ser lo mismo que los agrupe por valores que sean igual para evitar así que aparezcan los mismos valores a lo largo de la tabla, es decir que aparezca una misma dirección varias veces. Usamos SELECT DISTINCT porque es el valor a seleccionar, y no un filtro por el que agrupar.

Después le decimos que el identificador del número de pedido de los productos que contiene el pedido que está en una tabla (sales_flat_order_item) deben coincidir con el identificador del número de pedido de la tabla que contiene los datos generales del pedido (sales_flat_order). Y además, que muestre las direcciones email de aquellos pedidos en que alguno de sus productos coincidan con el conjunto de atributos indicado, y para ello debe comparar que en la tabla catalog_product_entity (la que contiene los productos asociados a un conjunto de atributos) se encuentre el identificador del producto.

Llegando a comprender esta consulta, podremos obtener otras de gran interés como por ejemplo, obtener el listado de clientes que hayan comprado un producto determinado que incluso es más simple que la explicada en este epígrafe.

Consulta para obtener correos, nombre y apellidos de los clientes que han comprado una determinada referencia en un periodo de tiempo

Este listado es una variante del anterior. Emplea las mismas tablas, solo que varían los datos a mostrar y en lugar de buscar un determinado conjunto de atributos, compara la referencia, y si hemos sido cuidadosos al establecer las referencias, por ejemplo incluyendo dígitos para categoría, tipo, marca, fabricante, etc., podremos obtener mucha y valiosa información.

El listado también incluye dos condiciones nuevas respecto al anterior: El pedido debe estar Completado y debe estar comprendido en un periodo de tiempo.

SELECT DISTINCT (
sfo.customer_email
), customer_firstname, customer_lastname
FROM sales_flat_order sfo
LEFT JOIN sales_flat_order_item sfoi ON sfo.entity_id = sfoi.order_id
WHERE sfo.status = "complete"
AND sfoi.updated_at >= "2014-01-01"
AND sfoi.product_id
IN (

SELECT entity_id
FROM catalog_product_entity
WHERE sku LIKE '14%'
)

Consulta para obtener las cantidad pedida de cada artículo de la tienda

Nos mostrará un listado de la cantidad de cada producto pedida (facturada) en la tienda agrupada por la referencia (sku) y en orden descendente de mayor a menor.

Esta consulta solo usa una tabla, pero tiene la virtud personalizar el resultado de la consulta mediante el renombrado de cada columna del listado.

SELECT sku AS IDENTIFICADOR, name AS NOMBRE, (
SUM( qty_invoiced ) - SUM( qty_refunded )
) AS TOTAL
FROM sales_flat_order_item
WHERE product_type = 'simple'
AND qty_invoiced <> 0
GROUP BY sku
ORDER BY TOTAL DESC;

La traducción sería:

SELECCIONA los valores de la COLUMNA sku y muéstralo con el nombre IDENTIFICADOR, los valores de la COLUMNA name y muéstralo con el nombre NOMBRE, y el resultado de SUMAR los valores de la COLUMNA qty_invoiced MENOS la SUMA de los valores de la COLUMNA qty_refunded y muéstralo con el nombre TOTAL

DE la TABLA sales_flat_order_item

DONDE el valor de la COLUMNA product_type SEA IGUAL a simple

Y el valor de la columna qty_invoiced SEA DISTINTO de cero

AGRUPADOS POR el valor de la COLUMNA sku

ORDENADOS POR el TOTAL en orden DESCENDENTE.

De momento es todo. Considero que estos reportes son de interés, no sólo para la gestión habitual, sino como guía para obtener otros personalizados a nuestras necesidades y que podremos obtener apoyándonos en este artículo y la abundante documentación existente sobre MySQL.