Search API Connector Documentation
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
- From the dashboard, open the account menu in the top right and click Account.
- In the account menu, click Settings > API Keys
- Click Create Private API Key
- 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.
- In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request)
- Select Klaviyo from the drop-down list of applications
- Select the
/api/v2/lists
to see the available lists in your account - Under parameters, fill in the api_key parameter with your API key
- Run the request, and you will get back a table showing all of your lists and their respective IDs.
- 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. - 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:
- Select the
/api/v1/metrics
endpoint to get a list of all the metric IDs available in in your Klaviyo account - 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
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
I'd like the query to pick up parameters fromt he spreadsheet.
At the moment I have to edit the query to to change the data range. I'd like to be able to get the query from a cell in the spreadsheet whenever it's run, so that the date ranges int he query are different every time it's run.
Hey Andy, sure, you can reference date cells in your sheet using +++ syntax. That way you won't need to manually update your request URL. Please see this article for more information and let me know if you have any questions!
Cheers. works a treat
Hi Ana - thank you for all your guides. They are very helpful!
I wanted to pull out performance (transactions and value) from specific flows and all the campaigns.
With
https://a.klaviyo.com/api/v1/campaigns?api_key=pk_*****
I still need to figure out a way to get the data i need.I looked at the documentation Klaviyo offers but I was wondering if you had the answer (and maybe others need this too).
https://help.klaviyo.com/hc/en-us/articles/360040093732-Export-Conversion-Data#conversions7
I wish to get: campaign_name, sent_date, total_opens, total_clicks, total_transactions and total_revenue
Excellent question. Unfortunately this API doesn't contain an endpoint to pull out campaign metrics all in one go. I found quite a few people asking similar questions in their forum, for example here. Based on the community manager responses, the only way to do this is through a sequence of API requests, where you pull out metrics one by one and filter on the campaign ID.
Hi Ana, Thanks for compiling this documentation. I'm extracting all members of a segment via this API call (https://developers.klaviyo.com/en/reference/get-segment-members) which worked for the first 1000 results, but I'm struggling to make the pagination work. It appears that if I use page parameter and run until page count equals 2 or more it just duplicates the data from the first 1000 rows.
Do you have any idea on how to get access to the next pages?
Thanks,
Wouter
Hey Wouter! It looks like this endpoint uses a different method of pagination (cursor instead of page parameter). I've updated the article with the correct pagination settings for getting list and segment members, please check the pagination section for instructions and see if that resolves the issue.
That did the trick. Cheers for the quick reaction, Ana!
Is it possible to pull the created date for the /api/v2/group/[LIST-ID]/members/all API? I can't find a field reference that works.
That endpoint doesn't contain created date, you can find a lot of discussion/complaints about it in their forum, e.g. here. Currently the solutions they provide are 1) pull the create data and other data for each individual profile with a request URL like
https://a.klaviyo.com/api/v1/person/12345
, where 12345 is replaced by the member ID, or 2) create a date-based segment and include that segment in your request, e.g.https://a.klaviyo.com/api/v2/group/12345/members/all
where 12345 is replaced by the segment ID. Either way you can use API Connector's multi-query functionality to loop through all the members or segments and run the API request for each one.Hi, is it possible to get a list of all segments and emails in them? Column A: Segment name, column B: email.
Thanks.
Kirill
I don't think Klaviyo provides an endpoint with this info, if that's what you mean. But you can create it yourself with a set of custom requests like this:
1) pull a list of your segments with the
https://a.klaviyo.com/api/segments/
endpoint2) Each segment ID will be returned in that list, so you can now cycle through the list and pull emails for each one like this:
https://a.klaviyo.com/api/segments/+++Segments!B2:B10+++/profiles/
3) Tick the "Add request URL" box so you have the profile ID listed next to each record (to get the name alongside the ID you can do a vlookup or similar function)
Hi there - I'm trying to get the number of subscribers in a given list and segment, but I can't see to find the right endpoint. Appreciate any help you could offer...
Hey PJ, unfortunately Klaviyo still hasn't made this available. Their forum suggests getting this info by exporting to CSV instead.