Salesforce: won / lost opportunities cohort analysis

Understanding your funnel is fundamental. If you use Salesforce as a CRM, with Actiondesk, you can easily run a cohort analysis and understand for each month, w

What do I get out of it?


Understanding your funnel is fundamental. If you use Salesforce as a CRM, with Actiondesk, you can easily run a cohort analysis and understand for each month, what was your close rate and loss rate.

How does it work?

1.The first step is to import your opportunities in a sheet

  • Create a new 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 "Opportunities"
  • Choose the columns that you want to use: You'll need at least the "Opportunity Id", "Created date", and "Stage" columns

2. Add a month column

The second step is to add a calculated column to return the created month of each deal in a handy format: {year}_{monthnumber}

  • Write the title of your new column at the right of the table headers to add an empty column: Call it "Month"
  • Then write the formula =YEAR(#'Created Date')&"_"&MONTH(#'Created Date')

3. Create a new sheet and build the layout of the dashboard

  • Create a new sheet called "Dashboard Salesforce"
  • In B4 to B8, fill in the following items:
  • of lost deals
  • won deals
  • Total deals
  • % lost
  • % won

  • In C3 to E3, write the months that you'd like to see the data for, for example: 2021_1, 2021_2, 2021_3

4. Write your countifs formula

Now you can write your countifs formula:

  • # of lost opportunities in C4: =COUNTIFS('Salesforce Opportunities'!#Stage,"Closed lost",'Salesforce Opportunities'!#Month,C3)
  • # of won opportunities in C5: =COUNTIFS('Salesforce Opportunities'!#Stage,"Closed won",'Salesforce Opportunities'!#Month,C3) 
  • Total # of opportunities C6: =COUNTIFS('Salesforce Opportunities'!#Month,C3)

For each of those, copy paste of drag the formula to the right

Then, you just have to do the ratio for the percentages.

Great work building this dashboard. You've used the 3 key features of Actiondesk: Calculated columns, lookup and aggregation formula



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