Klaviyo to BigQuery Integration: 2 Easy Methods

Easily move your data from Klaviyo To BigQuery to enhance your analytics capabilities. With Hevo’s intuitive pipeline setup, data flows in real-time—check out our 1-minute demo below to see the seamless integration in action!

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.

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.

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.

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

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. 

Step 1: Configure Klaviyo as a Source

Step 2: Configure Google BigQuery as a Destination

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!

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 60+ 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? Explore Hevo’s 14-day free trial and experience the feature-rich Hevo suite firsthand.

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

FAQs