Using Hubspot Formulas in Reports for Conversion Rates

[CWatson12]

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.

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])

[Akash_Vi_R]

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.

[CWatson12 · Accepted solution]

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!