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

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

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

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

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

lists::id

list_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

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

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

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

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

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

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

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

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

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

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

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

member_id

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

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

PreviousIntegration Setup NextWorkflow Configuration Setup

Last updated 9 months ago

Integration Overview

Integration Availability

Entity Relationship Diagram (ERD)

Metrics TABLE

Campaigns TABLE

Automations TABLE

​Lists TABLE

Campaign Lists TABLE

Members TABLE

Member Attributes TABLE

Sends TABLE

Bounces TABLE

Opens TABLE

​Clicks TABLE

Spams TABLE

Unsubscribes TABLE

Events TABLE

Member Exclusions TABLE

Member Exclusions TABLE

Stg Member Lists TABLE

Lists TABLE

Clicks TABLE