Define data schema

Overview

Let's say you're an e-commerce store selling plants. You want to use Hightouch Audiences to personalize your marketing campaigns based on your customers' geographic region and purchase history. Before you start creating these audiences, you need to define two essential datasets:

1. The set of people who make up your audiences—in other words, your customers
2. The characteristics you want to filter these people on. For example, geographic region, whether they bought a specific product, etc.

In Hightouch, you define the first as a parent model and the second as related models and events. Related models and events are similar; they're characteristics to filter parent models off of. Once you've created your models, you define the relationships between them. These models and the relationships between them are known as your data schema.

Schema setup is a one-time configuration process. Once you've set up your schema, you can build thousands of audiences with just a small set of initial models and events. Continue reading to learn more about the different model types, requirements, relationships, and setup instructions.

Required skillset

The schema setup process requires a technical understanding of your data and its relationships. Data or analytics engineers are often involved.

If your data schema involves several related models and events, it can be useful to start by creating an entity relationship diagram.

Parent models

Parent models define the primary dataset you want to build your audiences off.

In the plant store example, the parent model would be the dataset of all customers with the following columns:

• user_id: a unique identifier for the customer, the primary key for this table and used as a foreign key for other tables
• name: the user's name
• geo_region: the geographic region where they live, valuable for determining the appropriate plants for their growing conditions
• age: the user's age
• Any other user information that helps define models

Parent model setup

Parent model definition is a one-time step you can complete by following these steps:

1. Click the Setup button at the top right of the Audiences overview page.

2. Clicking Setup brings you to the Parent models tab. Click Add parent model.

3. Select an existing supported source.
4. Select how you want to create your model. Depending on the source, you can either:
   • Write a query in the SQL editor
   • Select a table using the visual table selector
   • or leverage existing dbt models or Looker Looks
5. Choose a primary key and labels for your parent model. The audience builder displays labels previewing results, so you should select labels that help you understand your audience, for example, name or email.

6. Click Finish to save your model.

Related models and events

Related models and events provide additional characteristics or actions on which you want to filter your parent model. Related model and event setup are similar; neither require a primary key. Events require a timestamp column.

In the plant e-commerce example, a related model could be a Purchases table with columns for:

• user_id: a reference to the user that made the purchase
• product_name: the name of the product
• price: the price of the purchase
• used_coupon: whether the user used a coupon when checking out

An events model might include Pageview events with columns for:

• user_id: a reference to the user that viewed the page
• page_path: the URL path for the page (for example, /promo)
• timestamp: when the user viewed the page

Related model setup

Related model and event definition is a one-time step you can complete by following these steps:

1. Click the Setup button at the top right of the Audiences overview page.
2. Select the Related models tab. Click Add related model.

3. Select an existing supported source.
4. Select how you want to create your model. Depending on the source, you can either:
   • Write a query in the SQL editor
   • Select a table using the visual table selector
   • or leverage existing dbt models or Looker Looks
5. Give your related model a descriptive Name.
6. Click Finish to save your model.

Event setup

1. Click the Setup button at the top right of the Audiences overview page.
2. Select the Events tab. Click Add event.

3. Select an existing supported source.
4. Select how you want to create your model. Depending on the source, you can either:
   • Write a query in the SQL editor
   • Select a model using the visual table selector
   • or leverage existing dbt models or Looker Looks
5. Give your event a descriptive Name and select a column to use for the Timestamp.
6. Click Finish to save your model.

Column configuration

Once you've set up a parent, related, or events model you can update its configuration by clicking on it and going to its Configuration tab. You can also apply additional configurations on its Columns.

For example, you can:

• Disable a column from being part of the model
• Change a column's data type
• Provide an alias for it to appear as in the visual audience builder.
• Turn on suggestions so that suggested values appear when using the visual audience builder. For example, if a user is creating an audience they want to filter on "City," turning on suggestions populates a dropdown with values—New York, Hong Kong, etc.—found in the dataset.
• Redact values so they don't appear in the audience preview or explorer. This includes providing a custom text to display instead. If you don't enter a text, Hightouch displays <REDACTED BY HIGHTOUCH> or a customized message of your choosing, for example Field redacted.

Relationships

Once data models are setup, it's time to define the relationships between them.

In the plant e-commerce example, let's say you want to create an audience of customers who've purchased a specific plant type or who've visited a particular promo page. You need to define a relationship between your parent model (users) and your related model (purchases) and events (pageviews).

Under the hood, Hightouch generates SQL JOINs between your models. To make this possible, you must configure create relationships by selecting the foreign keys between your models. You have two options for creating relationships:

• Direct relationships—this covers the majority of relationships, including one-to-one and one-to-many relationships
• Through relationships

Direct relationships

In most cases, you only need to set up direct relationships, which relate models directly to each other. For the plant e-commerce example, you need a relationship between users and purchases.

To create a direct relationship, follow these steps:

1. From the Parent models tab, select the parent model you want to define relationships for.
2. Open the Relationships tab.

3. Click Add direct relationship.
4. Enter a Relationship name, for example, "Customer purchases."
5. Select the appropriate Related Model; you must have configured it first.
6. Under Mappings, create the primary-foreign key relationship by selecting the primary key from the parent model and the corresponding foreign key from the related mode.

7. If relevant, toggle on Merge Columns.
8. Click Save.

Merge columns

If the primary and foreign keys in the models you're relating have a one-to-one relationship, you can choose toggle on Merge columns. For example, suppose your Users table has an email field that's unique. In the related model, imagine you're matching on email. That related model needs to also have only one unique email per row.

Merging columns adds columns from the related model to whichever table you're relating. You can do this as often as you need to merge multiple columns into one model and more easily filter on it in the visual audiences builder.

Through relationships

In general, you should create direct relationships and only use through relationships if you have to. This arises when different models aren't directly related.

For example, models are often related through an intermediary table when many-to-many relationships occur. For this reason, many-to-many relationships are also known as through relationships.

For the plant e-commerce example, imagine you have different subscription groups:

• quarterly pet-friendly subscription
• succulent of the month subscription
• annual easy care subscription, etc.

Customers can have several subscriptions. And each subscription has multiple users subscribed to it. Model-wise this equates to:

A Users table with a column for:

• user_id: a unique identifier for the user
• other personal information such as name, email, etc.

A Subscriptions table with a column for:

• product_id: a unique identifier for the subscription
• name: the subscription's name
• delivery: describing the delivery schedule, either monthly, quarterly, or annually
• price: the subscription cost

A Memberships table with columns for:

• user_id: a reference to the user
• product_id: a reference to the subscription

A user is part of a subscription group if the Memberships table has entry matching user_id and product_id.

In this example, you could say, "Users are related to subscriptions through the memberships table." To build audiences based on subscription properties, for example, all customers that receive a monthly delivery, you would need to create the following relationships:

• A direct relationship from users to memberships on user_id
• A direct relationship from memberships to subscriptions on product_id
• A through relationship from users to subscriptions via the preceding two direct relationships

To create a through relationship in Hightouch, make sure you've setup the necessary direct relationships then follow these steps:

1. From the Parent models tab, select the parent model you want to define relationships for.
2. Open the Relationships tab.

3. Click Add through relationship, below any existing direct relationships.
4. Enter a Relationship name, for example, "User subscription membership."
5. Select the appropriate related models.
6. Click Add relationship.