Orbit Retail

Category:

Data Engineering & Analytics / Business Intelligence (BI)

Client:

Orbit Retail (Fictitious Client)

The Challenge

Orbit Retail, a high-growth e-commerce startup, was paralyzed by unreliable data. While the volume of sales transactions was increasing, the data itself was messy, slow to process, and undermined critical business decisions.

The core issues were:

Data Quality: Critical fields, such as Order Date, were stored in inconsistent, non-standard formats (e.g., DD/MM/YYYY), causing ETL processes to fail and reports to break.
Missing Data: Key geographical identifiers, like Postal Codes, were often missing, hindering regional performance analysis.
Reactive Reporting: The complexity of key metrics (like rolling 30-day averages for anomaly detection) meant queries were too slow. The team was constantly reacting to sales events after they occurred, rather than proactively managing risks.

The Solution

My role as an Analytics Engineer Intern was to build the foundation of a reliable data platform using PostgreSQL and Power BI. I implemented a structured ELT (Extract, Load, Transform) pipeline to establish data governance and performance.

Key Technical Deliverables:

Data Cleansing & Structuring: Designed a professional two-tier schema (raw /analytics). Used Advanced SQL functions (TO_DATE, COALESCE) to fix date format inconsistencies and impute missing postal codes, creating a clean analytics.fact_sales table.
Performance Optimization: Introduced a Materialized View (analytics.sales_anomalies) to pre-calculate the complex, slow-running rolling 30-day sales average. This ensured that Power BI dashboards could query the results instantly.
Proactive Alerting: Developed and deployed a system using SQL Window Functions (AVG() OVER (...)) to automatically flag immediate Sales Anomaly Alerts, shifting the operational team from reactive review to proactive intervention.

The Result

The project successfully transformed the raw data into validated business intelligence across two core dashboards: Performance and Sales Trends. The results immediately empowered executives to make proactive decisions:

Finding Category	Key Insight & Business Impact
Strategic Performance	The West Region (31% of revenue) is the primary growth driver, led by the Consumer Segment. This validates marketing and inventory prioritization in that area.
Operational Risk	The Anomaly Detection System is live and successfully flagged a high-value sales spike ($1,207.84 for a specific chair) against its regional average, proving the system's utility for fraud and supply chain investigation.
Logistics Efficiency	Standard Class shipping averages 5 days, a full two-day lag compared to other modes. This finding provides the operations team with a clear target for improving fulfillment times.
Demand Forecasting	Sales exhibit strong predictable seasonality, with massive peaks consistently occurring every March and November. This is critical guidance for annual budgeting and staffing decisions.

Impact: The new data architecture established immediate data trust, significantly improved report load times, and successfully shifted Orbit Retail's operations from reactive reporting to a culture of proactive, data-driven decision-making.

View Project Deliverables Below:

(1). Orbit Retails Sales Dashboard

(2). Orbit Retail SQL Analysis