With non-contractual customers, what method should I apply to calculate their demand for a specific time period viz. month, quarter

Question

I have customer data for 18 months in the format as follows:

CustomerID   Date    Total_Spend Spend_Cat_1 Spend_Cat_2 Spend_Cat_3 Spend_Cat_4
    1       1/1/2013     373         202           0          17         154
    1       1/18/2013    103          92          11           0           0
    1       8/2/2013     476         330         146           0           0
    1       12/12/2013   332         216         116           0           0
    2       1/1/2013     399         204         195           0           0
    2       1/13/2013    485         315           0           0         170
    3       1/1/2013     326         238           0          22          66
    4       1/1/2013     354         184         170           0           0

I have done my research towards finding a predictive model that could forecast each customer's total spend as well categorized spend (Spend_Cat_1,2,3 & 4) in each of the next 12 months or 4 quarters. The nearest I could come was to Croston's method (I am using R for the programming part) but I am not sure if my data qualifies for "Intermittent Demand". Even if I got to the correct model I am not sure how to do it as there is little help available on this topic over the internet.

If it is needed, I have around 7000 rows of 2000 different customers.

Answer 1

If you have only 7000 observations for 2000 customers, then on average you have only 3.5 observations per customer. Over 18 months, this comes to about one observation per customer per 5.1 months. This is very sparse on monthly granularity, and still intermittent on quarterly granularity.

Yes, this is intermittent data. You can use Croston's method on monthly or quarterly aggregate data. However, I am always a bit skeptical how useful Croston is, since it will forecast average demand. For instance, your first customer had a total demand of 373+103+476+332 = 1284 over the 18 months, or about 71.3 per month, so that is roughly what Croston will give you. How useful is such a forecast in the context of whatever you plan on actually doing with it? Note that this point forecast is far below any actual nonzero realization. Also note that Croston does not attempt to forecast when the nonzero demand will occur.

(The difference between Croston and the simple overall average is that Croston will try to weight recent data more heavily, but with only 3.5 observations over 18 months, it will be hard to detect any dynamics.)

If these demands reflect something like your customers stocking up, depleting their stocks, then stocking up again, then we should expect some autoregression. Right after a demand, the chance for another demand from that customer will be low. Croston will not model this, but you could certainly try to separately model inter-demand intervals and nonzero demands in a similar way, then try to forecast both the timing and the height of the peaks.

If so, then classical error measures may be misleading. Haben et al. (2014, IJF) offer an alternative error measure for peak forecasting. Be particularly careful of the MAD/MAE for intermittent series (Morlidge, 2015, Foresight and Kolassa, 2016, IJF).

Alternatively, you could take a look at MAPA (Kourentzes et al., 2014, IJF). Or go for density forecasts, e.g., by taking historical data per customer and smoothing the histogram using some kernel. Once again, you have very little data per customer, so your success will likely be limited.

In terms of your categories, you have a hierarchical problem with a very simple hierarchy: the forecasts for the four categories should add up to the total forecast. I personally am a fan of the optimal combination approach by Hyndman et al. that you will find in the hts package for R. However, this only works on the level of point forecasts (so far), and again, with your data, how much it will help is limited.