Anbindung und Analyse von Daten aus HubSpot mit Power BI

by Jannik
5 Monaten ago
1023 Views

Anbindung und Analyse von Daten aus HubSpot mit Power BI

Digitalisierung, Modernes Marketing und Vertrieb für Unternehmen  – für viele Mittelständler und Unternehmen sind diese Worte wie Teenager Sex. Dementsprechend reden alle darüber, keiner will zu geben, dass er keinen hat und insgeheim weiß man auch gar nicht so richtig, wie es funktioniert.  Folglich ist der Klassiker, den man stattdessen vorfindet, der folgende: „Wir haben eine Exceltabelle – Rot eingefärbte Kontakte sind wichtig“. Aber 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 (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. Daher möchten wir in diesem Beitrag 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?

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

Des weiteren ist die zweite entscheidende Komponente für die praktische Umsetzung ein API-Key für HubSpot. Um einen HubSpot-API Schlüssel zu erstellen, benötigt man Admistratorrechte für den HubSpot-Account. Die Anleitung findet man auf knowledge.hubspot.com. Eine erste Erprobung kann aber auch einfach mit dem Demo Key „demo“ und durchgeführt werden, der über die API auf die Beispieldaten zugreift, welche HubSpot bereitstellt. Die 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 und eine Anbindung der Daten mit Power BI realisiert werden.

Schließlich können wir also auf alle Daten unseres HubSpot-Systems zugreifen. Nebenher können diese je nach unseren Bedürfnissen analysiert werden, ohne direkt in Power BI zu wechseln. 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. Beispielsweise 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

Jetzt muss an dieser Stelle 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

Danach die 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 property“ ist eine Einbindung von weiteren Eigenschaften des Deals somit 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.

Nachfolgend ist der Aufruf der Daten aus HubSpot in Power BI wie hier abgebildet zu sehen:

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

Jetzt kopieren wir für unser Beispiel 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

Nun liest PowerQuery aus der URL ein Json File aus. Dazu sehen wir am unterem Rand 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. Darum macht das erste genau das, was der Name sagt: Wenn es weitere Daten gibt, die relevant sind, dann ist dieses Feld wahr. Sollte dies nicht der Fall sein, dann wird 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

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

Hierzu kann folgender Text 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

Entsprechend ist ein Weg, um die API-URL und damit die Abfrage flexibler zu gestalten, 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. Folglich kann dieses Vorgehen genauso für verschiedene weitere Eigenschaften wiederholt werden. Anschließend sieht dies dann wie folgt aus:

Damit erkennen wir deutlich den API-Key Parameter, welchen wir jetzt steuern können. Desweiteren können wir auch weitere Teile der URL per Parameter bzw. Variable verändern. Abschließend wird dies in einem 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.

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

Folgerichtig sorgt das nachstehende Script dafür, dass 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.

HubSpot Power BI

Anschließend darf in dem aufklappenden Menu 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.

HubSpot

Schritt 8 der Anbindung von HubSpot durch Power BI

Transformieren des Zeitstempels

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

HubSpot mit Power BI Anbindung

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

Sie haben Fragen zu Power BI oder HubSpot?

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

Nachfolgend haben wir das M-Script aufgeführt, um die Schritte 7 und 8 im Beispiel komfortabler zu gestalten. Richtigerweise führt es alle beschriebenen Schritte automatisch aus. Dafür kann das Script im erweiterten PowerQuery Editor eingefügt und ausgeführt werden. Dadurch 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 eigene Umsetzung dienen kann.

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

HubSpot mit Power BI