Blog

Sales Report Template: Hubspot To Google Sheets Dashboard

Jonathan Parisot
June 1, 2020
Jonathan Parisot

Article Breakdown


1. Why You’ll Love My Sales Report Template

> The 6 Marketing And Sales Questions Any HubSpot User Should Be Able to Answer

> Why Spreadsheets Rock

2. Sales Report Template [Google Sheet Download]

3. Sales Metrics Questions This Dashboard Will Give You An Answer To

> Funnel analysis

> Number of deals and amount per lifecycle stage

> Number of deals per month per stage

> Closed win deals vs objective per month per sales rep

> Number of deals per stage per sales rep

> Number of contacts by source per month

4. How To Update The Dashboards With Your Own Data

5. Behind The Scenes: How This Template Work


Why You’ll Love My Sales Report Template

The 6 Marketing And Sales Questions Any HubSpot User Should Be Able to Answer

HubSpot provides tons of information and it’s essential that you learn how to master it so that you can answer those keys questions for your business:

  1. How long does a deal take to close? 
  2. What’s the number of deals in each lifecycle stage?
  3. What’s the performance of each sales representative? 
  4. How are we performing versus objectives?
  5. Based on the current pipeline, what’s the projected revenue for the next few months?
  6. Where are leads coming from? How do they find us?


Why Spreadsheets Rock

You might have tried using a native sales dashboard included in HubSpot. But if you’re like me, you probably found it insufficient, clunky and hard to customize. That’s why I ended up building my own sales dashboard in a spreadsheet.


Personally, I’ve always preferred exporting my data in a spreadsheet because:

  1. It’s easy to customize the dashboard according to my exact needs and the specificities of my business.
  2. When I want to better understand a trend, it takes me a few clicks to see the underlying records and understand the root cause of the trend.
  3. I can easily see multiple records at a glance.
  4. I can share insights with my team in a convenient way.


I’m really happy with the sales dashboard I built from scratch, importing data from HubSpot to Google Sheets. I thought I might as well make it available to others that, I’m sure, would definitely love saving the hours I spent. You’ll tell me if you found it useful by sharing this article on Twitter or Linkedin 😉

Sales Report Template [Google Sheet Download]


The sales report template contains the following dashboards built on top of HubSpot data in Google Sheets: 

  • Funnel analysis
  • Number of deals and amount per lifecycle stage
  • Number of deals per month per stage
  • Amount per month per stage
  • Closed win deals vs objective per month per sales rep
  • Amount by sales rep per month
  • Amount by sales rep per stage
  • Number of deals per stage per sales rep
  • Contacts by source per month
  • Contacts by source as a % per month


You can just stop there and have a dive in the template or read on and see explanations on the metrics and how they can be useful to you:


DOWNLOAD SALES REPORT TEMPLATE


Sales Metrics Questions This Dashboard Will Give You An Answer To


a/ Funnel analysis


This dashboard calculates the following metrics for each of the past 12 months:

  • Number of contacts
  • Number of opportunities
  • Ratio Contacts / opportunities
  • Number of closed deals
  • Number of won deals
  • Number of lost deals
  • Close rate (%)
  • Win rate (%)
  • Loss rate (%)


Questions this dashboard gives you an answer to:

1. How is our overall pipeline evolving? 

> Over time, do we have more or less leads?

> Over time, do we have more or less opportunities?

2. How many deals have we won each month?

> Are we getting better or not?

3. If our pipeline is growing, is the win rate staying high or is it deteriorating?

> We might have more leads, but if our win rate is decreasing, it might mean we have more leads we actually don’t want (and who waste our sales representatives' time!)

b/ Number of deals and amount per lifecycle stage


Questions this dashboard gives you an answer to:

1. How is our current pipeline looking? 
2. Do we have enough deals in the pipeline to reach the objective of this month and the following months?

> If not, we need to do something about it: maybe run a campaign to increase the top of the funnel.

3. Based on the probability of each stage, how much revenue are we expected to make out of the current pipeline?
4. What’s the average deal value?

c/ Number of deals per month per stage



Questions this dashboard gives you an answer to:

1. How many deals are in each stage for each month?
2. Depending on the duration of your sales cycle, after a certain period of time, you shouldn’t have deals that are not closed

> Ex: if my sales cycle duration varies between 1 week and 2 months, after 2 months, I shouldn’t have any deals which are not closed. 

> If I do, I know there’s probably a problem that needs to be solved.

Similarly, you have the amount per month per stage:



d/ Closed win deals vs objective per month per sales rep




Questions this dashboard gives you an answer to:

1. How did this specific sales representative perform?
2. How is her/his performance evolving over time? 
3. How is s/he performing compared to her/his objectives?


Similarly, you have the amount per month per sales rep:



e/ Number of deals per stage per sales rep


Questions this dashboard gives you an answer to:

1. How is the current pipeline of each sales rep looking? 
2. Does each sales rep have enough deals in each stage to reach her/his objective of this month and the following months?

Similarly, you have the amount per stage per sales rep:



f/ Number of contacts by source per month


Questions this dashboard gives you an answer to:

1. Through which channel are our leads finding our product?
2. How is each channel’s performance evolving over time?

Another way to look at this data is as a percentage of each month:



How To Update The Dashboards With Your Own Data


  • In deals, erase the data in the range A2:H3000 and replace each column by your own data (by exporting it from HubSpot).
  • In contacts, erase the data in the range A2:210001 and replace each column by your own data (by exporting it from HubSpot).
  • In the tab “Instructions & inputs”, enter the 12 months for which you want to see your data.
  • If you use custom deal stages and not the HubSpot default ones, you'll have to replace the name of the stages.


Behind The Scenes: How This Template Works


  • Deals & Contacts tabs are raw data exported from HubSpot, the rest are calculated dashboards.
  • In Deals & Contacts, you have a calculated column that computes the creation month of each contact / deal. You don't need to touch this formula, it will apply automatically to your whole formula thanks to the arrayformula. You can read more about array formulas here, they’re very useful in Google Sheets.
  • In "Sales rep breakdown", the sales rep in the dropdown input comes from the range AD2:AD12, this list will be automatically modified with your own sales rep using the UNIQUE() formula.
  • In each dashboard, the list of months will use the input in the tab “Instructions & inputs”, sometimes using the TRANSPOSE() formula.


Conclusion



If you read until here, there’s probably no need to highlight to you how important it is to understand your sales data, the behavior of your lifecycle and to have a robust sales report.


One problem with this sales dashboard is that you still need to export data from HubSpot manually each time you want to update your dashboard with up-to-date data. 

Unfortunately, there’s no native integration between HubSpot and Google Sheets.


Good news though, there are ways to connect HubSpot to Google Sheets. If you want to read about solutions like Zapier, G-Accon, Supermetrics, Automate.io or Actiondesk, this article highlights the pros and cons of each product along with their pricing options 👉 Connect Hubspot to Google Sheets: 4 Best Connectors in 2020.



Oh, and if you think this whole thing is a painful process, feel free to check out our product Actiondesk, it's a spreadsheet software that lets you import and refresh data seamlessly from Hubspot, SQL, Stripe, Salesforce and more.

Share


Share On Twitter:

Share On Linkedin:


Suggested Article


Hubspot to Google Sheets: 4 Best Connectors in 2020



Request Access To Actiondesk