Finding the bottlenecks in a $66.31M operation using SQL, Power BI and root cause analysis.
A retail logistics company is seeing inconsistent fulfillment times across its warehouse network. The average is 6.44 days for delivery. Why are some orders taking significantly longer than others?
Secondary question: The finance team is missing invoice deadlines. Is there a way to automate the tracking so nothing falls through manually?
The fulfillment problem is not systemic. It is localized.
Analysis of fulfillment times by warehouse identified that the North Region's 6.97-day average was driven by a single warehouse: WH-4, averaging 7.63 days. Every other warehouse in the network was performing within acceptable range. The problem was not the region's processes overall. It was one facility.
The East Region's low revenue has a supply explanation, not a demand explanation.
The East Region had the lowest revenue at $7.67M. Isolating inventory data showed it also had the lowest stock levels at 475 units. Lost sales due to stock-outs, not weak demand, is the more likely explanation. The fix is a supply chain adjustment, not a sales strategy.
A regional average hides the real problem. "The North Region is slow" leads to region-wide process reviews, retraining, and broad interventions. "WH-4 specifically is slow" leads to a targeted audit of one facility. These are completely different responses with completely different costs.
The same logic applies to the East Region revenue gap. "East has weak sales" leads to marketing spend. "East has insufficient inventory" leads to a restocking decision. Getting the segmentation right before making a recommendation is the entire point of this kind of analysis.
Power BI Dashboard Tracks $66.31M in revenue across four regions. Data model built on Star Schema connecting Sales, Finance, CRM, and Operations tables. Key metrics: Total Revenue, Average Fulfillment Days, Conversion Rate (0.52). Regional heatmaps and fulfillment trends by warehouse.
Finance Automation (Google Apps Script)
The finance team was monitoring invoice deadlines manually. The script runs daily:
- Invoices due within 5 days are flagged yellow.
- Overdue unpaid invoices trigger an automated HTML email alert to the finance team.
Result: manual monitoring time reduced by 60% and no missed escalations.
SQL Analysis
- Top 5 customers by revenue for account management prioritization.
- Overdue payments by region (North Region highest at 53 overdue accounts).
- Lead-to-sale funnel analysis tracking conversion from New to Closed.
Operations: Conduct a targeted process audit of Warehouse WH-4 specifically. Do not apply region-wide changes until the root cause at WH-4 is understood.
East Region: Increase inventory allocation before addressing sales strategy. The revenue gap is more likely a supply constraint than a demand problem.
Finance: Maintain the automated alert script and review escalation thresholds quarterly.
SQL Scripts/- Full query library for all analysis.Dashboard_and_Models/- Power BI files and data models.Process Automation/- Google Apps Script codebase.Strategic_Analysis/- Full root cause analysis report.
SQL, Power BI, Google Apps Script, JavaScript, root cause analysis, Star Schema data modeling