Solved

Creating a Renewal Rate % based on two fields from Opportunity Object

  • 9 August 2017
  • 9 replies
  • 152 views

Badge
We could use some assistance on a report we are trying to build.

The end goal here is to build a report that shows the CSMs their “Renewal Rate

%”. This is using a calculated field based on two fields (‘Amount Closed’ and

‘Available Amount’) from the Opportunity Object.









So, what we have tried so far, is to create a custom MDA object

and then a new rule to push data from the Opportunity object to this new custom

object. What we are having difficulties with is getting an accurate total

renewal rate % for each CSM. We are able to Sum the ‘Amount Closed’ and

‘Available Amount’ but not take those Sums and create a ‘Renewal Rate %’ for

each CSM (at the CSM level not each individual Account). We tried creating a

rule that pushed the Sum of ‘Amount Closed’ and ‘Available Amount’ to the new

custom MDA object, where we created a calculated field for the ‘Renewal Rate %’

using the data that was pushed from the rule. However, this does not sum it

into one value, it gives us a value per opportunity. Which wouldn’t be an

issue, since we can Sum in the Report Builder using the ‘By’ option set to the

CSM (which does give us an accurate Sum of ‘Amount Closed’ and ‘Available

Amount’). The issue with this method is that it also Sums the ‘Renewal Rate %’

which isn’t accurate, since it is just adding up the %’s from the calculated

field in the MDA, with no weighting option for how much more a contract was

renewed for. We also

have tried to create a calculated field in the Rule we were building but that

doesn’t give us the correct value either due to the formula options that are

available, doesn’t include the option for ‘A/B’.



Is there a way to calculate the Renewal Rate % total per CSM? Based on those two fields which we are able to push into the MDA.
icon

Best answer by jean.nairon 14 October 2020, 21:22

View original

9 replies

Badge +3
Manmeet - Have you tried using Bionic Rules?  Since you basically need two steps - one is to aggregate by CSM and create your data set - then you can do in "an in row calculation" either as part of the Bionic Rule or directly in your target object (Target Object - CSM, Amount Closed, Available Amount, Renewal Rate).  You probably also want to do this by quarter.
Badge
Thanks! I will give it a try with Bionic Rules. We have not used these here yet so I wasn't sure if they had the capabilities we needed. I will play around with bionic rules to see if it gets me to the correct Renewal Rate.
Userlevel 7
Badge +1
Hi Manmeet,  I recently started going through all of the pending questions . Was checking to see if this has been resolved .
Badge
Hi Sai,



Thanks for checking back. I was able to figure it out after some experimentation. All set here. Thanks!
Userlevel 7
Badge +1
Great to hear that,you are always welcome for any questions, problems and Ideas.

I am currently experiencing the same issue. I tried using bionic rules to load the aggregated ARR fields by CSM, however I also need this by fiscal quarter. There is currently no way to transform a date field into fiscal quarter via rules engine. We tried grouping by contract end date = last day of calendar month and then in reporting grouping by FQ, however in doing so we still run into the same issue mentioned above. The renewal rate % then takes the sum of the renewal rates for all 3 months in any given quarter.

Badge +1

@a_heller  - I’d be interested in hearing a solution for your problem as well. We’ve had similar questions come up from our team! 

Userlevel 6
Badge +4

@cnichols, @a_heller,

 

For calculating anything by fiscal quarters, you could create an MDA table for your fiscal calendar and use that as a filter in your rule queries. 

 

Alternatively, you could also create a new field in Salesforce on the Oppty object to calculate which quarter the oppty falls in. I’ve used formulas in the past similar to this in Salesforce: 

 

CASE(
MONTH(CloseDate),
1, "Q2-"+(TEXT(YEAR(CloseDate))),
2, "Q2-"+(TEXT(YEAR(CloseDate))),
3, "Q2-"+(TEXT(YEAR(CloseDate))),
4, "Q3-"+(TEXT(YEAR(CloseDate))),
5, "Q3-"+(TEXT(YEAR(CloseDate))),
6, "Q3-"+(TEXT(YEAR(CloseDate))),
7, "Q4-"+(TEXT(YEAR(CloseDate))),
8, "Q4-"+(TEXT(YEAR(CloseDate))),
9, "Q4-"+(TEXT(YEAR(CloseDate))),
10, "Q1-"+(TEXT(YEAR(CloseDate)+1)),
11, "Q1-"+(TEXT(YEAR(CloseDate)+1)),
12, "Q1-"+(TEXT(YEAR(CloseDate)+1)),
"Blank")

 

Once you have this field in Gainsight, you can leverage it for grouping data in your reports or rules. 

 

Hope this gives you a few more ideas on how to set this up. 

Badge +1

Thank you @jean.nairon - This will be most helpful for us. I’ll definitely get this implemented this week! 

Reply