Klaviyo

Klaviyo is an sms and email marketing automation platform for e-commerce, which helps grow businesses.

Data integration: Skyvia supports importing data to and from Klaviyo, exporting Klaviyo data to CSV files, replicating Klaviyo data to relational databases, and synchronizing Klaviyo data with other cloud apps and relational databases.

Backup: Skyvia Backup does not support Klaviyo.

Query: Skyvia Query supports Klaviyo.

Establishing Connection

To create a connection with Klaviyo, you need to specify Private API Key and Public API Key.

Private API Key — REST API authentication token for connecting to Klaviyo. A private API key is also used for reading data from Klaviyo and manipulating sensitive objects, such as lists. You can manage an API token in the Klaviyo interface. If needed, you can also generate multiple private API keys in your Klaviyo account. Read more about private API keys here.

Public API Key — a unique identifier for your Klaviyo account, also called your Site ID. Every Klaviyo account has only one public API key. Read more about the public API key here.

Getting Credentials

To get the API Keys, perform the following steps:

1. Sign in to Klaviyo.
2. Click the User icon in the bottom left corner.
3. Select Settings on the appeared menu.
4. Switch to the API Keys tab and copy your Public API Key and Private API Key.

Creating Connection

Enter the obtained API Keys in the Connection Editor.

Connector Specifics

Object Peculiarities

Read-only Objects

The following Klaviyo objects are read-only: Accounts, EmailCampaignTags, SMSCampaignTags, SegmentProfiles, SegmentTags, ListTags, EventMetrics, EventProfiles, FlowActions, FlowTags, GroupTags.

EmailTemplates

You can update only imported HTML templates in the EmailTemplates object. You can’t update drag & drop templates.

Campaigns

The Campaigns Object displays only the Email campaigns. It does not display SMS campaigns. When inserting a new campaign, a new template is created based on the template specified during import. For example, if you import five campaigns and set the same TemplateId value for all campaigns in the import package, each campaign will have a unique TemplateId field value in the created records.

EmailCampaigns and SMSCampaigns

The CampaignMessages field returns empty results when querying. This field is needed for importing data to these objects.

Profiles

If you query all records without filters from this object, it returns empty in the query results. You can get its specific records using filter by PersonId. You can retrieve the PersonId values from the ListExclusions and ListMembers object. You can also get it from Klaviyo UI. Follow the steps below for this.

1. Go to Klaviyo and click Audience -> Profiles.
2. Open the specific profile.

3. Copy its PersonId from the URL.

   

ListMembers

The record is successfully added to the ListMembers object only if there is no record with the specified ListId and Email. If the record already exists in the object for the specified ListId and Email, and if values are specified for the PhoneNumber or PushToken fields, then the Update operation is performed instead of Insert, i.e., a new record will not be added, but instead the PhoneNumber and PushToken fields will be updated for the existing record with the ListId and Email fields.

ListSubscribe

ListSubscribe object allows retrieving data only by Email and PhoneNumber. Values for both columns must be specified in a filter or the SQL command WHERE clause united by the AND operator. For example,

SELECT t.* FROM ListSubscribe AS t WHERE (t.ListId = 'YvXkqE' AND t.Email = '[email protected]' AND t.PhoneNumber = '+13239179104')

You can insert records to this object for lists with a Single opt-in turned on. For other lists, record inserting finishes successfully, but no records are added.

DailyMetrics, WeeklyMetrics, MonthlyMetrics

The DailyMetrics, WeeklyMetrics, and MonthlyMetrics are the child objects for the Metrics object. We recommend filtering by MetricId when querying data from the DailyMetrics, WeeklyMetrics, and MonthlyMetrics to increase query performance.

Skyvia does not require filtering by the parent object record Id when querying data from a child object. Without filtering by parent record Id, Skyvia first queries all the parent object records, reading each record Id. Then Skyvia looks up child object records to each parent object record Id. This approach allows querying child objects without knowing their parents, but this method consumes time and API calls. It uses at least one API call for every parent object record. Thus, working with these tables may affect performance. We strongly recommend using filters on the parent object fields when querying data from child objects.

DailyMetrics, WeeklyMetrics, MonthlyMetrics store metrics for calendar days, weeks, and months respectively. when querying data from these tables you can set the date range for data selection. To do this, set filters by Date, WeekStartDate and MonthStartDate fields and =, >, >=, <, <= operators.

If you don’t use filters by date when querying data from these objects, by default, Klaviyo returns data for one calendar year before today.

If you use the filter by start date only, Klaviyo returns the data for a calendar year from the start date or until today if a year has yet to pass from the start date.

These objects return the data only for the full and already passed calendar days, weeks (Monday till Sunday), and months (1 to 30 (31) days). They do not return data for current days, weeks or months. You can’t select a part of the period, only full period returns in the query result.

If you filter by the date range, which is wider than a full calendar week or month, Klaviyo returns the data only for the full included calendar week or month. If you filter by the date range, which is less than a calendar week or month, Klaviyo returns the empty result.

For example, you perform the following query:

SELECT * FROM MonthlyMetrics WHERE MonthStartDate > '2022-12-25' AND MonthStartDate < '2023-02-05'

This query result will return only the data for the full month included in the selected period (from the 1st to the 31st of January).

Incremental Replication and Synchronization

Skyvia supports Replication with Incremental Updates for such Klaviyo objects as Images, Campaigns, EmailCampaigns, SMSCampaigns, EmailCampaignMessages, SMSCampaignMessages, EmailTemplates, Lists, ListExclusions, Metrics, DailyMetrics, WeeklyMetrics, and MonthlyMetrics.

Incremental Replication tracks only the inserted records for the DailyMetrics, WeeklyMetrics, and MonthlyMetrics objects.

When replicating DailyMetrics, WeeklyMetrics, and MonthlyMetrics, Skyvia pulls data only for the already passed periods. Incremental Replication does not pull data for the current date for DailyMetrics, the current week for WeeklyMetrics, and the current month for MonthlyMetrics.

Skyvia supports Synchronization for Campaigns, Lists, and EmailTemplates.

DML Operations Support

Operation	Object
INSERT, UPDATE, DELETE	CouponCodes, EmailTemplates, Lists, Templates, CatalogCategories, CatalogVariants, CatalogItems, Tags, TagGroups, EmailCampaigns, SMSCampaigns
INSERT, UPDATE	Campaigns, Coupons, Images, Profiles
INSERT	ListMembers, ListSubscribe, PeopleExclusions
UPDATE	EmailCampaignMessages, SMSCampaignMessages, Flows, Segments
DELETE	ListProfiles

Stored Procedures

Skyvia represents part of the supported Klaviyo features as stored procedures. You can call a stored procedure, for example, as a text of the command in the ExecuteCommand action in a Target component of a Data Flow or in Query.

AddListProfiles

The following command inserts records to the ListProfiles object.

call AddListProfiles(:ListId, :PersonId)

CreateEvent

The following command adds new event for specific existing profile

call CreateEvent(:email,:phone_number,:metric,:properties,:time,:value)

PARAMETER NAME	DESCRIPTION
Email	Email of the profile for which the event is being created
Phone_number	Phone number of the profile for which the event is being created
Metric	The associated metric for the event
Properties	Properties of the new event in format, for example {"Color": "Grey","Tovar": "TV-1506"}
Value	A numeric value to associate with this event. For example, the dollar amount of a purchase

ProfileAndEvent

Use the following procedure to create new event together with a new profile

call CreateProfileAndEvent(:email, :phone_number, :first_name, :last_name, :city, :region, :country, :zip, :consent, :metric, :properties, :time, :value)

PARAMETER NAME	DESCRIPTION
Email	Email of the profile for which the event is being created
Phone_number	Phone number of the profile for which the event is being created
Consent	List of strings, for example ['sms', 'email', 'web', 'directmail','mobile']
Metric	The associated metric for the event
Properties	Properties of the new event in format, for example {"Color": "Grey","Tovar": "TV-1506"}
Value	A numeric value to associate with this event. For example, the dollar amount of a purchase

SuppressProfiles

To change the profile status to suppressed, use the command

call SuppressProfiles(:SuppressionEmail)

PARAMETER NAME	DESCRIPTION
SuppressionEmail	The list of emails for suppression in the following format [{"type":"profile","attributes":{"email":"[email protected]"}}]

UnsuppressProfiles

To change the profile status to unsuppressed, use the command

call UnsuppressProfiles(:SuppressionEmail)

PARAMETER NAME	DESCRIPTION
SuppressionEmail	The list of emails for suppression in the following format [{"type":"profile","attributes":{"email":"[email protected]"}}]

Supported Actions

Skyvia supports all the common actions for Klaviyo.