As promised, here are some tips and tricks for building Excel based web analytics reports. In the future, I will actually use paragraphs in my blogging, but until then you'll get my thoughts in numbered form.
All Tools
1. Build data requests based on other data requests. This gives you the ability to always get visits, visitors, pages to, etc. for the top pages, no matter what the top pages happen to be. It can also give you complete flexibility over reporting date range.
2. Anything that can be referenced in a cell should be referenced. Referencing will make it possible to copy and paste the same request (if your tool has this feature) and get back different data. Even if you're not trying to build a copy and pastable report, referencing everything will keep your reporting cleaner, easier to change/update and be much faster to trouble shoot (Disclaimer: I stick to this when building reports within HBX’s Report Builder, but not necessarily all of the all tools. I have never seen HBX lose a cell reference, but other tools like Excel Client, are less reliable and can lose or misuse references.)
3. Excel Vlookups are your friend…for several reasons.
a. When pulling data for specific site elements (pages, links, etc.) you can either build a request that pulls data for only that element or you can pull a bunch elements all at once. For tools that allow you to copy and paste requests, it is often better to get individual element data with individual requests, but for those where that is impossible the vlookup is usually your fastest bet. It is almost always the case when pulling a large block of site elements, like pages, that their order will change from time period to time period. Usually you can be pretty confident that your order confirmation will be amongst the top 200 most viewed pages each month, but you can bet the farm it won’t be rank 67 every month. The solution for this is to insert a vlookup to seek out the order confirmation page data.
b. If you would like to show how a data element has changed over time its usually helpful if those elements line up. Since vlookups need fixed values to look up against, you’ll have to decide which period you will use to fix the values. Sometimes it is best to use the most recent period of data as your fixed vlookup values so that the most recent period will be in ascending order. However, there are also instances where building a request to span the entirety of the time period you're looking at be at a more granular level and use the names within that as your fixed values. This is your best bet. If you're looking at campaigns, referrers or anything that might have only had significant volume during a sliver of the time you're looking at then it's best to use a vlooup list connecting to data that spans the length of time your seeing.
4. Dates and date ranges visible to viewers of reports should all be requests or references of requests. Doing this will make it impossible to forget to update the dates in a report and give you an extra check to ensure your report has refreshed.
HBX Report Builder
1. As much as you can build cookie cutter reports. Cutting and pasting ten requests five times is much easier than building fifty individual requests. There are many strategies behind doing this.
a. Report Builder lets you edit multiple requests at once. Your limited to changing the report date range, granularity or account – but within those limitations you can still do a lot. It quickly converts a generic report for one account to a report for an entirely different account or makes a weekly report a monthly report nearly instantly.
b. Anything that can be referenced in a cell should be referenced (aside from the mass edit possibilities mentioned above). Referencing will make it possible to copy and paste the same request and get back different data. Even if you're not trying to build a cookie cutter style report referencing everything, it will keep your reporting cleaner, easier to change/update and be much faster to trouble shoot (Note: this tip is under the HBX section and not all of the tools sections because I have never seen HBX lose a cell reference, but other tools like Excel Client, have had past bugs where do not properly refresh
c. References can either be absolute or relative, which works perfect when your referencing one cell but gets dicey when your referencing two cells, one absolute and one relative. When that is the case the trick is to make both relative. To make both relative you can add in extra rows or columns, copy the absolute values into them and then hide them.
d. HBX tends to lose your active segments within requests when you pick it from the drop down list. It cannot lose the segment if you write it into an Excel cell and reference it. Even if you don’t reference anything else, always reference your active segments. It will save you a ton of headaches.
Omniture’s Excel Client
1. When building two or more similar data blocks do not press “Done” as soon as you have pressed “Insert Now.” Once you have inserted your data block you can go back a step, make the necessary changes and then insert another new block (An easy one, yes, but I didn’t want to leave Omniture out).
Nice stuff. I'm a heavy user of excel for our WA. Vlookups are your friend.
One thing I like to use in conjunction with vlookups are drop down lists for dates. This allows users of the report to choose the specific dates they want.
Posted by: Rich | October 11, 2007 at 03:44 PM