SiteCatalyst Excel client isn't quite as good as report builder

In my previous job, when I first started thinking about moving from HBX to SiteCatalyst, I had a big concern over whether we could use the excel client in the same way as we did Report builder.  Now I use SiteCatalyst for my new company, I wish I'd blogged about Report Builder because I don't have access to it any more and realise how good it was.  So instead I am going to blog about the SiteCatalyst Excel client, because I can show you bits of it and how you can use it to your advantage.

Firstly though, a bit of history.  Report Builder worked so well and was so popular for a number of reasons.  First on this list was that it was impossible to do a number of things in HBX that seemed like they should be standard and secondly, creating filters in HBX could tend to be time consuming because you had to navigate to the correct report, then wait for it to load, set up your filters, wait for it to load, etc, etc.  Far easier would be to set up one report in Report Builder that referenced a cell, then you could change the cell really easily to get a new report.  Or you could even copy and paste it to get two slightly differently filtered lists next to each other.

Particularly useful was the functionality I used for failed search terms on the internal search.  We'd set it up so that we collected not just the search term, but four associated metrics (eg type of search, any other drop down variables, etc).  We would then use this list to look at which terms we weren't throwing up results for.  This was particularly difficult, because really we were only interested in one type of search, but the user interface in HBX didn't allow us to filter these terms for just those using site search.  Report builder did allow you to do this.

The main area I used it for was reporting upwards on work that we'd done.  Specifically the way the tagging worked in HBX meant that every page was named after its hierarchy  followed by its page name.  This way if you tagged all one type of content in the hierarchy, you could easily filter this list in report builder.  You could then create a report showing how a total of all of the entries changed over time to show whether the content was doing better or not.  You could also do your single access to show whether the bounce rate had gone up or down.  Now there was another step.  If you had 20 different types of content, you could paste them into a spreadsheet and get report builder to reference them in a nice copy and paste option.

For SiteCatalyst, they have got around a lot of these issues with the use of correlations.  The one thing I don't like about correlations is the lack of metrics associated with them (you can only have page views).  For a content rich site - where you really need entries, single access and visits - the correlations don't really work that well, so you want to set up reports that you can filter.

This leads us into another point - if you don't want correlations, but you have set up your account to use the custom traffic variables - can these be of use?  Usually recommendations are that you use your custom traffic variables to pick up the hierarchy of your content.  We do that in a really useful way that allows us to set up the correlations that we really need - against referring domains, keywords, etc.

However the downside is that whilst these are good at rolling up visits into a nice deduplicated measure and entries (assuming that you always pass a value in), your single access is nonsense.  In this case single access is just a situation where that was the only variable that was passed through.  So if you look at several different pages with the same custom traffic variable (but different page names), but only one value for that custom variable, you are counted as a bounce.

An easy way around this is to develop an ASI slot for single access visits and compare this with your main account for entries.

This is where the Excel client comes in useful.  Rather than sitting and switching between accounts, it can be useful to compare your two accounts in Omniture using a bit of 'Copy and Paste'.

 

Once you've created your report in sitecatalyst, it is very easy to copy the whole sheet and paste it in a new workbook, before editing the newly copied report to show the single access report.  This is useful particularly if you are going to want to use this regularly, because of the ease of opening up the a report and refreshing all.

I've also found it useful to make sure that you set your date ranges based on excel sheets, so that they are easily changed.  Don't be fooled in the UK if it thinks that you have the wrong date range.  The clever thing about this is when you copy and paste, you can link your cells back to one original, so that you don't have to change lots.  In fact, this is where a whole series of 'vlookups' in your excel become important (ps learn excel like the back of your hand - there are so many functions that allow you to do more or less everything).

The other advantage the excel client has is that it mimics your user interface perfectly (unlike the datawarehouse and discover that lack any new reports you've set up through SAINT).  One disadvantage though is that it mimics your interface's metrics perfectly as well, meaning if you are missing visits in your report in the user interface, you'll be missing them in the excel client.

One area that report builder particularly outweighs the excel client is that you can't run any of the correlations in the excel client.  Really I used report builder to pick up places that I couldn't create graphs over time and would have had to have downloaded report by report by report to get a series of months.  With SiteCatalyst you can do all that in the user interface except when you do traffic correlations.  It would be nice if you could run correlations in the excel client, so that you could create a report over time to see if something is going up or down.  I suppose you can't really have everything.

Personally I think maybe Omniture missed the point of report builder.  Where it was probably created to allow people to create automated management reports, I certainly didn't use it for that - I had a whole library of reports that could just be updated.  I find this hard to do in the Omniture Excel client, which has to be mainly used as creating a set of high level management reports.  Check out the Omniture blog post on the excel client for more hints and tips.

SAINT classification - a walk through

Calm down all those Roger Moore (or Val Kilmer if you're a bit younger) fans, I'm not going to talk about him at all in here.  Sorry.  Instead I am going to talk about something that I am doing at the moment at work (surprise surprise) and funnily enough this is something that Adam Greco has talked about before on the Omniture blog, so I'm going to try and do this in a different style, otherwise I'll be saying the same thing has he said and I'll be adding no value at all.

Essentially SAINT is a type of classification that you can do in SiteCatalyst that allows you to classify one of your variables into some different stream, if you can't code it in on the page.  In our case, we have a series of content that are written by people who work in various different departments and in various different teams in those departments and we don't capture in our CMS what they are.  However they are keen to know as a department and team within the department how their content is doing as a whole.  This is where the classifications work.  We have a list that is coordinated for one group of content that describes this one to many relationship and what we want to do is put this into SiteCatalyst without having to code it into the pages.

With HBX in previous companies we did this by populating the custom elements of the tags from the database, but sometimes it wouldn't work and sometimes it wasn't right, so you'd have to go back and get your dev team to sort out the tagging.  This could be expensive, time consuming and in the mean time you'd lack any data on the stats.  Well with SiteCatalyst one of the benefits is that you can run the historic data as well and if you make a mistake, you can correct it and it will replace all the data.  How many times have you sat there and someone has said "Oh, I didn't realise that this fell outside the standard and wouldn't be included!"

The process starts rather simply.  You have to name your fields.  In the Report Suites section of your user management, choose the report suite that you want to add the classifications to and then go to edit settings (as in the image below), choose 'Traffic' and 'Traffic Classifications'.

This will bring you up with a new screen, where you can add in your new reports that you want to create:

 

Here in this case, I've got my 'Guide' variable which I have mapped across to the different departments and am now going to add in one classification of 'Department', another of 'Agency' and another of 'Service'.  Remember you can add classifications to classifications, but the upkeep may be more (or less) difficult.  In this case, maybe it would have been better to create the first classification of 'Service' and then a classification of 'Service' could have been 'Agency'.  Given the Agency that a Service sits in is unlikely to change that frequently (if at all, but you never know with the British Government), then this may have made more sense.  In this case, I've actually added in as three separate columns because in some cases there is no real agency or service and these fields are going to be left blank in favour of just a department.

Now, as you can see below, I've added in a few extra fields into my left hand navigation when looking at data:

Unfortunately there is nothing in these data reports at the moment, because I haven't told SiteCatalyst which Guide relates to which department, agency and service.  So I need to upload this into SiteCatalyst.  But there is one thing I need to do first.  I need to check the format that we are picking up the guide name in.  This will determine how I am going to upload the data.  In this case, there is a bit of a difference from just the name of a guide and whether I've truncated it to a certain number of characters, etc.  I'm saying this deliberately here, because I know I have and I do.  It means a simple list of guide names with their associated Department and agencies isn't going to work.  I need to use excel to concatenate the name of the theme into the name of the guide.  I also need to check through the work done elsewhere to find any discrepancies.  Unfortunately this is a one to one relationship, so any particular piece of content that has two creators is going to have to name one as the main owner.

This brings us on nicely to the point where we have to upload the data onto SiteCatalyst.  Fortunately SiteCatalyst gives us a nice option of doing this in a very easy way by giving us the template that we can download.  Again in your Admin options, you have the option of SAINT Classifications, which this time is the area you want to be looking in:

The template you download will have very little in it.  It will tell you the title of your columns and a few lines at the top that don't really do anything else.  This is where you need to put your classifications in.  So your first column (the key) will have the values that appear in SiteCatalyst already (in this case it is the name of our guides) and in the second, third and fourth (and however many other classifications you have) you can put in the other values:

 

One thing to bear in mind, is that you need to make sure that you don't save this file in another format.  Make sure it sticks as .tab and doesn't pick up any excel formatting, otherwise you'll end up not being able to upload it properly.  

Importing it is easy.  Just select the import option from the same menu you created your download from, choose the report name, report suite and filename and then upload.  Don't worry too much about overwriting data on conflicts - this will only be a problem if you are changing the data from previous imports.  At the end of this you'll get a report telling you how successful it has been (I just got this one below):

Now it is time to go and look at your lovely data!!!

One thing I'd say is that you have automatically created correlations between your different new reports, but not to your original report.  One thing you could do would be to create another column in the table that linked these into the original report for instant correlations at no extra cost.  I think this is something that I am going to try for next time.