Clickstream Data Feed Lookup Table Column Reference

[ryan_neufeld1]

Could someone direct me to a resource that provides a Column Reference for the Lookup tables provided with the Clickstream Data Feeds?  I've found the Column Reference for the Datafeed (http://microsite.omniture.com/t2/help/en_US/sc/clickstream/datafeeds_reference.html), but have not located the same for the included Lookup tables.  I am specifically looking for the field description information to provide the field names and data types to our DBAs.

Thanks in advance.

[Gigazelle]

Hi Ryan,

All lookup tables should be included with each data feed file. For example, the 'browsers' column within the data feed shows a whole bunch of different numbers. In the same tarball, there is also a browsers.tsv that shows which browsers represent which numbers. Is that the info you're looking for?

[ryan_neufeld1]

We have the lookup tables, however; my DBA is looking for the structure (column names and variable types/widths) for each of the lookup tables.

[jacobh35707634 · Accepted solution]

The lookup key column will be at most a 32-bit unsigned integer.

The associated lookup value will be at most a 255 character string.

Some of the lookup key columns will never get close to that (connection type comes to mind, I think it currently has ~5 values), but since you are only looking at 15 or so lookup files for a typical feed, if you use these max values you'll be set going forward.

[ryan_neufeld1]

Thanks Jacob, I will pass that along.

[warrensander]

one exception would be the products column which can be longer than 255 characters. Even with just one product the merchandising evar value in the product clause can be 255 characters plus the rest of the product string.

and any list evars you have can have individual entries that are 255 characters with separators. I don't know if you get multiple lines for each entry in the list evars

[jacobh35707634]

Hi Warren, the guidelines in my post are for the lookup columns, not the hit data columns. The column sizes for the hit data varies widely and should be set based on the reference table Ryan linked above.

For those who haven't had the pleasure of consuming a raw data feed, some columns contain a numeric key that is mapped to a human-readable string using a lookup file. For example, a browser key in the raw feed might be '657', you'd use the lookup file to find out that this represents 'Google Chrome 38.0'.

    "I don't know if you get multiple lines for each entry in the list evars"

The individual entries for list evars are all still together in the feed, so these columns can be quite a bit longer than 255 chars as you pointed out (these columns are marked as "text" in the reference).