CHALLENGE
DATA ANALYTICS

En este informe se presentan las métricas y análisis solicitados para el challenge sobre los tickets de compra de KWK correspondientes al periodo del 01/04 al 15/04. Se ha creado una base de datos de ejemplo que simula las condiciones y datos requeridos para este ejercicio.

A continuación, se abordarán las preguntas específicas relacionadas con las categorías de productos, el comportamiento de los clientes y las métricas de rentabilidad, utilizando SQL para obtener los datos.

¿Cuáles son 5 categorías a nivel 3 con más pedidos para el período de 01/04 al 15/04?

Sentencia SQL utilizada

SELECT Products_2022.categoria1_3, COUNT(*) AS total_pedidos
FROM kw_ticket_lines
JOIN Products_2022 ON kw_ticket_lines.producto = Products_2022.producto_id
WHERE kw_ticket_lines.fecha BETWEEN '2024-04-01' AND '2024-04-15'
GROUP BY Products_2022.categoria1_3
ORDER BY total_pedidos DESC
LIMIT 5;

2. ¿Cuál es el promedio de pedidos por cliente para dichas categorías?

Sentencia SQL utilizada (Promedio General)

SELECT Products_2022.categoria1_3,
COUNT(kw_ticket_headers.ticket) / COUNT(DISTINCT kw_ticket_headers.cliente) AS promedio_pedidos_cliente
FROM kw_ticket_headers
JOIN kw_ticket_lines ON kw_ticket_headers.ticket = kw_ticket_lines.ticket
JOIN Products_2022 ON kw_ticket_lines.producto = Products_2022.producto_id
WHERE kw_ticket_lines.fecha BETWEEN '2024-04-01' AND '2024-04-15'
AND Products_2022.categoria1_3 IN ('Pienso', 'Terrarios', 'Comida Húmeda', 'Sustratos', 'Cuerdas')
GROUP BY Products_2022.categoria1_3;

Sentencia SQL utilizada (Promedio por Cliente y Categoría)

SELECT Products_2022.categoria1_3,
kw_ticket_headers.cliente,
COUNT(kw_ticket_headers.ticket) / COUNT(DISTINCT kw_ticket_headers.cliente) AS promedio_pedidos_cliente
FROM kw_ticket_headers
JOIN kw_ticket_lines ON kw_ticket_headers.ticket = kw_ticket_lines.ticket
JOIN Products_2022 ON kw_ticket_lines.producto = Products_2022.producto_id
WHERE kw_ticket_lines.fecha BETWEEN '2024-04-01' AND '2024-04-15'
AND Products_2022.categoria1_3 IN ('Pienso', 'Terrarios', 'Comida Húmeda', 'Sustratos', 'Cuerdas')
GROUP BY Products_2022.categoria1_3, kw_ticket_headers.cliente
ORDER BY Products_2022.categoria1_3;

3. ¿Cuál es el ratio de pedidos recogidos en tienda vs domicilio para cada una de esas categorías?

Sentencia SQL utilizada

SELECT Products_2022.categoria1_3,
SUM(CASE WHEN kw_ticket_headers.tipo_de_entrega = 'tienda' THEN 1 ELSE 0 END) AS pedidos_tienda,
SUM(CASE WHEN kw_ticket_headers.tipo_de_entrega = 'domicilio' THEN 1 ELSE 0 END) AS pedidos_domicilio,
(SUM(CASE WHEN kw_ticket_headers.tipo_de_entrega = 'tienda' THEN 1 ELSE 0 END) /
SUM(CASE WHEN kw_ticket_headers.tipo_de_entrega = 'domicilio' THEN 1 ELSE 0 END)) AS ratio_tienda_vs_domicilio
FROM kw_ticket_headers
JOIN kw_ticket_lines ON kw_ticket_headers.ticket = kw_ticket_lines.ticket
JOIN Products_2022 ON kw_ticket_lines.producto = Products_2022.producto_id
WHERE kw_ticket_lines.fecha BETWEEN '2024-04-01' AND '2024-04-15'
AND Products_2022.categoria1_3 IN ('Pienso', 'Terrarios', 'Comida Húmeda', 'Sustratos', 'Cuerdas')
GROUP BY Products_2022.categoria1_3;

4. Dentro de domicilio: ¿cuál es el ratio de envío gratis vs envío pago?

Sentencia SQL utilizada Ratio general para las categorías seleccionadas

SELECT
SUM(CASE WHEN kw_ticket_shipping.importe_neto = 0 THEN 1 ELSE 0 END) AS total_envio_gratis,
SUM(CASE WHEN kw_ticket_shipping.importe_neto > 0 THEN 1 ELSE 0 END) AS total_envio_pago,
(SUM(CASE WHEN kw_ticket_shipping.importe_neto = 0 THEN 1 ELSE 0 END) /
SUM(CASE WHEN kw_ticket_shipping.importe_neto > 0 THEN 1 ELSE 0 END)) AS ratio_gratis_vs_pago
FROM kw_ticket_headers
JOIN kw_ticket_lines ON kw_ticket_headers.ticket = kw_ticket_lines.ticket
JOIN Products_2022 ON kw_ticket_lines.producto = Products_2022.producto_id
JOIN kw_ticket_shipping ON kw_ticket_headers.ticket = kw_ticket_shipping.ticket
WHERE kw_ticket_lines.fecha BETWEEN '2024-04-01' AND '2024-04-15'
AND kw_ticket_headers.tipo_de_entrega = 'Envio a Domicilio'
AND Products_2022.categoria1_3 IN ('Pienso', 'Terrarios', 'Comida Húmeda', 'Sustratos', 'Cuerdas');

Sentencia SQL utilizada para ratio en las categorías específicas

SELECT Products_2022.categoria1_3,
SUM(CASE WHEN kw_ticket_shipping.importe_neto = 0 THEN 1 ELSE 0 END) AS total_envio_gratis,
SUM(CASE WHEN kw_ticket_shipping.importe_neto > 0 THEN 1 ELSE 0 END) AS total_envio_pago,
(SUM(CASE WHEN kw_ticket_shipping.importe_neto = 0 THEN 1 ELSE 0 END) /
SUM(CASE WHEN kw_ticket_shipping.importe_neto > 0 THEN 1 ELSE 0 END)) AS ratio_gratis_vs_pago
FROM kw_ticket_headers
JOIN kw_ticket_lines ON kw_ticket_headers.ticket = kw_ticket_lines.ticket
JOIN Products_2022 ON kw_ticket_lines.producto = Products_2022.producto_id
JOIN kw_ticket_shipping ON kw_ticket_headers.ticket = kw_ticket_shipping.ticket
WHERE kw_ticket_lines.fecha BETWEEN '2024-04-01' AND '2024-04-15'
AND kw_ticket_headers.tipo_de_entrega = 'Envio a Domicilio'
AND Products_2022.categoria1_3 IN ('Pienso', 'Terrarios', 'Comida Húmeda', 'Sustratos', 'Cuerdas')
GROUP BY Products_2022.categoria1_3;

5. ¿Cuál de dichas categorías tiene la mayor rentabilidad en Euros/pedido promedio?

Sentencia SQL utilizada

SELECT Products_2022.categoria1_3,
SUM(kw_ticket_lines.importe_neto) / COUNT(DISTINCT kw_ticket_headers.ticket) AS rentabilidad_por_pedido_promedio
FROM kw_ticket_headers
JOIN kw_ticket_lines ON kw_ticket_headers.ticket = kw_ticket_lines.ticket
JOIN Products_2022 ON kw_ticket_lines.producto = Products_2022.producto_id
WHERE kw_ticket_lines.fecha BETWEEN '2024-04-01' AND '2024-04-15'
AND Products_2022.categoria1_3 IN ('Pienso', 'Terrarios', 'Comida Húmeda', 'Sustratos', 'Cuerdas')
GROUP BY Products_2022.categoria1_3
ORDER BY rentabilidad_por_pedido_promedio DESC

Sentencia SQL utilizada para la creación de las tablas para el challenge

Creación de la tabla kw_ticket_headers

CREATE TABLE kw_ticket_headers (
ticket INT PRIMARY KEY,
cliente VARCHAR(255),
fecha DATE,
devolucion BOOLEAN,
tipo_de_entrega BOOLEAN -- 0: Recogida en Tienda, 1: Envío a Domicilio
);

Creación de la tabla Products_2022

CREATE TABLE Products_2022 (
producto_id INT PRIMARY KEY,
categoria1_1 VARCHAR(255),
categoria1_2 VARCHAR(255),
categoria1_3 VARCHAR(255),
marca_propia_o_ext BOOLEAN -- 1: Marca Propia, 0: Resto
);

Creación de la tabla kw_ticket_shipping

CREATE TABLE kw_ticket_shipping (
ticket INT,
no_linea INT,
fecha DATE,
producto INT,
importe_neto DECIMAL(10,2),
importe_coniva DECIMAL(10,2),
PRIMARY KEY (ticket, no_linea),
FOREIGN KEY (ticket) REFERENCES kw_ticket_headers(ticket)
);

Creación de la tabla kw_ticket_cupones

CREATE TABLE kw_ticket_cupones (
Fecha_Pedido DATE,
No_Pedido INT,
Num_Linea INT,
Descripcion VARCHAR(255),
Importe_sinIVA DECIMAL(10,2),
Importe_ConIVA DECIMAL(10,2),
PRIMARY KEY (No_Pedido, Num_Linea),
FOREIGN KEY (No_Pedido) REFERENCES kw_ticket_headers(ticket)
);

Creación de la tabla kw_ticket_lines

CREATE TABLE kw_ticket_lines (
ticket INT,
no_linea INT,
fecha DATE,
producto INT,
unidades INT,
importe_neto DECIMAL(10,2),
importe_coniva DECIMAL(10,2),
importe_costo DECIMAL(10,2),
PRIMARY KEY (ticket, no_linea),
FOREIGN KEY (ticket) REFERENCES kw_ticket_headers(ticket)
);