Developing an appropriate time series model to predict sales based on past month record

I have been operating an online business for two years in a row now, so I have my monthly sales data for about two years. My business for every month is certainly affected by seasonal swing ( performs better at Christmas, etc), and probably some other factors that I am not aware of.

In order to predict future sales better, and in order to gauge the effectiveness of my sales campaign, or the impact of new competitors, I want to be able to develop an appropriate time series model to extrapolate my current sales data into future. This is so that when I compare the result of my prediction with the actual result, I can quantitatively test the effectiveness of my sales campaign, or the impact of competitors.

My question is, given that I have 2 years worth of sales data, is there anyway I can formulate a predictive time-series model for this?

Note: I am interested more in the background concepts and theories, rather than the black box tools. Speaking of tools, I have mathematica, matlab, R, Excel, Google Spreadsheet.... you name it.

• What software do you use? Apr 7 '14 at 6:39
• @DimitriyV.Masterov, I have Matlab/R/Excel/Mathematica... you name it. Actually I am more interested in the concepts rather than writing the actual code itself Apr 7 '14 at 7:42

Yes, there are ways to do this. People make their living doing stuff like this ;-)

You are looking for causal forecasting. Look at this free online textbook on forecasting to learn about forecasting methodology.

You have two key issues on your hands that you need to deal with: seasonality (or more generally, time series structure, possibly with autoregression) on the one hand, and causal effects like promotions on the other hand. Chapter 8 in the textbook above deals with the time series stuff in the context of ARIMA, while Chapter 5 deals with causal effects.

Happily enough, it is possible to address both issues by calculating either so-called ARIMAX (the X stands for "external effects", i.e., ARIMA with external effects) models, or regressions with ARIMA errors. See Rob Hyndman's blog post on "The ARIMAX model muddle" for the difference. The auto.arima() function in the forecast R package will fit a regression with ARIMA errors. Let's walk through an example, where I take a standard dataset with strong trend and seasonality and add "promotions".

library(forecast)
AirPassengers # a built-in dataset
#      Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
# 1949 112 118 132 129 121 135 148 148 136 119 104 118
# 1950 115 126 141 135 125 149 170 170 158 133 114 140
# 1951 145 150 178 163 172 178 199 199 184 162 146 166
# 1952 171 180 193 181 183 218 230 242 209 191 172 194
# 1953 196 196 236 235 229 243 264 272 237 211 180 201
# 1954 204 188 235 227 234 264 302 293 259 229 203 229
# 1955 242 233 267 269 270 315 364 347 312 274 237 278
# 1956 284 277 317 313 318 374 413 405 355 306 271 306
# 1957 315 301 356 348 355 422 465 467 404 347 305 336
# 1958 340 318 362 348 363 435 491 505 404 359 310 337
# 1959 360 342 406 396 420 472 548 559 463 407 362 405
# 1960 417 391 419 461 472 535 622 606 508 461 390 432

set.seed(1) # for reproducibility
promos <- rep(0,length(AirPassengers))
promos[sample(seq_along(AirPassengers),10)] <- 1
promos.future <- c(0,1,0,0,1,0,0,1,0,0,1,0)
AP.with.promos <- AirPassengers
AP.with.promos[promos==1] <- AP.with.promos[promos==1]+120

model <- auto.arima(AP.with.promos,xreg=promos)
summary(model) # examine the model - you'll see the estimated promo coefficient
# Series: AP.with.promos
# ARIMA(0,1,1)(0,1,0)[12]

# Coefficients:
#           ma1    promos
#       -0.3099  122.2599
# s.e.   0.0947    2.2999

# sigma^2 estimated as 151.2:  log likelihood=-457.4
# AIC=920.79   AICc=920.98   BIC=929.42

# Training set error measures:
#                     ME     RMSE     MAE        MPE     MAPE      MASE         ACF1
# Training set 0.2682805 11.12974 8.24397 0.06139784 2.867274 0.1860814 0.0008326436

fcast <- forecast(model,xreg=promos.future,h=length(promos.future))
fcast
#          Point Forecast    Lo 80    Hi 80    Lo 95    Hi 95
# Jan 1961       447.1516 431.3951 462.9081 423.0542 471.2490
# Feb 1961       543.4115 524.2670 562.5559 514.1326 572.6904
# Mar 1961       449.1516 427.1345 471.1687 415.4793 482.8239
# Apr 1961       491.1516 466.5956 515.7076 453.5964 528.7068
# May 1961       624.4115 597.5556 651.2674 583.3389 665.4841
# Jun 1961       565.1516 536.1777 594.1255 520.8399 609.4633
# Jul 1961       652.1516 621.2044 683.0988 604.8220 699.4812
# Aug 1961       758.4115 725.6095 791.2135 708.2452 808.5778
# Sep 1961       538.1516 503.5942 572.7090 485.3006 591.0026
# Oct 1961       491.1516 454.9237 527.3795 435.7459 546.5573
# Nov 1961       542.4115 504.5869 580.2361 484.5637 600.2593
# Dec 1961       462.1516 422.7950 501.5082 401.9608 522.3424
promos.ts <- ts(c(AP.with.promos,fcast$mean), start=start(AirPassengers),frequency=frequency(AirPassengers)) promos.ts[c(promos,promos.future)==0] <- NA plot(fcast) points(promos.ts,pch=19,col="red")  The red dots are the promotions. By default, you'll get prediction intervals plotted in grey. You can feed multiple regressors into your model through the xreg parameter, which you should do if you have different types of promotions with different effects. Experiment a little. I would recommend looking at more fine-grained data than monthly if you have them, e.g., weekly. Especially of course if your promotions don't run for full months. You can do this separately by product, again especially if you promote specific products, or on whole categories. An alternative would be, given that you are more interested in concepts than code, to look at Exponential Smoothing and change it to suit your needs, by adding promotional components to the standard three level, season and trend components. You can do a lot more yourself with Exponential Smoothing than with trying to maximum likelihood estimate an ARIMAX model, but Smoothing can turn into a bit of a bookkeeping nightmare if you have multiple promotion types. • ARIMA models with covariates are discussed in Section 9 of the book: www.otexts.org/fpp/9/1 Apr 7 '14 at 10:05 • Thanks, Rob. I really need to go through the book more often... Apr 7 '14 at 10:23 • Thanks @StephanKolassa! a side question, can I get the book you mention in the above post, in mobi or epub format? Apr 7 '14 at 10:36 • @Graviton: good question. Best to ask the author(s). One of them is Rob Hyndman, who commented above. Apr 7 '14 at 11:11 • @Graviton. Working on it. See robjhyndman.com/hyndsight/fpp-amazon Apr 9 '14 at 3:53 first, you don't have a lot of data to play with, only 24 observations. In your case it means that you barely have a couple of parameters to estimate reliably. the most systematic way in forecasting is to come up with a data generation process (DGP). you make an assumption about what is the true process for your sales, then try to estimate its parameters. consider a pure time series model with AR(1) DGP:$x_t=\phi x_{t-1}+c$, i.e. your sales this month are weighted average of sales last month plus and a constant. you already have 3 parameters (two coefficient and an error variance), which means about 8 observations per parameter - clearly not a lot. since your sales are seasonal, we must do something about it. one way is to add multiplicative seasonality:$(1-L)(1-L^{12})x_t=c$in lag operator notation, or in expanded form:$x_t=c+\phi_1x_{t-1}+\phi_{12}x_{t-12}-\phi_1\phi_{12}x_{r-13}\$. this adds one more parameter to estimate, so you go down to 6 observations per parameter - a real stretch.

in Matlab this model is specified as arima('ARLags',1,'SARLags',12)

this is assuming that your sales are stable, i.e. generally not growing.

if you think that your sales are growing, then you have two options: random walk (RW) and a time trend.

in Matlab RW is specified with arima('D',1,'SARLags',12)

obviously, these are only examples of different DGPs. whatever you do keep in mind the number of parameters to estimate. with 24 observations your model must be very simple, 4 parameters at most (including variances).

Here is what you should do Make two graphs:

• Sales vs. Time for the entire 24 months
• Sales vs. Time with the second year plotted on top of the first year

Look at them. Annotate the dates of any special promotions, or known competitive activity. "December" is usually pretty obvious, but add a note if helps call it out.

Go ahead and fit a time series model - any model (there are hundreds). The model may give you a slightly better forecast for the next period (t+1) than your judgement. At least, it'll challenge your judgement. Beyond the next period (t+n, n>1), any time series model is crap.† So forget about quantitatively evaluating the effectiveness of sales campaigns or effects of competitors. If you compare actual sales to predictions, you'll find the predictions are crap. Predicting the future is hard, and no method changes that basic fact.

You'll find your two graphs more useful. Study those for a while, then spend the rest of your time coming up with ideas on how to increase sales - this will be a far more profitable use of your time that trying to fit a time series model.

† You have more hope if you can creating a predictive model based on leading indicators - i.e., housing sales for the prior month may be useful to predict the sales of window shades in the current month.