Omniture ExcelClient [Inside Omniture SiteCatalyst]

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