What do I get out of it?
Understanding your funnel is fundamental.
If you use Pipedrive 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 deals in a sheet
- Create a new sheet called "Pipedrive deals"
- Click on the import button
- In the data source dropdown, choose your Pipedrive account
- In the "Table to import" dropdown, choose "Deals"
- Choose the columns that you want to use: You'll need at least the "Id" and "Deal created", and "Stage Id" columns
- Click on the "Import" button
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}
- Place your cursor on the call to the right of your table headers
- Write the title of your column ("Month") and press enter
- Then enter your formula =YEAR(#'Deal created')&"_"&MONTH(#'Deal created')
3.Lookup the stage name
It'd be great to have the stage name instead of the stage id. To do that:
- Create a new sheet called "Pipedrive stages"
- Import the table "Stages" with at least the columns "Id" and "Name"
- Then in your sheet with the Deals, write a lookup function:
=LOOKUP(#'Stage Id', 'Pipedrive stages'!#Id, 'Pipedrive stages'!#Name)
4. Create a new sheet and build the layout of the dashboard
- Create a new sheet called "Pipedrive won / loss cohort analysis"
- Copy the unique values of your deal stage column (right click on the column and choose "Copy unique" option)
- Write the months that you'd like to see the data for, for example 2020_9 ; 2020_10 ; 2020_11
5. Write your countifs formula
Now you can write your countifs formula:
- # of lost deals: =COUNTIFS('Pipedrive deals'!#'Stage Name',"Closed lost",'Pipedrive deals'!#Month,C3)
- # of won deals: =COUNTIFS('Pipedrive deals'!#'Stage Name',"Closed won",'Pipedrive deals'!#Month,C3)
- Total # of deals: =COUNTIFS('Pipedrive deals'!#Month,C3)
Then, you just have to do the ratio for the percentages.
