Lookup table usage

After Hightouch runs identity resolution (IDR) on your graph, Hightouch outputs tables into your warehouse so they can be used for any business need.

Your marketing team can use them for paid campaigns, your sales team for deduplicating outreach efforts, or data scientists for fraud dection.

The output of IDR can also helpful in constructing models or parent models in Hightouch.

Hightouch creates two output tables:

1. A resolved table consisting of every row from your input models (<your_output_table>_resolved)

• This table has the columns: ht_id, model_pk, and model. The model_pk is the primary key from your model and the model is the model slug.
• This table has a column for each identifier you have mapped in your project. If the model doesn't have a column for that identifier, the value is null.

2. A resolved identities table consisting of all the rows from your graph's input models (<your_output_table>_resolved_identities)

• This table has a ht_id for each row as well as an array containing all the identifiers of that profile.

How to use the output tables

If you need to select just one identifier from a user profile, the resolved output table provides every identifier for all users across all models, along with a HT_ID that can be used to identify which identities should be merged together.

This table gives you full control over how you choose which identifier to use on an identity, for example:

• most frequent email
• most recent user id
• most recent device id

The resolved_identities table collapses all the merged users into a single row with multiple identifiers in an array (for example ['kevin.tran@gmail.com', 'kevin@hightouch.com'].)

This gives a unified view of all the user profiles and their associated identifiers.

Example: Finding the most frequent or recent email

If you are using Customer Studio, you can leverage the output of IDR in your parent models.

In this example, we'll use a hypothetical users table and output table from IDR, accounts_activity.

There are 3 steps:

1. Create the parent model
2. Create the related model
3. Configure the Traits

Let's take a look:

1. Parent model

The parent model SQL is as follows:

SELECT users.*, idr.HT_ID
FROM HIGHTOUCH_PLANNER.accounts_activity_resolved AS idr
JOIN USERS AS users ON idr.USER_ID = users.USER_ID;

The base table is the users table and the generated Hightouch ID is joined in so it can be used later.

2. Related model

The next step is to create a 1:many related model with resolved table and join it with the parent model on the Hightouch ID.

select * from HIGHTOUCH_PLANNER.accounts_activity_resolved

3. The Traits

Each row in users may have multiple emails in the IDR output table. Traits can be used to quickly aggregate which email to use for a campaign.

For example, you may want to create a trait that grabs the most frequent email detected in the IDR output table.

Alternatively, you can order the IDR table by timestamp and grab the most recent email detected in the IDR output table.