Combine COVID & POS Data to Ensure In-Stocks with Snowflake & Alteryx
We all know this is a truly unprecedented time in the world, and although sometimes we feel like we've been through something similar before, the reality is that we haven't. Sure - we've been through natural disasters. We've watched smaller outbreaks from afar. We've all made a stock-up trip to the grocery store "just in case" our power went out, or "just in case" we were stuck in our house during a cold-stretch for a couple days, but none of those compare to what we're seeing in our world right now. COVID-19 has drastically changed the way that the world works - both for now and for the future - and although we've never experienced anything like this before, we've also never been equipped to respond like we are today.
Of course the true heroes of this fight are the people on the front-line across many different industries - I won't attempt to list them all because I would never do it justice, but first and foremost, we owe them a level of gratitude that we will never been able to express to them.
While the front-line continues to fight for the health and safety of all of us, we each have our own small part we can play as well. For those of us in the Retail & CPG industry, it's making sure shelves are stocked with essential items.
To aid in this fight, we have one resource that is stronger than it's ever been... #data and #analytics. Although our challenge is greater than ever, so are the information & technology resources available to fight it. Thanks to partners like The Johns Hopkins University, StarSchema, Snowflake, Alteryx, and Atlas Technology Group, we are all able to access and leverage #data and #analytics to play our part in this fight.
Let's take a look at how to solve this problem:
The Pieces:
1) Access to real-time COVID-19 outbreak data - we need locations, dates, and numbers of cases - cue The Johns Hopkins University COVID-19 dataset, available FREE OF CHARGE through StarSchema within the #snowflakedataexchange on Snowflake Computing (https://www.snowflake.com/datasets/starschema/)
2) Access to POS data - we need store level, item level data - preferably by day. For those of us in the industry, this is available through partners like Atlas Technology Group (www.atlasdsr.com)
3) Access to the right tool to prep, combine, and analyze our data quickly and efficiently - cue #alteryx for the best advanced analytics platform out there! (www.alteryx.com)
4) Access to the right visualization tool - cue #tableau for a fantastic data visualization tool! (www.tableau.com)
The Questions:
The next step in the process is to blend together the data in #alteryx and understand whether an individual store's sales surge is impacted by a couple things:
1) It's proximity to an outbreak
2) Stay-at-home orders
3) Pantry-loading
The short answer to the (3) questions above is yes. So now what? Let's look at a quick example of a large category within a large national retailer (purposely vague and although data represents true results it has been tweaked slightly for sharing purposes, but applicable to almost everything in a Grocery store).
Our main questions to answer are:
1) What (and how many) stores are within what proximity to an outbreak?
Let's use #alteryx to find the nearest outbreak to each store:
Then let's use #tableau to map all of the stores. For this example, we used bands of 10 miles (nearest outbreak is within 10 miles, within 11-20 miles, within 21-30 miles, etc).
Thanks to #tableau for awesome & easy mapping - the dots above represent a sample of stores and their proximity - the darker the dot, the closer it is to an outbreak, but we grouped these into buckets of 10 miles.
The Results:
2) What is the initial sales surge? Around +50% across the board, regardless of proximity.
3) Does the sales surge remain consistent? No - let's look at the following weeks:
Week 2:
... and Week 3:
As we can see, the closer a store is to the outbreak, the quicker the sales surge decreases. You might think "well, that's because they bought more up front then the stores that are further away".... but they didn't. Their initial sales surges were all around +50%. So what happened?
There are likely two main drivers of the faster decrease in stores closer to outbreaks: first, I'm assuming "stay-at-home" orders are more strict. Second, and more important to our analysis, is that the stores went out of stock. Being closer to an outbreak has most likely made it harder to get those stores restocked, and therefore the stores run out much quicker than the stores that are further away.
Let's look at the Out of Stocks for Weeks 1-3 (blue is FEWER out of stocks, orange is MORE out of stocks):
The Insights:
1) Proximity to an outbreak DOES matter, although not in the first week - we see the same sales surge across all stores within the first week, but as the weeks go on, the closer stores will run out of stock much faster
2) As is expected, the states that were hit the hardest initially by COVID-19 are still struggling with out of stocks. California, New York, Pennsylvania, etc - these stores need more attention to ensure their shelves are stocked with essentials.
3) I can't get into the details here, but we are able to use sales velocities (how fast an item is selling on shelf) to predict when and where the next shipment will be needed. Using the COVID-19 data to track outbreaks, inventory, sales velocities, and predicted customer demand, we can strategically allocate resources to deliver the next shipment directly to where it needs to go!
Conclusion:
At Seek Data, our mission is to amplify your two most important assets: Your People + Your Data. We believe that this is a CRITICAL need for the next generation of business, especially in times like these. We would love to work with you to help you and your teams accelerate your analytics journey - so please feel free to reach out to find out more!
Thanks again to #snowflake, #alteryx, #starschema, #tableau, and #atlastechnologygroup for their partnerships throughout this journey, and for providing the best analytics solutions in the market!