Analytical - web analytics for blogmasters

Several months ago, I made a promise to put together a spreadsheet that bloggers could use to calculate MMAs (Multiple Moving Averages). It’s been a while but I finally have a free one for anyone interested.

I use my MMA charts to get an idea of what’s been happening, as well as what type of pageviews or ad revenue that I MIGHT experience in the near future. In other words, MMAs are trendlines, but they’ve been “averaged” out to smooth out spikes.

MMAs give you insight into patterns that your website/ weblog metrics (be it pageviews, ad clicks, revenue, CTR), etc., are experiencing, and what might be coming up. It’s hard to pick these patterns off by looking at a list of numbers. You need a graph for a better view. And because there are several “rolling averages” being calculated, this isn’t something you want to calculate by hand. Which is why I use a spreadsheet. (I could write a program, but a spreadsheet is simple and pretty much any blogger can use it.)

I use MMAs for a lot of uses. As I’ve mentioned in my (incomplete) series of articles on Moving Averages and Multiple Moving Averages, I’ve used MMAs to correctly predict stock market turns (along with other data).

At the very least, they give you a “reverse crystal ball” view of what’s been happening with your weblogs and websites. They also show you that you shouldn’t worry too much about what happens on any given day, because there are always “transients” in the metrics. Worrying about day-to-day changes might give you ulcers. It’s the medium- and long-term patterns that are more important. (Not to say they won’t make you ill too.)

So here is my MMA Multiple Moving Average spreadsheet, using the publicly-shared, web-based ZohoSheet spreadsheet format. You should NOT need an account at zohosheet.com, but you may want one. (They also have the web-based zohowriter, which I’ve been using for two days. It’s compatible with MS-Word.)

The ZohoSheet is compatible with both MS-Excel and OpenOffice, and you can export to HTML or PDF. Go to the link above and view the spreadsheet, then find the export link in the displayed page and choose your spreadsheet flavour.

You’ll see that I’ve set up the sheet from Jan 01, 06 to Dec 31, 06. (The date format is a quirk of ZohoSheet. Feel free to change, as well as the duration. Replace the data in the column that says Pgvws with your own values. They can be pageviews, ad clicks, ad revenue or hey, even stock share prices. Whatever you like.

I’ve used MMA “windows” of 7 days, 14d, 28d, and then multiples of 28 days. I’ve only shown the MMA graph up to 56 days (approx. 2 months). [The data in the sample is some of mine from last year, transposed to this year. You can see the severe short-fluctuations that get “normalized” or evened out.]

If you want to change the sliding “window” durations, feel free. But make sure you look at the cell formulas first to understand what I’m doing before you try to extrapolate. Same goes for shortening or lengthening the data collection duration.

Good luck. Feel free to ask questions here, but being currently busy, it may take me a while to respond (at least during weekdays).