I would like to apply one or more data mining techniques to a dataset, in order to see the effect advertising has on sales.

I am working from this dataset. It has 36 consecutive entries of monthly data for both sales and advertising - it's very small.

I exported the dataset to a ".csv". I deleted the date column, because I will use R's ts (time series object). The ".csv" now looks like this:

..., ..., ...

The example coded below works. However, I had to split the matrix into two lists, because of the HoltWinters() function. I would prefer to analyse Advertising and Sales together at the latter stages. What other data mining techniques may be more beneficial?

data <- read.csv("./advertising_sales.csv", header=TRUE)
data_ts <- ts(data, start = c(2011,1), frequency = 12)
print(data_ts) # to check data has been correctly added

> Jan 2012    13        17.3    21
+ Feb 2012    14        25.3    29  
+ ...
+ Nov 2013    35        23.4    17
+ Dec 2013    36        16.4     1

data_ts_ad <- data_ts[,1] #assign advertising as list, for HoltWinters
data_ts_sa <- data_ts[,2] # assign sales as list, for HoltWinters

#do HoltWinters for advertising
data_ts_ad.hw <- HoltWinters(data_ts_ad)

>           Jan      Feb      Mar      Apr      May      Jun      Jul      Aug
+ 2014 18.52852 25.47521 27.16683 36.41340 38.14678 33.04452 33.22488 32.12758
+ 2014 32.58964

lines(predict(data_ts_ad.hw, n.ahead=24), col=2)

#do HoltWinters for sales
data_ts_sa.hw <- HoltWinters(data_ts_sa)

>          Jan      Feb      Mar      Apr      May      Jun      Jul      Aug
+ 2014 11.05723 23.27877 50.06859 57.22696 61.50669 26.35195 62.26159 70.83347
+ 2014 23.18957

lines(predict(data_ts_sa.hw, n.ahead=24), col=2)

I recently came across a book called R and data mining: Examples and Case Studies by Yanchang Zhao. It has excellent worked examples and this is where I have found inspiration. However, I can't get my small brain to think which techniques can be applied to this dataset.

I am new to R, so please try dumb-down your answers slightly.

EDIT: Output of data_ts is given below.


structure(c(12, 20.5, 21, 15.5, 15.3, 23.5, 24.5, 21.3, 23.5, 
28, 24, 15.5, 17.3, 25.3, 25, 36.5, 36.5, 29.6, 30.5, 28, 26, 
21.5, 19.7, 19, 16, 20.7, 26.5, 30.6, 32.3, 29.5, 28.3, 31.3, 
32.2, 26.4, 23.4, 16.4, 15, 16, 18, 27, 21, 49, 21, 22, 28, 36, 
40, 3, 21, 29, 62, 65, 46, 44, 33, 62, 22, 12, 24, 3, 5, 14, 
36, 40, 49, 7, 52, 65, 17, 5, 17, 1), .Dim = c(36L, 2L), .Dimnames = list(
    NULL, c("Advertising", "Sales")), .Tsp = c(2006, 2008.91666666667, 
12), class = c("mts", "ts", "matrix"))
  • 2
    $\begingroup$ Using an ARIMA model, sales does a remarkably good job of predicting average advertising during the current and next month. This suggests a somewhat contrarian approach of investigating the procedures used by the marketing department to allocate advertising resources: they might simply be following sales trends rather than leading them. If that's the case, your analysis of the data will be completely different (or even unnecessary), yet this insight alone (if it turns out to be correct) could have a substantial positive effect on the business. Isn't that the whole point of data mining? $\endgroup$
    – whuber
    Apr 13 '14 at 17:13

Given that you have a time series, with possible influences of trend and seasonality on sales, I recommend that you look for time series techniques that can handle causal effects such as advertising. This thread should be a good starting point, although your focus appears not to be forecasting.

Try something like this:

> library(forecast)
> model <- auto.arima(data_ts[,"Sales"],xreg=data_ts[,"Advertising"])

This will build an ARIMAX model for sales, with advertising as an external variable. You can then do summary(model) to see, e.g., parameter estimates.

> summary(model)
Series: data_ts[, "Sales"] 

      data_ts[, "Advertising"]
s.e.                    0.6574

sigma^2 estimated as 575.3:  log likelihood=-51
AIC=106   AICc=106.57   BIC=108.35

Training set error measures:
                    ME     RMSE      MAE       MPE     MAPE      MASE
Training set -2.821585 13.84857 9.039446 -40.91741 64.68516 0.5506261
Training set 0.003027406

We see that ARIMAX believes that each unit of advertising increases sales by 1.64. You can plot:


If you have future values data_ts_ad_future for your advertising, you can forecast and plot point forecasts and prediction intervals:

data_ts_ad_future <- ts(sample(data_ts[,"Advertising"],12,replace=TRUE),
fcst <- forecast(model,xreg=data_ts_ad_future)

time series

  • $\begingroup$ Thanks Stephan. I am using your advice and will see if I come right. As requested, I have included the output of dput(data_ts). Would you mind giving it a try now that you know what data_ts looks like? $\endgroup$ Apr 11 '14 at 8:15
  • $\begingroup$ Thx. I adapted my answer to your data. $\endgroup$ Apr 11 '14 at 8:29
  • 2
    $\begingroup$ It appears that advertising lags sales by about a month. A plausible model is that advertising is reacting to sales rather than (directly) driving it. $\endgroup$
    – whuber
    Apr 13 '14 at 16:17

Keeping models as simple as possible(but not too simple) is very important. There is absolutely no proof that one should incorporate seasonal differencing into a reasonable model for your data. Some analysts believe that complicated models will yield higher consulting fees. Differencing is a form of a transformation(complication) and like other transformations (drugs/alcohol) can have consequences. Early references (before Intervention Detection ala http://www.unc.edu/~jbhill/tsay.pdf) not being aware of the need for seasonal pulses often incorporated seasonal AR or seasonal differencing when a few seasonal indicators night be sufficient. The ACF of the original series is devoid of any need for seasonal differencing. The acf of lag 12 is induced by a few months have year-to-year similarities BUT this is atypical as only three months exhibit similarities (March +22, April +12 and August +35). enter image description here . A useful model which includes 3 seasonal pulses and 3 pulse effects (one-time only) is as follows. enter image description here . The acf of the residuals suggests sufficiency enter image description here which is visually supported by a plot of the residuals enter image description here . The actual/fit and forecast plot is as follows enter image description here which provides forecasts that are much more pleasing to my eye than others presented here. Note that my forecasts for Y are based upon the expected X for next year using the most recent year's values as a typical baseline. Different forecasts for X will translate into different forecasts for Y. enter image description here . Notice that the forecast pattern for AUTOBOX and auto.arima are "somewhat similar but have a different level". Also note the "false forecast" of a high July next year from auto.arima as it believes the July 1992 value as the basis rather than challenging the 1992/7 value as being exceptional as AUTOBOX does. AUTOBOX senses that July 1992 is significantly higher than expectations (52 versus 21 and 33 for prior two years) thus contains an an "outlier" by a magnitude of +22.8623 . Adjusting that July value by subtracting 22.8623 yields a forecast that is not flawed by the unusual. In contrast the values for August are 22 and for the last two years a confirming 62 and 65 thus the estimated August effect is a plus 35.698. In the absence of an ARIMA effect the adjustment for August would have been [(62+65)/2] -22 or 41.5 .

Finally software availability often limits what some researchers can do or even know about what can be done as they personally don't have access to innovative methods. The advantage of Stack_Exchange is the free openness and exchange of ideas and approaches.

The example comes from the 1982 book by Abraham and Ledolter. http://tinyurl.com/mxurcxy See page 70 where they analyze this time series. They find that there are lag effects of advertising while auto.arima does not. Notice that AUTOBOX includes an AR(1) structure for the noise which translates to a lag structure on both Y and X thus generally supporting the textbook solution of lagged dependence. The problem with auto.arima is that if you don't treat the anomalies the error variance is enlarged thus the necessary AR structure is missed due to the downward bias in the acf as the acf is partially based on the (inflated) error variance.

To answer Whuber's question I present here exactly how the forecast is made for time period 37 .enter image description here

  • $\begingroup$ I didn't catch where your analysis addresses the stated objective of assessing the effect of advertising on sales. Would you mind pointing out what part of your results bears on that question? $\endgroup$
    – whuber
    Apr 13 '14 at 16:19
  • $\begingroup$ @whuber The equation presents 1.5919 as the instantantaneous positive response of sales to ad while (1.5919*.391=.6) reflects the decrement coefficient due to the previous ad value $\endgroup$
    – IrishStat
    Apr 13 '14 at 17:31
  • 1
    $\begingroup$ Thank you! Am I correct in counting 16 parameters fitted in your model? I ask this because you emphasize simplicity at the outset. This indicates I must be misinterpreting the output, because a 16-parameter model for 36 data values would be far from simple. Yet it is otherwise unclear what the extra 12 coefficients (for I~S000020df etc.) mean. $\endgroup$
    – whuber
    Apr 13 '14 at 22:43
  • 1
    $\begingroup$ The equation lists 9 estimated coefficients : 1 for a constant ; 1 for the adv effect; 1 for the memory effect (ARIMA) ; 3 seasonal dummies reflecting unique March/April/Aug effects AND 3 adjustments for unusual values at periods 31,30 and 6. The actual forecast equation just uses 3 coefficients to predict period 37 : the constant , the adv effect and the memory effect N.B. The memory effect is multiplied by the adv effect to create a pseudo-coefficient for the lag of X (adv). $\endgroup$
    – IrishStat
    Apr 14 '14 at 0:57
  • $\begingroup$ The I~P for Pulse coefficients don't come into play in this model's forecast. They are listed for completeness sake. $\endgroup$
    – IrishStat
    Apr 14 '14 at 1:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.