Projects

Project 1
Olist Store E-Commerce

Excel Dashboard

Goal

I developed this dynamic Excel dashboard to transform 115,000+ rows of raw transactional data into a centralized command center. The objective was to provide stakeholders with real-time visibility into the $14.27M revenue stream, allowing them to monitor high-level KPIs like Average Order Value and On-Time Delivery rates through a single, interactive interface.

Process

Data Cleaning: Used Power Query to fix date formats, handle missing review scores and group 70+ messy product categories into clean labels
Data Modeling: Linked three separate tables (Orders, Payments and Products) using Order_ID as the primary key
KPI Engineering:
- On-Time Delivery: Created logic to compare "Estimated" vs. "Actual" delivery dates
- Average Order Value (AOV): Calculated total revenue divided by unique order counts

Key Insights

Single-Item Problem: The Average Product Price ($120.65) and Average Order Value ($120.64) are nearly identical. Customers almost never buy more than one item at a time.
Shipping Drain: Freight costs ($2.25M) eat up 15.8% of total revenue. This is a major leak in profit margins.
Revenue Concentration: A tiny fraction of categories (Health/Beauty, Housewares) generates the majority of the $14.2M revenue.

Recommendations

Bundle Products: Launch a "Free Shipping over $150" rule to push the $120 AOV higher and encourage multi-item carts.
Cut the Long Tail: Stop marketing the bottom 50% of low-performing categories and reinvest that budget into the top 5 "Hero" categories.
Logistics Check: With 16% of revenue going to shipping, the company should move inventory closer to high-density customer hubs to lower freight costs.

Tableau Dashboard

Goal

I built this comprehensive Tableau dashboard to analyze the correlation between logistics efficiency and customer satisfaction. By visualizing $15.70M in total sales, the goal was to identify supply chain bottlenecks, specifically the 21-day average delay tied to 1-star reviews, and provide data-driven benchmarks for the 3,090 active sellers.

Process

Calculated Fields: Used LOD Expressions (Level of Detail) to accurately count unique sellers and products (73) across various filtered views
Advanced Visuals: Developed Lollipop Charts and dual-axis graphs to highlight the inverse relationship between shipping speed and review scores
Time Intelligence: Created a "Weekday vs. Weekend" dimension to identify peak shopping periods

Key Insights

The Speed-Satisfaction Link: 5-star reviews average 11 days for delivery, while 1-star reviews take 21 days. Shipping speed is the single biggest driver of negative sentiment.
The Weekend Opportunity: 77.2% ($12.1M) of sales occur on weekdays. Weekend shopping is significantly underutilized, representing only 22.8% of total revenue.
Category Bottlenecks: The Fashion category has the highest delivery lag at 16 days, compared to just 11 days for Pet Shops.

Recommendations

The "11-Day" Standard: Set 11 days as the operational benchmark for all sellers. Any seller exceeding 12 days should receive automated performance warnings.
Weekend Flash Sales: Deploy weekend-only promotions to balance the weekly warehouse load and capture the missing 22.8% market share.
Fashion Logistics Audit: Investigate the supply chain for the "Fashion" and "PCs" categories to reduce their 16-day average and prevent them from dragging down the overall customer experience.

Power BI Dashboard

Goal

I built this Power BI dashboard to analyze $13.59M in revenue and identify geographical sales hotspots. By modeling Customers, Payments and Reviews, the goal was to provide a unified view of the Olist ecosystem and uncover how regional dominance in São Paulo and Credit Card usage (78.34%) drive the platform's growth.

Process

Data Modeling: Established a Star Schema to connect disparate tables via Order_ID and Customer_ID for seamless cross-filtering
DAX Measures: Authored DAX formulas to calculate Total Payment Value ($16.01M) vs. Net Revenue ($13.59M) to monitor the financial impact of vouchers
Regional Mapping: Integrated geographical data to visualize order density across all Brazilian states

Key Insights

Market Concentration: São Paulo is the clear powerhouse, driving over 40,000 orders. There is a significant drop-off in volume once moving outside the top three states (SP, RJ, MG).
Credit Card Reliance: 78.34% of payment volume is driven by credit cards. This suggests that installment-based purchasing is the primary driver of high-value transactions.
Revenue Gap: A discrepancy exists between payment value and revenue, highlighting high voucher usage that isn't captured as direct product profit.

Recommendations

Regional Expansion: Launch targeted marketing campaigns in Tier 2 states like Minas Gerais (MG) and Rio de Janeiro (RJ) to diversify the revenue base and reduce reliance on SP.
Payment Optimization: Partner with banks to offer exclusive cashback or installment deals for high-ticket items to further capitalize on credit card preferences.
Fulfillment Strategy: Prioritize logistics infrastructure in the top three states to ensure the 96K "Delivered" order volume maintains high customer satisfaction scores.

SQL Queries

Goal

I developed this SQL script to serve as the foundational data engine for the E-commerce project. The objective was to build a robust database architecture that integrates nine disparate datasets, covering orders, payments, reviews and logistic and extract high-level business intelligence. The queries were designed to provide stakeholders with data-driven answers regarding revenue trends, delivery performance, and seller distribution.

Process

Database Schema Setup: Created the ecommerce database and validated the integrity of nine core tables (orders, items, reviews, etc.) using SELECT * audits
Data Aggregation & Joining: Utilized complex JOIN logic (LEFT/RIGHT) to connect the olist_order_payments table with olist_order_reviews and olist_order_items for cross-functional analysis
Date & Time Intelligence:
- Day-Type Classification: Used DAYOFWEEK logic to categorize transactions into "WEEKDAY" vs "WEEKEND"
- Logistics Latency: Applied DATEDIFF and DATE_FORMAT functions to calculate carrier delay averages and monthly order trends
Customer & Seller Profiling: Built Common Table Expressions (CTEs) and GROUP BY logic to segment sellers by geography and identify repeat customer behaviors

Key Insights

Operational Latency: Calculated the average handover time to carriers and delivery delays, identifying that shipping efficiency is a key metric in customer satisfaction scores.
Payment Dominance: Identified that certain payment methods (Credit Card) drive higher order volumes and correlate with specific review score outcomes.
Geographical Concentration: Pinpointed the top 5 states and cities with the highest seller density, revealing where the platform’s supply chain is most concentrated.
Revenue Seasonality: The monthly trend and weekday/weekend queries highlight peak purchasing windows, showing exactly when the platform generates the most liquidity.

Recommendations

Carrier Optimization: Use the avg_carrier_delay_days insight to set stricter Service Level Agreements (SLAs) for sellers in high-delay regions.
Targeted Marketing: Since queries revealed specific "Top 5" states for sellers, marketing spend should be shifted to under-represented states to balance the marketplace.
Payment Strategy: With the data showing the most frequently used payment types, the business should optimize checkout flows for those specific methods to reduce cart abandonment.
Customer Retention: Based on the "Repeated Customers" query results, implement a loyalty program specifically targeting those customers to increase their lifetime value (LTV).

Projects

Project 1 Olist Store E-Commerce

Excel Dashboard

Project 1
Olist Store E-Commerce