How to Connect BigQuery to Klaviyo? | 2 Easy Methods

on Data Replication, Google BigQuery, Hevo Activate, KLAVIYO, Reverse ETL • June 22nd, 2022 • Write for Hevo

BigQuery to Klaviyo - Featured Image

With the current trend of businesses and e-commerce platforms offering personalized messages to customers, customer bonding and brand awareness have increased by many folds. Tools like Klaviyo help in understanding the customer much better and finding minute details to help segment customers and offer the best results. 

Google BigQuery is a popular cloud-based Data Warehouse solution that efficiently stores vast amounts of complex user data. When data is replicated from BigQuery to Klaviyo, this data allows your teams to generate actionable insights on historical data so that accurate analysis and data comparisons can be performed.

This article provides a comprehensive overview of BigQuery, Klaviyo, and two different methods to replicate data from BigQuery to Klaviyo.

Table of Contents

What is Google BigQuery?

Google BigQuery Logo: BigQuery to Klaviyo | Hevo Data
Image Source: Cxl

Google BigQuery is a popular cloud-based Data Warehouse that is fast and powerful. This solution can tightly integrate with all Google services and provide maximum throughput. It employs serverless architecture, combined with an integrated Query Engine that provides a blazing-fast query performance. To put that into perspective, Google BigQuery is well capable of processing terabytes of data in seconds.

You can learn more about BigQuery Architecture by reading another of our blogs – Google BigQuery Architecture: The Comprehensive Guide.

Google BigQuery uses a usage-based pricing model. It lowers the total cost of ownership (TOC) by about 26% to 34% compared to other leading cloud-based alternatives. It offers flat-rate on-demand pricing options as well. With serverless architecture, it eagles you to focus more on querying performance using SQL instead of managing infrastructure.

Key Features of Google BigQuery

Google BigQuery Architecture Logo: BigQuery to Klaviyo | Hevo Data
Image Source: Medium

Here are a few key features of Google BigQuery:

  • Serverless Model: Organizations employing a Data Warehouse usually must specify the hardware and software requirements beforehand. Also, administrators are needed to maintain the servers’ performance, reliability, and security. Google BigQuery’s serverless model allows you to focus more on insights generation rather than spending resources on server management and infrastructural requirements.
  • SQL and Programming Language Support: You can perform operations on the data present in Google BigQuery using SQL. It also supports connections to other programming languages like Python, C#, Java, PHP, Node.Js, GO language, and many more.
  • Tree Architecture: Google BigQuery can also be extended to other systems. The root server receives the incoming requests and forwards them to a branch called the mixer. These requests are forwarded to leaf nodes for further computation.
  • Support For Multiple Data Types: Google BigQuery supports various data types, including strings, numbers, Boolean values, structs, and arrays.
  • Unmatched Security: Google BigQuery supports automated data encryption on stored and transferred data. It employs all the security regulations of Google Cloud. Google BigQuery can also isolate jobs and manage the security of multi-tenant activities. Administrators can set permissions for individuals and groups to access tables, views, and records.

Explore Methods to Connect BigQuery to Klaviyo

Klaviyo is a popular Email Marketing Platform & Automation tool that helps in understanding customer information efficiently on real-time data. Insights like customer conversion, brand awareness, and many more can be easily monitored.

Google BigQuery is efficient in storing complex business data, and replicating data from BigQuery to Klaviyo helps in analyzing historical data and comparing it so that required changes can be made. Replicating data from BigQuery to Klaviyo can be done in two ways:

Method 1: Export & Import Table Data From BigQuery to Klaviyo Using CSV

This method involves manually converting your BigQuery data into CSV Files using SQL commands. The data in the CSV files must be modified according to the format supported by Klaviyo. And then, the CSV can be imported into Klaviyo to perform Data Analytics. It is a lengthy process that will also require troubleshooting certain errors manually.

Method 2: Faster and Easier Data Sync From BigQuery to Klaviyo Using Hevo Activate

Hevo Activate provides a hassle-free, one-stop solution to connect BigQuery to Klaviyo (Coming Soon For Hevo Activate!) effortlessly. Hevo Activate syncs customer /product data in the warehouse to your Business Application. With Hevo Activate you can bring the data to the fingertips of your business teams, where they need it the most – Business Applications. Teams can now make faster, smarter actions by analyzing the user journeys, and creating personalized experiences with Hevo Activate.

GET STARTED WITH HEVO ACTIVATE FOR FREE

What is Klaviyo?

Klaviyo Logo: BigQuery to Klaviyo | Hevo Data
Image Source: Paubox

Klaviyo is a robust and feature-rich Email Marketing Platform. It enables e-commerce store owners to give their customers a more personalized experience. Klaviyo has helped businesses earn $3.7 billion in revenue worldwide, and it attracts about 67 new brands every day.

Klaviyo’s email marketing feature, when used in conjunction with other platforms like Shopify, allows businesses to create a holistic ecosystem for their products. Klaviyo also lets you send bulk SMS campaigns and personalize each message using powerful automation. It also helps you craft Multi-Channel Customer Experiences and manages them all from a single platform.

Key Features of Klaviyo

  • Ample Integrations: Klaviyo has 200+ pre-built integrations that enable you to use historical and real-time data from different sources. Many integrations are just one-click solutions making it easier to gain important information.
  • Real-time Marketer Friendly Segmentation: Using Klaviyo, different aspects like purchased products, website browsing, order details, and many more can be simply added to your customer communication channels, and messages can be sent quickly as well. 
  • Real-time Customer Profiles: Your business can have a holistic overview of all your customer activities, perform data analysis, and monitor customer platforms.
  • Flexible Data Storage and Retention: Klaviyo offers a one-stop solution by providing data storage, retention, columns, fields, and other aspects in its NoSQL data platform.

What are the Benefits of BigQuery to Klaviyo Integration?

Copying data from BigQuery to Klaviyo has the following benefits:

  • Your business teams can create and send event triggers by personalizing them based on your target audience.
  • Your teams can communicate more precisely and effectively through various forms of communication like emails, SMS, mobile calls, and many more with updated information.
  • The historical and real-time data can be used to create customer segments with separate personalization and strategies of approach.
  • It allows you to prioritize goals for your marketing teams and maximize your investments.

How to Connect Connect BigQuery to Klaviyo: 2 Methods

Method 1: Export & Import Table Data From BigQuery to Klaviyo Using CSV

In this BigQuery to Klaviyo method, you will use your BigQuery Warehouse to get CSV files containing your target audience’s data and send them to the Klaviyo marketing platform. It is a simple two-step process that involves: 

Step 1: Exporting BigQuery Data in the form of CSV

You can export BigQuery table data as a CSV file format in three different ways. Here we have listed and described all three methods of doing so.

A) BigQuery to Klaviyo Export Using Cloud Console

This simple method uses BigQuery’s Web UI known as Google Console. This method can be used only when there is a one-time data export.

  • Go to the BigQuery page in the Google Cloud Console.
  • Open the Explorer panel, and expand your project and dataset panel.
  • Select the table to be exported.
  • From the right panel, click on the Export button, then click on Export to Google Cloud Storage.
  • In the pop-up menu, select the GCS bucket location you want to export data into.
  • In the Export Format, select CSV
  • In the Compression, select None.
  • In the GCS, navigate through the bucket and location where your CSV file needs to be saved. 
  • Click on the Download Icon.

B) BigQuery to Klaviyo Integration Using bq Extract Command

Another method to export data from BigQuery to Klaviyo is by using the bq extract command.  

  • Go to Google Search Console, and click on the Terminal icon on the right side.
  • Enter the below command to export the BigQuery table to a CSV file.
bq extract [--FLAGS] RESOURCE DESTINATION

In this command,

  • Resource represents the table that you need to export.
  • Destination represents the bucket and its location in GCS. 
  • Flags are optional elements that allow you to:
    • Choose your compression type.
    • Choose your file format. The default value is CSV.
    • Choose your delimiter for the CSV file.
    • Choose if you want to include a header, the default value is true.

If you have installed the Google Cloud SDK, you can use the following command in your local terminal to download the file from your GCS.

gsutil cp gs://your_bucket/your_folder/your_file.csv.gzip /your_local_folder

Using the Google Cloud SDK is helpful if you frequently need to export your BigQuery data and especially if you know your way around cron jobs.

C) Connect Google BigQuery to Klaviyo via Client Libraries (APIs)

This BigQuery to Klaviyo method can be used if you have experience in connecting and using the BigQuery API. This method requires maximum effort and a thorough understanding of programming languages like Node.Js, Java, and Python.

For this example of exporting CSV from BigQuery to Klaviyo, we will use Python.

# Import library
from google.cloud import bigquery

# Declare client
client = bigquery.Client()

# Parameters for your case 
project = 'bigquery-public-data'
dataset_id = 'austin_bikeshare'
table_id = 'bikeshare_stations'
table_location = 'US' # Open the table in the BigQuery web UI, go to the details tab and look for 'Data location' 

bucket_name = 'your_bucket'
file_location = 'your_folder/austin_bikeshare.csv'
destination_uri = f'gs://{bucket_name}/{file_location}'
dataset_ref = bigquery.DatasetReference(project, dataset_id)
table_ref = dataset_ref.table(table_id)

# Exporting the entire table
extract_job = client.extract_table(
    table_ref,
    destination_uri,
    location = table_location,
)

extract_job.result() 

The entire table has now been stored in GCS as a CSV file. You can download the file with the below code.

from google.cloud import storage

bucket_name = 'your_bucket'
source_file_name = 'austin_bikeshare.csv'
destination_file_name = 'austin.csv'

storage_client = storage.Client()
bucket = storage_client.bucket(bucket_name)
blob = bucket.blob(source_file_name)

blob.download_to_filename(destination_file_name)

Alternatively, you can use a standard SQL query to limit the exported data and immediately store the result in a CSV, bypassing GCS.

# Querying a subset of the table
sql_query = ("""
    SELECT station_id, name, status 
    FROM `bigquery-public-data.austin_bikeshare.bikeshare_stations`
        WHERE status = 'active'
    """)


# Storing the data in a pandas DataFrame
df = client.query(sql_query).to_dataframe()

# Saving the DataFrame to a CSV
df.to_csv('filename.csv')

After executing the above codes, you will find the CSV file in your system or the location mentioned. 

Step 2: Importing Table Data From BigQuery to Klaviyo

Create a CSV file

Before uploading the CSV, Klaviyo requires formatting of your profile properties. 

  • Open the CSV on any spreadsheet software.
  • Add the first column with the label Email.
  • Add the name of all the profile properties in the first cell as shown in the following image.
  • Add the values in the following rows. 
  • Save the CSV file.
A sample CSV file ready for upload:  BigQuery to Klaviyo | Hevo Data
Image Source: googleusercontent

Upload Profile Properties

After the CSV is formatted, perform the following steps to upload your file.

  • Go to the Lists & Segments tab.
  • Click on Create List / Segment and select Lists.
  • Click on Upload Contacts.
  • Select the CSV you created in the previous step.
  • An Import review page will open, select Klaviyo under Klaviyo Field.
  • Review the fields and click on Start Import.
  • The import will start and may take anywhere between a few seconds to hours, based on the size of your CSV file.

Review Your Import

  • After the import is complete, open the list and select any profile.
  • Open the information section.

All the properties that you have uploaded will be visible here. Verify if the uploaded information is correct and matches what you extracted from your BigQuery Data Warehouse.

Limitations While Exporting CSV Files From BigQuery

The manual method for copying data from BigQuery to Klaviyo has the following drawbacks.

  • Limited CSV file size. Google Cloud Storage only allows exporting only 1 GB of data in a single file.
  • Google BigQuery doesn’t allow nested or repeated data from Google Cloud Storage to be exported as CSV files.
  • Your BigQuery table data cannot be exported to your local file storage. Only Google Cloud Storage is a supported location.
  • BigQuery data from multiple tables cannot be exported as a single job.
  • Compression other than GZIP is not supported during export.
  • The order of data in exported tables is not guaranteed.

Method 2: Faster and Easier Data Sync From BigQuery to Klaviyo Using Hevo Activate

Hevo Activate: BigQuery to Klaviyo | Hevo Data

All the drawbacks mentioned above can be overcome when you use an Automated Reverse-ETL Solution like Hevo Activate.

With Hevo Activate, you can empower business teams with 360° customer profiling and sync customer and product usage data available in your BigQuery Data Warehouse to CRMs like HubSpot and Marketing Automation Solutions like Klaviyo (Coming Soon for Hevo Activate!) in a hassle-free & effortless manner.

Sign up here for a 14-Day Free Trial!

Hevo Activate enables seamless data transfer from BigQuery and other Data Warehouses like Snowflake, Amazon Redshift, or Firebolt to all your Business Applications. Business Teams can now make faster and smarter decisions with Hevo Activate by analyzing user journeys and creating personalized experiences for the customers.

Try our 14-day full access free trial today to experience an entirely automated hassle-free Data Replication!

Here’s how Hevo Activate is best-in-class:

  • Real-Time Data Replication: Hevo Activate, with its strong integration with various data sources, allows you to transfer data quickly & efficiently. This ensures efficient utilization of bandwidth on both ends.
  • No APIs, CSVs, Just SQL!: Use the powerful and smart SQL Query Editor to filter and transform data from your data warehouse table to a Target object. The in-built validation checks ensure each query is validated before you save it.
  • Secure: Hevo Activate has a fault-tolerant architecture that ensures that the data is handled safely and consistently with zero data loss.
  • On-Demand Sync: Hevo Activate gives users the option to resume the sync or run sync now to execute data sync as per their demand.
  • Intelligent Data Type Conversion: During the mapping action, Hevo Activate transforms the field types of the synced data automatically. 
  • Data Transformation: Hevo Activate provides a simple interface to perfect, modify, and enrich the data you want to transfer.
  • Live Support: The Hevo Activate team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

Conclusion

Klaviyo is a popular business marketing platform that enables you to provide personalized experiences to customers, which helps create a better brand image. It works efficiently on real-time data to generate insights. BigQuery is a leading solution, trusted by millions to store and process data efficiently. Replicating business data from BigQuery to Klaviyo allows you to generate insights on historical data and understand the customer’s shift in requirements, brand awareness, and other features. 

This article provided two easy methods to replicate data from BigQuery to Klaviyo. The first is a tedious manual method comprising many complex steps, and the second one is an easy solution using Hevo Activate that offers a fully managed Data Replication solution from BigQuery to Klaviyo.

Hevo Activate will automate your data transfer procedure, freeing up your time to focus on other elements of your business, such as analytics and customer management. This platform enables data transfer from Cloud-based Data Warehouses such as Google BigQuery, Snowflake, Amazon Redshift, and others to CRMs like Salesforce and Business Intelligence Software like Klaviyo (Coming Soon for Hevo Activate!). It will make your work life a lot smoother and give you a hassle-free experience.

VISIT OUR WEBSITE TO EXPLORE HEVO ACTIVATE

Want to take Hevo Activate for a ride? SIGN UP for a free 14-day trial to streamline your data integration process. You can check out Hevo Activate’s pricing and select the best plan for your business needs.

Share your learning experience about integrating BigQuery to Klaviyo in the comments below.

Sync Data from BigQuery to Business Applications Seamlessly