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 | |
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 | |
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 | |
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 | |
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 | |
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 | |
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 | |
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 | |
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 |
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 |
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:
Use endpoint #1 to pass {id} is for subscribe event only to determine email
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 |
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 |
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 |
Last updated