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. HubSpot is a completely managed cloud-based customer relationship management software (CRM). HubSpot provides three modules – a marketing hub, a sales hub, and a service hub. The three hubs allow companies to manage all activities related to their leads and customers. Solutions like HubSpot makes business easier by relieving companies from making huge investments in custom software development. HubSpot offers subscription-based pricing for each module.

A drawback of using cloud-based solutions for managing your business process is that you lack complete control over your raw customer data and are limited to analyzing data through the reporting dashboards provided by the software. It is a typical requirement in organizations to extract the customer data into an on-premise server and then run ad hoc analysis on the data using enterprise-level business intelligence tools. 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. 

Table of Contents

• Prerequisites
• What is Hubspot?
• What is PowerBI?
• Why connect Hubspot and PowerBI?
• Method 1: Importing CSV Files Generated via HubSpot APIs
• Method 2: Importing CSV Files Generated via HubSpot User Interface
• Conclusion

Prerequisites

• HubSpot admin account.
• Create a Power BI account.
• Basic knowledge of databases and business intelligence tools.

What is Hubspot?

Hubspot is a Marketing, Sales, and Service platform that allows you to store and analyze all of your company development data in the cloud, including Leads, Contacts, Deals, Activities, and so on.

HubSpot is a one-stop shop for Content Creation, Lead Generation, Social Media Sharing, CRM, Workflow Automation, Sales Funnel Mapping, and Performance Tracking. Thousands of businesses rely on HubSpot products for their business needs due to its user-friendly interface and affordable pricing options.

What is PowerBI?

Microsoft’s Power BI is a popular Business Analytics service. It allows users to generate Reports, Visualizations, and Dashboards using data from a variety of sources. Power BI is available as a desktop program or as a fully managed Cloud Service. For users that want to deploy a web version on-premise, Microsoft also offers a stripped-down version of Power BI Cloud called a Power BI Report Server.

With more firms opting for Cloud-based solutions over on-premise solutions, Power BI Cloud has become a popular choice among business analysts. Its ability to seamlessly connect with Active Directory authentication and Azure services makes it a strong fit for businesses that already use Microsoft’s stack.

Why connect Hubspot and PowerBI?

The HubSpot + Power BI Integration aid 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.

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. 

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.

Hevo Data, with its strong integration with 100+ Sources & BI tools such as HubSpot, allows you to not only export data from sources & load data in the destinations, but also transform & enrich your data, & make it analysis-ready so that you can focus only on your key business needs and perform insightful analysis using BI tools. It helps transfer data from HubSpot to a destination of your choice for free. In short, you can easily extract data from HubSpot using Hevo and visualize it on Power BI.

visit our website to explore hevo

Give Hevo a try and sign up. Hevo offers plans & pricing for different use cases and business needs, check them out!

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