🧽E-Learning - Limpieza de datos
Breve descripción del proyecto: En este proyecto, analizaremos los datos de LearnData que es una empresa de e-learning dedicada a la venta de cursos online de análisis de datos. Su principal objetivo es:
Comenzar a construir una infraestructura tecnológica para analizar sus datos.
Limpiar los datos para que los puedan consumir las áreas de negocio.
Contexto
LearnData es una empresa de e-learning dedicada a la venta de cursos online de análisis de datos. Su principal objetivo es:
Comenzar a construir una infraestructura tecnológica para analizar sus datos.
Limpiar los datos para que los puedan consumir las áreas de negocio.
Herramientas a utilizar
El lenguaje de programación utilizada para realizar el proyecto fue MySQL.
Conjunto de datos del proyecto
LearnData utiliza las siguientes herramientas para gestionar su negocio:
Wocommerce: Es un plugin de wordpress que te permite convertir tu web a un sitio de ecommerce y vender productos.
Stripe: Es una plataforma de procesamiento de pagos por internet, al igual que paypal.
Wordpress: Es un sistema de gestión de contenidos(CMS), un software utilizado para construir, modificar y mantener sitios web.
Loading...
Desarrollo | Ejecución
Análisis previo del problema
1. ¿Que fuentes de datos tiene la empresa?
La empresa utiliza wordpress con un plugin de wocommerce como plataforma de venta de sus cursos online y luego cuenta con stripe como pasarela de pagos a de más de los pagos de tarjeta de crédito.
2. ¿En que formato se descargan los datos?
Los datos crudos los tendremos en csv directamente descargados de las fuentes.
3. ¿Que datos tenemos?
Tenemos datos de los productos osea cursos que se venden, los clientes, de los pedidos y de los pagos recibidos por stripe.
4. Modelo de datos
Tenemos la tabla de pedidos que se relaciona con la de clientes y productos mediante SKU_producto e id_cliente y por otro lado tenemos la tabla la de pagos de stripe que la relacionaremos con la de pedidos por el numero de pedido.
5. Análisis exploratorio de las tablas.
Pasos para la ejecución del proyecto
1. Crear una nueva base de datos en MYSQL llamada “learndata” + tablas: dim_clientes; dim_producto;fac_pedidos; fac_pagos_stripe
2. Crear la tabla de productos a partir de los datos en crudo: Chequear como vienen los datos, cambiar los nombres de los campos , insertar los campos a la nueva tabla.
3. Crear la tabla de clientes a partir de los datos en crudo: Chequear como vienen los datos, cambiar los nombres de los campos, convertir el campo date_created que viene como timestamp a solo fecha, extraer del campo billing, todos los descriptivos del cliente que necesitamos aprendiendo a parsear un JSON, insertar los campos a la nueva tabla.
4. Crear la tabla de pedidos a partir de los datos en crudo: Chequear como vienen los datos, cambiar los nombres de los campos, sustituir el nombre del producto por el id, normalizar la columna método de pago, convertir a date la columna fecha_pedido, redondear decimales de la columna coste_articulo a enteros, insertamos los pedidos a la tabla.
5. Crear la tabla de cobros de stripe a partir de los datos en crudo: Chequear como vienen los datos, cambiar los nombres de los campos, obtener el número de pedido con la función RIGHT, quitar el numero de pedido de la descripción que es lo que nos va a permitir unir esta tabla con otras, pasar a timestamp el campo “created”, reemplazar las commas por puntos, convertir el número a decimal con dos lugares despues de la comma, insertar tabla en nueva.
# 1. Crear la nueva base de datos en MYSQL.
CREATE SCHEMA learndata_1;
# 2. Crear la tabla de productos a partir de los datos en crudo.
CREATE TABLE dim_clientes_1 (
id_cliente int,
fecha_creacion_cliente DATE,
nombre_cliente varchar (100),
apellido_cliente varchar (100),
email_cliente varchar (100),
telefono_cliente varchar (100),
region_cliente varchar (100),
pais_cliente varchar (100),
codigo_postal_cliente varchar (100),
direccion_cliente varchar (225),
PRIMARY KEY (id_cliente)
);
# 3. Crear la tabla productos para almacenar la información sobre los curso que vendemos.
CREATE TABLE dim_producto_1 (
id_producto int,
sku_producto int,
nombre_producto varchar (200),
publicado_producto BOOLEAN,
inventario_producto varchar (100),
precio_normal_producto int,
categoria_producto varchar (100),
PRIMARY KEY (sku_producto)
);
# 4. Crear la tabla de pedidos donde se almacenarán todas nuestras ventas
CREATE TABLE fac_pedidos_1 (
id_pedido int,
sku_producto int,
estado_pedido varchar (50),
fecha_pedido DATE,
id_cliente int,
tipo_pago_pedido varchar (50),
costo_pedido int,
importe_de_descuento_pedido decimal (10,0),
importe_total_pedido int,
cantidad_pedido int,
codigo_cupon_pedido varchar (100),
PRIMARY KEY (id_pedido),
FOREIGN KEY (id_cliente) REFERENCES dim_clientes_1 (id_cliente),
FOREIGN KEY (sku_producto) REFERENCES dim_producto_1 (sku_producto)
);
# 5. Crear la tabla de pagos de stripe que recibimos
CREATE TABLE fac_pagos_stripe_1 (
id_pago varchar (50),
fecha_pago datetime(6),
id_pedido int,
importe_pago int,
moneda_pago varchar (5),
comision_pago decimal (10,2),
neto_pago decimal (10,2),
tipo_pago varchar (50),
PRIMARY KEY (id_pago),
FOREIGN KEY (id_pedido) REFERENCES fac_pedidos_1 (id_pedido)
);
# 6 Insertamos los datos limpios y las columnas que queremos a nuestra nueva tabla.
INSERT INTO learndata_1.dim_producto_1
SELECT
id as id_producto,
sku as sku_producto,
nombre as nombre_producto,
publicado as publicado_producto,
inventario as inventario_producto,
precio_normal as precio_normal_producto,
categorias as categorias_producto
FROM learndata_crudo.raw_productos_wocommerce;
# 7 Insertamos los datos limpios y las columnas que queremos a nuestra nueva tabla.
SELECT
id as id_cliente,
date_created as fecha_creacion_cliente,
JSON_VALUE(billing,'$[0].first_name') as nombre_cliente,
JSON_VALUE(billing,'$[0].last_name') as apellido_cliente,
JSON_VALUE(billing,'$[0].email') as email_cliente,
JSON_VALUE(billing,'$[0].phone') as telefono_cliente,
JSON_VALUE(billing,'$[0].Region') as region_cliente,
JSON_VALUE(billing,'$[0].country') as pais_cliente,
JSON_VALUE(billing,'$[0].postcode') as codigo_postal_cliente,
JSON_VALUE(billing,'$[0].address_1') as direccion_cliente
FROM learndata_crudo.raw_clientes_wocommerce;
# 8 Insertamos los datos limpios y las columnas que queremos a nuestra nueva tabla.
INSERT INTO learndata_1.dim_clientes_1
SELECT
id as id_cliente,
DATE(str_to_date(date_created,"%d/%m/%Y %H:%i:%s")) as fecha_creacion_cliente,
JSON_VALUE(billing,'$[0].first_name') as nombre_cliente,
JSON_VALUE(billing,'$[0].last_name') as apellido_cliente,
JSON_VALUE(billing,'$[0].email') as email_cliente,
JSON_VALUE(billing,'$[0].phone') as telefono_cliente,
JSON_VALUE(billing,'$[0].Region') as region_cliente,
JSON_VALUE(billing,'$[0].country') as pais_cliente,
JSON_VALUE(billing,'$[0].postcode') as codigo_postal_cliente,
JSON_VALUE(billing,'$[0].address_1') as direccion_cliente
FROM learndata_crudo.raw_clientes_wocommerce;
# 9 Insertamos los datos limpios y las columnas que queremos a nuestra nueva tabla.
# Verificamos que valor esta mal en los datos en la tabla pedidos
SELECT DISTINCT w.nombre_del_articulo
FROM learndata_crudo.raw_pedidos_wocommerce w
LEFT JOIN learndata_1.dim_producto_1 p ON p.nombre_producto = w.nombre_del_articulo
WHERE p.nombre_producto IS NULL;
# Revisamos que valores del SKU son diferentes en la tabla pedidos
SELECT DISTINCT SKU
FROM learndata_crudo.raw_pedidos_wocommerce w
LEFT JOIN learndata_1.dim_producto_1 p ON p.nombre_producto = w.nombre_del_articulo
WHERE p.nombre_producto IS NULL;
INSERT INTO learndata_1.fac_pedidos_1
SELECT
numero_de_pedido as id_pedido,
CASE WHEN p.sku_producto IS NULL THEN 3 ELSE p.SKU_producto END AS SKU_producto,
estado_de_pedido as estado_pedido,
DATE(fecha_de_pedido) as fecha_pedido,
`id cliente` as id_cliente,
CASE WHEN titulo_metodo_de_pago LIKE '%Stripe%' THEN 'Stripe' ELSE 'Tarjeta' END AS metodo_pago_pedido,
coste_articulo as costo_pedido,
importe_de_descuento_del_carrito as importe_de_descuento_pedido,
importe_total_pedido as importe_total_pedido,
cantidad as cantidad_pedido,
cupon_articulo as codigo_cupon_pedido
FROM learndata_crudo.raw_pedidos_wocommerce W
LEFT JOIN learndata_1.dim_producto_1 p ON p.nombre_producto = w.nombre_del_articulo;
# 9 Insertamos los datos limpios y las columnas que queremos a nuestra nueva tabla.
INSERT INTO learndata_1.fac_pagos_stripe_1
SELECT
id as id_pago,
TIMESTAMP(created) AS fecha_pago,
RIGHT(description,5) as id_pedido,
amount as importe_pago,
currency as moneda_pago,
CAST(REPLACE(fee,',','.')AS DECIMAL(10,2)) as comision_pago,
CAST(REPLACE(net,',','.') AS DECIMAL(10,2)) as neto_pago,
type as tipo_pago
FROM learndata_crudo.raw_pagos_stripe;
SET @@SESSION.sql_mode='ALLOW_INVALID_DATES';
# 1. ¿Cual es el total de ventas de la empresa?
SELECT SUM(importe_total_pedido) as ventas
FROM learndata_1.fac_pedidos_1;
Loading...
# 2. ¿Cual es el total de las ventas por año?
SELECT year(fecha_pedido) as anio,
SUM(importe_total_pedido) as ventas
FROM learndata_1.fac_pedidos_1
GROUP BY year(fecha_pedido)
Loading...
#3. ¿Cuál es el total de ventas por producto?
SELECT nombre_producto,
sum(importe_total_pedido) as ventas
FROM learndata_1.fac_pedidos_1 p
LEFT JOIN dim_producto_1 pr on pr.SKU_producto = p.SKU_producto
GROUP BY nombre_producto
ORDER BY sum(importe_total_pedido) DESC
Loading...
#4. ¿Cuál es el total de ventas por producto pero por cantidad?
SELECT nombre_producto,
sum(importe_total_pedido) as ventas,
sum(cantidad_pedido) as cantidad
FROM learndata_1.fac_pedidos_1 p
LEFT JOIN dim_producto_1 pr on pr.SKU_producto = p.SKU_producto
GROUP BY nombre_producto
ORDER BY sum(importe_total_pedido) DESC
Loading...
#5. ¿A que precio se ha vendido cada producto? ¿Podrias sacar el valor único?
SELECT
DISTINCT nombre_producto,
costo_pedido
FROM learndata_1.fac_pedidos_1 p
LEFT JOIN learndata_1.dim_producto_1 pr on pr.SKU_producto = p.SKU_producto
Loading...
#6. ¿A que podríamos atribuir ese crecimiento de ventas? ¿Podriamos ver las ventas por producto y por año?
SELECT nombre_producto,
year(fecha_pedido) as anio,
sum(importe_total_pedido) as ventas,
sum(cantidad_pedido) as cantidad
FROM learndata_1.fac_pedidos_1 p
LEFT JOIN dim_producto_1 pr on pr.SKU_producto = p.SKU_producto
GROUP BY year(fecha_pedido),nombre_producto
ORDER BY nombre_producto,year(fecha_pedido) ASC
Loading...
#7. ¿Cuáles son las ventas por meses del año 2021? Ordena las ventas de mayor a menor.
SELECT month(fecha_pedido) as mes,
sum(importe_total_pedido) as ventas
FROM learndata_1.fac_pedidos_1
WHERE year(fecha_pedido)=2021
GROUP BY month(fecha_pedido)
ORDER BY sum(importe_total_pedido) desc
Loading...
#8. ¿Cuales son los top 3 clientes que compran en términos monetarios? Necesitamos que se traiga el nombre completo con apellido en un solo campo.
SELECT
CONCAT(nombre_cliente," ",apellido_cliente) as nombre_completo,
sum(importe_total_pedido) as ventas,
format(sum(importe_total_pedido), '##,###,00') as ventas_2
FROM learndata_1.fac_pedidos_1 as pe
LEFT JOIN learndata_1.dim_clientes_1 c on c.id_cliente = pe.id_cliente
GROUP BY nombre_completo
ORDER BY ventas desc
LIMIT 3
Loading...
#9. ¿Cúales son los top 3 clientes que compran por cantidad? Necesitamos que se traigan el nombre completo con apellido
SELECT
CONCAT(nombre_cliente," ",apellido_cliente) as nombre_completo,
sum(cantidad_pedido) as cantidad
FROM learndata_1.fac_pedidos_1 pe
LEFT JOIN learndata_1.dim_clientes_1 c on c.id_cliente = pe.id_cliente
GROUP BY nombre_completo
ORDER BY cantidad desc
LIMIT 3
Loading...
#10. ¿Cúal es el método de pago con el cual más pagan los clientes ( términos monetarios)?
SELECT
tipo_pago_pedido,
sum(importe_total_pedido) as ventas
FROM learndata_1.fac_pedidos_1
GROUP BY tipo_pago_pedido
Loading...
#11. ¿Cuanto es el importe total en términos monetarios utilizado en cupones?
SELECT sum(importe_de_descuento_pedido) as importe_cupones
FROM learndata_1.fac_pedidos_1;
Loading...
#12. ¿Cúal es el total de cupones utilizados en las ventas en terminos cuantitativos? Comparalo con todas las ventas y calcula el porcentaje en términos cuantitativos.
WITH cupones as (
SELECT id_pedido,
codigo_cupon_pedido,
CASE WHEN codigo_cupon_pedido='' then 0 else 1 end as cupones
FROM learndata_1.fac_pedidos_1)
SELECT sum(cupones) as total_cupones,
count(DISTINCT p.id_pedido) as pedidos,
(sum(cupones)/count(DISTINCT p.id_pedido)*100) as porcentaje
FROM learndata_1.fac_pedidos_1 p
LEFT JOIN cupones c on c.id_pedido=p.id_pedido;
Loading...
#13. El mismo cálculo pero desglosado por año.
WITH cupones as (
SELECT id_pedido,
codigo_cupon_pedido,
CASE WHEN codigo_cupon_pedido='' then 0 else 1 end as cupones
FROM learndata_1.fac_pedidos_1)
SELECT
year(fecha_pedido) as anio,
sum(cupones) as total_cupones,
count(DISTINCT p.id_pedido) as pedidos,
(sum(cupones)/count(DISTINCT p.id_pedido)*100) as porcentaje
FROM learndata_1.fac_pedidos_1 p
LEFT JOIN cupones c on c.id_pedido=p.id_pedido
GROUP BY anio
Loading...
#14. ¿Cúal es el total de comisiones pagadas a stripe?
SELECT ABS(sum(comision_pago)) as total_comisiones
FROM learndata_1.fac_pagos_stripe_1;
Loading...
#15. ¿Cúal es el porcentaje de comision de cada pedido realizado por Stripe?
SELECT tipo_pago_pedido,
id_pago,
comision_pago,
comision_pago/importe_pago as porcentaje
FROM learndata_1.fac_pedidos_1 p
INNER JOIN learndata_1.fac_pagos_stripe_1 s on s.id_pedido=p.id_pedido;
Loading...
#16. Del ejericio anterior Cúal es la media de porcentaje total redondeado a dos dígitos los decimales.
SELECT
ROUND(AVG(comision_pago/importe_pago),3) as porcentaje,
FORMAT(AVG(comision_pago*100/importe_pago),2) as porcentaje_total
FROM learndata_1.fac_pedidos_1 p
INNER JOIN learndata_1.fac_pagos_stripe_1 s on s.id_pedido=p.id_pedido;
Loading...
#17. Calcula el total de ventas, las ventas sin comision DE STRIPE y las comisiones DE STRIPE por año
SELECT
year(fecha_pedido) as year,
sum(importe_total_pedido) as total_ventas ,
IFNULL(sum(comision_pago),0) as comisiones,
sum(importe_total_pedido) + IFNULL(sum(comision_pago),0) as ventas_netas
FROM learndata_1.fac_pedidos_1 p
LEFT JOIN learndata_1.fac_pagos_stripe_1 s on s.id_pedido=p.id_pedido
GROUP BY year(fecha_pedido)
Loading...
Conclusiones
Resumen de los resultados: En este proyecto de limpieza de datos para LearnData, se logró crear una infraestructura tecnológica básica y se realizaron tareas fundamentales de limpieza y transformación de datos. Los datos crudos de las fuentes WooCommerce y Stripe se convirtieron en tablas limpias y estructuradas que son más útiles para su análisis. Esto sienta las bases para futuros análisis y toma de decisiones basadas en datos en LearnData.
Cumplimiento de los objetivos: Se lograron los objetivos principales del proyecto, que incluyen la creación de tablas limpias y estructuradas para clientes, productos, pedidos y pagos de Stripe. Esto permitirá a las áreas de negocio de LearnData consumir y analizar estos datos de manera efectiva.
Impacto y relevancia: Los resultados de este proyecto tienen un impacto significativo en el negocio de LearnData al permitir un análisis de datos más eficiente y efectivo. La limpieza y estructuración de datos son pasos críticos para obtener información valiosa de los datos brutos.
Limitaciones y áreas de mejora: Se reconoce que este proyecto se centró en la limpieza y estructuración básica de datos, y hay áreas adicionales que podrían mejorarse. Por ejemplo, podría explorarse la adición de datos de información salarial, duración de los cursos y retroalimentación de los clientes para un análisis más completo. Además, la calidad de los datos crudos puede mejorar desde las fuentes.
Recomendaciones: Se recomienda a LearnData que continúe mejorando la calidad de sus datos en las fuentes, ya que la calidad de los datos crudos es esencial para cualquier proyecto de análisis de datos. Además, se sugiere que exploren análisis más avanzados utilizando estas tablas limpias para obtener información valiosa sobre su negocio.
Reflexión sobre el proceso: El proceso de limpieza y estructuración de datos puede ser desafiante, pero es un paso fundamental en cualquier proyecto de análisis de datos. Se destacan las lecciones aprendidas sobre la importancia de la consistencia de los datos y la necesidad de comprender profundamente las fuentes de datos antes de realizar la limpieza.
Futuras investigaciones: En futuros proyectos, LearnData podría explorar análisis más avanzados, como la predicción de la demanda de cursos o la identificación de patrones de comportamiento de los clientes. También podrían considerar la implementación de un sistema de seguimiento de datos en tiempo real para tomar decisiones más ágiles basadas en datos.