Import Klaviyo Data to Google Sheets

In this article, we'll walk through how to pull API data from Klaviyo into Google Sheets, using the API Connector add-on for Sheets.

The Klaviyo API is very awkwardly designed for reporting, as nearly every request involves first pulling IDs, then running a second (or third...) request to associate those IDs with metrics. This article will give a few examples showing how it works. The good thing is, you only need to set up your requests once and then can automate your reporting.

Contents

• Before You Begin
• Part 1: Get your Klaviyo API Key
• Part 2: Example - Pull Contact Data to Sheets
• Part 3: Example - Pull Campaign Metrics to Sheets
• Part 4: Create a Custom Request with the New APIs
• Part 5: Handle Pagination
• Part 6: API Documentation

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Get Your Klaviyo API Key

1. From the dashboard, open the account menu in the top right and click Account.
   
2. In the account menu, click Settings > API Keys
   
3. Click Create Private API Key
   
4. You'll now see your API key. Copy that down as we'll use it in all our requests.
   

Part 2: Example - Pull Contact Data to Sheets

The easiest way to get started with the Klaviyo API is through API Connector’s built-in integration.

1. In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request)
2. Select Klaviyo from the drop-down list of applications
   
3. Select the /api/v2/lists to see the available lists in your account
   
4. Under parameters, fill in the api_key parameter with your API key
5. Run the request, and you will get back a table showing all of your lists and their respective IDs.
   
6. You can now put those list IDs in a different API request to get all of the contacts in that list. Choose the /api/v2/group/{list_id}/members/all endpoint and enter in a list ID from the list_id field in the prior request.
7. Click Run. Now you have a spreadsheet of all contacts from that particular list.
   

Part 3: Example - Pull Campaign Metrics to Sheets

For our second example, we'll pull in some campaign metrics. Unfortunately there's no simple endpoint to get a report containing standard metrics like open rates, click rates, bounce rates, and so on, for a list of campaigns. Instead we'll need to fetch our metric IDs, and then pull in metrics one by one. Here's how it works:

1. Select the /api/v1/metrics endpoint to get a list of all the metric IDs available in in your Klaviyo account
   
   
2. Now select the /api/v1/metric/{metric_id}/export endpoint to get a report for your metric of interest. Plug in your metric ID and hit Run.
   

Optionally filter for specific campaigns (e.g. to see click rate for a specific campaign) by using the where parameter, e.g. where = [["$message","=","YOUR_CAMPAIGN_ID"]]

Of course you'll need to run yet another request to get your campaign IDs... You can use the /campaigns endpoint for that.

Part 4: Create a Custom API Request with the New APIs

API Connector's integration currently uses Klaviyo's v1/v2 API. Klaviyo launched new APIs in October, 2022 (info), which you can access via a custom request. Here's an example request configuration:

• Application: Custom
• Request URL: https://a.klaviyo.com/api/profiles/?filter=greater-than(created,2022-04-01T11:30:00Z)&page[size]=100
• OAuth: None
• Headers:
  • Authorization: Klaviyo-API-Key your_api_key
  • Accept: application/json
  • Revision: 2023-01-24

Note that the new API also uses a new method of pagination. The new API uses next page URL pagination like this:

• Pagination type: next page URL
• Next page path: links.next
• Run until: choose when to stop fetching data
  

Part 5: Handle Pagination (v1/v2)

Klaviyo will generally limit the number of records returned at once, unless you loop through using the parameters shown in their documentation. The specific parameters depend on the endpoint. Note that this section applies to the v1/v2 API; see above for information on paginating the new APIs.

• Metric endpoints
• List & segment endpoints

Metric endpoints

Metric endpoints use the 'page' and 'count' parameters:

With API Connector, you can loop through these pages automatically using pagination handling, like this:

• Pagination type: page parameter
• Page parameter: page
• Run until: choose when to stop fetching data
  

List & segment endpoints

List & segment endpoints use the 'marker' cursor:

This would be automated like this:

• Pagination type: cursor
• Next token parameter: marker
• Next token path: marker
• Run until: choose when to stop fetching data
  

Part 6: API Documentation

Official API documentation: https://developers.klaviyo.com/en/reference/api-overview