Connect HubSpot to Power BI: 2 Easy Methods

Are you struggling to connect HubSpot to Power BI? If yes, then you have landed in the right place. This post is about the steps involved in getting data from HubSpot and using that as a source for analysis using Power BI, a popular business intelligence tool from Microsoft.

Power BI does not support the HubSpot connector natively but allows importing data in various formats. You will use a CSV file import for this attempt. HubSpot supports exporting various data elements through its user interface. It also provides a rich set of APIs to extract data. You can connect HubSpot to Power BI using the following methods:

Method 1: Importing CSV Generated via HubSpot APIs

HubSpot allows access to API through an Oauth-based authentication and via developer token-based authentication. For this exercise, you will be using a developer token-based authentication. You will begin by going to the HubSpot admin panel and generating the API keys.

1. Go to settings and generate API keys using the integrations tab.
2. Once you have the API keys, use the below curl command to fetch the contact details from HubSpot.

curl -o contacts_details.json https://api.hubapi.com/contacts/v1/lists/all/contacts/all?hapikey=<api_key>&count=10

The output of the command will be a JSON file named contacts_details.json. The above API provides access to all the customer contacts that are stored in your HubSpot account. The API is paginated with a limit of 100 items. If your list is beyond this limit, you will need to use an additional parameter called vid-offset to fetch the rest of the items.

3. You will now use a command-line utility called ‘jq’ to extract the relevant details from the JSON file and convert it into a CSV file.

cat contacts_rdetails.json | jq -r ".contacts[] | [.vid,.properties.firstname.value,.properties.company.value,.properties.lastname.value] | @csv" >> contacts_data.csv

The generated CSV file will look as below.

9333497,raghav,hevo ltd,heera
9333498,dawson,mercury.com,richard
9333495,user1_name,,user2
9333494,romira,tel-mario ltd,hector
9333493,marsh,tel-extel inc,jack

4. The above CSV file can now be imported to Power BI. To do this, go to Power BI and click “Get Data” and then, import the CSV file as shown below.

Drawbacks

The above approach uses HubSpot APIs to extract data. The biggest advantage of using this approach to connect HubSpot to Power BI is that it can be automated by converting the above set of steps into a script. But there are a few drawbacks to this approach.

1. Additional steps to handle pagination and API access limits of HubSpot should be implemented.
2. The Power BI import is still manual because of the lack of Power BI native support. Power BI API supports uploading data as excel files. So as a workaround, converting CSV to excel and then using Power BI APIs to import can be considered.
3. The method above shows using the contacts API. HubSpot has a large list of APIs, which the developer should be familiar with to use all the data with Power BI.

Method 2: Importing CSV Generated via HubSpot User Interface

HubSpot provides a simple user interface to download CSVs for various data elements. To download the contact details as CSV, follow these steps:

1. Login to HubSpot and navigate to “Contacts”. In the Contacts view, click the “Export View” as shown below.

2. Click “Export” and select “CSV” as the file format. 

3. HubSpot will send an email to the registered email account with a link to download the CSV file.

4. Use this CSV file and import data to Power BI as mentioned in the previous method.

Drawbacks

The advantage of using this method to connect HubSpot to Power BI is that it does not require you to write code. But this comes with the obvious drawbacks as listed below. 

1. There is very little possibility of automating this flow.
2. Power BI import is still manual. Even though Power BI supports importing data via API, it does not support CSV file import. A workaround is to first convert files to Excel and then use the import API.
3. There are limited opportunities to execute a transformation before uploading to Power BI. 

Connect Power BI Hubspot with a pre-built connector

Middleware solution certified by HubSpot is one of such pre-built connectors. This is available directly on the HubSpot App Marketplace.

You can query and manipulate data within Power BI without any troubles with the integrity of the HubSpot database. The steps involved in using this pre-built connector are,

• Download the HubSpot-certified API app -> choose the “Connect an App” from the PC portal authorize to connect to your HubSpot portal -> type in all the necessary details to enable the service.
• Connect a new or existing Power BI document by selecting the “Get Data” button on the Power BI toolbar.
• In the next window, select the “Database” from the list on the left menu. Then select  “SQL Server Database.”
• Type in your login credentials -> select OK ->  provide user details -> highlight “Database” option on the left side of the pop-up window -> select “Connect.”
• Now, you can populate tables with details from HubSpot -> choose the tables you want to access in Power BI -> select “Load.”

Why Connect Hubspot and PowerBI?

The HubSpot Power BI Integration aids data analysis for business analysts and decision-makers. The HubSpot to Power BI connector is easy to set up with just a few clicks, and it comes with the necessary cloud-based server and data warehouse.

Data may be queried and updated in Power BI without compromising the HubSpot database or SQL server’s integrity. This enables you to reconcile sales and accounting data within Power BI without incurring the costs of directly linking the two systems.

With capabilities like quick measurements, grouping, forecasting, and clustering, Power BI allows business users to delve deeper into data and discover trends they might have missed otherwise. You’ll feel right at home in Power BI if you’re familiar with Excel.

Conclusion

In this blog, you have learned about the various ways to connect HubSpot to Power BI. Each method has its own merits and demerits. But if you want to integrate your data before opting for analysis and visualization in a BI platform, then you can use Hevo.

Share your experience of connecting HubSpot to Power BI in the comment section below.