Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi there,
I am wondering if there is any opportunity to get data out of Hubspot into my BI reports.
Do you know something?
Regards,
Florian
Here's how I'm getting the data into a query using HubSpots API that is compatible with Power BI Online's scheduled refresh (i.e. it doesn't compain about "dynamic datasources"). Code is based on this other solution.
Here are some examples below. You should be able to follow the pattern for any of the other API endpoints.
Get all Marketing Emails
let
GetPages = (queryParams)=>
let
Host = "https://api.hubapi.com",
Source = Json.Document(
Web.Contents(
Host,
[RelativePath = "marketing-emails/v1/emails", Query = queryParams, Headers=[#"Content-Type"="application/json", Authorization="Bearer INSERT-YOUR-TOKEN-HERE"]]
)),
LL= @Source[objects],
Next = [limit="1000", offset = Source[#"offset"]],
result = try @LL & @GetPages(Next) otherwise @LL
in
result,
Fullset = GetPages([limit="1000"]),
#"Converted to Table" = Table.FromList(Fullset, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"
Get all CLICK events
let
GetPages = (queryParams)=>
let
Host = "https://api.hubapi.com",
Source = Json.Document(
Web.Contents(
Host,
[RelativePath = "email/public/v1/events", Query = queryParams, Headers=[#"Content-Type"="application/json", Authorization="Bearer INSERT-YOUR-TOKEN-HERE"]]
)),
LL= @Source[events],
Next = [limit="1000", eventType = "CLICK", offset = Source[#"offset"]],
result = try @LL & @GetPages(Next) otherwise @LL
in
result,
Fullset = GetPages([limit="1000", eventType = "CLICK"]),
#"Converted to Table" = Table.FromList(Fullset, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"
Get all Contacts
let
GetPages = (queryParams)=>
let
Host = "https://api.hubapi.com",
Source = Json.Document(
Web.Contents(
Host,
[RelativePath = "crm/v3/objects/contacts", Query = queryParams, Headers=[#"Content-Type"="application/json", Authorization="Bearer INSERT-YOUR-TOKEN-HERE"]]
)),
LL= @Source[results],
Next = [limit="100", properties = {"hubspot_owner_id", "firstname" , "lastname", "company" , "email"}, after = Source[#"paging"][#"next"][#"after"]],
result = try @LL & @GetPages(Next) otherwise @LL
in
result,
Fullset = GetPages([limit="100", properties = {"hubspot_owner_id", "firstname" , "lastname", "company" , "email"}]),
#"Converted to Table" = Table.FromList(Fullset, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
in
#"Converted to Table"
Yes, there is a new opportunity (Sep. 2024) to do that completely free - it's a certified HubSpot App that will synchronize all of your HubSpot data (and other sources too) and give you simple access to it:
https://ecosystem.hubspot.com/marketplace/apps/revos
It's completely free for daily sync and easy to setup, no coding required, here how you can set it up in under 5 minutes:
Hi @FN , you can use Power BI connector for this integration. Here one of the providers: https://vidi-corp.com/
Hi @FN,
You can use our open source Power BI connector for HubSpot.
All code is available for free on GitHub here:
https://github.com/audoxcl/Power-BI-HubSpot-Connector
And here are the instructions on how to install and use it:
https://www.youtube.com/playlist?list=PLI3w6pdNV1sR3foUBnwl15rU_uthKkjS8
If you have any questions or concerns, you can contact us here:
WhatsApp: +56 9 9675 0572
Email: info@audox.com
Javier Núñez, CEO
Audox
CRM, BI, PM Consulting Services
Skyvia provides two methods for integrating Power BI with HubSpot. One involves syncing HubSpot data to a data warehouse that's connected to Power BI, and the other involves creating an OData feed from HubSpot data for direct consumption by Power BI. These options facilitate seamless data import and analytics.
You can leverage Skyvia to integrate Hubspot with Power BI in two main ways. First, you can replicate your Hubspot data to a data warehouse and subsequently connect it to Power BI. Alternatively, you can expose your Hubspot data as an OData endpoint that Power BI can directly consume. Both methods facilitate seamless data sharing and analytics.
Hello,
I am trying to use the workaround of the link: GitHub - ranmax123/powerbi-custom-connectors: Repo for storing custom data connectors for Power BI (provided by someone in this chat). But unfortunately its not working for me.
In the workaround they use an API key but Hubspot changed this, you have to create an app that results in a specific API.but When i am using the API Key in Power Bi Desktop, it is not working. I got an error that the credentials are not right.
Is this method not working anymore or am I doing something wrong?
Is there any other recommendation now that HubSpot is sunsetting its API Key and replacing it with private apps?
@Wessling If you use the HubSpot Connector for Power BI from the HubSpot App Marketplace, there is no need for using an API key or creating a private app. Certified HubSpot apps don't use these methods for user authentication.
A HubSpot Certified App is available in the HubSpot App Marketplace for connecting HubSpot to Power BI. There is a free trial.
https://ecosystem.hubspot.com/marketplace/apps/marketing/analytics-data/powerbi
Here is a map of data flow from HubSpot to Power BI that the integration provides:
Companies | --> | Companies |
Contacts | --> | Contacts |
Deals | --> | Deals |
Engagements | --> | Engagements |
Email Events | --> | Email Events |
Tickets | --> | Tickets |
Owners | --> | Owners |
Products | --> | Products |
Forms | --> | Forms |
Companies | --> | Company Properties |
Contacts | --> | Contact Properties |
Associations | --> | CRM Associations |
Deal Pipelines | --> | Deal Pipelines |
Deals | --> | Deal Properties |
CampaignEmailEvents | --> | Marketing Email |
Custom Objects | --> | CRM Associations |
Quotes | --> | CRM Associations |
Contact Lists | --> | Contact Lists |
Website Pages | --> | Analytics |
Workflows | --> | Workflows |
Here is a link to the documentation: https://support.datawarehouse.io/hc/en-us/articles/360006051874-Power-Bi-Connector-Manual
You can use one of the multiple platforms available but test their free plans first.
Here is one that worked for me.
https://windsor.ai/microsoft-power-bi-hubspot-report-dashboard-template/
The OP posted just the question I wanted to ask, but I was hoping the solution would be something more straight-forward.
It's still rediculously complicated to get data (from anywhere tbh) into Power BI in a visually useful manner. Getting it into Power BI is only the start - and that sucks.
I found this alternative to all the hastle usually involved; don't get me wrong, I still might need to do some tweaking of the reports, but I've got something to start with - and a good one too.
Check out Tugger (https://tuggerapp.com); they poll your data into a data warehouse and provide a free report template for Power BI.
The signup was easy, and had a 14 day free trial too.
What surprised me was how easy it was; it basically does what it says on the tin.
If you are looking for a no-coding solution, I can recommend you Skyvia. It is a third-party tool that allows you to load data from HubSpot to Power BI (https://skyvia.com/data-integration/analyze-hubspot-with-powerbi)
This integration isn't performed directly - Skyvia provides it through a database or cloud data warehouse with automatic schema creation.
Hi All,
We just released an update to the FREE Power BI Connector to HubSpot with following updates:
1. Tickets object
2. Owners object
3. Associated company in contacts object
4. Associated company in deals object
Head to our repo: https://github.com/ranmax123/powerbi-custom-connectors
Direct link to the .MEZ file: https://github.com/ranmax123/powerbi-custom-connectors/tree/master/HubSpot/build
For any issues and/or enhancement requests you can open a GitHub issue.
Cheers,
Ranbeer Makin
hi @ranbeermakin, is this option "data safe"?
(I'm starting with this sync and I want my data to be safe)
WOW! Can't believe that an open source PBI integration really exists, lol.
Very cool of you guys making this available, me and my company appreciate it a lot 😉
Do you know how the data update works? I couldn't find any info about it on the repo
I'm having trouble getting the the Hubspot Ticket table to appear in the Navigator. I'm not sure which "Column 1" the error (screen shot below) is referring to, or how to fix it. Any ideas?
You could try Tugger LTD's solution, as I posted previously; the example reports they include might actually provide what you need.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
12 | |
11 | |
11 | |
8 |
User | Count |
---|---|
43 | |
25 | |
15 | |
14 | |
12 |