Klaviyo to BigQuery Integration: 2 Easy Methods

Today, superior marketing tools have significantly increased customer bonding and brand recognition. Klaviyo is one such marketing tool that helps brands provide customized emails and SMS to their customers. It is one of the best email marketing platforms to gather and engage customers. Klaviyo allows businesses to export their marketing campaign data to a centralized repository like Google BigQuery, which can be combined with powerful BI tools for real-time data analysis. You can connect Klaviyo to Google BigQuery using standard APIs or third-party ETL (Extract, Transform, and Load) tools.

This article will guide you on connecting Klaviyo to BigQuery using two methods.

Prerequisites

• The basic need for integration.

What is Klaviyo?

Developed in 2012, Klaviyo is a marketing automation platform that helps online businesses to grow by running personalized emails and Facebook campaigns. It is a top-rated email marketing platform that has helped brands generate over $3.7 billion in revenue in 2021.

Klaviyo is a unified platform that directly gives brands ownership of their customer data and interactions. It empowers brands to turn their customer interactions into long turn relationships. With Klaviyo, brands can integrate the customer data with more than 250 native platforms to automate personalized emails and SMS communications. This results in acquiring, retaining, and engaging more with the customers.

Key Features of Klaviyo

• More than 200 Integrations: Klaviyo consists of in-built integrations that can bring historical and real-time customer data in one place. It provides simple one-click integration for many popular e-commerce websites.

• Real-time, Marketer Friendly Segmentation: Klaviyo provides simple, quick, and granular segmentation for purchased products, order value, website browsing behavior, and more. All this segmentation can directly help you in customer communications.

What is Google BigQuery?

Developed in 2010, Google BigQuery is a popular and highly scalable data warehouse that requires zero administration. It consists of a BI engine that offers fast query performance to help you analyze petabytes of data. Google BigQuery is a SQL-based Data warehouse as a Service (DWaas) with zero administration. As it is a fully managed data warehouse, BigQuery does not need hardware provisioning or maintenance.

It is based on Dremel technology and stores data in columnar format, providing high performance and data compression capabilities. BigQuery can also help businesses to focus on reducing costs by scaling computation and storage independently.

Key Features of Google BigQuery

• BigQuery BI Engine: BigQuery is a BI engine that helps organizations process large volumes of data with sub-second query response time and high concurrency. This BI engine can work with BI tools such as Google Data Studio, Tableau, PowerBI, and more for in-depth data analysis. It can also work along with client libraries, BigQuery SQL, JDBC drivers, and more.

• Machine Learning: Google BigQuery helps enterprises in creating machine learning models with SQL queries. It supports several machine learning models such as Logistic Regression, Binary Logistic Regression, K-means clustering, Multi-Class Regression, and more.

• Real-time Analytics: BigQuery helps businesses in real-time data transfer and analysis. It allocates resources intelligently to deliver better performance and outcomes that can help businesses generate reports quickly.

• User-friendly: In BigQuery, storing and analyzing data is a simple process. BigQuery has a simple interface that provides simple instructions at every step to set up your data warehouse quickly. As BigQuery is a fully managed data warehouse, the tasks such as deploying clusters, setting storage size or compression, and encryption settings are done automatically.

Methods to Connect Klaviyo to BigQuery

Klaviyo allows businesses to export marketing campaign data so that businesses can review and measure performance. In this article, you will learn two methods to export Klaviyo to BigQuery.

• Method 1: Connect Klaviyo to BigQuery Using Hevo
• Method 2: Manually Connect Klaviyo to BigQuery

Method 1: Connect Klaviyo to BigQuery Using Hevo

Hevo provides Google Bigquery as a Destination for loading/transferring data from any Source system, which also includes Klaviyo. You can refer to Hevo’s documentation for Permissions, User Authentication, and Prerequisites for Google BigQuery as a destination here. 

Configure Klaviyo as a Source

Configure Klaviyo as the Source in your Pipeline for Klaviyo to BigQuery Integration by following the steps below:

• Step 1: In the Asset Palette, select PIPELINES.
• Step 2: In the Pipelines List View, click + CREATE.
• Step 3: Select Klaviyo on the Select Source Type page.
• Step 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 5: TEST & CONTINUE is the button to click.
• Step 6: Set up the Destination and configure the data ingestion in Klaviyo to BigQuery Connection.

Configure Google BigQuery as a Destination

To configure BigQuery as a Destination in Klaviyo to BigQuery Integration, follow these steps:

• Step 1: In the Asset Palette, choose DESTINATIONS.
• Step 2: In the Destinations List View, click + CREATE.
• Step 3: Select Google BigQuery as the Destination type on the Add Destination page in Klaviyo to BigQuery Connection.
• Step 4: Select the authentication method for connecting to BigQuery on the Configure your Google BigQuery Account page.

• Step 5: Perform one of the following:
  • To connect with a Service Account, follow these steps:
    • Attach the Service Account Key file.
    • Click on CONFIGURE GOOGLE BIGQUERY ACCOUNT.
  • To join using a User Account, follow these steps:
  • Click on + ADD A GOOGLE BIGQUERY ACCOUNT.
  • Sign in as a user with BigQuery Admin and Storage Admin permissions.
  • Provide Hevo access to your data by clicking Allow.

• Step 6: Configure your Google BigQuery Warehouse page with the following information:
  • Destination Name: Give your Destination a distinctive name.
  • Project ID: The BigQuery instance’s Project ID.
  • Dataset ID: The dataset’s name.
  • GCS Bucket: A cloud storage bucket where files must be staged before being transferred to BigQuery.
  • Sanitize Table/Column Names: Select this option to replace any non-alphanumeric characters and spaces in table and column names with an underscore (_).
  • Populate Loaded Timestamp: Enabling this option adds the __hevo_loaded_at_ column to the Destination Database, indicating the time when the Event was loaded to the Destination.
• Step 7: To test the connection, click TEST CONNECTION and then SAVE DESTINATION to finish the setup.

Method 2: Manually Connect Klaviyo to BigQuery

In this method, you will learn how to manually export data from Klaviyo to BigQuery using CSV files.

Exporting Klaviyo Data 

The first step in Klaviyo to BigQuery Integration is to export Klaviyo data.

• Export Campaign Analytics

You can export your campaign data into a CSV file or access reports from the last 30 days by clicking on Accounts > Downloads for Klaviyo to BigQuery Connection.

Navigate to the Campaigns tab to create a new csv file. Click on Options, and you can see the dropdown list. Select Export Analytics from the dropdown list.

• Set the Time Range of the export.
• Select the Tag you want to export.
• Select whether you want to include email campaigns or SMS campaigns.
• Choose whether you want to include all the email versions in your export.

Click on the Export Analytics button to start your export. 

The resulting csv file contains columns such as Campaign Name, Tags, Subjects, List, and more.  

• Exporting Flow Analytics in Klaviyo

To export analytics for a certain flow in Klaviyo to BigQuery Integration, go to the Flows tab of your Klaviyo account. You can see the Export Analytics button at the top.

After clicking the Create Flow button, a modal will appear with the export options below.

• Time-range: You have an option to export all the flows that have been sent over time, or you can also limit the time range of the export.
• Tag: If you are using tags for organizing your flows, you can specify to only export flows that have tags.
• Aggregate analytics by day, week, or month: When you check this box, a dropdown list appears that helps you view the analytics of each variation separately.

If you want to export the analytics for a single flow, create a new tag, then tag the flow and export the analytics for that tag in Klaviyo to BigQuery Connection.

The resulting csv file contains column names such as Flow ID, Flow Name, Flow Message ID, Flow Message Name, and more. 

Importing Data to BigQuery

You can append your csv file for overwriting an existing table or position in BigQuery. When the file is loaded into BigQuery, it can be converted into columnar format or Capacitor.

Before loading the csv file into BigQuery for Klaviyo to BigQuery Integration, add the required IAM permissions.

• Permissions to load data into BigQuery.

bigquery.tables.create
bigquery.tables.updateData
bigquery.tables.update
Bigquery.jobs.create

Each predefined IAM role consists of the below permissions.

roles/bigquery.dataEditor
roles/bigquery.dataOwner
roles/bigquery.admin (includes the bigquery.jobs.create permission)
bigquery.user (includes the bigquery.jobs.create permission)
bigquery.jobUser (includes the bigquery.jobs.create permission)

• Permissions to load data from the cloud data storage.

Storage.objects.get
Storage.objects.list

• You need the below prerequisites to load the csv file into a BigQuery table.

1. The Cloud Console.
2. The bq command-line tool’s bq load command.
3. Calling the jobs.insert API method.
4. Client libraries.

Follow the below steps for loading csv files into Google BigQuery in Klaviyo to BigQuery Migration.

• Step 1: Navigate to the BigQuery on the Cloud Console.

• Step 2: Open your project using the Explorer pane and select a database.
• Step 3: Click on the Create table option in the Dataset info section.

• Step 4: Go to the Create table panel and enter the below information.

Choose Google Cloud Storage in the Source section, create the table from the list, and then follow the steps below.

• You can either select the file from the Google Cloud Storage bucket or enter the Cloud Storage URI. 
• Select the csv file format.

Enter the below information in the Destination section.

• Select the database for creating a table.
• In the Table field, you need to enter the table’s name.
• Verify that the table is set to Native table.

• Step 5: In the Schema definition, enter the Schema definition and then select Auto-detect to enable auto-detection for Schema. You can enter the Schema definition using any of the following ways.

• Step 6: Click on Edit as Text and paste the Schema in JSON arrays. You can generate the Schema using the same process as creating a JSON schema file while using JSON arrays.

The Schema of the existing table can be viewed in the JSON format by using the below command.

bq show --format=prettyjson dataset.table

• Step 7: Click on the Add field to add the table Schema. You can also enter the field’s name, type, and mode accordingly.

To create a table in BigQuery, you need to click on Advanced Options and follow the next instructions.

Limitations of Connecting Klaviyo to BigQuery Manually

Businesses can connect Klaviyo to BigQuery using standard APIs and manual processes. With the manual processes, companies can easily export data from Klaviyo to BigQuery. Although this process might seem easy, it cannot process real-time data. While in the case of standard APIs, you need a strong technical team to connect Klaviyo to BigQuery. Therefore, to eliminate such issues, you can connect Klaviyo to BigQuery using third-party ETL tools like Hevo to automate data pipelines between Klaviyo to BigQuery seamlessly.

Conclusion

In this article, you learned to connect Klaviyo to BigQuery. Klaviyo helps businesses in creating customized emails and SMS for marketing. Integrating Klaviyo to BigQuery can assist businesses in gaining meaningful insights from marketing data.

However, as a Developer, extracting complex data from a diverse set of data sources like Databases, CRMs, Project management Tools, Streaming Services, and Marketing Platforms to your Database can seem to be quite challenging. If you are from non-technical background or are new in the game of data warehouse and analytics, Hevo Data can help!

Visit our Website to Explore Hevo

Hevo Data will automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc. Hevo provides a wide range of sources 150+ Data Sources (including 40+ Free Sources) – that connect with over 15+ Destinations. It will provide you with a seamless 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 firsthand.

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