Anbindung und Analyse von Daten aus HubSpot mit Power BI

by Jannik
2 Wochen ago
679 Views

Modernes Marketing und Vertrieb für Unternehmen

Digitalisierung – für viele Mittelständler und Unternehmen ist dieses Wort wie Teenager Sex – alle reden drüber, keiner will zu geben, dass er keinen hat und insgeheim weiß man auch gar nicht so richtig wie es funktioniert.  Der Klassiker, den man stattdessen vorfindet, ist der folgende: „Wir haben eine Exceltabelle – Rot eingefärbte Kontakte sind wichtig“. Jede weitere Analyse oder tiefere Begründung bleibt jedoch aus.

Ein Mittel, um ein wenig Struktur in die Vertriebs- und Marketingabteilungen zu bekommen, sind moderne Marketing Automation und Customer Relationship Manangment Systeme, kurz CRM. Eine beliebte Plattform ist HubSpot. HubSpot ist eine führende All-in-one-Plattform für Inbound-Marketing, Sales, CRM und Kundenservice, welche durchgehende Prozesse unterstützt. In diesem Beitrag möchten wir aufzeigen, wie Daten aus HubSpot an Power BI angebunden werden können. Die gezeigten Techniken können aber auch auf andere per Rest API ansprechbare Programme bzw. Apps angewendet werden.

Was ist für die Anbindung von HubSpot mit Power BI notwendig?

Für einen ersten Prof of Concept ist die freie Version von Power BI vollkommen ausreichend, sodass man die nachfolgenden Schritte direkt nachmachen. Für Excelfreunde ist die Anbindung mit demselben Script auch in Excel per Power Query möglich, da die dahinter liegende Scriptsprache M identisch ist.

Die zweite entscheidende Komponente für die praktische Umsetzung ist ein API-Key für HubSpot. Um einen HubSpot-API Schlüssel zu erstellen, benötigt man Admistratorrechte für den HubSpot-Account. Eine Anleitung findet man auf knowledge.hubspot.com. Für einen erste Erprobung kann aber auch einfach der Demo Key „demo“ gewählt werden, mit dem auf die Beispieldaten, welche HubSpot bereitstellt über die API zugegriffen werden kann. Eine Dokumentation der API ist unter folgenden Link zu finden: developers.HubSpot.com/docs/overview

In unserem Beispiel beschäftigen wir uns nur mit den Deals und der Deal API von HubSpot. Die angewendeten Schritte und Techniken können aber genauso auf Contacts und andere Bereiche Übertragen werden und eine Anbindung der Daten mit Power BI realisiert werden.

Wie können also auf alle Daten unseres HubSpot-Systems zugreifen und diese je nach unseren Bedürfnissen ohne direkt in Power BI analysieren. Ob wir ein klassisches Reporting aufsetzen oder eher in die Richtung Befähigung der einzelnen Mitarbeiter zu Self Service BI gehen, ist dabei vollkommen unseren Anforderungen überlassen – ebenso welche Techniken wir zur Analyse nutzen – von einfachen Grafiken bis zu komplexeren Statistiken.

Anleitung für die Anbindung von HubSpot in Power BI

Die Datenanbindung aus HubSpot erfolgt in diesem Beispiel direkt durch Power BI und ohne zusätzliche Software oder Zwischenspeicherung in einer Datenbank.

Schritt 1 der Anbindung von HubSpot durch Power BI

Abfrage der API

Wie bereits erwähnt, möchten wir im Beispiel nur die Informationen zu den Deals in HubSpot abrufen und im weiteren Verlauf mit Power BI analysieren. Folgende URL ist die Standard „Get all del“-Abfrage der HubSpot API:

https://api.hubapi.com/deals/v1/deal/paged?hapikey=demo&includeAssociations=true&limit=2&properties=dealname

Folgend eine kurze Erklärung wie sich dieser API-Call zusammensetzt:

https://api.hubapi.com/deals/v1/deal/paged?

Der obige Teil der Abfrage ist der URL bzw. der grundlegende Aufruf, der Dealsektion ohne die Spezifikation weiterer Parameter.

hapikey=demo

An dieser Stelle muss der eigene HubSpot-API Key verwendet werden, um die Daten aus der eignen HubSpot-Plattform abrufen zu können. hapikey=“Eigener API KEY von HubSpot“ oder für Abruf der Beispieldaten „demo“.

&includeAssociations=true

Das &-Zeichen verknüpft die einzelnen Teile der Abfrage miteinander. An dieser Stelle wird die Möglichkeit genutzt, die mit dem Deals verknüpften Kontakte mit abzurufen.

&limit=2

Angabe des Limits wie viele Deals je Abfrage abgerufen werden. In diesem Beispiel sind es lediglich 2 Deals, maximal sind es 250, Standardeinstellung der HubSpot-API sind 100.

&properties=dealname

Die „properties“ ermöglichen es verschiedenen Eigenschaften der Deals abzufragen. In diesem Beispiel wird der Name des Deals abgerufen, um diese später als Dimension zur Auswertung in Power BI zur Verfügung zu haben. Durch das & wird einerseits dieser Teil der Abfrage mit der restlichen Abfrage verbunden, andererseits können weitere Eigenschaften abgerufen werden, um die Auswertungen später in Power BI aussagekräftiger gestalten zu können und um eine höhere Analyseflexibilität zu haben. Mit &properties=“name der entsprechenden propertie“ ist eine Einbindung von weiteren Eigenschaften des Deals so einfach möglich. Eine detailliere Beschreibung, wie man weitere Eigenschaften abrufen kann, findet sich auf developers.HubSpot.com/docs/overview.

Schritt 2 der Anbindung von HubSpot durch Power BI

Power BI mit Power Query

Für die hier gezeigte Lösung ist nichts weiter notwendig als die kostenfreie Variante von Power BI Desktop (Anmerkung für Excelfans – PowerQuery und PowerPivot können den Job auch erledigen). Die Desktopvariante von Power BI kann unter folgenden Link: https://powerbi.microsoft.com/de-de/desktop/ kostenlos heruntergeladen werden.

Um die Reports in Power BI später gegebenenfalls komfortabel verteilen zu können, wäre ein Upgrade auf Kostenpflichtige Variante Power BI Pro empfehlenswert. Eine kurze erste Einführung zum Thema Power BI und Datenanbindung gibt es in unserem YouTube-Video aus unsere Reihe „Watch & Learn“ oder bei Microsoft.

Der Aufruf der Daten aus HubSpot in Power BI gestaltet sich wie nachfolgend abgebildet:

Verbinden mit Webdaten in Power BI
Eingeben der URL für den API-Abruf

Für unser Beispiel kopieren wir einfach die Beispiel-URL und fügen diese in Power BI ein: https://api.hubapi.com/deals/v1/deal/paged?hapikey=demo&includeAssociations=true&limit=2&properties=dealname

Schritt 3 der Anbindung von HubSpot durch Power BI

PowerQuery, Offset und hasMore

Aus der URl liest PowerQuery ein Json File aus – am unterm Rand sehen wir zwei sehr wichtige Felder, da die Abfrage nicht alle Daten mit einem Mal ausgeben kann, gibt sie einen Feld hasMore und ein Feld offset wieder. Das erste macht genau das was der Name sagt: Gibt es weitere Daten die relevant sind dann ist dieses Feld wahr, sollte dies nicht der Fall sein dann wir der Wert falsch ausgegeben. Um nun für zukünftige Abfragen an der richtigen Stelle wieder mit der Abfrage zu beginnen, gibt das zweite Feld an welcher Datensatz, der zuletzt ausgegebene war.

Schritt 4 der Anbindung von HubSpot durch Power BI

Auslesen der Werte für weitere Analyse

Mit der Hilfe des PowerQuery Editors überführen wir Daten in eine Form, in der wir sie weiterverarbeiten und nutzen können. Dies sieht im Erweiterten Editor dann wie folgt aus:

Folgender Text kann per in den Editor eingefügt werden, um die gezeigte Tabelle zu erzeugen:

Script anzeigen

let

    Source = Json.Document(Web.Contents(„https://api.hubapi.com/deals/v1/deal/paged?hapikey=demo&includeAssociations=true&limit=2&properties=dealname“)),

    deals = Source[deals],

    #“Converted to Table“ = Table.FromList(deals, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #“Expanded {0}“ = Table.ExpandRecordColumn(#“Converted to Table“, „Column1“, {„dealId“, „properties“}, {„Column1.dealId“, „Column1.properties“}),

    #“Expanded {0}1″ = Table.ExpandRecordColumn(#“Expanded {0}“, „Column1.properties“, {„dealname“}, {„Column1.properties.dealname“}),

    #“Expanded {0}2″ = Table.ExpandRecordColumn(#“Expanded {0}1″, „Column1.properties.dealname“, {„value“}, {„Column1.properties.dealname.value“})

in

    #“Expanded {0}2″

Schritt 5 der Anbindung von HubSpot durch Power BI

Parameter zur Steuerung der API

Ein Weg, um die API-URL und damit die Abfrage flexibler zu gestalten, ist das Anlegen von Parametern für einzelne Bestandteile der Abfrage.  Beispielweise, um sie für weitere Eigenschaften zu verwenden oder um den API-Key zu ändern.

Dafür legen wir einen neuen Parameter an, den wir in unsern Beispiel API-KEY nennen. Dieses Vorgehen kann genauso für verschiedene weitere Eigenschaften wiederholt werden. Dies sieht in der dann wie folgt aus:

Wir erkennen deutlich den API-Key Parameter, welchen wir jetzt steuern kann. Wir können auch weitere Teile der URL per Parameter bzw. Variable verändern dies wir in einen weiteren Schritt notwendig sein.

Schritt 6 der Anbindung von HubSpot durch Power BI

Alle Daten mittels Loop abfragen

Der folgende Schritt ist der entscheidende, wenn es darum geht die Lösung in einem Unternehmen effektiv einzusetzen und nicht nur das maximale Abfragelimit von 250 Datensätzen zu nutzen.

Um alle gewünschten Daten aus HubSpot abfragen zu können, kann in Power BI (PowerQuery) eine Schleife erstellt werden, welche folgender Logik folgt: Lade Daten solange hasMore=ture und beginne immer mit dem letzten Offset.

Folgendes Script sorgt dafür das Power BI die HubSpot API mehrfach abruft:

Script anzeigen

let

HubSpot = List.Skip(List.Generate( () => [IsMore = null, Last_Key = 0, Counter = 0], // Start Werte der Liste

each [IsMore] <> false,

each [WebCall  = Json.Document(Web.Contents(„https://api.hubapi.com/deals/v1/deal/paged?“ & „hapikey=“ & #“API-KEY“ & „&includeAssociations=true“ & „&limit=100″&“&properties=dealname&porperties=createdate“

& „&offset=“&Text.From([Last_Key])&““,

[Headers=[ContentType=“application/json“, Authorization=“Bearer Token“]])),// Abfrage und Schleife

Last_Key = try [WebCall][#“offset“] otherwise 0,

IsMore = if [Counter] < 1 then null else [WebCall][#“hasMore“],

Counter = [Counter]+1,

Table = Table.FromRecords(WebCall[deals])

]

,each [Table] // selector

) ,1)

in   HubSpot

Schritt 7 der Anbindung von HubSpot durch Power BI

Auflösen der JSON-Struktur in eine Tabelle

In diesem Zwischenschritt sorgen wir dafür, dass die von Power BI empfangenen Hubspot-Daten, in eine Struktur umgewandelt werden, mit dem man in Power BI weiterarbeiten kann. Dazu klicken wir in jeder der Spalten auf das Symbol mit den zwei Pfeilen.

In dem aufklappenden Menu darf in jeder Spalte dann nur der Value markiert sein, um die Werte aus der JSON-Struktur herauszulösen. Values enthält die durch die Angabe der Properties unsere abgefragten Daten.

Schritt 8 der Anbindung von HubSpot durch Power BI

Transformieren des Zeitstempels

Der Zeitstempel der Spalte „Createdate“ ist in UNIX-Standardzeit angegeben. Für eine weitere Analyse im herkömmlichen Datumsformat, muss diese einmalig umgewandelt werden.

Nach dem wir diesen Schritt vollzogen haben, können wir die Abfrage schließen und haben eine Datengrundlage für die weiter Analyse der Daten aus HubSpot in Power BI geschaffen.

Abkürzung der Schritte 7 und 8 per M-Script

Um die Schritte 7 und 8 im Beispiel komfortabler zu gestalten, haben wir nachfolgend das M-Script aufgeführt, welches alle beschriebenen Schritte automatisch ausführt. Dieses Script kann im erweiterten PowerQuery Editor eingefügt und ausgeführt werden. So gelangt man ebenfalls zu der aufbereiteten Datenbasis zu weiteren Analyse mit Power BI.

Script anzeigen

let

HubSpot = List.Skip(List.Generate( () => [IsMore = null, Last_Key = 0, Counter = 0], // Start Werte der Listeeach [IsMore] <> false,

each [WebCall  = Json.Document(Web.Contents(„https://api.hubapi.com/deals/v1/deal/paged?“ & „hapikey=“ & #“API-KEY“ & „&includeAssociations=true“ & „&limit=100″&“&properties=dealname&properties=createdate“ & „&offset=“&Text.From([Last_Key])&““,

[Headers=[ContentType=“application/json“, Authorization=“Bearer Token“]])),// Abfrage und Schleife

Last_Key = try [WebCall][#“offset“] otherwise 0,

IsMore = if [Counter] < 1 then null else [WebCall][#“hasMore“],

Counter = [Counter]+1,

Table = Table.FromRecords(WebCall[deals])],each [Table] // selector) ,1),

    #“Anlegen der Tabelle“ = Table.FromList(HubSpot, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    Aufklappen = Table.ExpandTableColumn(#“Anlegen der Tabelle“, „Column1“, {„portalId“, „dealId“, „isDeleted“, „associations“, „properties“, „imports“, „stateChanges“}, {„Column1.portalId“, „Column1.dealId“, „Column1.isDeleted“, „Column1.associations“, „Column1.properties“, „Column1.imports“, „Column1.stateChanges“}),

    #“Überflüssige Spalten entfernen“ = Table.RemoveColumns(Aufklappen,{„Column1.associations“, „Column1.imports“, „Column1.stateChanges“, „Column1.portalId“, „Column1.isDeleted“}),

    Dealeigenschaften = Table.ExpandRecordColumn(#“Überflüssige Spalten entfernen“, „Column1.properties“, {„dealname“, „createdate“}, {„Column1.properties.dealname“, „Column1.properties.createdate“}),

    #“Expanded {0}2″ = Table.ExpandRecordColumn(Dealeigenschaften, „Column1.properties.dealname“, {„value“}, {„Column1.properties.dealname.value“}),

    #“Umbennen der Spaltennamen“ = Table.RenameColumns(#“Expanded {0}2″,{{„Column1.properties.dealname.value“, „Dealname“}}),

    #“Expanded {0}3″ = Table.ExpandRecordColumn(#“Umbennen der Spaltennamen“, „Column1.properties.createdate“, {„value“}, {„Column1.properties.createdate.value“}),

    #“Spalte Create Time“ = Table.RenameColumns(#“Expanded {0}3″,{{„Column1.properties.createdate.value“, „Create Time“}}),

    #“Sicher stellen das keine Duplicate in der Liste sind“ = Table.Distinct(#“Spalte Create Time“, {„Column1.dealId“}),

    #“Changed Type“ = Table.TransformColumnTypes(#“Sicher stellen das keine Duplicate in der Liste sind“,{{„Dealname“, type text}, {„Column1.dealId“, Int64.Type}, {„Create Time“, Int64.Type}}),

    #“Transformieren in Nomal Zeit“ = Table.AddColumn(#“Changed Type“, „Benutzerdefiniert“, each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [Create Time]/1000)),

    #“Removed Columns“ = Table.RemoveColumns(#“Transformieren in Nomal Zeit“,{„Create Time“}),

    #“Changed Type1″ = Table.TransformColumnTypes(#“Removed Columns“,{{„Benutzerdefiniert“, type datetime}}),

    #“Renamed Columns“ = Table.RenameColumns(#“Changed Type1″,{{„Benutzerdefiniert“, „Create Time“}})

in

    #“Renamed Columns“

Schritt 9 der Anbindung von HubSpot durch Power BI

Aufbau eins ersten Dashboards

Nachfolgend haben wir ein kleines Beispiel für ein Dashboard erstellt, welches als Anregung für die eignen Umsetzung dienen kann.

Ausgewertet wird die Anzahl der Deals im zeitlichen Verlauf. Hierzu werden verschiedene Visualisierungen innerhalb des Dashboards genutzt. Je nachdem, welche zusätzlichen Properties man in der HubSpot API abruft, können zusätzlich eine Vielzahl komplexerer Dashboards in Power BI erstellt werden. Mit DAX-Berechnungen kann zudem die Basis für weitere vertiefende Analysen gelegt werden.

Sie haben noch Fragen?