More on Analytic Reporting Models

[Don’t forget, our first Ask Semphonic is this Tuesday, December 11th. We’ve gotten lots of great questions around the theme – the Omniture acquisition of Visual Sciences. I think the discussion will be quite interesting and I looking forward to seeing how the Ask Semphonic concept works! Join us if you can – click here to register.]

I got quite a bit of feedback to the last post – including several requests to delve a little deeper into the actual process of creating analysis-driven reports.

Here’s a sample of the report I was discussing – a report that embodies the goal of building analysis into reporting to make management reports more useful:

This particular report is a traffic model. Its primary visualization is a 3 month trend of site visits. That simple visualization occupies the middle of the report. At the top report is the actual % traffic change month over month. Just below that is the real heart of the report. Under a heading of “Factors Driving Decrease” or “Factors Driving Increase” are the major elements that explain the reported change in site traffic. Along with each element is a bar chart indicating the percentage of the change for which this single factor appears to be responsible. At the bottom of the report are the factors that are driving change counter to the main trend.

The purpose is to help a decision-maker understand what factors are driving changes in site traffic – with the understanding that from month to month many different types of factors might come into play. A static report that simply isolated one common set of factors (like changes in source) might easily miss or mis-represent the reasons behind a change.

I chose this particular report for two reasons. First, questions about changes in traffic are surprisingly common. We get asked why traffic changed at least as often as any other single question. And while some experts argue that decision-makers shouldn’t care about issues like this, I see that, in fact, they do. I even believe they are right to.

I also chose this particular model because it illustrates a key difference between analysis-based reporting and traditional models. Many traditional report sets actually capture all of the data necessary to produce this report. But they make the decision-maker do all the work – both in understanding all the reports to look at to understand the change and in actually isolating the key drivers of change in those reports.

In my experience, decision-makers have a hard time with both of these tasks. That’s why we often get asked questions about traffic changes – even when the answers seem to us to be available in the management report set.

So how do you go about building a report like this?

The first step is analysis. That’s why we call it analytics-based reporting. You have to start with a comprehensive understanding of what drives traffic variation. For most sites, key factors will include things like: sourcing changes (including new direct visitors, search traffic both paid and organic, campaign traffic and link traffic), changes in site engagement (particularly in driving repeat visits), seasonal changes, and even random variation.

One of the most important responsibilities of a good analytic-based report is to make sure the decision-maker understands whether a change is worth worrying about at all. Not every variation matters. So a good model needs to include a measure of “expected variation” in the overall measure – and alert the decision-maker when variation exceeds or is short of that threshold!

Understanding components by seasonality is a really just a sub-class of understanding expected variation. Where normal seasonal patterns are driving change, the actual causal factors behind the change may or may not be interesting. But the decision-maker should know when the variation is largely driven by seasonal factors.

Next, you need to understand the potential drivers of change. Traffic, expressed as visits, is necessarily driven by sourcing mechanisms. But when traffic changes are caused by variation in direct sourcing, it’s important to understand whether the increase in visits is driven by improved site loyalty or by increases in new visitors. In many cases, we prefer to remove ALL repeat visitors from the Source portion of the analysis.

It’s also important to insure that the model capture the important levels of analysis. For example, if a company is driving direct traffic with mass media, the analysis may need to drive down to visitor geography to understand how changes in traffic are being caused by marketing in specific DMAs. For other sites, that level would be consistently useless.

Once you’ve built a working model (in this case for Traffic), you need to translate that into an Excel Macro or .NET Excel project (assuming you want to report in Excel). The above model is Excel and was built using a VBA Script.

The VBA scripting language isn’t the world’s best. However, it provides such a high-level of integration with Excel that it’s often the easiest choice for this type of implementation.

The first step is to create an Excel worksheet that grabs all of the necessary data. Typically, this involves a number of different requests to the web analytics solution. You can do this in most tools using either vendor or 3rd Party Excel integration tools.

Once you have the data organized in consistent data blocks within a worksheet, you have to write the program that will analyze the data and populate the presentation worksheet (shown above). Typically, this means analyzing each factor identified in the model separately. In each case, you’ll identify the actual measured change in the factor. Then, the program should choose the factors in the model that explain the most change. These should be populated in the presentation worksheet. The bar graphs that show the percent of explained change are simply a conditional formatting feature available in Excel 2007.

Identifying counter-trends is almost identical. You simply look for factors that trended against the overall direction of change – isolating the ones that drove the largest counter-movements. Once you’ve got them, you populate the counter-trends elements in the presentation worksheet.

In a fully automated environment, the program can be triggered whenever the data blocks refresh. In manual setups, the program and the data blocks can be tied to a button or simply RUN from the macro menu.

Analytics-based reporting isn’t just applicable to traffic models. It can be used to tackle reporting on almost any important area of web site measurement – from conversion rates to changes in revenue to shifts in engagement or even total site value. The integration of an analytics model into the reporting consistently goes a long way to solving what I have long argued is the biggest problem in analytics reporting – the mistaken belief that reporting is about specific KPIs. I’ve argued elsewhere and often that no single KPI is actionable, and that the job of a report set is to provide the decision-maker with all of the information necessary to understand some aspect of the business and act intelligently with respect to it. Analytics-based reporting goes a very long way toward achieving exactly that.

Comments

[Alex]

Hey Gary,

Great post. What are your favorite resources discussing VBA scripts?

Do you integrate other contextual data--changes in budgets, trends from Google Trends, etc.?

Cheers,
-Alex

[Tim Madel]

I attended your presentation in DC earlier this year and found the information extremely provocative. You have inspired me to improve our reporting!!!