Smart Ingest Field Mapping Types

Field mapping is the process of connecting data fields in your data model to fields in Iterable. This configuration ensures that the data you import is correctly formatted and organized in Iterable.

Continue reading to learn more about basic and advanced field mapping methods in Smart Ingest, including: column value, static value, variable value, Liquid template language, arrays, objects, and arrays of objects.

# In this article

# Column value

You can sync fields from your data warehouse to Iterable in a one-to-one fashion. This sends the value stored in the data model column directly to a field in Iterable, without any data transformation.

To do this, select a field name from your data source, and then select an existing corresponding field in Iterable.

# Static value

With static mapping, you can specify a constant value to use for each field value. Select a data type (String, Number, Boolean, or Null) and then enter the static value you want to enter for every record.

For example, you can update users to have a field value in Iterable with the string “Snowflake Smart Ingest” by using static mapping.

# Variable value

With variable mapping, you can send any of the fields that sync job metadata to a field in Iterable:

• Model ID - The ID of the current data model as a string.

• Model name - The name of the current data model as a string.

• Sync ID - The ID of the current sync as an integer.

• Sync run ID - The ID of the current sync run as an integer.

• Current timestamp - The current timestamp as an ISO 8601 string ("YYYY-MM-DDTHH:mm:ss.sssZ").

For example, you may want to send a current timestamp to a smartIngestLastUpdate field in Iterable, to identify the last time this sync impacted a given record.

# Template value

Create custom values using the open source Liquid template language.

The template mapper is convenient when Iterable requires data in a specific format, and you want to avoid baking this specificity into your data model. For example:

• You want to validate email addresses before sending them to Iterable.

• You want to enable list membership using Liquid conditionals instead of using segmentation in Iterable.

# Using the template mapper

To use the template mapper:

1. Open the field mapper by clicking on a model column.

2. Select Template, then select a Variable and Function. The function's code automatically appears in the editor window.

3. Click Apply.

4. In the field input to the right of the templated value, select the Iterable field name to which you want to map the templated value.

# Liquid template formatting

When entering a Liquid template, you can use variables and functions to create custom values.

# Liquid variables

Variables follow this format:

{{ row['company'] }}

The double handlebars enclose a Liquid snippet that generates an output. The column name, company in this example, should be in single quotes inside the square brackets.

Smart Ingest also provides these alternative syntaxes:

{{ row.company }}
{{ company }}

# Liquid functions

Functions follow this format:

A single pipe (|) comes after the variable and before the name of the function and any necessary parameters. See the Liquid Reference or Liquid Cheatsheet for a complete list of available functions.

# Liquid variables in the advanced mapper

You can use metadata variables in your liquid templates, such as a model's name, all model column names, and the timestamp of a sync's last run.

$ <column_name>
$ Model Name
$ Timestamp

You can view all available variables in the Variables column in the template editor.

# Liquid-inspired functions

Smart Ingest provides these non-native functions for you to use out of the box:

# Liquid function chaining

You can use more than one Liquid function in a template by chaining them. For example, you may want to put a string variable into camel case before hashing it. To chain functions, separate them with a single pipe (|):

Examples:

# Learning more about Liquid

For more information on Liquid, see the Liquid docs, Liquid Cheatsheet, and Liquid Reference for a complete list of available functions.

# Create an object

The object inline mapper allows you to construct objects and map them to destination fields in Iterable usings columns from your model.

# Required data format for an object

In this example, a user is planning to build an object that contains standard address fields, where the data model contains each field as a distinct column.

Example data model schema:

Example Iterable user profile:

{
  "email": "user@example.com",
  "firstName": "John",
  "lastName": "Doe",
  "active": "true",
  "address": {
    "address1": "1 Ferry Building",
    "address2": "San Francisco",
    "city": "San Francisco",
    "state": "California",
    "postalCode": "94105"
    },
  "signupDate": "2023-05-12 22:13:52 +00:00"
}

Notice that the address field in Iterable is an object that contains multiple fields. The data model contains these fields as separate columns.

To sync a customer's mailing address fields to an object named address in Iterable, first confirm the fields in the data model and the fields in Iterable that you need to update. Sometimes the field names are different, and it’s important to build the correct hierarchical data structure. In this case, the user would need to map the fields from the data model to build the address object in Iterable.

# Instructions

To create an object field mapping:

1. In the destination field input on the right, either select an existing field name to which you want to map an object, or type the name of a new object to create in Iterable.

   

2. Click on the source field input on the left, select Create an object, then click Apply.

   

3. Select the relevant source fields you want to map in the object, and enter names of destination fields in Iterable as needed.

   

   IMPORTANT

   Smart Ingest can’t “see” the fields contained in nested objects in Iterable.

   • Use the Project Settings page in Iterable to find existing field and object names.
   • Make sure you’re entering correct field names of existing fields.

   You can create advanced mappings such as nested objects and arrays of objects while using this tool, up to two levels deep.

   

4. Review the Advanced Configuration setting that controls object updates. To learn more, read Updating objects.

Here is the final field mapping that takes rows from the example data model and sends the data into the address object on the Iterable user profile:

# Create a simple array

This section covers how to map fields from your data model to an array in Iterable. An array is a single field that contains a list of values of the same type.

In Iterable, arrays are stored in any field data type. For example, of you have a string field in Iterable called favoriteFood, you can store an array of strings in this field. If you have a number field in Iterable called favoriteNumber, you can store an array of numbers in this field.

Smart Ingest treats arrays as an open data type, and you can map a single column that contains a JSON array from your data model to any field type in Iterable.

# Required data format for an array

In Iterable, data is stored in JSON format, including the array data type (format). A JSON array is a hierarchical representation of data that reflects a one-to-many relationship. When you have data stored in a relational database, this relationship is often expressed in the relationships between fields shared across tables, or within rows of data with values that repeat themselves (such as foreign keys).

To map an array, first the data model must contain data in JSON format, as shown here:

[
    "Vinyasa Flow",
    "Studio Cycling",
    "Power Core"
]

If your data model doesn’t already have the array stored in JSON format, but instead is represented in tables, then the data must first be transformed into JSON array format for Iterable to accept it.

To accomplish this, you can use the SQL operation ARRAY_AGG() to aggregate the data into a JSON array.

Here is an example query that pulls from a table named registrations and provides an array of registration_id for each customer_id in the class_registrations column:

SELECT customer_id,
    ARRAY_AGG(registration_id) WITHIN GROUP (ORDER BY signup_date DESC) AS class_registrations
FROM registrations
GROUP BY customer_id;

# Instructions

To map an array:

1. In field mapping, find and select the field you want to map to an array in the left-hand source field menu. Here, the array is stored as class_registrations in the data model.

   

2. Select a compatible field in Iterable to map the array to in the right-hand destination field menu. In this example, the array is mapped to a new field called classRegistrationIds.

   

3. Continue mapping fields to finish creating your sync, or save the sync configuration if you’re editing an existing sync.

# Create an object array

This section covers how to map fields from your data model to an array of objects that you can then import to Iterable.

An object array (also called array of objects) is a list of objects that contain the same set of f ields.

You can sync object arrays to two data types in Iterable: object or nested. Most use cases indicate using the nested data type in Iterable. To learn more about these data types and understand the differences between them, read Field Data Types.

# Required data format for an object array

In Iterable, data is stored in JSON format, including the array data type (format). A JSON array is a hierarchical representation of data that reflects a one-to-many relationship. When you have data stored in a relational database, this relationship is often expressed in the relationships between fields shared across tables, or within rows of data with values that repeat themselves.

To create an array of objects, the data model must contain this relational data in JSON format. Once the format is correct, you can proceed to mapping the field in Smart Ingest.

For an array of objects, the data model should contain the data in JSON format like in this example:

[
    {
        "class_name": "Vinyasa Flow",
        "class_day": "Monday",
        "class_time": "14:00"
    },
    {
        "class_name": "Studio Cycling",
        "class_day": "Tuesday",
        "class_time": "08:00"
    }
]

If your data model doesn’t already have the array stored in JSON format, but instead is represented in tables, then the data must first be transformed into JSON format for Iterable to accept it.

There are three ways you can accomplish this:

• Table selector: Ask your database administrator to create a custom view specific for your Smart Ingest sync that builds the JSON object array within your database. Then, use the table selector and select the view as the sync’s data model.

• SQL query: Use a SQL query as the modeling method for your sync, and build the JSON object array as part of your data model using a JSON construct function that's specific to your data source.

• Template mapper: Use the template mapper to build a JSON object array by using the built-in json_construct function.

# Instructions

There are two ways to map an array of objects to Iterable: a basic object array mapping or a detailed object array mapping.

In the below example, the CLASS_REGISTRATIONS column is sent to Iterable twice: to registrationsTestField and to registrationsTestArray.

The first field mapping syncs CLASS_REGISTRATIONS as a simple column mapping to registrationsTestField. This is a basic object array mapping that sends all the data contained in the object array to Iterable.

The second field mapping is more specific. With detailed object array mapping, you can select particular fields from CLASS_REGISTRATIONS and map them to custom field names in the Iterable object registrationsTestArray.

Option 1: Basic object array mapping

This is a simple and fast method of mapping an array of objects and ensuring that all the data contained in the object array are sent to Iterable. However, this can result in unexpected field names created in Iterable and isn’t recommended if you’re concerned with excess field creation and data hygiene. It's recommended when you want to quickly sync all the data in the object array and don't need to filter or transform the data in any manner (including to a new field name in Iterable).

To set up a basic object array field mapping, follow the same instructions as creating a simple, making sure that the object array has the correct data format stored in the field.

Option 2: Detailed object array mapping

With this configuration, you can select specific fields from your object array and map them to specific field names in the Iterable object. This is useful if you want to ensure that only specific fields are sent to Iterable, if you want to use advanced mapping types, or if you want to choose whether to sync null values for each field as they appear in the array.

To set up detailed object array mapping:

1. In field mapping, click Add Mapping

2. In the drop-down source field menu on the left-hand side, and click Create an Array.

   

   If you don’t see the column name, first use the refresh button. If this still doesn't detect the field, go back and make sure the field contains data in the correct format for an object array.

3. Select the object array field from the available options. The array builder now shows options to add array mappings:

   

4. For each field you want to send to Iterable, select it from the left-hand source field menu.

   

   Smart Ingest uses the first 100 rows from your query to extract the available fields for each array object. To add additional properties that may be missing, type the name into the input field and then click Select field_name as an expected column value.

   

5. Enter the field names for existing and/or new fields that are stored in the object in Iterable.

   

6. Choose whether to sync null values.

   By default, Smart Ingest does sync null values unless you choose to ignore them.

   Non-existent values, however, are treated differently from a filed that has a key and a null value. Non-existent values are skipped.

   For example, if CLASS_REGISTRATIONS[0].class_name exists and CLASS_REGISTRATIONS[1].class_name doesn't exist, CLASS_REGISTRATIONS[0].class_name is mapped and stored in Iterable, and CLASS_REGISTRATIONS[1].class_name won't be mapped—the field is skipped in Iterable and no field is created or updated for that array item.

Given the previous example row and the mappings shown in the screenshot above, Smart Ingest would sync the following payload to Iterable:

[
    {
        "classDay": "Monday",
        "className": "Vinyasa Flow",
        "classTime": "14:00",
        "registrationId": "107422037"
    },
    {
        "classDay": "Saturday",
        "className": "Power Core",
        "classTime": "12:00",
        "registrationId": "154606712"
    }
]

# Want to learn more?

For more information about some of the topics in this article, check out this Iterable Academy course. Iterable Academy is open to everyone — you don't need to be an Iterable customer!

• Smart Ingest: Field Mapping