Klaviyo is a digital marketing platform focused on email marketing automation for e-commerce businesses. Klaviyo offers an SMS and email marketing automation platform for e-commerce. The free account covers 250 contacts and 500 emails sent.

You can write Treasure Data job results directly to your Klaviyo. You use this integration and TD query jobs to add profiles to a Klaviyo member list.


This feature is in BETA. Contact your Customer Success Representative for more information.

 


Prerequisites

  • Basic knowledge of Treasure Data, including the TD Toolbelt.

  • A Klaviyo account with a Private API access token.

Requirements and Limitations

Your query requires a field name email (case sensitive). If your query does not contain an email field name, then an error is returned and the job fails. 

If your query has a field name phone_number (case sensitive), then data from this field must follow Klaviyo's accepted phone number format. Otherwise, an error is returned and the job fails.

Obtain the Private API access token from Klaviyo Console

1. Login into Klaviyo Console.
https://www.klaviyo.com/login
2. Type the user name and password.
3. Navigate to Setting → API key then select create Private API Key if you don't have any key. 
4. Copy your Private API Key.

Obtain the List Name or List ID from Klaviyo Console

1. Login into Klaviyo Console.
https://www.klaviyo.com/login
2. Select Lists & Segments on the left sidebar.
3. Select List Name.

Use the TD Console to Create Your Connection

Create a New Connection

In Treasure Data, you must create and configure the data connection prior to running your query. As part of the data connection, you provide authentication to access the integration.

1. Open TD Console.
2. Navigate to Integrations Hub Catalog.
3. Search for and select Klaviyo.


4. Select Create Authentication.

5. Type the Private API key to authenticate.
6. Type a name for your connection.
7. Select Done.



Define your Query

Your query requires an email column(case sensitive), and the data type is a string. 

If your query has a phone_number column (case sensitive), then data from this field must be a string and follow Klaviyo's accepted phone number format.

1. Complete the instructions in Creating a Destination Integration.
2. Navigate to Data Workbench > Queries.
3. Select a query for which you would like to export data.
4. Run the query to validate the result set.
5. Select Export Results.
6. Select an existing integration authentication.
7. Define any additional Export Results details. In your export integration content review the integration parameters.
8. Select Done.
9. Run your query.
10. Validate that your data moved to the destination you specified.


Integration Parameters for Klaviyo


ParameterValuesDescription
Data operationAddAdd profiles to a Klaviyo List
Add toList IDuse to determine a Klaviyo List

List Nameuse to determine a Klaviyo List
List ID or List Name valueList ID or list name that profiles will be added toIf Add to is List Name and List Name value do not exist in Klaviyo then a new List will be created with these values

Example Query

Your query requires an email column

SELECT email, col_a, col_b, col_c
FROM your_table;


Optionally Schedule the Query Export Jobs

You can use Scheduled Jobs with Result Export to periodically write the output result to a target destination that you specify.

1. Navigate to Data Workbench > Queries.
2. Create a new query or select an existing query.
3. Next to Schedule, select None.
4. In the drop-down, select one of the following schedule options.
5. Optionally, if you selected Custom cron: you can enter the following values for Cron.


Cron Value

Description

0 0 1 * *

Run once a month at midnight on the morning of the first day of the month

0 0 * * *

Run once a day at midnight

0 * * * *

Run once an hour

""

Create a job that has no scheduled run time.

1
2
3
4
5
6
7
8

*    *    *    *    *
-    -    -    -    -
|    |    |    |    |
|    |    |    |    +----- day of week (0 - 6) (Sunday=0)
|    |    |    +---------- month (1 - 12)
|    |    +--------------- day of month (1 - 31)
|    +-------------------- hour (0 - 23)
+------------------------- min (0 - 59)

The following named entries can be used:

  • Day of Week: sun, mon, tue, wed, thu, fri, sat

  • Month: jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec

A single space is required between each field. The values for each field can be composed of:

Field Value

Example

Example Description

a single value, within the limits displayed above for each field.



a wildcard ‘*’ to indicate no restriction based on the field. 

‘0 0 1 * *’ configures the schedule to run at midnight (00:00) on the first day of each month.
a range ‘2-5’, indicating the range of accepted values for the field.‘0 0 1-10 * *’ configures the schedule to run at midnight (00:00) on the first 10 days of each month.
a list of comma-separated values ‘2,3,4,5’, indicating the list of accepted values for the field.

0 0 1,11,21 * *’


configures the schedule to run at midnight (00:00) every 1st, 11th, and 21st day of each month.
a periodicity indicator ‘*/5’ to express how often based on the field’s valid range of values a schedule is allowed to run.

‘30 */2 1 * *’


configures the schedule to run on the 1st of every month, every 2 hours starting at 00:30. ‘0 0 */5 * *’ configures the schedule to run at midnight (00:00) every 5 days starting on the 5th of each month.
a comma-separated list of any of the above except the ‘*’ wildcard is also supported ‘2,*/5,8-10’‘0 0 5,*/10,25 * *’configures the schedule to run at midnight (00:00) every 5th, 10th, 20th, and 25th day of each month.

Optionally Configure Export Results in Workflow

Within Treasure Workflow, you can specify the use of a data connector to export data.

Learn more at Using Workflows to Export Data with the TD Toolbelt.

Example Workflow for add profile by List ID

_export:
  td:
    database: klaviyo_db
 
+klaviyo_export_task:
  td>: export_data.sql
  database: ${td.database}
  result_connection: new_created_klaviyo_auth
  result_settings:
    type: klaviyo
    api_token: api_token
    operation: add
    list_identify_type: id
    list_value: id_value

Example Workflow for add profile by List Name

_export:
  td:
    database: klaviyo_db
 
+klaviyo_export_task:
  td>: export_data.sql
  database: ${td.database}
  result_connection: new_created_klaviyo_auth
  result_settings:
    type: klaviyo
    api_token: api_token
    operation: add
    list_identify_type: name
    list_value: name_value
  • No labels