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

Du möchtest Klaviyo Reports und Metriken automatisiert über Power Query in deine Dashboards und Workbooks importieren?

Mit dieser Anleitung / diesem Code, kannst du über die Klaviyo API Metriken via Power Query in Power Bi oder Excel importieren und in deinen Dashboards und Tabellen verwenden. Das Ganze ist kostenlos, sodass du nicht für ein zusätzliches No-Code-Tool bezahlen musst.

Problemstellung: Klaviyo POST Request und Power Query Architektur

Die größte Herausforderung bei der Report-Automatisierung mit der Klaviyo API ist dabei, dass der Software-Anbieter die coole API (die Query Metrics Aggregate API) nur über HTTP-POST-Requests zugänglich gemacht hat. Die anderen Metriken Endpunkte sind dabei über GET-Requests bedienbar:

Power Query – das ETL Modul von Power Bi und Excel – hingegen hasst POST-Requests und wandelt mit der Web.Contents() Funktion Requests nur in POST-Requests um, wenn ein Requestbody definiert wurde. Und das Ganze ist alles andere als intuitiv zu coden und nicht über die GUI zu bewältigen. Entsprechend könnt ihr euch auf eine Menge M-Code freuen.

Der Code: Klaviyo Post Request via Power Query

Variablen

• Die Werte für API Key, metric_id und Revision sind Dummy-Werte, aber theoretisch sollten deine Requests so in der Art aussehen.
• Die Id der Metrik, die du suchst, musst du mit einem GET-Request an den Get-Metrics-Endpunkt abfragen. Ich habe das Thema zuerst für eine Metrik entwickelt, die die Anzahl der neuen Subscriber zu einer Liste ermittelt, aber du hast wahrscheinlich eigene Metriken im Kopf oder im Auftrag stehen.
• In diesem Fall habe ich die Werte für jeden Monat im Jahr 2022 abgefragt, was an dem Filter-Array mit greather-or-equal und less-than Parametern zu erkennen ist. Dieser Endpunkt kann lediglich Requests verarbeiten, die einen Zeitraum von maximal einem Jahr umfassen.
• Alle Parameter des Bodys außer dem „by:“-Array sind verpflichtend (ich habe es auch ohne versucht)

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

• Achte darauf, dass die Authentifizierung für den Request „anonymously“ in Power Query ist! Wir regeln die Authentifizierung hier im M-Code separat, weil Power Query Web.Contents() dafür zu unflexibel ist.
• Definiere RequestURL, RequestPath und RequestHeaders separat, ehe sie später in die Web.Contents() Funktion als Parameter / Optionen eingegeben werden.
• Beachte, dass die Klaviyo API den API-Key in der Authorization mit dem Klaviyo-API-Key Präfix erwartet!
• Der Revisions-Header ist auch verpflichtend und ist ein Konzept, dass ich nicht 100% durchdringe, aber ich setze den Parameter meistens auf Heute() – 1 Tag.
• Erstelle den RequestBody mit den Parametern, den die Klaviyo API erwartet. Wichtig: Die API erwartet einen JSON-Body. Entsprechend musst du diese schäbige Schreibweise verwenden, in denen du quasi jedes Anführungszeichen innerhalb des Variablen-Strings nochmal escapest (Deshalb die Doppel „“).
• Setze den Request (hier: Source) nach dem M-Schema zusammen.
  Web.Contents(url as text, optional options as nullable record) as binary
• In der Content-Option musst du deine Variable aus dem Request-Body noch einmal in Binary umwandeln, da der Request sonst noch fehlerhafte Zeichen an die API schicken will und es einen Error wirft!
• Bereite Power Query darauf vor die Response mit Json.Document() als JSON zu parsen (Vorsicht: Case-Sensitiv!)

Resultat

Am Ende müsstest du ein JSON-Object im Query-Editor angezeigt bekommen, das so in etwa aussieht:

Von dort aus, kannst du dich dann durch das JSON Dokument navigieren. Viel Spaß übrigens dabei. Dies ist nochmal ein neues Level von sh*tty – aber das ist dann dein Problem!

Ausbaustufe: Klaviyo POST Request Funktion

Weil ich die Daten nicht einmalig benötigt habe, habe aus der Abfrage eine Custom Function in Power Query gebaut, die es mir erlaubt, die Abfrage für jedes Jahr (ab einem Startzeitpunkt) durchzuführen.

Die Funktion variabilisiert dabei die folgenden Parameter:

• ReportStartDate, ReportEndDate – offensichtlich.
• Revision – Wie gesagt, keine Ahnung warum, aber ich nehme immer den gestrigen Tag / das gestrige Datum.

(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

Das erlaubt es mir dann entsprechende dynamische Report-Table zu Beginn der Haupt-Query zu erstellen und die JSON-Bodys dort einzufügen:
(Die Revision Variable ist dabei als separate Query innerhalb Editors definiert und muss nicht in die Formel übergeben werden).

Probleme mit der Klaviyo Report-Automatisierung?

Solltest du Probleme mit der Automatisierung deiner Reports in Bezug auf Klaviyo haben, lass gerne einen Kommentar da. Solltest du schnelle Hilfe benötigen, schau mal bei https://thebigc-agency.de vorbei. Die Lösung wurde von THE BIG C Agency aus einem Projekt bereitgestellt. Die Agentur spezialisiert unter anderem in der Daten-Analyse und Bereitstellung für Start-Ups und KMUs.