Background
As businesses grow, they often face challenges in managing their parcel shipping expenses, which can quickly become a significant cost center. In this case study, we will explore how we converted raw FedEx and UPS invoices into relation database tables and created interactive dashboards in Power BI to help our client gain visibility and control over their parcel shipping costs.
Objective
Our 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.
Solution
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
Results
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 spending. The dashboards we created enabled them to make data-driven decisions about carrier selection, service types, and accessorial charges, resulting in significant cost savings
Conclusion
By converting raw FedEx and UPS invoices into a structured database and developing interactive dashboards in Power BI, we were able to help our client gain visibility and control over their parcel shipping expenses. By enabling our client to make data-driven decisions about their parcel shipping, we were able to help them optimize their spend and improve their bottom line. The use of Power BI allowed our client to identify opportunities for cost savings, and make informed decisions to improve their parcel shipping operations.