It has often been said that Excel is the one application most often used when analyzing data on a computer.  Its use far outnumbers any other tool including those tools that fall within the category of Business Intelligence.  It is also our experience at Webtrends that our customers 8-25-2009 8-57-42 AM.pngalso use Excel to a great extent for the further analysis, integration , and presentation of data extracted or exported from Webtrends Analytics reports. So, it is important that Webtrends make available to its customers the tools needed to get that data from the Webtrends Analytics reporting tool and into Excel in an easy and straightforward way.  This is what the new REST URL DX API is all about.

 

There are multiple ways that you get data into Excel using the REST URL APIs.  The most important part of the process is to create/generate a valid REST URL in the first place.  Once that is done, it a fairly straightforward process to use it to get web data and populate rows of data where you want them.  Two very simple ways to do this, of course, are: 1) use the REST URL Generator application available at https://generator.webtrends.com; and 2) to use REST URLs that are generated with the new Webtrends Analytics 9 Insight interface (http://insight.webtrends.com).  In each of these all you need to know is your log in information and what data (report) you want to extract.  Using either of these methods will get you well on your way to populating Excel workbooks and then using that information in the way you want.

 

But, suppose you want to do more.  Suppose I want to extract data from Webtrends and then use the power to of Excel to store it, update it, and present it in the ways you need.  And, I want all of this in a single tool.  I don't want to go back and forth between a Webtrends application and Excel.  I want it all in Excel.

 

Well, this is possible.  Using tools such as VBA (Visual Basic for Applications) within Excel, you can access the Webtrends data, build REST URLs and populate the data into worksheets where you need the data.  The interesting part of this process is really how to you build and manage the REST URLs that extract your data.  If you read the developer notes on this site about how to build REST URLs, you will quickly learn that there is more than one format based on the data you need to get and that data can be returned in different formats.  These formats go by the names of XML, JSON, and HTML.  Somewhere imbedded in these formats hides the data I want and need to get the job done.  And why do you need to know this?  Because the various REST URL methods are needed to find out what data is available, such as profile, reports, time periods and the like. And you will need this information if you are going to build  a working REST URL that ultimately gets report data.

 

So, we have developed this Excel/VBA Sample Application that will make this easy for you.  The application has embedded a REST URL Generator functionally equivalent to the REST URL Generator mentioned above.  The VBA code that is part of the application uses all of the appropriate methods for extracting configuration information and parses it out from the data streams that come from using the REST URLs methods for doing things like getting a list of profiles and so forth.  The application also stores the REST URLs that you generate and lets you define where you want this data to go.

 

So, what does this mean for you?  Well, you can use the application as is.  It is quite functional and has complete usage documentation associated with it.  Or, as it is more intended, you can use this as a base for exploration or expansion on its functionality.  Add to it to do more formatting, analysis, data comparisons and the like.  Then feed what you did back into this developer network so that others may use your experience as a launching pad for their own application.

 

Whatever you do with it, enjoy!

 

Michael Love

Senior Solutions Engineer