HubSpot provides the possibility of having dashboards and reports. However, what if you need advanced visibility to assess the performance of your sales pipeline? You would need a more robust solution in such cases. Power BI lets you create highly interactive reports and dashboards. In this guide, we’ll explore the different ways to connect HubSpot to Power BI to help you decide on a solution that fits your specific needs.
What are the options to connect HubSpot to Power BI?
The manual export as CSV method allows users to export data from HubSpot and then import it into Power BI. It’s the most straightforward way to move data; however, it’s prone to causing errors and can be time-consuming. Now, here’s a brief overview of each option for connecting HubSpot to Power BI:
- Certified apps: Among the many apps in the HubSpot Marketplace, Datawarehouse.io is a HubSpot-certified app to run the HubSpot Power BI integration.
- Coupler.io: It is a reporting automation solution to automate data flows and optimize reporting.
- ODBC Driver – The HubSpot ODBC driver provides you with the ability to connect live data directly from various applications.
Moving ahead, let’s take a look at the steps to connect HubSpot to Power BI using each of these methods. However, we’ll not be covering the ODBC driver. If you are interested in it, then check out what the installation flow looks like in our tutorial on Mailchimp to Power BI connection.
Connect Power BI to HubSpot using Coupler.io
Here are the steps to connect Power BI to HubSpot using Coupler.io in just a few minutes:
Click on Proceed in the form below where we’ve preselected HubSpot as a source and Power BI as a destination. You’ll be offered to create a Coupler.io account for free.
Step 1: Extract data
- To connect your HubSpot account, click on Connect.
- Sign into your HubSpot account and select it on the redirected page. Select your Source HubSpot account, and click on Continue.
- Select your Data entity under Basic settings, such as Contacts or Deals or any other entity. You can pick any data entity from HubSpot depending on the datasets you need to fetch. Let’s say you want to create a customized report on deals won or lost. In that case, you can select the Deals data entity.
- You can apply Date filters to your importer at this stage. Suppose you want to extract deals that were created after a certain date, for example, 2023-09-16. You would select that date. The importer will only extract deals created after September 16, 2023. Similarly, you can apply other Date filters, too.
- Advanced filters are used to fine-tune and refine your data import requirements. For instance, if you’d like to import deals that are at a specific Stage (such as Closed Won) in HubSpot, Advanced filters can be used for that. Read our guide on How to use HubSpot advanced filters?
Step 2: Transform data
- At this stage, your data will be loaded for preview. Coupler.io gives you the ability to transform data before loading it into Power BI. There are four options at the top pane to transform data, and they are:
- Column Management: Hide, rename, and show grouped or ungrouped data.
- Filters: Apply filters as per your specific needs.
- Sort: Sort your data as required.
- Formula: Add new columns with the help of formulas.
The opportunity to preview and manage your data at this step is very useful for the HubSpot analytics process.
Step 3: Manage data
- Proceed and copy the Integration URL from Coupler.io.
- Once copied, scroll to the bottom and click on Finish and Proceed, and then click on Save and Run. Please ensure you ARE NOT missing out on this step, as the URL works only after this.
- Next, go to Power BI and click on Get Data, followed by clicking on Web.
- In the dialog box, paste the Integration URL link and click OK.
- Power BI will establish a connection with Coupler.io, and your HubSpot data will be loaded into the Power Query Editor.
- When you have your importer ready, Coupler.io also allows you to automate your data import. To do this, go back to Coupler.io, toggle the Automatic data refresh button, and set a time interval as to when you would like your data import to run automatically. This ensures you always work with the most updated data in Power BI.
Your data connection between HubSpot and Power BI is all set now!
Coupler.io supports many destinations for your choice, for example, you can connect HubSpot to BigQuery.
Let’s now talk about the pros and cons of using Coupler.io. As you just saw, this no-code method is quick and easy to set up. The functionality to transform data before loading it into Power BI makes it all easier, as you can cleanse and structure data in Coupler.io itself. You can also automate data import, as it allows you to schedule automatic data refreshes with just a click. It supports data importing for multiple other applications as well.
As for cons, pricing is one of them as Coupler.io is not free. However, you can start for free without providing your credit card details.
Can you connect HubSpot to Power BI using the API?
Recently, HubSpot has sunsetted API keys on grounds of security. It’s believed that Private Apps are more secure rather than API keys. Hence, the latest HubSpot rollout will not allow you to integrate HubSpot to Power BI using the HubSpot API key.
However, as we’ve already discussed other ways to connect Power BI to HubSpot, another way is to use certified apps by HubSpot. Datawarehouse.io is a middleware solution that allows users to integrate HubSpot databases into other BI tools such as Power BI, Tableau, Excel, Looker Studio, and many more.
Connect HubSpot to Power BI using Datawarehouse.io
For this integration, you’ll need to first create an account with Datawarehouse.io. Let’s have a look at the steps to do this below:
- Open Datawarehouse.io, and go to the Integration store.
- In the Integration store, you’ll find the HubSpot to Power BI Connector. There’s a 14-day trial period, but just a heads up – they will need your credit card details. Click on Select Options.
- Click on Get started. Another page to authorize HubSpot connection for authentication will be displayed. Click on Authorize.
- You’ll be prompted to choose your HubSpot account to establish a connection.
- The Datawarehouse.io app will request you to provide access to your HubSpot account. Select the checkbox to agree, and click on the Connect app button.
- Next is the Checkout Form, which includes all your personal information and billing details such as your credit card. Go ahead, fill this out, and click on Get Started.
- Once completed, you will receive account activation details in your email.
Note: It may take up to 24 hours to start using the connector.
And now, let’s look at the steps to import data from HubSpot to Power BI using Datawarehouse.io:
- Select the Get Data button in Power BI and then click on More.
- From the left-hand pane, click on Azure, and then click on Azure SQL database in the right-hand pane. Click on Connect.
- Enter the SQL Server database information. You must have received this via the activation email sent by Datawarehouse.io.
- Select the Import button for Data Connectivity mode, and click OK.
- Select Database from the left-hand pane, and enter your Username and Password received via the activation email. You must also ensure to select your database in the Select which levels to apply these settings to dropdown, and not just the server. Finally, click on Connect.
- Now, from the Navigator, you should be able to pull any data tables hosted on your SQL server.
- Once you’ve selected the checkboxes that you’re willing to create reports on, click on Load and start visualizing your data.
Let’s have a look at some of the pros and cons of using Datawarehouse.io now. One of the biggest pros it’s certified by HubSpot, and pretty easy to set up, too. It provides a 14-day free trial period, but you need to provide your card details for this. It’s a no-code method; however, there’s no built-in functionality to transform your data before loading. And they offer some free templates to use upon request.
How to move data from HubSpot to Power BI manually
This method does not allow you to connect HubSpot to Power BI but you can move data using it. First, you’ll need to export data from HubSpot in a CSV file. We described how you can do this in our HubSpot export guide. Then follow these steps:
- Go to Power BI, click on Get data, and then Text/CSV.
- Select the CSV file from your local machine, and click on Open.
- Once imported, you’ll see the data preview. Click on Load.
After loading the data, you should be able to see it in the Power Query Editor.
The downside of the native method is that it can be repetitive when you need to import multiple HubSpot data entities. And since you need to process a lot of data many times, this method isn’t scalable enough.
On the contrary, the good side is that when you need to review data, this method can prove to be helpful.
What can you do with HubSpot and Power BI integration?
Now, let’s see what makes this integration worthy. Among the numerous advantages that you can leverage with just one powerful integration, we have listed a few use cases here if you need any inspiration:
- Real-time reporting: With HubSpot and Power BI integration, when you set up scheduled data refreshes, your reports and dashboards are always up to date with the latest HubSpot data.
- Analyze marketing tactics: By visualizing key metrics such as click-through rates, bounce rates, or website traffic data, you can monitor the performance of your marketing strategies.
- Identify patterns: Thanks to some of Power BI’s analytical features, you can recognize certain aspects that might go unnoticed otherwise. For instance, if some of your deals aren’t making any progress at all, create an interactive report in Power BI to visualize data to identify the marketing tactics behind successful deals and simply replicate them.
- Monitor sales pipeline: A good way to keep track of the trajectory of your sales pipeline for each lead, Power BI dashboards can help you bifurcate the sales pipeline of all your leads, deals, and customers and identify bottlenecks. You can then work on areas of improvement.
Benefits of HubSpot Power BI integration
HubSpot Power BI integration can make your everyday business hours easy-going. Once integrated and automated, you can focus on innovation, creativity, and inventing data-driven solutions. Some of the most common benefits across all industries are:
Business intelligence: By combing through HubSpot data, Microsoft Power BI reports and dashboards allow you to gain insights on business processes that you can repurpose later.
Customized reports and dashboards: A not-so-unique but fundamental benefit when it comes to data analysis. Customized reports and dashboards give users the ability to visualize complex HubSpot data by simplifying it. This, in turn, helps in focusing on identifying and implementing advanced approaches for business improvement.
Share and collaborate: Easily share your reports and dashboards, including visualized and transformed data, with your peers, clients, and stakeholders. This is a constructive way to seek feedback, solutions, and ideas from them, which can further help identify and eliminate bottlenecks.
The best fit: which HubSpot Power BI integration should you choose?
There’s no right answer to this, as the choice you make will ultimately depend on your business needs. When choosing a Power BI connector, it’s important to weigh the pros and cons, especially your requirements. Since every option offers different features, the decision really depends on what features are most valuable to your business.
Finally, realize the power of integration to have insightful data-driven reports and dashboards, deeper analysis leading to powerful solutions, and a lot more. This is just the tip of the iceberg. HubSpot Power BI integration can prove to be useful for everybody, from data analysts to salespeople across all industries.
All you have to do is make a choice about which option you would pick to connect HubSpot to Power BI. So, choose wisely, and happy integration!
Automate data export with Coupler.io
Get started for free