Semphonic Blogs

Using Excel for analysis and reporting

• 12/15/07 More on Analytic Reporting Models, by Gary Angel
• 12/15/07 Modeling Traffic, by Gary Angel
• 11/17/07 Omniture Excel Client – How to Deal with Bugs, by Paul Legutko

More on Analytic Reporting Models

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.

Posted by Gary Angel 12/09/07

Go to Blog Titles

Modeling Traffic: A Different Approach to Good Reporting

I’ve written before that Management Reporting is one of the most difficult tasks in all of web analytics. It’s an area where – as a company – we’ve struggled mightily. Nor is it the case that when I see other reporting styles and implementations I’m very impressed. Mostly they are worse, often much worse, than Semphonic efforts that I judge failures.

I’ve also written previously about why I think so many efforts at reporting are misguided. They tend to over-focus on identifying individual KPIs instead of describing real-world situations. And when they do describe a real-world situation, they generally lack the intelligent context to make clear to the decision-maker what’s driving change.

We’ve done some work recently that I think addresses both these concerns and has produced some reporting that is significantly more interesting.

I’m going to illustrate this work with a report on Traffic. Yes traffic.

Traffic is nothing to sneer at. Visits to a site may not be the type of KPI that you are supposed to focus on. But in my experience, it represents a crucial real-world fact about your online business opportunity. In addition, I get more questions about changes in traffic than almost any other single variable.

Suppose your report set includes a nice chart like this:

When a marketing manager sees the July to August, August to September, or September to October change, a perfectly reasonable question is generated. Why?

It’s possible that the consituents of an answer are already present in the report set.

Changes in visit traffic may well be driven by improvement or decline in various sources. If the report set also contains a report on marketing channels, the seeds of an answer might be there:

A chart like this contains a chunk of the data necessary to undertand the traffic variation shown in the 1st chart. But it certainly doesn’t make it easy to answer the question. Nor is all of the necessary data contained in this one place.

We can see, for example, that the Direct Traffic is mirroring the overall pattern – two months of decline and then a recovery. Perhaps it’s the causal cuplrit. But what’s causing the Direct Traffic to change?

To understand that, a manager might look for a different chart somewhere else in the report. Making the assumption that direct traffic is driven by offline marketing and repeat visits, the manager might look for something like this:

We can see that there was indeed some decline in visits per visitor in August. But that September actually went up instead of down. So changes in Loyalty may have been responsble for some (how much?) of the decline between July and August but can’t possibly have contributed to the decline between August and September.

Going back to the source chart, we can see that the decline from August to September looks to be biggest in Organic Search. So perhaps we would drill down into a detailed report on Search to see a comparison of August and September.

Google and Yahoo are both down - so that may be the driver for the monthly change. But it’s interesting to note that some other engines are actually up – forming a small counter-trend.

Now, we just have to explain the upward tend in October!

You can see that this is getting to be a lot of work. You can also see that it would require lots of charts and tables and fair amount of smarts to get an answer about what’s driving traffic change. That will lead to reporting bloat – and most users will wonder why they are drowning in so much apparently useless data.

To combat this, takes a very different approach. The essential idea is to build an analytic model of a concept like Traffic (or Conversion or Satisfaction or Efficiency or Engagement) and then embody that model in a report.

To do this we dump all of the relevant data for the model into a working spreadsheet. Then, we create a (fairly complicated) Excel Macro that processes the data using the analytic model. It then populates the report spreadsheet with the core numbers being tracked along with the analysis of the key causal factors and counter-trends.

The beauty of this approach is two-fold. First, a good deal of analytic complexity and intelligence can be built into the model. This can prevent a decision-maker from misunderstanding or missing the contribution of key factors (like visitor loyalty) on traffic. Second, it allows the report to encapsulate all of the key information in one simple presentation that provides actual immediate and well thought-out answers to the inevitable questions.

Here’s an example of the output for the Traffic Model from this situation:

The model captures not just the trend in traffic; it immediately identifies the factors that drove the trend, the extent to which they contributed, and any counter-trends that were also in-place. To get the same intelligence from any non-model based report set would take big chunk of work and a fair amount of knowledge about web analytics. The two things that a good report set is supposed to make unnecessary!

The more I work on reporting, the more I think that the distinction between analysis and reporting is meaningless. Because unless you embody good analysis in your reporting, you’ll never deliver a good report set.

Posted by Gary Angel 12/02/07

Go to Blog Titles

Omniture Excel Client – How to Deal with Bugs

The Omniture Excel Client Tool is indispensable for reporting purposes. It is also as sensitive as Middle East politics. I have very little idea of how it technically works, but over the years I’ve found some practices which help make the tool more reliable.

First, how do you know the tool is broken? Here are some things I’ve seen:

1. Data Blocks return all “0’s” where last week there was real data.
2. Line-items you’ve defined come up now as “Undefined” or “Unspecified”.
3. Instead of the data-block, you get an Excel version of the Omniture Login screen.
4. Your date-range is nonsensical (such as dates in the 1990’s).
5. You get a “No Data Returned” message when you know data should exist.
6. On “refresh”, the data-block disappears and nothing happens.
7. The structure of the Data Block changes (e.g. dates are now left-right instead of top-down)
8. You get error messages telling you that an Excel-referenced value is missing.

If any of these happens, there’s a bug. Here are some tips to try to fix it:

1. Logout, then login again.
2. If you’re trying “Refresh All”, go to each worksheet and use “Refresh Worksheet” instead (Refresh All is notoriously unreliable).
3. Quit Excel and re-enter, opening only the Omniture-fed document (other Excel Documents might be confusing the Excel Client).
4. Do a “Edit Data Block”, don’t change anything, and refresh the request manually.
5. Make sure there are no other people logged on as you in either Excel Client or SiteCatalyst (I don’t think this affects SiteCatalyst, but I know it affects Excel Client).
6. If you’ve renamed the document recently, go back to the earlier version and refresh that one instead (this is a tip for symptom #8 above).
7. Wait an hour or two, use a different computer, change your IP address, or use a proxy server (I think bugs are specific to different Omniture servers).

To avoid bugs, here are some tips:

1. Keep all Omniture Data Blocks in one worksheet, with no formatting, and have forward facing, pretty tables in other tabs.
2. Never insert, delete, or move columns, rows, or cells in the worksheet with SiteCatalyst Data Blocks.
3. Excel-referenced values should be on the same worksheet as the Data Blocks, and should be kept to a minimum.
4. Don’t move around in Excel when you’re refreshing a Worksheet.
5. When designing the Data Blocks, decide whether to have data going top-down or left-right, and keep that consistent.
6. Use the same login when creating multiple Data Blocks. Also use the same Excel version (2003 or 2007) when constructing the document.
7. The tab with the Data Blocks should be called something very simple – no caps, spaces, or strange characters.

If anyone has other tips or tricks, please let me know. The last thing I ever want to do is call Omniture and open a ticket.

Posted by Paul Legutko 11/17/07

Go to Blog Titles