Ideology of Solution
Upon consideration of the stated problem and ideal solution it was important to the RedShift group members to depart from focusing on a perfect solution that would otherwise be impossible to implement, but instead target a feasible, low cost solution with a well thought out plan of action. This approach allowed us to quickly rule out ideas including RFID, inks, and bottle changes due to the cost of implementation in manufacturing as well as the high variable costs incurred long term. The ideal solution need not be accurate on a case by case basis, but accurate in volume and across time within an acceptable confidence interval. It should also be simple to implement and easy to recruit vendors and distributors through incentives either offered by Coca Cola or through economies gained through implementation of the solution (higher volumes sold without occurring burdensome costs).
What is it?
Keep it simple. Rather than rely on equipment that is hard to implement, can be stolen or broken, etc.. We rely on a simple assumption and build stopgaps for potential issues that arise around it. The assumption: If someone opens the cooler, and a coke is bought a short time after, they took a coke from the cooler. No cameras, no weight sensors, just a door sensor and a UPC reader / interceptor. Note: While this may not hold true 100% of the time, it would be well within an acceptable confidence interval over volume and would accurately account for total stock at the location and sales in given time intervals. The importance isn’t that every sale be accurate, but that heuristics over time give an accurate model that is sufficiently granular. This solution provides a lot more than the problem at hand. Not only will it give an accurate idea of bottles sold over time and possible out of stock solutions (both for coolers and locations), it could also provide information on sales of competitive products, and sales of complimentary goods, all while piggybacking off a simple technology that we know we can capitalize on. We would like to mention: There is no silver bullet. This solution is meant to be implemented in whole or in part in a variety of ways across a host of different systems and vendor / end point models. What make it viable is that it is flexible and customizable while focusing on simplicity.
How does it work?
We will integrate with the retailer’s existing POS solution to send our system (RedNet) real time data on sales and inventory levels of CocaCola products. For retailers that only have a cash register without a scanner or internet connectivity we will provide them with our scanner with embedded connectivity. In addition, for retailers with an existing POS that we have not yet provided a software integration with we have a hardware solution called the "Interceptor" that will attach to their barcode scanner and intercept all coke product UPC's and send that data to RedNet. UPC readers are simplistic enough in design that very few iterations of dongle design would cover most of the market.
There is also a mobile app called RedWatch that will be made available to retail employees and distributors. This app will be available via smart watch, iPhone, android, windows, and SMS. When a cooler runs low on stock, RedWatch will send a push notification or text message to employee(s) on duty to restock the cooler. Optionally, we may employ an open/close sensor on the cooler door, that when coupled with POS data and employee's entries into RedWatch (potential text reply functionality) will help bring a more accurate picture of restocking times (under the assumption that not all employees will remember to use RedWatch to mark a cooler as restocked).
What does it measure?
- Real time and historical sales at the POS.
- Open/close times and durations at the cooler.
- Employee data entries of when they restocked a cooler and of how much of an SKU.
Does the retailer/bottler/distributor have to do anything different to make your solution work?
Yes. The retailer, bottler, and distributor will need to grant our team of system integrators a one time access to their POS, databases, ordering systems in order to set up the automated data export to our RedNet system.
Retail stocking employees will need to use RedWatch to notify them to restock prior to an Out of Stock situation. Retailers that did not use any POS in the past will need to use our POS system or adopt another POS system that we support.
What materials does your solution require?
- Open/close door sensor with radio connectivity to base station (POS).
- Point of sale system. At a minimum a: scanner, internet connectivity (WiFi, Cellular, LAN or TEL depending on region), microprocessor.
- Servers and databases to host RedNet (public or private cloud).
Best guest on cost to implement
- Retailers without a POS will require a minimum of $10 in hardware
- Open / close sensor: $2 per cooler
- Labor cost of implementation per site: less than $100 possibly less than $50 when economies of scale are factored in
- Monthly hosting and storage costs: several thousand (less if using established CocaCola resources)
- Fixed cost for development of solution and establishment of infrastructure: less than a million
Challenges you/your team ran into
Originally wanted to set up a full AWS IOT stack that would include a raspberry pi connected to AWS IOT -> AWS Kinetic Firehose -> AWS S3 -> AWS Redshift. However, due to time constraints on this project I opted instead to set up a simplified MySql database only.
What you/your team learned
While Out of Stock seems to be the biggest problem Coca-Cola faces with their coolers there are several other related problems that are of interest which includes product purity and retail price monitoring. In addition, there are many moving parts to the supply chain that need to be considered as a whole in order to deliver the end product to the retailer when they need it.
- Perform an in-depth supply and value chain analysis.
- Identify Coke's largest retailers.
- Identify the POS systems with the largest market share of coke retailers.
- Develop a full set of requirements and goals for RedNet, RedWatch, and the system integrators.
- Develop a minimum viable product and implement it in a single test market.
- Iterate quickly to make improvements to the product.
- Identify additional test markets in different regions and implement a pilot program.
- Scale the solution globally.
- Continually integrate solutions to collect greater more accurate and greater quantities of data and better draw conclusions about sales, regional preferences, bottlenecks, etc…
- Leverage the product to integrate with other CocaCola systems to capitalize on backend synergies in order to grow profitability and gross sales.
Anything else you want to add
Depending on data sharing agreements set up with retailers/distributors/bottlers RedNet has the capability to display pricing data on CocaCola products every step of the way. In addition, RedNet could theoretically contain data on any other product contained in the retailer’s inventory that could be used to determine complimentary products sold with CocaCola.
This solution does not solve the problem of “Veggies” in the cooler, which we take to mean competitor products.
This solution has a critical assumption: that access to POS data is legal, and that retailers will provide us with an interface. To address this we again focus on the flexibility of the idea itself. Perhaps access to POS is revoked. Incentive programs with and additional UPC scanner are still appealing. Automatic restocking of a location is still appealing. Contracts can be negotiated that only give access to Coke specific sales.
Charts at https://chartio.com/kitefaster-llc/coca-cola/ updated every minute.
-Scan UPC code. (currently works for 049000004632)
-Quantity in cooler is decreased by 1. When quantity of that product reaches 5, text message (via Twilio) is sent to all users at that location to restock.
-Cooler is restocked to 10. (Possible text back, cooler sensor, or web interface integration) -Stockroom is reduced by 10. -When stockroom reaches only 10 units, an order is placed to the distributor. -Stockroom is restocked to 50.
-Business intelligence dashboard is available that shows real-time and historical orders by SKU with price. -Retailer can see sales from their location(s). -Distributor can see their sales to their retailers, how often they order. -Both retailer and distributor can get a prediction on when and in what quantity future orders will be made based on historical data. -Corporate can see a god’s eye view of all data. -Depending on data sharing agreements made with retailer we can also see sales and possibly prices of non-CocaCola products and complimentary good sold, etc.
Retail Onboarding and Sales Model
The hardest part of any project is buy-in. A sales representative explains to retailer / POS the benefits of the system in predicted increase in sales and reduction in OOS, CocaCola offered incentives, and automated reordering. Sales pitch will be tailored to what customer would value the most based on prior research. Many smaller retailers may be swayed by an upgrade to barcode scanners alone.
Rep / implementation team performs software integration with existing POS or installs interceptor / scanner. Retailer enters current stock and prices into web interface. Stock can also be tracked automatically with integration into coke distribution network.