Delivering fresh groceries to customers is a challenge, even more so in the summer months when ice cream needs to remain perfectly frozen and the outside temperature is above 30 degrees. In order to succeed in this challenge, Picnic has numerous operational processes in place. We would like to share with you how we set up exactly the right processes by having visibility from start to end.

Preparing the deliveries for our customers starts in our Fulfilment Center (FC). Here, we store fresh products in two cold storage areas, i.e., one for chilled and one for frozen products. These areas ensure products remain the right temperature until they are collected in small crates for our customers. From the moment products leave the FC, they are exposed to outside temperatures. In order to prevent immediate temperature changes, the crates in which we ship chilled and frozen products are insulated and have additional cooling elements inside. From the FC, products are shipped to different hubs by a large truck. They are then loaded into our delivery vehicle and the last mile delivery starts.

How do we know if the cooling elements in the crates keep products fresh, and how many to add on different days? In order to find reliable answers to those questions, we started to measure temperature in our crates.

Measuring temperature every step of the way

Every day in each FC, a selection of crates get an additional log tag which measures the temperature throughout the day. After all products for the customer are collected, we add log tags to the crates using a handheld scanner. This scanner runs our Warehouse Management System (WMS). Adding a log tag is really easy using the simple and intuitive guided flow (see earlier blog post about how we build guided flows). Directly after the log tag is added, we close the crate with a lid and the journey starts. Every 5 minutes, the inside temperature of the crate is measured throughout our supply chain.

Collecting the data and preparing it for analysis

Just like we have warehouses to fulfill deliveries at a large scale, we also have a Data Warehouse (DWH) that fulfills our analytical requirements. The DWH collects, integrates and structures data from all operational source systems to allow for deep analyses across the business. The tracking of product temperature along the supply chain provides important insights into our logistics excellence, and as such is extensively covered in the DWH.

When it comes to temperature logging, there are two essential data sources that need to be combined in order to provide high quality data. The first source is the readings from the log tag itself. After delivery, log tags are collected and brought back to the FC where the measurement files are uploaded to an Amazon S3 bucket. The moment the files arrive on S3 they are automatically loaded into the Data Warehouse for further integration and analysis.

Example payload of log tag reading:

{
“logtag_date”: “21-Sep-20”,
“logtag_id”: “1800144217”,
“logtag_reading”: “3.2”,
“logtag_time”: “10:48:46”
}

As can be seen from example above, the log tag device only provides the data it is aware of – the temperature reading at a given moment. This by itself is not very useful as there is no way of knowing the context in which the reading occurred. This is where the second data source comes into play, the coupling between a log tag and a crate of products that is delivered to the customer’s door. For this, we need to find the corresponding WMS event created using the guided flow on the handheld scanner. This essential data is provided by the WMS to the DWH via events that are emitted at the moment of scanning. The event is immediately sent to our messaging queue RabbitMQ, where it is routed to the DWH collector.

Example payload of WMS log tag to crate coupling:

{
“delivery_ids”: [
“2tz77zl3qq”
],
“logtag_id”: “1800144217”,
“timestamp”: 1596964228341,
“crate_id”: “15022217”
}

Those two sources are combined in the DWH based on the logtag_id and timestamp fields. The key on which we join directly is the logtag_id which exists in both the measurement file and WMS event. Furthermore, we compare the event timestamp with the measurements timestamp and make sure we only take the measurements after the event timestamp into account. The delivery_id included in the WMS event serves as the key to connect with different types of data available in DWH. This data enables us to validate the performance of our cold chain on different days.

To make the analysis of product temperature complete and truly insightful, we also need additional data from the DWH besides the log tag readings and the WMS coupling to a delivery crate. The DWH also contains the readings from the truck’s on board computer, the data from the application that navigates the delivery vehicle to the customer’s door, and weather data that we collect from a public API of DarkSky. Based on this data, we know when the crate left the FC, when it was in the truck, and when it was delivered to the customer’s door. Throughout this entire chain, we can also check the outside temperature at the time. Together, this data is very powerful and allows us to make and measure improvements every step of the way.

Integrating all this data and making sense of it is a challenge. It doesn’t always go perfect but our DWH architecture helps us to discover data quality issues and address them. This is possible because all data for analysis is in the same place which enables quick checks just as well as thorough exploration. The Picnic DWH consists of multiple layers where the data is cleaned and centrally prepared. Therefore, when it comes to starting an analysis, we can easily see the progress of every product of a delivery in the DWH presentation layer. Beyond the tracking of the temperature, we can seamlessly combine this data with actual feedback from the customer. For example, if the customer reports that their ice cream was melted, we could track the product’s delivery path. Then we can pinpoint whether the root cause was the extremely hot weather, the cooling in the truck, the amount of ice packs in the crate or perhaps the lid was not tightly closed. This helps us act quickly and take systematic measures to improve in the future.

From data to actionable insights

Having all temperature related data in the same place enables us to validate the measures we take on different days. In the figure below, we plotted the inside crate temperature five hours before delivery on two different days. Operational guidelines tell us that we should add two cooling elements to the crate on a “normal” day versus four cooling elements on a hot day. This way, we ensure that products are delivered with the same temperature despite different weather conditions. You can also nicely see that each step in our supply chain has a different effect on our inside crate temperature. We are able to prevent a significant temperature increase at our locations because the FC and hubs are equipped with cold storage. When crates are shipped inbetween locations, the temperature increases but luckily not up to a point where the products start defrosting.

So shipping frozen items is a challenge. Especially when the crates have to travel a long way between locations. The figure below shows part of our distribution network. The bigger the distance, the higher the temperature increase between FC and hub. On really hot days, we are taking extra precautions by using trucks that can actively cool making sure the temperature does not reach a critical point where product freshness is at risk.

Putting our gained knowledge into practice

With all these insights we are ready to define operational guidelines. These guidelines say something about how we treat the crate with products throughout the entire chain. The hotter the day, the more energy we put in to keep our products fresh.

Each morning around 5 AM, a weather forecast message is automatically posted in Slack. It includes a location specific temperature forecast and a heat plan for the coming days. This heat plan is defined by a letter and provides answers to questions like, “How many cooling elements should we put in each crate?”, “Should we actively cool our trucks?”, “Can we store crates overnight at our hubs?”, etc. By sticking to this heat plan, we make sure we always provide just enough cooling capacity to the crate. We report product freshness issues and realized product temperatures on a weekly basis such that we never lose track of how our cold chain performs. If needed, we recalibrate our heat plan making sure our groceries are always delivered to the customer’s doorstep with the right temperature.