E-Learning | 🧽 Data Cleaning & Transformation

Business Problem

In this project, We will analyze the data of "LearnData", a company in the e-learning sector that specializes in selling online data analysis courses. Their primary objectives are:

To initiate the development of a tech-infrastructure for data analysis.

To clean and process the data so that it can be easily utilized by the various business areas within the company.

Data-Set | Diagram

"LearnData" uses the following tools to manage its business:

WooCommerce: This is a WordPress plugin that enables you to transform your website into an e-commerce platform and sell products.

Stripe: It's an online payment processing platform, similar to PayPal, that facilitates online transactions.

WordPress: This is a content management system (CMS), which is software used to build, modify, and maintain websites.

The data is originally in CSV format, and it is imported into MySQL to create the database named "learndata_crudo."

Analysis:

Preliminary Analysis:

Data Sources: The company utilizes WordPress with a WooCommerce plugin as the platform for selling its online courses. Additionally, they use Stripe as a payment gateway, handling credit card payments.

Data Format: The raw data is downloaded in CSV format directly from the sources.

Types of Data: The available data includes information about products (courses), customers, orders, and payments received through Stripe.

Data Model: The data is structured into several tables, including an Orders table linked to the Customers and Products tables through SKU_product and id_cliente. Another table is for Stripe payments, which is related to the Orders table using the order number.

Steps for executing the project:

1. Creating a new database named "learndata" along with the tables: "dim_clientes", "dim_producto", "fac_pedidos", and "fac_pagos_stripe".

## 1-- We create a new database where we will insert the clean tables CREATE SCHEMA learndata; ## 2--We create new tables that include ONLY the fields we will use, which provide us with relevant information. 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) ); CREATE TABLE dim_productos ( 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) ); CREATE TABLE fac_pedidos ( id_pedido INT, sku_producto INT, estado_pedido VARCHAR(100), fecha_pedido DATE, id_cliente INT, tipo_pago_pedido VARCHAR(100), costo_pedido INT, importe_descuento_pedido INT, 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_productos (sku_producto) ); CREATE TABLE fac_pagos ( id_pago VARCHAR(100), fecha_pago DATETIME(6), id_pedido INT, importe_pago INT, moneda_pago VARCHAR(5), fee_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) );

2. Create the Products table from raw data: Check the data format, change field names if necessary, and insert the fields into the new table.

## 3--We clean the products table: In this case, we only change the names and exclude the fields that do not provide information -- We insert the clean data to our new table INSERT INTO learndata.dim_productos 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 raw_productos_wocommerce;

3. Create the Customers table from raw data: Check the data format, change columns names, convert the "date_created" field from string to date format, extract necessary customer dimensions from the billing field (parse JSON), and insert the fields into the new table.

## 4--We clean the clients table: -- We insert the clean data to our new table INSERT INTO learndata.dim_clientes 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 raw_clientes_wocommerce;

4. Create the Orders table from raw data: Check the data format, change columns names, replace product names with product IDs, normalize the payment method column, convert the "date_pedido" column to a date format, round decimals in the cost_articulo column to integers, and insert the orders into the table.

-- We analyze what is happening with the sku SELECT DISTINCT sku FROM learndata_crudo.raw_pedidos_wocommerce; -- We see that there are outliers that do not follow a pattern, but since we have the product name -- in the description, we can match these outliers with the correct SKU using the description

SELECT * FROM learndata_crudo.raw_pedidos_wocommerce p LEFT JOIN dim_productos pr ON p.nombre_del_articulo = pr.nombre_producto; -- We find that the products table returns several null values. After filtering, we see that these correspond to the product -- Power BI: Como crear dashborads inteligentes (SKU = 3) SELECT * FROM learndata_crudo.raw_pedidos_wocommerce p LEFT JOIN dim_productos pr ON p.nombre_del_articulo = pr.nombre_producto WHERE nombre_producto IS NULL; -- We compare the name in both the products and orders tables and find an error in the word "dashboard" in the orders table #Power BI: Como crear dashborads inteligentes #Power BI: Como crear dashboards inteligentes

-- Regarding the payment method field, we have several options, but to simplify, we will classify all of them as either "card" or "Stripe SELECT DISTINCT titulo_metodo_de_pago FROM learndata_crudo.raw_pedidos_wocommerce;

-- When trying to insert the values, an error occurs indicating that an order number is duplicated SELECT * FROM raw_pedidos_wocommerce WHERE numero_de_pedido = 41624;

-- Temporarily, and to continue working, we delete one of these duplicate records.: DELETE FROM raw_pedidos_wocommerce WHERE numero_de_pedido = 41624 AND `id cliente` = 1324;

## 5.--We clean the orders table: -- We insert the data into our new table INSERT INTO learndata.fac_pedidos SELECT numero_de_pedido as id_pedido, CASE WHEN pr.sku_producto IS NULL THEN 3 ELSE pr.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 tipo_pago_pedido, coste_articulo as costo_pedido, importe_de_descuento_del_carrito as importe_descuento_pedido, importe_total_pedido as importe_total_pedido, articulo_numero as cantidad_pedido, cupon_articulo as codigo_cupon_pedido FROM learndata_crudo.raw_pedidos_wocommerce p LEFT JOIN dim_productos pr ON p.nombre_del_articulo = pr.nombre_producto;

5. Create the Stripe Payments table from raw data: Check the data format, change columns names, obtain the order number using the RIGHT function, remove the order number from the description (which will be used for table joins), convert the "created" field to a timestamp, replace commas with periods, convert numbers to decimals with two decimal places, and insert the table into the new database.

## 6.--We clean the payments table : -- We insert the data into our new table INSERT INTO learndata.fac_pagos 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 fee_pago, CAST(REPLACE(net,",",".") AS DECIMAL (10,2)) as neto_pago, type as tipo_pago FROM learndata_crudo.raw_pagos_stripe; -- If we encounter a date error (ERROR CODE 1292: incorrect datetime value: '2021-07-06T08:11:58Z'), we execute the following step: ##SET @@SESSION.sql_mode='ALLOW INVALID DATES';

Conclusions

Summary of Results: In this data cleansing project for LearnData, we successfully established a basic technological infrastructure and performed essential data cleaning and transformation tasks. Raw data from WooCommerce and Stripe sources were converted into clean and structured tables that are more suitable for analysis. This lays the foundation for future data-driven analysis and decision-making at LearnData.

Achievement of Objectives: The primary objectives of the project, including creating clean and structured tables for customers, products, orders, and Stripe payments, were met. This will enable LearnData's business areas to consume and analyze this data effectively.

Impact and Relevance: The results of this project have a significant impact on LearnData's business by enabling more efficient and effective data analysis. Data cleaning and structuring are critical steps in extracting valuable insights from raw data.

Recommendations: LearnData is advised to continue improving the quality of their data sources, as data quality is essential for any data analysis project. Furthermore, they should explore more advanced analytics using these clean tables to gain valuable insights into their business.

Future Research: In future projects, LearnData could explore more advanced analytics, such as predicting course demand or identifying customer behavior patterns. They might also consider implementing a real-time data tracking system for more agile data-driven decision-making.