CRM Analytics: Categorize Data with Bucket Field

Last Updated on December 11, 2022 by Rakesh Gupta

Big Idea or Enduring Question:

• How do you categorize report records in CRM Analytics to make analysis easier? 

Objectives:

After reading this blog, you’ll be able to:

• Add a bucket field while working with Data Prep (Recipe) 
• Different ways to create bucketing in CRM Analytics 
• and much more

In the past written a few articles on CRM Analytics. Why not check them out while you are at it?!

1. How to Pass Tableau CRM & Einstein Discovery Consultant Exam
2. CRM Analytics: Replace Nulls With Specified Values

I recently worked on a small requirement and would like to share my knowledge. I had a dataset that contained transactional data of a small company. The data included columns like Transaction Id, Customer Name, Transaction Amount, Mode of Transaction, etc. I also received several analysis requests for which I had to build queries and create a dashboard.

Most of the analysis required bucketing of the data, i.e., segregating the records into various buckets based on particular conditions. For example, if the transaction amounts between 1 USD and Rs.50 USD, then one bucket, 51 USD to 500 USD, falls into another bucket, and so on.

Let’s start with business use cases and understand how to implement bucketing in CRM Analytics. 

Business Use case

Donna Serdula is a System administrator at Gurukul On Cloud (GoC). She got a new requirement to categorize the opportunity data based on the following conditions: 

1. If the Opportunity Amount is null, then categorize them as Not Started
2. If Amount >= 10000, then categories such records as Large
3. If Amount >=5000 and Amount < 10000, then categories such records as Medium
4. Otherwise, categorize them as Small

Automation Champion Approach (I-do):

Bucketing is a powerful feature that allows you to create a bucket field based on existing values in another field, to make analysis easier. For example, use a bucket field to categorize households as small, medium, or large, based on the household’s annual income. 

You can add a bucket to the following fields.

1. Measure
2. Dimension
   1. Use a bucket field to categorize opportunities Stages as either closed or open.
3. Date
   1. Use a bucket field to categorize opportunities according to the season they were created.

We will create a bucket field called Deal Size to categorize opportunity into three possible values: Small, Medium, and Large. For this, we will use Data Prep (Recipe). 

There are 8 steps to solve Donna’s business requirement using CRM Analytics Recipe. We must:

1. On the Data Manager app page, navigate to Data Manager | Recipes | New Recipes.
2. Click Add Input Data option, and select the check box next to the connected object Opportunity.
   1. In the Selected Columns area on the right, you can choose columns to include selected input data or all columns to automatically update every available column when the recipe syncs.
3. Click Next.
4. The next step is to bucket a measure field in a recipe. For this, we have to use the Transform node.
5. Hover over a node to select the Transform node.
   1. Click on the header of the Amount field, and then select Bucket. 
      1. Use Bucket settings in the Add Transformation panel to add ranges per the requirement.
      2. For Range1, enter a value and name for the first bucket. We will bucket rows with amounts less than 5000 as Small. Repeat the same steps for Range2 and Range3.
         
   2. Once you’re done with all the ranges, click Apply.
6. It’s time to save the recipe result to the first dataset.
   1. Click Add Node button from a single node and select Output.
   2. Enter the dataset name – OpportunityBucket, and other details.
7. Almost there! Once everything looks good, click the Save and Run button.
8. Click Save again.

Formative Assessment:

I want to hear from you!

What is one thing you learned from this post? How do you envision applying this new knowledge in the real world? Feel free to share in the comments below.