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.
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, create a new custom dimension:
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. To activate the lookup table, select
+ more steps
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.
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:
The table has two or more rows with identical values in the first column. For example:
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.
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':