Leveraging service performance analytics and reporting BA-style artefacts to support supply-chain prioritisation decisions.
Presca Wanki — Service Performance & Insights Analyst (Reporting BA lens)
Focus: KPI definition, reporting requirements, data validation, and performance insights to support service improvement decisions.
- Decision supported: Prioritise countries for OTD improvement and identify key levers (Customer Segment / Shipping Class)
- Service KPIs: Late Delivery % (primary) + OTD % (derived)
- Tooling: BigQuery (SQL) → Tableau → GitHub documentation
- Included: Requirements + acceptance criteria, KPI glossary, validation rules, dashboard views, recommended actions & success measures
📊 Tableau Dashboard:
https://public.tableau.com/app/profile/presca.evans/viz/GlobalLogisticsandSalesPerformance/GlobalLogisticsDashboard
- Total Orders – order-level demand volume (base N)
- Sales – revenue context by country / segment / time (dataset assumption)
- Profit – directional profitability indicator to support prioritisation (dataset assumption)
- Late Delivery % – % of orders delivered after promised date (primary service KPI)
- OTD % – 100% − Late Delivery % (derived for readability)
As a Logistics & Customer Experience Analyst, I supported global shipment performance by tracking on-time delivery (OTD), late orders, escalations and customer impact across multiple regions.
Inspired by that work, this project uses a simulated logistics dataset to demonstrate how leaders can combine service performance, cost-to-serve and customer value into a single reporting view for faster prioritisation decisions.
Note: This project uses a simulated dataset designed to mirror global shipping performance scenarios. No confidential Pfizer data is used.
- Global Logistics Lead
- Customer Experience Lead
Which countries should we prioritise for OTD improvement — and what levers (segment, shipping class) will move the KPI fastest?
- Grain: Order-level (dashboard uses aggregated summaries for performance)
- Required filters: Country, Customer Segment, Shipping Class
- Late Delivery % = % of orders delivered after the promised date
- OTD % = 100% − Late Delivery % (derived for readability)
- OTIF note: “In-Full” is not measured in this dataset; OTD is used as the service KPI.
- Delivery performance varies widely across countries
- Shipping classes differ in cost and reliability
- High-volume customers are not always the most profitable
- Late deliveries cluster in specific periods and segments
📌 Example visual (map): (red = higher delays, larger bubbles = higher order volume)
| Component | Tool |
|---|---|
| Cloud data warehouse | Google BigQuery (SQL) |
| Visualisation / BI | Tableau Public |
| Version control | GitHub |
| Documentation | README.md |
This project is structured like a reporting delivery: define the decision need, confirm KPI rules, validate data quality, then build a dashboard that meets the reporting requirements.
Scope note: This is a BA reporting deliverable focused on KPI definition, reporting requirements, validation and decision support. The SQL/BigQuery layer is the implementation used to produce trusted metrics.
- Definition consistency: KPI rules match the glossary across the map, trends, and breakdown views.
- Transparency: Invalid/null date exclusions are handled consistently and visible; “Unknown” categories remain visible (not silently dropped).
- Usability: Base N and clear labels/definitions are shown to prevent misinterpretation.
This process map summarises the order-to-delivery workflow, key handoffs, and the exception path for data issues (for example, master data corrections) that can affect service KPI accuracy and operational decisions.
As a Global Logistics Lead, I need a ranked view of countries by Late Delivery % and order volume so I can prioritise OTD improvement initiatives.
Acceptance criteria
- Ranking updates based on selected filters (Country, Customer Segment, Shipping Class)
- Late Delivery % follows the glossary definition (after promised date)
- Invalid/null promised or delivered dates are excluded from KPI calculation and counted separately (visible base/exclusions)
- View shows the order base (N) to avoid misinterpretation
As a Customer Experience Lead, I need to slice Late Delivery % by Customer Segment and Shipping Class so I can target actions that reduce delays for high-value customers.
Acceptance criteria
- Segment and Shipping Class breakdowns use consistent KPI rules across all views
- “Unknown” Shipping Class is retained and visible (not silently dropped)
- Sales/Profit context is available alongside service performance where relevant
As a Global Logistics Lead, I need a trend view of Late Delivery % so I can distinguish temporary spikes from sustained deterioration.
Acceptance criteria
- Trend uses the same KPI rules as prioritisation (no mismatch)
- Filters apply consistently and trend remains interpretable by country/segment
- Trend supports spike vs sustained deterioration interpretation
As a Global Logistics Lead, I need to compare shipping classes on Late Delivery % vs Cost-to-Serve vs Profit impact so I can adjust routing rules without harming service.
Acceptance criteria
- Comparison is filterable by country and segment
- Cost-to-Serve and Profit follow glossary caveats (dataset assumptions noted)
- “High cost / low benefit” classes are visible to support rule review
As a reporting BA, I need data quality visibility so stakeholders can trust the service KPI and understand exclusions.
Acceptance criteria
- Null/invalid dates handling is stated (flagged + excluded from SLA KPIs)
- Country naming standardisation prevents double counting
- Missing Shipping Class is surfaced (“Unknown” category)
As a CX Lead, I need a simple way to turn findings into actions so improvement work is trackable.
Acceptance criteria
- “Recommended actions” includes owners and success measures
- Priority list approach is defined (Top countries by Late % + volume + value context)
| KPI | What it means | How it’s calculated | Grain | Exclusions / missing-data handling | Filters / breakdowns |
|---|---|---|---|---|---|
| Late Delivery % | % of orders delivered after the promised date | Late orders ÷ valid orders × 100 | Order | Exclude orders with null/invalid promised or delivered dates; keep excluded count visible for transparency | Country, Customer Segment, Shipping Class, Time |
| OTD % | On-time delivery rate | 100% − Late Delivery % | Order | Same rules as Late Delivery % | Country, Customer Segment, Shipping Class, Time |
| Total Orders (Base N) | Total order volume (context for KPI interpretation) | Count of orders | Order | Keep “Unknown” shipping class visible; orders with invalid dates are excluded from Late % denominator but still count toward volume | Country, Segment, Shipping Class, Time |
| Sales | Revenue context to interpret service impact | Sum of sales amount | Order | Flag anomalies (e.g., negative/zero values) rather than silently removing | Country, Segment, Shipping Class, Time |
| Profit (directional) | Directional profitability indicator for prioritisation | Sales − Cost (dataset assumption) | Order | Flag cost/profit anomalies for review; do not silently remove | Country, Segment, Shipping Class, Time |
| Cost-to-Serve (proxy) | Directional service cost per order (supports shipping-class decisions) | Cost per order / cost proxy (dataset fields) | Order | Flag extreme outliers; keep visible for transparency | Country, Segment, Shipping Class, Time |
| Shipping Class Mix | Distribution of orders across shipping classes | % of total orders by shipping class | Order | Keep “Unknown” class visible (data quality transparency) | Country, Segment, Time |
Notes
- “Valid orders” for Late Delivery % = orders with both promised date and delivered date present and valid.
- Profit and Cost-to-Serve are directional and depend on dataset assumptions; use for comparisons and prioritisation, not accounting-grade reporting.
- Dataset is simulated; results illustrate a repeatable reporting approach rather than real-world Pfizer performance.
- Cost-to-Serve is a proxy based on dataset assumptions; use as directional for comparisons.
- Service KPI focuses on OTD (promised vs delivered date); OTIF “In-Full” is not available in this dataset.
| Risk area | Handling approach |
|---|---|
| Null/invalid delivery or promised dates | Flagged and excluded from SLA KPI calculations; counted for transparency |
| Inconsistent country naming | Normalised using mapping tables to prevent double counting |
| Missing Shipping Class | Retained as “Unknown” to avoid hiding issues |
| Cost/profit anomalies | Flagged in summary outputs for review (not silently removed) |
- Late Delivery % is always between 0% and 100%
- OTD % equals 100% − Late Delivery % for the same filter set
- Late Delivery % denominator excludes invalid/null promised or delivered dates (and excluded count is visible)
- Dashboard totals reconcile to summary tables for the same filter set
- KPI rules are consistent across prioritisation, breakdowns and trends
- “Unknown” Shipping Class is visible and included in totals
- Loaded shipment, order, customer and product tables into BigQuery
- Created staging logic to handle nulls, invalid dates, and inconsistent country names
- Built curated summary tables to keep Tableau performant while preserving order-level logic:
summary_customer_segmentsummary_late_deliveriessummary_orders_overtimesummary_shipping_efficiencysummary_country_mapping
📌 Summary outputs are exported to Data Summary/ for transparency and reproducibility.
Executive layer (fast scan)
- KPI banner: Total Orders, Sales, Profit, Late Delivery %, OTD %
Prioritisation layer (where to act)
- Country-level prioritisation view (order volume + Late Delivery %)
Diagnostics layer (why / which lever)
- Shipping-class efficiency comparison (cost vs service trade-offs)
- Segment distribution (value + service exposure)
- Trend views (spikes vs sustained deterioration)
Look for: Priority countries with high Late % + high volume
Look for: “High cost / low benefit” classes for routing rule review
Look for: Spike-based vs sustained deterioration (different fixes)
- Service risk is concentrated: A small set of countries drive the largest late-delivery impact (late % + volume).
- Cost isn’t always buying reliability: Some higher-cost shipping classes don’t consistently improve OTD outcomes.
- Value exposure exists by segment: Delays affecting high-value segments create disproportionate customer risk.
- Patterns repeat over time: Trends indicate spike-based vs structural issues — requiring different fixes.
| Action | Owner | Success measures |
|---|---|---|
| Build a Top 5 “Priority Countries” backlog (Late % + volume + value context) | Global Logistics Lead + CX Lead | Late Delivery % reduction in priority countries; reduced repeat late patterns |
| Review shipping-class decision rules in priority countries | Ops Excellence + Carrier/Procurement | Improved OTD at equal/lower cost-to-serve; reduced profit leakage |
| Protect high-value segments during peak periods (prioritisation + proactive comms) | CX Lead + Planning | Late Delivery % improvement for priority segments; fewer customer-impact incidents |
| Run structured root-cause reviews for priority countries | Cross-functional | Sustained improvement over 4 weeks post-fix; fewer repeat exceptions |
- Data quality exclusions are explicit (invalid/null dates flagged and excluded from SLA KPIs)
- Country naming is standardised to prevent inconsistent rollups
- “Unknown” Shipping Class is retained and visible for transparency
Service: Late Delivery %, OTD % (overall + priority countries), Late % by segment & class
Operational: volume in priority countries, repeat late patterns
Commercial: profit/cost movement by shipping class, value-segment exposure
- Add explicit SLA thresholds (on-track / at-risk / critical) to support early warning
- Add carrier benchmarking (if carrier fields available) to support negotiation and accountability
- Extend drill-down to regional/hub-level views (if geography fields available)
- Add customer behaviour signals (e.g., reorder frequency, escalation sensitivity)
- Automated refresh + alerting (BigQuery → Tableau schedule + sustained breach alerts)
Global-Logistics-and-Sales-Performance/
├── README.md
├── LICENSE
├── Images/
│ ├── Global_logistics_map.jpeg
│ ├── Shipping_Class_Efficiency.jpeg
│ └── Sales_Profit_Trend.jpeg
├── SQL Scripts/
│ ├── (staging + cleaning + summary table SQL scripts)
│ └── ...
└── Data Summary/
├── summary_country_mapping.csv
├── summary_customer_segment.csv
├── summary_late_deliveries.csv
├── summary_orders_overtime.csv
└── summary_shipping_efficiency.csv