Skip to main content

Create lookup tables for custom fields

Replace custom dimension or metric values with a lookup table.

Use a lookup table when a standard WHEN condition and THEN value cannot produce the custom field that you need. Funnel searches the first column for a matching value. It then returns the value from the second column in the same row.

​

Before you begin

  • Prepare a comma-separated values (CSV) table with two columns.

  • Put the values that Funnel must find in the first column.

  • Put the values that Funnel must return in the second column.

​

Create a dimension lookup

For example, use a lookup table to replace campaign codes with readable campaign names:

Value to find (first column)

Value to return (second column)

ABC123

Summer sale

DEF456

Winter sale

GHI789

Black Friday

In Funnel, you enter the same table as CSV, one row per line:

ABC123,Summer sale
DEF456,Winter sale
GHI789,Black Friday
  1. Go to Data > Organize > Dimensions.

    ​

  2. Create a custom dimension.

    ​

  3. Add one rule that applies to all data. In this example, the rule affects the Campaign custom dimension across all connected data sources.

    ​

  4. Select then.

    ​

  5. Select lookup in the first list.

    ​

  6. Select a comparison operator: contains, contains (ignore case), =, >, ≥, < or ≤. Select = when the values in Funnel exactly match the codes in the lookup table.

    ​

  7. Paste the CSV table into the input field. Each row must contain two values separated by one comma. Funnel searches for the first value and returns the second value.

    ​

    If you keep the lookup data in Google Sheets, you can look up values from Google Sheets.

​

What to expect

The custom dimension replaces each matching campaign code with the readable campaign name from the same row of the lookup table.

​

Troubleshoot an invalid lookup table

A red border around the input field means that the lookup table is invalid. Check for the following common problems.

​

Duplicate entries

The lookup table is invalid when two or more rows contain the same value in the first column. For example:

​

ABC123,Summer sale
​ABC123,Winter sale

​

Remove or change one row so that ABC123 occurs only once.

​

More than two columns

A row with more than one comma contains more than two columns. For example:

​

ABC123,Summer sale,June

​

Remove the additional column. Each row must contain only the search value and the return value.

​

Metric example

A lookup table can also return numeric values for a custom metric.

​

For example, use a lookup rule to count calls that last more than 60 seconds. Configure the rule to return 1 when the call duration is at least 60.1, and 0 for shorter calls. Select ≥ as the comparison operator.

​

In a lookup table for a custom metric, both columns must contain numbers.

Did this answer your question?