Data Models & Queries

🚧

Cloud Connect recently got an updated UI! Any customers using the original Cloud Connect UI that is found within Lytics Decision Engine under the Data > Connections tab should reference the legacy documentation for data models and queries that can be found here.

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. Write the SQL query.





    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.

  1. Configure the Data Model.



    Name: Data Model name
    Description: The Description of the Data Model.
    Slug: The Data Model name that will be used in the membership and activation fields 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.



    Primary Key: Select the data warehouse ID that will be used to match against profiles in Lytics.
    External Lytics Key: Select a Lytics user profile field that is a unique identifier to map to an identifier in your incoming data source.
    Activated Fields:Select the fields you want to bring into Lytics profiles for activation. Note that each data model can only include 25 columns as activated fields.
    Sync Frequency: Select a time interval to run the query on - this is also the frequency that your Lytics profiles will be updated. The beta options are currently every 1 hour to every 3 weeks.
    Create new Lytics profile: Check this box if you want to create new profiles in Lytics if a profile with a matching ID does not already exist in Lytics. If you do not select this check box, only users with matching IDs to the Data Model IDs will be updated.

  2. Activate the Model. 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).

BQ-SQL-holiday-sample

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, profiles will be updated with a membership field that can be used to create an audience. Activated fields can be added to the Data Model that can then be used for more dynamic audiences.

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.