Imports via SFTP

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 .
2716

Import Data via SFTP

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

Add New Import Key

  1. Enter the following details:
FieldDescription
UsernamePrepopulated field that displays your CleverTap Account ID.
Name your import keyEnter the name for your import key.
Paste your SSH public keyEnter the SSH key pair generated and shared with Clevertap in the public key file.

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.

time_stampidentitycookie_idid_nameid_emailid_phoneis_marriedageFailure Description
1574668407[email protected]dummy_cookie-001JohnDoe-001[email protected]+918876543211Y21Phone number is not valid
1574668408[email protected]dummy_cookie-002JaneDoe-003[email protected]+918876543212N22Phone number is not valid

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:

Column Header

Description

ts

  • This field is optional.
  • Indicates the timestamp of the file upload.
  • If this information is not given in the file, it indicates the timestamp when CleverTap processed the file.

ObjectId/Identity/guid/fbid

  • This field is mandatory.
  • Uniquely identifies the user who performed the event.

The following is an example of an events CSV file:

event_nametime_stampidentitycookie_iditem_nameitem_priceitem_delivery_datestatus_delivered
Purchased1578503667[email protected]cookie_123Awesome Ring - 10123$D_1589274432true
Purchased1578503667[email protected]cookie_123Awesome Ring - 11123$D_1589274432false
Purchased1578503667[email protected]cookie_123Awesome Ring - 12123$D_15892744321
Purchased1578503667[email protected]cookie_123Awesome Ring - 13123$D_15892744320

📘

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:

Column Header

Description

ObjectId/Identity/guid

  • This field is mandatory.
  • Uniquely identifies the user.

ts

  • This field is optional.
  • Indicates the timestamp of the file upload.
  • If this information is not given in the file, it indicates the timestamp when CleverTap when CleverTap processed the file.

The following is an example of a profile CSV file:

time_stampidentitycookie_idid_nameid_emailid_phoneid_phone.operationis_marriedagePlaylistPlaylist.operationMSG-SMSMSG-DNDSMS
1574668407[email protected]dummy_cookie-001JohnDoe-001[email protected]+918876543211true21Rock$addtruefalse
1574668408[email protected]dummy_cookie-002JaneDoe-002[email protected]+918876543212false22Pop$removefalsetrue
1574668409[email protected]dummy_cookie-003JohnRoe-003[email protected]+918876543213$set12510
1574668410[email protected]dummy_cookie-004JaneRoe-004[email protected]+918876543213$delete02801
[email protected]dummy_cookie-005JaneRoe-005[email protected]+91887654321332

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:

Expected keys

Description

type

The event/profile/(any other type of data that we may support in the future).

fileName

The name of the data file.

clientEmail

This is a mandatory field. We send the update mails to this email ID.

columns

This is an optional field.

  • ctName - This is an optional field. The actual property name in CleverTap.
  • dataType - This is an optional field. The supported dataTypes are STRING, INTEGER, FLOAT, BOOLEAN, and LONG. If a dataType is not defined, then it is considered STRING.

📘

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:

Error MessageResolution
No data file foundThe data file is not uploaded. Upload the data file again and create a new manifest file for it.
File couldn't be readThe file is not in the expected format. Upload the file again with the correct format, that is, CSV for data files and manifest file for mapping.
Internal infra errorUpload the file again followed by a new manifest file.
Incorrect details in manifest fileThis error file is emailed to you. Check the manifest file for incorrect details.
Atleast one of identity / fbId / gpId / objectId is mandatory under 1024 chars to identify the userCheck that a minimum of one ID is present.
[Internal error] Error while importing dataThis is an internal error. Try importing the data again.
Event name is mandatoryThe event name is blank or the column name is not available.
Event name belongs to CleverTap restricted system eventsDo not use the event names of the existing system events.
Cannot have more than 100 event propertiesA maximum of 100 event properties is allowed. Check that the event properties do not exceed it.
Profile operation must be one of $add, $set, $remove, $deleteAdd a valid profile operation.

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.

1439

Receive Notifications for Data Import

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:

FormatDescriptionExample
dd/MM/yyyy HH:mm:ssdd: Indicates days from 01 to 31
MM: Indicates months from 01 to 12
yyyy: Indicates 4-digit year
HH: Indicates 2-digit hours from 00 to 23
mm: Indicates 2-digit minutes from 00 to 59
ss: Indicates 2-digit seconds from 00 to 59
14/04/2023 01:46:23, 14/04/2023
MM/dd/yyyy HH:mm:ssMM: Indicates months from 01 to 12
dd: Indicates days from 01 to 31
yyyy: Indicates 4-digit year
HH: Indicates 2-digit hours from 00 to 23
mm: Indicates 2-digit minutes from 00 to 59
ss: Indicates 2-digit seconds from 00 to 59
04/14/2023 01:46:23, 04/14/2023
yyyy/dd/MM HH:mm:ssyyyy: Indicates 4-digit year
dd: Indicates days from 01 to 31
MM: Indicates months from 01 to 12
HH: Indicates 2-digit hours from 00 to 23
mm: Indicates 2-digit minutes from 00 to 59
ss: Indicates 2-digit seconds from 00 to 59
2023/14/04 01:46:23, 2023/14/04
yyyy/MM/dd HH:mm:ssyyyy: Indicates 4-digit year
MM: Indicates months from 01 to 12
dd: Indicates days from 01 to 31
HH: Indicates 2-digit hours from 00 to 23
mm: Indicates 2-digit minutes from 00 to 59
ss: Indicates 2-digit seconds from 00 to 59
2023/04/14 01:46:23, 2023/04/14
dd MMM yyyy HH:mm:ssdd: Indicates days from 01 to 31
MMM: Indicates months from JAN-DEC
yyyy: Indicates 4-digit year
HH: Indicates 2-digit hours from 00 to 23
mm: Indicates 2-digit minutes from 00 to 59
ss: Indicates 2-digit seconds from 00 to 59
14 APR 2023 01:46:23, 14 APR 2023
UnixTracks time as a running total of seconds.1681460089

📘

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.