Search API Connector Documentation

Print

Import Klaviyo Data to Google Sheets

Last Updated On
byAna
< Knowledge Base

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

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.
    klaviyo-apikey-img1
  2. In the account menu, click Settings > API Keys
    klaviyo-apikey-img2
  3. Click Create Private API Key
    klaviyo-apikey-img3
  4. You'll now see your API key. Copy that down as we'll use it in all our requests.
    klaviyo-apikey-img4

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
    klaviyo-application
  3. Select the /api/v2/lists to see the available lists in your account
    klaviyo-endpoints-list
  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.
    klaviyo-response
  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.
    klaviyo-response2

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
    klaviyo-endpoints-metrics
  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.
    klaviyo-response3

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
klaviyo-custom

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
    klaviyo-newapi-pagination

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:
klaviyo-pagination

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
    pagination-page-parameter

List & segment endpoints

List & segment endpoints use the 'marker' cursor:
klaviyo-pagination-marker

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
    klaviyo-pagination-marker-cursor

Part 6: API Documentation

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

12 thoughts on “Import Klaviyo Data to Google Sheets”

  1. 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.

    Reply

  2. 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

    Reply

    • 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.

      Reply

  3. 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

    Reply

    • 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 here for instructions and see if that resolves the issue.

      Reply

    • 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.

      Reply

    • 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/ endpoint
      2) 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)

      Reply

Leave a Comment

Jump To...