How to set up a Data Pipeline for Pipedrive
Learn ways to set up a data pipeline for SaaS platforms like Pipedrive to help develop reporting and management dashboards.
Introduction
Pipedrive is one of many SaaS applications that can benefit from setting up a data pipeline for reporting and management dashboards. The process I am highlighting in this article is applicable to all other prominent SaaS applications too.
Problem
The native reporting solution, insights, and dashboards don't give you all the information you need. You can't wrangle data (like you can with tools like Google Data Studio and Tableau) to get the answers you want. Additionally, these native dashboards don't have interactivity as Tableau does.
If you dare to find insights, you have to do what I did.
- Export data from Pipedrive
- Visualize it either:
- in a spreadsheets program like Excel, Google Sheets, etc., or
- in a BI tool like Google Data Studio, Tableau, or PowerBI.But not without reinventing the wheel, which I have already done.
The exact process and tools that you choose are secondary. What's important is for you to know that something like this is possible and easily doable.
Expect a high RoI in terms of business insights and data-driven decision-making.
How does setting up a data pipeline for Pipedrive help?
If you'd like to see what the end result will look like, you should check the following two resources:
- A live Tableau dashboard that uses data from Pipedrive
- A tutorial that walks you through how to use Tableau
An essential part of visualizing data from Pipedrive in Tableau is setting up the data pipeline.
How to set up a data pipeline for Pipedrive?
Let's dive in.
Tableau doesn't connect directly with Pipedrive. Therefore, we have to explore other options that can help us do that. These options range from manually refreshing the data to setting up an automation to periodically refresh the data in Tableau.
Manually Downloading Data from Pipedrive
Downloading Deals data manually from Pipedrive is the easiest option. You just have to navigate to the Exports tab in Pipedrive. Currently, this page resides under Tools and apps.
Step 1: Simply click on Deals, and then CSV.
Step 2: Pipedrive will start creating a CSV for download.
Step 3: Once done, you can then click the Download button to download the CSV to your local computer.
Refreshing Data in Tableau Dashboards
Tableau Public (the free version of Tableau) automatically refreshes data once a day if the data is stored in Google Sheets.
Once the data is downloaded to your local machine, you can import it into the Google Sheets document by following the steps outlined below:
Step 1: Go to cell A1 in the Deals tab in the Google Sheets document holding this Deals data. Step 2: From the File menu in Google Sheets, click on Import. Under the Upload tab, navigate to the downloaded Deals data and upload it. Step 3: Once uploaded, you will see the following options. Select 'Replace data at selected cell'.
Step 4: Once done, your Tableau will be automatically refreshed with this data within 24 hours. Step 5: You can force immediate data refresh by logging into your Tableau Public account, and clicking the Request Update button.
Manually Download Data Version 2
Completing the steps in the above option may seem like a humongous task. But it is not. Once you get used to it, takes less than 5 minutes to refresh the data.
But we can bring it down to 3 minutes.
Enter Google Drive File Stream.
ℹ️ Note 1: The only change here is that the Tableau dashboard will be connecting to a CSV file stored in your Google Drive, and not a Google Sheets document in your Google Drive.
ℹ️ Note 2: Switching data sources in Tableau is easy, as long as the two data sources have identical column names.
⚠️ Note 3: Tableau Public connects to Google Sheets, but it doesn't connect to Google Drive. For Google Drive, you will have to purchase a Tableau Desktop license, which costs $70 per month.
Step 1: Download Google Drive File Stream.
Step 2: Add your Deals.csv file to a folder in your Google Drive.
Step 3: Switch the data source for your Tableau dashboards to this new source.
Now, every time you want to refresh the data, you only have to complete the first step in the previously described process. In the last step, simply overwrite the Deals.csv file in your Google Drive folder.
Flatly.io
Flatly.io (affiliate link) was one of the first tools I explored to automatically download the data from Pipedrive on a set schedule. However, I needed an option to export the data with 'pretty' column names, instead of system column names. I may have missed out on checking out a few other options, but you may give it a try.
With Flatly.io (affiliate link), you can set up an automation to download the data from Pipedrive at a pre-defined frequency. This data can be written to a Google Sheets document, or directly to a Google Drive as a CSV. You can choose the option based on which version of Tableau you are using.
Flatly's pricing plans are listed below.
Coupler.io
Coupler.io (affiliate link), a Railsware company, recently launched a similar offering.
Coupler offers a view from the other side of the transaction. Instead of writing data to a Google Sheets document or to a CSV in Google Drive, you can set up Coupler to read data from Pipedrive using a Google Sheets add-on.
Coupler's pricing plans are listed below.
Stitch
Stitch is another platform that allows periodical downloading of data from Pipedrive. However, you have to write this data to a cloud database like Google BigQuery or Amazon Redshift, PostgreSQL, Microsoft Azure. You could potentially be incurring additional charges to store the data in these cloud servers. Besides, you may need a person to help with setting all this up and running. Besides, you will definitely need a Tableau Desktop license if you opt for this option.
Stitch's pricing plans are listed below.
Summary
Of all the options that I have evaluated, Coupler.io seems to be the best option. You can connect the data downloaded using Coupler.io with Tableau Public (the free version of Tableau). At the same time, you can automate the data refresh in the Google Sheets document using Coupler.io's Google Sheets add-on.