All You Need to Know About Data Lookups

A Data Lookup allows you to match different pieces of data together.

A good example of this is if you wanted to connect the Person Data FAVOURITE_STORE with a Global Data Table such as STORE. This means that a recipient's store preferences would be matched with the store's opening times, address, or other useful pieces of information. You could put this to use by inserting a Data Lookup block in an email.

There are different kinds of Data Lookup, depending on which sorts of data you are linking together.

• Person Data to Global Data Table
• One-to-Many Table to Global Data Table
• Unique Lookups
  • Unique One-to-Many Table Lookup
  • Unique One-to-Many Table - Global Data Table Lookup
• Advanced Lookup (GDT to GDT)
• Person to Person lookup

Person Data to Global Data Table

The following is a Global Data Table which contains information about a specific store. 

Recipient A has the following Person Data values.

 By creating a Person Data to Global Data Table Lookup, we are able to link these two values:

• POSTAL_CODE (Person Data) = 1117
• STORES.POSTAL_CODE (Global Data Table) = 1117

Configuring a Person Data to Global Data Table Lookup to match on ZIPCODE (PD) - STORES.POSTAL_CODE (GDT)

To make use of this Data Lookup match, we can insert a Data Lookup block in our email.

Changing an email block type from Static to Data Lookup

Once you have a match, you can access other data fields in the matching row. In the example below, that means that we can merge in the opening hours, based on the POSTAL_CODE.

You can only match two data fields of the same type. For example, you can create a link between two number data types but not between a string data type and a number data type.

Inserting the value of STORES.OPENING_HOURS’for the row match

Remember, Global Data Tables are only intended for public non-personal data e.g. product information. Be aware that recipient specific data in Global Data Tables could lead to GDPR issues for you as a data controller. Recipient specific data has to exist either as Person Data or One-to-Many data. 

One-to-Many Table to Global Data Table

The following is a Global Data Table which contains information about a publication.

 Recipient A has the following One-to-Many Table values:

By creating a One-to-Many Table to Global Data Table Lookup, we are able to create a match on these two values:

• PUBLICATION.ID (GDT) = 005929
• SUBSCRIPTION.ID (Global Data Table) = 005929

Configuring a One-to-Many Table to Global Data Table Lookup to match on SUBSCRIPTION.ID (OTM) - PUBLICATION.ID (GDT)

In our email, we can make use of this lookup by inserting data for the specific publication matched by the One-to-Many to Global Data Table Lookup.

Inserting the value of PUBLICATION.TITLE’(GDT) for the row matching the Data Lookup 

Unique Lookups

A Unique Lookup allows you to create a match between a recipient's Person Data or One-to-Many Table field and a field in a Global Data table. Unlike normal Data Lookups, a Unique Lookup will always result in a single match. 

You don't need to use a dedicated Lookup Block for this sort of Lookup. This means that you can use them where there isn't the visual space to insert a Block. This might be a subject line, email from address, email from name, or SMS content.

Unique Lookups come in three types:

• Unique Person Data - Global Data Table Lookup
• Unique One-to-Many Lookup
• Unique One-to-Many - Global Data Table Lookup 

As Unique Lookups are not tied to a specific block type, you can use them in both Static Blocks and in conjunction with Data Lookups. This also means that all sorting logic and conditions are configured directly on the Lookup.  

Unique One-to-Many Table Lookup

In the example below, we are looking at magazine subscriptions again. We want to include the title of the most recently purchased magazine subscription in the Subject Line. We achieve this by creating a Unique One-to-Many Table lookup. The lookup below first filters out all rows where the RETURNED status is true (the purchase has been refunded), and then provides the most recent purchase based on the date value of TIME.

Configuring a Unique One-to-Many Table lookup, to find a row where RETURNED is not equal to ‘True’, with results sorted by ascending values in the field

Unique One-to-Many Table - Global Data Table Lookup

In the example below, we want to find a row in the recipient's 'Purchases' table where the category field matches a row in the Global Data Table 'Products'. Therefore, you can learn what the recipient's previous products are and then recommend similar ones from the same category. As with other Unique Lookups, we can merge the data where conventional Lookup Blocks are unavailable.

Configuring a Unique One-to-Many - Global Data Table Lookup matching rows between PURCHASES (OTM) to PRODUCT (GDT)

Advanced Lookup (GDT to GDT)

The 'Advanced Lookup (GDT to GDT)' allows you to define a Parent Lookup based on one lookup (PD/OTM to GDT), and look up matching rows in a second Global Data Table.

Example

You have a One-to-Many table 'RENTALS'

• This table contains the ID of the specific holiday home

You have a Global Data Table table 'HOMES'

• This table contains the ID of all available holiday homes, as well as the ID of the nearest Regional Office

You have a Global Data Table table 'OFFICES'

• This table contains the ID of all Regional Offices.

First we create a lookup which matches RENTALS to HOMES based on RENTALS.HOLIDAY_HOME_ID to HOMES.ID.

This will result in one or more rows (based on the amount of RENTALS), and return the Global Data Table fields from 'HOMES'. These fields can be used as the basis of an 'Advanced Lookup (GDT to GDT)'.

The 'Advanced Lookup (GDT to GDT)' is configured to match HOMES_KEY_COLUMN to REGIONAL_OFFICES.OFFICE_LOCAL_KEY. This match will allow us full access to the Global Data Table fields of both the first lookup, as well as the new one.

An 'Advanced Lookup (GDT to GDT)'. Matching holiday homes to their nearest regional office

Excluded Records

Finally, you also have the option to select "Exclude Records" if you open this tab. This means that the advanced lookup will exclude records that you found as a result of the first lookup. This is only relevant if you use the same Global Data Table in the advanced lookup as in the primary lookup.

This could be useful if you made a primary lookup that found all houses you have previously rented and would like to use the advanced lookup to find recommended houses (within same area, type or size). In order to avoid showing the houses you already have rented previously, you would need to check off in the Excluded Records box. 

Person to Person lookup

The 'Person to Person' Lookup allows you to create a parent-child lookup. In this example, the 'parent' has a unique person data field 'ACCOUNT_ID'. One or more recipients has a matching value stored in the 'indexed' person data field 'PARENT_ACCOUNT_ID'. This allows you to create a relationship between two or more recipients, based on two different Person Data fields.

A Person to Person Lookup matching ACCOUNT_ID with PARENT_ACCOUNT_ID