Calculate conversion from signup to deal signed across any dimension you'd like (country, industry, deal owner, etc)
Improve your deal conversion rate and ultimately your revenue
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".
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 🤯