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

EXTRACTION EXAMPLE

Postman can be used to query the Klaviyo APIs and check the data returned in each endpoint

Was this article helpful?
0 out of 0 found this helpful