While the reports and dashboards you can create within Omniture SiteCatalyst are great, there is no escaping the fact that power web analysts have an affinity for Microsoft® Excel®.  Microsoft Excel provides numerous ways to manipulate and view data that will never be available in any web analytics tool.  For this reason, Omniture provides access to a powerful “ExcelClient” (not a typo, there is no space) which allows you to pull data from your Omniture SiteCatalyst data set into Microsoft Excel.  This Omniture ExcelClient is extremely powerful, especially when combined with advanced knowledge of Microsoft Excel, a tool well known to traditional analysts.  In this post I will familiarize you with what you can do with the Omniture ExcelClient and how it can be used to simplify your web analysis.

What is the Omniture ExcelClient?
So what exactly is the Omniture ExcelClient and why should you care about it?  In a nutshell, the Omniture ExcelClient is an Excel add-on that allows you to define data queries (known as “Data Blocks”) of your SiteCatalyst data and embed them into Microsoft Excel.  Through a wizard interface accessed within Excel, you can add a report query to a spreadsheet and save it so that in the future you don’t have to repeat the same query again.  Instead, you can simply “refresh” the query since it already knows what data you are looking for.  So the first two benefits of the Omniture ExcelClient are that it allows you to easily push data from SiteCatalyst into Excel and that it can save you time by not requiring you to re-create queries.  But wait…there’s more!  The best part of the Omniture ExcelClient is that it allows you to tie parts of the data query to values in specific cells of the Excel spreadsheet.  While this may not sound very exciting, it is (I promise)!  Using this feature you can:

  1. Tie the SiteCatalyst report suite ID to a cell so you can view the same data block for different report suites by changing just one cell (assuming you have the same variable definitions in both)
  2. Tie the start and end dates to cells so you can view the data block for different time ranges by changing one or two cells
  3. Tie the number of rows you want to view in the report to a cell so you can modify the number of rows in your report by changing one cell
  4. Tie a search phrase to a cell so you can enter a term or phrase and have the report results filtered as you would using the search box within SiteCatalyst.  This feature recognizes both “include” and “exclude” phrases.

All of these options are shown in the following sample Excel spreadsheet:

In the example shown here, instead of tying a SiteCatalyst data block to a particular report suite, you can tie it to the report suite ID found in cell “B1.”  Instead of tying a data block to the month of August 2008, you can make it so the data block looks to cells “B2″ and “B3″ for the start date and end date and so on.  Then, after you update these cells, all you need to do is click the “Refresh Worksheet” button in the ExcelClient toolbar to pull the appropriate data from SiteCatalyst.  This allows you to build very flexible data blocks which can return different data sets based upon your current needs.  Finally, in addition to all of the preceding items, you get the ability to create as many charts and graphs as you want based upon your SiteCatalyst data and even merge SiteCatalyst data with other data (i.e. offline data) that you pull into Excel.  This means that if you or one of your associates is an Excel guru, you can do virtually anything with your SiteCatalyst data.  When I was an Omniture client, I had an excel workbook with about 30 tabs and one summary sheet that had pretty graphs and data aggregations.  Each morning, I would come to the office, click the “Refresh All” button and go grab a morning drink knowing my summary report would be done by the time I got back!

How Do I Install and Get Started with the Omniture ExcelClient?
To use the Omniture ExcelClient, your organization must have a license and your user ID must be added to the appropriate ExcelClient security access group by your SiteCatalyst administrator.  Once this is in place, you can download the Omniture ExcelClient by clicking the link within the SiteCatalyst tab (green arrow) as shown here:

Once you have installed the Omniture ExcelClient, open Excel and you will find it in the Excel toolbar or in the “Add-Ins” area of Microsoft Excel (depending upon your version).  Next, you click the “Insert Data Block” button, login using your SiteCatalyst credentials and follow the wizard to create a data block:

Important Things to Know About the Omniture ExcelClient
The following are some important things to know about the Omniture ExcelClient:

  1. You can add multiple data blocks to an Excel worksheet, but you need to be sure that you are not overlapping data blocks or you will be in trouble.  Keep in mind that you may choose to make the number of rows or columns be dynamic (i.e. tied to a cell) so be sure to take this into account when adding multiple data blocks to the same worksheet (A trick I use is to zoom out to 25% in Excel and you can see where all of your data blocks are!)
  2. There is a Microsoft enforced limit of time that data has to return on a worksheet (a few minutes).  If you have many complex data blocks on the same worksheet, you could experience a timeout so consider using multiple worksheets when appropriate.  Note that you can refresh multiple worksheets at one time using the “Refresh All” button.
  3. I have found that I sometimes encounter problems when my Excel filename and/or worksheet names have spaces in them.  I believe that this has been addressed in newer releases, but I err on the side of caution and use underscores instead of spaces in file/worksheet names to be safe.
  4. Due to the interplay between Omniture and Excel, I have at times experienced situations where a spreadsheet will get corrupted.  For this reason, I recommend that you save historical versions of your spreadsheet just to be safe.
  5. If you don’t know what your Omniture report suite ID’s are, look in report suites area of the Admin Console or ask your SiteCatalyst administrator.
  6. In a future post, I will discuss one of the more advanced Omniture ExcelClient features known as Publishing, which allows you to upload your Excel spreadsheet and have it delivered to different people for different report suites on a scheduled basis.

Real-World Example
So let’s go through another real-world example to show you the power of the Omniture ExcelClient.  In this example, a retail subsidiary of our fictitious client Greco Inc. has two websites that are structured the same, but sell completely different products.  One (Electronics Plus) sells consumer electronics and the other (CoolFlowers) sells, you guessed it, flowers.  An executive who oversees both sites is currently getting different reports from the product manager of each site and it is making it difficult for her to compare the two sites.

To solve this problem, the two product managers determine what information the executive needs to see on a daily basis and use the Omniture ExcelClient to define the data that is needed.  The first data block they choose to report is Revenue by Search Keywords.  They set-up the ExcelClient data block so that it has the correct report and tie it to the cells shown here:

The resulting report pulls the Revenue for the top Search Keywords for the specified timeframe and the selected report suite, which in this case is ElectronicsPlus:

Upon looking at this report the executive asks to see the top 5 revenue generating Search Keywords that have the phrase “computer” in them.  Since the web analyst planned ahead and tied the “Top” and “Search” components of the query to cells in Excel, all he needs to do is change the value in the “Top” cell to “5,” enter “computer” in the “Search” cell and refresh to see the following:

After this slight digression, the executive asks to see the original Top 10 Search Keyword Revenue report he had seen for ElectronicsPlus for CoolFlowers.  To produce this, the web analyst simply changes the cells to have the appropriate report suite ID, changes the “Top” value back to “10,” clears the “Search” cell and refreshes to see the following report:

As you can see from just this one data block, the ExcelClient provides the flexibility to see different types of data quickly. This becomes even more powerful when multiple data blocks are added and charts and graphs are embedded into Excel based upon the SiteCatalyst data.

 

Have a question about anything related to Omniture SiteCatalyst? Is there something on your website that you would like to report on, but don’t know how? Do you have any tips or best practices you want to share? If so, please leave a comment here or send me an e-mail at insidesitecatalyst@omniture.com and I will do my best to answer it right here on the blog so everyone can learn! (Don’t worry – I won’t use your name or company name!). If you are on Twitter, you can follow me at http://twitter.com/Omni_man.

Learn more about Omniture Consulting
Learn more about Omniture University

 

13 comments
Naeem
Naeem

I installed the ExcelClient , however not able to find the excel client in excel tool bar or in add ins .Could you please helpp me out.

Tehmina
Tehmina

Why is there a difference in values of data points extracted using Excel Client and Report Builder?

Marla
Marla

I just started using ExcelClient yesterday, hoping that it would save me a huge chunk of my time and effort. I'm trying to run multiple queries on the natural search keyword report - to be specific, I have 2700 specific terms that I want to know the number of searches for each term. It doesn't seem like excelclient is letting me input a range of cells for the search terms and a corresponding range to insert the results. Does anyone know of a way to automate this process, either with a macro or some way that I haven't thought of, to auto-fill the various search terms?

susan
susan

Hi, Adam. I have two campaign classifications called "channel" and "affiliate." I want to report by channel broken down by affiliate. When I build the data block, I can select the "channel" report or the "affiliate" report; but I can't figure out how to get both channel and affiliate on the report. Is this possible?

Ben Gaines
Ben Gaines

Greg, I work closely with our support team (and with many Excel Client users on Twitter), and have not seen file corruption due to Excel Client in quite some time. Development on the tool has, of course, continued since this post was made and I believe these issues have been addressed since Sep 2008. That said, if you continue to see issues related to Excel Client, please let me know (omniture care at adobe dot com, or http://twitter.com/omniturecare) and we would be happy to look into them. Thanks, Ben Gaines

Greg Doran
Greg Doran

4. Due to the interplay between Omniture and Excel, I have at times experienced situations where a spreadsheet will get corrupted. For this reason, I recommend that you save historical versions of your spreadsheet just to be safe. So when this happens, your file is just gone? The root cause has not been determined and addressed in the 2 years since this post? This could be a great tool. Saves you a ton of time clicking around SC but is really worthless if 50%+ of the time your file gets corrupted which is what i am now seeing.

Adrian Cutler - Zed Digital
Adrian Cutler - Zed Digital

Hi Robert, I find it best to have the today() in one field and then ='cell where today() is' in the cell that the client will look at. Aaron, see above to

Robert
Robert

I have tried everything I could think of to get variable dates to work but have not succeeded. I have changed the format of the date in the spreadsheet, tried using static dates in the spreadsheet (rather than =TODAY()) but no dice. Is there some really simple step I am missing?

Hans
Hans

Hello, are there any sample reports built with the Excel client available? It would be great to get helpful suggestions on how to build own reports via Excel client (with graphics etc.).

Adam Greco
Adam Greco

Aaron - The issue is that when using the Publishing feature, the file is never "opened" so the TODAY() function won't refresh. For this reason you have to either open the spreadsheet and re-upload it periodically, or use a future date that is hard-coded. Feel free to submit an enhancement request through ClientCare if you'd like to see this changed. Thanks!

Aaron
Aaron

Hi Adam, I find the Excel plugin very useful and have been trying out the new publishing feature however I don’t seem to be able to achieve something with absolute dates. I use Absolute ranges, and point them to cell references in the worksheet. The start date cell has set value, but the end date cell contains TODAY(). When I receive the report, the data only refers to the dates as per when I actually saved the report, it ignores the TODAY() formula. I can't think of any other way of having a custom date range be automatically generated in an Excel file, which would save me from having to refresh the report manually and email it on to my distribution list. Any ideas?

Melissa
Melissa

I just started using the ExcelClient this week. It is a phenomenal tool! I wish I had started using it months ago! A tip to other SiteCatalyst users...if you haven't started using this tool, take the time to learn how to use it. It's well worth the effort!