Klaviyo API POST Request in Power Query (Power Bi, Excel)

You want to import Klaviyo reports and metrics automaticylly via Power Query into your Dashboards and Workbooks?

With this guide / code, you can import metrics via Power Query into Power Bi or Excel via the Klaviyo API and use them in your dashboards and spreadsheets. The whole thing is free, so you don’t have to pay for an additional no-code tool.

Main Issue: Klaviyo Post Requests and the underlying Power Query Architecture

The biggest challenge in report automation with the Klaviyo API here is that the software vendor has made the cool API (the Query Metrics Aggregate API) accessible only via HTTP POST requests. The other metrics endpoints are accessible via GET requests:

Power Query – the ETL module of Power Bi and Excel – on the other hand hates POST requests and uses the Web.Contents() function to convert requests into POST requests only if a request body has been defined. And the whole thing is anything but intuitive to code and cannot be handled via the GUI. Accordingly you can look forward to a lot of M-code.

Code Snippet: Klaviyo Post Request via Power Query

Variables

• The values for API Key, metric_id and revision are dummy values, but in theory your requests should look something like this.
• You’ll need to query the id of the metric you’re looking for with a GET request to the get-metrics endpoint. I first developed the theme for a metric that gets the number of new subscribers to a list, but you probably have your own metrics in mind or on order.
• In this case, I queried the values for each month in 2022, which can be seen in the filter array with greather-or-equal and less-than parameters. This endpoint can only handle requests that span a period of one year or less
• All parameters of the body except the “by:” array are mandatory (I tried it without)

Request

(M-Code)

let
    RequestURL = "https://a.klaviyo.com",
    RequestPath = "/api/metric-aggregates/",
    RequestHeaders = [#"Revision" = "2023-07-15", #"Accept" = "application/json", #"Content-Type" = "application/json", #"Authorization" = "Klaviyo-API-Key aa_1aa111a1a11a1d1a22b222b333cccc4dd"],

    RequestBody = "{""data"": {""type"": ""metric-aggregate"",""attributes"": {""metric_id"": ""AAAbCC"",""measurements"": [""count""],""interval"": ""month"",""page_size"": 1500,""by"": [""List""],""filter"": [""greater-or-equal(datetime,2022-01-01T00:00:01)"",""less-than(datetime,datetime,2022-12-31T23:59:59)""],""timezone"": ""UTC""}}}",

    Source = Json.Document(Web.Contents(RequestURL, [RelativePath=RequestPath, Headers=RequestHeaders, Content=Text.ToBinary(RequestBody)]))

in Source

Walkthrough

• Make sure that the authentication option for the request is “anonymously” in Power Query! The authentication is handled separately in the M-code, because Power Query Web.Contents() is too inflexible for that.
• Define RequestURL, RequestPath and RequestHeaders separately before entering them as parameters / options later in the Web.Contents() function.
• Note that the Klaviyo API expects the API key in the Authorization Header with the Klaviyo API key prefix!
  The revision header is also mandatory and is a concept that I don’t 100% get through, but I usually set the parameter to Today() – 1 day.
• Create the RequestBody with the parameters that the Klaviyo API expects. Important: The API expects a JSON body. So you have to use this shabby notation, where you escape every quote inside the variable string again (hence the double “”).
• Make sure to compose the request (here: source) according to the M-scheme:
  Web.Contents(url as text, optional options as nullable record) as binary
• In the Content-Option you have to convert your variable from the request body to binary again, because otherwise the request will send incorrect characters to the API which will in return produce an error!
• Prepare Power Query to parse the response as JSON with Json.Document() (Caution: Power Query is case sensitive!)

Result

At the end you should get a JSON object in the query editor that looks something like this:

From there, you can navigate through the JSON document. Have fun with it by the way. The JSON structure is some next level sh*t – but by now that’s your problem!

Advanced Level: Klaviyo POST Request Function

Since I am in need of this object periodically and for multiple time periods, I built a Custom Function in Power Query to execute this query on deman, which allows me to run the query for each year (starting from a start time) and on every refresh.

In doing so, the function variabilizes the following parameters:

• ReportStartDate, ReportEndDate – Obviously.
• Revision – As I said, no idea why, but I always take yesterday’s day/date.

(M-Code)

(reportstart as text, reportend as text) =>
let
    RequestURL = "https://a.klaviyo.com",
    RequestPath = "/api/metric-aggregates/",
    RequestHeaders = [#"Revision" = revisions, #"Accept" = "application/json", #"Content-Type" = "application/json", #"Authorization" = "Klaviyo-API-Key aa_1aa111a1a11a1d1a22b222b333cccc4dd"],

    RequestBody = "{""data"": {""type"": ""metric-aggregate"",""attributes"": {""metric_id"": ""AAAbCC"",""measurements"": [""count""],""interval"": ""month"",""page_size"": 1500,""by"": [""List""],""filter"": [""greater-or-equal(datetime,"& reportstart & ")"",""less-than(datetime," & reportend & ")""],""timezone"": ""UTC""}}}",
    Source = Json.Document(Web.Contents(RequestURL, [RelativePath=RequestPath, Headers=RequestHeaders, Content=Text.ToBinary(RequestBody)])),
    data = Source[data]

in data

This allows me then to create a corresponding dynamic report table at the beginning of the main query and insert the JSON bodys there als cells:
(The revision variable is defined as a separate query within the editor and does not need to be passed into the formula).

Trouble with Klaviyo Report Automation?

If you have any problems with automating your reports related to Klaviyo, feel free to leave a comment. Should you need quick help, check out https://thebigc-agency.de. The solution was provided by THE BIG C Agency from a real life project. The agency specializes in data analysis and deployment for startups and SMEs, among others.