🧽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.

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';

Run to view results

# 1. ¿Cual es el total de ventas de la empresa? SELECT SUM(importe_total_pedido) as ventas FROM learndata_1.fac_pedidos_1;

Run to view results

# 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)

Run to view results

#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

Run to view results

#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

Run to view results

#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

Run to view results

#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

Run to view results

#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

Run to view results

#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

Run to view results

#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

Run to view results

#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

Run to view results

#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;

Run to view results

#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;

Run to view results

#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

Run to view results

#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;

Run to view results

#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;

Run to view results

#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;

Run to view results

#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)

Run to view results

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.