Overview

You can import user and event data from third-party SFTP clients such as Bigquery, Hana, Salesforce, etc. to the CleverTap SFTP server. After establishing the connection with the CleverTap SFTP, you can directly import such data to our dashboard automatically at regular intervals. You can then use the power of our analytics, segmentation, and experiences to enhance engagement and retention.

Prerequisites

The following are the prerequisites for importing data via SFTP:

• You must have an SFTP client to import data into CleverTap's SFTP server.
• Ensure that your plan includes the SFTP feature.

Establish Connection with SFTP Server

The CleverTap SFTP server needs to recognize the SFTP client to start receiving files. To establish this secured file transfer connection:

1. Go to Settings > Partner > Import and click SFTP .

2. Select the Saved Keys tab and click + Add key. On clicking, Add new import key popup opens.

3. Enter the following details:

After our SFTP server receives the SSH public key, a user is created for you. After successful authentication, you can seamlessly start transferring data files. You can refer to the server URL at the bottom of the Saved Keys tab for connecting to the SFTP URL.

Import Files via SFTP

To import files, use any SFTP client and execute the following commands from the command prompt to upload your files:

1. sftp -i ~/.ssh/id_rsa username@serverURL
2. put filename.csv
3. put file.manifest

You receive an email notification for the following:

• When the file is picked up for processing.
• When it takes longer than expected to process the file, you receive an email notification for file processing every 2 hours till the time file is processed.
• Upon successful file upload and processing.
• When the import fails for some reason, you receive a failure email with a CSV file attachment containing failed records' details. You can download this CSV file and check it for errors.

📘

Private Beta Release

The above feature of sending an email notification to users at different milestones is released in private Beta.

Moreover, you can start using this data in our engagement channels.
The following is an example of an error file. The last column in this file displays all the errors.

For more information about CSV and manifest files, refer to Sample CSV Files for Upload and Sample Manifest File for Upload sections.

Sample CSV Files for Upload

You can upload profiles or events data to CleverTap with an SFTP client. All files (Events and Profiles) must be uploaded in CSV format.

❗️

Order of File Upload

A manifest file upload must follow each CSV file upload to map your data upload with CleverTap properties. The data file processing will start only after the manifest file is uploaded.

Events CSV file

The events CSV file contains all the event details that you want to send to CleverTap. The events file has the following fields:

The following is an example of an events CSV file:

📘

Data File Timestamp

The data file's timestamp column(ts) must have an epoch format.
If any other column has date-time values, mention these in the $D_epoch format.

Profiles CSV file

The profiles CSV file contains all the user profile details you want to send to CleverTap.
The Profiles file has the following fields:

The following is an example of a profile CSV file:

Operations for Profile Properties

You can use the following operations on profile properties:

• $add - This operation will add value to the property array. For example, you can add rock songs as Playlist values.
• $remove- This operation will remove the value from the property array. For example, you can remove Pop from the existing Playlist.
• $set - Use this operation to replace the current value. In our example, the phone number will be set as "+918876543213."
• $delete - This operation removes the property. For example, you can completely remove the property id_phone for Jane Roe and all the values in it.

Communication Preferences for Profiles

The following are the communication preferences available:

• MSG-sms: This field in the file helps to know if the user wants to be notified via an SMS or not.
• MSG-email: This field in the file helps to know if the user wants to be notified via email or not.
• MSG-whatsapp: This field in the file helps to know if the user wants to be notified via WhatsApp channel or not.
• MSG-push: This field in the file helps to know if the user wants to be notified via a push notification or not.
• MSG-dndSMS: This field in the file helps to know if the user has enabled DND for SMS notifications.
• MSG-dndEmail: This field in the file helps to know if the user has enabled DND for email notifications.

📘

Note

To add or remove multiple values in a property, create a separate row for each value.

Sample Manifest File for Upload

The manifest file is a JSON file that maps the custom attributes in your CSV files with CleverTap properties. The format for the manifest file is anyfilename.manifest. Every manifest file must have a unique name. The following keys are mandatory in a manifest file:

📘

Manifest Note

If a column is not mapped to a CleverTap property name, the data is processed with the column name as the property name.

Manifest File for Events

Create a manifest file to map your custom events to CleverTap Events. The following is a sample manifest file for events:

{
    "fileName": "events.csv",
    "type": "event",
    "columns": {
        "event_name": {
            "ctName": "evtName",
                "dataType": "STRING"
        },
		"time_stamp": {
			"ctName": "ts",
      			"dataType": "INTEGER"
		},
		"identity": {
			"ctName": "identity",
      			"dataType": "STRING"
		},
		"cookie_id": {
			"ctName": "objectId",
      			"dataType": "STRING"
		},
		"item_name": {
			"ctName": "Name",
      			"dataType": "STRING"
		},
		"item_price": {
			"ctName": "Price",
      			"dataType": "FLOAT"
		},
		"item_delivery_date": {
			"ctName": "DeliveryDate",
      			"dataType": "STRING"
		},
    "status_delivered": {
      "ctName": "DeliveryStatus",
            "dataType": "BOOLEAN"
    }
},
    "clientEmail": "[email protected]"
  
}

📘

Charged Event

In the case of SFTP import, CleverTap does not support importing items array for the charged event.

Manifest File for Profiles

Create a manifest file to map the details of your user profiles to CleverTap Profiles. The following is a sample manifest file for profiles:

{
	"fileName": "profiles.csv",
	"type": "profile",
	"columns": {
		"time_stamp": {
			"ctName": "ts",
      			"dataType": "INTEGER"   
		},
		"identity": {
			"ctName": "identity",
       			"dataType": "STRING"
		},
		"cookie_id": {
			"ctName": "objectId",
       			"dataType": "STRING"
		},
		"id_name": {
			"ctName": "Name",
       			"dataType": "STRING"
		},
		"id_email": {
			"ctName": "Email",
       			"dataType": "STRING"
		},
		"id_phone" : {
			"ctName": "Phone",
       			"dataType": "STRING"
		},
		"is_married": {
			"ctName": "Married",
       			"dataType": "BOOLEAN"
		},
		"age": {
			"ctName": "Age",
       			"dataType": "INTEGER"
	  },
    "MSG-SMS": {
      "ctName": "MSG-sms",
            "dataType": "BOOLEAN"
    },
    "MSG-DNDSMS": {
      "ctName": "MSG-dndSMS",
            "dataType": "BOOLEAN"
    }
},
    "clientEmail": "[email protected]"
}

Troubleshooting

There may be instances where you see some errors during file upload. The following table lists the errors that you may encounter and the steps to resolve them:

The status for each file is sent via email.

Receive Imports Notification

The data files are picked up for ingestion in the order in which they are uploaded. You can configure a webhook to receive notifications for the following:

• When the file is picked up for processing.
• When it takes longer than expected to process the file, you receive an email notification for file processing every 2 hours till the time file is processed.
• Upon successful file upload and processing.
• When the import fails for some reason, you receive a failure email with a CSV file attachment containing failed records' details. You can download this CSV file and check it for errors.

To configure a webhook, click Receive Notifications and choose an already configured webhook from the list.

View imports

After a successful import, you can view your imported files on the CleverTap dashboard.
Click Settings > Import > Activity log tab to check for your uploaded files. This tab displays your uploaded files and their status.

Supported Date Time Format

CleverTap supports multiple Date Time formats, as shown in the following table:

📘

Key Points to Remember

• CleverTap supports uploading more than one field of type DATE with different date formats
• If the timestamp is missing in any of the fileds of type DATE, CleverTap adds the default timestamp of 00:00:01 to the date value included in the file when uploading it to the dashboard. For example, if the format defined in the manifest file for a particular DATE field is dd/MM/yyyy HH:mm:ss and the file includes the value as 14/04/2023, then the CleverTap stores the value as 14/04/2023 00:00:01 to the dashboard.

FAQs

Q. What is the maximum file size for upload?
A. You can upload a maximum of 5 GB for each data file.

Q. How much time does it take to complete the file processing?
A. It will take up to 2 hours to complete the processing for a maximum upload of a 5 GB data file.

Q. Is segmentation possible on the uploaded data?
A. Yes.

Q. Is engagement possible from the uploaded data?
A. Yes. You can use our engagement channels to engage with your users.

Q. Do you support live campaigns on the uploaded data?
A. Yes. However, the support is available only for Push, Email, and SMS engagement channels.

Q. Can we do uploads at regular intervals?
A. Yes. You can write a script to connect to CleverTap SFTP and transfer data daily when it is ready.

Q. Can we upload any event or profile file without the manifest file?
A. No. Every data file is recognized by a unique manifest file. Every manifest file should have a unique name.

Q. Can we upload any event or profile file without the column headers in the CSV file?
A. Column headers are mandatory for a file. Without the column headers, the file will go into an error state.

Q. What are the mandatory columns in the event and profile data files?
A. The column headers mandatory are ObjectId.