Custom Sales Reporting for a Booking Agency

Time of the project: 1 month

Scope of the project:

• Setting up custom fields
• Custom reports in Google Sheets
• Integrating Google Sheets with CRM through custom API
  

Reporting is one of the most important tools that a CRM system offers. However, for many industries, built-in analytics aren’t sufficient to get the full picture of the sales process. In this case study, you’ll learn how we transferred data from Pipedrive to Google Sheets, created custom reports and automated commission calculations.

Project management with SoftwareSupp

When you start using a CRM, you typically need to verify your initial assumption about the workflow after the implementation. Further CRM optimization is a necessary process that maximizes the benefits of the software.

When you start seeing the benefits of using a CRM system, it’s possible that you’d like to use the newly acquired sales data to create more advanced reports. They help you gain more insights into your business. The agency asked us to prepare custom sales analytics for their team. This project required an expert with a specific set of skills in this area.

To make sure that the project is always run in the most effective way, SoftwareSupp assigns a dedicated project manager that takes care of the entire process. This means that if a certain task requires a consultant with a different set of skills, the project is never started from scratch. The project manager makes sure that everyone knows what the next steps are and figures out the timelines. All the most important details are passed on to ensure a smooth project transition from one expert to another.

For this project, we cooperated with Kamil who is an expert in app development, custom analytics, and software integrations. He completed many similar projects in the past that involved Pipedrive-Google Sheet integrations and custom script development.

Adding custom fields

Custom fields are an extremely useful feature for the booking agency. The reason is that booking businesses need a number of customer details that they use for reservations on behalf of their clients. Their offers are tailor-made, so no two trips are exactly the same.

With the help of Kamil, we implemented new custom fields in Pipedrive. These fields included the information necessary for an agent such as:

• currency
• transport
• destination
• hotel
• event/service
• reservation method
• payment method
• lead source
  

These fields were then also used for a more in-depth analysis of the client’s profile and revenues. It also allows the agency to prepare better offers for future clients and increase their margins. To further optimize the sales process, they needed more advanced reports that take into account the custom fields that they use in their CRM

Setting up custom reports in Google Sheets

Pipedrive doesn’t provide advanced reporting options when it comes to custom fields. The agency wanted to have monthly reports exported to Google Drive. 

Their main goals of the project were to:

• create a dashboard with a visual representation of custom fields
• be able to easily compare and verify sales goals
• get a tool that will make preparing reports for the stakeholders easier
• automate commission calculations
  

Our consultant created a Google Sheet document that presented data from Pipedrive and allowed for their further modifications. The sales team was able to choose a specific month and year and export all deals that were closed within this month.

Kamil used Pipedrive’s API to transfer information from Pipedrive to Google Sheets. He created tables that generated reports automatically and charts that displayed all the data in a more visual way.

With Pipedrive’s API, you can access the data from your Pipedrive account and use it in another application. Everything is secured by the access token that is unique to a user. You can find it in the “Personal preferences” tab in Pipedrive. The token ensures that all your data is safe and only you have access to the contents of your Pipedrive account.

To import the relevant data from Pipedrive and present it in Google Sheets, our expert created custom scripts within Google Sheet. In Google Workspace, you can create custom scripts using Apps Script. It’s a developing platform created by Google that is based on JavaScript. For G Suite integrations, it’s the easiest and fastest choice, as the development happens within the Google environment. To write your own script, you can use Google’s Script Editor. 

Our consultant created custom tables and charts that showed the sales in relation to the custom fields from Pipedrive. Thanks to this setup, the sales team was able to see which destinations bring in the most revenue and how it related to other factors.

Another important aspect was to estimate the profitability of the destinations. It was calculated using the revenue, margins, and costs. They could also check which airplane class is the most popular or brings the most profit. These in-depth reports allowed the team to get insights into that sales process that weren’t available by default in the CRM system.

These additional reports were also an important tool for the management that helped estimate their business performance as well as make sure that the sales representatives are on track with their targets. Thanks to multiple filtering options, it was possible to generate comparative reports for various factors. 

For every report generated, the client is able to export an .xlsx report and send it via email.

Our expert added a button in the Google Sheet menu that allows the client to send the report to a given email address.

Automated commission calculation

The proper automation setup can speed up tasks related to finances and accounting. For this booking agency, our expert created an automated commission calculator. As the deals have different currencies, Kamil used an external tool that provides a currency exchange rate for a specific date. This way, the team doesn’t need to follow currency exchange rates and check historical data.

To calculate the commission, it was crucial to make sure that all deals were assigned to the month in which the customer signed the contract. Normally, the close date is assigned automatically by Pipedrive when the deal is moved to a specific stage. However, in this case, the sales representatives wanted to have control over this field as the dates needed to be consistent with what is written in the contracts. We created a custom field and marked it as “required” in Pipedrive to make sure it’s always filled in.

To guarantee that the date is always provided, we created a pop-up window that requires the salesperson to fill this field in. This setup ensures accurate reporting and commission calculation.

The commission calculations aren’t as straightforward as they might seem in the booking business. The calculations need to take into account the revenue, costs, different currencies, and the commission rules set up by the company. Thanks to the custom scripts and reports that use the Pipedrive data, we were able to automate this process for the client.

Insights into the sales process

To adapt to the reality of international bookings, the sales analytics need to include various details about the clients. Data security is also important as the agency has access to clients’ personal information to be able to make book tickets, transfers, and hotels on their behalf.

To optimize the sales process, the team needs to take into account many factors that go beyond a simple cost-profit equation. There are many factors that contribute to the satisfaction of the client that are not easily measurable. However, with the right analytics in place, finding valuable insights is easier. With automated reporting, you gain more time to talk to your clients and hear their personal feedback about each and every booking they embark on.