Import profile and event data with SFTP

Before you begin

Make sure you've set up SFTP data transfer with Klaviyo by setting up your public/private SSH key pair and establishing a connection to Klaviyo's SFTP server.

You will learn

In this article, you’ll learn how to initiate an SFTP import and the requirements for your import, including formatting.

SFTP CSV ingestion allows customers to transfer comma-separated files over SFTP to sync their customer data. Currently, the SFTP import tool supports these operations:

• Event: create
• Profiles: create, update

This feature is ideal for customers who want to bulk import CSV data using an SFTP client of their choice. At this time, we allow the creation of profiles and events in Klaviyo and may, in the future, expose other data types via SFTP import. SFTP import supports a variety of fields on events and profiles as well as any custom field you wish to define.

Initiate your SFTP import

Once connected to Klaviyo’s server, you should see two remote directories titled profiles and events.

At this point, you can transfer CSVs via your SFTP client into either directory to initiate an ingest job. CSVs placed into the events folder will generate Klaviyo events and CSVs placed in the profiles folder will generate Klaviyo profiles.

Valid CSVs require specific headers and cell values must be formatted correctly for ingest to work properly. Similar to Klaviyo’s Create Profile and Create Event API endpoints, certain fields are required for data creation to occur. Valid CSV formats are explained below.

📘

We recommend you test with a small CSV (e.g., several rows) to test data generation before moving to larger CSVs in order to verify ingest works as you expect.

Ingest process considerations

Please note the following about the ingest process:

• Ingested event rows will either create events or result in a “no-operation” (no-op). Given a CSV row with the same headers and values ingested twice, the second ingestion will be a no-op (essentially, the same event with the same identifying properties cannot be created twice). Events are unique on either a unique_id property or the combination of profile, time, and metric name. Uploading the same event CSV twice will result in events only being created once, given the same identifying properties.
• Ingested profile rows will create or update profiles or result in a no-op. Profiles are unique on email, phone number, or external_id. A CSV ingest of profiles can create or update profiles. The same CSV ingested twice will only create or update profiles once.
• At this time, Klaviyo assumes all files in either the events or profiles directory must be ingested and will attempt to ingest duplicate files. We recommend you name files with a timestamp to avoid duplicate file uploads.

General CSV formatting and size limitations

Klaviyo expects the first row of all CSV files passed to be a header row and subsequent rows as values to ingest. It is important to note the following:

• Commas are the only accepted delimiters and all files are expected to have a .csv extension.
• Given row values with commas or other special characters, we expect double quotes to wrap cell values or we will interpret commas as delimiters.

Cell values must be formatted according to the following data types:

• Dates
  Dates must be ISO8601 compliant.
• Phone numbers
  Read our help article on acceptable phone number formatting.

📘

Klaviyo observes the following file limitations:

• Klaviyo will not ingest files with any row (including headers) exceeding 1MB.

Event CSV formatting

The following are required headers for Event CSVs:

• Metric name
  Name of the Klaviyo metric you want to associate the event with. An account can have up to 200 metrics and a metric may be created when an event is created.
• Time
  When the event occurred, in an ISO8601 compliant format.

One or more of the following identifying headers are also required:

• Profile.Email
  The associated customer’s email.
• Profile.PhoneNumber
  The associated customer’s phone number. Note if Profile.PhoneNumber is the only identifying column, you must have SMS enabled in your account.
• Profile.ExternalID
  The associated customer’s ID from another system of record.

You can also include optional special fields such as:

• Value
  The numeric value to associate with the event.
• Unique ID
  A unique identifier for the event. If this is not included, a combination of profile, time, and metric name will be used to create an identifier for deduplication.
• Other profile properties can be specified via Profile.PropertyName. For nested JSON fields, the dot notation can be used like so: Profile.Location.Zip, Profile.Location.Country.

Custom event properties can be added on via any header addition, such as MyProperty. Additionally, custom profile properties can be defined via prefacing props with “Profile,” for example, Profile.MyProperty.

View and download an example event CSV.

Profile CSV Formatting

One or more of the following identifying headers are required:

• Email
  The profile’s email address.

• PhoneNumber
  The profile’s phone number. Note if PhoneNumber is the only identifying column, you must have SMS enabled in your account.

• ExternalID
  A unique identifier used to associate Klaviyo profiles with profiles in an external system, such as a point-of-sale system. Format varies based on the external system.

You can also include optional special fields such as:

• List ID
  The list identifier you want a profile to be single-opted-in to for an email list. If you want all uploaded profiles to be subscribed to the same list, this value will be the same for all rows. Please note that you must provide a valid email if you populate a List ID column in the same row. We will not process rows with an invalid List ID.
• Email Marketing Consent
  A string indicating whether or not a profile has consented to email marketing. Acceptable values are SUBSCRIBE, UNSUBSCRIBED, NEVER SUBSCRIBED. You can consent users into Email marketing via SFTP by providing PhoneNumber, and Email Marketing Consent fields. You can optionally include an Email Marketing Consent Timestamp field as well. View and download an example profile CSV with Email Marketing Consent.
• Email Marketing Consent Timestamp
  The timestamp of when a profile has consented to email marketing.
• SMS Marketing Consent
  A string indicating whether or not a profile has consented to SMS marketing. Acceptable values are SUBSCRIBE, UNSUBSCRIBED, NEVER SUBSCRIBED. You can consent users into SMS marketing via SFTP by providing PhoneNumber, and SMS Marketing Consent fields. You can optionally include an SMS Marketing Consent Timestamp field as well. View and download an example profile CSV with SMS Marketing Consent.
• SMS Marketing Consent Timestamp
  The timestamp of when a profile has consented to SMS marketing.
• SMS Transactional Consent
  A string indicating whether or not a profile has consented to transactional SMS. Acceptable values are SUBSCRIBE, UNSUBSCRIBED, NEVER SUBSCRIBED. You can consent users into transactional SMS via SFTP by providing PhoneNumber, and SMS Transactional Consent fields. View and download an example profile CSV with SMS Transactional Consent.
• SMS Transactional Consent Timestamp
  The timestamp of when a profile has consented to transactional SMS.
• First Name
  Individual’s first name.
• Last Name
  Individual’s last name.
• Organization
  Name of the organization for whom the individual works.
• Title
  Individual’s job title.
• Image
  URL pointing to the location of a profile image; must be in URL format or row will be skipped.
• Location.Address1
  First line of street address.
• Location.Address2
  Second line of street address.
• Location.City
  City name.
• Location.Region
  Region or U.S. State.
• Location.Country
  Country name.
• Location.Latitude
  Latitude coordinate; please use up to four decimal precision.
• Location.Longitude
  Longitude coordinate; please use up to four decimal precision.
• Location.Zip
  Zip code.
• Location.Timezone
  Time zone; we recommend using time zones from the IANA TZ Database.

Custom profile properties can be added on via any header addition (ex. MyProperty)

View and download an example profile CSV.

Checking SFTP ingest job status

To check your SFTP ingest job status, navigate to the Data import tab. Any in progress, failed, or completed jobs will show up in the Recent Imports table. You will receive email notifications and in-app messages for all failed and successful jobs. Klaviyo maintains logs of all jobs and is available to assist in debugging the reasons behind skipped rows.

Finally, please be aware that ingest jobs will complete prior to metrics and profiles being available in your account. Klaviyo can process up to 6 million profile updates per hour without list subscriptions or up to 1 million per hour with list subscriptions. However, updated metrics and profiles may take several minutes to appear. If they do not start populating within 20 minutes after the job completes, please contact Klaviyo support.

Troubleshooting

Access denied

If you encounter an Access denied error, it may be due to one of the following reasons:

• The transfer mode is not explicitly set to ASCII.

  Check if a different transfer mode is being set by default and update accordingly.

• The key is not an OpenSSH formatted public key.

  AWS Transfer Family will only accept the SSH key formats listed in its documentation. If you are using an SSH2 key, you'll need to convert it.

• The file type is not .csv