Using Aggregate Nodes to Roll-up Metrics
One of the nodes we love in CRM Analytics is the Aggregate Node. Aggregates allow you to roll-up/summarize metrics from one object to another object. Aggregations can help answer questions such as How many opportunities exist per account? How much opportunity amount do we have per account? How many activities do we have per Opportunity or per Account?
Additionally, you can select which data will be grouped by, for example, we are summarizing Opportunity amount GROUPED by Account id, or summarizing Activities GROUPED by Opportunity id, or even go further.
Now, if you are an org with a Hierarchical Aggregation on your Accounts, there’s also Hierarchical Aggregation which is able to handle multi-level data. The way this works is will apply aggregation to any hierarchical data to sum values at each level of the relationship,
The aggregate node is certainly, so let’s go through 4 common use cases:
1) Aggregation of Opportunity data at Account level
Let’s assume we are doing Account level analysis but want to summarize Opportunity data or Activity data for an Account; for example I want to sum the total amount of Opportunity per account or total no. of Opportunities/account or count the total no. of Activities for an Account
A couple of considerations when using an Aggregate node:
We are changing the grain when using an aggregate node
If we aggregate Opportunity summary metrics we won’t see additional fields/details from Opportunity, if we want Opportunity detail, we should create a separate dataset using Opportunity as our grain
Let’s do an exercise:
1) Add your Account data and Opportunity data to a recipe
2) Create an aggregate node next to the Opportunity node
3) The fields we aggregate on will be no. of rows and grouped by Account id
Aggregate Node rolling up Opportunity Amount grouped per Account ID
4) We will now see the different aggregations per Accountid
5) Your input will be one row per opportunity, we are aggregating on the Accountid giving us one row per account
6) The last step is to join back your aggregate to your account making sure you keep your Account as your left grain and your aggregate as your right source:
Our Analysis is at an Account level so we need to make sure we maintain account as left grain
Below is a video describing the steps:
2) Hierarchical Aggregation
Now, let’s talk about a really common use case where we have an Account Hierarchy, with a Parent Account, Child Accounts and you need to aggregate Opportunities all the way up to the parent.
Or perhaps we have a role hierarchy and we want to roll salespeople data all the way up to a director.
The steps for this approach would be the following:
1) Input Opportunities and Accounts and create a Join using your Account as left grain and Opportunity as right grain
We are joining before the aggregate so we have Parent Account ID available as a field during our aggregate
2) Once you have created a join, then you can create an Aggregate node, and aggregate the metric you want, for example, Sum of Amount
3) The grouping in a hierarchical aggregation will be different since we will now group by Account ID and include the Parent ID column as well
As you see above, you have the option to roll-up a Percentage Column to apply a % split. The aggregated value is multiplied by the number in this column. For example, your records where you have multiple parents, you are able to break the percentages down.
4) We need to join this data back to Accounts (left side: Account), right side: Aggregation of the Opportunity data.
Join your aggregate back to the Account
5) Register your dataset and run your recipe
6) If you wish to verify your data in a lens, open your dataset, group by your Account Name and add your 2 aggregate measures to the lens. The result will be you’ll see all the opportunities as well as the entire amount under a parent account.
Once you open your dataset, you’ll be able to see the entire amount and count of rows under the Parent Account
Now, if you wish to see all the Accounts under the hierarchy with it’s corresponding records, I recommend you taking a look at this blog: Multi-level Account Hierarchy: Flatten, Aggregate, Join, Transform and Report!
Below is a video describing the steps:
3) Activity Roll-up by Opportunity or Account
This is similar to use case 1, except here we are using a transform node to flag the records that we care about then filter those records out and then aggregate them...there’s 2 potential solutions here
Solution A: We roll-up both Tasks and Events (Activities) into Opportunities
We know Salesforce combines all Activities into Tasks and Events, categorizing Emails, Calls, Meetings, etc.. into these 2 buckets.
1) Input Tasks and Events, and perhaps use an Append to group All activities.
NOTE: If you are not comfortable with using Append yet, you can leverage the Customer Insights templated app. One of the recipes is an Activities recipe which leverages Append. For more info check out this blog and trailhead: Blog: Want to start using CRM Analytics Recipes? Try a Template! and Trailhead: Customize the CRM Analytics Customer Insights App
2) Once we have ALL our activities we can aggregate our measures and group by the WhatId. Now remember the WhatId is the API name for the '“Related To” field on Tasks and Events. The WhatId can refer to different objects including Accounts, Opportunities, Campaigns, Cases or custom objects.
For our use case, we want to know all activities related to Opportunities to identify which Opportunities
Aggregate all activities related to an Opportunity or Account
3) We add the Opportunity data ad since our analysis is based off Opportunities, we will use this as the grain, making sure, Opportunities is on the left side and our Aggregate is on the right:
To join, we will use the Related to ID, from Activities to bring in the Related Opportunity record
4) Register your Dataset and explore in a lens.
If you see our dataset above, we appended both Tasks and Events to get an ActivityId and then aggregated this data
5) Now to verify your recipe, create a new lens, grouping by Opportunity name and add your aggregated measure to see the total no. of activities per Opportunity.
Below a video explaining the steps:
Solution B: Flag and filter specific Activity types
This use case is similar to 3. except we will try to summarize calls, emails and tasks.
1) We will start with tasks and split them into Calls, Emails and Events via a filter (if we need to create a flag to identify the different types of activities, we can create a transform node to flag the specific details.
2) Each aggregate node will now have different inputs, and should reflect the summaries you need. The reason why we need to split the tasks is if you try to aggregate the different activity types in one node it may replicate the numbers, so it’s better to filter them and then do 3 separate aggregates in that recipe.
Below a video explaining the steps:
As you can see, there are many use cases for Aggregate nodes. If you need additional help or resources see below some links:
RESOURCES:
