Integration Specifications

Speed of extraction

Klaviyo is an extremely slow endpoint and loading history can take a considerable amount of time if the data volume is high.

Integration Overview

The Klaviyo API has a non-standard approach to data extraction due to complexity and API limits. Multiple APIs are used to populate certain key tables for Email/SMS and thus endpoints are defined by table.

Integration Availability

This integration is available for:

• Enterprise

• Growth

Entity Relationship Diagram (ERD)

Click here to view the ERD for the Daasity Klaviyo integration illustrating the different tables and keys to join across tables.

The following endpoints are used in data extraction:

API Reference	Endpoint
Metrics	metrics
Metrics	metrics/timeline
Profiles	person/{PERSON_ID}
Lists V2	lists
Lists V2	list/{LIST_ID}
Lists V2	list/{LIST_ID}/subscribe
Lists V2	list/{LIST_ID}/members
Lists V2	list/{LIST_ID}/exclusions_all
Campaigns	campaigns

Metrics TABLE

API Endpoint: api/v1/metrics

Data Mapping

JSON Element	Database Column
MD5(esp_source||':'||esp_integration||':'||id)	key
id	id
name	name
integration::category	source
Daasity: table where metric data is stored	destination
created	created_at
updated	updated_at
Daasity: 'Klaviyo'	esp_source
Daasity: ID generated for each Klaviyo integration	esp_integration_id
Daasity: timestamp when loaded into DB	__synced_at

Business Rules

Business Rule	Value
Load Type	UPSERT
Extraction Frequency	Daily

Campaigns TABLE

Bulk Campaign API Endpoint: api/v1/campaigns

Flow Campaign (i.e. single send within a flow) API Endpoint: /api/v1/metric/#{id}/timeline and generating the Flow when a flow_id is present

Data Mapping

JSON Element	Database Column
MD5(esp_source||':'||esp_integration||':'||id)	key
id	id
name	name
NULL	parent_campaign_key
MD5(flow_id)	automation_key
NULL	automation_position
list_id	list_key
from_email	from_email
from_name	from_name
subject	subject
status_label	status
sent_at	sent_at
campaign_type	campaign_type
Daasity: 'Campaign'	type
created	created_at
updated	updated_at
Daasity: 'Klaviyo'	esp_source
Daasity: ID generated for each Klaviyo integration	esp_integration_id
Daasity: timestamp when loaded into DB	__synced_at

Business Rules

Business Rule	Value
Load Type	UPSERT
Extraction Frequency	Daily

Automations TABLE

API Endpoint: api/v1/flows

Data Mapping

JSON Element	Database Column
MD5(esp_source||':'||esp_integration||':'||id)	key
id	id
name	name
created	created_at
updated	updated_at
status	status
Daasity: 'Klaviyo'	esp_source
Daasity: ID generated for each Klaviyo integration	esp_integration_id
Daasity: timestamp when loaded into DB	__synced_at

Business Rules

Business Rule	Value
Load Type	UPSERT
Extraction Frequency	Daily

​Lists TABLE

API Endpoint: api/v2/lists

Data Mapping

JSON Element	Database Column
MD5(esp_source||':'||esp_integration||':'||id)	key
list_id	id
list_name	name
list_type	list_type
NULL	status
NULL	rating
created	created_at
updated	updated_at
NULL	archived_at
Daasity: 'Klaviyo'	esp_source
Daasity: ID generated for each Klaviyo integration	esp_integration_id
Daasity: timestamp when loaded into DB	__synced_at

Business Rules

Business Rule	Value
Load Type	UPSERT
Extraction Frequency	Daily

Campaign Lists TABLE

API Endpoint: api/v1/campaigns

​Data Mapping.

JSON Element	Database Column
MD5(esp_source||':'||esp_integration||':'||id)	key
lists::id||':'||id	id
lists::id	list_id
id	campaign_id
Daasity: 'Klaviyo'	esp_source
Daasity: ID generated for each Klaviyo integration	esp_integration_id
Daasity: timestamp when loaded into DB	__synced_at

Business Rules.

Business Rule	Value
Load Type	UPSERT
Extraction Frequency	Daily

Members TABLE

API Endpoint: /api/v1/metric/#{id}/timeline

Create members by identifying any new members that do not exist when events are pulled

Data Mapping.

JSON Element	Database Column
MD5(esp_source||':'||esp_integration||':'||person::id)	key
person::id	id
person::$email	email
person::$timezone	timezone
person::$first_name	first_name
person::$last_name	last_name
person::$source	source
NULL	predict_user_id
person::$address1	address1
person::$address2	address2
person::$city	city
person::$state	state
person::$zip	zip
person::$country	country
person::$phone_number	phone_number
person::$longitude	longitude
person::$latitude	latitude
person::consent	consent
person::$consent_timestamp	consent_timestamp
person::created_at	created_at
Daasity: timestamp when extracted	updated_at
Daasity: 'Klaviyo'	esp_source
Daasity: ID generated for each Klaviyo integration	esp_integration_id
Daasity: timestamp when loaded into DB	__synced_at

Business Rules.

Business Rule	Value
Load Type	UPSERT
Extraction Frequency	Daily

Member Attributes TABLE

API Endpoint: /api/v1/metric/#{id}/timeline

Create members by identifying any new members that do not exist when events are pulled.

Data Mapping.

JSON Element	Database Column
MD5(esp_source||':'||esp_integration||':'||person::id)	key
person::id	id
MD5(person::id)	member_key
person::key	name
person::value	value
Daasity: timestamp when extracted	created_at
Daasity: timestamp when extracted	updated_at
Daasity: 'Klaviyo'	esp_source
Daasity: ID generated for each Klaviyo integration	esp_integration_id
Daasity: timestamp when loaded into DB	__synced_at

Business Rules.

Business Rule	Value
Load Type	UPSERT
Extraction Frequency	Daily

Sends TABLE

API Endpoint: /api/v1/metric/#{id}/timeline where event = "Received Email"

Data Mapping.

JSON Element	Database Column
MD5(esp_source||':'||esp_integration||':'||id)	key
id	id
MD5(event_properties::$message)	campaign_key
MD5(event_properties::$flow)	automation_key
MD5(person::id)	member_key
NULL	list_key
person::$email	email
timestamp	event_at
Daasity: 'Klaviyo'	esp_source
Daasity: ID generated for each Klaviyo integration	esp_integration_id
Daasity: timestamp when loaded into DB	__synced_at

Business Rules.

Business Rule	Value
Load Type	UPSERT
Extraction Frequency	Daily

Bounces TABLE

API Endpoint: /api/v1/metric/#{id}/timeline where event = "Bounced Email"

Data Mapping.

JSON Element	Database Column
MD5(esp_source||':'||esp_integration||':'||id)	key
id	id
MD5(event_properties::$message)	campaign_key
MD5(event_properties::$flow)	automation_key
MD5(person::id)	member_key
NULL	list_key
person::$email	email
timestamp	event_at
Daasity: 'Klaviyo'	esp_source
Daasity: ID generated for each Klaviyo integration	esp_integration_id
Daasity: timestamp when loaded into DB	__synced_at

Business Rules.

Business Rule	Value
Load Type	UPSERT
Extraction Frequency	Daily

Opens TABLE

API Endpoint: /api/v1/metric/#{id}/timeline where event = "Opened Email"

Data Mapping.

JSON Element	Database Column
MD5(esp_source||':'||esp_integration||':'||id)	key
id	id
MD5(event_properties::$message)	campaign_key
MD5(event_properties::$flow)	automation_key
MD5(person::id)	member_key
NULL	list_key
person::$email	email
timestamp	event_at
Daasity: 'Klaviyo'	esp_source
Daasity: ID generated for each Klaviyo integration	esp_integration_id
Daasity: timestamp when loaded into DB	__synced_at

Business Rules.

Business Rule	Value
Load Type	UPSERT
Extraction Frequency	Daily

​Clicks TABLE

API Endpoint: /api/v1/metric/#{id}/timeline where event = "Clicked Email, Clicked SMS"

Data Mapping.

JSON Element	Database Column
MD5(esp_source||':'||esp_integration||':'||id)	key
id	id
MD5(event_properties::$message)	campaign_key
MD5(event_properties::$flow)	automation_key
MD5(person::id)	member_key
NULL	list_key
person::$email	email
timestamp	event_at
Daasity: 'Klaviyo'	esp_source
Daasity: ID generated for each Klaviyo integration	esp_integration_id
Daasity: timestamp when loaded into DB	__synced_at

Business Rules.

Business Rule	Value
Load Type	UPSERT
Extraction Frequency	Daily

Spams TABLE

API Endpoint: /api/v1/metric/#{id}/timeline where event = "Marked Email as Spam"

Data Mapping.

JSON Element	Database Column
MD5(esp_source||':'||esp_integration||':'||id)	key
id	id
MD5(event_properties::$message)	campaign_key
MD5(event_properties::$flow)	automation_key
MD5(person::id)	member_key
NULL	list_key
person::$email	email
timestamp	event_at
Daasity: 'Klaviyo'	esp_source
Daasity: ID generated for each Klaviyo integration	esp_integration_id
Daasity: timestamp when loaded into DB	__synced_at

Business Rules.

Business Rule	Value
Load Type	UPSERT
Extraction Frequency	Daily

Unsubscribes TABLE

API Endpoint: /api/v1/metric/#{id}/timeline where event = "Unsubscribed, Unsubscribed from List"

Data Mapping.

JSON Element	Database Column
MD5(esp_source||':'||esp_integration||':'||id)	key
id	id
MD5(event_properties::$message)	campaign_key
MD5(event_properties::$flow)	automation_key
MD5(person::id)	member_key
NULL	list_key
person::$email	email
timestamp	event_at
Daasity: 'Klaviyo'	esp_source
Daasity: ID generated for each Klaviyo integration	esp_integration_id
Daasity: timestamp when loaded into DB	__synced_at

Business Rules.

Business Rule	Value
Load Type	UPSERT
Extraction Frequency	Daily

Events TABLE

API Endpoint: /api/v1/metric/#{id}/timeline for all other events

Data Mapping.

JSON Element	Database Column
MD5(esp_source||':'||esp_integration||':'||id)	key
id	id
MD5(event_properties::$message)	campaign_key
MD5(event_properties::$flow)	automation_key
MD5(person::id)	member_key
NULL	list_key
person::$email	email
timestamp	event_at
Daasity: 'Klaviyo'	esp_source
Daasity: ID generated for each Klaviyo integration	esp_integration_id
Daasity: timestamp when loaded into DB	__synced_at

Business Rules.

Business Rule	Value
Load Type	UPSERT
Extraction Frequency	Daily

Member Exclusions TABLE

API Endpoint: /api/v1/people/exclusions

Data Mapping.

JSON Element	Database Column
email	email
reason	reason
timestamp	created_at
Daasity: unique id for the account	_account_id
MD5(email)	_sync_id
Daasity: timestamp when loaded into DB	__synced_at

Business Rules.

Business Rule	Value
Load Type	UPSERT
Extraction Frequency	Daily

Member Exclusions TABLE

API Endpoint: /api/v1/people/exclusions

Data Mapping.

JSON Element	Database Column
email	email
reason	reason
timestamp	created_at
Daasity: unique id for the account	_account_id
MD5(email)	_sync_id
Daasity: timestamp when loaded into DB	__synced_at

Business Rules.

Business Rule	Value
Load Type	UPSERT
Extraction Frequency	Daily

Stg Member Lists TABLE

API Endpoint 1: /api/v1/metric/#{id}/timeline

API Endpoint 2: /apiv2/list/{LIST_ID}/members

Process:

1. Use endpoint #1 to pass {id} is for subscribe event only to determine email

2. Use endpoint #2 to pass the email into each list and determine if the member is part of that list

Data Mapping.

JSON Element	Database Column
list_id	list_id
id	member_id
email	email
created	created_at
Daasity: unique id for the account	_account_id
MD5(list_id||':'||member_id)	_sync_id
Daasity: timestamp when loaded into DB	__synced_at

Business Rules.

Business Rule	Value
Load Type	UPSERT
Extraction Frequency	Daily

​Member List TABLE

Updated from stg_member_lists table

Data Mapping.

JSON Element	Database Column
MD5(esp_source||':'||esp_integration||':'||id)	key
list_id	id
id	list_id
email	member_id
Daasity: subscribe event	subscribed
Daasity: unsubscribe event	unsubscribed_at
Daasity: 'Klaviyo'	esp_source
Daasity: ID generated for each Klaviyo integration	esp_integration_id
Daasity: timestamp when loaded into DB	__synced_at

Business Rules.

Business Rule	Value
Load Type	UPSERT
Extraction Frequency	Daily