Clevertap Export Integration

Copy

• Copy for LLM

  Copy page as Markdown for LLMs
• View as Markdown

  Open this page as Markdown
• Open in ChatGPT

  Get insights from ChatGPT
• Open in Claude

  Get insights from Claude
• Connect to Cursor

  Install MCP server on Cursor
• Connect to VS Code

  Install MCP server on VS Code

Overview

CleverTap is a premier customer engagement and retention platform that combines app analytics with marketing tools to help businesses drive user engagement and improve retention. It offers three key functionalities:

• User Behavior Tracking and Analysis: Monitor user actions and analyze product usage patterns.
• Segmentation and Targeted Campaigns: Segment users based on behavior and run personalized campaigns across various channels.
• Campaign Analysis: Evaluate campaign effectiveness to optimize user engagement and business metrics.

The CleverTap integration connects Treasure Data with CleverTap, enabling seamless synchronization of user profiles, events, and audiences. This integration empowers businesses to leverage Treasure Data’s unified data and CleverTap’s engagement tools to enhance their CX, CRM, and retention marketing efforts.

Prerequisites

• Basic Knowledge of Treasure Data
• Basic knowledge of the CleverTap Engagement Platform

Limitations

• The maximum file size for uploading a user audience custom list is 5GB.

Create a New Connection

Before running your query, you must create and configure the data connection on the TD Console. As part of the data connection, you provide authentication to access the integration. Complete the following steps.

1. Open TD Console.
2. Navigate to Integrations Hub > Catalog.
3. Search for and select CleverTap.
4. Select Create Authentication, and provide the credential information for the integration as described in the following table.
5. Select **Continue,**enter a name for the Authentication, and select Done.

Authentication Fields

Configure a Query Result for Export

The TD Console supports multiple ways to export data. Please follow these steps to export data from the Data Workbench.

1. Navigate to Data Workbench > Queries.
2. Select New Query, and define your query.
3. Select Export Results to configure the data exporting.
4. Select an existing CleverTap authentication or create a new one described previously
5. Select Done.

Connector Configuration Parameters

The following table describes the configuration parameters for the CleverTap export integration.

Detailed Guide for Each Target Data Entity

For all target data entities, you need to build a data export query that includes a combination of default and custom fields that adhere to CleverTap API guidelines. For default fields, ensure that the column names match those listed in each feature guide's "Field/Column-Level Specifications" section. The connector automatically normalizes the column names to match CleverTap's required format, so you do not need to worry about case sensitivity.

For example, for the default field "objectId," the column name in your export query can be written in any case, such as "OBJECTID," "objectid," or "OBJECTid." The connector will standardize the column name to "objectId" to align with CleverTap's requirements.

Target Data Entity: User Profiles

For the User Profiles data entity, we support a few operation modes:

• Upload User Profiles: Update user profile information such as gender, phone, etc.
• **Upload Device Tokens:**Add an existing device token to a CleverTap user profile.
• **Demege User Profile:**Demerge a user profile.
• **Update Email/Phone Subscription:**Set a phone number or email status as subscribed or unsubscribed.
• Disassociate A Phone Number: Disconnect a phone number from a user profile.

This document includes one detailed guide section for the Upload User Profiles mode and one section for the four remaining operation modes.

Upload User Profiles Details

I. Building the Export Query

To successfully upload/modify user profile data to CleverTap, you must construct an export query that adheres to specific data specifications. These specifications are divided into two levels.

Export Query Specifications

Field/Column-Level Specifications

Default Field Columns

Field/Column-Level Names For Export Queries

Example Query:

SELECT
  IDENTITY,
  CAST(
 'objectid' AS VARCHAR
  ) AS objectId,
  name,
  email,
  'M',
  CAST(
 '+1234567890123456789' AS VARCHAR
  ) phone,
  '1995-05-15T00:00:00Z',
  CAST(
 email_pref AS BOOLEAN
  ) AS MSG_email,
  CAST(
 'true' AS BOOLEAN
  ) AS MSG_push,
  CAST(
 'true' AS BOOLEAN
  ) AS MSG_sms,
  CAST(
 'true' AS BOOLEAN
  ) AS MSG_whatsapp,
  CAST(
 json_parse('["Newsletters", "Promotions"]') AS arrayvarchar
  ) "Custom Value 1"
FROM
  clever_tap_upload_user_profiles LIMIT 1

Custom Field Columns

Handling Date-Time

The integration can handle values cast as timestamps or in ISO-8601 date-time string format for date-time custom field columns. When using ISO-8601 date-time strings, ensure the value includes the letter “Z” at the end to allow the connector to detect and convert the date-time value into a Unix time value and apply the format “$D_unix_time_value” as required by CleverTap’s API contract.

Examples for Handling Date-Time Custom Field Columns

1. Casting as Timestamp

• Create a column of type string in the source data table:

ALTER TABLE source_data_table ADD COLUMN date_time_field VARCHAR;

• Insert date-time value strings into that column:

INSERT INTO source_data_table (id, date_time_field)VALUES(1, '2024-08-28'),(2, '2024-08-28 15:30:00'),(3, '2024-08-28T15:30:00');

• Cast the column value as timestamp:

SELECT id, CAST(date_time_field AS timestamp) AS date_time_field FROM source_data_table;

2. Using ISO-8601 Date-Time String

• Create a column of type string in the source data table:

  ALTER TABLE source_data_table ADD COLUMN date_time_field VARCHAR;

• Insert date-time value strings into that column, ensuring they are formatted as ISO-8601 with a “T” separator and “Z” at the end:

INSERT INTO source_data_table (id, date_time_field)VALUES(1, '2024-08-28T00:00:00Z'),(2, '2024-05-28T15:30:00.123Z'),(3, '2024-02-28T15:30:00Z');

• Use the column directly in the query:

SELECT id, date_time_field FROM source_data_table;

Handling an Array of String Custom Field Columns

For the integration to accurately interpret a column’s data and map it to an array of string user properties in CleverTap, the following options are available:

1. Direct Use of Array Column

• Create a column of an array of strings in the source data table:

ALTER TABLE source_data_table ADD COLUMN array_column ARRAYVARCHAR;

• Insert an array of string values into this column:

INSERT INTO source_data_table (id, array_column)VALUES(2, ARRAY['fig', 'grape', 'honeydew', 'kiwi', 'lemon']),(3, ARRAY['mango', 'nectarine', 'orange', 'pear', 'quince']),(4, ARRAY['raspberry', 'strawberry', 'tangerine', 'ugli fruit', 'vanilla bean']),(5, ARRAY['watermelon', 'ximenia', 'yellow apple', 'zucchini', 'apricot']);

• Use the array of string columns directly in the query:

SELECT id, array_column FROM source_data_table;

2. Using String Columns Cast to JSON or JSON Array

• Create a column of string data type in the source data table:

ALTER TABLE source_data_table ADD COLUMN array_string_column VARCHAR;

• Insert array string values into this column:

INSERT
  INTO
 source_data_table(
   id,
   array_string_column
 )
  VALUES(
 1,
 '["apple", "banana", "cherry", "date", "elderberry"]'
  ),
  (
 2,
 '["fig", "grape", "honeydew", "kiwi", "lemon"]'
  ),
  (
 3,
 '["mango", "nectarine", "orange", "pear", "quince"]'
  ),
  (
 4,
 '["raspberry", "strawberry", "tangerine", "ugli fruit", "vanilla bean"]'
  ),
  (
 5,
 '["watermelon", "ximenia", "yellow apple", "zucchini", "apricot"]'
  )
;

• Use the array of string columns by casting to JSON array or JSON in the dataset export query:

  • Cast as array:

SELECT id, CAST(json_parse(array_string_column) AS ARRAYVARCHAR) AS array_column FROM source_data_table;

• Cast as JSON array:

SELECT id, CAST(json_parse(array_string_column) AS JSON) AS array_column FROM source_data_table;

II. Connector Configuration

Details Guide for Other Operation Modes ofUser Profiles

The User Profiles operations allow you to perform various operations to manage and update user profile data. The following sections outline the four different operation modes available under this feature:

1. Upload Device Tokens: Add or update device tokens associated with user profiles.
2. Demerge User Profile: Separate merged user profiles.
3. Update Email/Phone Subscription: Set email or phone subscription statuses for existing user profiles.
4. Disassociate a Phone Number: Disconnects the phone number from existing user profiles.

Each operation mode has specific requirements and configurations that must be followed to ensure successful data uploads to CleverTap.

I. Building the Export Query

To perform the previously described operations, you must construct an export query that adheres to specific data specifications, which are divided into two levels.

Export Query Specifications

• Null Value Columns: Any columns with null values will be ignored.
• Ignored Columns: Any columns not required for the selected operation mode will be ignored.

2. Field-Level Details

Default Field Columns for Each Operation

II. Connector Configuration

For each operation mode, the connector must be configured with specific settings.

Target Data Entity: User Events

I. Building the Export Query

Your export query must adhere to the following detailed data specifications to upload user event data to CleverTap successfully. This section outlines the requirements for both default and custom fields, as well as specific column naming rules and data constraints.

1. Export Query Specifications

2. Field/Column-Level Specifications

Default Field Columns

Custom Field Columns

II. Connector Configuration

Target Data Entity: User Audiences - Custom List

This feature allows you to create a segment of users by uploading a list of user identities to CleverTap. The following details guide you through building the export query and configuring the connector.

I. Building the Export Query

To successfully create a segment by uploading a list of user identities to CleverTap, you must construct an export query that adheres to specific data specifications. These are divided into two levels.

1. Export Query Specifications

2. Field/Column-Level Specifications

Default Field Columns

II. Connector Configuration

(Optional) Export Integration Using the CLI

The TD Toolbelt can trigger the Query Result exporting from a CLI. It would be best to specify the parameters for the exporting job using the --result option of the td query command. For more information, refer to this article.

The format of the option is JSON, and the general structure is as follows.

User Profiles

{
  "type": "clever_tap",
  "account_id": "986-XXX-XXYY",
  "passcode": "XXYY",
  "region": "UNITED_STATES",
  "target_data_entity": "USER_PROFILES",
  "user_profiles_operation": "UPLOAD_USER_PROFILES",
  "array_operation": "REPLACE",
  "skip_invalid_record": true,
  "thread_count": 5
}

User Event

{
  "type": "clever_tap",
  "account_id": "986-XXX-XXYY",
  "passcode": "XXYY",
  "region": "UNITED_STATES",
  "target_data_entity": "USER_EVENTS",
  "skip_invalid_record": true,
  "thread_count": 5
}

Custom List

{
  "type": "clever_tap",
  "account_id": "986-XXX-XXYY",
  "passcode": "XXYY",
  "region": "UNITED_STATES",    "target_data_entity": "USER_AUDIENCES_CUSTOM_LIST",
  "name_for_custom_list_upload": "demo name",
  "email_for_custom_list_upload": "demo@example.com",
  "segment_operation_mode": "UPDATE",
  "skip_invalid_record": true
}

CLI Parameters

Example usage

1. Upload User Profiles

td query \
  -d db_name \
  -w "select identity,
             objectid,
             name,
             email,
             gender,
             phone,
             dob,
             msg_email,
             msg_push,
             msg_sms,
             msg_whatsapp,
             CAST(json_parse('["Newsletters", "Promotions"]') AS JSON) AS 'Custom Value 1'
      from tbl_name" \
  --type presto \
  --result '{
    "type": "clever_tap",
    "td_authentication_id": 514830,
    "target_data_entity": "USER_PROFILES",
    "user_profiles_operation": "UPLOAD_USER_PROFILES",
    "array_operation": "REPLACE",
    "thread_count": 5,
    "skip_invalid_record": true
  }'

2. Upload Device Tokens

td query \
  -d db_name \
  -w "select objectId,
             Id,
             Type,
             p256dh,
             auth
      from tbl_name" \
  --type presto \
  --result '{
    "type": "clever_tap",
    "td_authentication_id": 514830,
    "target_data_entity": "USER_PROFILES",
    "user_profiles_operation": "UPLOAD_DEVICE_TOKENS",
    "thread_count": 5,
    "skip_invalid_record": true
  }'

3. De-Merge User Profile

td query \
  -d db_name \
  -w "select identity
      from tbl_name" \
  --type presto \
  --result '{
    "type": "clever_tap",
    "td_authentication_id": 514830,
    "target_data_entity": "USER_PROFILES",
    "user_profiles_operation": "DEMERGE_USER_PROFILE",
    "thread_count": 5,
    "skip_invalid_record": true
  }'

4. Update Email/Phone Subscription

td query \
  -d db_name \
  -w "select type,
             value,
             status
      from tbl_name" \
  --type presto \
  --result '{
    "type": "clever_tap",
    "td_authentication_id": 514830,
    "target_data_entity": "USER_PROFILES",
    "user_profiles_operation": "UPDATE_EMAIL_PHONE_SUBSCRIPTION",
    "thread_count": 5,
    "skip_invalid_record": true
  }'

5. Disassociate A Phone Number

td query \
  -d db_name \
  -w "select value
      from tbl_name" \
  --type presto \
  --result '{
    "type": "clever_tap",
    "td_authentication_id": 514830,
    "target_data_entity": "USER_PROFILES",
    "user_profiles_operation": "DISASSOCIATE_A_PHONE_NUMBER",
    "thread_count": 5,
    "skip_invalid_record": true
  }'

6. Upload User Events

td query \
  -d db_name \
  -w "select identity,
             objectid,
             ts,
             evtname,
             product_name as 'Product name',
             category as 'Category',
             price as 'Price',
             currency as 'Currency',
             amount as 'Amount',
             payment_mode as 'Payment mode',
             delivery_by as 'Delivery By',
             items_0__category AS 'Items[0].Category',
             items_0__book_name as 'Items[0].Book name',
             items_0__quantity as 'Items[0].Quantity',
             items_1__category AS 'Items[1].Category',
             items_1__book_name as 'Items[1].Book name',
             items_1__quantity as 'Items[1].Quantity'
      from tbl_name" \
  --type presto \
  --result '{
    "type": "clever_tap",
    "td_authentication_id": 514830,
    "target_data_entity": "USER_EVENTS",
    "thread_count": 5,
    "skip_invalid_record": true
  }'

7. Upload Custom List

td query \
  -d db_name \
  -w "select type,
             identity
      from tbl_name" \
  --type presto \
  --result '{
    "type": "clever_tap",
    "td_authentication_id": 514830,
    "target_data_entity": "USER_AUDIENCES_CUSTOM_LIST",
    "name_for_custom_list_upload": "Test",
    "email_for_custom_list_upload": "test@example.com",
    "segment_operation_mode": "UPDATE",
    "skip_invalid_record": true
  }'

Related articles

Other Configurations

• The Result Export can be scheduled to periodically upload data to a target destination.
• All import and export integrations can be added to a TD Workflow. The td data operator can export a query result to a specified integration. For more information, see Reference for Treasure Data Operators.