Skip to main content
All CollectionsData Transformation
How to use lookup tables when creating custom fields
How to use lookup tables when creating custom fields

Learn how to look up values for custom dimensions and metrics

Ilona Norman avatar
Written by Ilona Norman
Updated over 4 months ago

Lookup tables can be used for advanced custom fields when the standard "WHEN condition THEN value" is not sufficient for producing the desired result. With a lookup table, Funnel can populate the custom field by searching for a match in a table and returning the corresponding value on that particular row.

Dimension example

Let's say we're using codes for our campaign names where each code corresponds to a human-friendly name for the campaign:

To translate the campaign code to its human-friendly counterpart, start by creating a new "Custom Dimension" from the "Dimensions" page in Funnel.

In this example, we'll keep it simple and create just one rule that applies to the "Campaign" custom dimension which means it will affect data across all connected data sources. Add a Rule for all data, and activate the lookup table by selecting then

Select lookup in the first dropdown.

Next, we choose our operator and in our case, = will be appropriate since our campaign names in the data collected by Funnel corresponds exactly to the codes in our lookup table.

The last step is to paste our lookup table into the input field. The table must be represented as comma-separated values (CSV) where each row consists of two values separated by a comma. The first value is what the lookup function will search for and the second is the value that Funnel will return as a value for the custom field.

Note: If you already have data for this in a google sheet, you can lookup values from a google sheet directly.

And that's it! The custom dimension will now translate the campaign codes into their human-friendly counterparts.

Troubleshooting

In case the input field gets a red border, something is wrong with the lookup table.

Two common problems that invalidate the lookup table are:

Duplicate entries

The table has two or more rows with identical values in the first column. For example:

ABC123,Summer sale
ABC123,Winter sale

In this case, the lookup table is invalid because ABC123 occurs multiple times.

More than 2 columns

One or more rows has more than one comma. For example:

ABC123, Summer sale, June

In this case, the row will be interpreted as having more than two columns and Funnel will not know which value to return.

Metric example

Though you're less likely to create a custom metric using the lookup feature, there are specific cases where this can be useful.

Let's say that you're tracking calls and you're trying to count the number of calls with a duration of over 60 seconds or exclude calls that are under 60 seconds.

You could create the following lookup rule in Funnel to return '1' if the call is ≥60.1, otherwise, the value will be '0':

Did this answer your question?