People looking to gain insights from visual representations of their data can benefit from connecting their HubSpot data to Microsoft Power BI. Power BI is an advanced visualization tool that can transform data into visual insights for business intelligence.
HubSpot is a cloud-based CRM tool that can provide organizations with support for business growth through its sales, customer service and marketing software. The software enables users to access and analyze their data within the app to generate insights, but its reporting capabilities can be limited.
SEE: Hiring Kit: Database engineer (TechRepublic Premium)
By connecting the information from HubSpot and Power BI, you can transform your enterprise and CRM data to create insightful visuals and generate reports that can make it easier to understand and gain actionable insights from business data.
While the Power BI software connects to data sources to generate interactive reports from their data information, it does not provide direct native support for HubSpot integration. So how can you extract maximum value from your data?
After you have a HubSpot admin account and have created a Power BI account, you can follow the methods below to connect your systems for data access and increased functionality.
Import HubSpot CSVs to Power BI with HubSpot APIs
HubSpot APIs can be utilized to connect HubSpot CSVs to Power BI through a developer token-based authentication strategy.
To begin, go to the HubSpot admin panel. Here you can go to the settings icon on the navigation toolbar and use the integrations tab to generate an API key. If you’ve already generated an API key, click Show to display your API key (Figure A). Click Copy to copy your API key.
Figure A
Now, you can use the following curl command to access the contact details from your HubSpot admin account:
curl -o contacts_details.json https://api.hubapi.com/contacts/v1/lists/all/contacts/all?hapikey=<api_key>&count=10
Bear in mind that the API is paginated and has a limit of 100 items. Therefore, for a contact list containing over 100 items, you will be required to to use the vid-offset command to access the remaining information.
Once this is done, you will have a JSON file containing all of the customer contact information from your HubSpot account, named contacts_details.json.
To gain the relevant information from the JSON file and convert it into a CSV, you must use a command-line utility called ‘jq’
cat contacts_rdetails.json | jq -r ".contacts[] | [.vid,.properties.firstname.value,.properties.company.value,.properties.lastname.value] | @csv" >> contacts_data.csv
Now that you have the CSV file generated from these actions you can import this information into Power BI. In Power BI, hover over Get Data from the toolbar at the top of the screen to see the drop down menu. Select the Text/CSV option from the drop-down menu to import the CSV file (Figure B).
Figure B
By following these steps you should be able to access all of the CSV file data from HubSpot within Power BI.
Import HubSpot CSVs to Power BI with HubSpot UI
A no-code method to download CSVs from HubSpot is through the HubSpot user interface.
To access contact details, start in HubSpot and select Contacts, then click the drop-down button that says Export View (Figure C).
Figure C
Select Export and choose CSV as the file format.
Once this is done, HubSpot will send an email to the registered account email address. This should contain a link to download the CSV file (Figure D).
Figure D
From here you can import the CSV file information into Power BI by clicking Get Data from the toolbar at the top of the screen. Following Figure B once more, you can select the Text/CSV option to import the CSV file.
From here, you should be able to access all of the CSV file data from HubSpot within Power BI.
Using a pre-built connector: HubSpot and Power BI integration
Another way to connect HubSpot to Power BI is to utilize a pre-built connector. These connectors work between applications to facilitate the integration process. Many are available online and carry out different processes to transport information between the HubSpot and Power BI systems
One example of a HubSpot-certified middleware solution would be the DataWarehouse.io HubSpot Power BI connector that can be accessed to connect HubSpot data to Power BI and is available in the HubSpot App Marketplace. This integration method can allow users to query and manipulate data within Power BI without affecting HubSpot database integrity.
First, download the HubSpot certified API app. Authorize the app to connect to your HubSpot portal by selecting Connect App in HubSpot desktop portal, and fill out the required information to activate the service.
To connect a new or existing Power BI document using the HubSpot Connector, you must first select the Get Data button from the toolbar at the top of the Power BI application screen.
Once you see the Get Data pop-up, select Database from the list of options on the left-hand side of the pop-up (Figure E) and click SQL Server Database from the list on the right-hand side of the pop-up.
Figure E
Next, you will need to enter your login credentials, which were emailed to you when you signed up. Select OK (Figure F).
Figure F
After this, you will need to enter your user information. First, make sure you have the Database option highlighted on the left side of the new pop-up (Figure G), then add your user information and select Connect.
Figure G
Now you can populate all the tables with the information that is available from HubSpot. Select the tables you want to access in Power BI and click Load. This will load all of your fields with the data information from HubSpot. They will appear and become accessible under the section labeled Fields on the right side of the application (Figure H).
Figure H
The Power BI Integration App supports connectors with the following HubSpot CRM and Sales features: Company records, contact activity, deals, meetings, tasks, ticketing, contact and company insights, custom reporting, goals and multiple deal pipelines.
Microsoft Weekly Newsletter
Be your company's Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets.
Delivered Mondays and Wednesdays
