MySQL Data Analyst building backend telemetry pipelines, restocking engines and revenue audits. Focused on proof of work

github

Hafsa Motiwala  • Peerlist

<h3>Project Overview</h3><p>This project focuses on optimizing procurement and supply chain resilience for an online storefront called Horizon Marketplace. The business faces challenges with shrinking profit margins, tied-up cash, and customer complaints due to stockouts. The analysis aims to identify unreliable suppliers, quantify revenue lost from empty shelves, and develop an automated system for reordering stock.</p><h3>Data Infrastructure and Database Design</h3><p>The analytical pipeline integrates raw database logs, sales ledger transactions, and daily warehouse inventory snapshots. The database schema includes tables for products, sales, purchase orders, and daily inventory, establishing relationships between them to track product flow and stock levels.</p><h3>Key Findings and Analytical Insights</h3><ul><li><p><strong>Supplier Performance:</strong> Analysis revealed that suppliers have unreliable delivery timelines. Alpha Logistics shows a high Perfect OTIF rate, while Delta Corp has a significantly lower rate and frequent delays, impacting inventory availability.</p></li><li><p><strong>ABC Inventory Tiers:</strong> Products are categorized into tiers based on revenue contribution. Class A products (23 items) generate 80% of the revenue, while Class C products are slow-moving and consume valuable warehouse space.</p></li><li><p><strong>Stockout Impact:</strong> Significant revenue loss ($63,897.26 for one product) was attributed to stockouts caused by supplier delays, highlighting the critical need to maintain inventory for high-demand items.</p></li><li><p><strong>Restocking Engine:</strong> A predictive restocking matrix is proposed, calculating safety stock and reorder points based on sales volatility and supplier lead times to prevent stockouts.</p></li><li><p><strong>Warehouse Performance:</strong> Both regional warehouses (WH-EAST and WH-WEST) demonstrate similar efficiency, indicating that the core issues stem from upstream supplier performance rather than internal operations.</p></li></ul><h3>Strategic Recommendations</h3><p>The project recommends the following actions:</p><ol><li><p><strong>Supplier Accountability:</strong> Implement performance improvement plans for unreliable suppliers like Delta Corp or reallocate their volume.</p></li><li><p><strong>Automated Ordering:</strong> Automate the reordering process for top-selling products based on calculated safety stock limits and reorder triggers.</p></li><li><p><strong>Inventory Liquidation:</strong> Reduce purchases of low-velocity Class C products and liquidate existing slow-moving stock to free up cash and warehouse space.</p></li></ol>

StockPulse

An end-to-end SQL database audit isolating onboarding latency bottlenecks, 15-day silent churn pacing thresholds, and financial leakage models. Includes complete database schemas and strategic implementation guides.

Audit: B2B Funnel Optimization and Revenue Le

A behavioral analysis of user activity across SaaS products, focusing on engagement, retention, conversion, and churn patterns over time.