Tuesday, April 26, 2016

A Simple Illustration Of The Powerful Regression Tool In Excel

Regression is a statistical way of finding the relationship between a set of variables (inputs) and a particular variable you want to predict/compute.

Maybe for instance you are a regional sales manager for one of the telecommunications company. It has been extremely hard to grow subscribers and despite the huge spend on advertising, the subscribers count has been flat. So you decide to focus more on growing customer share (share of wallet) rather than kill yourself over the market share that isn't improving.

You got the business intelligence unit to give you historic data of subscribers -- both the ones still on your network and those who have left (churned). So yesterday, you got the data. You looked through it and noticed that there was a pattern everyone who left (churned) exhibited. And you also noticed that there were a set of subscribers who generated a high average revenue per user (ARPU) for the company.

What do you do with the insights?

Simple. With regression you will have a way of identifying customers who are most likely going to churn. And you won't stop there, you will also be able to identify customers with the same profile as those generating high ARPU. And you know what that will translate to business-wise? 

You can do targeted promotional campaign to those guys who are about to churn. It's an excellent customer retention strategy, focusing on the people who aren't happy and using your learning from them to ensure others won't become unhappy too. 

Then you also do targeted campaigns to people spending less than what their profiles predict they should spend. Another easy way of growing the revenue and customer share of wallet. Effective use of resources on areas there is a huge potential ROI.

So how does Regression work?

First you need to enable it. Go to File, Options, Add-Ins, Excel Addins and enable Analysis ToolPak.

Then under Data Menu, you can now access it.

So I have set up a simple example case.

I don't know the relationship between the area of a circle and its radius. I want Regression to figure out for me what that relationship is, so I can be able to predict/compute the area of any circle I have its radius.

See the result below.

The most important parts of a regression analysis results are the ones I highlighted in yellow. The Adjusted R Square indicates how fitting the model is. The coefficients are used to build the relationship equation. P-value should be less than 0.05. 

Hopefully, it now makes sense.