API Connector Documentation

Print

Import Klaviyo Data to Google Sheets

Last Updated OnJune 28, 2024
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.

Klaviyo's v1 and v2 APIs will be sunset on June 30, 2024, so API Connector's preset integration for Klaviyo will no longer work. Instead, create a custom request to Klaviyo's new unversioned APIs.

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: Create a Custom API Request with the New APIs

The biggest benefit of Klaviyo's new APIs is that they allow you to fetch metrics for all your campaigns at once (still only a single metric at a time, though). Below is an example request; substitute in your metric ID where it says your_metric_id, and your API key where it says your_api_key. You can also try out different breakdown variables by substituting "$attributed_message" with the attributes shown here.

  • Application: Custom
  • Method: POST
  • Request URL: https://a.klaviyo.com/api/metric-aggregates/
  • OAuth: None
  • Headers:
    • Authorization: Klaviyo-API-Key your_api_key
    • Content-Type: application/json
    • Revision: 2024-06-15
  • Request Body:
    {
    "data": {
    "type": "metric-aggregate",
    "attributes": {
    "metric_id": "your_metric_id",
    "measurements": [
    "count"
    ],
    "interval": "day",
    "page_size": 500,
    "timezone": "UTC",
    "filter": [
    "greater-or-equal(datetime,2023-01-01T00:00:00+00:00)",
    "less-than(datetime,2024-01-01T00:00:00+00:00)"
    ],
    "by": [
    "$attributed_message"
    ]
    }
    }
    }
  • Report style: grid (under Output options)
  • Flatten field to header: custom (under Output options)
    • Path to header: data.attributes.data.dimensions
    • Path to value: data.attributes.data.measurements
    • Flatten array: checked
klaviyo-v4customResponse

Part 3: Handle Pagination

Klaviyo's pagination methods vary based on the endpoint.

Endpoints using the POST method, like the metric-aggregates endpoint, generally return 500 records by default, and use "cursor body" pagination to fetch more.

Endpoints using the GET method generally use cursor-based pagination as shown here.

Part 4: API Documentation

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

Query Metric Aggregates documentation: https://developers.klaviyo.com/en/reference/query_metric_aggregates

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

      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 the pagination section 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

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

    Reply

Leave a Comment

Jump To