Cloud Connect Data Models

A Data Model is used by Cloud Connect to link or "connect" a user's external data warehouse to Lytics profiles. Each Data Model represents a set of records defined by a SQL query. Each Data Model will also configure a join key which defines how the Data Model is joined to Lytics Profiles.

Once a Connection to your data warehouse has been created, Data Models can be created via the Data Models tab within the navigation bar:

Creating a Data Model

Click + Create New from the Data Models dashboard and complete the following steps.

1. Select the Connection.

2. Generate the SQL query. Please note that currently, the main identifier must be of type string in the database.
   

   
   

   Query Editor: Write and test standard SQL queries directly in Lytics. Alternatively, copy and paste queries you've tested in your BigQuery or Snowflake instance. When you click Test Query, Lytics will return 10 sample records.

   
   

3. Test the query and validate the results.
   

4. Configure the Data Model.
   

   

   
   

   Name: Data Model name
   Slug: The Data Model name that will be used in the membership and mapped field names in the audience builder. It is important that the name chosen here makes sense for those building audiences in the tool. If no slug is selected, it will auto-populate using the model name.
   Description: A longer description of the Data Model.
   

   Map a Unique Identifier: Select a user profile field that is a unique identifier in the Lytics user table to map to an identifier in your incoming data source.
   

   

   
   

Primary Key: Select a key in your data source — the column from your SQL query results that uniquely identifies each record.
External Lytics Key: Select a Lytics user profile field that corresponds to your primary key. Only identity ("by") fields are available for selection.

Field Mappings: Map source fields to Lytics fields. For each mapping, use the dropdowns to select a Source column from your SQL query results and a Lytics profile field where the data should be stored. You can add multiple mappings to bring in as many fields as needed.

⚠️

Mapping constraints:

• Lytics target fields must already exist in your published schema
• You cannot map to Lytics identity ("by") fields.
• Each Lytics field can only appear once in your mappings

Create Profiles: Checking this will cause new profiles to be created in Lytics for all rows in the result set that do not already match an existing profile. If left unchecked, only existing profiles with matching identifiers will be updated.

Sync Settings: Select how often the sync should run.

• Hourly — runs every hour
• Daily — runs once per day at the specified time
• Weekly — runs once per week on the specified day and time
• Monthly — runs once per month at the specified time

Depending on the frequency selected, additional options may appear for time of day, timezone, and day of week.

5. Create Data Model. Once you have added all the details, click the Create Data Model button in the top right corner to save the configured data model.

6. Activate the Model. Navigate to the model you created from the Data Model list view and click the Activate button. Until the Data Model is activated, its SQL query will not be run against your data warehouse, and Lytics profiles will not be updated with the selected fields.

📘

When building multiple Cloud Connect Data Models with the same primary key, you must select the same External Lytics Key mapping. Selecting a different Lytics key will result in an error when you try to save the new Data Model.

Example Use Case

Consider this scenario to demonstrate why you'd want to create a Cloud Connect Data Model driven audience instead of a standard Lytics audience. Your company sells e-bikes and wants to run a holiday campaign that sends a promotion to any customer who purchased an e-bike in November or December last year. Perhaps you also want to refine your audience to those who are interested in particular bike brands. All of this purchase and product data already exists in your Google BigQuery instance (as shown below).

Instead of directly importing all that purchase history data into Lytics, you can write a SQL query to find which customers meet this audience criteria. By copying this query directly into the Lytics Model Builder (as shown in the generate a query screenshot above), you'll create a new audience in Lytics that will be updated on the frequency interval you decide.

Once the Data Model is activated, the Data Model's slug will be added to the Datamodels field on each matching user profile, and any configured field mappings will sync the corresponding data from your warehouse into the mapped Lytics profile fields. You can use the Datamodels field in the Audience Builder to target members of the Data Model, and the mapped fields for more dynamic audience rules.

SQL Translator

If you would like to skip having to write a SQL query, simply describe the Data Model of what you wish to fetch and using GenAI, Lytics will translate the description to a SQL query for you. Lytics makes queries to your database to ensure that the data is up to date and accurate when creating the query.