Back to Blog

How to Build Sales Tracker with Google Sheets

A sales dashboard is a report with different data that demonstrates whether your business is going well. It contains different sales metrics visualized in an easy-to-read way. Modern sales management software, such as HubSpot or Pipedrive, provide this functionality out of the box. It’s quite handy to use built-in sales dashboards. However, they only contain specific metrics and have limited customization capabilities. That’s why those who are looking for custom reporting choose Google Sheets. And today we’ll show how you can build an interactive sales tracker with this tool.

Why is Google Sheets a great solution for a sales dashboard?

It’s customizable

Some people use only basic functions and they see spreadsheets as follows:

However, Google Sheets is a trove of formulas, charts, tables and other tools for custom reporting. You can do almost everything with this tool:

  • Gantt charts for product management:
  • Data monitors:

and many more use cases.

It’s calculation centered

Google Sheets function list is huge, but it allows you to make any manipulation with data. For example, the following formula calculates the number of deals sorted by two parameters (Year and Country):

=IF(
  AND(
    ISBLANK(B1),
    ISBLANK(D1)),
  COUNTA(Deals!A2:A),

 IF(
  ISBLANK(D1),
  COUNTA(
   Filter(Deals!A2:A,Deals!Z2:Z=B1)),

 IF(
  ISBLANK(B1),
  COUNTA(
   FILTER(Deals!A2:A,Deals!CN2:CN=D1)),
  COUNTA(
   FILTER(Deals!A2:A,Deals!Z2:Z=B1,Deals!CN2:CN=D1))))
)

There are also functions to import data from any of various structured data types, link data from other sheets and spreadsheets, return the maximum value selected from a database table-like array or range, and so on. 

It’s cloud based

You can access your sales tracker based on a spreadsheet from any place and device using your Google account. This allows you to be flexible in your activities.

It’s free

Small business owners are unwilling to scatter funds on advanced software. That’s why they primarily opt for low-budget solutions. Google Sheets is free, which makes it a titbit for entrepreneurs.

Why can building a sales report in Google Sheets be troublesome?

The main challenge associated with spreadsheets is that you have to build your sales dashboard manually. On one hand, this gives you customization freedom. On the other hand, tailoring formulas and formatting data may take a lot of effort. With that in mind, let’s build a ready-to-use sales dashboard, so you can save your time. What we’re going to begin with is the selection of metrics to put on.

Which metrics should I put on the dashboard? 

In our sales tracker, we’ll set up the following metrics:

  • Conversion rate – percentage of successful deals.
  • Average value per deal – how much money on average you earn per successful deal.
  • Average deal lifetime – how much time on average you spend on each successful deal.
  • Cumulative revenue – how much money you earned from the first successful deal until today.
  • Win ratio – percentage of won deals to all closed deals.

The metrics you can go with are mostly defined by your source data. For example, if your sales software lacks data about geolocation, you won’t be able to insert geo-based metrics in a spreadsheet. So, see what data you have and build your sales dashboard based on that. In our use case, we’ll pull data from Pipedrive. If you leverage HubSpot, Zoho, or another CRM tool, you can import data from there. 

How to import data from a CRM app to Google Sheets?

If you work with Pipedrive and HubSpot, you can use Coupler.io, a Google Sheets add-on, for data import. You need to cover three steps:

  • Install Coupler.io
  • Set up a necessary importer
  • Run the importer to pull your data to the spreadsheet

We’ve blogged about how to export data from Pipedrive to Google Sheets, so check it out if you want to learn how. HubSpot users can benefit from another step-by-step guide on how to integrate HubSpot with Google Sheets

In our use case, we picked the Pipedrive importer. We pulled the source data into a newly created sheet titled Deals. Now, we can get on to formulas and charts.

Building an interactive sales dashboard 

The sales dashboard template, which you can download and adjust for your project, consists of the following sections:

  • Geo chart with a conversion rate and total revenue of all countries
  • Total deals 
  • Total revenue, $
  • Average deal life-time, days
  • Deals (won, open, and lost)
  • Cumulative revenue, $
  • Win ratio
  • Lost deals vs. Won deals

Let’s check out what you need for each section.

Geo chart 

For the Geo chart, we need three columns: Country, Conversion rate, and Total revenue. You can fill in the Country column manually using Data Validation as follows:

Or you can apply the following formula:

={"Country name"; UNIQUE(Deals!Z2:Z)}

Conversion rate is the ratio of won deals to total deals. Since we’re calculating the conversion rate per country, we should use FILTER within COUNTIF and COUNTA functions. Here is the formula we used for the calculations:

=COUNTIF(
  Filter(Deals!$AL$2:$AL,Deals!$Z$2:$Z=A54),"won")/
 COUNTA(
  Filter(Deals!$AL$2:$AL,Deals!$Z$2:$Z=A54))
  • Deals!AL2:AL – the status column of the imported data
  • Deals!Z2:Z – the org_id.address column of the imported data
  • A61 – a cell with the country name

Read the FILTER Function Tutorial to learn more about filtering options in Google Sheets.

Now you can drag this formula to apply it for other countries. Alternatively, you can use a Copy Down shortcut: Ctrl+d. Learn more about Google Sheets shortcuts to optimize your workflow.

Total revenue is simply a sum of all won deals for a particular country. The calculation looks as follows:

=SUM(
  Filter(Deals!$AF$2:$AF,Deals!$Z$2:$Z=A54,Deals!$AL$2:$AL="won")
 )
  • Deals!AF2:AF – the value column of the imported data
  • Deals!AL2:AL – the status column of the imported data
  • Deals!Z2:Z – the org_id.address column of the imported data
  • A61 – a cell with the country name

Drag this formula as well.

Once you’ve set up data for each country, you can add a Geo chart as follows:

Sorting by country

We applied data validation (data range Deals!Z2:Z) to the A19 cell to enable sorting by country. Formulas for the rest of the metrics will contain the following functions:

Total deals, Total revenue, Average deal life-time

  • Formula for Total deals:
=IF(
  ISBLANK(A19),
  COUNTA(Deals!AL2:AL),
  COUNTA(
   Filter(Deals!AL2:AL,Deals!Z2:Z=A19))
)
  • Formula for Total revenue:
=IF(
  ISBLANK(A19),
  SUM(
   Filter(Deals!AF2:AF,Deals!AL2:AL="won")),
  SUM(
   Filter(Deals!AF2:AF,Deals!Z2:Z=A19,Deals!AL2:AL="won"))
)
  • Formula for Average deal life-time:

To calculate the average deal life-time, we need a value measuring how many days have been spent per deal (either won or lost). So, let’s create an additional column on the Deals sheet (CM). We used the MINUS function to calculate the number of days per deal and ARRAYFORMULA to fill in all the cells in the column:

=ARRAYFORMULA(
  IF(
   ISBLANK(AP2:AP),"",
   MINUS(AP2:AP,AH2:AH))
)
  • AP2:AP – the close_time column of the imported data 
  • AH2:AH – the add_time column of the imported data 

Now we can go back to the Dashboard sheet and calculate the average deal life-time, as follows:

=IF(
  ISBLANK(A19),
  IFERROR(
    AVERAGE(Deals!CM2:CM)),
  IFERROR(
    AVERAGE(
     Filter(Deals!CM2:CM,Deals!Z2:Z=A19))))
)
  • Deals!CM2:CM – the Days per deal column

Once the calculations are done, apply a Scorecard chart to each value separately. 

Deals (won, open, and lost)

Now, let’s figure out the number of deals by status: won, open, and lost. Here’s the formula with the use of the COUNTIF function:

=IF(
  ISBLANK(A19),
  COUNTIF(Deals!AL2:AL,"open"),
  COUNTIF(
   Filter(Deals!AL2:AL,Deals!Z2:Z=A19),"open")
)
  • Deals!AL2:AL – the status column of the imported data
  • Deals!Z2:Z – the org_id.address column of the imported data
  • A19 – a cell with the country name

The best way to visualize these values is to use a pie chart. Check out how they look on a 3D pie chart we made:

Cumulative revenue, $

This metric will show the cumulative revenue for each year. For example, the third year’s revenue will sum up the revenue in the first, second, and third years. For this, we’ll need to sort our deals by year. First, let’s create a separate column titled Year (CN) on the Deals sheet and apply two functions:

  • YEAR – to return the year specified by a given date
  • ARRAYFORMULA – to return the YEAR function to the entire column 

The formula looks as follows:

=ARRAYFORMULA(
   YEAR(AH2:AH)
)
  • AH2:AH – the add_time column of the imported data

Now, build a simple table and apply Cumulative revenue formulas for each year:

2016

=IF(
 ISBLANK(A19),
 SUM(
  IFERROR(
   Filter(Deals!AF2:AF,Deals!CN2:CN=2016,Deals!AL2:AL="won"))),
 SUM(
  IFERROR(
  Filter(Deals!AF2:AF,Deals!Z2:Z=A19,Deals!CN2:CN=2016,Deals!AL2:AL="won")))
)

2017

=IF(
 ISBLANK(A19),
 SUM(
  IFERROR(
   Filter(Deals!AF2:AF,(Deals!CN2:CN=2017)+(Deals!CN2:CN=2016),Deals!AL2:AL="won"))),
 SUM(
  IFERROR(
   Filter(Deals!AF2:AF,Deals!Z2:Z=A19,(Deals!CN2:CN=2017)+(Deals!CN2:CN=2016),Deals!AL2:AL="won")))
)

2018

=IF(
 ISBLANK(A19),
  SUM(
   IFERROR(
    Filter(Deals!AF2:AF,(Deals!CN2:CN=2018)+(Deals!CN2:CN=2017)+(Deals!CN2:CN=2016),Deals!AL2:AL="won"))),
  SUM(
   IFERROR(
    Filter(Deals!AF2:AF,Deals!Z2:Z=A19,(Deals!CN2:CN=2018)+(Deals!CN2:CN=2017)+(Deals!CN2:CN=2016),Deals!AL2:AL="won")))
)

2019

=IF(
 ISBLANK(A19),
 SUM(
  IFERROR(
   Filter(Deals!AF2:AF,(Deals!CN2:CN=2019)+(Deals!CN2:CN=2018)+(Deals!CN2:CN=2017)+(Deals!CN2:CN=2016),Deals!AL2:AL="won"))),
 SUM(
  IFERROR(
   Filter(Deals!AF2:AF,Deals!Z2:Z=A19,(Deals!CN2:CN=2019)+(Deals!CN2:CN=2018)+(Deals!CN2:CN=2017)+(Deals!CN2:CN=2016),Deals!AL2:AL="won")))
)
  • Deals!AF2:AF – the Value column of the imported data
  • Deals!CN2:CN – the Year column
  • Deals!AL2:AL – the status column of the imported data

Let’s insert a chart:

Lost deals vs. Won deals

There is nothing intricate about calculating both lost and won deals. Here’s how it looks:

=IF(
  ISBLANK(A19),
  COUNTIF(
   Filter(Deals!AL2:AL,Deals!CN2:CN=2016),"lost"),
  COUNTIF(
   Filter(Deals!AL2:AL,Deals!Z2:Z=A19,Deals!CN2:CN=2016),"lost")
)

Replace Deals!CN2:CN=2016 with the corresponding year. The formula for won deals is the same, but “lost” should be replaced with “won“.    

Let’s apply a column chart for this metric:

Win ratio

To define the win ratio, we need to divide won deals by closed deals (lost + won deals). You can use a simple or complex formula for this calculation:

Simple formula:

=E75/(D75+E75)
  • E75 – a cell with won deals in 2016
  • D75 – a cell with lost deals in 2016

Complex formula:

=IF(
  ISBLANK(A19),
  IFERROR(
    COUNTIF(
     Filter(Deals!AL2:AL,Deals!CN2:CN=2016),"won")/
    (COUNTIF(
      Filter(Deals!AL2:AL,Deals!CN2:CN=2016),"won") + 
    COUNTIF(
      Filter(Deals!AL2:AL,Deals!CN2:CN=2016),"lost")),
    "No closed deals"),
  IFERROR(
    COUNTIF(
      Filter(Deals!AL2:AL,Deals!Z2:Z=A19,Deals!CN2:CN=2016),"won")/
    (COUNTIF(
      Filter(Deals!AL2:AL,Deals!Z2:Z=A19,Deals!CN2:CN=2016),"won") +
    COUNTIF(
      Filter(Deals!AL2:AL,Deals!Z2:Z=A19,Deals!CN2:CN=2016),"lost")),
    "No closed deals")
)
  • Deals!CN2:CN – the Year column
  • Deals!AL2:AL – the status column of the imported data
  • Deals!Z2:Z – the org_id.address column of the imported data

Whichever formula you pick, calculate the win rate for each year and build a chart based on those values, as follows: 

Here it is! Your sales dashboard is almost done. Now you can apply formatting as you like and arrange charts in an order that’s comfortable for you and your team. Coupler.io also allows you to schedule the automatic data refresh. So, the dashboard will update on its own!

To wrap up: can I build a similar sales tracker with data from another CRM?

Sure you can! The dashboard is just a shell, which processes the raw data you feed in. If you go with HubSpot, SalesForce or anything else, make sure to import data relevant to your dashboard. Coupler.io will help you do that fast and easy. You will probably need to perform additional manipulations. But that’s what we love Google Sheets for – it always has a proper function to help you carry out any task. Good luck!

Back to Blog

Comments are closed.

Access your data
in a simple format for free!

Start Free