Manually Import Historical Event Data

read

Overview

While we recommend using Klaviyo's integrations with tools such as Shopify, Magento, Bigcommerce, and our API for custom integrations, it's also possible to manually add event data to Klaviyo. A common example of this is adding historical purchase data from a previous ecommerce platform that Klaviyo doesn't have a prebuilt integration for.

 

Format Your Data

The first step is to format your data correctly in a CSV file. Below is an example of some example Placed Order data, along with a description of the fields below. Each row in your CSV file should represent an action or activity someone performed. For example, "Purchased product" or "Signed up for webinar." If you have multiple actions to track, you should upload them as separate CSV files.

Required Fields:

The two required fields are a timestamp to identify when the event occurred, and a customer property (an email or a unique ID) to identify who performed the action.

  • Timestamp: A timestamp showing the day and time of the event. The format is YYYY-mm-dd HH:MM:SS. For example, 1:30pm on November 14th would be: 2012-11-14 13:30:00.
  • Person//Email or Person//ID: Most likely you will use an email to identify your customers. Some customers will also use an unique ID. You can use both if you choose.

Optional Fields:

There are two special fields to take note of.

  • $event_id: This is a unique identifier for the event.In practice, you should send an $event_id if you have an unique identifier for each event, for example an order ID. You should also set the $event_id if you expect certain events to occur at the same point in time. This can happen when someone takes one action which you will split into multiple events. For example, if someone purchases multiple items and you want to record one event for each item purchased.
  • $value: Use this field if the event your importing includes a value. For example, if you're importing Placed Order event data, each order will have a value. You do not need to prefix your values with a $ or other currency symbol.

If you have additional data about the person who did something, it should go in a column whose label starts with Person// and then the type of data that column has. For instance, if you had the company or organization each person belongs to, you'd want to label that column Person//Organization. The special prefix let's us know to associate the data in that column with each person rather than the activity. The following are special columns you can use to help you identify people:

  • Person//First Name: first name
  • Person//Last Name: last name
  • Person//Phone: phone number
  • Person//Organization: organization
  • Person//Title: job title

If you have additional data about each action, you can include it in extra columns with any labels you want. For instance, if someone purchases something and you want to include what item they bought, you could include that data in a column labeled Item Purchased. Below are some examples.

  • Item Purchased
  • Item Category
  • Product
  • Location

If you don't have data for a particular column, leave it blank and we'll ignore it. For instance, if you don't know someone's organization, you can leave the column for Person//Organization blank and we'll skip tracking that property for that row in your data. 

Once your done formatting your data, save it in a CSV file.

Upload a CSV File of Data

After your data is property formatted, navigate to your account's Integrations tab and click the link to import data by uploading a CSV.

historicalEventByCSV.png

On the next page, you'll be prompted to upload your CSV file.

After you load and import your CSV file, you will have the opportunity to name the event. If you are attempting to map the imported data to an event metric that already exists in your account, make sure to type the event name exactly as it already appears.

Once you've named your event you can preview your data below. When you're ready to import your data click Start importing events.

Note for Ecommerce Stores Loading Purchase Data

If you're uploading purchase data, you'll want to upload two files - one for the overall order, and a second that includes item by item data.  For example, a customer might place an order for multiple products. In this case there would be one event for the Placed Order, and then separate events for each of the Ordered Products. Here's what the headers of those files should look like typically: 

Placed Order

Person//Email Person//First Name Person//Last Name Timestamp $event_id $value Quantity Items
John@gmail.com John Smith 2014-03-10 08:55:01 12425 49.00 2 ["To Kill a Mockingbird", "Pictionary"] 

Ordered Product

Person//Email Person//First Name Person//Last Name Timestamp $event_id $value Quantity Item
John@gmail.com John Smith 2014-03-10 08:55:01 12425 24.00 1 To Kill a Mockingbird

Note

We do not use the $ to prefix order values. Use just numbers to indicate the price or value of an order. Using the $value header will populate the data correctly in your Klaviyo account.

The reason to include two metrics is that it makes it easier to later trigger emails off of both an overall order and off of specific categories / items. 

For Ordered Product events, $event_id needs to be a unique identifier for each combination of the order and the product ordered. One easy way to create a unique identifier is to combine the Order ID and Product ID. For example, in the data below the $event_id is a combination of the Order ID and Product ID.

Person//Email Timestamp $event_id Order Id Product Id
John@gmail.com 2014-03-10 08:55:01 1222195559 12221 95559

Import Troubleshooting

Below are some common issues and resolutions you may find when manually importing historical event data.

My Import is Taking a Long Time

Imports can take between 5 minutes and 24 hours depending upon the size of your list -- we highly recommending beginning the import of any list at least 24 hours in advance of needing it for a given campaign to ensure the import process does not hold up your send.

My Import Keeps Failing

If your import fails, it is likely due to one of the following reasons:

  • Is your file a .csv file? Your file must be in .csv format. If you attempt to upload an Excel file or a .txt file, the import will fail.
  • Did you format your Timestamp column correctly? You should format the date and time as YYYY-mm-dd HH:MM:SS. For example, 1:30pm on November 14th would be: 2012-11-14 13:30:00. As a second check, open your CSV in a text editor to ensure that any trailing 0s in your timestamps are not being stripped out when you save or export your CSV files. If you're working with data in a spreadsheet, make sure the Timestamp cells are set to the correct timestamp format.
  • Do all required columns have data filled in for every row? You must have a column labeled Person//Email or Person//ID. If either of these columns have an empty row, this can cause your import to fail.
  • Does your Person//Email column have an invalid email addresses in it? If you have values within the Person//Email that don't have a valid email address format, this can cause your import to fail.
  • Are all of your columns labeled correctly? Are there any spaces? Make sure that all of your column headers match what is outlined in this guide. You will also want to make sure there are no spaces in your column names.
Was this article helpful?
1 out of 1 found this helpful