Web analytics and Regression: put a line through that data

/

I do lots of key performance indicator and dashboard work. Much of it is longitudinal (this is stat geek speak for “over time”), and customers say, “I see the little dots, what is the trend?” If you have a big gorilla package like Sitecatalyst, you can ask to see the graphs smoothed. But we are not all so lucky, so smart or so rich as to have Omniture on our
side – so I finally figured out how to do it in Excel.

Although Microsoft keeps the ability to do linear regression, even with basic Office XP, pretty well-hidden, it is trivial once you figure it out.

First, create the data, or use mine:

57
68
48
52
78
79
58
80
83
98
67
64
93

Then, highlight the data in Excel and choose the graph wizard with the little chart wizard icon that is on the top of your screen or just choose Insert>Chart. The graph you want is Scatter, like the picture on the left, and you want the subtype that has the little dots only (it will probably already be highlighted, as it is in this screenshot.)

Click Next, Next, Finish (although you can add any options you like along the way, such as a title, legend, etc.)

Now comes the moment of truth. While your graph is highlighted, look at your Excel toolbar, and you should see a new menu, Graph, probably to the right of tools. Choose Chart>Add Trendline>Linear>OK.

Excel will will do the linear regression and add a line showing the trend of your datapoints. It should look something like this the screen shot on the left.

This is somewhat of a statistics hack. After all, we don’t know how well the data fits the line, which a package like MiniTab would tell us. But it’s free if you have Excel. You can do it tonight on your computer.

Robbin
LunaMetrics

Our owner and CEO, Robbin Steif, started LunaMetrics twelve years ago. She is a graduate of Harvard College and the Harvard Business School, and has served on the Board of Directors for the Digital Analytics Association. Robbin is a winner of a BusinessWomen First award, as well as a recent Diamond Award for business leadership. You should read her letter before you decide to work with us.

Contact Us.

LunaMetrics

24 S. 18th Street, Suite 100,
Pittsburgh, PA 15203

Follow Us

1.877.220.LUNA

1.412.381.5500

getinfo@lunametrics.com

Questions?
We'll get back to you
in ONE business day.