Pipedrive: calculate the number of deals per stage per month

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.


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.

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