How Dirty Hands automated their custom reporting in Pipedrive

Time of the project: 1 month

Scope:

• Custom reporting script
• Integration between Excel and CRM system
• Reports' optimization

Effect: Custom reporting script completed

Choosing the right CRM is not the only problem our clients reach out to us to get some help with. We support our clients in general CRM implementations as well as in more advanced cases. One of the stories that can illustrate that is the one about custom reporting for Dirty Hands.

Having an efficient CRM in place is a great first step that can transform the way you work. However, when you explore all its functionalities you may find that the default configuration doesn’t always match your exact workflow. Customizations in reporting are one of the most influential adjustments you can apply to improve your CRM performance.

Every company has a specific set of needs when it comes to making informed decisions based on data. That’s why generating precise reports is crucial to optimize your business operations.

With SoftwareSupp consultants, you can not only implement and choose the right CRM, but you can seek help with more advanced issues that are too complicated to solve on your own.

“We need more detailed reports”

Dirty Hands is a family-owned company that helps grocery stores with retail execution, sales, data, and reporting. They live by the “of service” mentality and that’s how they build great relationships with their customers.

The Dirty Hands team has already used Pipedrive for some time when they got in touch with us. They had a reporting issue that they tried to solve on their own but the task seemed too advanced and time-consuming.

To make the best decisions, many businesses need specific reports and summaries. Sometimes, the options available in the CRM system by default are not detailed enough. That was the problem the Dirty Hands team was facing.

They needed efficient automation that would allow them to track specific product’s value on every stage of the sales funnel. In other words, they wanted to know the worth of all products (potentially) sold on every stage of the funnel. Dirty Hands needed a solution that would be reliable and cost-efficient.

By default, Pipedrive shows you the sum of all deals’ value on a specific stage of the funnel, It’s not possible to have a breakdown for each product separately. And that’s exactly what Dirty Hands needed to manage their business more effectively. The team also wanted to see this “product value per deal stage” report in Pipedrive Insights.

Let’s explore how we approached this configuration.

Custom reporting setup

As in Pipedrive it’s not possible to generate a report based on the product value itself, we needed to approach this task in a different way.

The main idea was to use Pipedrive’s custom fields. These new fields mirrored the value of the products attached to a deal.

This means that for every product, you’ll have an additional field that shows the exact same value. If “Product 1” costs $500, the custom field will also show $500.

It’s important to note that you can only use numerical custom fields for this purpose. Otherwise, you won’t be able to generate the report.

Having applied this solution, you can sum all custom fields up and generate the report in Pipedrive Insights.

But that was only the first stage of the project. The real challenge was to automate the process of updating the custom fields. Managing these fields manually wasn’t an option as the reporting process needs to be efficient and accurate. With manual updates, there are simply too many possibilities for human errors and inconsistencies.

Let’s automate the process

The real challenge was to automate the process of updating manual fields so that the user would be served with up-to-date, reliable data in Pipedrive.

We created an automation flow using 3 technologies:

• Pipedrive’s webhooks
• Zapier
• Google Sheets

The goal was to create an automation that would update the custom field value anytime the deal is changed. We needed to:

1. identify if the deal was changed
2. see if the changes affected the products
3. if so, apply the changes in Pipedrive

The automation flow looked like this:

First, we used the deal.updated webhook in Pipedrive to get the deal’s ID into a Google Sheet.

This webhook caused the code embedded in a Google Sheet to run. This piece of App Script code had a goal to get a specific set of data from Pipedrive. Once the process was over, we had the timestamp and the deal ID listed in the spreadsheet.

The Google Sheet is deployed as a web application. It makes it possible for the Pipedrive webhook to trigger the code upon the deal update. Then, the script fills in the deal ID in the Google spreadsheet.

Once the deal ID is already in the spreadsheet, Zapier is being launched. In Zapier, we have a Python code set up that downloads the relevant field values from the deal. We connect with the client’s Pipedrive database to get all the info we need.

This code also compares the entries and identifies if the product value field was updated.

The next part of the automation funnel only runs if the product value has changed. The last “PUT” stage uses a Pipedrive-Zapier trigger called “deal updated” to finally update the custom field with the correct values and make the automated reporting possible.

We also created a separate automation funnel to make sure that this sequence would also work for new deals that are created in Pipedrive.

When a deal is created, its custom fields are empty by default. This zap updates all custom fields with the “0” value upon the deal creation. Thanks to this additional funnel, the entire process can run smoothly and without any manual work on the team’s end.

It was crucial to make sure to optimize the process and make it cost-effective for the client. The automation uses only 3 zaps in the first funnel and 1 zap in the second one.

To ensure the highest quality of the automation, we used unit-testing to check if the data is flowing smoothly at every stage of the process.

All reports available in Pipedrive

Thanks to this automation, the Dirty Hands team can always access real-time product value reports in Pipedrive Insights. They can easily see what is the value of a particular product on every stage of the funnel. They can also identify which products in their offer perform best.

The automation allowed them to see more detailed product information that otherwise wouldn’t be available in Pipedrive.

Even though the Dirty Hands team is quite advanced at using Pipedrive as a reporting tool, they chose to cooperate with the CRM experts. It let them speed up the automation process and save the team members’ time.

At SoftwareSupp, we specialize in solving difficult CRM cases and we really creating custom solutions for our clients. An atypical request always creates an interesting challenge for us that we’re ready to take up.