Klaviyo Exporter

📘

Check out the API docs here for more information.

🚧

Warning

Please be advised that this exporter has the potential to significantly alter your data if not used correctly. Exercise caution and avoid misuse. If you are unsure of how to operate it, there is a risk of causing irreversible changes to your data.

Set up

To get set up with the Klaviyo Exporter connector you have to provide the below details on the Source page of the connector:

• API Key

Features

Reports detail

The Modify Profiles report uses the endpoint specified here and sends a request per row in the source table.

Given a table with a row:

id       |    type     |  attributes
123      |  "profiles" | {"fancy-key": "abc"}

It will send a request with the body:

{"data": {"id": 123
          "type": "profiles"
          "attributes": {"fancy-key": "abc"}}}

To the following url:
PATCH "https://a.klaviyo.com/api/profiles/{id}/"
where "{id}" is replaced with the value of the "id" column, in this example 123. So:
"https://a.klaviyo.com/api/profiles/123/"

If you’ve imported profiles using the Klaviyo extract connector you can reshape them into a table with the following SQL:

CREATE OR REPLACE TABLE WAFFLE_TEST.PROFILE_CHANGER AS
SELECT 
    ID,
    TYPE,
    OBJECT_CONSTRUCT_KEEP_NULL(
        'first_name', FIRST_NAME,
        'title', TITLE,
        'organization', ORGANIZATION_,
        'last_name', LAST_NAME,
        'anonymous_id', ANONYMOUS_ID,
        'location', PARSE_JSON(LOCATION),
        'phone_number', PHONE_NUMBER,
        'properties', PARSE_JSON(PROPERTIES),
        'image', IMAGE,
        'email', EMAIL,
        'external_id', EXTERNAL_ID
    ) AS attributes
FROM WAFFLE_TEST.KLAVI_PROFILES
WHERE ID = '01H3M0GDDCW263FJ6VWNN2HSDS';

Many Keys have to be removed from the profiles data including but not limited to: created , updated , links , relationships, last_event_date
and nested fields need to be parsed as JSON like Location, or properties.

After the ones you want to edit are in the table you can alter them, for example here we alter the name inside of the properties (inside of attributes) for a given id:

UPDATE WAFFLE_TEST.PROFILE_CHANGER
SET ATTRIBUTES = OBJECT_INSERT(
    ATTRIBUTES,
    'properties',
    OBJECT_INSERT(
        ATTRIBUTES:properties::OBJECT,
        'name',
        'Piggledy', true),
        true
)
WHERE ID = '01H3M0GDDCW263FJ6VWNN2HSDS';

Limitations

🚧

Meta field

This Exporter DOES NOT SUPPORT the meta tag present in the documentation

🚧

Connector will change data within Klaviyo

Use of the report on this connector will result in changes to the data within your Klaviyo system. We advise having backup of the data, in case you want to revert back.

Our suggestion is also to experiment and explore the behaviour of the connector and the API, by first spending sufficient time testing things with dummy data before making any attempts to go to Production changes.