Account Discovery with Cohort Benchmarking
Account Discovery is a new CRM Analytics template in the Spring 23’ release available to Revenue Intelligence customers. Account Discovery is just one feature of Revenue Intelligence for Sales Cloud.
As a Sales Leader or Sales Rep, you want to quickly identify, and prioritize at-risk accounts, and understand why so that you can take the necessary steps to mitigate the risk. At the same time, you want to continue giving the needed attention to accounts that have an upside. This is where Account Discovery comes in.
One unique feature of Account Discovery is cohort benchmarking. If this is a new concept to you, that’s okay! We’ll dive into it. Using the Cluster Smart Transformation, Account Discovery analyzes measures such as: number of deals closed, deals open, win rate, activities, products, and many more, and assigns each account to a cluster – based on commonalities found in the measures. Then, each account is benchmarked against the cohort (cluster) average for the six KPIs below.
In this example, the selected account has a 66% win rate which is 34% higher compared to the cluster average. Account Discovery is an out-of-the-box template, which you can explore, but we’re going to focus on customizing this dashboard with your own benchmark metric. Naturally, the next question you might be wondering is – how do I do this? If so, you are in the right place!
Getting Started
Before we get into that, let’s discuss why you would want to add your own benchmark. Suppose the 6 metrics above are a great start for your organization, but you want to add more to see how your accounts compare with the metrics that you choose.
First, we need to identify what field we want to add to the benchmark. In this example, we will be using Annual Revenue, a standard field on the Account object.
Next, open the Account Discovery recipe (there’s only one). At a high level, this recipe aggregates data across opportunities, activities, products, and contacts then puts them all at granularity of accounts. Now, side-scroll to the end when you see the collection of nodes below.
Start by creating a new aggregate from the node called Update Labels, add formulas, clean columns, & cluster. We will call this new aggregate node “Cohort Annual Revenue Avg”.
Under Aggregates, select the + symbol and choose Average as the function, then select the Annual Revenue field. Then, group the rows by the Account Cluster field. Your aggregate node should like this:
Updating the Dataset
Now that we have the Annual Revenue average for each account cluster, we want to write that back to the dataset and compare each individual account to the cohort average.
To do this, we need to connect the new Cohort Annual Revenue Avg node using a join.
In the join, select the Account Cluster field for the keys and label the prefix something like “Cohort_AnnualRevenue”. Then, hit Apply.
The Cohort Benchmark Formula
Finally, we need to update the Cohort Benchmarking node to include our new Annual Revenue cluster average comparison.
When you open this transform node, it looks like there is a lot going on. But, let’s break it down. There are two formulas we need to add – The percent difference calculation and the text color value.
Create a new custom formula and call it “Cohort Annual Revenue Diff”. If you used Cohort_AnnualRevenue as your prefix label from the previous join, then you can copy and paste this formula:
concat(round(((AnnualRevenue - "Cohort_AnnualRevenue.AVG_AnnualRevenue") / "Cohort_AnnualRevenue.AVG_AnnualRevenue") * 100,0), '%', ' ',
case when AnnualRevenue > "Cohort_AnnualRevenue.AVG_AnnualRevenue" then '↑' when AnnualRevenue < "Cohort_AnnualRevenue.AVG_AnnualRevenue" then '↓' else '–' end)Set the output type is Text and the default value as 0% –.
The Column Label can be “Cohort AnnualRevenue Diff” and the API Name can be “Cohort_AnnualRevenue_Diff”.
This first formula compares the AnnualRevenue for each account against the cohort (cluster) average as a percent difference. The second formula then assigns a ↑ or ↓ symbol depending if it’s higher or lower than the average. These two values are concatenated together which gives us the format XX% ↑
Now we want to assign a green or red color to the value based on the percent difference. This is where the second formula comes in.
Create a new custom formula and call it “Cohort Annual Revenue Color” If you used Cohort_AnnualRevenue as your prefix label from the previous join, then you can copy and paste this formula:
case when ((AnnualRevenue - "Cohort_AnnualRevenue.AVG_AnnualRevenue") / "Cohort_AnnualRevenue.AVG_AnnualRevenue") < 0 then '#e74340' when ((AnnualRevenue - "Cohort_AnnualRevenue.AVG_AnnualRevenue") / "Cohort_AnnualRevenue.AVG_AnnualRevenue") > 0 then '#2e844a' else '#514f4d' endSet the output type is Text.
The Column Label can be “Cohort AnnualRevenue Color” and the API Name can be “Cohort_AnnualRevenue_Color”.
This second formula simply assigns the color green for being above average or the color red if it’s below the average.
Then, save and run the recipe.
Adding Annual Revenue to the Dashboard
Now that we have the Annual Revenue, Cohort AnnualRevenue Diff, and Cohort AnnualRevenue Color fields in the dataset, we are ready to add them to the dashboard!
Open the dashboard in Analytics Studio and go into edit mode. For this example, we’re going to replace the YTD Spend column with our new Annual Revenue benchmark so that everything fits nicely still.
So, first we will rename the YTD Spend text widget to Annual Revenue.
Next, we need to update the main query, All Accounts, with the Annual Revenue fields. Navigate to the list of queries and hit the drop-down arrow to edit. Alternatively, you click just double-click the main repeater widget to enter edit mode.
One of the nice things about the Account Discovery dashboard is there are no interactions (bindings) or SAQL. This makes it very easy to edit declaratively.
Add the Annual Revenue field as a column, and since we are replacing the YTD Spend field, find the field called Cohort YTD Spend Diff in the Group By section of the compare table and update it to Cohort AnnualRevenue Diff.
Next, we need to add the Cohort AnnualRevenue Color field to this compare table and update the conditional formatting for Cohort AnnualRevenue Diff to reference it.
At the time of writing this, the limit for the number of dimensions you can group by in a compare table is 10. But, you can add more by hiding the additional columns.
If we scroll down further in the compare table, you will see a few of these hidden dimensions that are included in the groupings. Find the Cohort YTD Spend Color field and select Show.
Now that the Cohort YTD Spend Color field is visible, we can select it to update the grouping to our new Cohort AnnualRevenue Color field.
Tip: If you want to keep the compare table tidy, once you update this field go ahead and hide it by hitting the dropdown arrow and selecting Hide.
Next, let’s update the conditional formatting for the new Cohort AnnualRevenue Diff field to reference the Cohort AnnualRevenue Color field. Select the paint roller icon to open formatting options.
Navigate to the column tab and choose Cohort AnnnualRevenue Diff and expand the Conditional Formatting options. Select Add Rule.
For the reference column, pick Cohort AnnualRevenue Color and for the coloring method choose Use Color in Reference Column. What this does is apply a text color based on the direction of the Cohort Annual Revenue difference. If it’s above the average, the text will be green. If it’s below the average, the text will be red.
Hit Apply and then Update at the bottom of the compare table to save your changes.
At this point, we’re almost done! The last thing we need to do is add our new fields to the dashboard in the main repeater widget.
Select the main repeater widget and then choose Edit Repeater Content. Find the text widget that has the YTD Spend and Cohort YTD Total Spend fields.
Delete the text there and select Add Query Data. Choose Annual Revenue under Dynamic Text Field, check the shorten number box, and hit Done.
Then we will add the cohort benchmark field underneath the Annual Revenue field. Select Add Query Data again and choose Cohort AnnualRevenue Diff then hit Done.
Finally, let’s make the formatting consistent with the rest of the text widgets in the dashboard. Highlight the Annual Revenue text line and set font size to 14. For the text color, set it as #181818.
Then, highlight the Cohort Annual Revenue text line and set the font size to 12. Since we are relying on conditional formatting to control the text color of this text, we will leave the text color as the default.
Hit Done in the upper right corner to see your changes and then save the dashboard.
That’s it! You should now see the Annual Revenue field with the cohort benchmark against the average beneath it for each row.
Written by
