Custom Lookup Tables

Beginning with version 5.6, Urchin allows you to define custom lookup tables. One useful application of a lookup table is to substitute human readable text for the often cryptic request parameters used with dynamic URLs. For example, consider a web site in which the Pages & Files-->Page Query Terms report is used to rank the popularity of requested documents. In the report (shown below), the document id is displayed instead of the full document name. The numeric id is shown because the report simply ranks the popularity of requests of the form

Applying a lookup table which maps document names to numeric ids allows us to view the same information in Pages & Files-->Requested Pages, with the full document name displayed.

This article illustrates how to create and apply a lookup table for this example. The details of your lookup table and filters may differ according to your particular application, however, the basic steps will still apply.

Defining Your Lookup Table

To define your table:

  1. Create a table in Excel that maps your codes to text labels. An example is shown below. The first row of the file must begin with "#Fields:", followed by "request_stem" in column 2.

  2. Save the Excel table as a tab delimited plain text file in the lib/custom/lookuptables directory of your Urchin distribution. You must save the file with an extension of ".lt".

Applying the Table

  1. Apply the following Advanced filter to your profile. This filter tells Urchin to look in the request_uri for the string "id=", extract the id, and write the id into the request_stem. (Note that request_stem is the title of the second column of the lookup table.)

  2. Apply the following Lookup Table filter (applied on the request_stem field) to your profile. Select your lookup table in the Table Name drop down list. If your lookup table does not appear as an option in the drop down list, make sure that your lookup table file name ends with .lt and that it has been saved in the lib/custom/lookuptables directory of your Urchin distribution.