INTRODUCTION

One of the least publicized but most exciting features in the newest version of WebTrends is the addition of a direct connection to WebTrends data.  WebTrends has now given all their customers, not just those who buy the Marketing Lab, access to their data from all the WebTrends reports.

 

Having a connection to the data is of limited use, however, unless you have the right tool to efficiently connect to and query the data and work with it in a familiar environment.  WebTrends customers have long favored Excel as the reporting and analysis tool of choice and now you can make full use of the new WebTrends connector while feeding your addiction to Excel by using DataLinks from Business Intelligence Inc. 

 

Imagine being able to update your Excel reports and analyses with current WebTrends data at the click of a mouse!  No more CPT (Copy/Paste Technology)!  With DataLinks you can even join data across different WebTrends reports or profiles and create a consolidated report in Excel!  Since DataLinks also connects easily to virtually any other data source you can also use it to combine data from your CRM, ERP or other systems with your WebTrends data, all in the familiar working environment of Excel! 

 

If you are intrigued by the possibilities, we encourage you to read on to learn how to install and configure the WebTrends connector and the DataLinks desktop program.  Once these one-time setup procedures are completed you are ready to reap the benefits of having a direct connection between your WebTrends data and Microsoft Excel using DataLinks from Business Intelligence Inc.

EXECUTIVE SUMMARY

1.     Install and configure the WebTrends ODBC Connector.

2.     Install and configure DataLinks creating a new datasource in DataLinks for each WebTrends profile that you wish to report on.

3.     Use DataLinks to connect to your new WebTrends datasource and build your query/report pushing the dataset out to a worksheet in an Excel workbook using the Query Table option.

4.     Use DataLinks to create a relationship between different Tables/Reports in WebTrends and push the resulting dataset out to Excel.

5.     Use DataLinks to connect to any other configured data source and build your query/report and push the resulting dataset out to another worksheet in the same Excel workbook as in 3 above using the Query Table option.

6.     Save the Excel workbook.

7.     Create a new data source in DataLinks pointing to the Excel workbook you just created and saved.  Since DataLinks interprets each worksheet in an Excel workbook as a “table” in a database, the user is able to relate data from one source to data from another source by joining the “tables” or worksheets.

8.     Push the resulting dataset out to a worksheet in the same workbook or to a new Excel workbook altogether.

9.     Refresh the data in all the reports/tables simply by clicking the Refresh All button in Excel.

 

Installing and Configuring the WebTrends ODBC Driver

You can install the WebTrends ODBC Driver to your desktop from the WebTrends Administration Console.

To install the WebTrends ODBC driver and configure a connection:

  1. In the left pane of the Administration Console, click Install Components > Accessories > ODBC Driver.  (NOTE: If you do not see this option available on your login, ask your WebTrends Administrator to add the ODBC connection option to your login profile.  It is not turned on by default.)

 

  1. Complete the steps in the installation wizard.
  2. Manually create a DSN connection.
    1. Select Start > Settings > Control Panel > Administrative Tools.
    2. Double-click Data Sources (ODBC).
    3. Select the System DSN tab.

    

 

    1. Click Add.

 

 

    1. Select WebTrends ODBC Driver from the list and click Finish. The DSN Configuration dialog box opens.
    2. In the Data Source Name text box, type a name for your data source.  (Note: You can use the same ODBC connection to connect to different WebTrends profiles in DataLinks – see Installing and Configuring DataLinks, step 2 below for instructions in how to set up your datasources in DataLinks.)
    3. In the Host Server Name text box, specify the host name for the computer where the WebTrends UI server resides. If you are connecting to WebTrends On Demand, the host name is always ondemandui.webtrends.com.
    4. In the Port text box, specify the WebTrends UI server port. By default, the WebTrends UI server uses port 7099 in software installations. If you use WebTrends On Demand, enter 80 in the text box.
    5. If you are connecting to WebTrends On Demand, type the Account Name for a WebTrends On Demand User Account in the Account Name text box. If you are connecting to a WebTrends software installation, do not change the default name provided.
    6. If you use a Windows domain login to connect to WebTrends, click With Windows NT authentication using the network login ID.

      7.
    7. If you want to connect to the database using a WebTrends user name and password that you specify, click With WebTrends authentication using a login ID and password entered by the user and type the user name and password in the text boxes provided. This user name and password pair must be defined in the Users settings of the WebTrends Administration Console.

 

    1. Click Connect to populate the Profile list.
    2. Select a profile from the Profile list.   (Note: when you set up datasources in DataLinks you will be able to use this same ODBC connection to connect to other profiles and templates.  See Installing and Configuring DataLinks, step 2 below for further information on setting up your datasources in DataLinks.)
    3. If you are creating a connection to a Report database, select a template from the Template list.
    4. Click OK.
    5. Click OK again to save your settings.

Installing and Configuring DataLinks

  1. Click on the link below to begin the download process http://www.bidata.net/download/DataLinks.zip

User Name: download

Password: helloworld

 

    1. The file download dialog box will prompt you to either Open, Save, or Cancel. Choose 'Save' and select a convenient location (such as the desktop) on your computer.

 

    1. Once the download is complete, double-click on the downloaded file ‘DataLinks.zip’.
    2. Double-click the file Setup.Exe.  This will start the installation wizard.  Answer the questions presented until the process is completed, then click the Close button.  DataLinks is now installed on your desktop.
    3. Launch DataLinks.  Click Start > All Programs > Business Intelligence > DataLinks to launch DataLinks
    4. On opening the first time you will be prompted to Activate DataLinks or use the fully-functioning trial version.  If you have purchased DataLinks click on the Activate DataLinks button.  After reading the license agreement check the I Agree to All The Terms and Conditions Listed Above box and type the serial number provided to you.  If you have not purchased DataLinks but wish to use the trial version, click on the Use Trial Version button.

                       

 

  1. Create a new datasource by pointing DataLinks to your WebTrends data.
    1. Click DataSources > New DataSource
    2. Give your datasource a friendly name that identifies that it is pointing to your WebTrends data.

 

 

    1. Select Generic from the drop down menu for Database Server Type.

 

    1. Choose Use the DataLink wizard to generate a connection string.  Select Next to advance to the next step of the process.

 

    1. Select Launch Data Link wizard to create the connection string necessary to connect to the WebTrends data.

 

    1. Select Microsoft OLE DB Provider for ODBC Drivers and click Next to continue.

 

    1. Click on Use Connection String and select Build

 

    1. Under the Machine Data Source tab select the data source you created in step 3.k. above (Installing and Configuring the WebTrends ODBC driver) and click OK to continue.

 

    1. You now have the ability to select which profile and template that you want this particular DataLinks datasource to connect to by using the pull down lists in the following screen.  Click OK once you have made your selection.

    1. Click OK again to continue.
    2. Highlight the entire connection string that you just built and right-click, select copy and then click OK.  (Note: the connection string is a long list of characters so be sure you have it all selected before copying it and moving to the next step.)

 

    1. Click Back to return to the Connection String dialogue box.  Select Use a user generated connection string and click Next.

 

    1. Click in the Enter user generated Connection String field and right click and select paste to copy the connection string you just built into the field.  Check the Use ODBC box and click Finish to complete the setup of this connection.

 

  1. To set up DataLinks connections to additional WebTrends profiles simple repeat step 2 above and select a different profile and/or template in step 2.i.  Of course you will also want to give each connection to a profile a unique name in DataLinks that identifies it to you.

 

Using DataLinks to Connect to and Mine Your WebTrends Data

DataLinks is designed to connect to virtually any data source and allow the user to mine the data in an easy to use graphical user interface and push the resulting dataset out to Excel for further analysis and reporting.  Some data sources such as the data from the WebTrends Analytics module exposed by WebTrends are not true relational databases and thus the ability to query and manipulate the data with any tool is limited.  DataLinks gets around many of these limitations, however, through its tight integration with Excel.  As you work through the examples below on your own WebTrends data, you should be aware of the following:

 

·         The tables that show up in the database pane of DataLinks are not actual tables in WebTrends but reports.  When you run a query against the WebTrends data what you are actually doing is accessing a report and returning the results in a flat file.

·         If you are used to querying relational databases you may be frustrated by the limitations placed on WebTrends queries such as the lack of ability to join tables.  DataLinks provides functionality to get around the limitation on joins (see below) but there are other limitations of the WebTrends connector that cannot be circumvented without functionality being added to the WebTrends connector.

Connecting to and Querying a Single WebTrends Table/Report in DataLinks

  1. Select Start > All Programs > Business Intelligence >DataLinks to start DataLinks (or if Excel is already open, select the BI logo from the DataLinks toolbar).
  2. Select the WebTrends datasource that you created above.  (Note: If you created a datasource for more than one profile you will see each source listed under the Generic connection.)

 

  1. The database pane of DataLinks will populate with all the reports available from WebTrends, including custom reports you may have created.  Select the report you want to run and drag the entire table/report or individual fields from the report to the Selection pane.

 

  1. Drag and drop a time element for the report into the Criteria pane.  If you know the format of the data, you can enter the filter value in the value field of the criteria pane; however, if you do not know what data is in the field of the report simply click on the icon in the right side of the value field for each filter and DataLinks will retrieve the valid choices from the report.  (Note: in most WebTrends reports you can choose a predetermined time element by using the TimePeriod field, or you can create your own custom data range by selecting a StartDate and an EndDate.)

 

  1. At any time during this process you can preview the data by selecting the Preview Query Results button to the right of the preview pane in DataLinks (Note: a Preview Query Results button can also be found in the primary toolbar at the top of the DataLinks window.)

 

  1. Since all the WebTrends tables include a sum record, if you want to exclude that record in your dataset, add a filter to exclude it by adding a field to the criteria pane to filter out non-null values as shown below.

 

  1. You are now ready to push your dataset out to Excel.  To do so simply click on the Excel icon in the main toolbar at the top of the DataLinks window or click on the arrow to the right of the Excel icon to expose other options including the dataset as a CSV (text) or XML file.

 

  1. DataLinks gives you two choices to push your dataset out to Excel: an Excel Query Table or an Excel Pivot Table.  The difference is that while a Query Table includes all the detail for each record in the dataset populating individual rows, a Pivot Table keeps the detail in the background and allows you to slice and dice and summarize the data in a number of powerful ways. 

(Note: A detailed discussion of the power of Pivot Tables is outside the scope of this How-To document, but suffice it to say that the ability of DataLinks to connect Excel directly to large sources of data makes Pivot Tables even more useful than when Pivot Tables are pointing to data within the same Excel workbook.  Unlike Query Tables, Pivot Tables are not bound by the 65,000 row limitation but can point to and report on datasets with hundreds of thousands and even millions of records.  For purposes of illustration in this example we have pushed the data out to Excel in Query Table format.)

In the Export Options you can check a box to use a simple Report Wizard to help you format your report in Excel.  You can also choose to make the connection to the data refreshable from within Excel.  If this option is selected you are able to refresh your data from within Excel simply by clicking the Refresh button.  Finally, you are able to push the data out to a specific workbook or to a New Workbook.  If you select an open workbook in this drop down, note that the data will be populated wherever the cursor is left in the file.

 

 

Use DataLinks to Create a Relationship Between Two or More WebTrends Tables/Reports

Although, as noted above, the WebTrends data is not a true relational database but simply a series of flat file reports, DataLinks is able to allow for the joining of two or more WebTrends reports within a profile to each other, or the same report across profiles or even data from other systems all within the familiar environment of Excel. 

 

In the following example we will demonstrate how you could build a report that joins data from the Entry Pages and the Exit Pages reports into one report in Excel.  There is no limit to how this technique could be applied to the various WebTrends reports.  Since DataLinks connects to virtually any other data source you can also use this technique to combine your WebTrends data with other data from other systems.  The resulting report is updated by simply hitting the Refresh button in Excel.

 

  1. Follow the instructions in Connecting to and Querying a Single WebTrends Table/Report in DataLinks above, to push two or more datasets out to individual worksheets in the same Excel workbook.  Give each sheet a distinct name so you can distinguish one from the other.  Save the Excel Workbook and remember the name and location of the file so you can point DataLinks to it in the next step.  (Note: You do NOT need to close the workbook once it has been saved.  If you intend to put the joined report in this workbook simply leave it open through steps 2 - 11 below.)

 

  1. Create a new datasource in DataLinks pointing to the Excel workbook you just created.  From within DataLinks select DataSources > New DataSource from the main menu.  Give your datasource a friendly name that identifies what it is.  This is the name that this datasource will appear as in DataLinks.  Select Excel Workbook from the dropdown list of database types.

 

  1. Click Next.  Select Use the Data Link wizard to generate a connection string.

 

  1. Click Next.  Click on the Launch the Data Link wizard button to continue.

 

  1. Enter or select the Excel workbook you just saved and click OK.

 

  1. Click Finish and you will see your new datasource listed under the Excel category of the Datasources pane in DataLinks.  Click on the radio button to the left of the new datasource and your data will immediately show up in the Database pane with each worksheet in the Excel workbook listed as a separate “table” in a relational database.  In the screen shot below I have expanded the tables to show the individual fields as I want to limit which fields I take from each report into my combined report.

 

  1. To create a relationship between these two tables we first need to drag and drop some fields to the selection pane.  The next step is to create a relationship between or to “join” the two tables so we can generate our report on visits that entered and exited specific pages on our website.

 

  1. To create a relationship between data in a datasource, you simply click on the Relationships tab in the Selection/Relationship pane of DataLinks and drag the field or fields you are creating a relationship for into the Relationship pane.  This brings up the Relationship Properties window as shown below.  Select which tables and fields you are wanting to join and the Relationship Type and click OK to continue. 

 

  1. Follow these steps for creating as many relationships as you require to get the desired result.  Here I have added a second relationship matching up the Title fields between the EntryPages report and the ExitPages report so the end report will show only those URLs and Titles that match between the two reports.

 

  1. Click on the Excel icon in the toolbar (i.e., the Export Query Results button) to export the results to Excel.  Position the cursor in cell A1 in Sheet 3 of your workbook and select the same workbook in the Destination Excel Workbook section of the window.  Click on Export Query Results and your new report will be created in Excel in the same workbook with the reports on which it is based.

 

  1. Rename the sheet in the workbook that contains the new report and you have now successfully joined data between two WebTrends reports in Excel.

Excel Tips and Tricks

Refreshing All Your Reports with One Click

As you build out multiple refreshable reports in Excel you may wish that you could refresh them all at once with one mouse click.  Excel offers that capability but the button you need is not on the standard toolbars; you will have to get it and add it yourself.  Fortunately, Excel makes this process very easy as you will see if you follow the instructions below.

 

  1. First you should make sure that the PivotTable toolbar in Excel is visible.  To determine if it is select Toolbars from the View menu and see if there is a checkmark by the PivotTable option.  If not, click on PivotTable to activate it on your toolbar.  The PivotTable toolbar includes an exclamation point (!) icon which allows you to refresh a single Query or PivotTable to an external datasource.  You will now be adding another icon that will allow you to refresh all the Query or PivotTables in an Excel workbook with one click.

 


  1. To customize your Excel toolbar to add the Refresh All option, click Customize on the Tools menu.

 

 

  1. Click on the Data category and scroll down the list of commands until you reach a command appropriately named Refresh All.

 

 


  1. Click and drag the Refresh All icon to a point in your toolbar where you want it to reside.  I recommend you put it next to the Refresh icon in the PivotTable toolbar.  Click Close to close the customize window and you are now ready to refresh all the queries in your Excel workbook with one click of the mouse!

 

 

 

 

Pointing Your Excel WebTrends Reports to a Different Profile

With the DataLinks ODBC Template Excel workbook you now have the capability to point the reports you have built in Excel to another WebTrends profile or even to another account entirely.

 

Step 1 – Start Excel and open the DataLinks ODBC Template workbook as well as the workbook that contains the reports you want to point to a different profile.

Step 2 – Copy (don’t move) the worksheets from your report workbook into the DataLinks ODBC Template workbook.

Step 3 – Add the connection string(s) for the profile(s) you want to point the Excel reports to.

 

 

Step 4 – Change the profile in the DataLinks ODBC template to point to the profile you wish to analyze

Step 5 – Change the date in the reports to the time period you wish to analyze.

Step 6 – Refresh all the reports with one click of the mouse.