Klaviyo has over 300 pre-built integrations but they mostly allow businesses to import data from marketing platforms to Klaviyo. When it comes to exporting data from Klaviyo, the integration options are quite limited, but there are a couple of solutions to fix this issue. In this article, we’ll discuss the manual way of exporting marketing data from Klaviyo, as well as the automated methods that can help you connect Klaviyo to BigQuery.
Methods to connect Klaviyo to BigQuery
Since BigQuery offers more capabilities for uncovering insights from business data, it’s a good idea to integrate Klaviyo with it. Below are three methods to make this happen:
1. Coupler.io: It’s a reporting automation platform to connect Klaviyo to spreadsheets, dashboards, and data warehouses. It requires no coding knowledge so users can easily set up an automated data flow between two apps. This solution lets you transform your data immediately, and schedule subsequent data transfers to your destination.
2. Custom ETL scripting: You can extract data from any app using its API. Google Apps Script allows users to create scripts that can query an API and get specific data from it. Then, you can download or send it in the JSON format directly to the destination. If you have some coding knowledge, it helps to automate such tasks by yourself without the need for a third-party platform.
3. Manual data import: It’s possible to export different types of Klaviyo data as CSV. If you have administrative permissions, you can locate the data (list & segments, benchmarks, profiles, etc), download the CSV file, and then upload it to BigQuery manually. If you don’t need to update Klaviyo data in BigQuery often, you may want to use the native export functions to move your data when you need to. For regular updates, it’s better to consider automation.
Prepare your Klaviyo API key
If you want to connect Klaviyo to BigQuery automatically, you will need to prepare your Klaviyo API key first. So whether you decide to build a connection with a user-friendly automation app or by writing scripts, both of these options require an API key.
To find the key, you have to go to Settings in Klaviyo. Under Account, click API keys, you’ll find any existing keys and the button that says Create Private API Key.
Now, let’s go ahead to explore the methods to help connect Klaviyo to BigQuery.
How to connect Klaviyo to BigQuery automatically with no coding
To pull data from Klaviyo to BigQuery with Coupler.io, you need to take three simple steps.
1. Extract your data from Klaviyo
Click Proceed in the form below with preselected Klaviyo as a data source and BigQuery as the destination. You’ll be offered to create a Coupler.io account for free.
Next, you’ll use your Private API key to connect your Klaviyo account. We already explained how to get the key in the previous section. Then, you have to select the data entity you want to export. I.e. campaigns list, profiles list, report, etc.
2. Preview and Transform your data
The second part of the Coupler.io importer setup process allows you to check the data being exported from Klaviyo. It also lets you manipulate and transform your data in different ways. First, you can rename, reorder, and hide columns. This module also lets you sort and filter data, as well as use formulas to calculate custom metrics for new columns. If needed, you can also blend data from two or more sources into one dataset before sending it to BigQuery.
After transforming your data, proceed to the next step. There, you’ll be asked to connect your BigQuery account by uploading the .json key file associated with it. Next, you’ll need to enter a new or existing BigQuery dataset name and table name, specifying where to place your data.
3. Schedule updates and manage your data
The last step allows you to turn your data into self-updating reports, live dashboards, or self-refreshing backups.
As soon as you’re done setting up your destination, toggle on Automatic Data Refresh. Once you have turned it on, you can select the interval for updates, preferred days and times, and other preferences.
The interval can be from once a month to every hour, 30 minutes, and even every 15 minutes.
When you’re done with the settings, click Run importer.Here’s our Klaviyo data already imported into BigQuery.
Now, that’s done! With Coupler.io, you can create such an integration in just a few minutes and collect data from 50+ apps. It allows you to create auto-updating reports, live dashboards, and set up data backups with the help of its auto-refresh functionality.
How to use Apps Script to connect Klaviyo to BigQuery
Google Apps Script is a good platform for creating a script that can query the Klaviyo API and pull data from it. But you’ll need to have some experience with JavaScript and working with APIs to use this method.
Let’s try to get all profiles from Klaviyo into BigQuery. To begin, open the Apps Script editor. You’ll need a Klaviyo Private API key and API endpoint to pull data from. To add data to your BigQuery destination, you’ll need a project id, dataset id, and table id.
Here’s a sample script to pull profiles data from Klaviyo into BigQuery:
// Set your Klaviyo API key and BigQuery project and dataset information
var klaviyoApiKey = 'YOUR_KLAVIYO_API_KEY';
var bigQueryProjectId = 'YOUR_BIGQUERY_PROJECT_ID';
var bigQueryDatasetId = 'YOUR_BIGQUERY_DATASET_ID';
var bigQueryTableId = 'YOUR_BIGQUERY_TABLE_ID';
function pullProfilesFromKlaviyoToBigQuery() {
var klaviyoProfiles = fetchKlaviyoProfiles();
if (klaviyoProfiles && klaviyoProfiles.length > 0) {
insertProfilesIntoBigQuery(klaviyoProfiles);
} else {
Logger.log('No profiles to insert into BigQuery.');
}
}
function fetchKlaviyoProfiles() {
var apiUrl = 'https://a.klaviyo.com/api/profiles/?page[size]=20';
var headers = {
'Authorization': 'Klaviyo-API-Key ' + klaviyoApiKey,
'Accept': 'application/json',
'Revision': '2023-10-15'
};
var options = {
'method': 'get',
'headers': headers,
'muteHttpExceptions': true
};
var response = UrlFetchApp.fetch(apiUrl, options);
if (response.getResponseCode() !== 200) {
Logger.log('Error fetching profiles: ' + response.getContentText());
return [];
}
var jsonResponse = JSON.parse(response.getContentText());
return jsonResponse.data || []; // Adjust based on the actual response structure
}
function insertProfilesIntoBigQuery(profiles) {
if (profiles.length === 0) {
Logger.log('No profiles to insert into BigQuery.');
return;
}
var bigquery = BigQuery;
var insertAllRequest = BigQuery.newTableDataInsertAllRequest();
insertAllRequest.rows = profiles.map(function(profile, index) {
return {
json: {
id: profile.id, // Assuming 'id' is present in the Klaviyo data
attributes_email: profile.email, // Adjust according to actual Klaviyo data field
attributes_phone_number: parseInt(profile.phone_number, 10) || null, // Ensure phone number is an integer or null
attributes_external_id: profile.external_id,
attributes_first_name: profile.first_name,
attributes_last_name: profile.last_name,
attributes_organization: profile.organization,
attributes_title: profile.title,
attributes_image: profile.image,
attributes_created: profile.created ? new Date(profile.created).toISOString() : null, // Convert to TIMESTAMP format
},
insertId: index.toString() // Optional: Unique ID for de-duplication
};
});
try {
var insertResponse = BigQuery.Tabledata.insertAll(insertAllRequest, bigQueryProjectId, bigQueryDatasetId, bigQueryTableId);
if (insertResponse.insertErrors && insertResponse.insertErrors.length > 0) {
Logger.log('Insert errors: ' + JSON.stringify(insertResponse.insertErrors));
} else {
Logger.log('Successfully inserted profiles into BigQuery.');
}
} catch (e) {
Logger.log('Error inserting data into BigQuery: ' + e.toString());
}
}
Next, you’ll need to save and run the script. If there are no errors, you’ll be asked to review permissions. Make sure to allow access to your BigQuery account. When Apps Script says the execution is successful, go to BigQuery to check if your data has been sent.
Here’s the Klaviyo profiles data imported into our BigQuery table:
Please refer to the Klaviyo API documentation for the right endpoints and authorization headers.
How to get Klaviyo data into BigQuery manually
The manual process of integration doesn’t actually connect Klaviyo to BigQuery, but it allows you to move data into the BigQuery warehouse. First, we’ll export data from Klaviyo as CSV, and then, we’ll go to BigQuery to upload the file.
To get started, log into your Klaviyo account and check the left menu bar for the data category you’d like to export. It can be your campaigns, lists & segments, or reports. In this example, we’ll export a list from Klaviyo and move the CSV to a BIgQuery table.
- Navigate to List & Segments. You’ll find the names of your lists.
Click the preferred one to continue.
- Click the Manage List dropdown by the top-right side. Select Export list to CSV.
The next page contains all the properties in your account. You need to review and select the ones you’d like to include in your CSV export. When you’re ready, click the Start Export button at the bottom of the page. The CSV file will download automatically.
Now, we’ve gotten our data in the CSV file format. It’s time to upload it to BigQuery. Here’s how:
- Go to BigQuery and click the plus symbol on the left side of the page.
Click Local file since we’re trying to upload a CSV from our computer.
- Upload the file and select the BigQuery project, dataset and table.
When you’re done, click Create table to add your data to the BigQuery table.
Here’s the data contained in the Klaviyo CSV file already added to BigQuery:
The manual method of moving data from Klaviyo to BigQuery can be very exhausting. It’s only recommended when working with small datasets. But if you have constantly updating data or large datasets, it’s best to use other methods such as building an automated connection.
The best method for Klaviyo to BigQuery integration
As mentioned above, there are three main methods to connect Klaviyo to BigQuery. You can use the manual procedure of exporting data as CSV and uploading it into BigQuery. It’s also possible to use Google Apps Script to write a script that pulls data from the API endpoint and inserts it into a specified table in a BigQuery dataset. Finally, you can use no-code automation apps like Coupler.io to automate marketing data flows from Klaviyo to BigQuery and other destinations (Google Sheets, Looker Studio, Excel, and Power BI). Although other methods have their own use cases, the use of automation apps helps to make everything more efficient especially when it comes to getting data for reports.
