This release is packed with exciting enhancements, including:
- Send Emails from Cockpit CTAs without applying a Playbook
- Add up to 50 custom fields in Program Participant configuration
- New Adoption Explorer (limited release) for managing your usage data with powerful pre-built analytics
- Instantly create a formula field directly in Report Builder
- Sally the AI Bot is available in Gainsight widgets on Salesforce Account/Oppty/Case pages
Some great news there, esp. about being able to build a custom fields directly in Report Builder!
What I find rather disappointing though is that an ability to do aggregate calculations in Report Builder is still missing in this release. Drawing from our own experience, this means being bound to undergo very painful workarounds. Personally, I consider this to be, by far, the most crucial feature not supported by GS reporting.
Do you have any estimate on when aggregate calculations can join GS Admins' arsenal?
Can you share an example of the type of aggregate calculation you are trying to do? We do support some types of aggregations in reporting and maybe we can help you accomplish this with the current set of capabilities.
Thank you for you reply!
For instance, it'd come extermely handy when calculating net retention in our case. Let me give you an example with a dummy data set.
Consider two subscriptions:
subsription_id | value_original | value_renewal01 | €1000 | €002 | €1500 | €2000------------------------------------------------------------------------
01 | €1000 | €0
02 | €1500 | €2000
Then, in the visualisation, we'd like to calculate net retention % as:
100*SUM(value_renewal)/SUM(value_original) = 100*2000/2500 = 80%
Obviously, calculating this for each record and aggregating (via average) doesn't work.
Any ideas how to do this in the current set up with the original data set, i.e. with the ability to drill-down?
You can do this today with formula fields and column level aggregation.
Create the formula field as follows:
Set a columnn calculations aggregation in the field setup window:
The finished result is a report that shows the percent for each row, as well as an overall average:
And if you further want to analyze aggregations by cohort group (like by CSM for example) you can enable row grouping and you'll get a report that looks like this:
I very much appreciate that you looked into this.
I'm afraid this doesn't result in correct results though. The column aggregation simply averages the result percentages under each row, which is an incorrect (arithmetic) average to use. The correct one would be weighted average, or similarly
100*SUM(Open Upsell Opprtunity Est. ARR)/SUM(ARR)
So, I take it is not currently possibly otherwise than calculating this aggregation in a seprarte MDA?
That's a good callout. The current formula fields capabilities in reporting do not do a weighted average calculation if you want to aggregate percentages.
@rakesh - can you think of another potential solution here?
PS: Bionic Reporting's formula capabilities should be powerful enough to solve this.
Looking forward to hearing more about it, since – as mentioned – this is an essential feature for multiple use cases.
are their any updates for this problem so far ?