Developing a Power BI Weather Dashboard to Optimize Transportation Costs
In this case study, read about how Clarkston’s data and analytics experts helped with developing a Power BI weather dashboard to optimize transportation costs for a biopharmaceutical company. Read a synopsis of the project below or download the full case study.
Download the Developing a Power BI Weather Dashboard Case Study Here
Clarkston Consulting, in partnership with a biopharmaceutical company specializing in immune-dermatology treatments such as plaque psoriasis and atomic dermatitis, developed a tool integrating Python and PowerBI to monitor weather trends and conditions to identify optimal timeframes across the United States to ship drugs required to be maintained within a specific temperature range during transit.
Prior to the inception of the tool, the client was using, expensive coolers to maintain the product temperature year-round. The client’s sales representatives received four annual sample shipments. To reduce costs, the use of corrugated boxes with temperature indicators during select months of the year was proposed. The idea needed to be tested and the specific times of the year needed to be identified before implementing this strategy.
The client planned a pilot to ship to all their sales representatives across the United States. The logistics team manually evaluated weather conditions weekly for every location within their network for two weeks prior to the planned shipping date. This process was extremely time-consuming and cumbersome, and the efforts were wasted due to high temperatures delaying the pilot. The delay provided an opportunity to create a more optimized approach to gathering and evaluating weather data.
To address the noted challenges, the Clarkston team acquired and then queried 40 years of open-source climate data for locations across the client’s distribution network from an API to model optimal shipping schedules. This team then transformed this analysis into a PowerBI dashboard for ease of use and accessibility.
The dashboard’s first use case was to identify optimal shipping times using historical weather data, based on the drug’s labeled conditions. Once the timing had been identified, the forecast component of the visually intuitive dashboard determined packaging strategy. The tool played a large role in the pilot’s success. Shipments from both waves went very smoothly and arrived to each sales representative with no issues. There was no product loss during the pilot, and the new process was adopted and implemented for all future shipments.
Download the Developing a Power BI Weather Dashboard case study, and learn more about our Data and Analytics Consulting Services by contacting us below.
Contributions by Quinn R. Anderson & Ayush Makhija
