Salesforce: custom funnel report

We'll build a simple funnel report listing for each month using the following metrics: number of new opportunities, new contacts, opportunities won and lost.

What do I get out of it?

We'll build a simple funnel report listing for each month using the following metrics: number of new opportunities, new contacts, won opportunities, lost opportunities and close, win and loss rate.

How does it work?

1.Import opportunities from Salesforce

The first step is to import your opportunities in a sheet

  • Create a sheet called "Salesforce opportunities"
  • Click on the import button
  • In the data source dropdown, choose your Salesforce account
  • In the "Table to import" dropdown, choose "Opportunity"
  • Choose the columns that you want to use: You'll need at least the "Opportunity ID" and "Created Date", and "Stage" columns
  • Click on Import

If you can't connect your salesforce account yet, you can use the following datasets in your onboarding database: salesforce_opportunities

2. Import contacts

Same drill for contacts this time

  • Create a sheet called "Salesforce contacts"
  • Import contacts
  • You'll need at least the following columns: "Contact ID", "Created Date"

3. Let's now start building the layout of our report

  • Create a new sheet (+ button at the bottom) called "Salesforce custom funnel report"
  • Vertically, in B4 to B6, write the months you want to see the data for, for example: 2021_2 ; 2021_1 ; 2020_12 ; 2020_11
  • Then write the metrics you want to follow in C3 to F3
  • # of opportunities
  • # of contacts
  • # of won opportunities
  • # of lost opportunities
  • Close rate
  • Win rate
  • Loss rate

4.Add a month column to opportunities

Before calculating our metrics, we need to add a calculated column to return the created month of each deal in a handy format: {year}_{monthnumer}

  • Go back to the sheet "Salesforce opportunities"
  • Place your cursor on the cell to the right of your table headers
  • Write the title of your column (in this case "Month") and press enter
  • Then enter your formula

5.Let's count the number of opportunities created per month

  • In the sheet "Salesforce custom funnel report", in C4, add the following formula: =COUNTIF('Salesforce opportunities'!#Month,B4)
  • Then copy paste or drag downwards

6.Let's do formulas for won and lost deals

  • For won opportunities, the formula will be: =COUNTIFS('Salesforce opportunities'!#'Stage',"Closed won",'Salesforce opportunities'!#Month,B4)
  • For lost deals: =COUNTIFS('Salesforce opportunities'!#'Stage',"Closed lost",'Salesforce opportunities'!#Month,B4)

7. Let's now compute the ratio win rate, loss rate and close rate

  • Win rate: =E4/C4
  • Loss rate: =F4/C4
  • Close rate: =H4+I4

8.Last step is to calculate the number of contacts

You know the drill, so we'll help you less 😈

  • In "Saleforce contacts", create a calculated column to return the month
  • Then, in "Salesforce custom funnel report", write a countif to calculate the number of contacts per month 

=YEAR(#'Created Date')&"_"&MONTH(#'Created Date')



X
Please wait...
Oops! Something went wrong while submitting the form.