Using Weather Data to Predict Retail Sales
Overview of Use Case
Vantage Data is a retail focused analytics company. We help our retail partners to better plan their inventory and staffing for their retail locations. Vantage uses Alteryx to model a number of known factors (including day of week, season, time of month, holidays etc.) and now uses real time weather forecasts to predict changes sales figures in stores - including sales figures across key categories.
Our clients are looking to be more proactive with data and ensure that their resources (stock and personnel) are deployed forward in the most efficient manner possible. For example, DC Managers trying to prioritize shipments of stock to stores; planners trying to determine promotions and markdown strategies and timings; and regional HR managers trying to appropriately staff stores.
This case involves several steps including:
- Collecting historical weather data from online sources in JSON
- Geospatial mapping of weather data to store locations
- Normalizing sales data across stores and categories
- Building a predictive model (linear regression) based on weather (conditions, max temperature and min temperature)
- Collecting live daily weather forecasts for the next 14 days (JSON)
- Publishing a conditions favorability report across stores and categories
We used Designer to build the workflows and Alteryx Server is running on a regular basis to provide updated patterns. While Promote is not yet used, we are building a business case to have these models be managed and improved with promote as well as using the API endpoint functionality to integrate predictions with source systems.
Some of our key steps:
- Data is primarily housed in Microsoft Azure, Snowflake is the main repository for sales data and Alteryx Server is deployed in Azure
- Sales data is coming from Snowflake, we rely on the In-DB tools to process billions of rows of scan level data
- Weather data history is also coming from Snowflake - data is available in JSON which has historical conditions every 3 hours from over 20,000 data points
- Weather data forecasts are provided via an API from an online source (again in JSON format). This data provides 14-day forward predictions of minimum and maximum temperatures
- We use Tableau dashboards to provide executives with high level visibility of weather conditions in different regions. The dashboards also show high potential categories (like ice cream before a heat wave, or umbrellas before a rain storm)
- For the individual category managers, they use Alteryx Gallery analytic apps to download weather-based factors for their categories. They use these outputs to adjust their short-term inventory and promotion allocations
- For Regional managers - we use Alteryx Gallery to send reports of locations that are expecting an exceptionally high or exceptionally low amount of traffic in the next several days. These are exception reports which they can use to adjust staffing plans
 |
IN-DB Tools for daily sales at a store level Snowflake used for a data source of 1Bn rows (100GB)  |
 |
Find Nearest – Maps every store to a weather station Data stream out/data stream in – pushes heavy lifting to cloud (My favorite technique in Alteryx)  |
 |
Good old summarize and unique to determine the “dominant” weather conditions of the day  |
 |
Json parse tool combined with a little parse and filter magic! 
|
 |
 |
 |
 |
 |
Relative stock levels vs conditions favorability
 |
Within the first few weeks of use our weather prediction model, our customers have seen several key benefits, such as:
- Defer the planned markdown of winter stock days before a major cold front
- Reduce the stock out in leisure locations during a high temperature/good weather public holiday by planning an extra mid-day delivery in key locations (No running out of hamburgers and charcoal :))
- Adjust the space allocation of summer products in stores in more temperate locations
Alteryx makes me feel empowered. I love the feeling of helping someone win their time back with Alteryx. We have also mentored several employees within our client’s organizations who have used the power of Alteryx to truly alter their careers.
The power of cloud and the low cost of storage is making anything possible. Efficiencies that I always knew existed are becoming solvable through the availability of data. We are also becoming less and less limited by how data needs to be organized to extract value from it.
Coming soon we are very excited about guided machine learning and AI guided analytics. This will help point talented individuals to areas of their organizations where they can achieve real value. Using AI to start and guide this journey will give confidence and purpose to an entirely new generation of problem solvers
See details of use case presented at the London User Group in June 2019 attached.
