Conversion rate analysis

What do I get out of it?

Improve your deal conversion rate and ultimately your revenue

How does it work?

Let's calculate the conversion rate per country per industry per month. (You could do it on different dimensions, the methodology would be similar)

1) Import "Deals" from Hubspot in a sheet called "Hubspot deals" with the following columns: dealId, Company Id, Create Date

2) Import "Companies" from Hubspot in a sheet called "Hubspot companies" with the following columns: companyId, Industry

3) In Deals, add a calculated column to your table called Company Industry and add the following formula: =LOOKUP(#Company Id, Hubspot companies!#companyId, Hubspot companies!#Industry)

Now we know the industry each deal is associated to.

4) Add a calculated column called "Month" to return the month with the format "YYYY_MM" using the formula =YEAR(#Create Date)&"_"&MONTH(#Create Date)

5) Now create a third sheet called "Conversion rate analysis".

• In A1, write the name of one industry
• In B3, write "# of deals created"
• In B4, write "# of deals won"
• In B5, conversion rate
• In C2 to the right, write the months you want the data for with the format "YYYY_MM"

6) In C3, write the formula =COUNTIFS('Hubspot deals'!#Month, B2, 'Hubspot deals'!#Company Industry, $A$1) and paste this to the right

7) In C4, write the formula =COUNTIFS('Hubspot deals'!#Month, B2, 'Hubspot deals'!#Company Industry, $A$1, 'Hubspot deals'!#Deal Stage, "Closed won") and paste this to the right

8) Lastly, in C5, do the ratio =C4/C3 and paste to the right

Wow, you have an awesome dynamic dashboard. You just have to change the industry in the cell A1 if you want to see data for another industry 🤯