header
Solved

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

  • 9 August 2017
  • 9 replies
  • 110 views

Userlevel 6
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

@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. 

View original

9 replies

Userlevel 7
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.
Userlevel 6
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
Hi Manmeet,  I recently started going through all of the pending questions . Was checking to see if this has been resolved .
Userlevel 6
Badge
Hi Sai,

Thanks for checking back. I was able to figure it out after some experimentation. All set here. Thanks!
Userlevel 7
Badge
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.

Userlevel 5
Badge

@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 7
Badge

@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. 

Userlevel 5
Badge

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

Reply