馃Ы Limpieza de datos | E-Learning
En este proyecto, analizaremos los datos de una empresa ecommerce que se dedica a vender productos f铆sicos online. Responderemos a diferentes preguntas de negocio tanto financieras como de marketing.
Contexto
Comenzar a construir una infraestructura tecnol贸gica para analizar sus datos.
Limpiar los datos para que los puedan consumir las 谩reas de negocio.
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. Se trata del CMS m谩s popular del mercado, ya que lo utilizan el聽65,2%聽de los sitios web cuyo CMS conocemos. Esto se traduce en el聽42,4%聽de todos los sitios web, casi la mitad de Internet.
LearnData quiere comenzar a analizar sus principales m茅tricas financieras, pero no tiene ningun sistema creado para poder capturar, analizar y tomar mejores decisiones.
Tareas:
Crear la tabla de clientes a partir de los datos en crudo
Crear la tabla de pedidos a partir de los datos en crudo
Crear la tabla de cobros de stripe a partir de los datos en crudo
Herramientas a utilizar
Utilizaremos Lenguaje de SQL para poder crear la base de datos y limpiar los datos.
Desarrollo | Ejecuci贸n
# 1. Crear la nueva base de datos en MYSQL
CREATE SCHEMA learndata ;
# 2. Crear la tabla de clientes para almacenar la informaci贸n de nuestros clientes.
CREATE TABLE dim_clientes (
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(255),
PRIMARY KEY (id_cliente)
);
# 3. Crear la tabla productos para almacenar la informaci贸n sobre los curso que vendemos.
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)
);
# 4. Crear la tabla de pedidos donde se almacenar谩n todas nuestras ventas
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)
);
# 5. Crear la tabla de pagos de stripe qeu recibimos
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)
);
# 6 Insertamos los datos limpios y las columnas que queremos a nuestra nueva tabla.
INSERT INTO learndata_1.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;
# 7 Insertamos los datos limpios y las columnas que queremos a nuestra nueva tabla.
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;
# 8 Insertamos los datos limpios y las columnas que queremos a nuestra nueva tabla.
INSERT INTO learndata_1.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,
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 p on p.nombre_producto = w.nombre_del_articulo;
# Trato de duplicados -- detectamos que no nos deja insertar la informaci贸n porque encontramos un duplicado --
SELECT * FROM learndata_crudo.raw_pedidos_wocommerce
WHERE numero_de_pedido = 41624;
----- Debemos 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 volves a ejecutar la query --
# 9 Insertamos los datos limpios y las columnas que queremos a nuestra nueva tabla.
INSERT INTO learndata_1.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 les da un error de fechas ( Errror Code 1292 : Truncated incorrect datetime value) Ejecuten lo siguiente:
SET @@SESSION.sql_mode='ALLOW_INVALID_DATES';
Conclusiones
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. Tambi茅n es necesario determinar con el equipo de ingenieria de datos la realizaci贸n de los pipelines de datos y automatizaci贸n de la descarga, limpieza de los mismos.