WebTrends Engage 09 – London – ODBC Data Extraction with Tim Clement

• Web Analytics
• October 21, 2009

I’m sure most of the UK companies will have met or heard of Tim Clement.  Come to think about it I even think he’s done a stint for WebTrends in Australia.  As the event magazine states he’s a father of three and can be regularly found at weekends on a football pitch cheering on his kids.  Tim is a technical architect / solutioner and has always been at the forefront of driving innovative solutions.  He was talking about the how to extract the information out of WebTrends for executive dashboards or additional analysis.

Everyone knows that web analytics isn’t easy (despite @vineshc’s daily mantra of “web analytics is easy”) ensure that the tags are right and that they are collecting the right information is crucial.  However, also is getting the reports to tell you the truth of the site (and understanding any discrepancies).  I’ve also spoken about the notes feature that Casey Carey was promoting in his keynote speech and also the news RSS feed which is also available in WebTrends Analytics 9.

However, this interface, despite how easy it has been made, even with being built according to strict web standards allowing cut and paste of tables, will not be logged into by the senior decision makers.

If you give a senior manager access to WebTrends and say – “there you go, everything you need is in there”, you are running the risk of managers going “This tool is unworkable it doesn’t easily give me the information I want!”.

Data extraction and Dashboards are the way forward.  Tim Clement has helped many companies achieve this and yes he’s even taught yours-truely a thing or two.

Main Ways to extract information from WebTrends:

ODBC Driver – [great for dashboard building]

REST API – [great for dashboard building]

Web Services

Word / PDF / CSV [best for the odd extraction request from other teams].

The ODBC Driver is a plugin which allows you to extract the information into Excel and Access.  This is a fantastic tool because not only can you set up multiple connections you can automate it using VBA in a very very easy way.  Unfortunately, it can break down occasionally and I was pleased to hear that I wasn’t the only analyst that experienced problems migrating customers from Excel 2003 to Excel 2007.  The benefit of using this is you can use strored queries or the Microsoft Query tool which is an Excel feature giving you an Access-esque interface dragging table dimensions into a query and adding criteria.

I’ll use Excel 2007 as an example.  Select the Data tabe from the top of the menu and then ‘Other Data Sources’.  I’ve included a screengrab below.   Please note that you will need to have created the DSN in order to access the backend data.  I recommend also that a specific administration account is set up for access to the data.  This is in case the individual that builds dashboards leaves and has to have his username and password removed from the system and then subsequentally all DSNs will have to be changed and if the excel dashboard is shared – could prove to be rather embarressing.

Once you’ve selected the appropriate DSN [see below] then you will be given a list of all the dimensions and measures in that table that you can include in the report.   Remember to only include those items that you need as if including all measures can increase processing time and delay the production or refresh of the dashboard.

I find that then the best way to continue is to click through the rest of the screens and then manage the data manipulation (usually date driven) through the Microsoft Query criteria box – see below.

By viewing the results in Microsoft Query you are able to add criteria e.g. “last month’s” data versus the same period last year (but this would take two queries).  This not only gives you the information you require but also gives you a comparision time period.

Within the criteria window (its ACCESS-esque) you’ll be able to put in the necessary criteria. When designing dashboards dont be worried about how many queries you create, but do make your customers aware that the more queries the higher the chance that it will take longer to process.

The REST API is something new to me and I will blog about that later.  But it uses web services and is very easy to use.

Related Linked:

Data Extraction Criteria Documentation

http://generator.webtrends.com

http://developer.webtrends.com

WebTrends Home Page and Client Login

Dashboards Data Extraction Engage09 Tim Clement Web Analytics WebTrends