Klaviyo is an email marketing platform - featuring email and SMS marketing automations. The Daasity integration with Klaviyo extracts data from: metrics, profiles, lists, lists V2 and campaigns to provide a complete view email and SMS performance.
Key Topics
List as bullet points the key topics outlined in this article
- Extraction Overview
- Entity Relationship Diagram
- Metrics Table
- Campaigns Table
- Automations Table
- Lists Table
- Campaigns Lists Table
- Members Table
- Member Attributes Table
- Sends Table
- Bounces Table
- Opens Table
- Clicks Table
- Spams Table
- Unsubscribes Table
- Events Table
- Member Exclusions Table
- Sgt Member Lists Table
- Member List Table
Integration Availability
This Integration is available for:
- Growth
- Pro
Extraction 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.
NOTE: Klaviyo is an extremely slow endpoint and loading history can take a considerable amount of time if the data volume is high.
Entity Relationship Diagram (ERD)
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 |
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 |
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 |