1 2 3 Previous Next

Data Extraction

33 Posts

Hello Webtrends Developers. 

I'm excited to announce a major upgrade to our Webtrends Data Extraction API: version 3.

V3 Documentation

 

 

Why a version 3?

Although Webtrends regularly upgrades and enhances the REST Web Services throughout the year, new versions and announcements are reserved for significant releases in which major new functionality and capability is added.  V3 brings with it some features that will make it easier to build your HTML5 dashboards, trend your multi-dimensional data, or build your own localized interface to the data. We've been hard at work (if I do say so myself). 

 

With that said, here are all the details.

 

 

What's new?

New Trending Features and Formats 

You've always been able to trend Webtrends data via REST but it brought with it some limitations including limits on multi-dimensional data (it's pretty challenging to trend cross-correlated dimensional data) and some legacy report data.

 

I'm happy to say that all the dimensional data in v3 is trendable.

 

When you jump into multi-dimensional trending it's easy to get buried in your data so we also added more than one view on this trend data with a period_type parameter depending on what you are trying to trend.

 

For example: if you are trying to build your trend lines where time is the critical dimension, choose period_type=indv.

period_ind.PNG

If you are trying to trend specific items of data over a date range, choose period_type=trend.

period_trend.PNG

Or if what you want is the multi-dimensional detailed data but aggregated across your date range period_type=agg

 

period_agg.PNG

 

 

JSONP Data Format

Building HTML dashboards?  JSON data is fast, efficient and well supported... except you might have found that you can't include an external JSON data package directly into your HTML because the source is from an external domain (dx.webtrends.com).  JSONP stands for JSON with padding and it is the accepted format for indicating a non-script data package which means it is not an executable script from another domain, i.e. browsers won't block the inserted data from another domain. 

 

It's a simple yet useful addition.  Check wikipedia on more details about JSONP.

 

 

Localization and International Dates

Now your data is fully localized including data headers.  This may not have been an issue for users consuming XML data in your single language spreadsheet or app but if you wanted to build a dashboard out of the data (HTML) or create an Excel pivot table directly from the output of the REST request, it sure was a hassle when you cross languages.  Now you can build your own language selector in Excel and the returned data is localized and ready.

 

Additionally, dates in raw data formats (XML, XML2 and JSON) all conform to ISO 8601, the accepted international date format:  2012-01-01.

 

If you are in HTML format, dates will be formated in the chosen style for your Webtrends profile.

 

 

A10 Spaces

Webtrends Analytics 10 introduced "Spaces" for your various types of data.  You can now select key metrics, report lists and even your profile list for each space.

 

 

Facebook and Mobile External Data

External Data that Webtrends is pulling into your Space dashboards is also available including Facebook, iTunes, etc.  Not only does this give you one easy place to pull this data back out, we've added value to the data... it trendable, and the lifeline for the data is the life of your Webtrends data rather than the limited time it is available in the various external data source (usually about 60 days).

 

 

Generator update

v3 of the DX REST Generator is also now available at Generator.webtrends.com and has been updated to v3

 

 

Please note we also know that v2_1 is still important to many users and it is still available via link on Generator for those still working with existing REST urls).  Go to the v2.1 DX REST Generator directly from here

 

 

 

 

Documentation

There are a host of other features and changes as well.

Please take a closer look at the v3 documentation.

Should you have the need, version 2_1 is naturally still working and available.

 

We'd like your feedback too.  Particularly if there is something specific in version 2 that you don't see and there isn't a better way to do it in v3.  Comment, or send us an email with your needs, we're listening I can assure you.

On behalf of all of us at  Webtrends, I'm pleased to announce the immediate availability of our Data Extraction API for Analytics 9 On Premises. Documentation on the Data Extraction API is available here on the Developer Network (see link to the right).  Installation of the data extraction API is an optional part of the Analytics 9 installation program located in our Software Center, http://www.webtrends.com/Support/UpgradeCenter.aspx .

 

Details on all of the new and updated components for Analytics 9 On Premises are on our new Release Notes blog located at http://releasenotes.webtrends.com/on-premises/

 

As a reminder, participating  in our community discussion on the Developer Network is easy -  simply create an account on this site.  This will allow you to create  new discussions and comment on existing ones.  If you prefer not to  create an account, you can still view activity on our site but won't be  able to comment, participate or get notifications.

 

-Derek Fine, Webtrends Product Management

This is just a note to the community that we're keeping our Data Extraction documentation fresh: some of our advanced methods for DX 2.0 have been enhanced with new examples/samples.

 

 

Take a look at the Parameters chapter in the documentation here:

http://product.webtrends.com/dxapi/index.html

 

 

Also, we'd like to encourage users to take a look at the new realtime, alerting and syndication features.  I'll post a blog on how to get the most out of syndication soon too but for now, try experimenting with the new features detailed on our v2.0 documentation.

Continuing the momentum with our Webtrends open APIs, we’re excited to release version 2 of our Data Extraction REST API.

 

 

This is the most significant release of our Data Extraction web services since its debut.  We’ve added lots of nuts and bolts features you’ve been requesting (thank you all for your incredible feedback btw, keep it coming!) like enhanced support for trending, additional legacy data report support, more advanced filtering features, faster response time and better handling of large data sets.  If you haven’t used it lately, check out your list of reports and trending availability for your account at https://generator.webtrends.com to see how some of this affects you.

 

 

But it’s the big stuff we’re most excited to announce.  These are new features that, we think, will quietly change the industry.

Realtime and Key Metrics Data

See the announcement of our latest Analytics Insight release.  These features are now available within our REST API.   http://www.webtrends.com/products/analytics.aspx?WT.ac=hp_hero_analytics

 

 

Analytics Data as Syndicated Content

Sounds lofty and I argue it’s a game changer.  Until now, integrating analytics data requires a connection and a data pull be defined and updates occur at some predetermined regular frequency.  Granted, we’ve made this process easy by making our REST a copy and paste (of a single web address) process for applications, including Excel.  But with the wide availability of our realtime data, this means you either have to have your applications constantly polling and probing for the latest data or you need something new… push data.

 

With DX v2 we now deliver Webtrends data as a constant stream of syndicated content… all our Insight users have active feeds (RSS or Atom format) of analytics data.

 

Want to spread your key traffic data widely throughout your organization with no coding, no apps, and no copy and paste emails?  Want to send up-to-the-moment data to users that aren’t analytics users (i.e. think executives or marketing folks that don’t have the time or know-how to log in and examine the data)?  Maybe you want to set up your own internal system for creating automated actions like campaign launches based on realtime data thresholds and you already have a systems infrastructure for doing so?

 

Any RSS or Atom compatible application can simply “subscribe” to one of many customizable syndicated DX data feeds.  A frequent example:  Outlook users can have one or more custom Webtrends feeds constantly updated right within their RSS inbox.  See the API v2 docs for full descriptions but you can subscribe to the Webtrends key metrics, alerts, and notes for specific users, specific profiles, “my” alerts, etc.  And the data will simply keep flowing automatically.  Note that not all measures for all of our report data points are available in this format yet but we’re expanding quickly we really wanted to get your feedback first so check it out and tell us your experiences.

 

 

Alerts, Notes and Alerting API

Many of you already saw the fanfare of our alerting features added to Webtrends Insight which we’ve also added to DX REST v2.  Being the gearhead I am, I’m happy to mention that via our web services alerting has wider distribution options than is listed in the Insight interface since Alerts are also provided using our syndication method.  Set up an alert and regardless of the device configuration you have, your alerts will show up in your DX syndication feed (RSS or Atom format) so that any reader will automatically get the alert.  Browsers, Outlook, Android, iPhone, Windows Mobile or Blackberry phones with RSS readers, etc. all can easily consume updates.  Note that as with all our web services, authentication is required so it must be an RSS or Atom client that supports authentication.

 

And this isn’t limited to Alerts or even Analytics data only.  The Webtrends Notes feature allows you to create marketing calendars and other events as notation on your Insight data.  These notes are stored as a syndication feed as well, by user, by profile so you create REST requests for any/all of these.

 

Q:  Is there an API for creating alerts?  A:  There is a foundation for using our API to create new alert definitions but we are putting the polish on the REST API format now.  If you have feedback suggestions on the topic, we’re more than happy to hear what you have to say.

 

 

Language and Locale Support

Webtrends has a wide audience to please so we’re happy to now have support within our REST API for language and locale settings.  Report, column and tokenized names within Webtrends can now be requested by language as well as support for data formats by locale where possible.  Using a language ID code and locale reference, dates and decimals are configured based on locale in most cases, see our documentation for details.  One exception is currency since Webtrends reports allow for multiple currencies, even within the same report; locale will not translate your currencies… you’ll still need to reference the report meta data to identify which columns have which currencies in them.  Which languages are supported?  Check your list of languages within your Webtrends Analytics interface for full list of supported languages.

 

 

Why a version 2?

Since the Data Extraction API debuted in beta last Spring, we’ve actually had 5 significant releases and many other maintenance releases and yet the REST API only shows a version 1 and 1.1 prior to the 2.0 release.  Our philosophy is to only make a new REST version if changes could be disruptive to previous REST requests like if the XML structure changes, we add new rows of data that could shift dashboards, etc.

 

Will there be a version 2.x?  Not if we can help it.  Sure it's nice to have a splashy version release but we want to roll out features without disrupting use of the REST addresses.  So until we have something so big it needs a 3.0, we plan on simply adding features to v2 and keeping the REST address the same.

 

 

 

Invitation to all REST users (Please switch to v2 quickly )

We’d like to invite and encourage everyone to adopt version v2 as fast as possible.  Though this sounds like a selfish request on the part of Webtrends, the honest truth is that we want to speed up development and provide a more compelling service for our customers and maintaining version 1 and 1.1 slows us down.  So please, if you have dashboards or applications running on version 1.x of the REST API, take some time and switch the requests to v2.

 

 

The DX Community

As always, thanks for being such a valuable addition to making Webtrends innovation hum.  Keep the feedback up.

<fanfare>Version 2 of the Data Extraction API is now available.</fanfare>

 

As always, we strongly encourage upgrading to version 2.0, so that you can put the new functionality to work and give us your feedback and ideas for future development.

 

If you can’t migrate immediately, version 1.1 is still available for use (documentation at http://product.webtrends.com/dxapi/v1/index.html). Note: Using some v1.1 URIs in v2 will produce different results, because of changes in data. In XML format, there will be new values (for example, isSearchable); in HTML, layout is altered.


To use v2 features, specify v2 in your URIs. For example:

https://ws.webtrends.com/v2/ReportService/profiles/YcL4a5dufF6/reports/RZ2YgglpHk5/info?format=xml


This URI uses version 1.1:

https://ws.webtrends.com/v1_1/ReportService/profiles/YcL4a5dufF6/reports/RZ2YgglpHk5/info?format=xml

Additions and Changes

  • Alerts and Notes API: Use the syndication feeds method to create a feed of alerts and notes.
  • Key Metrics: Use the keymetrics method to summarize important data for profiles of interest.
  • Internationalization: The language parameter enables you to specify a language preference for report data.
  • More reports available: Browsers by Versions, Onsite Ad Impressions, Onsite Ad Clickthrough Rates, Onsite Ad Clickthroughs, Cities, Countries, Mobile Devices, Mobile Browsers (these have been removed from the list of legacy reports not supported).

Enhancements and Bug Fixes

  • Report API
    • Measure ordering: Measures in report data appear in the order in which you specified them.·
    • Multidimensional search: The get report data for period method now includes the attribute IsSearchable, so you can search all dimensions (like using the report filter in Analytics).
  • Trending for two-dimensional reports, including many built-in (legacy) reports: The Data Exchange API now provides the same trending functionality on two-dimensional reports as the Analytics user interface (where the trend is shown for the first dimension). For example, the Content Groups and Sub-groups report is not available in Version 1 of the API; in Version 2, you can get trend data on the Content Groups dimension. Note: On built-in (legacy) reports, interval data is only available for Visits and Page Views.
  • ENG367660: Reports available to user not accurate
  • ENG367545: Token names for Mobile Browser and Mobile Device reports not current
  • ENG367783: API error in Insight

I have seen a bit of an interest in the original sample application that I put together and since then there have been a number of changes to to DX API.  With the latest release of the Webtrends Analytics Insight user interface we have now update this API to V2.  Along with that I have had a chance to do additional testing of the Excel VBA application.  These two events have now led to version v6 of the Sample Application.

 

So, what’s in version 9?  Well, first of all, there are bug fixes.  I won’t detail them here, but there were a few and there are probably a few still in there. Next I have updated all of the REST calls to V2 of the API.  And, finally, I have added new functionality that will allow you to see both the Profile and Report Meta data items for any selected report.  It’s all in the documentation, so please read it for details.

 

One of the interesting updates in the V2 DX API is the inclusion of two new reports: an Account Key Metrics report and a Profile Key Metrics report.  These have been added as new reports in this Sample Application as well.

 

In keeping with my commitment to update this application on a periodic basis I have some new functionality. This new functionality adds the ability to create a "SummaryCharts" worksheet that can contain different types of graphs and charts from the data that has been downloaded via the REST URLs.  To make this even more useful there is also an option to select a function that will email this worksheet to one or more addresses at will.  This new version is v9 and it supports the v2.1 REST API.  I have removed all but the last two versions of the application from the site.

 

 

Version 10 Now Available!

 

One of the attachments below is v10.  This version adds the ability to create a single query to extract monthly data and then have the application create multiple queries for subsequent months.  This is especially helpful when you want to search for a speciifc item in a report and then trend that in detail over a number of months.  The documentation that is included provides specifics on how to do that.

 

 

So, as always, enjoy this new version of the application and any feedback you have is welcome.  The new version can be found as an embedded object in the attached documentation.  You will still find v8 and v9 attached to this post.

 

Michael Love

Senior Solutions Engineer

Recently I have been working a lot with the Webtrends DX web services here internally.  Our consulting & services group has been working with various technologies to connect, retrieve data, and provide that data in informational reports to clients and partners.  In the course of working on these various projects I have added some functionality to the Webtrends DX web services SDK and also made some fairly solid in roads on the practices used to develop applications for Excel, Outlook, and other Microsoft Office Applications.  Here is a break down of the progress I have made over the last few weeks.  I hope it is helpful in everyone's efforts to extend, report, and provide analytics data & knowledge to their respective organizations!

 

Webtrends DX SDK - The SDK provides dramatically simplified standard programmatic access to reports, profile listings, report listings, and a mapper object to provide the reports in various object formats.  Recently I have added the ability for the mapper object to take a standard dimensional report object and provide the report as a standard ADO.NET DataTable.  This dramatically simplifies a lot of the functions of sorting, finding, or specifying various parts of the report data or manipulating it for presentation.  In addition this is a more familiar object format for ETL using SSIS or even other ETL tools  out on the market.  The following is an example of the code used to retrieve a report with the SDK and then do a conversion into a standard DataTable Object.

 

User webtrendsUser =
    new User
        {
            AccountName = Resources.Options.Default.Account,
            UserName = Resources.Options.Default.Password,
            Password = EncryptionHelper.Decrypt(Resources.Options.Default.Password)
        };

var report = ReportFactory.CreateDimensionalReport("reportIdGoesHere", "profileIdGoesHere", null, null, null,
                                                   null, null, true, webtrendsUser);
Mapper mapper = new Mapper(report);
DataTable dataTable = mapper.MapReportDefinitionToDataTable();

 

In the code above, I have created a Webtrends User object and assigned the appropriate parameters.  In this case I am using the Resources (Options.Settings) available to Windows Application to store and retrieve those settings.  This way the user will not have to enter them every time they want to run the report.

 

I then have the report object being pulled from the ReportFactory Object via the CreateDimensionalReport factory method.  The parameters that are actually needed are the report ID, the profile ID, and the Webtrends User Object.  The null values are other various parameters that can be set but do not particularly need to be set.

 

Then I create a Mapper Object and pass it the report object via the constructor.  This object requires a report object upon instantiation.  I setup the object this way since there always needs to be a report object before any manipulations or mappings can be made.

 

The last step is to declare the DataTable Object, then pull the data table from the builder method MapReportDefinitionToDataTable().

 

For the SDK assembly and code check out the Document Outlining the offering.  In addition over the next couple of weeks I will be posting more information regarding how to use the SDK, best practices, and code snippets for accessing the Webtrends DX Web Services.  There will be additional blog entries and definitely some documentation coming soon (I'm working on it!).  But in the meantime, if you have any questions, comments, or ideas on how we should extend or modify the SDK please let us know here on the Webtrends Developer Forums!

 

I'll have some more entries real soon about best practices, how to code against Outlook or Excel, and more.  So subscribe and stay up to speed on everything Webtrends!  Thanks for reading.

This is the final installment of the postings on the sample application.  Up to this point we have dealt with the process of getting information about profiles and report definitions.  We have yet to get any real report data.  So, this post will deal with that process and what we do with the report results once we get them.

 

 

The first step in the process is, of course that of creating a REST URL that requests report data.  The application takes care of all of this based on the selections made by the user for such things as time periods to cover, search criteria, limiting rows, how to deal with totals, and the selection of specific measures.  There is a fair amount of work involved in making sure everything is valid, but the end result is a REST URL that looks something the one shown below.

 

 

https://ws.webtrends.com/v1_1/ReportService/profiles/YcL4a5dufF6/reports/95df19b6d9f2/?totals=all&period=2009m01d16&format=html&suppress_error_codes=true

 

 

The major difference between this URL and those we have seen in the past is that there are multiple parameters specified which control what and how much data we get back.  A second difference here is that we are also using a different data type for our results.  In this case we have chosen the “html” format rather than JSON.  When getting report results for Excel the “html” format makes it extremely easy to populate the result data into Excel.  The result data is returned as a table definition which when pasted into a spreadsheet automatically places our data into rows and columns in a tight report fashion.  In fact the result data from the REST URL above produces the results shown below.

 

 

<table border=1>
    <tr>
        <th>Time</th>
        <th>Browsers</th>
        <th>Views</th>
        <th>Visits</th>
    </tr>
    <tr>
        <td>1/16/2009</td>
        <td>  </td>
        <td>83752</td>
        <td>8941</td>
    </tr>
    <tr>
        <td>1/16/2009</td>
        <td>Microsoft Internet Explorer</td>
        <td>48488</td>
        <td>5193</td>
    </tr>
    <tr>
        <td>1/16/2009</td>
        <td>Mozilla</td>
        <td>13547</td>
        <td>1445</td>
    </tr>
    <tr>
        <td>1/16/2009</td>
        <td>Netscape</td>
        <td>5942</td>
        <td>636</td>
    </tr>
</table>

 

If you look closely at this result, you will see that we have a single table with a border (width of 1 pixel).  The table is divided into several table rows.  Each table row has a number of columns.  These hold the dimension and measure data for the report.  Also important to note are the first two rows in the table data.  The first row holds the names of the report columns.  In every report I have seen the first column has always been time.  The second row holds the “total” data.  Even when a report has no totals this row of data will be present and will contain the date value.  The remaining rows contain the report detail as specified in the REST URL request.

 

 

It is these two top rows that may need some special handling.  Suppose I have a series of queries that return report data for individual periods, say one week’s worth of data but one day at a time.  What I really want to get to here is a single report that shows each result for each day, but I only want one set of header data and I don’t want any totals since I can create those myself.  If I were to paste in the results from two successive days without making any changes to the results, I would get a spreadsheet that looks like the following.

 

 

Time

Browsers

Views

Visits

1/16/2009

 

83752

8941

1/16/2009

Microsoft Internet Explorer

48488

5193

1/16/2009

Mozilla

13547

1445

1/16/2009

Netscape

5942

636

Time

Browsers

Views

Visits

1/15/2009

 

93520

9992

1/15/2009

Microsoft Internet Explorer

54057

5821

1/15/2009

Mozilla

15601

1666

1/15/2009

Netscape

6120

635

 

 

Note that this table contains two sets of headers and two total columns.  What we really want is a result that has only one header row and no totals.  It should look like the following.

 

 

Time

Browsers

Views

Visits

1/16/2009

Microsoft Internet Explorer

48488

5193

1/16/2009

Mozilla

13547

1445

1/16/2009

Netscape

5942

636

1/15/2009

Microsoft Internet Explorer

54057

5821

1/15/2009

Mozilla

15601

1666

1/15/2009

Netscape

6120

635

 

 

This gives me a table that I can work with better for creating pivot tables, charts and appropriate totals.

 

So, how do we do this?  Well, first of all, the application allows you to define that you want this data, headers or totals, stripped off before inserting into the spreadsheet of choice.  Since we know exactly where these are in the result set, we really only need to identify which row of data to remove: headers are always in row one and totals are in row two.  The code in the application that does this is shown below.

 

 

' Check to see if we need to strip off the Date/Totals row

        If UCase(m_strIgnoreTotalDate) = "Y" Then

            m_intCheck = InStr(1, g_strResult, "</tr>")  ' Find the first end of table row - that's the header row

            If m_intCheck > 1 Then

                m_intStart = m_intCheck + 1                                                 ' Start of date/total row

                m_intEnd = InStr(m_intStart, g_strResult, "</tr>", vbTextCompare) + 4    ' End of date/total row

                ' rebuild the result without the date/total stuff

                g_strResulta = Left(g_strResult, m_intStart + 3)

                g_strResultb = Right(g_strResult, Len(g_strResult) - m_intEnd)

                g_strResult = g_strResulta & g_strResultb

            End If

        End If

       

        ' Get our data and put it on the Clipboard

        ' Check to see if we need to strip off the header row

        If UCase(m_strIgnoreHeader) = "Y" Then

            m_intCheck = InStr(1, g_strResult, "<table border=1>")

            If m_intCheck = 1 Then

                m_intStart = InStr(1, g_strResult, "<tr>")                       ' Start of the header row

                m_intEnd = InStr(1, g_strResult, "</tr>", vbTextCompare) + 4     ' End of the header row

                ' Rebuild our result without that stuff

                g_strResulta = Left(g_strResult, 16)

                g_strResultb = Right(g_strResult, Len(g_strResult) - m_intEnd)

                g_strResult = g_strResulta & g_strResultb

            End If

        End If

 

 

The right approach is to first look to see if we want to remove the “Total” row and do that.  The reason for this is that we know this data is in row two.  If we were to delete the header row first, the total row would move to the first row and would complicate the logic.  So, the easy way to do this once we have decided that removing it is the thing to do, we simply look for where that row starts and ends.  The start position is simply the first character after the end of the first row and the end is the last position of the “</tr>” string.  Once we calculate those two values we simply recreate our result to copy the data up to the start and after the end of that row.

 

 

The same basic approach is taken for the header row.  We know exactly where the start of that row is because we know the number of characters leading up to it.  We then calculate the end in the same way we did for the totals row except that we are only looking for the end of this first row.  Once we have these values calculated, we can again recreate the result set to include only the data prior to and after the first row of data.

 

 

Once we have the result set that is needed for our spreadsheet the only real task left is to decide where to put it.  By that I mean what worksheet and which cell.  The cell we need is the top left cell.  Then we paste our data in there and we are done.

 

 

I hope these posts have provided useful information.  Whether you choose to use this sample application or to write your own my goal here has been to provide some insight into how one can approach using the REST URLs with Excel and VBA.  I will continue to work on this application with bug fixes (I am sure there are a few) and new functionality.  Eventually I will add the trend reports and will support future versions of the API.

 

 

As always, I welcome your comments.

 

 

Michael Love

Senior Solutions Engineer

The previous posts on this application have provided some insight on how to get general profile and report data.  We have also parsed the results that were returned assuming that we used the JSON data format.  So now we are just about ready to create a REST URL that will allow us to fetch some actual report data.  But before we do there is one last task to be performed and that is getting a list of measures that are available for our desired report.  We need to do that just in case we want to limit the measures return to specific measures rather than all.  One other element we are interested in knowing is whether or not we can use the “search” functionality to limit the results based on contents in the dimension data.  Whether or not we can do that is determined by whether or not the report is multi-dimensional or hierarchical.

 

 

The REST URL to make this request to get what is referred to as the report meta data is shown below.  This URL clearly defines both the Profile and the Report desired via their ID values.  The “info” field defines this as a request for the report meta data.

 

 

     https://ws.webtrends.com/v1_1/ReportService/profiles/YcL4a5dufF6/reports/95df19b6d9f2/info/?format=json

 

 

This request is made in the same way that previous requests are made and were discussed in my prior posts.  The resulting data in JSON format will look something like the data below.  Remember, however, that there are no spaces or other extraneous characters returned.  These are shown here to make this easy to read.

 

 

{
    "accountID" : 18079,
    "profileID" : "YcL4a5dufF6",
    "ID" : "95df19b6d9f2",
    "name" : "Browsers",
    "language" : null,
    "type" : "dimensional",
    "properties" : {
        "isHierarchy" : false,
        "intervalsEnabled" : false,
        "internalID" : "topbrowsers_v",
        "IsRealTimeCompatible" : true,
        "ProfileCategory" : null
    },
    "dimension" : {
        "ID" : "Desc",
        "name" : "Browsers",
        "type" : "data",
        "Range" : null,
        "Properties" : null,
        "SubDimension" : null
    },
    "measures" : [
        {
            "name" : "Views",
            "accumulationType" : null,
            "ID" : "Hits-0",
            "columnID" : 0,
            "measureFormatType" : "numeric",
            "AllowTotals" : true
        },
        {
            "name" : "Visits",
            "accumulationType" : null,
            "ID" : "Users-0",
            "columnID" : 0,
            "measureFormatType" : "numeric",
            "AllowTotals" : true
        }
    ]
}

 

 

The entire result set is enclosed in curly brackets – “{  }”.  There are within this set of data three (3) main segments of data.  The first segment is general report information.  Here we have such information as the Profile ID, the Report ID, the Report Name, and other general properties.  It is here that we find a couple of critical pieces of data. These are both in the properties area: isHierarchy”, and "intervalsEnabled".  The “isHierarchy” value tells us whether or not we can use the search functionality.  A value of “false” indicates this is a single dimension report and that we can do this.  On the other hand, a value of “true” indicates that the report contains more than a single dimension and that we cannot use the search functionality.  The second property item that may be of interest to us is the "intervalsEnabled" item.  The value of this item tells whether or not trends data is available and we can use those time periods to retrieve data. Again, a value of “false” means interval/trend data is not available and “true” means that interval/trend data is available.  This sample application has not implemented or enabled this feature, so for our discussion here the point is moot.

 

 

The second segment of data in our result is a list of dimensions.  In a single dimension report this list is obviously short and appears as shown above.  In a multi-dimensional report the sub dimensions are listed as shown below.

 

 

"dimension" : {

        "ID" : "contentgroup",

        "name" : "Content Group",

        "type" : "data",

        "Range" : null,

        "Properties" : null,

        "SubDimension" : {

            "ID" : "xIfaV44n7l5",

            "name" : "Content Sub-Group",

            "type" : "data",

            "Range" : null,

            "Properties" : null,

            "SubDimension" : null

        }

    },

 

 

Note that each sub dimension is listed as a hierarchical item of the dimension above it.

 

The key set of data that we want out of this result data is the list of measures.  The measures are all in a third segment bounded by square brackets – “[  ]”.  Each individual measure is defined within its own space bounded by curly brackets – “{  }”.  For each measure there are a number of definition items including, "name", "accumulationType", "ID","columnID", "measureFormatType", and "AllowTotals".  For purposes of our application we only need two of these values: “name” and “ID”.  The name filed is used for display to the user in the interface and the ID is used when we create the REST URL to request our data.  In the application we store this data in a custom data type.  This data type is shown below.

 

 
Public Type MEASUREDATA
    name As String
    id As String
    accumulationType As String
    columnID As String
    measureFormatType As String
    allowTotals As String
End Type
Public g_Measures() As MEASUREDATA
 

The function below is used to extract the data we need from the JSON result.  Once extracted the list of measures is available for the user to select. A check is also made to either enable or disable the use of the “search” functionality based on this being a single or multi-dimensional report.

 

 

Public Sub GetReportMeta()

   

    ' Get Report Meta so we can have list of measures

    Dim m_intWhere As Integer

    Dim m_strIsHier As String

   

    frmProfileList.lstMeasures.Clear                       ' Clear form we will display list on

    g_intMeasureCnt = 0                                    ' Initialize the profile count to 0

       

    ' Check to see if the user has selected the Profile Summary

    If frmProfileList.lstReports.ListIndex = frmProfileList.lstReports.ListCount - 1 Then

        frmProfileList.optMultiple.Enabled = False

        frmProfileList.optSingle.Enabled = False

        frmProfileList.optSEPeriods.Value = True

        Exit Sub                ' Exit this sub because there are no choices for measures

    Else

        frmProfileList.optMultiple.Enabled = True

        frmProfileList.optSingle.Enabled = True

        frmProfileList.optSEPeriods.Enabled = True

    End If

   

    ' Set up URL for fetching Report Meta

    g_strURL = g_strBaseREST & g_strSelectedProfileID & g_strReportsREST & g_Reports(frmProfileList.lstReports.ListIndex).id & g_strReportsMetaREST

   

    ' Call function to execute REST URL

    If Not RunREST(g_strURL, g_strUserName, g_strPassword) Then

        Exit Sub

    End If

       

    ' Check to see if the report is hierarchical - no search on those

    m_intWhere = InStr(1, g_strResult, "isHierarchy")                ' Find start point for measures list

    m_strIsHier = Mid(g_strResult, m_intWhere + 13, 4)

    If m_strIsHier = "true" Then

        frmProfileList.txtSearch.Enabled = False            ' disable text entry in search box

    Else

        frmProfileList.txtSearch.Enabled = True             ' enable text entry in search box

    End If

   

    ' Display the JSON URL Result

    m_intWhere = InStr(1, g_strResult, "[")                ' Find start point for measures list

    g_strResulta = Mid(g_strResult, m_intWhere + 1, Len(g_strResult) - (m_intWhere + 2))

   

    g_blnMore = True

    Do While g_blnMore

        g_intSplit = InStr(1, g_strResulta, "},")                 ' See if there is more than one entry

        If g_intSplit = 0 Then

            g_strResultb = Mid(g_strResulta, 2, Len(g_strResulta) - 2)

        Else

            g_strResultb = Mid(g_strResulta, 2, g_intSplit - 2)   ' Grab the first set of data

        End If

        g_strResultb = Replace(g_strResultb, """", "")            ' Get rid of the extra " characters

        g_strArr = Split(g_strResultb, ",")                       ' Create an array with this set of data

        ReDim Preserve g_Measures(g_intMeasureCnt)                ' Make room for new measure

        g_Measures(g_intMeasureCnt).name = Mid(g_strArr(0), 6)           ' Store the measure name

        frmProfileList.lstMeasures.AddItem g_Measures(g_intMeasureCnt).name    ' Add name for selection from form

        g_Measures(g_intMeasureCnt).id = Mid(g_strArr(2), 4)             ' Store the Measure ID

        g_Measures(g_intMeasureCnt).accumulationType = Mid(g_strArr(1), 18) ' Store the Accumulation Type

        g_Measures(g_intMeasureCnt).allowTotals = Mid(g_strArr(5), 13) ' Store the Allow Totals value

        g_Measures(g_intMeasureCnt).columnID = Mid(g_strArr(3), 10) ' Store the Column ID

               

       

        ' Check to see if we are done parsing

        If g_intSplit = 0 Then

            g_blnMore = False                             ' We're done

        End If

        g_strResulta = Right(g_strResulta, Len(g_strResulta) - g_intSplit - 1)   ' Set up for next profile

        g_intMeasureCnt = g_intMeasureCnt + 1               ' Bump the profile count

    Loop

   

    Application.Cursor = xlNorthwestArrow           ' Set the application cursor back to normal (arrow)

   

End Sub

 

 

So, now we have all of our data available in the user interface for the user to select what data will be included in the requested report.  The three keys areas of selection are the time periods, the search data (if available), and the selected measures.  Other selections include limiting what rows to return and how to deal with report totals.  I won’t go into all of the logic on these selections and the interactions, but there are certain checks that are made to ensure consistency and validity of the selected items.  But once these selections are made the user can create the URL and then copy it to the worksheet for eventual execution.

 

 

That’s all for this post.  The next and last post on this application will deal with processing the results of the report data requests.

 

Michael Love

Senior Solutions Engineer

This second post on dissecting the Sample Application deals with the process of requesting two separate, but related, sets of report data: the List of Available Reports for a Profile and the List of Report Periods for which data may be available.  I say “may be available” because it is possible that a report may not have existed when the Profile initially began analyzing.  A custom report may have been added some time after that initial set up process.  In that case the report would not have any data until when it had been enabled for the profile and the report template(s) available to the user.  This point really has nothing to do with the application.  I provided this comment only as a point of report data clarification.

 

 

The process in place within the application is that these two data requests will take place when the user clicks on a Profile Name in the combo list.  The request for Report List is made first followed by the List of Periods.  There is nothing that requires that one be done before the other; it’s just the sequence in which I chose to do them.  The REST URL to request a Report List looks like the following:

 

 

https://ws.webtrends.com/v1_1/ReportService/profiles/YcL4a5dufF6/reports/?format=json

 

 

Note that again I use the JSON data format to keep down the amount of data returned.  The process used to create this URL and send it is much like that used for the Profile List.  The difference, of course, is in the format of the REST URL where we need to include the Profile ID and then add the fields to identify the request as one for the Report List.

 

 

So, the data format that I get back looks very similar to the format used for the Profile List.

 

 

[

{"accountID":18079,

"profileID":null,

"name":"Creatives",

"ID":"WcTGeY1bNj5",

"language":null,

"type":null,

"Category":"Marketing",

"IsHierarchy":false,

"IntervalsEnabled":false,

"IsRealtimeCompatible":false,

"properties":null

},

{

"accountID":18079,

"profileID":null,

"name":"Creative Types",

"ID":"uZK6QjyBNj5",

"language":null,

"type":null,

"Category":"Marketing",

"IsHierarchy":false,

"IntervalsEnabled":false,

"IsRealtimeCompatible":false,

"properties":null

}

]

 

 

The formatting here has been added only to make reading the data easier.  The JSON format returns one long string bounded by square brackets – “[  ]”.  Within these brackets each report is detailed within a set of curly brackets – “{  }”.  Within those brackets are the individual meta items that define the report in a general format.  The two items that are really important to us here are the “name” and the “ID”.  The application parses this data using the following code.

 

 

' Display the JSON URL Result

    ' We need to parse out the results to get the data we need

    frmProfileList.lstReports.Clear                  ' Clear form we will display list on

    g_intReportCnt = 0                               ' Initialize the profile count to 0

    g_strResulta = Mid(g_strResult, 2, Len(g_strResult) - 2)       ' Strip the [] characters from each end

    g_blnMore = True

    Do While g_blnMore

        g_intSplit = InStr(1, g_strResulta, "},")    ' See if there is more than one entry

        If g_intSplit = 0 Then

            g_strResultb = Mid(g_strResulta, 2, Len(g_strResulta) - 2)  ' Grab the enry when only one left

        Else

            g_strResultb = Mid(g_strResulta, 2, g_intSplit - 2)     ' Grab the first set of data

        End If

        g_strResultb = Replace(g_strResultb, """", "")    ' Get rid of the extra " characters

        g_strArr = Split(g_strResultb, ",")               ' Create an array with this set of data

        ReDim Preserve g_Reports(g_intReportCnt)

        g_Reports(g_intReportCnt).id = Mid(g_strArr(3), 4)                      ' Store the Report ID

        g_Reports(g_intReportCnt).name = Mid(g_strArr(2), 6)                    ' Store the Report Name

        g_Reports(g_intReportCnt).accountid = Mid(g_strArr(0), 11)              ' Store the Account ID

        g_Reports(g_intReportCnt).profileid = Mid(g_strArr(1), 11)              ' Store the Profile ID

        g_Reports(g_intReportCnt).language = Mid(g_strArr(4), 10)               ' Store the Language

        g_Reports(g_intReportCnt).type = Mid(g_strArr(5), 6)                    ' Store the Report Type

        g_Reports(g_intReportCnt).category = Mid(g_strArr(6), 10)               ' Store the Report Category

        g_Reports(g_intReportCnt).ishierarchy = Mid(g_strArr(7), 13)            ' Store Hierarchy vlaue

        g_Reports(g_intReportCnt).intervalsenabled = Mid(g_strArr(8), 18)       ' Store Interval enablement

        g_Reports(g_intReportCnt).isrealtimecompatible = Mid(g_strArr(9), 22)   ' Store Realtime

        g_Reports(g_intReportCnt).properties = Mid(g_strArr(10), 12)            ' Store Properties

       

        ' Check to see if we are done parsing

        If g_intSplit = 0 Then

            g_blnMore = False                             ' We're done

        End If

        g_strResulta = Right(g_strResulta, Len(g_strResulta) - g_intSplit - 1)   ' Set up for next report

        g_intReportCnt = g_intReportCnt + 1               ' Bump the report count

    Loop

   

    ' Sort the report list arrays

    SortReports

   

    ' Load Report Names into form

    For i = LBound(g_Reports) To UBound(g_Reports)

        frmProfileList.lstReports.AddItem g_Reports(i).name     ' Add name for selection from form

    Next

   

 

The parsing process here is almost identical to what was done for the List of Profiles, the exception being the actual fields of data that are extracted and stored.  To store the data I have again set up a specific data type for the report data.  It looks like the following:

 

 

Public Type REPORTDATA

    accountid As String

    profileid As String

    name As String

    id As String

    language As String

    type As String

    category As String

    ishierarchy As String

    intervalsenabled As String

    isrealtimecompatible As String

    properties As String

End Type

Public g_Reports() As REPORTDATA

 

 

While this application does not yet use this, the “intervalsenabled” value is used to determine whether or not the trends option for report is valid.

 

Prior to loading the report names into the combo list on the form the application will sort that list into alphabetical sequence.  Unfortunately, the list returned from the request does not do that for you.  Using the custom data type here makes that sorting process easier because all of the report information I kept together.

 

 

The next step in the process here is to get the List of Periods that are available.  This is yet another REST URL format for this set of data.  This REST URL has the following format:

 

 

https://ws.webtrends.com/v1_1/ReportService/profiles/YcL4a5dufF6/periods/?format=json

 

 

This REST URL is almost identical to the Report List request with the one exception that it has the “periods” data request defined.  As before, we still use the JSON format for our data type.  This request is submitted just like all of the others we have done.  The format of the result data is shown in the following:

 

 

{"Report":["2009m01d04","2009m01d05","2009m01d06","2009m01d07","2009m01d08","2009m01d09","2009m01d10","2009w02","2009m01d11","2009m01d12","2009m01d13","2009m01d14","2009m01d15","2009m01d16","2009m01d17","2009w03","2009m01","2009q01","2009"],"Realtime":[“2009m01d18”]}

 

 

This format is a bit different than what we have seen with Profiles and Reports.  The outside delimiters in this case are the curly brackets – “{  }” – and the inside delimiters are the square brackets – “[  ]”.  In parsing this data all we care about are the individual periods.  The application, at this time also does not deal with the “Realtime” or express data.  That data will be ignored in this version of the application.  So, all we need to parse are the comma-delimited values, without the quote marks that are contained inside the first set of square brackets.  The code to do that looks like the following:

 

 

' Parse out the URL Result - set into individual time periods

    If InStr(1, g_strResult, "Realtime") Then

        g_strResultb = Mid(g_strResult, 12, (InStr(1, g_strResult, "Realtime") - 15)) ' Need to account for profiles with Express Data

    Else

        g_strResultb = Mid(g_strResult, 12, Len(g_strResult) - 29)    ' No express data

    End If

    g_strResultb = Replace(g_strResultb, """", "")            ' Get rid of the extra " characters

    g_strPeriods() = Split(g_strResultb, ",")                 ' Create an array with this set of data

       

    ' Load data onto Report Periods Form

    frmProfileList.lstPeriods.Clear                       ' Start with empty list

    For i = 0 To UBound(g_strPeriods)

        ReDim Preserve g_strReportPeriod(i)

        frmProfileList.lstPeriods.AddItem g_strPeriods(i)   ' Save in the form

        g_strReportPeriod(frmProfileList.lstPeriods.ListCount - 1) = g_strPeriods(i)    ' Save in our array for use later

    Next

 

 

This is fairly straightforward code to do this.  However, there are a few other items that we need to add to the list for the user to pick for time periods.  These are the relative starting point of “current day”, “current month”, and “current year”.  So these are manually added to our pick list since they have no representation in the list of periods returned.  The code to do that is as follow:

 

 

' Add relative time periods at end of form and array

    ReDim Preserve g_strReportPeriod(i + 3)

    frmProfileList.lstPeriods.AddItem "Today"

    g_strReportPeriod(frmProfileList.lstPeriods.ListCount - 1) = "current_day"

    frmProfileList.lstPeriods.AddItem "This Month"

    g_strReportPeriod(frmProfileList.lstPeriods.ListCount - 1) = "current_month"

    frmProfileList.lstPeriods.AddItem "This Year"

    g_strReportPeriod(frmProfileList.lstPeriods.ListCount - 1) = "current_year"

 

 

So, that ends this portion of the blog post.  In the next blog post we will be getting additional report meta information and dealing with creating a REST URL to fetch actual report data.

 

So, until the next posting,

 

Michael Love

Senior Solutions Engineer

Recently I posted a blog entry with a Sample Excel/VBA Application.  The idea behind this application was to provide any developer who is interested a baseline set of code for using the REST API for extracting data from Webtrends Analytics.  The latest version of that application is v4 and it supports the v1.1 REST URL API.  This new version is attached to this blog.  Please note that the attachment is a Microsoft Word document.  The application which is and Excel spreadsheet with VBA driving the action is included in that document as an embedded file.

 

I have decided to follow up that posting with more detail on how that application works.  This follow up work will come in a series of postings that sort of start at the beginning of the application and works its way through to the end.  The focus will be on the process of building the REST URLs and then parsing the resultant data from each call.

 

The sample application assumes that there are no REST URLs to begin with and that the user of the application will need to create them interactively.  This aspect of the application builds off the idea of the REST Generator application at http://generator.webtrends.com.  To make this work requires a series of steps and calls to Webtrends to fetch specific data.  I am assuming for purposes of this discussion that the reader has a fair knowledge of VBA with Excel and I will, therefore, not go into detail on every line of code in this application.  The code is documented on a line by line basis.

 

So, let’s start with the sequence in which the application gets its data and what is does with it.  To dynamically, interactively, generate a REST URL that requests report data we need to do the following:

 

1.       Get a list of the profiles available to us.

2.       Get a list of the reports that exist in the desired profile and the time periods that might exist for the reports.

3.       Get the report data.

 

There’s a bit more detail involved in these and I will cover that as I cover each of the mail points in the list above.  This posting will cover just the process involved in requesting a list of profiles.

 

I will warn you now that I am a brute force programmer and you may have better ideas than I do about specific functions and how they work.  That’s good and I welcome any feedback you have.

 

Let’s begin by looking at the general format for a valid REST URL.  For more detail on this I refer you to the Webtrends developer site at http://product.webtrends.com/dxapi/api_description.html.  The general REST URL has the following format:

 

https://ws.webtrends.com/v1.3.1/ReportService.svc/profiles/faj38jaFH/reports/J893kIL34/?period=2009&format=xml

where,

https: = protocol

ws.webtrends.com = site where data request is sent

v1.3.1 = API version number

ReportService.svc = web service called

Faj38jaFH = profile id

J389kIL34 = report id

period=2009 = report period

format=xml = result data format

 

 

The actual format of the REST URL you will use will depend heavily on what the request is targeted to return.  For a list of profiles the REST URL will look like the following:

https://ws.webtrends.com/v1_1/ReportService.svc/profiles/?format=xml

or,

https://ws.webtrends.com/v1_1/ReportService.svc/profiles/?format=json

 

The difference here is the format in which the result information is returned.  In the sample application I have used JSON.  So, this is the format I will reference in this post.

 

Using VBA to request web data in this form is a simple process.  In fact, in the sample application I have included a standard function to make this retrieval, no matter what data is being requested.  This function looks like this:

 

Private Function RunREST(url As String, ByRef username As String, ByRef password As String)

 

    ' Function will execute a REST url along with the UserName and Password passed as parameters

    Application.Cursor = xlWait                                  ' Set the Wait cursor while executing the REST Call

   

    ' Set up the object needed for the web service request

    Set g_objHttp = CreateObject("MSXML2.XMLHTTP")

   

    ' Open our connection and pass the user credentials

    g_objHttp.Open "GET", url, False, username, password          ' False indicates the call is synchronous - wait for URL Result

    g_objHttp.send                                                ' Send the request

    g_strResult = g_objHttp.responseText                          ' Get the URL Result

   

    ' Check to see if our credentials passed - display error when they don't

    If g_strResult = "Must authenticate" Then

        Application.Cursor = xlNorthwestArrow                     ' Reset the application cursor to normal (arrow)

        MsgBox "Authentication Failed", vbCritical, "Authentication Error"

        username = ""                                             ' Reset User Name

        password = ""                                             ' Reset Password

        RunREST = False                                           ' Set return value for failure

        Exit Function                                             ' Exit - we're done here

    End If

   

    RunREST = True                                                ' Set return value for success

   

End Function

 

 

The key statements in this function really are those that do the actual call of the REST URL.  They are:

 

 

g_objHttp.Open "GET", url, False, username, password

g_objHttp.send

g_strResult = g_objHttp.responseText

 

 

The g_objHttp.Open statement creates an open connection for the HTTP request.  Asscociated with that statement we pass the type of call, “GET”, the actual URL to be used with the call, and the user name and password.  The parameter shown as “False” indicates how this call is to be handled by the application.  The value “False” indicates that it is a synchronous call, meaning that the application will wait for the response before continuing.  Passing the user name and password prevents the user from being challenged when the call is made.  The g_objHttp.send statement does just what is implied – it sends the URL request via the web.  The last statement – g_strResult = g_objHttp.responseText – assigns whatever the result of the request is to our internal variable for further analysis.

 

 

The remaining statements in this function simply check to determine whether or not the authentication credentials had been accepted or rejected.  If either the user name or password were not valid, the result returned will simply state “Must authenticate”. That’s what this code checks for and displays an appropriate error and returns ‘False” as the function value.

 

Now that we have a result set it is time to do something with the data.  Remember that the data is returned in JSON format.  The JSON format for a list of profiles looks something like this:

 

 

[{"ID":"YcL4a5dufF6","name":"...sample: Zedesco 2009","AccountID":18079},

{"ID":"CxoSsHxKlF6","name":"..Sample: ExactTarget 2009","AccountID":18079},

{"ID":"1ZnDMawXD16","name":".sample: Z-2005","AccountID":18079},

{"ID":"1To2KI7wD16","name":"Sample: ET-2005","AccountID":18079}]

 

 

The paragraph formatting has been added to make this more readable.  The actual result is returned as one log string.  The entire string is bounded by square brackets – [].  Each individual set of profile data is bounded by curly brackets – {}.  Eand each of these is separated by a single comma.  Each set of profile data has three name/value pairs.  The names are: “ID”, “name”, and “AccountID”.  Not that these are all surrounded by double quotes and also separated by commas.  So parsing this data out should not be any serious challenge.  Using my brute force coding style I accomplish this with this set of statements:

 

 

' Display the JSON URL Result

    ' We need to parse all of the results to get what we want

    frmProfileList.lstProfiles.Clear                           ' Clear form we will display list on

    g_intProfileCnt = 0                                        ' Initialize the profile count to 0

    g_strResulta = Mid(g_strResult, 2, Len(g_strResult) - 2)   ' Strip the [] characters from each end

    g_blnMore = True

    Do While g_blnMore

        g_intSplit = InStr(1, g_strResulta, "},")                 ' See if there is more than one entry

        If g_intSplit = 0 Then

            g_strResultb = Mid(g_strResulta, 2, Len(g_strResulta) - 2)  ' Only one entry - grab it

        Else

            g_strResultb = Mid(g_strResulta, 2, g_intSplit - 2)   ' Grab the first set of data

        End If

        g_strResultb = Replace(g_strResultb, """", "")            ' Get rid of the extra " characters

        g_strArr = Split(g_strResultb, ",")                       ' Create an array with this set of data

       ReDim Preserve g_Profiles(g_intProfileCnt)

        g_Profiles(g_intProfileCnt).id = Mid(g_strArr(0), 4)        ' Save the Profile ID

        g_Profiles(g_intProfileCnt).name = Mid(g_strArr(1), 6)      ' Save the Profile Name

        g_Profiles(g_intProfileCnt).accountid = Mid(g_strArr(2), 11)    ' Save the Profile Account ID

       

       

        ' Check to see if we are done parsing

        If g_intSplit = 0 Then

            g_blnMore = False                               ' We're done

        End If

        g_strResulta = Right(g_strResulta, Len(g_strResulta) - g_intSplit - 1)   ' Set up for next profile

        g_intProfileCnt = g_intProfileCnt + 1               ' Bump the profile count

    Loop

   

    ' Sort the resulting array into sequence

    SortProfiles

   

    ' Now load the form with the Profile Names

    For i = LBound(g_Profiles) To UBound(g_Profiles)

        frmProfileList.lstProfiles.AddItem g_Profiles(i).name              ' Add name for selection from form

    Next

 

 

If you look for this code in the application, you will find it in the “GetProfiles” subroutine.  One of the methods I used to help store the profile data was to set up a specific data type for it.  That data type, found in the Data_Module module looks like this:

 

 

Public Type PROFILEDATA

    name As String

    id As String

    accountid As String

End Type

Public g_Profiles() As PROFILEDATA

 

 

Creating this data type makes it easy to keep all of the profile data for one profile together and then be able to sort the data into “name” sequence.  When we present the profile data to the end user we use the Profile Name.  When we use the profile data to later request a list of reports we will need the corresponding Profile ID.

 

The next blog post will cover creating a REST URL to retrieve a List of Reports and what do with that result.

 

Michael Love

Senior Solutions Engineer

To use v1.1 features, specify v1_1 in your requests. This URI uses version 1.1:

 

https://ws.webtrends.com/v1_1/ReportService/profiles/YcL4a5dufF6/reports/RZ2YgglpHk5/info?format=xml

 

The prior version is still available*, but we encourage you to move quickly to adopt the new version, so that our enhancement efforts are driven by your feedback.

This URI uses version 1:

 

https://ws.webtrends.com/v1/ReportService/profiles/YcL4a5dufF6/reports/RZ2YgglpHk5/info?format=xml

 

Changes made in version 1.1 are flagged in the documentation.

 

* Note: URIs produced with Generator v1.1 are not compatible with the Data Extraction API v1.0. If you need to produce URIs for version 1.0, then you can access Generator v1.0 here:

https://generator.webtrends.com/v1

 

Additions and Changes

 

  • XML parsing:  Additional metadata is defined for some views in the XML format (such as the profile summary method), so data collected with XML parser code that uses position, rather than the XML tag name, may not be positioned as before when using v1.1. Webtrends recommends as a best practice to use the XML tag name, rather than position, in XML parsing, so this is a good time to change position-dependent parser code to use tag names.
  • Combining data from different accounts:  When you log in to a parent account, you can query profiles from any sub-accounts that you have   permission to access. Visibility into profiles is no longer restricted to profiles in the account to which you authenticate in your initial session. You can now combine data from profiles in different accounts, specifying an account with the account name parameter.
  • Templates and report access:  The Data Exchange API now restricts access to data based on permissions and on whether a report is included in the templates available to you.
  • New XML2 format: “XPath-friendly” output is now available (useful in dynamic applications).
  • Measure totals: Additional choices for returning totals.
  • Changes  to the Generator utility: Added XML2 and CSV formats and new totals functionality. Removed charting and mobile routing functionality.

Bug Fixes and Enhancements

 

  • ESC365229: The profile summary report has one changed measure and one new measure:
    Changed "Average per Day" to "Average Page Views per Day"
    Added "Average Visits per Day"
  • ENG363413: Profile and report permissions error in Generator.
  • Data Extraction API numbers are returned directly from the analytics engine without manipulation, so they are the same as those in Webtrends reports. Discrepancies that still exist are due to calculations performed by the Webtrends user interface (rounding, averages, and calculated measures).
  • Search string filters on reports are now applied to both dimensions.
  • Reports including the Pages dimension now return page titles as a data column, along with the URI (formerly, the page title was omitted).

Known Issues

  • ENG365353: The list templates method may not produce expected results.
  • ENG365139: The Languages and Java Versions reports may not produce expected results when using the search parameter, since actual values differ from display values (“en” in the data, but “English” is displayed).
  • ENG364956: Comparison of certain measures (particularly averages and subtotals) may reveal differences between v1 and v1.1 of the API. v1.1 is considered more accurate, because the data comes directly from analysis databases.
  • ENG365121: Incorrect or partial data is returned (values contain percent symbols).
  • ENG365341: Column aggregates on the geography drilldown in Excel are incorrectly displayed.
  • ENG365425: Report and template listings do not reflect the user’s view permissions.

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

The very clever Webtrends Open Campaign is all about transparency.  We are showcasing our technologies, along with creative solutions from some of our partners, to demonstrate what's possible using data of all shapes and sizes.  From our site:

Have a look around the web site. Get to know the people and partners involved. And come back often. We'll explore our results and introduce new marketing strategies every week.

Picture 1.png

 

One post early on in the blog was from our own Robin Balmer, who has put together some visualizations around metrics from the campaign.  He's pulling data from our DX API, and put a flash visualization together to present a trend of views and visits over time.  Simple, elegant and relatively easy to do as he notes in his blog post:

 

 

I set this task of visualization-building as a fun programming challenge for myself and as a means to get the visualizations to work stylistically with the design of the web site. But you don’t have be an experienced coder to get at Webtrends data - you can use the API to pull data right into Excel and analyze or visualize it from there. I saw some great examples of this during Webtrends’ last Developer Day.

Nice work Robin!

WT.gifWe are pleased to announce the general availability of our Webtrends Data Extraction API.  This v1 release is the culmination of a ton of effort of many individuals inside Webtrends, as well as the terrific support from our partners and customers who have provided feedback and suggestions during our beta process.  Thank you all for helping us reach this milestone.

 

This open, innovative API service, provides you with elegant access to your powerful Webtrends analytics data.  This is the first time we have leveraged a full beta cycle as part of our iterative development process.  It is also the first time we've reached out to our community, via this Developer Network, to solicit feedback and validate our approach.  It's been a huge win for us.

 

What's new?

Recently, we've been working on some behind the scenes governance (security and usage controls), and focusing on fixing bugs/issues.  We've also modified the HTML and CSV formats slightly to return data back in a more easy-to-consume structure, per your suggestions.  These changes, along with some additional functionality has made this API much easier to use directly within Excel, making it the only API in the industry that is powerful enough for sophisticated applications, while easy enough to also be used by everyone via Excel.

 

We've launched the formal "/v1/" version structure into the URL.  If you are using the "/beta/" request in your URL, you should switch over to "/v1/".  We'll continue to use the "/beta/" structure for future updates, so please don't use it for any production services or applications.  Please consult our documentation for the most current information on the API.

 

What's next?

We are going to continue to iterate rapidly on this Data Extraction platform.  It's already become very popular with our customers and partners as it is so easy to use.  Look for a regular maintenance release on a monthly basis, along with a 3-4 month cycle of releases that will include awesome new features.

 

We will continue to use this Developer Network for communications, ideas, and general sharing of information.  As always, please feel free to login and post questions, respond to other questions, or post ideas you have.  Your creativity and innovation is contagious.  It's all good.

Filter Blog

By date:
By tag: