How to work with lookup tables

Lookup tables are comprised of a key column with a corresponding column of values. The key can be a piece of respondent data collected on your form, a query string parameter, or an Ion core field. The value should be the one that corresponds to the key. By looking up a value based on a key, you can serve up targeted content and control the way respondents navigate through your creative along with a whole suite of actions you can take “behind the scenes.”

Here are just a few examples of what lookup tables combined with server scriptlets can accomplish:

• Check to see if the respondent’s location is considered serviceable by your business.
• Map the respondent’s location to a specific sales representative.
• Display content based on a form selection, query string parameter, or core field.
• Send conditional notifications and email templates.

Once you have outlined what you intend to accomplish, the first step is to create your lookup table file. You may have the ability to do this programmatically or it could be something you can accomplish by hand. Either way, the result should be a two-column .csv file. Column A will consist of your keys and column B will consist of the corresponding values.

If you’re creating a short, simple lookup table, you can skip creating the .csv file, and enter the lookup table content manually, right within your console. Here are the steps to take to add your lookup table to your ion console:

1. Click Data Management in your console’s side nav and select Lookup Tables
2. Click on the “New lookup table” button then add a title and optional description.
3. Click the Lookup Table Actions dropdown menu and then import your properly formatted .csv file by clicking on the Import Entries link.
4. Or, add entries individually by clicking on the “New entry” button.

You also can automatically transfer lookup table content to Ion via a PUT request. This advanced functionality requires a posting mechanism to deliver content to the lookup table and working closely with the Ion team to bring this functionality to fruition. Please contact your customer success manager if you intend to take this route.

Once the lookup table has been uploaded or added to your Ion console, you’ll use a server scriptlet to look up a value based on the key. The following function is used to accomplish the lookup:

Once you’ve used the function above to “grab” the value from the lookup table, you can use the scriptlet to execute subsequent actions. For example, you may simply return the value from the lookup table and dynamically insert this into your creative. Or, you could return a response code value and use this as your rule condition on your creative(s).

Lookup Table Condition

On some of your Creatives, you might want to set them to only accept certain values, so respondents who enter the desired data can access the experience or be sent to a different page than those who do not enter this data.

For example, you may only provide your services within certain zip codes. In this scenario, you could upload a Lookup Table to your Ion console and use advanced Rules to check and ensure the zip code entered into your Form is in the Lookup Table and in turn send the visitor to a page to review services available. Any respondent who enters a zip code that is not in the Lookup Table could be sent to a different page that lets the visitor know they are not in a serviceable area.

Before we get started…

For a situation similar to the one described, you’ll want to create or upload a Lookup Table to your Ion console. For more information on Lookup Tables in Ion, please see this first.

For example purposes, this post was written with the attached Lookup Table in mind where acceptable zip codes are listed in the spreadsheet’s column A to serve as the “Lookup Key” and the word “serviceable” in column B to serve as the “Lookup Value” for each zip code. Also for example purposes, we will be calling this Lookup Table “ServiceZips”.

For this, you’ll want to have a Creative in place with a Form Field that requires a zip code. For example purposes, we will be using the ZipCode Data Field.

• ServiceZips.csv

Let’s Get Started

With the ServiceZips Lookup Table added to your Ion console, navigate to your Creative. By default, you will want to set the Form to “Go to Page” to the one that indicates that the visitor is not in a serviceable area.

Next, single-click the Submit Button on your Form and open up its Rules. From here, you may add the below Rule:

In this Rule, the “Lookup table data” Condition is selected. In the first available textbox after it, you will want to reference the name of the Lookup Table (ServiceZips) and then the Lookup Key you are looking for separated by a period (ServiceZips.33432). The condition is set as “equals” to the word “serviceable”, which is the corresponding Lookup Value for the Lookup Key (33432) within the ServiceZips Lookup Table. When this condition is met, visitors are sent to the “Services” page.

Taking this a step further, you may set up a Rule with the “Lookup table data” Condition to dynamically look for the zip code entered into the Form by replacing the Lookup Key (i.e. 33432) with {% raw %} {{ZipCode}} {% endraw %}. This setup would take whatever the visitor has entered for their zip code and check the ServicesZip lookup table for it. If there is a zip code in your lookup table that matches the one entered in the form and its corresponding lookup value is serviceable, the respondent will be sent to the Services page.

*NOTE: When testing, you will need to use a live URL as this rule will not function as needed within a Preview. Please also note that if your lookup values are numeric, you will want to use the “Lookup table numeric data” condition.

---

If you have any questions, please contact us at [email protected].