Technologische Kompetenz für modernes Business Intelligence aufbauen
Ob “Digitalisierung”, “Modernes Marketing” oder “Neue Vertriebskonzepte”: Für viele Unternehmen sind diese Begriffe, völlig zu unrecht, irgendwelche Hype-Themen. Trotzdem will natürlich niemand den Anschluss verpassen. Dementsprechend reden alle irgendwann auch über Data Management, Datenanalyse oder Business Intelligence, aber im Detail herrscht verschämte Ahnungslosigkeit. In der Praxis führt dies zu einer eher unprofessionellen Arbeitsweise. Statt die Vorzüge von standardisierten, automatisierten und tiefgründigen Business-Analysen zu nutzen, werden Entscheidungen immer noch auf Basis bunt eingefärbter Excel-Tabellen getroffen.
Veraltete Prozesse sind stets nur mit technologischer Weitsicht aufzubrechen. Um Vertriebs- und Marketingabteilungen umfassend zu strukturieren sind deshalb moderne Marketing Automation und Customer Relationship Manangment Systeme (CRM) empfehlenswert. Eine etabliert, weil beliebte, Plattform ist HubSpot. HubSpot ist eine führende All-in-one-Plattform für Inbound-Marketing, Sales, CRM und Kundenservice, die durchgehende Prozesse unterstützt. Dieser Beitrag zeigt, wie Daten aus HubSpot an Power BI angebunden werden können. Die aufgeführten Maßnahmen können ebenso auf andere per Rest API ansprechbare Programme beziehungsweise Apps angewendet werden.
Was ist für die Anbindung von HubSpot mit Power BI notwendig?
Für einen ersten Proof of Concept ist die kostenfreie Version von Power BI vollkommen ausreichend. Die folgenden Schritte können Power BI Anfänger also unmittelbar anwenden. Da die dahinter liegende Scriptsprache M ist, ist für Excel-Freunde die Anbindung mit demselben Script auch in Excel selbst 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, werden Administrationsrechte für den HubSpot-Account benötigt. Eine Anleitung dazu ist auf knowledge.hubspot.com zu finden. Eine erste Erprobung kann auch einfach mit dem Demo Key „demo“ durchgeführt werden, der über die API auf die Beispieldaten zugreift, welche HubSpot bereitstellt. Die Dokumentation der API ist unter folgendem Link zu finden: developers.HubSpot.com/docs/overview
Dieses Beispiel beschäftigt sich 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 kann also auf alle Daten des HubSpot-Systems zugegriffen werden. Nebenher können diese je nach den individuellen Bedürfnissen analysiert werden, ohne direkt in Power BI zu wechseln. Ob ein klassisches Reporting aufgesetzt werden soll oder es eher um das Thema Befähigung der einzelnen Mitarbeiter zu Self Service BI geht ist den jeweiligen Anforderungen überlassen. Auch welche Techniken zur Analyse genutzt werden soll ist im Einzelfall entscheidbar, von der einfachen Grafik bis zur komplexen Statistik.
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: Abfrage der API
Wie bereits erwähnt sollen im Beispiel nur die Informationen zu den Deals in HubSpot abgerufen und im weiteren Verlauf mit Power BI analysiert werden. 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 beziehungsweise der grundlegende Aufruf, der Deal-Sektion ohne die Spezifikation weiterer Parameter.
hapikey=demo
Jetzt muss an dieser Stelle der eigene HubSpot-API Key verwendet werden, um die Daten aus der eigenen 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 erfolgt die Angabe des Limits, wie viele Deals je Abfrage abgerufen werden. In diesem Beispiel sind es lediglich zwei 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 detaillierte Beschreibung, wie weitere Eigenschaften abgerufen werden können, findet sich auf developers.HubSpot.com/docs/overview.
Schritt 2: 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 Desktop-Variante von Power BI kann unter folgenden Link: Power BI Desktop – Interactive Berichte | Microsoft Power BI kostenlos heruntergeladen werden. Um die Reports in Power BI später gegebenenfalls komfortabel verteilen zu können, wäre ein Upgrade auf die kostenpflichtige Variante von Power BI Pro empfehlenswert.
Nachfolgend ist der Aufruf der Daten aus HubSpot in Power BI wie hier abgebildet zu sehen:
Verbinden mit Web-Daten in Power BI
Eingeben der URL für den API-Abruf
Jetzt wird für dieses Beispiel einfach die Beispiel-URL kopiert und in Power BI eingefügt:
https://api.hubapi.com/deals/v1/deal/paged?hapikey=demo&includeAssociations=true&limit=2&properties=dealname
Schritt 3: PowerQuery, Offset und hasMore
Nun liest PowerQuery aus der URL ein Json File aus. Dazu stehen 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: Auslesen der Werte für weitere Analyse
Anschließend werden mit der Hilfe des PowerQuery Editors Daten in eine Form überführt, in der sie weiterverarbeitet und genutzt werden 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:
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: 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 wird ein neuer Parameter angelegt, den wir in unserem 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. Des Weiteren können auch weitere Teile der URL per Parameter beziehungsweise Variable verändert werden. Abschließend wird dies in einem weiteren Schritt notwendig sein.
Schritt 6: 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:
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: Auflösen der JSON-Struktur in eine Tabelle
In diesem Zwischenschritt wird dafür gesorgt, dass die von Power BI empfangenen Hubspot-Daten in eine Struktur umgewandelt werden, mit dem in Power BI weiter gearbeitet werden kann. Dazu wird in jeder der Spalten auf das Symbol mit den zwei Pfeilen geklickt.
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 der abgefragten Daten.
Schritt 8: 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.
Nachdem dieser Schritt vollzogen ist, kann die Abfrage geschlossen werden. Hierdurch ist eine Datengrundlage für die weitere Analyse der Daten aus HubSpot in Power BI geschaffen.
Abkürzung der Schritte 7 und 8 per M-Script
Nachfolgend wird 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 wird die aufbereitete Datenbasis zur weiteren Analyse mit Power BI ebenfalls erreicht.
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: Aufbau eines 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.
