Time series grouping for detecting market cannibalism

I'm dealing with daily sales of products in a supermarket.

Together with the historical data I have promotions for each product and weather data. What I lack is data about stock in and stock out of products.

In particular the stock out I miss for a product $i$ at time $t$ (say Pepsi) $Y_{i_t}$ could correspond to a spike in sales for a similar product $j$ at the same time $t$ (say Coke) $Y_{j_t}$ , where $t$ denotes a day.

Basically I want to find clusters of the same kind of products so that I know how to redistribute the items over similar products, avoiding other stock outs of the similar products, what is called Market Cannibalism I don't have data on the amount of time the supermarket need to restock the items stocked out, but I hope they don't restock the items daily, since at that point I could not use the information for forecasting.

Thank you :)

• what do you mean clusters ? Are you trying to use models/coefficients to determine commonality ? An outlier in one series could coincide with an outlier in another series reflecting the cannibalism effect..To identify this you would need very informative models . – IrishStat Nov 15 '16 at 16:35
• Hi @IrishStat, I would say that I have a latent hierarchical structure of my products. For now I have been using as a distance the weighted Euclidean distance between the simple autocorrelation ( dist.ACF) or partial autocorrelation ( dist.PACF ) coefficients, and the Pearson correlation distance, through the TSclust package. – Tommaso Guerrini Nov 15 '16 at 16:42
• acf/pacf are correlations . correlations can be severe;y afftected by anomalies see stats.stackexchange.com/questions/245931/… . This post has set a personal record for me ( 4 down votes) . All without cause AFAIAC . You may have a similar need to obtain robust estimates of the acf/pacf unaffected by exceptional activity for your grouping. If you like my response to that question by all means give it an uptick as I am currently demoralized Just kidding I think I will make it through the day .. – IrishStat Nov 15 '16 at 16:50
• @IrishStat Thank you again! I'll read that and wait for other answers here before ticking this question – Tommaso Guerrini Nov 15 '16 at 16:52
• I didn't mean this question I was referring to the one I pointed to requesting advice on a robust correlation coefficient. You certainly should leave your question open as it invites help from a large audience – IrishStat Nov 15 '16 at 16:55

I'm mostly answering how and why the available data can be sufficient. This is what I understand your question to be about.

Don't worry too much, you have the most important data available. I'm not sure if you have a background in statistics and time series and have now been assigned this task or if you have a background in supply chain management. Please don't feel insulted if I explain familiar concepts to you.

You do not have data about how much stock came in at what moment. There are a couple of ways to manage stocks, one or a combination of which will probably have been used in the past:

• It could have been an economic order quantity (EOC) per SKU (which determines the restocking frequency at a given level of demand) combined with a safety stock per SKU.
• It could have been a kanban system with two containers where you order a new container as soon as you open the last one in stock. (If deliveries are in fixed intervals, not every SKU can be on its EOC. Some will typically be on a kanban system.)
• Some inventory could be managed by your suppliers. (This happens when sales for a particular product are very difficult to predict and the supplier has better demand forecasts)

Even if you do not have the past data about stock intake, you should be able to find out with which policies this supermarket operates. This tells you enough about stock intake to construct your model and get actionable recommendations from it.

Regarding stockouts, you do not crucially need data about past stockouts per SKU per day I think. You can find your clusters using data about sales per day per SKU. This data you should have, right? (I couldn't imagine a supermarket who doesn't log this data.) I don't understand why you would want to find clusters based on stockouts in the first place. Stockouts are a delayed consequence of exceptional demand, at best a proxy for measuring demand. You have the direct data of this demand (daily sales), base your clusters on that data.

• well said ..good advice .. – IrishStat Nov 15 '16 at 17:00
• Thank you! To further explain: I'm not trying to find clusters based on stockouts. I'm trying to find groups of products which are similar in the sense that being one not available the consumer usually buys the other and viceversa. How do I detect this? If I know one product was stocked out and I see rises in other similar product sales I may infer that those similar product have answered the demand for the first one. Another causal factor could be a promotion which causes up-spikes in the demand of the product promoted and down-ones in the similar products. – Tommaso Guerrini Nov 16 '16 at 8:13
• In your first example you describe substitutes. While they can be found in the way you suggest, they could also manifest through joint periods of high sales. You have the data to look in the second way, not in the first way. Your second example describes complementary products. When you buy more of both when one is on promotion, it means you use them together (not that one replaces the other). This is not the same concept. You could find complementary products through frequent item set mining. – David Ernst Nov 16 '16 at 11:01
• @user7019377 what do you mean with "they could manifest through joint periods of high sales" in reference to substitutes? If I have joint periods of high sales I would have low residuals for any of the products (if I don't expect the spike in the demand). I didn't consider whether a promotion could positively affect other products not in promotion (frequent set mining), but intuitively I would expect a negative correlation when a promotion occurs. What I'm doing right now is checking the residuals of my univariate fitted models and see whether a big residual corresponds to a compensation – Tommaso Guerrini Nov 16 '16 at 13:13
• effect in other similar products residuals.. by the way, thank you for your answers, I was able to know that there is the possibility of a daily restock for my items – Tommaso Guerrini Nov 16 '16 at 13:14