5 tips for SiteCatalyst Report Builder

I promise that the posts about SiteCatalyst detail are going to stop in the next few months in favour of more online featured posts, but today I thought I’d do a little bit on Report builder. For those of you who weren’t aware, Report Builder was actually a HBX tool before the ‘merger’ with Omniture. Previously Omniture customers were used to using Excel Client, which isn’t quite in the same class as Report Builder.

Excel Client is one of those oddities – almost all SiteCatalyst people will be able to use it if they want to. It is, however, slightly outmoded because of the dashboard features that you have in SiteCatalyst that you didn’t in HBX. SiteCatalyst itself has much more functionality than HBX does (or did – is there anyone still left on it?), so Excel Client, as I wrote last time, is a little disappointing. With Report Builder you always felt that you could do more than you could in HBX and it was more malleable; With Excel Client you could do just about the same or less and it wasn’t malleable at all.

So Report Builder in SiteCatalyst should be brilliant, should it not? Well it is and it isn’t. There are things that you can do in report builder that you’d never have been able to do in SiteCatalyst because you can do all the stuff that you can do in Excel alongside it, plus you can have multiple requests in a worksheet. Here are my top tips:

1. Create a Lookup from one report to another

How many times have you created two reports in Excel and then used a VLookup to get the values from one into the other report? Far too often? This is where Report Builder comes into its own, because you can use your second report as a lookup from the first one.

So for example I want to create a report that shows my site sections and how many visits they get, along with the entries and the bounce rate. Simple? Not on your nelly, unless you have hundreds of segments set up.

I’ve set a custom traffic variable (prop) to be the site section though, so I can get visits for it, but I can’t get entries, single access or exits because I don’t have pathing enabled (and those variables don’t mean the same thing when it comes to props anyway). I do however have the site section as part of the name of each page.

So I create one report which shows me the top 10 site sections and the visits. I then create another report with a filter attached that looks for the first value as such, with a large number of rows, so that we capture everything.

We then add in entries and single access as metrics and choose a custom layout. We hide the page names (although the filters will still apply, we just don’t want them on the page) and make the metrics be the sub totals and apply them to the next two fields.

This now gives us two new cells which will show the total entries and single access for all of the top 1000 pages with that site section name in it (don’t forget that you can add up entries and single access because they don’t need to be deduplicated – they only occur once per visit).

The next step is a simple copy and paste (using the tool) for all of other sections:

Don’t forget that you’ll need to do a refresh of the requests to get the actual data!

2. Create requests with different date ranges

As well as being able to use the fixed dates (eg April 2012, 10th March – 14th March, Q1 2011, etc), rolling dates (last week, this week, last month, etc), preset dates (a combination of fixed and rolling!) and using cells to set date ranges (so that you can copy and paste as above or just simply change the date range on a whim), you can also use custom expressions.

The custom expressions don’t really make sense to start with until you start playing with them. The way they work is that you can choose a start and end date based on expressions of the current day, week, month, quarter, year.

  • cd is the current day
  • cw is the current week (starting on a Sunday)
  • cm is the current month (starting on the first)
  • cq is the current quarter (starting on the first of the quarter – Jan, Apr, July, Oct)
  • cy is the current year (starting on the first)
So if you want to run a report for last month you have a start date of ‘cm – 1m‘ and an end date of ‘cm – 1d‘ (this would show you from the first of the month before the current one to the day before the start of the current month).
This allows you to do lots of rolling time periods that you might not ordinarily be able to do using SiteCatalyst. Eg if you’re in the financial sector and your weeks run from Thursday through to Wednesday you can have a report that reflects this by creating a report with the date range:

cw – 1w – 3d to cw – 1w + 3d   (effectively last Sunday minus three days to last Sunday plus three days) 

This handy ability allows you to manipulate SiteCatalyst to run reports on the date range the rest of your company wants to, rather than the way it wants to. With the implementation of the ‘visitors’ metric in version 15 (rather than daily, weekly, monthly, etc) means you can go any way you want with dates.

3. Create trended reports for multiple metrics and different values of a variable

A pet hate of virtually everyone who has used SiteCatalyst is that when you turn your data into a trended report you can only have 5 variables at once and you can’t have more than one metric! Arrrgggghhhh!
You obviously have more flexibility in Excel because you can put your metrics on different axis to create more exciting reports.
By creating these reports like this you can do lots of different things with them, including graphing them over time, showing percentage increases over time, etc, etc.
Those additional graphs that normally would involve you creating several reports in SiteCatalyst for, downloading and then playing around in Excel with are a huge time saver.
One of my favourite things is the the ‘sub total’ part that we discussed in the first part. By giving page names sensible titles based on their hierarchy I can create filters based on common site sections or content types then you can create entries and bounce rates and so on really easily.

4. Create a filter for your search terms to exclude your brand

When I talk about SEO metrics (which I do quite often), I often talk about measuring search minus brand. It’s a big metric for me because I think that you should be ranking for your brand search terms anyway, so by looking at this metric you are discovering how well you rank for your products.
This is of course very easy using report builder, especially when you want totals to report to your management.
Create a report for your search keywords, add in a filter for your top 10,000 (or however many) that removes any ‘brand’ keywords and then use the sub total option to give you the visits, revenue and so on that you have generated for non-brand related keywords.
Then run it alongside one that has brand related keywords to show how well your brand team are doing!

5. Use your Sub Relations to create filters

I know in the sitecatalyst version 15 you can create filters anyway using the segment builder option. But using the ability to show sub relations in Report Builder is really good considering you can hide the options.
So for example I want to know what channels people were in when they bought stuff arriving at the site through non-branded search terms (or branded, whatever). I can create a report that has the primary dimension of the non-branded search terms using the filter we created earlier and the secondary dimension of the channel the user was in.
By hiding the search terms and using sub totals again I can easily see where people were. In fact, using tip number 1 above, I can use this to compare the revenue through the channels in total with the revenue that came through the search channel.
Here I have really just combined three of the separate reports. Now if I only could come up with a way of combining the other two into this last tip as well…
Posted in Omniture, Report Builder, SiteCatalyst, Web Analytics
One comment on “5 tips for SiteCatalyst Report Builder
  1. Great post. I have a post-it note on my desk to write exactly this post. But you beat me too it! Nice work. #mustworkfaster

Leave a Reply

Your email address will not be published. Required fields are marked *