Define data schema

Overview

Let's say you're an e-commerce store selling plants. You want to use Hightouch Customer Studio 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, products they've purchased, actions they've taken on your website or app, 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 a small set of initial models and events.

By following along with the setup on this page, you'll learn how to create a schema like this:

Continue reading to learn more about model types, 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 helpful 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. It could include 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. Go to the Schema page.
2. Ensure the correct data source is selected from the dropdown next to Schema.

3. Click Create parent model.
4. By default, Hightouch prompts you to select a table in your source. If you prefer to use a custom SQL query, dbt model, or another modeling method to define your model, you can change it from the modeling method dropdown.

5. Once you've defined your model and previewed the results, click Continue.
6. Enter a name for your parent model, for example, "Users."
7. Optionally, enter a description.
8. Select a primary key. You must select a column with unique values, e.g. user_id.
9. Select columns for the model's primary label and secondary label. Hightouch displays these column values when previewing audiences.
10. Click Create parent model.

The parent model now appears in the schema builder. You can edit an existing parent model by clicking on it and making changes in the Query tab.

For more configuration options, refer to the column configuration section.

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 requires a primary key. Events require a timestamp column.

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

• user_id: a reference to the user that owns the plant
• product_name: the plant name
• price: the price of the plant they purchased
• used_coupon: whether the user used a coupon when checking out

An events model might include Product viewed events with columns for:

• user_id: a reference to the user that viewed the product
• page_path: the URL path for the product detail page
• timestamp: when the user viewed the product

Relationships

When you create related or event models, you simultaneously set up their relationships.

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

Under the hood, Hightouch generates SQL JOINs between your models. To make this possible, you must create relationships by selecting the foreign keys between your models. You create relationships during the related model and event setup, though you can also add them later.

Related model setup

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

1. Related models must be created in relation to an existing model. Click the + button on any existing model and select Create a related model.

2. By default, Hightouch prompts you to select a table in your source. If you prefer to use a custom SQL query, dbt model, or another modeling method to define your model, you can change it from the modeling method dropdown.

3. Once you've defined your model and previewed the results, click Continue.
4. Enter a name for your related model, for example, "Plants."
5. Optionally, enter a description.
6. Define the related model's Relationship. By default, Hightouch names the relationship the same as the related model, but you can edit it by clicking the pencil icon.
7. Select the relationship's cardinality: 1:1, 1:many, or many:1.

8. To relate rows from the related model to rows in the parent model, select the relevant foreign key columns from the parent and related models.
9. If there are multiple columns that the parent and related model must match on, enable Multiple join keys and make additional selections.

10. If you want to add columns from the parent model onto the related model, turn on Merge columns.
11. Click Create related model.

The related model now appears in the schema builder. You can edit an existing related model by clicking on it and making changes in the Query tab.

For more configuration options, refer to the column configuration section.

Event setup

Event definition is a one-time step similar to related model setup. The only difference is that event models require a timestamp column. You can complete event setup by following these steps:

1. Event models must be created in relation to an existing model. Click the + button on any existing model and select Create a related event.

2. By default, Hightouch prompts you to select a table in your source. If you prefer to use a custom SQL query, dbt model, or another modeling method to define your model, you can change it from the modeling method dropdown.

3. Once you've defined your model and previewed the results, click Continue.
4. Enter a name for your event model, for example, "Product viewed."
5. Optionally, enter a description.
6. Define the related event's Relationship. By default, Hightouch names the relationship the same as the event model, but you can edit it by clicking the pencil icon.
7. Select the relationship's cardinality: 1:1, 1:many, or many:1. In most cases, parent models have a 1:many relationship with events.

8. To relate rows from the events model to rows in the parent model, select the relevant foreign key columns from the parent and event models.
9. If there are multiple columns that the parent and related model must match on, enable Multiple join keys and make additional selections.

10. If you want to add columns from the parent model onto the event model, turn on Merge columns.
11. Click Create event.

The event model now appears in the schema builder. You can edit an existing event model by clicking on it and making changes in the Query tab.

For more configuration options, refer to the column configuration section.

Bidirectionality

Relationships are bidirectional. That means you can create and view relationships starting from a parent or related model or event.

For example, creating a many:1 relationship from purchases to users:

is the same as creating a 1:many relationship between users to purchases:

The schema overview page shows the schema from the perspective of a parent model; labels are displayed accordingly.

Check out the schema management section if you have a complex schema with multiple parent models.

Merge columns

Merging columns adds columns from one model onto another so that the model with additional columns is more easily filterable in the visual audiences builder.

In a 1:1 relationship, you can merge columns in either direction. The Merge columns toggle only appears on the model that will receive the additional columns.

In a 1:many relationship, you can only merge columns from the parent model (the model on the 1 side of the relationship) to the related model (the model on the many side of the relationship). For example, if you have a 1:many relationship from Users to Plants, you can only merge Users columns onto Plants columns. This means that the Merge columns toggle only appears when the relationship is viewed on the related model, where it's displayed as many:1.

Multiple join keys

In most cases, selecting one pair of keys is enough to form a relationship between two models. You may want to select multiple foreign keys to increase accuracy. Multiple join keys increase accuracy because all pairs of keys must match for the Hightouch to JOIN the relevant rows.

For example, imagine you're relating a Plants model to Product viewed event model with these foreign keys:

• PLANT_ID ⇔ PRODUCT_ID
• PDP_URL ⇔ URL

Product viewed events will only be related to a particular plant if both the PRODUCT_ID and and URL from the event data matches a particular plant's PLANT_ID and PDP_URL. Having both keys ensures that only Product viewed events from a particular page and with a particular PRODUCT_ID are related to a particular plant.

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 Query tab. You can also apply additional configurations in its Columns tab.

For example, you can:

• Disable a column from being part of the model.
• Provide an alias for it to appear as in the visual audience builder. To do so, click the pencil icon that appears next to the column name when you hover over it.

• Redact values so they don't appear in the audience preview or explorer.

• Turn on suggested values for the visual audience builder. For example, if you're creating an audience you want to filter on "Brand," turning on suggestions populates a dropdown with values—Nike, Adidas, etc.—found in the dataset.

You can also refresh suggestions and select how frequently you want suggestions to be refreshed.

Schema management

As your Customer Studio implementation becomes more advanced, your schema may start to look increasingly complex, with multiple parent models all shown in the schema overview. Hightouch offers a few features to help you better understand and manage models in a complex schema.

The schema overview pages only show models related to the source displayed in the top left dropdown. If you don't see models you expect, ensure the correct data source is selected.

Search for models

The search function provides a way to find models and jump between them quickly. Click the magnifier icon and then enter the name of the model you're searching for.

Focus on a parent model and its relationships

If you know which parent model and its related models you want to work with, click it to open its detail page. From the model detail page, click View model schema. Hightouch focuses on and rearranges related models based on their connection to the parent model.

This view is beneficial if you have multiple parent models that connect and you want to make sense of your schema from the perspective of each parent model.

Delete models

You can delete any model from its detail page by clicking the horizontal three-dot menu and clicking Delete.

Legacy schema setup

On May 11, 2023, Hightouch released a new schema builder UI. Though the UI is improved, the underlying concepts remain the same.

If you're in the legacy schema builder, you can access the new schema experience by clicking New schema experience on the top right of the Schema page.

If you're in the new schema builder and want to access the legacy one, click Legacy schema.

Legacy 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.

Legacy 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.

Legacy 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.

Legacy relationship setup

In the legacy schema builder, you need to setup models before you can define relationships between them. 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 relationship setup

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, and it must have the same source as the parent model.
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.

Through relationship setup

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.