While the reports and dash­boards you can cre­ate within Omni­ture Site­Cat­a­lyst are great, there is no escap­ing the fact that power web ana­lysts have an affin­ity for Microsoft® Excel®.  Microsoft Excel pro­vides numer­ous ways to manip­u­late and view data that will never be avail­able in any web ana­lyt­ics tool.  For this rea­son, Omni­ture pro­vides access to a pow­er­ful “Excel­Client” (not a typo, there is no space) which allows you to pull data from your Omni­ture Site­Cat­a­lyst data set into Microsoft Excel.  This Omni­ture Excel­Client is extremely pow­er­ful, espe­cially when com­bined with advanced knowl­edge of Microsoft Excel, a tool well known to tra­di­tional ana­lysts.  In this post I will famil­iar­ize you with what you can do with the Omni­ture Excel­Client and how it can be used to sim­plify your web analysis.

What is the Omni­ture Excel­Client?
So what exactly is the Omni­ture Excel­Client and why should you care about it?  In a nut­shell, the Omni­ture Excel­Client is an Excel add-on that allows you to define data queries (known as “Data Blocks”) of your Site­Cat­a­lyst data and embed them into Microsoft Excel.  Through a wiz­ard inter­face accessed within Excel, you can add a report query to a spread­sheet and save it so that in the future you don’t have to repeat the same query again.  Instead, you can sim­ply “refresh” the query since it already knows what data you are look­ing for.  So the first two ben­e­fits of the Omni­ture Excel­Client are that it allows you to eas­ily push data from Site­Cat­a­lyst into Excel and that it can save you time by not requir­ing you to re-create queries.  But wait…there’s more!  The best part of the Omni­ture Excel­Client is that it allows you to tie parts of the data query to val­ues in spe­cific cells of the Excel spread­sheet.  While this may not sound very excit­ing, it is (I promise)!  Using this fea­ture you can:

  1. Tie the Site­Cat­a­lyst report suite ID to a cell so you can view the same data block for dif­fer­ent report suites by chang­ing just one cell (assum­ing you have the same vari­able def­i­n­i­tions in both)
  2. Tie the start and end dates to cells so you can view the data block for dif­fer­ent time ranges by chang­ing one or two cells
  3. Tie the num­ber of rows you want to view in the report to a cell so you can mod­ify the num­ber of rows in your report by chang­ing one cell
  4. Tie a search phrase to a cell so you can enter a term or phrase and have the report results fil­tered as you would using the search box within Site­Cat­a­lyst.  This fea­ture rec­og­nizes both “include” and “exclude” phrases.

All of these options are shown in the fol­low­ing sam­ple Excel spreadsheet:

In the exam­ple shown here, instead of tying a Site­Cat­a­lyst data block to a par­tic­u­lar 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 Work­sheet” but­ton in the Excel­Client tool­bar to pull the appro­pri­ate data from Site­Cat­a­lyst.  This allows you to build very flex­i­ble data blocks which can return dif­fer­ent data sets based upon your cur­rent needs.  Finally, in addi­tion to all of the pre­ced­ing items, you get the abil­ity to cre­ate as many charts and graphs as you want based upon your Site­Cat­a­lyst data and even merge Site­Cat­a­lyst data with other data (i.e. offline data) that you pull into Excel.  This means that if you or one of your asso­ciates is an Excel guru, you can do vir­tu­ally any­thing with your Site­Cat­a­lyst data.  When I was an Omni­ture client, I had an excel work­book with about 30 tabs and one sum­mary sheet that had pretty graphs and data aggre­ga­tions.  Each morn­ing, I would come to the office, click the “Refresh All” but­ton and go grab a morn­ing drink know­ing my sum­mary report would be done by the time I got back!

How Do I Install and Get Started with the Omni­ture Excel­Client?
To use the Omni­ture Excel­Client, your orga­ni­za­tion must have a license and your user ID must be added to the appro­pri­ate Excel­Client secu­rity access group by your Site­Cat­a­lyst admin­is­tra­tor.  Once this is in place, you can down­load the Omni­ture Excel­Client by click­ing the link within the Site­Cat­a­lyst tab (green arrow) as shown here:

Once you have installed the Omni­ture Excel­Client, open Excel and you will find it in the Excel tool­bar or in the “Add-Ins” area of Microsoft Excel (depend­ing upon your ver­sion).  Next, you click the “Insert Data Block” but­ton, login using your Site­Cat­a­lyst cre­den­tials and fol­low the wiz­ard to cre­ate a data block:

Impor­tant Things to Know About the Omni­ture Excel­Client
The fol­low­ing are some impor­tant things to know about the Omni­ture ExcelClient:

  1. You can add mul­ti­ple data blocks to an Excel work­sheet, but you need to be sure that you are not over­lap­ping data blocks or you will be in trou­ble.  Keep in mind that you may choose to make the num­ber of rows or columns be dynamic (i.e. tied to a cell) so be sure to take this into account when adding mul­ti­ple data blocks to the same work­sheet (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 work­sheet (a few min­utes).  If you have many com­plex data blocks on the same work­sheet, you could expe­ri­ence a time­out so con­sider using mul­ti­ple work­sheets when appro­pri­ate.  Note that you can refresh mul­ti­ple work­sheets at one time using the “Refresh All” button.
  3. I have found that I some­times encounter prob­lems when my Excel file­name and/or work­sheet names have spaces in them.  I believe that this has been addressed in newer releases, but I err on the side of cau­tion and use under­scores instead of spaces in file/worksheet names to be safe.
  4. Due to the inter­play between Omni­ture and Excel, I have at times expe­ri­enced sit­u­a­tions where a spread­sheet will get cor­rupted.  For this rea­son, I rec­om­mend that you save his­tor­i­cal ver­sions of your spread­sheet just to be safe.
  5. If you don’t know what your Omni­ture report suite ID’s are, look in report suites area of the Admin Con­sole or ask your Site­Cat­a­lyst administrator.
  6. In a future post, I will dis­cuss one of the more advanced Omni­ture Excel­Client fea­tures known as Pub­lish­ing, which allows you to upload your Excel spread­sheet and have it deliv­ered to dif­fer­ent peo­ple for dif­fer­ent report suites on a sched­uled basis.

Real-World Exam­ple
So let’s go through another real-world exam­ple to show you the power of the Omni­ture Excel­Client.  In this exam­ple, a retail sub­sidiary of our fic­ti­tious client Greco Inc. has two web­sites that are struc­tured the same, but sell com­pletely dif­fer­ent prod­ucts.  One (Elec­tron­ics Plus) sells con­sumer elec­tron­ics and the other (CoolFlow­ers) sells, you guessed it, flow­ers.  An exec­u­tive who over­sees both sites is cur­rently get­ting dif­fer­ent reports from the prod­uct man­ager of each site and it is mak­ing it dif­fi­cult for her to com­pare the two sites.

To solve this prob­lem, the two prod­uct man­agers deter­mine what infor­ma­tion the exec­u­tive needs to see on a daily basis and use the Omni­ture Excel­Client to define the data that is needed.  The first data block they choose to report is Rev­enue by Search Key­words.  They set-up the Excel­Client data block so that it has the cor­rect report and tie it to the cells shown here:

The result­ing report pulls the Rev­enue for the top Search Key­words for the spec­i­fied time­frame and the selected report suite, which in this case is ElectronicsPlus:

Upon look­ing at this report the exec­u­tive asks to see the top 5 rev­enue gen­er­at­ing Search Key­words that have the phrase “com­puter” in them.  Since the web ana­lyst planned ahead and tied the “Top” and “Search” com­po­nents of the query to cells in Excel, all he needs to do is change the value in the “Top” cell to “5,” enter “com­puter” in the “Search” cell and refresh to see the following:

After this slight digres­sion, the exec­u­tive asks to see the orig­i­nal Top 10 Search Key­word Rev­enue report he had seen for Elec­tron­ic­sPlus for CoolFlow­ers.  To pro­duce this, the web ana­lyst sim­ply changes the cells to have the appro­pri­ate report suite ID, changes the “Top” value back to “10,” clears the “Search” cell and refreshes to see the fol­low­ing report:

As you can see from just this one data block, the Excel­Client pro­vides the flex­i­bil­ity to see dif­fer­ent types of data quickly. This becomes even more pow­er­ful when mul­ti­ple data blocks are added and charts and graphs are embed­ded into Excel based upon the Site­Cat­a­lyst data.

 

Have a ques­tion about any­thing related to Omni­ture Site­Cat­a­lyst? Is there some­thing on your web­site that you would like to report on, but don’t know how? Do you have any tips or best prac­tices you want to share? If so, please leave a com­ment 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 every­one can learn! (Don’t worry — I won’t use your name or com­pany name!). If you are on Twit­ter, you can fol­low me at http://​twit​ter​.com/​O​m​n​i​_​man.

Learn more about Omni­ture Con­sult­ing
Learn more about Omni­ture 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!