Parcel Shipping Analytics: A Case Study on FedEx and UPS Invoices
ObjectiveOur main objectives for this project were to:
- Convert raw FedEx and UPS invoices into a structured and organized database format
- Develop interactive dashboards in Power BI to provide actionable insights into our client's parcel shipping expenses
- Enable our client to make data-driven decisions to optimize their parcel shipping costs and improve their bottom line.
To achieve our objectives, we first developed a data model by converting the raw FedEx and UPS invoices into structured database tables using SQL Server Management Studio. We identified the relevant fields in the invoices, including carrier, service type, shipment weight, shipping cost, and accessorial charges, and organized them into tables to create a relational database.
Next, we connected our database to Power BI and developed several interactive dashboards to provide visibility into our client's parcel shipping expenses. The dashboards included visualizations such as:
- Parcel spend by the carrier, service type, and accessorial charges
- Carrier performance metrics such as on-time delivery, delivery time, and transit time
- Shipping cost trends over time and by destination zones
- Accessorial charge analysis to identify opportunities for cost savings
By leveraging Power BI to visualize and analyze their parcel shipping expenses, our client was able to gain visibility into their shipping costs and identify areas where they could optimize their spend. The dashboards we created enabled them to make data-driven decisions about carrier selection, service types, and accessorial charges, resulting in significant cost savings