Las tiendas online o comercios electrónicos recogen al día cientos de datos de clientes que, la mayoría de ocasiones son olvidados en nuestras bases de datos. El error, es no utilizar esos datos, no sólo para aumentar nuestras ventas, sino para ofrecer a nuestros clientes ofertas de valor y productos o descuentos personalizados a sus necesidades.
Uno de nuestros clientes nos consultó como podía sacar partido a todos esos datos (compras realizadas, productos, ofertas, etc.), para lo que desarrollamos un módulo que realiza consultas en la base de datos de Prestashop, y nos ofrece, en formato .csv los datos que estamos buscando, con la opción también de sincronizarse con las APIS de las herramientas de email marketing que utiliza.

A continuación os ofrecemos algunas de estas consultas, que podéis realizar directamente en la base de datos, para obtener más información y personalizar vuestras comunicaciones con los clientes de vuestras tienda online Prestahop.

Las siguientes consultas están hechas teniendo en cuenta que el prefijo de las tablas de la BBDD es ps_, en caso de ser otro, sólo habría que sustituir este prefijo por el que corresponda.

Realizar una oferta basándonos en la fecha de compra de un producto

Muy útil, por ejemplo, en una ecommerce de cosmética o de ropa infantil, donde podremos realizar una oferta de una producto que hay que volver a comprar o que necesita renovarse. Para ello, vamos a obtener un listado de las compras desde una determinada fecha (en el ejemplo: 2016/01/01), con nombres de productos, atributos y datos de clientes (nombre, apellidos y correo electrónico):

SELECT product_lang.name As Producto, COALESCE (attribute_lang.name, '') AS Atributo, customer.firstname As Nombre, customer.lastname As Apellidos, customer.email As Correo
FROM ps_orders AS orders JOIN ps_order_detail AS order_detail ON (order_detail.id_order = orders.id_order)
    JOIN ps_customer AS customer ON (orders.id_customer = customer.id_customer)
    JOIN ps_product_lang AS product_lang ON (product_lang.id_product = order_detail.product_id)
    LEFT JOIN ps_attribute_lang AS attribute_lang ON (attribute_lang.id_attribute = order_detail.product_attribute_id)
WHERE orders.date_add > '2016/01/01' AND product_lang.id_shop=1 AND product_lang.id_lang = 1  AND (attribute_lang.id_lang = 1 OR attribute_lang.id_lang IS NULL)
GROUP BY Correo

Conocer las categorías con más ventas y hacer ofertas a clientes que las visitaron

Por ejemplo, para una tienda online de libros, si queremos ofrecer a los clientes que ya compraron un libro de una categoría determinada, una oferta sobre la última novedad publicada. Para ello, obtendremos un listado de las compras desde una determinada fecha de productos de una categoría (en el ejemplo: que contenga Loquesea), con nombres de productos, atributos y datos de clientes (nombre, apellidos y correo electrónico):

SELECT product_lang.name As Producto, COALESCE (attribute_lang.name, '') AS Atributo, customer.firstname As Nombre, customer.lastname As Apellidos, customer.email As Correo
FROM ps_orders AS orders JOIN ps_order_detail AS order_detail ON (order_detail.id_order = orders.id_order)
    JOIN ps_customer AS customer ON (orders.id_customer = customer.id_customer)
    JOIN ps_product_lang AS product_lang ON (product_lang.id_product = order_detail.product_id)
    LEFT JOIN ps_attribute_lang AS attribute_lang ON (attribute_lang.id_attribute = order_detail.product_attribute_id)
    JOIN ps_category_product AS category_product ON (category_product.id_product = order_detail.product_id)
    JOIN ps_category_lang AS category_lang ON (category_lang.id_category = category_product.id_category)
WHERE orders.date_add > '2016/01/01' AND product_lang.id_shop=1 AND product_lang.id_lang = 1  AND (attribute_lang.id_lang = 1 OR attribute_lang.id_lang IS NULL) AND category_lang.id_lang = 1  AND category_lang.name LIKE '%Loquesea%'
GROUP BY Correo

En el caso de que queramos una categoría concreta (por ID), obtendremos  un listado de las compras desde una determinada fecha de productos de una categoría (en este caso por id numérico: 20), con nombres de productos, atributos y datos de clientes (nombre, apellidos y correo electrónico):

SELECT product_lang.name As Producto, COALESCE (attribute_lang.name, '') AS Atributo, customer.firstname As Nombre, customer.lastname As Apellidos, customer.email As Correo
FROM ps_orders AS orders JOIN ps_order_detail AS order_detail ON (order_detail.id_order = orders.id_order)
    JOIN ps_customer AS customer ON (orders.id_customer = customer.id_customer)
    JOIN ps_product_lang AS product_lang ON (product_lang.id_product = order_detail.product_id)
    LEFT JOIN ps_attribute_lang AS attribute_lang ON (attribute_lang.id_attribute = order_detail.product_attribute_id)
    JOIN ps_category_product AS category_product ON (category_product.id_product = order_detail.product_id)
WHERE orders.date_add > '2016/01/01' AND product_lang.id_shop=1 AND product_lang.id_lang = 1  AND (attribute_lang.id_lang = 1 OR attribute_lang.id_lang IS NULL) AND category_product.id_category = 20
GROUP BY Correo

Conocer productos estacionales

Ideal para ofrecer accesorios y complementos a clientes que compraron un producto determinado. Para ello, será necesario obtener un listado de las compras desde una determinada fecha de un producto concreto (en el ejemplo: que contenga Loquesea), con nombres de productos, atributos y datos de clientes (nombre, apellidos y correo electrónico):

SELECT product_lang.name As Producto, COALESCE (attribute_lang.name, '') AS Atributo, customer.firstname As Nombre, customer.lastname As Apellidos, customer.email As Correo
FROM ps_orders AS orders JOIN ps_order_detail AS order_detail ON (order_detail.id_order = orders.id_order)
    JOIN ps_customer AS customer ON (orders.id_customer = customer.id_customer)
    JOIN ps_product_lang AS product_lang ON (product_lang.id_product = order_detail.product_id)
    LEFT JOIN ps_attribute_lang AS attribute_lang ON (attribute_lang.id_attribute = order_detail.product_attribute_id)
WHERE orders.date_add > '2016/01/01' AND product_lang.id_shop=1 AND product_lang.id_lang = 1  AND (attribute_lang.id_lang = 1 OR attribute_lang.id_lang IS NULL) AND product_lang.name LIKE '%Loquesea%'
GROUP BY Correo

Si por el contrario, necesitamos obtener un listado de las compras desde una determinada fecha de un producto concreto (en este caso por id numérico: 63), con nombres de productos, atributos y datos de clientes (nombre, apellidos y correo electrónico):

SELECT product_lang.name As Producto, COALESCE (attribute_lang.name, '') AS Atributo, customer.firstname As Nombre, customer.lastname As Apellidos, customer.email As Correo
FROM ps_orders AS orders JOIN ps_order_detail AS order_detail ON (order_detail.id_order = orders.id_order)
    JOIN ps_customer AS customer ON (orders.id_customer = customer.id_customer)
    JOIN ps_product_lang AS product_lang ON (product_lang.id_product = order_detail.product_id)
    LEFT JOIN ps_attribute_lang AS attribute_lang ON (attribute_lang.id_attribute = order_detail.product_attribute_id)
WHERE orders.date_add > '2016/01/01' AND product_lang.id_shop=1 AND product_lang.id_lang = 1  AND (attribute_lang.id_lang = 1 OR attribute_lang.id_lang IS NULL) AND product_lang.id_product = 63
GROUP BY Correo

Ofrecer nuevos descuentos a un cliente determinado

Si alguna vez ha utilizado un descuento para comprar:

Obtener un listado de las compras desde una determinada fecha con datos de clientes (nombre, apellidos y correo electrónico) que han comprado usando alguna vez algún descuento:

SELECT customer.firstname As Nombre, customer.lastname As Apellidos, customer.email As Correo
FROM ps_orders AS orders JOIN ps_customer AS customer ON (orders.id_customer = customer.id_customer)
WHERE orders.date_add > '2016/01/01' AND orders.total_discounts > 0
GROUP BY Correo

Si nunca han utilizado un descuento para comprar:

Obtener un listado de las compras desde una determinada fecha con datos de clientes (nombre, apellidos y correo electrónico) que nunca han comprado usando ningún descuento:

SELECT customer.firstname As Nombre, customer.lastname As Apellidos, customer.email As Correo
FROM ps_orders AS orders JOIN ps_customer AS customer ON (orders.id_customer = customer.id_customer)
WHERE orders.date_add > '2016/01/01' AND orders.total_discounts = 0 AND orders.id_order NOT IN (SELECT id_order FROM ps_orders WHERE total_discounts > 0)
GROUP BY Correo

Premiar a los clientes más fieles

Obtener un listado de las compras desde una determinada fecha con datos de clientes (nombre, apellidos y correo electrónico) y el número de veces que ha comprado:

SELECT customer.firstname As Nombre, customer.lastname As Apellidos, customer.email As Correo, COUNT(*) AS Compras
FROM ps_orders AS orders JOIN ps_customer AS customer ON (orders.id_customer = customer.id_customer)
WHERE orders.date_add > '2016/01/01'
GROUP BY Correo
ORDER BY Compras DESC

Conocer los productos más comprados

Obtener un listado de los datos de clientes (nombre, apellidos y correo electrónico) que han comprado un determinado producto (en el ejemplo: que contenga Loquesea), y las veces que lo han comprado:

SELECT product_lang.name As Producto, customer.firstname As Nombre, customer.lastname As Apellidos, customer.email As Correo, COUNT(*) AS Numero_Veces
FROM ps_orders AS orders JOIN ps_order_detail AS order_detail ON (order_detail.id_order = orders.id_order)
    JOIN ps_customer AS customer ON (orders.id_customer = customer.id_customer)
    JOIN ps_product_lang AS product_lang ON (product_lang.id_product = order_detail.product_id)
WHERE orders.date_add > '2016/01/01' AND product_lang.id_shop=1 AND product_lang.id_lang = 1 AND product_lang.name LIKE '%Loquesea%'
GROUP BY Correo
ORDER BY Numero_Veces DESC

Obtener un listado de los datos de clientes (nombre, apellidos y correo electrónico) que han comprado un determinado producto (en el ejemplo: que contenga Loquesea) más de X veces (en el ejemplo: 2):

SELECT product_lang.name As Producto, customer.firstname As Nombre, customer.lastname As Apellidos, customer.email As Correo, COUNT(*) AS Numero_Veces
FROM ps_orders AS orders JOIN ps_order_detail AS order_detail ON (order_detail.id_order = orders.id_order)
    JOIN ps_customer AS customer ON (orders.id_customer = customer.id_customer)
    JOIN ps_product_lang AS product_lang ON (product_lang.id_product = order_detail.product_id)
WHERE orders.date_add > '2016/01/01' AND product_lang.id_shop=1 AND product_lang.id_lang = 1 AND product_lang.name LIKE '%Loquesea%'
GROUP BY Correo
HAVING COUNT(*) > 2

Creando vistas SQL

Si usamos muy a menudo alguna de estas consultas, siempre puede venir bien crear una vista SQL, no teniendo que andar siempre copiando y pegando las sentencias SQL.
Por ejemplo:

CREATE VIEW Loquesea_mas_2_veces AS
SELECT product_lang.name As Producto, customer.firstname As Nombre, customer.lastname As Apellidos, customer.email As Correo, COUNT(*) AS Numero_Veces
FROM ps_orders AS orders JOIN ps_order_detail AS order_detail ON (order_detail.id_order = orders.id_order)
    JOIN ps_customer AS customer ON (orders.id_customer = customer.id_customer)
    JOIN ps_product_lang AS product_lang ON (product_lang.id_product = order_detail.product_id)
WHERE orders.date_add > '2016/01/01' AND product_lang.id_shop=1 AND product_lang.id_lang = 1 AND product_lang.name LIKE '%Loquesea%'
GROUP BY Correo
HAVING COUNT(*) > 2

¿Qué hacer con los datos? Utilízalos para tus campañas de email marketing

Una vez  tenemos los datos y la información que estábamos buscando, es hora de aprovecharlos para personalizas las comunicaciones con nuestros clientes. Para ello, podemos valernos de algunas de las herramientas de email marketing más utilizadas, que nos permiten utilizar los ficheross .csv obtenidos para el envío de estas comunicaciones de forma más automática.

Puedes encontrar cómo se hace en los siguientes enlaces:

• Correos personalizados con Outlook
• Correos personalizados con Thunderbird
• Correos personalizados con Mailrelay
• Correos personalizados con MainChimp

¿Qué te parece? ¿Aprovechas los datos de tu ecommerce para personalizar las comunicaciones con tus clientes?

Si tienes dudas y quieres que analicemos tu caso, ¡consúltanos!