Using Hubspot Formulas in Reports for Conversion Rates

Data Hub đź’¬ RevOps Discussions
1

Hello,
I am trying to calculate conversion rates for MQL:SAL and MQL:STG2. I have a custom object that creates a record for every MQL and then stamps known SAL date and STG2 dates if the deal is created within 90 days of said conversion.
I want to only count SALs or STG2 that were created AFTER the MQL date (I do have a proptery that says b4 SAL or B4 STG2). The thing I am trying to figure out is that the custom report should look at the total count of MQLs in a given tiemframe (easy enought to set up).

However, for SAL/STG2 I only want the distinct count to sum IF there is a Conversion:Deal of “B4 Deal Create”.

I am struggling to figure out how to run the formula. My current set up has the report excluding MQLs that never have a deal created or STG2 created so everything is 100% converted.
Here is the current report filter with a circle on what I need to be part of the SAL or STG2 count If scenario. It’s possible I may need to run datasets for something like this.

CWatson12_0-1742324092832.png
CWatson12_0-1742324092832.png293Ă—453 42.9 KB

And these are my current formulas (the third is just dividing these two counts):

MQL count: DISTINCT_COUNT([p539405_marketing_conversion.hs_createdate])

STG2 count: DISTINCT_COUNT([p539405_marketing_conversion.deal_stg2_date])

2

Just kidding, I realized I could make 2 new properties 1 to stamp yes if conversion is 90 days before deal create, and 1 for stg2 and those those to count for the SAL number, then drop the extra filters on the report itself!

3

This is a Hubspot limitation.
If you wanna go crazy with formulas (or have even more complex ones), try pushing data to a spreadsheet (Excel/G-Sheets) and use spreadsheets’ functionalities.
You can potentially build crazy dashboards as well.
There are a bunch of connectors - Superjoin is my fave. Works well 100% of the time.