Search documentation...

K
ChangelogBook a demoSign up

Define data schema

Customer Studio is available as an add-on on Business tier plans.

Overview

Audience model diagram

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, 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:

Finished schema

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.

Share this page with your technical team if you need assistance with schema setup.

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

Entity relationship diagram model

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

To use Amazon Redshift Spectrum as the source for a parent model, it's best to query a materialized view. This helps avoid Redshift's limitations around nested data.

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.

Source selection

  1. Click Create parent 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.

Table selection

  1. Once you've defined your model and previewed the results, click Continue.
  2. Enter a name for your parent model, for example, "Users."
  3. Optionally, enter a description.
  4. Select a primary key. You must select a column with unique values, like when creating other models.
  5. Select columns for the model's primary label and secondary label. Hightouch displays these column values when previewing audiences.
  6. Click Create parent model.

Parent model creation

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.

Parent model edits

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

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

Entity relationship diagram model

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.

If you want to relate two models, they must have the same underlying source in Hightouch.

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.

Model selection

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

Table selection

  1. Once you've defined your model and previewed the results, click Continue.
  2. Enter a name for your related model, for example, "Plants."
  3. Optionally, enter a description.
  4. 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.
  5. Select the relationship's cardinality: 1:1, 1:many, or many:1.

Cardinality selection

Explicit cardinality is a feature of the new schema builder, released in May 2023. If you created a related model before this release, Hightouch assumed the relationship's cardinality. If you assumption is incorrect, modify it via the dropdown. Cardinality affects your ability to merge columns.

  1. 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.
  2. If there are multiple columns that the parent and related model must match on, enable Multiple join keys and make additional selections.

If you enable multiple join keys, each pair of columns must match to create a relationship between rows.

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

Related model creation

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.

Related model edits

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.

Model selection

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

Table selection

If all of your events are in one table, it can be helpful to create one event model per event. You can use the SQL editor to select events with a specific name or type, for example, "Page viewed," "Order completed," etc.

SQL Editor

  1. Once you've defined your model and previewed the results, click Continue.
  2. Enter a name for your event model, for example, "Product viewed."
  3. Optionally, enter a description.
  4. 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.
  5. Select the relationship's cardinality: 1:1, 1:many, or many:1. In most cases, parent models have a 1:many relationship with events.

Cardinality selection

Explicit cardinality is a feature of the new schema builder, released in May 2023. If you created a related model before this release, Hightouch assumed the relationship's cardinality. If the assumption is incorrect, modify it via the dropdown. Cardinality affects your ability to merge columns.

  1. 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.
  2. If there are multiple columns that the parent and related model must match on, enable Multiple join keys and make additional selections.

If you enable multiple join keys, each pair of columns must match to create a relationship between rows.

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

Event model creation

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.

Event model edits

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:

Many:1 relationship

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

1:many relationship

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

1:many relationship

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.

Merging columns in the Hightouch UI

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_IDPRODUCT_ID
  • PDP_URLURL

Multiple join keys

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.

Column configuration in the Hightouch UI

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.

Column configuration in the Hightouch UI

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

Redacted user

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

Filter condition dropdowns auto-recommend up to 10,000 values for a given column. If a value doesn't show up automatically, you can type it in the search bar and click Add [value]. Make sure that the value you type matches the value in your model exactly.

Filter value suggestions

Check out this troubleshooting section if suggested values aren't appearing.

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

Column configuration in the Hightouch UI

If you want to use the percentile filter on a numeric column, make sure to turn off suggestions for that column.

Schema management

As your Customer Data 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.

Source selection

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.

Model search

The search function only shows 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.

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.

Model schema

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.

Model schema

Deleting a parent model also deletes its relationship to related and event models. Deletions cannot be undone. Ensure you want to delete a parent model before doing so.

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.

The legacy schema builder will be deprecated in June 2023. Please use the new schema builder and if you have any feedback or questions.

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.

Accessing the new schema builder

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

Accessing the legacy schema builder

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.

Adding a new parent model in the Hightouch UI

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

Adding a new parent model in the Hightouch UI

  1. Select an existing supported source.
  2. Select how you want to create your model. Depending on the source, you can either:
  3. 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.

Configuring a new parent model in the Hightouch UI

  1. Click Finish to save your model.

In parent models, each row should be unique and there shouldn't be any duplicates. Make sure to set up your model accordingly.

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.

Adding a new related model in the Hightouch UI

  1. Select an existing supported source.
  2. Select how you want to create your model. Depending on the source, you can either:
  3. Give your related model a descriptive Name.
  4. 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.

Adding a new event in the Hightouch UI

  1. Select an existing supported source.
  2. Select how you want to create your model. Depending on the source, you can either:
  3. Give your event a descriptive Name and select a column to use for the Timestamp.
  4. 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.

Entity relationship diagram model

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.

Adding a new direct relationship in the Hightouch UI

  1. Click Add direct relationship.
  2. Enter a Relationship name, for example, "Customer purchases."
  3. Select the appropriate Related Model; you must have configured it first, and it must have the same source as the parent model.
  4. 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.

Configuring a direct relationship in the Hightouch UI

  1. If relevant, toggle on Merge Columns.
  2. 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.

Entity relationship diagram model

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.

Adding a new through relationship in the Hightouch UI

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

Ready to get started?

Jump right in or a book a demo. Your first destination is always free.

Book a demoSign upBook a demo

Need help?

Our team is relentlessly focused on your success. Don't hesitate to reach out!

Feature requests?

We'd love to hear your suggestions for integrations and other features.

Last updated: Oct 3, 2023

On this page

OverviewRequired skillsetParent modelsParent model setupRelated models and eventsRelationshipsRelated model setupEvent setupBidirectionalityMerge columnsMultiple join keysColumn configurationSchema managementSearch for modelsFocus on a parent model and its relationshipsDelete modelsLegacy schema setupLegacy parent model setupLegacy related model setupLegacy event setupLegacy relationship setup

Was this page helpful?