Skip to main content
Full Circle Insights

Creating Custom Report Formulas Off Existing Reports

Conversion Rates between any two stages or milestones

Full Circle has standard reports that contain conversion rates between funnel stages i.e. MQL to SAL %.  However, there may be conversion rates that aren’t covered, for instance if you have multiple post opportunity funnel stages or if you have a custom milestone (ex POC), perhaps you may want to report on MQL to POC.  To do so, you can copy and adjust existing formulas in standard reports as follows:

1. Find and open the “Achieving: MQL Metrics” report from the Full Circle Dashboards Reports (MMF) Folder as below:

2. Open the report and click “Customize”.  You’ll notice the existing funnel conversion formulas that come standard “MQL to SAL”, “MQL to SQL” and “MQL to Won” conversions.  To create another conversion on a stage you’re tracking, i.e “MQL to SQO” edit one of the existing formulas as shown below:

The generic formula for conversion rate is:  
(Sum of conversion to stage) / (Sum of Conversion from stage)

If you’re trying to create a new formula for MQL to SQO, then you need to copy the below formula for MQL to SAL and replace the Sum of Conversion to Stage:

(CampaignMember.FCRM__FCR_SAR__c:SUM)/(CampaignMember.FCRM__FCR_QR__c:SUM)

The new formula would be like below: (make sure your field api name is correct)

(CampaignMember.CFCR_SQO__c:SUM)/(CampaignMember.FCRM__FCR_QR__c:SUM)

 

Now create a new report formula field and populate like below:

Average Velocity Rate between any two stages or milestones

There may also be velocity rates that aren’t covered between your funnel stages as well.  Generally, the standard reports only have the velocity between the next stage of the funnel (i.e. MQL to SAL).  You may want to report on the velocity between MQL and SQL for example or from MQL to a custom milestone (ex POC).  To do this, you need to follow the following steps.

1. If not there, create the desired velocity field on the campaign member.

a. Create a new formula field on campaign member of type “Number”
b. The formula should be:  Stage To Date - Stage From Date

i. For MQL to SQL:  FCRM__FCR_SQR_Date__c - FCRM__FCR_QR_Date__c
ii. See other example velocity fields for reference (i.e MQL to SAL)

2. Go to the standard report “Achieving: SAL Metrics” and customize the report

3. Edit the “MQL to SAL Velocity” formula as below:

The generic formula for this formula field is:
IF To Stage Sum > 0,  Velocity Field Sum / To Stage Sum

If we’re trying to create a formula for MQL to SQL velocity, then we need to update above formula as follows:


IF(CampaignMember.FCRM__FCR_SQR__c:SUM>0, CampaignMember.MQL_to_SQL__c:SUM/CampaignMember.FCRM__FCR_SQR__c:SUM, NULL)

 

 

  • Was this article helpful?