Connecting Power Bi to HubSpot Api

[Phantomiceman]

I am currently working at builidng a report in power bi that reports on the performance one of our hubspot email campaigns. For this I am trying to come up with an automated solution that will pull email campaign data from HubSpot into Power Bi. Currently this process invovles downloading a bunch data of exports from HubSpot as xlsx files, which can be tedious as a long term solution.I have tried using the Power BI connector on the the HubSpot market place (datawarehouse.io) and on the whole it does have alot of the information I require,however one fundemental column thats missing from the data that is available on the export and the API is the smtp id that groups all activities to a particular send. As we re-use the same email campaign each month as its part of an automated workflow therefore one receipeint could receive multiple emails from us from that campign, its essential we have this peice of information otherwise we are not able to accuratley identifty how many times a particular email has been opened, clicked etc.I am now currently looking at the email marketing API as this will be free to use(I want to try and save on cost if possible), however im new to this and im struggling to get much progress on it. I have tried to follow this post https://community.fabric.microsoft.com/t5/Developer/Pull-data-from-Hubspot-into-PBI-via-API/m-p/2206...  as im experiencing a similar problem but im still getting the same error as they are. I have read that people are stuggling to get to this to work after a change by HubSpot to private apps.Ideally I need a solution that will be able to loop through this API https://legacydocs.hubspot.com/docs/methods/email/get_events as it will only return a 1000 records at a time and I need the all campaign email event data for the current and previous year.

[ReportMaster]

Hi @Phantomiceman as a workaround, maybe you can use a 3rd party connector, which pulls data directly from the Hubspot API, it can save you a lot of time compared to the other options. I've tried windsor.ai, supermetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the Hubspot connector in the data sources list:

After that, just grant access to your Hubspot account using your credentials, then on preview and destination page you will see a preview of your Hubspot fields:

There just select the fields you need. Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url. 

[Anonymous]

Hi @Phantomiceman ,

After my testing, it's harder to try to implement the feature entirely in Power Query. My suggestion is to pull the missing columns you mentioned individually using another programming language, and continue to pull the rest of the normal ones using this method, and finally merge Queries in Power Query, which is probably the easiest solution at the moment.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.