Using Lookup Tables for Analytics Reports

Lookup tables provide Webtrends with information it can use to report on your web data more meaningfully. Most lookup tables are comma-separated value (CSV) files, text files, or databases. For example, you can use a simple CSV file with two columns to replace long product or item codes with more readable text equivalents. Instead of a long SKU number, Webtrends can use the information in the lookup table to report the equivalent product name. Webtrends also uses more complex, preconfigured lookup tables to create the Campaigns and Products drilldown reports. These reports show a hierarchical data structure that allows you to drill down from more general levels of data to highly specific ones.

This article describes some common uses for lookup tables, explains their format, and provides information about the uses and limitations of each type of lookup table.

When Can I Use a Lookup Table?

Many preconfigured reports use lookup tables by default. If you are licensed to create and edit custom reports, you can assign a standard lookup table to any dimension or measure when creating or editing a custom report. The Campaigns and Products drilldown reports also rely on specialized drilldown lookup tables. You can also assign a lookup table when creating URL Parameter Analysis definitions. Finally, you can use lookup tables in SmartReports by adding them as external data sources within Microsoft Excel. For more information about using lookup tables with SmartReports, see the SmartReports Help.

Who Can Use Lookup Tables?

If you use Webtrends software, you can create and modify lookup tables locally. If you use Webtrends On Demand, you should contact Webtrends Support to have your lookup tables uploaded and implemented.

To create, copy, and modify custom reports, including assigning lookup tables, you need to license custom reporting. However, Webtrends software allows you to modify preconfigured lookup tables such as the Campaigns and Products lookup tables.

Understanding Standard Lookup Tables

Standard lookup tables are typically used to help make custom reports and URL Parameter Analysis reports more readable and concise. If the information captured during analysis includes long or unintelligible strings that are not immediately readable to report users, you can provide a lookup table to translate this information into a readable form. This can help reduce the size of the report and makes the information easier to review. You can specify a CSV file, a text file, or (for custom reports only) an Oracle or SQL database.

There are some small differences in the way Webtrends processes lookup tables for custom reports and for URL Parameter Analysis. The following sections provide more detailed information about how each type of translation works.

Lookup Tables for Custom Reports

When you assign a standard lookup table in a custom report, you can specify a key column and a value column. The key column contains the data shown in the web activity data file. The value column contains the data you want to display in the report. Because you can specify key and value columns, you can use a lookup table with any number of columns, and you can use the same lookup table for different reports by identifying different columns as keys and values.

Custom reports support commas, spaces, and tabs as column delimiters in lookup tables. If a value in your custom report lookup table contains an internal delimiter, you can enclose the entire column value in quotes. However, you should not use quotes within a translation value.

For example, the following table shows how Webtrends parses a lookup table where a value contains an internal delimiter. This example assumes that the file uses commas as the delimiter.

If you are using commas as a delimiter and a value column contains the value news,foreign, Webtrends processes the content after the comma as an additional column and interprets the value as news. To return the correct value of news,foreign, make sure the value in the lookup table is "news,foreign".

URL query parameters are translated after any hex encoded strings have been decoded. For example, the encoded value “xxx%20yyy” is decoded to “xxx yyy” before the translation lookup occurs.

You can assign a lookup table to a custom report in either of the following locations:

• In the Based On dialog when you are editing a custom report dimension. Lookup table functionality is only displayed in this dialog when you click Advanced and select the Translate Substring Retrieved Above check box.

• In the What to Measure dialog when you are editing a custom report measure.

Lookup Tables for URL Parameter Analysis

You can also specify a standard lookup table in the URL Parameter Analysis settings.

Lookup tables must be text files with two columns and use commas, equals signs (=), or tabs as a delimiter. The key column is always the first column from the left, and the value column is the second column. URL Parameter Analysis does not support lookup table databases.

Webtrends interprets the characters before the first delimiter (comma or tab) as the key column, and all the characters after the first delimiter as part of the value column. This means that all characters after the first column are interpreted as part of the translated value, including additional delimiters and quotation marks. Quotation marks are not removed in report output. For example, if the value column contains the value news,foreign, as described in the example for custom report translation, Webtrends processes all the content after the comma as part of the value. The following table shows how Webtrends processes a lookup table where a value contains an internal delimiter.

If there are only two columns, Webtrends returns the value correctly as news,foreign. Any quotes enclosing the value are also returned in the report data. However, if the file contains any additional data in the same row, Webtrends interprets it as part of the value column and adds it to the translated string. As shown in the example, if the value column is followed by a comma-delimited column containing information about ad revenue, Webtrends returns a nonsensical value like news,foreign,$25,000.

Lookup Tables for Drilldowns

The campaigns.csv and products.csv lookup tables determine how Webtrends creates drilldown reports for marketing campaigns and product merchandising. Webtrends uses the information in these lookup tables to provide data linked to either a product SKU number (passed in the WT.pn_sku query parameter) or a Campaign ID (passed in the WT.mc_id query parameter). The information provided in this parameter should be the same as the key column of the lookup table. The value columns provide the information for additional columns in the reports. You can modify the values in the lookup tables to reflect data for your web site, but you must maintain the order and the naming scheme for the columns in the lookup table. The drilldown lookup tables, products.csv and campaigns.csv, are located in the following directory:

Webtrends installation directory\storage\config\wtm_wtx\datfiles\datasources

For more information about using the drilldown lookup tables to create Campaigns and Products drilldown reports see “Using Custom Reports."