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.

📘

Switch Mapping Options

Switching between mapping options clears any existing field selections and mappings configured in the current option.

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 exists, CleverTap updates or adds properties to the existing profile.
  • If a matching profile does not exist, 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 exists, CleverTap updates or adds properties to the existing profile.

🚧

Identity Field Validation Rules

• Identity/CleverTap ID values cannot be: undefined, null, na, n/a, 0, nil, -1, infinity, -infinity, inf, -inf, nan, -nan, empty, or xxxxxx.
• Values exceeding 1024 characters will be marked as errors.

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 a 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 the 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 a Column
   Choose a column from your database that contains user-related information, such as email, name, Customer Type, or Wallet Balance.

2. Assign a 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.

📘

System Properties

• Predefined properties like Email and Phone have fixed data types and validation rules. For more information, refer to System User Properties.
• Validation Rules:
  • Phone: Must start with + and contain more than four digits
  • MSG Properties (e.g., MSG-Email, MSG-SMS): Accepts only "0", "1", "true", or "false"
  • Property Names: Cannot include these characters: %, >, <, !, |, &, ., :, ;, $, ', ", \, #

3. Choose a 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. Select a timestamp column to fetch only newly added or updated rows.
3. Define when each event occurred using a created-on timestamp.
4. Choose how events are named (from a column or a static value).
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 (e.g., 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.

🚧

Identity Field Validation Rules

• Disallowed values: undefined, null, na, n/a, 0, nil, -1, infinity, -infinity, inf, -inf, nan, -nan, empty, xxxxxx
• Values exceeding 1024 characters are marked as errors.

Define Updated On Timestamp

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

1. Select a 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 the 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 selection.

Map Created On Timestamp

Use this field to track when each event occurred.

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

For more information, refer to Supported Date Format.

📘

Validation Rules for Timestamp

• If this field is not mapped, CleverTap uses the system timestamp, which may misrepresent actual event timing.
• Invalid formats or data types will cause import errors.

Choose Event Naming Method

Specify how CleverTap should assign names to imported events.

• Event from Column
  Import different event types using a column that contains event names. Ideal for dynamic event tables.

• Specific Event
  Assign a single static event name for all imported records. Best for importing a consistent event type.

📘

Validations Rules for Event Naming

• Event names must not include: %, >, <, !, |, &, ., :, ;, $, ', ", \, #
• System events cannot be imported or processed.

Map Event Properties

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

1. Select a Column
   Choose a column with event-related details like 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.

📘

Points to Remember

• System event properties and Charged: Item|Properties cannot be imported.
• Event property names must not contain: %, >, <, !, |, &, ., :, ;, $, ', ", \, #

3. Select Data Type
   Choose the appropriate data type based on your column values:

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 allows you to push the selected random data from the data warehouse's database 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.

🚧

Event Data Sync During Dry Run

When performing the dry run of event data, syncing may result in duplicate events. To maintain data integrity, avoid syncing events unless absolutely necessary.

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 so you don’t hit errors during the Dry Run.
• Use Incremental Timestamps: Always map an “updated at” or timestamp column so you can do 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, or a Custom Schedule, or opting for Manual Triggering. For detailed instructions, refer to Schedule Import.