Web analytics and Regression: put a line through that data/
January 10, 2006
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:
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.)
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.