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

Import Data

  1. Select the Saved Keys tab and click + Add key. On clicking, Add new import key popup opens.
492492
  1. Enter the following details:

Field

Description

Username

Prepopulated field that displays your CleverTap Account ID.

Name your import key

Enter the name for your import key.

Paste your SSH public key

Enter 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 [email protected]
  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_stamp

identity

cookie_id

id_name

id_email

id_phone

is_married

age

Failure Description

1574668407

[email protected]

dummy_cookie-001

JohnDoe-001

[email protected]

+918876543211

Y

21

Phone number is not valid

1574668408

[email protected]

dummy_cookie-002

JaneDoe-003

[email protected]

+918876543212

N

22

Phone 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_name

time_stamp

identity

cookie_id

item_name

item_price

item_delivery_date

status_delivered

Purchased

1578503667

[email protected]

cookie_123

Awesome Ring - 10

123

$D_1589274432

true

Purchased

1578503667

[email protected]

cookie_123

Awesome Ring - 11

123

$D_1589274432

false

Purchased

1578503667

[email protected]

cookie_123

Awesome Ring - 12

123

$D_1589274432

1

Purchased

1578503667

[email protected]

cookie_123

Awesome Ring - 13

123

$D_1589274432

0

📘

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_stamp

identity

cookie_id

id_name

id_email

id_phone

id_phone.operation

is_married

age

Playlist

Playlist.operation

MSG-SMS

MSG-DNDSMS

1574668407

[email protected]

dummy_cookie-001

JohnDoe-001

[email protected]

+918876543211

true

21

Rock

$add

true

false

1574668408

[email protected]

dummy_cookie-002

JaneDoe-002

[email protected]

+918876543212

false

22

Pop

$remove

false

true

1574668409

[email protected]

dummy_cookie-003

JohnRoe-003

[email protected]

+918876543213

$set

1

25

1

0

1574668410

[email protected]

dummy_cookie-004

JaneRoe-004

[email protected]

+918876543213

$delete

0

28

0

1

[email protected]

dummy_cookie-005

JaneRoe-005

[email protected]

+918876543213

32

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]"
  
}

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 Message

Resolution

No data file found

The data file is not uploaded. Upload the data file again and create a new manifest file for it.

File couldn't be read

The 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 error

Upload the file again followed by a new manifest file.

Incorrect details in manifest file

This 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 user

Check that a minimum of one ID is present.

[Internal error] Error while importing data

This is an internal error. Try importing the data again.

Event name is mandatory

The event name is blank or the column name is not available.

Event name belongs to CleverTap restricted system events

Do not use the event names of the existing system events.

Cannot have more than 100 event properties

A 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, $delete

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

14391439

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.

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.


Did this page help you?