Overview

After configuring import, this step lets you map your data warehouse columns to CleverTap’s user profiles or events. This ensures each row of data updates the correct fields in CleverTap.

• User Profile Data: Map user-specific fields such as email, first_name, last_name, or any other attribute from the database, to the user profile fields in CleverTap.
• Event Properties: Map event-specific columns such as add_to_cart, product_viewed, purchased to CleverTap event properties.

Use the toggle to switch between User Profile Data and Event Data mapping options.

User Profile Data

Configure how user data is mapped and imported from the data warehouse into CleverTap. This step ensures that user profiles are correctly identified, updated, and enriched with relevant attributes.

The mapping process consists of the following three steps:

1. Map identity to specify how incoming user records from data warehouses are matched to user profiles in CleverTap.
2. Select a timestamp column to fetch only newly added or updated records.
3. Map Snowflake columns to CleverTap user properties.

Map Identity

This step specifies how incoming user records from data warehouses are matched to user profiles in CleverTap, using either a unique identity field (such as email, phone number, or user ID) or the system-generated CleverTap ID.

• Identity: A unique user identifier, such as email, phone number, or custom ID, used to match profiles in CleverTap. The maximum length allowed is 1024 characters. When using Identity:
  • If a profile with the same identity already exists, CleverTap updates or adds properties to it.
  • If no matching profile exists, CleverTap creates a new profile and generates a corresponding CleverTap ID.
• CleverTap ID: A system-generated unique identifier assigned to each user profile in CleverTap. The maximum length allowed is 1024 characters. When using CleverTap ID:
  • The ID must be provided when making profile updates.
  • If a profile with the same ID already exists, CleverTap updates or adds properties to it.

Define Updated On Timestamp

Define the Updated On field to ensure CleverTap imports only new or updated rows since the last sync, optimizing performance and avoiding duplicate processing.

1. Select Timestamp Column
   Select a column that tracks when each row was added or updated. The following column types are supported:

   • NUMERIC
   • DATE
   • TIMESTAMP
   • TIMESTAMP_WITH_TIMEZONE

2. Configure Date Format
   Specify how CleverTap should interpret the timestamp values:

   • If the column type is NUMERIC, choose one of the following:

     • Epoch Seconds – 10-digit timestamps (for example, 1640995200)
     • Epoch Milliseconds – 13-digit timestamps (e.g., 1640995200000)

   • If the column type is DATE, TIMESTAMP, or TIMESTAMP_WITH_TIMEZONE, CleverTap automatically detects and processes the format, without requiring a format selection.

Map User Profile Properties

This step maps data warehouse columns to CleverTap user properties, ensuring seamless data import and accurate user profile updates. To do so, perform the following steps:

1. Select Column
   Choose a column from your database that contains user-related information, such as email, name, Customer Type, or Wallet Balance.

2. Assign User Property
   Map the selected column to a corresponding CleverTap user property.

   • Select from available system properties (for example, email_id maps to Email).
   • If the desired property isn’t listed, enter a new name to create a custom property.

3. Choose Data Type
   Select the appropriate data type for the column. Use the table below to determine when and how each type should be used:

Event Data

Configure how event data is mapped and imported from the data warehouse into CleverTap. This setup ensures event records are accurately linked to user profiles, timestamped correctly, and enriched with event-specific properties.

The mapping process consists of the following five steps:

1. Map identity to associate event data with user profiles in CleverTap.
2. Map Updated On Column to fetch only newly added or updated rows.
3. Map Created On Timestamp.
4. Choose Events to Import (from a column, static value, or as a Charged event).
5. Map Snowflake Columns to CleverTap Event Properties.

Map Identity

This step specifies how incoming event records from the data warehouse are matched to user profiles in CleverTap, using either a unique identity field (for example, email, phone, or user ID) or the system-generated CleverTap ID.

• Identity: Matches events to user profiles using a unique field. The maximum length allowed is 1024 characters.
• CleverTap ID: Matches records using CleverTap’s system-generated ID.

Map Updated On Column

This field ensures CleverTap imports only newly added or updated event records since the last sync.

1. Select Timestamp Column
   Choose a column that indicates when a row was last added or updated. Supported types:

   • NUMERIC
   • DATE
   • TIMESTAMP
   • TIMESTAMP_WITH_TIMEZONE

2. Configure Date Format

   • If the column type is NUMERIC, choose:
     • Epoch Seconds (for example, 1640995200)
     • Epoch Milliseconds (for example, 1640995200000)
   • If the column type is DATE, TIMESTAMP, or TIMESTAMP_WITH_TIMEZONE, CleverTap automatically processes the timestamp without requiring a format to be selected.

Map Created On Timestamp

Use this field to track when each event occurred.

To map the created-on timestamp, perform the following steps:

1. Select Column: Choose the column containing event timestamps.
2. Configure Date Format: Select the format used in the timestamp.

For more information, refer to Supported Date Format.

Choose Events to Import

This step determines how CleverTap names each imported event.

• Events from Column

  Import different event types using a column that contains event names. Ideal for dynamic event tables. For example, if your warehouse table includes an event_name column with values such as product_viewed, added_to_cart, and order_placed, each row imports into CleverTap as its own event, named directly from that column value.

• Specific Event

  Assign a single static event name for all imported records. Best for importing a consistent event type. For example, mapping an entire order_history table to a single order_placed event, regardless of what the source columns are named.

  • Charged Event

    Import purchase or transaction data as the Charged event. Selecting this option enables Item Data Mapping for the Items property. For more information, refer to Charged Event Data.

Map Event Properties

This step maps columns to CleverTap event properties, providing context and detail to each event.

1. Select Column

   Choose a column with event-related details such as category, price, or transactionID.

2. Enter Event Property

   Assign a name for the corresponding CleverTap event property. You can use existing property names or enter new ones to create custom event properties.

3. Select Data Type

   Choose the appropriate data type based on your column values:

   Data Type	When to Select	Example Values	Validation
   String	Text values such as category or customer type	"Jeans", "Premium User"	No validation
   Integer	Whole numbers, such as item count or balance	30, 500	Must be numeric
   Float	Decimal values such as price or discount	4.7, 99.99	Must be numeric
   Long	Large integers such as transaction IDs	123456789012345	Must be numeric
   Boolean	Binary flags, such as order status	true, false, 0, 1	Only accepts: "0", "1", "true", "false"
   Date	Timestamps such as event or checkout time	2024-03-18, 03/18/2024	Must match supported format. For more information, refer to Supported Date Format.

You can toggle on Auto Map Columns to automatically match your source columns to CleverTap event properties by name, and to infer a data type for each match. Auto mapping runs once, when you first configure the import, and is not available when editing an existing import. Every auto-mapped row remains editable, so you can adjust any property name or data type it selects. To start over, turn the toggle off to clear all auto-mapped values.

Charged Event Data

Charged event stores item-level detail in a reserved Items field, an array of objects where each item can carry its own sub-properties, such as category, product name, quantity, and price.

The Items array only supports the array-of-objects structure.

To map Charged event items, perform the following steps:

1. Select Column

   Choose the column that contains the array data. The destination property is always Items. You cannot map this column to a different property name.

2. Confirm Array Structure

   Each item in the array must be an object with its own sub-properties, such as category, product name, and quantity, for example:

   

   JSON

   [
     {
       "price": 194, 
       "productId": 7795, 
       "quantity": 7
     }, 
     {
       "price": 664, 
       "productId": 4235, 
       "quantity": 1
     }
   ]

Supported Date Format

If the selected column is of Date datatype, then you do not need to provide the date format. You can select from the following date format patterns when mapping Date type fields:

Perform Dry Run (Recommended)

After completing your mapping configuration, CleverTap strongly recommends performing a Dry Run to simulate the import and validate your settings, without affecting your CleverTap account. This step helps identify potential errors early and ensures your mapping is accurate.

Click Perform Dry Run after completing your mapping configuration. CleverTap will do the following:

1. Connects to the database of your data warehouse.
2. Retrieve random samples from your mapped data.
3. Validates the configuration as follows:
   • Mapping accuracy and configuration.
   • Data type compatibility.
   • Field validation requirements.
   • Potential errors or conflicts.
4. Displays results with detailed feedback.

Dry Run Results

The dry run results display comprehensive information about your mapping validation:

Validation Outcomes

After mapping data fields from the data warehouse to CleverTap, the system runs a validation check to assess configuration accuracy, data quality, and identity alignment. The outcomes are categorized as follows:

• Successful Validation: Indicates that the mapping and sample records meet CleverTap’s data structure and formatting requirements.
  • User profile data or event data from your data warehouse's database was validated successfully.
  • Random sample records processed without errors.
  • Mapping configuration confirmed as accurate.
  • Data types are properly aligned between your data warehouse and CleverTap.
• Error Detection: Indicates that the mapping contains issues that must be resolved before proceeding with the import.
  • Invalid data types or formatting issues.
  • Identity/CleverTap ID validation failures.
  • System property validation errors (Email, Phone format issues).
  • Prohibited characters in property names or event names.
  • Date format mismatches.
• Interpreting Results: Use the visual indicators to understand the outcome of your validation and take the appropriate next steps.
  • Green (Success): Mapping is correctly configured and ready for import.
  • Red (Error): Mapping must be corrected before proceeding with import.

Sync User Profile and Event Data (Optional)

Syncing user profiles and event data lets you push the selected random data from the data warehouse to CleverTap. For user profiles and events, if the profile is not present in CleverTap, a new profile is created, and user properties are captured against this profile.

Best Practices for Mapping

When configuring import mapping in CleverTap, keep the following best practices and tips in mind to make the most of these features:

• Validate Data Upfront: Clean and verify your source data before import. For example, ensure Email addresses are well-formed and active to avoid bounces in the future. Check phone numbers and dates to avoid errors during the Dry Run.
• Use Incremental Timestamps: Always map an “updated at” or timestamp column to enable incremental updates.
• Consistent Formats: Keep date and time formats uniform in your selected data warehouse. If you choose Date type, ensure the column’s format (YYYY-MM-DD, etc.) matches what you set in CleverTap. Inconsistent formats will cause parsing errors.
• Leverage Dry Runs: Always run the Dry Run feature before the actual import. This is like previewing a snowball before throwing it – it catches mapping issues so you can fix them without affecting live data.
• Limit Data to What’s Needed: Import only the columns you use in CleverTap. Avoid mapping extraneous fields or properties. This makes the process faster and reduces risk.
• Security and Access: Use a dedicated data warehouse user with minimal permissions for the integration. Grant only read access to the tables/columns you need.
• Naming Conventions: Use consistent names for properties and events. Avoid special characters in property keys (CleverTap does not allow the following characters: %, <, >, !, |, &, :, ;, $, ', ", \, #). If needed, map such keys to valid property names in CleverTap.
• Segmentation of Data: Consider filtering your data warehouse data to exclude obsolete or inactive users/events before import. This keeps your CleverTap instance clean and focused on relevant customers.

By understanding these options and best practices, you can ensure your data warehouse-to-CleverTap imports run smoothly and keep your customer data accurate and up to date.

In the next step, schedule the import by choosing Repeat Intervals, Custom Schedule, or Manual Triggering. For detailed instructions, refer to Schedule Import.