Report created in Power BI extracting data from Pipedrive CRM Software

If you are a Pipedrive system user, you must have already experienced the need to extract data to create presentations for the company’s executives or your manager or boss. It is common for those who work in the data collection area to hear phrases from clients such as: “the same thing every month”, “a lot of time”, “often depending on other people to obtain the data”, and others more.

Well, in this article we will speed up and facilitate your time in creating your presentation! For this, we will highlight a variety of reports obtained with the extraction of data from the Pipedrive CRM software and highlight some of the many presented in this BI.

First things first: Data

First, it is important to point out how we, from the BIX Tech team, have access to the data. All data is extracted through an open API (application programming interface).

We do some data processing and delete unused fields with the extracted data.

After extracting and transforming, we load the data into Power BI.

Then comes the beautiful part with visuals and colors that make your report look better and useful to anyone who uses it. Well, if a report isn’t useful, it’s no use being pretty, is it?

Enough of the ramblings and let’s show you what matters most.

Page Actual vs Budgeted in Power BI

Have you ever needed to check if a specific sales target was reached? Right below in Figure 1, we have a comparison of Actual vs Budgeted, which is the first page of our report.

Figure 1: Actual vs Budgeted page, Sales view.

The first highlight: with one click you can change from a sales analysis to a pipeline analysis. 

All visuals on the page will be adjusted (automatically filtered) to show pipeline results in the place where sales were previously presented, as shown in Figure 2.

Figure 2: Actual vs Budgeted page, trigger switch.

In this context, a good question arises: how do you have goal data if Pipedrive does not provide or make these fields available to be inserted?

And the answer is simple: with the Power BI tool, we can consolidate different data sources in the same report and best of all, we can report them so that they make sense in our reports. In this example, we use a .csv file to import the budget data, but it can come from a SQL database or an Excel file.

Figure 3: Actual vs Budgeted page, Pipeline view.

The second cool highlight is the tooltip on the graph over time.

When you hover the mouse over the visual in any month, a tooltip will open informing at the top of the values ​​of the accomplished, the goal, and the achieved percentage of the actual vs budget. And in the donut chart, the data for that month was broken down by Type of Sale, as shown in Figure 4.

Figure 4: Actual vs Budgeted page, tooltip.

The third highlight is in the detail. A button at the top of the clustered bar graph, Figure 5.

Figure 5: Actual vs Budgeted page, detail.

Clicking on it will take you to a detail page as shown in Figure 6. This breakdown can be used even with filters applied or not on the Actual vs Budgeted page.

This button takes you to a detailed table with customers, products, date of sale, and value information. Here, an important note is that the date of sale will be shown when the key is in sales, when the key is in pipelines it will show the date of creation of the deal.

Figure 6: Detail page Sales.

Through the side Menu, we can navigate between the pages of the report, so let’s navigate to the Deals Analysis report as shown in Figure 7.

Figure 7: Deals Analysis page.

As its title says, this report page shows the company’s deals data. Tracking how much we won from deals, how much we lost, and how much is still open.

The fourth highlight is, when we select a sales executive by clicking on its name, we end up filtering all report visuals with data from that sales executive, as seen in Figure 8.

Providing a quick analysis to the user, where he will know how much that executive lost in sales, how much he still has to sell, and how much he has sold!

Figure 8: Deals Analysis page, filtering the page with one click.
Figure 9: Deals Analysis page, detail.

Clicking on the “Detail by Products” button will take you to a detail page as shown in Figure 10. This breakdown can be used with filters applied or not to the page.

Figure 10: Detail Deals page | Product.

As the name itself says “Detail Deals | Product”, we can highlight the visual of the stacked column and line charts with Deals values ​​over time with a line informing the Win Rate percentage.

Now, what is Win Rate?

Our fifth highlight is the meaning of Win Rate, it is the percentage of valid deals closed in a specific period that were won.

In the table below, there is a breakdown of Deals by-products, allowing the user to discover the best-selling products in a given period, as well as lost or open ones.

Remember that Power BI allows the user to perform filters with a click on a row in a table, or a column in a column visual, and all the visuals on the page will adjust to the filter applied.

Still, on the Deals Detail page | Product, we have a button to further detail the Deals table by Products.

Figure 11: Detail Deals page | Product, button Detail by Product Share.

By clicking on it, we will be taken to an analysis of the share of the products, that is, a percentage that shows how much a sale of a single product represents of the total sales, total losses, or total open deals.

Figure 12: Share page | Product.
Figure 13: Customers Analysis page.

The sixth highlight is what I consider to be the most important in this report, the RFM classification.

What is the RFM classification?

The RFM classification provides a broad analysis of the status of the active customer portfolio, through a dashboard.

The idea is to know who the company makes the biggest sales to, how often these customers buy, and how many days have passed since their last order. This analysis brings several benefits to the management of the company, among them: helping to focus the company’s efforts on the most important customers. After all, the best customer is the one you can count on buying one or more times a month, and still placing high-value orders, right?

If you are interested in knowing more about the RFM classification, leave a comment on this article, and we might create another one further discussing this analysis.

Figure 14: Customers Analysis page, Detail Customers button.

As in the other pages, we always make it easy for the user with a quick and easy access detail using a button. On this page, the button is intuitive as it informs you that it is possible to detail a class of clients by selecting a class in the visual of classes of clients. After selecting it, the button will be enabled and then we just have to click on the button that has the following sentence: Detail Customers.

Figure 15: Detail page | Customers.

On this page, we inform the customer’s name, the R, F, and M score, the product bought, the quantity, the date of sale, and the value.

Figure 16: Open | Pipeline page.

The seventh highlight goes to the follow-up of your company’s open deals.

We were able to analyze by sales types, sales groups, sales executives, and sales funnel, whether in the graph or the table.

Through the side menu, we will navigate to the last page of this report in Power BI, Aging pipeline, as shown in Figure 17.

Figure 17: Aging | Pipeline page.

The last page of our report shows us all open deals over time, whether by quarter, by month, or by year. It is possible to generate insights for sales growth in the current month, or the next months or quarters, among other possible analyzes to be done with this information.

That’s it for today, I hope that after this article, it has become a little clearer about the capacity of the Power BI tool, and its applications.

Do you see possible scenarios on how to integrate these demos into your organization? Click here and let’s connect and continue the conversation.

Leave a Comment

Your email address will not be published. Required fields are marked *

Subscribe to our newsletter so you don't miss any content

 Get exclusive content on technology, data analytics, artificial intelligence, and many other topics that interest you for free in your inbox as soon as you sign up.