How to integrate Pipedrive to Power BI in Easy steps

This article discusses some of the best ways to extract data from Pipedrive and connect with Power BI for analysis. Power BI is a powerful platform that enables organizations to collect data from multiple sources and share insights. However, Pipedrive is a CRM platform that helps in the management of long sales processes. Let’s discuss how to establish Power BI Pipedrive integration.

What is Power BI?

Power BI is a Microsoft Business Intelligence (BI) tool that allows organizations to analyze and visualize raw data and create interactive insights. It connects multiple apps, connectors, and software services together to convert their data sources, visualize them, and discover vital information to make data-driven decisions. 

In simple terms, Power BI enables organizations to collect data from different sources, visualize them, and share insights. You can create multiple graphs and charts to visualize these data and build interactive dashboards.

It is good for any organization as it provides streamlined publication, comprises distribution capabilities, and allows support to integrate with multiple Microsoft products and services.

Another feature that makes it a top choice is it provides real-time stream analytics, i.e., you can access different sources to fetch data and get real-time analytics for better business decisions.

Overall, Power BI is a great business tool that offers a secure connection to data sources in the cloud or on-premises, supports hybrid configurations, and helps build insightful reports. Power BI Data Gateway, Power BI Desktop, Power BI service, Power BI Mobile Apps, and Power BI Report Server are a few types of Power BI tools that one can use to generate actionable information.

Key Features of Power BI

Power BI is one of the popular Business Intelligence tools currently used by many enterprises for its excellent services and features. Here are a few key features of Power BI that make it a top choice in the market.

• Streamlined Analytics: Power BI supports stream analytics features that further aid businesses in timely decisions. Through stream analytics, the report developers can run real-time analytics on multiple streams of data collected from sensors, devices, social media sources, or other applications.
• Supports Multiple Data Sources: Another vital feature of Power BI tools is it supports multiple data sources, including CSV, SQL Server, Web files, Excel, PDF, XML, MySQL database, etc., to generate graphs and charts for interactive visualizations.
• Power BI Desktop: Using this free software, you can easily access data and create reports. In order to run or use Power BI Desktop, all you have to do is download or install the software. Also, you do not require any expertise or query skills to run and build a report.
• Quick Collaboration: Using the Power BI’s excellent features, one can easily collaborate and distribute their reports within the organization. Power BI fulfills both the self-service and organizational data analytics requirements on one screen. It supports semantic models, an open connectivity framework, and an application lifecycle management (ALM) toolkit that allow coworkers and teams to share reports, datasets, and workbooks quickly.
• Deep Insights: Power BI’s visualization, analytics, and reporting capabilities enable businesses to track the data regardless of its size/type and interpret it. It supports various custom Q&A buttons, graphs, and charts that further help in generating interactive and insightful reports for quick decisions.
• Easy Implementation Process: The process to implement Power BI tools across enterprises is a quick process. You hardly require any engineering abilities or technical knowledge to install the software for performing analysis and generating insightful reports. All you need is to develop an API key or plug it inside the software to implement the tool.

What is Pipedrive?

Pipedrive is an all-in-one CRM platform designed with an approach to selling more with minimum manual work. The CRM software is easy to use and hardly takes any time to install or run. Also, the platform allows syncing with other 300+ tools, including Xero, Asana, Kixie, Google, etc. 

With the help of this platform, users can prioritize their activities, monitor deals, and schedule calls and emails as per their requirements. It also supports other features like one-click contact data collection, task automation, customization, integration, data protection, etc.

Today, most small- and medium-sized businesses use Pipedrive to drive more sales, monitor deals, and manage the pipeline. Its simple user interface helps in better management of sales deals and streamlines every action to make a successful sale.

Get full visibility into the different Sales Pipelines using Pipedrive. It is a popular Cloud-based application that can be accessed from any location via web browser and mobile applications. There are more advantages of using Pipedrive, such as it improves customer retention and sales productivity, builds customer satisfaction, and allows execution of better forecasting and reports.

Now that you’re familiar with Pipedrive and PostgreSQL, let’s dive straight into the Power BI Pipedrive integration.

Power BI Pipedrive Integration: Easy Steps

Many companies support Power BI Pipedrive integration as it helps perform an in-depth analysis of Pipedrive data. Connecting Pipedrive with Power BI is not possible without coding. However, there are a few other ways through which you can replicate to the Data Warehouse and then try connecting with Power BI. Another method is to use Restful API for the same or build a script.

You can not integrate Power BI Pipedrive directly. You will need to extract your Pipedrive data via the APIs provided by Pipedrive, load those results into a PostgreSQL Database, and then load the data from PostgreSQL to Power BI.

Follow the below-mentioned steps to establish a Power BI Pipedrive integration.

• Step 1: Pipedrive to PostgreSQL
• Step 2: Load Data from PostgreSQL to Power BI

Step 1: Pipedrive to PostgreSQL

The first step of Power BI Pipedrive integration requires you to load data from Pipedrive to Power BI. To analyze all your data using Power BI, one must load the Pipedrive data to a PostgreSQL Database and then connect it with Power BI. Pipedrive provides a RESTful API that helps developers to reach the stored information in the platform.

First, you will need to create a schema in PostgreSQL so that the schema can ingest the data from Pipedrive. 

Note: This schema should have a combination of tables with appropriate data types ( Integer, Float, JSON, String, etc.) to house each of the Pipedrive datums.

Connect to your PostgreSQL shell and create a table using the following query:

CREATE TABLE activities (
activity_id Integer SERIAL, 
success BOOLEAN 
data JSON,
related_objects JSON,
additional_data JSON, 
created_at TIMESTAMP WITH TIMEZONE 
); 

Next, fire an API call to “GET/v1/activities/{user_id}” to get all “Activities” assigned to a particular user. You can use the PostgreSQL library called urllib2 to GET/POST data to URLs.

import urllib.parse
import urllib.request

"host" = "api.pipedrive.com" 
values = {"base_path" : "/v1", 
          "api_token" : "hg567JKNM_0ghTY67",
          "scheme" : "https" }

data = urllib.parse.urlencode(values)
data = data.encode('ascii') # data should be bytes
req = urllib.request.Request(host, data)
with urllib.request.urlopen(req) as response:
   result_json = response.read()

You will get an aggregate JSON object called result_json after running the above query.

Next, use the above JSON object to create a PostgreSQL JSON array:

jsonb_agg(result_json) 

Next, formulate a function that can insert multiple rows into PostgreSQL.

For example:

CREATE OR REPLACE FUNCTION insertdata(json  result_json) ) 
  RETURNS VOID AS 
$$ 
 INSERT into activities values ( 0, json_to_recordset(result_json) ) ; 
$$ 
LANGUAGE sql STRICT; 

In the above query, 0 is used to allow PostgreSQL to generate auto-increment (SERIAL) integers that will act as unique IDs for each record. 

You will also need to create tables for User/UserSettings/Teams/Subscriptions/Product/ProductSettings/Persons/PersonSettings/Organizations/OrganizationFields/OrganizationRelationships etc., as well as for Notes/Leads/Calls/Deals/Files/Billing etc. 

Next, make appropriate API calls to fetch the most recent data and populate these tables. 

After completing the above steps, you will also need to run this periodically, and update/insert any new data that has been recorded by Pipedrive since your last run. 

Most people would add timestamps to each data row being entered in PostgreSQL to mark pre-existing data, on every new run of the program. So, this was the first step of Power BI Pipedrive integration.

Step 2: Load Data from PostgreSQL to Power BI

The second part of Power BI Pipedrive integration requires you to load data from PostgreSQL to Power BI. You will need to configure both PostgreSQL and Power BI to connect PostgreSQL to Power BI. Follow the below-mentioned steps to accomplish the Power BI Pipedrive integration.

• Configure PostgreSQL
• Configure Power BI

Configure PostgreSQL

First, edit the PostgreSQL pg_hba.conf configuration file and allow remote connections to all Databases:

sudo nano path/to/your/file/pg_hba.conf

Change the following line:

# TYPE   DATABASE   user    ADDRESS        METHOD
host        all                     all      0.0.0.0/0          md5

Save and close the file, then edit the postgresql.conf configuration file:

sudo nano path/of/your/file/postgresql.conf

Disable the SSL by changing the following line:

ssl = false

Save and close the file, then restart the PostgreSQL service to apply the configuration changes:

sudo service postgresql restart

Configure Power BI

You’re almost there, here’s the last bit of Power BI Pipedrive integration. Open the Power BI Desktop tool. Click on “Get data” and choose the option “More“.

Next, select the “PostgreSQL database” under the database option and click on the “Connect“.

In the next screen, you will need to enter the PostgreSQL Server IP and the Database name.

Next, provide the user name and the password of the PostgreSQL and click on “Connect“.

In the “Navigator” dialog, select and load the table you want to work with. Click on the “OK” if Power BI warns you about encryption.

Now, what if you are using a tool such as Postman? Postman is a good tool that can hep you build APIs. 

Use this link to add the Pipedrive API collection to your Postman workspace.

Navigate to your personal API token in your Pipedrive personal profile.

Select the collection and the object you need in Postman. 

Add your personal API token in the right parameter field, the

the organizational Pipedrive URL, and add other parameters for the chosen object. Next, copy the URL address.

• In Microsoft BI, choose Get Data -> Web and paste the copied URL

Now you will have the populated data in the table ready for analytical tasks.

That’s it. The challenge with this method is that you need to keep all the details of coding. But, that also comes with flexibility to carry this out in your own terms. 

This brings us to the end of Power BI Pipedrive integration.

Set up Pipedrive data transfer to Power BI using ODBC driver

An ODBC driver is a desktop app that implements Microsoft’s standard Open Database Connectivity to access data sources via lower-level APIs. Microsoft Power BI uses an ODBC driver to retrieve data from Pipedrive.

First, copy your API token from your Pipedrive personal profile.

Step 1: Configure ODBC driver

You must go to the folder where the ODBC driver is installed and open the ConfigureODBC.exe file. Next, you must go to the Advanced tab to specify the company domain URL and insert the previously copied  API token. Then, test the connection to ensure everything works as it should.

Step 2: Connect to Power BI from Pipedrive with ODBC driver

Next, Open your Power BI desktop application, go to Get data->more, and then select ODBC. Next, choose CData Pipedrive Source from the dropdown.

Next, using the correct login details, log into your Pipedrive account.

Finally, when Power BI and Pipedrive are connected, the data schema folder will appear in the Navigator window. Select the desired tables for data preview and import.

Conclusion

The best practice to analyze Pipedrive data using Power BI is to convert data to a Data Warehouse platform and connect with Power BI for analysis. Using the Power BI platform, one can gain access to perform an in-depth analysis of Pipedrive data. Follow the above-listed steps to integrate Power BI Pipedrive.

You can explore Power BI Dashboard Examples .

This article introduced you to Power BI and Pipedrive and later took you through the Power BI Pipedrive integration. However, it’s easy to become lost in a blend of data from multiple sources. Imagine trying to make heads or tails of such data. This is where Hevo comes in.

FAQ on Pipedrive to Power BI

References

1. https://blog.coupler.io/pipedrive-to-power-bi/#Power_BI_Pipedrive_integration_using_API