Predicting monetary values using linear model

Question

I am predicting revenue using 8 predictors like units sold,units ordered, total order etc...using LM model(linear regression)

The range of revenue is 0 - 20K.

Post Outliers removal: revenue 0 - 1K. Below is the distribution

0 revenue's are those cases when customer purchased something online and then returned. I retain them as they are genuine purchase and 10% of records are having 0's.

The data is skewed so log transformation is done after replacing 0's with 1 as log can't be applied on 0. The predictors are not having outliers and they all in the range of 1 - 10. The highest correlation is 0.80 which is of units sold.

I am concerned about the prediction part and ignoring multicollinearity effect.

This is how the actual revenue (x axis) and prediction (y axis) below. Not that great!

Two major problem here

1. For typical values prediction range is very high as seen in y - axis.
2. 0 values are never predicted close to 0's.

RMSE of predicted values is 27 as compared to standard deviation of 128

On predicting 2nd time but without any transformation these is what i got below. Much better!

At least no extreme values in prediction. RMSE is as low as 4.

But one major problem! It's predicting negative values as seen in y axis as it's not log transformed.

I would like to know what should be the approach here when predicting monetary values. What sought of transformation do i need to do or what other linear model do i need to try.

Please note i need to stick to linear model.

Edit: Model summary of log transformed data

Answer 1

I agree with Stephen's comment that your 10% return rate is effecting your model greatly. Really, you are attempting to capture to different phenomena in one model: returns and revenue. To correctly model this, one option would be to use a Hierarchical model. Hierarchical models are really just multiple models that branch.

In your case, the top level model would be a logistic regression predicting whether or not a product was returned. Then, using only the data points for products not returned you would create a linear model (absolutely keeping the log transformation). So you're code would look something like this.

mod1<-lm(returned~your_variables, data=train)
mod2<-lm(log(REVENUE_EURO~your_variables)

I would also suggest using the pairs() function to look for multicolinearity. Without seeing your data I can't be sure, but I would bet good money you could easily drop several regressors.