Este proyecto implica el entendimiento de una Base de Datos relativa a las compras de una Tienda Online y la utilización de esta base de datos para llevar a cabo las siguientes actividades de Transformación de Datos:

·         Dividir el nombre completo en nombre y apellido.

·         Corregir números de teléfono y correos electrónicos que no estén en un formato adecuado.

·         Corregir el número de contacto y eliminar el nombre completo.

·         Leer la columna BLOB y extraer detalles de atributos de las etiquetas regulares.

·         Leer la columna BLOB y extraer detalles de atributos de columnas anidadas.

·         Leer la columna BLOB y extraer detalles de atributos de columnas anidadas.

·         Crear tablas separadas para los atributos BLOB.

·         Eliminar registros inválidos de los elementos de la orden donde el ID de envío no esté mapeado.

·         Mapear el nombre y apellido faltantes con las credenciales de correo electrónico.

Tech stack: 

SQL Programming language

Oracle SQL Developer

1.       Dividir el nombre completo en nombre y apellido:

Las columnas First Name y Last Name no tienen Datos, o sea, la columna FULL_Name tiene nombre más el apellido del cliente y queremos ahora rellenar los datos que están en la columna FULL_NAME en la columna First Name y Last Name :

SELECT *

FROM customers

WHERE FIRST_NAME IS NOT NULL

OR LAST_NAME     IS NOT NULL;

✔️NO TENEMOS DATA EN LA COLUMNA FIRST_NAME NI EN LA COLUMNA LAST_NAME.

✔️Tengo que dividir la columna FULL_NAME en 2 partes:

SELECT FULL_NAME,

  SUBSTR(FULL_NAME, 1, INSTR(FULL_NAME, ' ')-1) AS FIRST_NAME,

  SUBSTR(FULL_NAME, INSTR(FULL_NAME, ' ')   +1) AS LAST_NAME

FROM CUSTOMERS;

✔️Actualizar la tabla de Customers:

UPDATE CUSTOMERS

SET FIRST_NAME = SUBSTR(FULL_NAME, 1, INSTR(FULL_NAME, ' ')-1) ,

  LAST_NAME    = SUBSTR(FULL_NAME, INSTR(FULL_NAME, ' ')   +1);

COMMIT;

TABLA CUSTOMER ACTUALIZADA:

2.       Corregir números de teléfono y correos electrónicos que no estén en un formato adecuado:

En la columna del email en la tabla de Customers vemos que la Base de Datos tiene un problema con los emails, casi todos terminan sin .com y lo que quiero ahora mismo es corregir esta situación.

1    tammy.bryant@internalmail      Tammy Bryant   Tammy Bryant  4882064823,8

2   roy.white@internalmail               Roy White           Roy  White    1970715550

4   victor.morris@internalmail          Victor Morris     Victor    Morris  226556146,7

5   beverly.hughes@internalmail    Beverly Hughes Beverly Hughes  6037014216,9

6   evelyn.torres@internalmail         Evelyn Torres     Evelyn   Torres   2503972924,5

7   carl.lee@internalmail                   Carl Lee                Carl       Lee         8902529098

Queremos ver si tenemos emails que terminen con .com:

SELECT *

FROM CUSTOMERS

WHERE upper(EMAIL_ADDRESS) LIKE upper('%.com%');

✔️Segundo esta consulta no tenemos registros que terminen sus emails con " .com"

✔️Quiero  añadir ".com" a estos registros:

UPDATE CUSTOMERS

SET EMAIL_ADDRESS = EMAIL_ADDRESS || '.com'

WHERE upper(EMAIL_ADDRESS) NOT LIKE upper('%.com%');

✔️Tenemos 392 filas actualizadas con ".com ".

✔️Comprobamos que tenemos los registros actualizados con ".com":

SELECT *

FROM CUSTOMERS

WHERE upper(EMAIL_ADDRESS) LIKE upper('%.com%');

COMMIT;

3. En la columna del contacto hay varios números de teléfono / móvil que tienen un punto/coma, como por ejemplo:

SELECT COUNT(*)

FROM CUSTOMERS

WHERE CONTACT_NUMBER LIKE '%,%';

✔️Tenemos 348 registros con una coma.

✔️Actualizar la columna del Contacto:

SELECT CONTACT_NUMBER, SUBSTR(CONTACT_NUMBER, 1, INSTR(CONTACT_NUMBER, ',')-1)

FROM CUSTOMERS

WHERE CONTACT_NUMBER LIKE '%,%';

✔️Actualizar la Tabla Customer:

UPDATE CUSTOMERS

SET CONTACT_NUMBER = SUBSTR(CONTACT_NUMBER, 1, INSTR(CONTACT_NUMBER, ',')-1)

WHERE CONTACT_NUMBER LIKE '%,%';

COMMIT;

4.       Corregir el número de contacto y eliminar el nombre completo:

✔️Quiero saber  los números de contacto con menos de 10 dígitos, la consulta que voy a realizar es la siguiente:

SELECT CONTACT_NUMBER, LENGTH(CONTACT_NUMBER)

FROM customers

WHERE LENGTH(CONTACT_NUMBER) < 10;

✔️La Base de Datos tiene 40 filas que tienen menos de 10 dígitos.

✔️Para corregir esta situación crearé un numero como este “9999999999 “ para los números que tienen menos de 10 dígitos.

UPDATE CUSTOMERS

SET CONTACT_NUMBER = 9999999999

WHERE LENGTH(CONTACT_NUMBER) < 10;

✔️Nos salta un resultado en que: “40 filas actualizadas. “

✔️Queremos saber ahora los números de contacto con menos de 10 dígitos:

SELECT CONTACT_NUMBER, LENGTH(CONTACT_NUMBER)

FROM customers

WHERE LENGTH(CONTACT_NUMBER) > 10;

✔️No tenemos registros con números de teléfonos con más de 10 dígitos.

✔️Queremos contar el numero de dígitos que tiene cada registro para tal usamos esta consulta:

SELECT distinct LENGTH(CONTACT_NUMBER)

FROM customers;

✔️No tenemos ningún registro con más de 10 dígitos.

✔️Eliminar la columna FULL_NAME de la tabla de CUSTOMERS:

ALTER TABLE customers DROP COLUMN FULL_NAME;

Mensaje del Sistema: “Table CUSTOMERS alterado. “

5.       Leer la columna BLOB y extraer detalles de atributos de las etiquetas regulares:

✔️En la tabla Products vemos que hay muchas columnas sin datos, según la consulta:

SELECT * FROM products;

✔️En la tabla Products, tenemos una columna Product_details donde los registros en esa columna aparecen la palabra (BLOB).

✔️Ahora quiero apenas de la tabla de productos estas columnas:

SELECT PRODUCT_ID,PRODUCT_NAME,UNIT_PRICE,PRODUCT_DETAILS

FROM PRODUCTS;

✔️Los datos de la columna Product_details están dentro de un fichero JSON y quiero 2 columnas del fichero JSON (COLOUR Y GENDER).

Haré la siguiente consulta:

SELECT PRODUCT_ID,

  PRODUCT_NAME,

  UNIT_PRICE,

  PRODUCT_DETAILS,

  COLOUR_NAME,

  GENDER_TYPE

FROM PRODUCTS,

     JSON_TABLE

      (

        PRODUCTS.PRODUCT_DETAILS

        COLUMNS

            ( COLOUR_NAME VARCHAR2(50) PATH '$.colour',

              GENDER_TYPE VARCHAR2(20) PATH '$.gender' )

        );

La Tabla Products tendrá la siguiente apariencia:

6.       Leer la columna BLOB y extraer detalles de atributos de columnas anidadas.

✔️Quiero separar dentro del fichero JSON dos columnas que están dentro de una columna, para tal haré la siguiente consulta:

SELECT PRODUCT_ID, PRODUCT_NAME, UNIT_PRICE, PRODUCT_DETAILS, RATING, REVIEWS

FROM PRODUCTS,

  json_table (

    PRODUCTS.PRODUCT_DETAILS, '$.reviews[*]'

    COLUMNS (

        RATING NUMBER PATH '$.rating',

        REVIEWS VARCHAR2(200) PATH '$.review'

      )

);

✔️La tabla Products que conseguiríamos seria:

7.       Crear tablas separadas para los atributos BLOB:

CREATE TABLE PRODUCT_DETAILS AS

SELECT PRODUCT_ID,

  PRODUCT_NAME,

  UNIT_PRICE,

  COLOUR_NAME,

  GENDER_TYPE,

  BRAND,

  DESCRIPTION,

  SIZES

FROM PRODUCTS,

     JSON_TABLE

      (

        PRODUCTS.PRODUCT_DETAILS

        COLUMNS

            ( COLOUR_NAME VARCHAR2(50) PATH '$.colour',

              GENDER_TYPE VARCHAR2(20) PATH '$.gender',

              brand,

              description,

              sizes FORMAT JSON --sizes es una lista y tenemos que poner FORMAT JSON

              )

        );

Comprobar:       

SELECT * FROM PRODUCT_DETAILS;

8.       Eliminar registros inválidos de los elementos de la orden donde el ID de envío no esté mapeado:

SELECT * FROM order_items;

SELECT * FROM shipments;

SELECT COUNT(*)

FROM order_items

WHERE SHIPMENT_ID IS NULL;

✔️Tengo 1238 registros que no tienen SHIPMENT_ID.

SELECT COUNT(*) FROM order_items;

✔️Tengo 3914 resgistros de order_items.

✔️Eliminar los registros que tienen SHIPMENT_ID IS NULL, para tal haré esta consulta para saber cuales son los registros que tenemos que eliminar:

SELECT * FROM order_items

WHERE SHIPMENT_ID IS NULL;

✔️Aquí tengo los 1238 registros con NULL Shipement_ID.

✔️Ahora eliminamos estos registros:

DELETE FROM ORDER_ITEMS

WHERE SHIPMENT_ID IS NULL;

COMMIT;

Confirmación que están eliminados los registros:

SELECT COUNT(*)

FROM order_items

WHERE SHIPMENT_ID IS NULL;

9.       Mapear el nombre y apellido faltantes con las credenciales de correo electrónico.

✔️Hay ciertos users en la base de dados que tienen email pero no han puesto su nombre ni apellido. Quiero rellenar los registros que no tienen estos datos a partir del email.

SELECT *

FROM customers

WHERE FIRST_NAME IS NULL

OR LAST_NAME IS NULL;

✔️Tenemos en la base de datos 8 registros sin Nombre y Apellido.

La Consulta:

SELECT EMAIL_ADDRESS,

  SUBSTR(EMAIL_ADDRESS, 1, INSTR(EMAIL_ADDRESS, '.')-1) FIRST_NAME,

  SUBSTR(EMAIL_ADDRESS, INSTR(EMAIL_ADDRESS, '.')+1,

                        INSTR(SUBSTR(EMAIL_ADDRESS, INSTR(EMAIL_ADDRESS, '.')+1), '@')-1) LAST_NAME,

  INSTR(EMAIL_ADDRESS, '.')+1, --andrea.james@internalmail.com;

  INSTR(EMAIL_ADDRESS, '@'),

  INSTR(SUBSTR(EMAIL_ADDRESS, INSTR(EMAIL_ADDRESS, '.')+1), '@')-1 -- STRING: james@internalmail.com;

FROM customers

WHERE FIRST_NAME IS NULL

OR LAST_NAME IS NULL;

✔️Actualizando la BD:

UPDATE CUSTOMERS

SET FIRST_NAME = SUBSTR(EMAIL_ADDRESS, 1, INSTR(EMAIL_ADDRESS, '.')-1),

    LAST_NAME = SUBSTR(EMAIL_ADDRESS, INSTR(EMAIL_ADDRESS, '.')+1,

                      INSTR(SUBSTR(EMAIL_ADDRESS, INSTR(EMAIL_ADDRESS, '.')+1), '@')-1)

WHERE FIRST_NAME IS NULL

OR LAST_NAME IS NULL;

COMMIT;

✔️Ya tenemos todos los registros con Nombre y Apellido.

🟢Ahora que tenemos la base de datos limpia podemos hacer otro tipo de consultas.

1.       Análisis de Comportamiento del Cliente (CUSTOMERS):

Insight: Identificar patrones de compra por ubicación, edad o frecuencia de compra.

Consulta SQL:

SELECT CUSTOMER_ID, COUNT(ORDER_ID) AS frecuencia_compras

FROM orders

WHERE CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM customers)

GROUP BY CUSTOMER_ID

ORDER BY frecuencia_compras DESC;

Resultado:

Con estos dados que son resultado de la consulta SQL anterior, que muestra la frecuencia de compras por cliente, podemos realizar varios análisis interesantes que podrían ser valiosos para un negocio.

Algunas ideas:

-          Segmentación de Clientes Basada en la Frecuencia de Compra:

Podemos clasificar a los clientes en diferentes segmentos (por ejemplo, "frecuentes", "moderados", "ocasionales") basandose en su frecuencia de compra. Esto permitirá a un negócio desarrollar estrategias de marketing y promociones personalizadas.

-          Análisis de Fidelidad del Cliente:

Identifica a los clientes más leales (aquellos con mayor número de compras) para ofrecerles programas de fidelización, descuentos especiales o acceso a productos exclusivos.

-          Patrones de Compra y Recomendaciones de Productos:

Al combinar esta información con los detalles de las órdenes, se puede analizar qué productos prefieren los clientes que compran con frecuencia y usar estos datos para hacer recomendaciones personalizadas.

-          Identificación de Clientes en Riesgo de Abandono:

Los clientes con pocas compras a lo largo del tiempo pueden estar en riesgo de abandonar. Se puede desarrollar estrategias para reenganchar a estos clientes, como ofertas especiales o encuestas para entender sus necesidades.

-          Análisis de Tendencias Temporales:

Al incluir datos temporales (por ejemplo, la fecha de las compras), se podrá identificar tendencias como épocas del año con mayor actividad de compra o cambios en los patrones de compra de los clientes a lo largo del tiempo.

-          Correlación con Datos Demográficos:

Si tenemos acceso a la información demográfica de los clientes (como edad, ubicación, etc.), podremos analizar cómo estos factores influyen en la frecuencia de compra y desarrollar estrategias de marketing dirigidas.

2.       Gestión de Inventario (INVENTORY y PRODUCTS):

Insight: Determinar qué productos necesitan reabastecimiento.

Consulta SQL:

SELECT p.PRODUCT_ID, p.PRODUCT_NAME, SUM(i.PRODUCT_INVENTORY) AS total_inventario

FROM inventory i

JOIN products p ON i.PRODUCT_ID = p.PRODUCT_ID

GROUP BY p.PRODUCT_ID, p.PRODUCT_NAME

HAVING SUM(i.PRODUCT_INVENTORY) < 500;

Resultado:

Con los resultados de la consulta anterior, que muestra el inventario total de diferentes productos con un inventario menor a 500 unidades, hay varios análisis interesantes que se puede realizar:

1-      Identificación de Productos con Bajo Inventario:

Podemos identificar los productos que requieren reabastecimiento urgente. Esta información es crucial para evitar la falta de stock, lo que puede llevar a la pérdida de ventas y clientes insatisfechos.

2-      Análisis de Demanda de Productos:

Si ciertos productos aparecen constantemente con bajo inventario, esto podría indicar una alta demanda. Esto puede ayudarte a ajustar tus estrategias de inventario y producción.

3-      Planificación de Compras y Producción:

Basándote en los niveles de inventario, puedes planificar las compras o la producción futura para asegurar que tengas suficiente stock de los productos más populares.

4-      Evaluación de Estrategias de Precios:

Para los productos con bajo inventario, considera si una estrategia de precios dinámica podría ser beneficiosa. Por ejemplo, podrías aumentar los precios ligeramente si la demanda es alta y el inventario es bajo.

5-      Análisis de Tendencias Estacionales:

Revisa si hay patrones estacionales en los productos con bajo inventario. Esto puede ayudarte a predecir cuándo necesitarás reabastecer ciertos artículos en el futuro.

6-      Relación con Promociones y Campañas de Marketing:

Analiza si las recientes campañas de marketing o promociones han afectado los niveles de inventario de ciertos productos, lo que podría indicar la efectividad de estas campañas.

7-      Distribución de Inventario entre Tiendas:

Si manejas múltiples tiendas o canales de venta, considera cómo se distribuye el inventario entre estas. Podrías necesitar redistribuir el stock para equilibrar los niveles de inventario.

Estos análisis pueden proporcionarte insights valiosos para mejorar la gestión de inventario, la planificación de compras y las estrategias de marketing y ventas. Además, te ayudarán a entender mejor la dinámica de la demanda de tus productos.

3.       Rendimiento de las Tiendas (STORES y ORDERS):

Insight: Evaluar el rendimiento de las tiendas físicas y online.

Consulta SQL:

SELECT s.STORE_ID, s.STORE_NAME, COUNT(o.ORDER_ID) AS total_ventas

FROM orders o

JOIN stores s ON o.STORE_ID = s.STORE_ID

GROUP BY s.STORE_ID, s.STORE_NAME

ORDER BY total_ventas DESC;

Resultado:

Con los resultados de la consulta anterior, muestra el total de ventas por tienda, donde se puede realizar varios análisis de datos valiosos que pueden ayudar a mejorar el rendimiento de las tiendas.

Os dejo algunas ideas cómo el análisis de datos puede ser una herramienta poderosa:

1-      Análisis de Rendimiento por Ubicación:

Identifica las tiendas con mejor y peor rendimiento. Analiza las posibles razones detrás de estas diferencias, como la ubicación geográfica, el tamaño de la tienda, la demografía local, o la competencia en el área.

2-      Estrategias de Marketing Localizadas:

Desarrolla estrategias de marketing específicas para cada tienda basadas en su rendimiento y características únicas. Por ejemplo, las tiendas con menos ventas podrían beneficiarse de promociones especiales o campañas de marketing más intensas.

3-      Optimización de Inventario:

Analiza si el inventario de cada tienda está alineado con su volumen de ventas. Las tiendas con más ventas podrían requerir un reabastecimiento más frecuente o una mayor variedad de productos.

4-      Comparación con Indicadores Clave:

Compara las ventas de las tiendas con otros indicadores clave, como el tráfico peatonal, las calificaciones de los clientes, o la eficiencia del personal. Esto puede ayudar a identificar áreas de mejora.

5-      Análisis Temporal:

Si tienes acceso a datos históricos, compara las ventas a lo largo del tiempo para identificar tendencias, como el crecimiento o la disminución en ciertas ubicaciones y ajusta las estrategias en consecuencia.

6-      Evaluación de la Experiencia del Cliente:

Realiza encuestas de satisfacción del cliente en diferentes tiendas para ver cómo las percepciones del cliente se correlacionan con el volumen de ventas. Utiliza esta información para mejorar la experiencia del cliente.

7-      Estudio de Impacto de Eventos o Cambios Locales:

Investiga cómo eventos locales, cambios en la infraestructura o la apertura de nuevas tiendas han afectado las ventas en cada ubicación. Esto puede ofrecer insights sobre cómo factores externos impactan el rendimiento.

8-      Benchmarking y Mejores Prácticas:

Identifica las mejores prácticas de las tiendas de mayor rendimiento y considera cómo se pueden aplicar estas prácticas en otras tiendas para mejorar su rendimiento.

Estos análisis pueden ofrecer a tu jefe, a tu CEO o a ti mismo una comprensión profunda de la dinámica de sus negocios y cómo el análisis de datos puede ser utilizado para tomar decisiones estratégicas más informadas y mejorar el rendimiento general de la empresa.