Creating a Sales Pipeline Dashboard in Google Sheets

Are you ready to create a sales pipeline dashboard so you can track your sales performance easily?

If you’re like most sales managers, then you’re always concerned about your targets.

You want quick and easy access to sales pipeline reporting so you can keep a close eye on sales trends, conversion rates, or how your sales teams are performing to win new clients.

Sadly, it’s not always easy to perform a sales pipeline analysis.

This is especially true if you don’t have a streamlined and efficient method of pulling crucial sales data into one, easy-to-use platform.

That is why in this post, we are going to create a full-flash spreadsheet-based sales pipeline dashboard (or sales template) for tracking sales progress with Google sheets.

Why create a sales pipeline report in Google Sheets

Google Sheets is free and it is great for team collaboration.

The sales team members can contribute to the spreadsheet directly and their work can be traced very easily. 

It is also easy to retrieve data from multiple sources – specifically with add-ons.

How to import crucial data for your sales pipeline report using Coefficient

Coefficient.io is a dedicated add-on for Google Sheets that makes importing data a breeze.

You can import data from a variety of sources for analysis which includes simple spreadsheets or CSV files as well as databases present online and offline. 

Here are some data sources:

1. Salesforce
2. HubSpot
3. Google Analytics
4. MySQL
5. Redshift
6. PostgreSQL
7. Snowflake
8. Looker
9. Google Sheet
10. CSV

Installing the add-on is easy. Go to Add-ons on the menu bar in Google Sheets, search for “Coefficient: Salesforce & Database Data connector.”

The add-on comes with a dedicated dialogue box to select the source of data. For this example, we will choose Google Sheet as the data source.

For this case study, we used a file named Sales_Data. Connect the analysis sheet to the data source by importing Sales_Data.

Our data is present in Sheet1 of Sales_Data, we will import the data and apply filters if required. For this analysis, we have not applied any filters.

This is how the analysis sheet looks once data is imported.

We are now ready to build our sales pipeline report.

Preparing the sales pipeline report

Let’s start by answering the fundamental questions related to a Sales Pipeline:

Open and closed deals and their dollar values

1. What is the number of deals that are open or closed?
2. What is the dollar value attached to these deals? 

We used the formulas SUMIF(), SUMIFS(), COUNTIF(), and COUNTIFS() to find the number of open or closed deals and to know their respective dollar values. The final output is this table below:

We applied a drop-down to get an individual quarter or select all of the quarters. The number of open and closed deals and their dollar values are displayed in the table.

As you can see from the table above, we have 10 deals open for the current quarter and that will yield $53367. For the current quarter, we have earned $29191.

Thus we can see that 35.35% of the income has already been realized.

Open deals by Stage

The second obvious detail that everyone might be interested in is the breakdown of every deal by the stage they are in the pipeline.

The image below shows a detailed report of open deals data in one quarter. Again, we used conditional sum formulas for this report.

The table above was produced using the formulas SUMIFS() and COUNTIFS() with conditionals added through the IF() function.

After selecting a calendar quarter, the data was displayed in the table showing the breakdown of the deals and their dollar values.

Deals won and lost by segment

The table below shows the details of the deals that the company had won and lost for the selected period and the breakdown of deals by segment and their dollar values. 

From the table, we can see that there is a 50:50 ratio in deals won and lost. You can also notice that the biggest share of deals belongs to Sales (sales of goods) followed by Training that is provided to clients.

In the second row, the dollar values of the deals are shown. Using the dollar value for each service type, we would know the marginal value each deal can bring to the company.

For example, acquiring one client that needs “sales service” will add 33847/7 = $4835 to the company whereas Management Services will give $5632 and $5232. Thus, the management service is the service type that adds the most value to the company.

A similar analysis could be done for deals won and lost against any service type to find out which service type needs improvement.

Year over year analysis of sales

In the following table, we analyzed the total deals won and lost for every year and compared them.

The data above shows the won/lost ratio. This ratio took a dip in the year 2019 and then increased again in 2020. The last row shows that the win ratio in 2019 decreased by almost 50%, and it increased again by 46% in 2020. Still, the ratios reported in these two years are lower than the base year of 2018.

Salesperson performance

No target can be achieved without team effort; hence it is really important to see the contribution of each individual in the team. The following table presents the performance of each salesperson in terms of dollar value-added.

The table shows the sales figures for the four salespeople working in the company. The most successful salesperson is Scheyer Andra who brought the business worth $26,908 of sales, followed by Karpin Karan with $15,125.

Graphs

Graphs always add value to reports by presenting key facts in visual form.

The following graphs show the open or active deals versus the delivered or closed deals. A total of 64% are still open. This implies that services still need to be delivered. This number also represents outstanding payments.

The following graph is the visual presentation of won and lost projects in every quarter of three calendar years.

It also shows the variation in business volume over the years as the bars in the graph rise and fall in every quarter.

From the visual inspection, there is almost a 50:50 ratio in projects won and lost by the company. 

The graph above presents the data from the last table in a visual form showing that major sales come from Scheyer Andra and Karpin Karan.

Syncing and updating the data

Once the report is produced, it will need to be updated to keep the report fresh. This is generally done manually by importing and exporting data, but you can get the data in real-time without importing or exporting using Coefficient. Our add-on makes that easy by providing an option to automatically schedule updates.

You just need to set when the Sales Pipeline Dashboard has to be updated and as the pipeline data is updated, the dashboard will be, too.

Try our pre-built sales pipeline dashboard – launch in a single click in Google Sheets

If you want to bypass the manual work of building a sales pipeline dashboard, you can launch our free Sales Pipeline Analysis template (Salesforce / HubSpot) in a single click. Our Sales Pipeline Analysis template offers pre-built dashboards that visualize your sales pipeline in Google Sheets.

Now you can measure individual, team, and company-wide sales performance for all dashboards and reports. Simply toggle the drop down menu to your preference.

With the Sales Pipeline Analysis template, you can track pipeline creation with live data from your Salesforce or HubSpot CRM. Track the flow of SQLs in your pipeline, how lead generation initiatives are performing, pipeline health, and sales forecasting KPIs, without having to build dashboards from scratch.

Visualize monthly conversions, measure win rate, and compare revenue year-over-year with our pre-designed dashboards. Understand how individual reps and teams are performing, what they’re contributing to total revenue, and also isolate trends that might call for an adjustment of sales tactics.

Check out our monthly velocity dashboard to see how much revenue is passing through your pipeline on a monthly basis. This allows you to understand how fast your sales reps are converting leads. It’s a key indicator of pipeline health, and incorporates many fundamental sales KPIs — SQLs, average deal value, and sales cycle length.

The template also maintains a monthly report for many of the key sales pipeline metrics: pipe created, closed won, closed lost, win rate, revenue, cycle length, average deal value, and more. This gives you a bird’s eye view of how your reps, teams, and company are performing in all facets of the sales pipeline.

Our one-click Sales Pipeline Analysis template (Salesforce / HubSpot) is a great free option for sales professionals who do not have the time to build and maintain dashboards.

Conclusion

Creating sales pipeline reports is made easy using the Coefficient.io add-on in Google Sheets. As you have seen in the various analysis we have done — determining values corresponding to different stages, deals won and lost and their dollar values, year over year analysis of sales, time-series presentation of data, and year wise performance of salespeople — reports could be easily generated using the proper software.

These data analysis reports can be used to find loopholes in your sales plans so you can implement immediate remedial actions that can boost conversions. You can also use them to observe sales trends, conversion rates, or the performance of your sales teams.

In preparing this report, we used the spreadsheet formulas SUMIFS(), COUNTIFS(), SUMPRODUCT(), IF(). In order to have details of these formulas, please download the dashboard sheet. 

The analysis shown here can be taken as a starting point and can further be explored. You can use it to further study the performance of a salesperson in a specific sector, analyze over a quarter comparison of sales trends, and know why a sales lead was lost, and many more.

Try Coefficient for free today!