Setting up Klaviyo to Redshift Integration: 2 Easy Methods

Klaviyo is the best out there for seamlessly integrating with Shopify-powered online stores. It has a number of email automation and customer database management features that help you keep and engage lost customers. The platform is simple to use and ideal for agencies.

AWS Redshift is an Amazon Web Services data warehouse service. It’s commonly used for large-scale data storage and analysis, as well as large database migrations.

This article explains two different methods to set up Klaviyo to Redshift integration in a few easy steps. In addition to that, it also describes Klaviyo and Redshift briefly.

Setting up Klaviyo to Redshift Integration

• Method 1: Using Hevo Data to Integrate Klaviyo to Redshift
• Method 2: Using Custom Code to Move Data from Klaviyo to Redshift

Method 1: Using Hevo Data to Integrate Klaviyo to Redshift

Hevo provides an Automated No-code Data Pipeline that helps you move your Klaviyo to Redshift. Hevo is fully-managed and completely automates the process of not only loading data from your 100+ data sources(including 40+ free sources)but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

Using Hevo Data, you can connect Klaviyo to Redshift in the following 2 steps:

• Step 1: Configure Klaviyo as the Source in your Pipeline by following the steps below:
  • Step 1.1: In the Asset Palette, select PIPELINES.
  • Step 1.2: In the Pipelines List View, click + CREATE.
  • Step 1.3: Select Klaviyo on the Select Source Type page.
  • Step 1.4: Set the following in the Configure your Klaviyo Source page:
    • Pipeline Name: Give your Pipeline a unique name.
    • Private API Key: Your Klaviyo account’s private API key.
    • Historical Sync Duration: The time it takes to ingest historical data.

• Step 1.5: TEST & CONTINUE is the button to click.
• Step 1.6: Set up the Destination and configure the data ingestion.
• Step 2: To set up Amazon Redshift as a destination in Hevo, follow these steps:
  • Step 2.1: In the Asset Palette, select DESTINATIONS.
  • Step 2.2: In the Destinations List View, click + CREATE.
  • Step 2.3: Select Amazon Redshift from the Add Destination page.
  • Step 2.4: Set the following parameters on the Configure your Amazon Redshift Destination page:
    • Destination Name: A unique name for your Destination.
    • Database Cluster Identifier: Amazon Redshift host’s IP address or DNS.
    • Database Port: The port on which your Amazon Redshift server listens for connections. Default value: 5439
    • Database User: A user with a non-administrative role in the Redshift database.
    • Database Password: The password of the user.
    • Database Name: The name of the Destination database where data will be loaded.
    • Database Schema: The name of the Destination database schema. Default value: public.

• Step 2.5: Click Test Connection to test connectivity with the Amazon Redshift warehouse.
• Step 2.6: Once the test is successful, click SAVE DESTINATION.

Here are more reasons to try Hevo:

• Smooth Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to your schema in the desired Data Warehouse.
• Exceptional Data Transformations: Best-in-class & Native Support for Complex Data Transformation at fingertips. Code & No-code Flexibility is designed for everyone.
• Quick Setup: Hevo with its automated features, can be set up in minimal time. Moreover, with its simple and interactive UI, it is extremely easy for new customers to work on and perform operations.
• Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
• Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

Try Hevo Today!

SIGN UP HERE FOR A 14-DAY FREE TRIAL

Method 2: Using Custom Code to Move Data from Klaviyo to Redshift

This method explains how to get data from Klaviyo and migrate Klaviyo to Redshift.

Getting Data from Klaviyo

• Developers can access data on metrics, profiles, lists, campaigns, and templates using Klaviyo’s REST APIs. You can refine the information returned by using two to seven optional parameters in each of these APIs. A simple call to the Klaviyo Metrics API to retrieve data, for example, would look like this:

GET https://a.klaviyo.com/api/v1/metrics

• As a response, the GET request returns a JSON object containing all of the fields from the specified dataset. For any given record, all fields might not be available. The JSON may appear as follows:

{
  "end": 1,
  "object": "$list",
  "page_size": 50,
  "start": 0,
  "total": 2,
  "data": [
    {
      "updated": "2017-11-03 17:28:09",
      "name": "Active on Site",
      "created": "2017-11-03 17:28:09",
      "object": "metric",
      "id": "3vtCwa",
      "integration": {
        "category": "API",
        "object": "integration",
        "id": "4qYGmQ",
        "name": "API"
      }
    },
    {
      "updated": "2017-11-03 20:54:40",
      "name": "Added integration",
      "created": "2017-11-03 20:54:40",
      "object": "metric",
      "id": "8qYK7L",
      "integration": {
        "category": "API",
        "object": "integration",
        "id": "4qYGmQ",
        "name": "API"
      }
    ]
  }

Loading Data into Redshift

You can use Redshift’s CREATE TABLE statement to define a table that will receive all of the data once you’ve identified the columns you want to insert.

Create Table Command 

The creation of tables in Redshift is similar to how you create tables in other databases. Table constraints, column constraints, and attributes, such as column attributes and table attributes, are all defined in the create table syntax.

Syntax

CREATE [ [LOCAL ] { TEMPORARY | TEMP } ] TABLE 
[ IF NOT EXISTS ] table_name
( { column_name data_type [column_attributes] [ column_constraints ] 
  | table_constraints
  | LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] } 
  [, ... ]  )
[ BACKUP { YES | NO } ]
[table_attribute]

where column_attributes are:
  [ DEFAULT default_expr ]
  [ IDENTITY ( seed, step ) ] 
  [ GENERATED BY DEFAULT AS IDENTITY ( seed, step ) ]             
  [ ENCODE encoding ] 
  [ DISTKEY ]
  [ SORTKEY ]
  [ COLLATE CASE_SENSITIVE | COLLATE CASE_INSENSITIVE  ]

and column_constraints are:
  [ { NOT NULL | NULL } ]
  [ { UNIQUE  |  PRIMARY KEY } ]
  [ REFERENCES reftable [ ( refcolumn ) ] ] 

and table_constraints  are:
  [ UNIQUE ( column_name [, ... ] ) ]
  [ PRIMARY KEY ( column_name [, ... ] )  ]
  [ FOREIGN KEY (column_name [, ... ] ) REFERENCES reftable [ ( refcolumn ) ] 


and table_attributes are:
  [ DISTSTYLE { AUTO | EVEN | KEY | ALL } ] 
  [ DISTKEY ( column_name ) ]
  [ [COMPOUND | INTERLEAVED ] SORTKEY ( column_name [,...]) |  [ SORTKEY AUTO ] ]
  [ ENCODE AUTO ]

After you’ve created a table, you might want to migrate your data to Redshift by using INSERT statements to add data row by row. For inserting data one row at a time, Redshift isn’t designed for it. If you have a large amount of data to insert, save it to Amazon S3 and then load it into Redshift with the COPY command.

Insert Command

A query can be used instead of the ‘values’ in the Redshift INSERT statement. If the query’s results are compatible with the table column structure, Redshift will execute the query and insert all of the query’s resultant rows.

Syntax

INSERT INTO table_name [ ( column [, ...] ) ]
{DEFAULT VALUES |
VALUES ( { expression | DEFAULT } [, ...] )
[, ( { expression | DEFAULT } [, ...] )
[, ...] ] |
query }

Copy Command

A COPY operation can be performed with as few as three parameters: a Table Name, a Data Source, and Data Access Authorization.

Amazon Redshift extends the COPY command’s functionality to allow you to load data in a variety of formats from multiple data sources, control data access, manage data transformations and manage the load operation.

Syntax

COPY table-name 
[ column-list ]
FROM data_source
authorization
[ [ FORMAT ] [ AS ] data_format ] 
[ parameter [ argument ] [, ... ] ]

Keeping Klaviyo Data up to Date

• It’s not a good idea to duplicate all of your data every time your records are updated. This would be a painfully slow and resource-intensive process.
• Instead, identify key fields that your script can use to bookmark its progress through the data and return to as it searches for updated data. It’s best to use auto-incrementing fields like updated at or created at for this. 
• You can set up your script as a Cron Job or a continuous loop to get new data as it appears in Klaviyo once you’ve added this functionality.
• And, as with any code, you must maintain it once you’ve written it. You may need to change the script if Klaviyo changes its API, or if the API sends a field with a datatype your code doesn’t recognize. You will undoubtedly have to if your users require slightly different information.

Conclusion

This article talks about the distinct ways for Setting up Klaviyo to Redshift integration. It also gives an overview of Klaviyo and Redshift.

Visit our Website to Explore Hevo

Hevo Data offers a No-code Data Pipeline that can automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc.

This platform allows you to transfer data from 100+ sources (including 40+ Free Sources) such as Klaviyo and Cloud-based Data Warehouses like Snowflake, Google BigQuery, Amazon Redshift, etc. It will provide you with a hassle-free experience and make your work life much easier.

Want to take Hevo for a spin?

Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.