Klaviyo Integration Extractor Info

Integration Overview

Klaviyo is an email marketing platform created for online businesses - featuring powerful email and SMS marketing automations. The Daasity integration with Klaviyo extracts data from almost all endpoints: metrics, profiles, lists, lists V2 and campaigns that allows merchant to have a complete 360 view of their email and SMS performance

This integration is available for:

  • Growth
  • Pro V1
  • Pro V2

ERD Embed

Endpoints

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

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