Challenge BI - Global 66

El Problema

Al revisar el modelo actual, noté que la tabla de transacciones contenía información repetida y campos de dinero con formato incorrecto (comas en lugar de puntos), lo que impedía realizar cálculos directos.

Mi Solución

Realicé un proceso de limpieza de datos con SQL para estandarizar los montos y convertir tipos de datos. Posteriormente, generé consultas analíticas utilizando CTEs y Window Functions para obtener insights de negocio.

Logros Técnicos

🧹 Data Cleaning: Eliminación de caracteres no numéricos.
🔄 Casting: Conversión masiva a DECIMAL(18,2).
📊 Analytics: Ranking de clientes con ROW_NUMBER().

KPIs Calculados

Ticket Promedio Total Enviado / Transacciones

Revenue Total Suma de ingresos positivos

Top Países Destinos con mayor volumen

Código de Limpieza y Análisis

A continuación se presentan los scripts utilizados para la normalización de la base de datos y la extracción de métricas clave.

USE [Global66];
GO

-- Limpie las comas de todos los campos de dinero
UPDATE [transaction]
SET 
    origin_amount = REPLACE(origin_amount, ',', ''),
    origin_amount_usd = REPLACE(origin_amount_usd, ',', ''),
    destiny_amount = REPLACE(destiny_amount, ',', ''),
    destiny_amount_usd = REPLACE(destiny_amount_usd, ',', ''),
    discount_amount = REPLACE(discount_amount, ',', ''), 
    discount_amount_usd = REPLACE(discount_amount_usd, ',', ''),
    revenue = REPLACE(revenue, ',', '');
GO

-- Converti las columnas de Texto a Número (Decimal)
ALTER TABLE [transaction] ALTER COLUMN origin_amount DECIMAL(18, 2);
ALTER TABLE [transaction] ALTER COLUMN origin_amount_usd DECIMAL(18, 2);
ALTER TABLE [transaction] ALTER COLUMN destiny_amount DECIMAL(18, 2);
ALTER TABLE [transaction] ALTER COLUMN destiny_amount_usd DECIMAL(18, 2);
ALTER TABLE [transaction] ALTER COLUMN discount_amount DECIMAL(18, 2);
ALTER TABLE [transaction] ALTER COLUMN discount_amount_usd DECIMAL(18, 2);
ALTER TABLE [transaction] ALTER COLUMN revenue DECIMAL(18, 2);
GO

-- 1. Cantidad de dinero que envió cada cliente
SELECT 
    CONCAT(c.name, ' ', c.last_name) AS nombre_completo,
    SUM(t.origin_amount_usd) AS total_sent_usd
FROM [customer] c
JOIN [transaction] t ON c.customer_id = t.customer_id
GROUP BY c.customer_id, c.name, c.last_name
ORDER BY total_sent_usd DESC;

-- 2. Cantidad de transacciones por producto
SELECT
    product,
    COUNT(transaction_id) AS total_transactions
FROM [transaction]
GROUP BY product;

-- 3. Top 3 de clientes que más transacciones realizaron por producto
WITH TransactionCounts AS (
    SELECT
        product,
        customer_id,
        COUNT(transaction_id) AS tx_count
    FROM [transaction]
    GROUP BY product, customer_id
),
RankedCustomers AS (
    SELECT
        product,
        customer_id,
        tx_count,
        ROW_NUMBER() OVER(PARTITION BY product ORDER BY tx_count DESC) as ranking
    FROM TransactionCounts
)
SELECT
    rc.product,
    c.name,
    c.last_name,
    rc.tx_count
FROM RankedCustomers rc
JOIN customer c ON rc.customer_id = c.customer_id
WHERE rc.ranking <= 3;

-- 4. Primera transacción de cada cliente (fecha, monto y producto)
WITH FirstTx AS (
    SELECT
        customer_id,
        transaction_datetime,
        origin_amount_usd,
        product,
        ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY transaction_datetime ASC) as rn
    FROM [transaction]
)
SELECT
    customer_id,
    transaction_datetime,
    origin_amount_usd,
    product
FROM FirstTx
WHERE rn = 1;

-- 5. Top 10 de países que recibieron más transacciones
SELECT TOP 10
    destiny_country,
    COUNT(transaction_id) AS total_received
FROM [transaction]
GROUP BY destiny_country
ORDER BY total_received DESC;

-- 6. Cantidad de transacciones con revenue positivo
SELECT
    COUNT(transaction_id) AS positive_revenue
FROM [transaction]
WHERE revenue > 0;

-- 7. ¿Qué cliente es el que más revenue generó?
SELECT TOP 1
    c.name,
    c.last_name,
    SUM(t.revenue) AS total_revenue
FROM customer c
JOIN [transaction] t ON c.customer_id = t.customer_id
GROUP BY c.customer_id, c.name, c.last_name
ORDER BY total_revenue DESC;
        

Visualización de Resultados