🧽 Limpieza de datos | E-Learning 🧽

En este proyecto, analizaremos los datos de una empresa de e-learning dedicada a la venta de cursos online de análisis de datos. Limpiaremos los datos para que los puedan consumir las áreas de negocio.

Contexto

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 además de los pagos de tarjeta de crédito. Tenemos datos de los productos, es decir, de los cursos que se venden, también de los clientes, de los pedidos y de los pagos recibidos por stripe.

Los principales objetivos de la empresa son comenzar a construir una infraestructura tecnológica para analizar sus datos y limpiar los datos para que los puedan consumir las áreas de negocio.

Este proyecto intentará responder a la siguiente consigna:

1. Crear una nueva base de datos en MYSQL llamada “learndata” + tablas: 1. dim_clientes; dim_producto;fac_pedidos; fac_pagos_stripe

2. Crear la tabla de productos a partir de los datos en crudo. 1. Chequear como vienen los datos 2. Cambiar los nombres de los campos 3. Insertar los campos a la nueva tabla

3. Crear la tabla de clientes a partir de los datos en crudo 1. Chequear como vienen los datos 2. Cambiar los nombres de los campos 3. Convertir el campo date_created que viene como timestamp a solo fecha 4. Extraer del campo billing, todos los descriptivos del cliente que necesitamos aprendiendo a parsear un JSON. 5. Insertar los campos a la nueva tabla

4. Crear la tabla de pedidos a partir de los datos en crudo 1. Chequear como vienen los datos 2. Cambiar los nombres de los campos 3. Sustituir el nombre del producto por el id. 4. Normalizar la columna método de pago. 5. Convertir a date la columna fecha_pedido 6. Redondear decimales de la columna coste_articulo a enteros 7. Insertamos los pedidos a la tabla

5. Crear la tabla de cobros de stripe a partir de los datos en crudo 1. Chequear como vienen los datos 2. Cambiar los nombres de los campos 3. 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 4. Pasar a timestamp el campo “created” 5. Reemplazar las commas por puntos 6. Convertir el número a decimal con dos lugares después de la comma. 7. Insertar tabla en nueva

Herramientas a utilizar

Utilizaremos Lenguaje de SQL para poder crear la base de datos y limpiar los datos.

Conjunto de datos del proyecto

Los datos crudos los tendremos en csv directamente descargados de las fuentes. La tabla de pedidos se relaciona con la de clientes y productos mediante SKU_producto e id_cliente, por otro lado la tabla de pagos de stripe se relaciona con la de pedidos por el número de pedido.

Desarrollo | Ejecución

-- 1. Crear una nueva base de datos en MYSQL llamada “learndata” + tablas: -- 1. dim_clientes; dim_producto; fac_pedidos; fac_pagos_stripe CREATE SCHEMA learndata; -- 2. Crear la tabla de productos a partir de los datos en crudo. -- 1. Chequear como vienen los datos -- 2. Cambiar los nombres de los campos -- 3. Insertar los campos a la nueva tabla CREATE TABLE dim_producto ( id_producto INT, sku_producto INT, nombre_producto VARCHAR(100), publicado_producto BOOLEAN, inventario_producto VARCHAR(100), precio_normal_producto INT, categoria_producto VARCHAR(100), PRIMARY KEY (sku_producto) ); INSERT INTO learndata.dim_producto 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 categoria_producto FROM learndata_crudo.raw_productos_wocommerce; -- 3. Crear la tabla de clientes a partir de los datos en crudo -- 1. Chequear como vienen los datos -- 2. Cambiar los nombres de los campos -- 3. Convertir el campo date_created que viene como timestamp a solo fecha -- 4. Extraer del campo billing, todos los descriptivos del cliente que necesitamos aprendiendo a parsear un JSON. -- 5. Insertar los campos a la nueva tabla CREATE TABLE dim_clientes ( id_cliente INT, 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(255), fecha_creacion_cliente DATE, PRIMARY KEY (id_cliente) ); INSERT INTO learndata.dim_clientes SELECT id as id_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, DATE(STR_TO_DATE(date_created,"%d/%m/%Y %H:%i:%s")) as fecha_creacion_cliente FROM learndata_crudo.raw_clientes_wocommerce; -- 4. Crear la tabla de pedidos a partir de los datos en crudo -- 1. Chequear como vienen los datos -- 2. Cambiar los nombres de los campos -- 3. Sustituir el nombre del producto por el id. -- 4. Normalizar la columna método de pago. -- 5. Convertir a date la columna fecha_pedido -- 6. Redondear decimales de la columna coste_articulo a enteros -- 7. Insertamos los pedidos a la tabla CREATE TABLE fac_pedidos ( 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 (id_cliente), FOREIGN KEY (sku_producto) REFERENCES dim_producto (sku_producto) ); INSERT INTO learndata.fac_pedidos 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, importe_de_descuento_del_carrito AS importe_de_descuento_pedido, importe_total_pedido AS importe_total_pedido, cantidad AS cantidad_pedido, coste_articulo AS costo_pedido, cupon_articulo AS codigo_cupon_pedido FROM learndata_crudo.raw_pedidos_wocommerce w LEFT JOIN learndata.dim_producto p on p.nombre_producto = w.nombre_del_articulo; # Trato de duplicados: Se detecta que no deja insertar la información porque hay un duplicado -- SELECT * FROM learndata_crudo.raw_pedidos_wocommerce WHERE numero_de_pedido = 41624; # Primero analizar si podemos detectar porque viene y si no se elimina, reportamos al equipo que corresponda -- DELETE FROM learndata_crudo.raw_pedidos_wocommerce WHERE numero_de_pedido = 41624 and `id cliente` = 1324; # Una vez eliminado se vuelve a ejecutar la query -- -- 5. Crear la tabla de cobros de stripe a partir de los datos en crudo -- 1. Chequear como vienen los datos -- 2. Cambiar los nombres de los campos -- 3. 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 -- 4. Pasar a timestamp el campo “created” -- 5. Reemplazar las commas por puntos -- 6. Convertir el número a decimal con dos lugares despues de la comma. -- 7. Insertar tabla en nueva CREATE TABLE fac_pagos_stripe ( 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 (id_pedido) ); INSERT INTO learndata.fac_pagos_stripe 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; # Si da error de fechas ( Error Code 1292 : Truncated incorrect datetime value) Ejecutar lo siguiente: SET @@SESSION.sql_mode='ALLOW_INVALID_DATES'; # Una vez ejecutado lo anterior se vuelve a ejecutar la query --

Conclusiones

Se crea una nueva base de datos en MYSQL con todos los datos limpios y listos para consumir a partir de sus datos históricos que nos descargaron en un CSV. Una vez finalizado el proceso de creación de una nueva base de datos & limpieza de datos las tablas están listas para ser consumidas por los expertos en visualización de datos.