Generating smart sales predictions from POS data to optimise inventory.
2019-07-21
Last school year, I worked as a barista at a zero consumer waste cafe. Despite being actively committed to environmental sustainability, even this cafe was plagued by a problem affecting food businesses everywhere: food waste. Every time I threw out out moldy bagels, I wished that we could know the future and forecast the daily demand.
Large cafe chains and grocery stores can track and manage every single thing that happens in their business, allowing them to reduce food waste and therefore maximise profits. Smaller businesses like local cafes and restaurants don't have the same opportunity. Without a team of data scientists at their disposal, how are they supposed to use tools meant for warehouses?
I built my demand forecast project to meet this need. It predicts future sales by using statistical methods and machine learning on point-of-sales data, taking advantage of the data that small businesses do have to guide food purchasing and production decisions. As a proof-of-concept (POC), I analysed 5 years of real-world data from a real-life cafe and designed models to accurately predict the underlying patterns in their sales. Because whether it's food or data, why throw it away when we can use it for good?
Technically this project was completed as part of an AI accelerator program, though I was independently motivated to solve this problem regardless. I'm choosing not to mention the program publicly because I unfortunately found it unhelpful and distracting.
I succeeded in cleaning and analysing my dataset. I wrote my code such that it can be used on other datasets from the same point-of-sales (POS) system (Square). I also designed an interactive wireframe that demonstrated my vision for the future user-facing application. My project won the top judged prize for the program, based on my pitch and poster.
For the POC, I gathered data from the same cafe I'd worked at.
They agreed to share their data in exchange for any insights I might glean through this project.
The data had to be exported in pieces from the POS system, and I later read every CSV file into the same pandas dataframe.
I kept all data, both raw and processed, in a folder called data in the root project folder, and ignored it with the .gitignore file.
The dataset consisted of more than 600,000 sales over a 5-year period.
The cafe was on-campus and opened according to the academic calendar of the University, adding an extra challenge to prediction. There were large gaps in the time series that corresponded to the dates that the University was closed. However, these dates were not consistent year to year because the academic calendar doesn't align with the standard calendar. This means that patterns existed according to both the time of the calendar year and the time of the school year.
I extensively explored the data before doing anything with it in order to see what I was dealing with. I made great use of visualisation, creating several graphs with Excel and Python (matplotlib). I also used pivot tables and functions in Excel and Python (pandas + numpy) to quantify and rearrange the data. Exploring the data revealed many fascinating insights about the business, including practices which were most effective and areas where revenue was being lost, which I communicated to the managers. In addition, it helped me understand how to process the data and how to approach the analysis phase.
The sample dataset contained 230 "unique" items. Of course, many of these items were duplicates, or effectively so. Every time an item was relabled (including punctuation and capitalisation changes) or re-categorised, it became a "new" item in the dataset.
Not all duplicates were easy to determine. If two sandwiches have the same ingredients with only one difference, are they the same sandwich? Does it matter what the ingredient is? What about the time periods during which each sandwich was sold, and whether or not these overlap? How can I determine the ingredients from just the item label anyway? What about the fact that the same item can change over time, without changing labels?
Many of the 230 items were short-term promotional items with non-descript labels that told me nothing about the type of item they were. When even the current managers didn't know what some of the numerous items were, I had to scour the cafe's social media posts to find out... But even that was hard, because promotional items weren't always announced, and certainly not always in the text portion of a post.
Even if I knew that a certain promotional item was a latte, how would I categorise it? If I categorised it as a promotional drink, would that inaccurately lower latte sales? If I categorised it as a latte, would that inaccurately increase latte sales? If that promotional latte didn't exist, how many of those sales would have gone to lattes, and how many would have gone to other items?
Despite the huge number of item labels, in many other cases I didn't have enough labels to tell apart items that should be considered unique. Consider a simplified version of what I called the "soup-salad-mac dilemma". The cafe sells soup, salad, and mac and cheese. At first, soup and salad are the same price, and mac and cheese is more expensive. Because the cafe only cares about ringing in orders, and not collecting clean data, it creates two labels: "soup/salad" and "mac and cheese". Later on, soup becomes more expensive, so they can't keep soup and salad in the same button. Soup and mac and cheese are served from the same warming pot, so out of a desire for simplicity, they make both items the same price. Then, they rename the two existing buttons to "soup" and "salad", and ring up mac and cheese as soup. Is it possible to tell the difference between soup, salad, and mac and cheese? If I group all three items together, am I making assumptions about the relative demands of each item? How do I understand the demands of soup and mac and cheese when I don't know which item was in the pot on any given day?
Even within specific items, there may be valuable information that isn't present in the dataset. Consider bagels. If the cafe sells 6 different bagels that can each come with 6 different cream cheeses, 4 additional spreads, and 4 different toppings, it might be useful to know the relative demands of each item so they could figure out how much to purchase, and whether any options should be cut from the menu. Unfortunately, this is only possible if this item data is specified in buttons on the POS system. If all I know is "bagel", there's nothing I can do.
Finally, the fact that I only have sales data and not purchase/production data (remember, my project is targeted towards places that lack purchase/production data) means that I miss a lot of context about the data. If I'm using sales as a proxy for demand, how do I know for sure that the number of items sold on a given day is the number that were demanded? What if there was more demand than the available stock, and the item ran out before demand could be entirely filled? What if an item sells more than it should because people buy it as a second choice after a different item they'd prefer runs out?
You might think that a solution is to consider something "out of stock" once sales become zero... But how do I know if an item is out of stock or if people simply stop purchasing it? What about items that are out of stock in the morning, but are re-stocked whenever the daily deliveries for those items come in? What if an item is in stock in the morning, goes out of stock, and then becomes re-stocked later in the day? What if a staff member thinks an item is out of stock, but later a different staff member finds more in the back room? What if a variant of an item (like a popular bagel spread) goes out of stock but is later re-stocked, and the rate of purchase of the main item ("bagel") changes accordingly, but at no point reaches zero?
The data cleaning process required me to make a lot of decisions about how to deal with messy, incomplete, and contradictory data.
I consolidated sales by day after processing because sales don't occur at consistent periods of time. Instead of having a continuous time series, I had a series of timestamped events that I needed to discretise. Essentially, I approximated the original series by binning it. I also consolidated sales by hour for visualisation purposes, but I found that it was too spiky for good prediction results. Considering that cafes generally only purchase items and ingredients on a daily basis, I decided that daily binning made the most sense.
I spent a lot of time researching different machine learning models for time series analysis problems. My problem is specifically a multivariate time series analysis problem because at each timestamp (day or hour) there are corresponding values for multiple categories (items). I sought to understand what's been done before and the different situations each model is best suited for. I developed a few baseline models based off the cafe's current practices as well as what a data-minded human would be able to accomplish. I also included naive prediction and linear regression models as baselines in order to check that the more complex models would actually perform significantly better than these simple ones.
In the end, I settled on SARIMAX and XGBoost as models best suited to my problem. I also decided to try Google's AutoML and Facebook's Prophet as out-of-the-box solutions. Prophet was created to deal with what was essentially my problem but on Facebook's scale, so I was optimistic about it. I didn't think that RNNs would be able to hold enough information in their "memory" to perform well, but I also decided to implement an LSTM out of curiosity. In future, I would be interested in performing tabular deep learning. This would require more data processing to convert the categorical variables into embeddings and to represent the cyclical nature of time.
As a human, I could be highly confident that there would be seasonality throughout the dataset. Therefore, I decided to supplement the dataset with features that would help the models understand this effect. Because I could expect that Mondays are probably similar to Mondays and so on, I added a feature for the day of the week. Because people's purchasing patterns are often affected by the weather, I added a feature for the average temperature that day. Again, the particular dataset I was looking at was from a cafe that was open 13 weeks twice a year according to the academic calendar, so I added two more features to deal with this additional effect. These were a feature for the week of the year, starting with the first week of school each year instead of the 1st of January, and a feature for whether it was Fall or Winter term. All these features proved helpful, according to my analysis.
First, we standardised the data. In order to keep things simple for the POC, I decided to focus on the one major item which was both frequently sold and frequently wasted: bagels. I assigned the data from the final term of the most recent year to be the training set, and all prior data to be the test set. From there it was pretty straightforward.
I calculated the root-mean-square error (RMSE) for every model and graphed each model's predictions compared to the ground truth (real values). It was a good idea to graph the results because a few models that had low RMSEs were actually wildly inaccurate. This is because the RMSE measures the overall sum of differences from the ground truth, but what I really wanted was a model that followed the shape of the ground truth closely. I wanted a model that would predict sudden increases and decreases even if it happened to be off by a day or two, rather than a model that "played it safe". I also considered two error measures of my own: food waste (overprediction) and lost sales (underprediction).
Ultimately, Prophet was the superior model. It managed to predict the true values very closely and quickly picked up on upswings and downswings in the sale values. The AutoML model (I don't know the type of model that was chosen because Google hides that information) was also quite good. It overpredicted less than Prophet, but it had a significant underprediction problem. Considering that food does not immediately go bad at the end of a single day, and therefore minor overprediction will not necessarily result in food waste, I decided that Prophet had better overall performance. Additionally, Prophet is free and open-source, while AutoML is paid and opaque. However, I recognise that my results could be specific to the train/test data that was used, and a more thorough analysis would have to look at the results for all item types well into the future.
The LSTM was by far the worst-perfoming model, simply outputting the average value every single day — a truly flat line. SARIMAX performed almost as badly, predicting an only slightly wavering line. Both models were effectively ignoring the data because they were underfitting; they had high bias and low variance. This leads me to suspect that I'd need to spend more time tuning the hyperparameters for both models to achieve useful results. However, if my eventual goal for this project is to build something that can work out-of-the-box for any cleaned dataset, I'll probably stick with Prophet for now.