How to Connect Klaviyo to Google Looker (Data) Studio & Sync Data Automatically

Nowadays, email marketing is not just about sending emails in bulk. Marketers need to monitor campaign performance and improvise constantly to make it effective. Klaviyo, a marketing automation platform, offers analytic dashboards that offer a quick overview of campaign performance.

The native reporting and analytics features of Klaviyo are impressive. But, they come with limited customization support. You may need to export the data into a more powerful data reporting and visualization tool to get deeper insights. Looker Studio is one such option. Connecting Klaviyo to Looker Studio can take campaign reporting to the next level. It can help you:

• Build an easily shareable dashboard
• Visualize campaign performance in real time
• Generate an insightful report for clients

In this article, we’ll cover the different ways to connect Klaviyo to Looker Studio, and automate reporting. So let’s get started with no further ado.

Options to connect Klaviyo to Looker Studio

Looker Studio does not support native integration with Klaviyo. However, the Klaviyo API can be used to get the required data into Looker Studio. API or Application Programming Interface is the common protocol that applications use to exchange information.

Klaviyo to Looker Studio connection is directly possible using the Coupler.io connector. Alternatively, you can also connect Klaviyo to Google Sheets, which can be used as a data source in Looker Studio.

Here are three possible methods to connect Klaviyo to Looker Studio:

• Klaviyo integration by Coupler.io – Easiest method to pull data from Klaviyo to Looker Studio using the connector. Supports data auto-update at regular intervals.
• Google Sheets integration by Coupler – Connect Klaviyo to Google Sheets, and use it as a data source in Looker Studio. Supports auto-refreshing data at regular intervals.
• Apps Script – Programmatic method to get data from Klaviyo to Google Sheets using Apps Script. This method demands coding proficiency.

In order to pull data from Klaviyo via API, you’ll need to provide the private API key. It acts as a token of authorization, just like a password. So first, let’s learn to get the Klaviyo API key.

How to get Klaviyo API key?

Follow these steps to get Klaviyo private API key from the dashboard:

• Go to Settings > API Keys, and click Create Private API Key.

• Now, enter any name for the API key, select the appropriate access level, and click Create.

• Now, the Klaviyo API key will appear on your screen. Download it as a .txt file on your computer.

You can also find it in the Settings > API Keys section of your Klaviyo account.

Once you’ve downloaded the private API key, you can use it to make various requests and get desired data. 

Connect Klaviyo to Looker Studio using Coupler.io 

Coupler.io offers a connector for Looker Studio to import data from 70+ different sources. You can use the connector to directly fetch data from Klaviyo using its API.

Note: In this tutorial, you’ll need to use the Klaviyo API endpoints to fetch the required data. Coupler.io is not responsible for any type of issues you may face because of wrong configuration.

UPD: Coupler.io already offers a direct Klaviyo integration with simplified settings. We recommend it as an optimal method to connect Klaviyo to Google Sheets, Excel, Looker Studio, and BigQuery. This method is more convenient than the one described below. Please check the details on the Klaviyointegration page.

Suppose you want to get metrics related to new subscribers in the last seven days from Klaviyo into Looker Studio, which gets updated every hour. Here’s how you can do this using the Coupler.io connector.

First of all, create a Coupler.io account. In your account, create a new importer and select JSON as data source and Looker Studio as destination.

Click Proceed and configure the importer as instructed below:

Source

• Enter the respective Klaviyo API endpoint in the JSON URL field. In our case, this is:

https://a.klaviyo.com/api/v1/metric/{Metric_Id}/timeline

You can get the {Metric_Id} of all the available metrics using the https://a.klaviyo.com/api/v1/metrics endpoint. Learn more about all available API endpoints and parameters through the official Klaviyo API documentation.

• Keep the HTTP Method as GET.
• Specify the URL query parameters required to get the desired data. For example:
  • data_type = c
  • timespan = LAST_7_DAYS
  • api_key = {Klaviyo API Key}

Destination

Follow the instructions provided in the destination section to connect Klaviyo to Looker Studio. The entire process is explained briefly below:

• Generate and copy the Coupler.io access token from your profile.

• Now, click the link provided in Coupler.io to create a data source in Looker Studio. You’ll be redirected to Looker Studio, where you need to provide the authorization. Click Authorize and proceed further.

• Now, you’ll be asked to enter the Coupler.io key. Enter the access token that you copied earlier and click Submit.

• Finally, click Create Report to pull the data from Klaviyo API.

The Klaviyo data will now be available in Looker Studio through the Coupler data source. You can use it to build visual dashboards and custom reports.

But first let’s complete the Coupler.io importer setup.

Schedule

• Enable the Automatic data refresh, select the preferred interval, days of week, and time.

Click Save and Run to complete the setup.

Here’s an example of a basic report built through the data from Klaviyo in Looker Studio via Coupler.io connector:

Woo-hoo! That’s so easy. 🎉

Now, let’s learn other alternate options to connect Klaviyo to Looker Studio.

Import Klaviyo data into Looker Studio using the Google Sheets integration 

Looker Studio offers native integration with Google Sheets, which can be used as a mediator between Klaviyo and Looker Studio. Using this method, you can also access the raw data manually in Google Sheets. This can serve as a data backup and can also be used for other calculations.

The integration by Coupler.io can help you connect Klaviyo to Google Sheets, which you can use in Looker Studio as a data source.

In this method, you need to create a JSON URL to Google Sheets importer using Coupler.io. Let’s see how to do this.

First of all, create a Coupler.io account, and log into your account. Now, create a new importer with JSON as data source and Google Sheets as destination.

Now, configure the importer using the following settings:

Source

• In the JSON URL field, enter the complete Klaviyo API URL (including parameters) of the data you want to import. For example:

https://a.klaviyo.com/api/v1/metric/{Metric_Id}/timeline

• Set HTTP method to GET.
• Enter the URL query parameters to pull desired data from Klaviyo. In this case, they will be:
  • data_type = c
  • timespan = LAST_7_DAYS
  • api_key = {Klaviyo API Key}
• Next, click Jump to Destination Settings to proceed further.

Optionally, you can also specify Columns and Path to pull the required data from the JSON.

Destination

• In the Account, sign in to your Google account.
• Select the Spreadsheet and Sheet, which you want to act as an intermediary to connect Klaviyo to Looker Studio.
• Click Jump to Schedule Settings to proceed to the final step.

In the destination settings, you can also define the target cell address / Range, which is not required here. There is also an option to choose the import mode from the following:

• Replace: Clears existing data and imports fresh data on each run.
• Append: Old data remains untouched. Fresh data is added to the sheet on each run.

Schedule

• Turn on the Automatic data refresh option, and select the Interval.
• Select the applicable Days of week, Time, and Time Zone.
• Finally click Save and Run to import the required data from Klaviyo to Google Sheets.

On a successful data import, the required Klaviyo data will be imported to Google Sheets.

Nice job Coupler! 👏🎉

Coupler.io will also auto-update the Google Sheet every day as per the schedule, so that you can always stay on top with the latest data. Now, you can easily use this Google Sheet to visualize Klaviyo data into Looker Studio.

Let’s see how to do it.

Connect Google Sheets to Looker Studio

Open the Looker Studio Report where you want to visualize the Klaviyo data and follow these steps:

• Add a new Google Sheets data source.

• Select the Google Sheet & Worksheet that you used to import Klaviyo data, tick the appropriate options, and click Add.

• Finally, click Add to Report to get the Klaviyo data into Looker Studio.

This will import Google Sheets data into Looker Studio, which you can visualize as you want!

Tada!! 🎉 You’ve successfully connected Klaviyo to Looker Studio via Google Sheets.

Set up Klaviyo Looker Studio data sync using Coupler.io

The Looker Studio auto-refreshes the data from Google Sheets every 15 minutes by default. You can change this by using the following steps:

• In the Looker Studio report, go to Resource > Manage added data sources.

• In the list of connected sources, click Edit against the Google Sheets that we used to connect Klaviyo to Looker Studio.

• Now, on the top-right corner of the section, the data refresh interval of the connection will appear. Click on it.

• Choose the preferred data refresh interval, and click Set Data Freshness.

Now, Looker Studio will update the data from Google Sheets every hour.

Tada!! 🎉 You’ve successfully automated Klaviyo to Looker Studio connection using Coupler.io. Now, just  sit back, relax, and let the importer pull the data regularly for you!

Connect Klaviyo to Looker Studio programmatically using Apps Script

Google Apps Script can be used to call the Klaviyo API and pull the required data into Google Sheets. Later on, the Google Sheets can be used as a data source in Looker Studio.

This method demands really high coding skills and knowledge of REST API. Therefore, it is the least preferred method to import Klaviyo data into Looker Studio.

Let’s say you want to get Klaviyo subscribers list data for the last seven days. Here’s how you can do this:

• Open the Google Sheets where you want to import the Klaviyo data, and go to Extensions > Apps Script.

• Now, in the script editor, paste the following code:
  

function fetchFromAPI() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var url = 'https://a.klaviyo.com/api/v1/metric/{Metric_ID}/timeline?data_type=c&timespan=LAST_7_DAYS&api_key={Your_Klaviyo_API_KEY}';
  var response = UrlFetchApp.fetch(url);
  var json = response.getContentText();
  var data = JSON.parse(json);
  sheet.clear(); // clear sheet before each run
  var rows = data.data.map(function(item) {
    var person = item.person;
    var firstName = person.$first_name;
    var lastName = person.$last_name;
    var email = person.$email;
    var consentTimestamp = person.$consent_timestamp;


    return [firstName, lastName, email, consentTimestamp];
  }).filter(function(row) {
    return row !== null; // Filter out null rows
  });


  sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, rows[0].length).setValues(rows);
}

• Save the script and click Run to execute it.

• On successful execution of the script, the required columns will be imported to the sheet.

Now, you can connect this Google Sheets as a data source to visualize Klaviyo data in Looker Studio.

Auto-import Klaviyo data into Looker Studio at regular intervals

Google Apps Script supports time-based triggers that we can use to import data from Klaviyo to Google Sheets at regular intervals. Just follow these steps to do that:

• In the Apps Script window, go to Triggers from the left menu, and Create a new trigger.

• Now, select Time-driven as the event source, select preferred interval, and click Save.

At this point, the Apps Script will run the function at regular intervals and keep updating the data in the Google Sheet. Connect the Google Sheets with Looker Studio, and configure the data refresh interval in Looker Studio as well to sync the data.

Which is the best method to analyze your Klaviyo data with Looker Studio?

Klaviyo to Looker Studio connection is possible using the API via Google Sheets as an intermediary. In this article, we’ve discussed three possible ways to connect Klaviyo to Looker Studio and automate the data flow.

The Google Apps Script method demands coding proficiency, and can be too complex for non-technical users. In conclusion, the Coupler.io connector is the best way to connect Klaviyo to Looker Studio without any technical hassle.

Furthermore, Coupler.io supports importing data from multiple sources, which means you can call multiple Klaviyo API endpoints and pull data through a single importer. Overall, it is the best way if you are looking to create data-rich reports in Looker Studio.

Back to Blog