Hayley Tuller and Jen Lee in our How I Solved This series.

How I Solved This: Build BANT Opportunity Scoring with Custom Metadata Types

By

Welcome to another “How I Solved This.” In this series, we do a deep dive into a specific business problem and share how one #AwesomeAdmin chose to solve it. Once you learn how they solved their specific problem, you’ll be inspired to try their solution yourself! Watch how Hayley Tuller built a BANT model with custom questions and answers using Flow and Custom Metadata Types, and then read all the details in the post below. 


Key business problem:

How do you translate a custom scoring model to Salesforce, but make it easy to maintain? A sales team transitioning to Salesforce already has a mature BANT scoring model for Opportunities they want to use in Salesforce, but they want to retain the flexibility to update the criteria of their model quickly and easily without reworking automations. 

Background:

BANT is an acronym that stands for Budget, Authority, Need, Timeline. It’s a schema for scoring and organizing opportunities to help ensure deals are properly qualified and sales teams get the most out of their time. The idea behind BANT is that certain key areas are most critical to making a sale, and efforts are best focused there. Specifically, if a prospective customer’s Budget aligns with the price, they have the Authority to make the purchase, have a clear Need, and the Timeline is right, a salesperson can be confident that the opportunity is likely to close. 

The challenge to creating a successful BANT model lies in how these categories are measured in a given industry or for a given product or service, which can vary wildly. Sales teams often develop sophisticated playbooks designed to capture key facts related to each category and award them a score relative to a total overall score. These scores are often at the heart of the firm’s sales strategy and, as such, are critical to reflect in Salesforce. 

A BANT question and answer set may look like this for the Budget category:

Q: What is the prospect’s opinion of the shared price?

      A1: The prospect feels the price is fair – 25 points

      A2: The prospect has not shared an opinion – 15 points

      A3: The prospect had a mild negative reaction to the price – 10 points

      A4: The prospect had a strong negative reaction to the price – 5 points

Of course, we all know that no such thing exists in Salesforce out of the box. But can we build it AND make it easy to maintain? CHALLENGE ACCEPTED! 🦸‍♀️

How I solved it:

Step 1: Create custom metadata types to store the questions, answers, and scores

If you’re new to custom metadata types (CMDTs), here’s your chance to jump in! Think of them like custom objects for admins. CMDTs let you create places to store business logic that can be referred to by both declarative configuration and code — and best of all, they’re considered part of the metadata. That means they’re copied to sandboxes and deployable in change sets. 

Start by creating a CMDT called “BANT Question.” You need three custom fields: one to track the question itself (I used a Text Area), one to track the question’s status (“Active” or “Retired”), and one to track the category (Budget, Authority, Need, or Timing, respectively). Here are my custom fields for BANT Question:

Custom fields for BANT Question.

Once you have a CMDT for the question, you need another type called “BANT Answers.” This type will have three fields as well. The first is the critical Metadata Relationship field that links the answer to its parent question. Don’t worry, that sounds hard, but it’s basically just a lookup field from one CMDT to another. Remember — these are just like custom objects for admins!

Once you have the Metadata Relationship field in place, now you just need a text field for your answer, and a number field for the score awarded to that answer. Here are the custom fields on my CMDT for BANT Answers:

Custom BANT answers.

Finally, add your BANT Questions and Answers as CMDT records. To do this, click the Manage Records button from the CMDT you want to work with first. In this case, I think the Questions are the better place to start, and then add the BANT Answers once you have your questions.

Add your BANT Questions and Answers as CMDT records.

You may also want to customize the BANT Questions detail section and related lists layout to make it easier to see your data as you go. Here’s what one of my finished BANT Question records looks like:

A finished BANT Question record.

Congrats! You’re now a CMDT hero!

Step 2: Create a custom object “BANT Scores” as a child of the opportunity

Now, we need to create a place for these questions and answers relative to a specific deal to be stored. We could do this with custom fields on the opportunity, but that would be a lot of fields and it would be difficult to update or change. Perhaps most critically, it would make it challenging to summarize scores. 

Instead, let’s make a custom object. I called this object “BANT Score,” and I gave it a master-detail lookup to the Opportunity, so it’s easy as pie 🥧 to summarize scores later. You’ll need a field to track the Question, one to track the answer, a picklist to track the category, and finally a number field to track the score this answer gets. I also added a Last Score Set field to help with auditing the solution and a Notes field to give sales reps a place to capture context.

Here’s my BANT Score object:

Hayley's BANT Score object.

Step 3: Automate the busy work and make data entry easy with Flow

A user could now create custom BANT Score records on their opportunity and manually enter in all that information — but we can do better than that. Remember, CMDTs are accessible from Flow. To save our sales reps from busy work, let’s create a flow that gets all the current BANT Questions from CMDT and uses them to create BANT Score records whenever an opportunity is created.  

To accomplish this, you’ll want to start with a record-triggered flow set to run after a record is created on the Opportunity object. We only want this to happen when a new opportunity is created, and we’ll use an after record save for our execution, like this:

Configuring a record-triggered flow.

Next, we’ll add a “Get Records” step to retrieve and store all the current BANT questions from Custom Metadata. Here’s how I set up my “Get BANT Questions” step:

How Hayley set up her "Get BANT Questions" step.

This will collect all the BANT Question Custom Metadata records with a status set to “Active.” This allows you to keep old BANT Questions if you want historical records, but only work with the active questions. We’re going to be dealing with a collection of records, so be sure to select All Records at the bottom under “How Many Records to Store.”

Now, you’ll want to loop through this collection, and create your “draft” BANT Scores. To do this, use a Loop element — here’s mine:

How to loop through a collection and create your "draft" BANT scores.

Inside the loop, we’ll use two Assignment elements to first create a “draft” BANT Score record, and then add your draft records to a collection so you can make them all at once. This is called “bulkifying” the flow and we do it this way to avoid creating new records inside a loop, which you should NEVER DO in Flow, for very good reasons

Bulkifying the Flow.

When you set the “draft” values, you can use the current metadata record in your loop for the values of your new “draft” Score record, like so:

Use the current metadata record in your loop for the values of your new “draft” Score record.

The variable “varDraftScore” is a record variable of the BANT Score object; once created, you can reference its individual fields and define a value for each. This is a little hard to read in the screenshot because the full values are truncated, but here’s how the fields line up:

varDraftScore.Opporutnity__c   =  $Record.Id

varDraftScore.Question_Number__c  = $Loop_Metadata.Label

varDraftScore.Question__c   = $Loop_Metadata.Question__c
varDraftScore.Category__c = $Loop_Metadata.Category__c

Finally, at the end, we create our shiny new BANT Scores on the new Opportunity. To do this,  we use a Create Records element:

Use a Create Records element to create our shiny new BANT Scores on the new Opportunity.

Huzzah! 🎉

Now that we’ve automated creating the BANT questions, let’s make it easy for our users to select the right answer to any question. To accomplish that, we’ll create another flow, and this time we’ll use a screen flow that we’ll deploy on the BANT Score record page. This flow will retrieve the correct answers from Custom Metadata and present them as a set of options from which the user can choose the best answer. 

Start your screen flow by performing a Get and storing the details of the current BANT Score record. You can do this by defining a variable for that record’s ID (recordId works great, but here I’ve used varBantScoreId to be more descriptive) and making it available for input. This way, we can pass a value into this variable from our record. 

Values on the BANT Score record in the Flow.

Now that you have the values on our BANT Score record, we can look up the matching answers for our question from Custom Metadata. This is why we took the time in the first step to be thoughtful and precise on labeling our questions. If a question is labeled “B2,” then we just need to get all answers whose question number contains “B2.” This will return “B2-1,” “B2-2,” and so on. This has the added flexibility of meaning we can have as many answers to any question as we want for our BANT model needs.  

The best way to do this in Flow is to create a variable of the “Record Choice Set” type. Add to it all BANT Answer records whose Label field contains our BANT Question Number. I like to also sort them in ascending order so they look nice in the screen flow. Under “Configure Each Choice,” be sure to set the Choice Value to Id — this is how we’ll recall what the user chose later. 

 Under “Configure Each Choice,” be sure to set the Choice Value to Id.

Now, you can craft a screen for the user to choose the answer they want. To give it extra flair, I used merge fields in a Display Text component to announce my question specifically, and I added dynamic Display Text components to alert the user if the score had previously been sent or not. This is easy to do by simply dragging the Display Text component onto your screen, and then setting the conditions under Component Visibility so that it shows only when the warning is relevant. In this case, I have two for if the field BANT_Score_Set__c is null or not. We’ll update this field when we update the scores, so we can keep track of when the Score record was last updated just for cases like this.

Drag the Display Text component onto your screen, and then setting the conditions under Component Visibility so that it shows only when the warning is relevant.

Lastly, add your Record Choice Set as a set of radio buttons by dragging the Radio Buttons input element onto the screen. In the Choice setting, point your radio buttons at the Record Choice Set variable we already made. This turns the set of records from custom metadata into a multiple choice input. It’s like magic! ✨

Lastly, add your Record Choice Set as a set of radio buttons by dragging the Radio Buttons input element onto the screen.

When the user makes their choice, you can update the values on the BANT Score based on what they choose, and even include their notes. Here’s where you set the “Last Score Set” using the $Flow variable of “Current Date” inside of an Update Records element. I also recommend following up this update with a feedback screen making it clear to the user that they successfully updated the flow.

Set Field Values for the BANT Score Records.

Now, let’s deploy this beauty to the right place in our UI. In order to avoid confusing users with the blank fields for Answer and Score from the BANT Score record, I’ve styled the Lightning record page for BANT Score so that the details pane is tucked behind a tab, and my screen flow is displayed prominently. This way, users know to use the screen flow to make their updates.

Deploying the BANT score.

Don’t forget to pass the record variable to the flow from the Lightning record page for your BANT Score record. To do this, after you drag the flow onto the canvas, check the box for “Pass record ID into this variable.” If you’re like me, you’re prone to forgetting this step and will wonder why your screen flow doesn’t work!

Check the box for "Pass record ID into this variable."

For a little extra flair, I added a Related Record component that leverages the mini layout associated with Object Specific Quick Actions, one each for the parent Opportunity and its Account. Again, this is easy because BANT Score is a child record in a master/detail relationship with Opportunity.  

To create these sneak peeks, start from the Object whose record you are peeking TO and create a new Quick Action. For the top Parent Opportunity component, we start from the Opportunity object and create a new action. You want to choose to update a record, not create. I like to call these “Related Record” so I don’t get it confused with my other Quick Actions. 

How to create a Related Record.

Once you have your Action, click the Edit Layout button from the Action’s page to customize which fields you want to appear in your Related Record component. This pretty much works just like a main page layout, but be careful not to pick too many fields. More than six fields will slow down your page load times.

Customize which fields you want to appear in your Related Record component.

Finally, open the Lightning record page for the BANT Score object. In my case, I just have the one page which is set to be my org default. You’re going to drag the Related Record component from the list of standard components on the left to your page. Then, configure the component so it points at the BANT Score’s parent Opportunity, and select your update action if needed (if there’s only one update Action on the object, it will default to that Action). Optionally, you can give the component a custom label; I used “Parent Opportunity” for the sake of clarity. Then, repeat all that for the Account, and you’re set.

Drag the Related Record component from the list of standard components on the left to your page.

Step 4: Scale it! Add mass scoring

So far so good! Now, our users can open each BANT Score and use the wizard to make accurate updates to the answer and the score, and our total BANT Score will roll up to the Opportunity. But can we make this even easier still? Wouldn’t it be great if users could update multiple BANT scores at once from a list of all the existing BANT scores? We can do that with a little help from our friends at UnofficialSF! If you’re new to this handy resource, UnofficialSF is a crowdsourced bank of installable components you can use to extend your flow. One note of caution: Especially because these are crowdsourced and not listed on the AppExchange, always be sure to test these (and ANY components you install) in your sandbox first!  

One of my favorites is Eric Smith’s Datatable. This tool lets you display any collection of records as an actionable grid. It’s awesome for working with batches of records and displaying a lot of information in a small amount of space.

Once installed in your org (along with the prerequisite base packs), you can start a screen flow with a Get of all the Opportunity’s BANT Scores, again using a variable you’ll create and set to accept input like “recordId.”

How to start a screen flow with a Get of all the Opportunity's BANT Scores.

Here, again, I like to add a sort based on the question number so the result looks nice in the datatable display. 

Then, you’ll create a screen using the Datatable component. Be sure to format its parameters according to Eric’s guide, but the most critical thing is to pass your “Get_BANT_Scores” collection into the datatable for display under the Display which records? field. 

Create a screen using the Datatable component.

Eric’s Datatable component outputs those records a user selects as a new collection, which you can now loop through. Inside the loop, put a screen allowing the user to pick the right answer and then use Assignment elements to set the new draft values, just as we did for an individual record back in Step 3.

Example of the loop you will create.

At the end, update your scores and the flow will cycle back to the newly updated Datatable.  Now, create a place on your Opportunity record page to distribute this flow. I personally like to put user aids and wizards like this behind a tab. It gives them room to breathe, but users don’t have to look at it unless or until they need it.

Now, let’s see it in action!  

See how users can update multiple BANT scores at once.

Users can now select multiple BANT Scores to work with and quickly page through their choices, answering the question and adding notes — and Salesforce will do the work of totaling up the scores. 

Step 5: Add progress indicators

Last, but most definitely not least, let’s give our users very clear indicators of how they’re doing. We want to show them both how their Opportunity’s BANT Score is shaping up AND how far along they are in completing scoring.  

Again, because BANT Scores are a child record of the Opportunity, we can roll up the total score pretty easily. We can also calculate the percentage of the BANT Scores that have been completed pretty easily, too. We could certainly display those numbers on our Opportunities highlights pane, and that would meet the requirement.  

But, can we do better than that? YOU BET! 🙌

Let’s create two declaratively built images on our Opportunity to show this information instead.  By using an image or an icon to share information, users can assimilate that information more quickly and in a more memorable way. We don’t want to get too complicated here and slow down our load times, so let’s use formula fields that leverage the little images available in every Salesforce org. There’s a zillion ways to use these and a bunch to choose from, but we’re going to use two: stars and color swatches. 

First, I’d like to express how high the Opportunity score is on a five-star scale. Our total possible score is 400, so that’s 80 points to a star. To create these formula fields, choose the text formula type and use the IMAGE() function.  

IMAGE (

      IF ( BANT_Score__c = 0,

            “/img/samples/stars_000.gif”,

      IF ( BANT_Score__c > 0 && BANT_Score__c <= 80,

            “/img/samples/stars_100.gif”,

      IF ( BANT_Score__c > 80 && BANT_Score__c <= 160,

            “/img/samples/stars_200.gif”,

      IF ( BANT_Score__c > 160 && BANT_Score__c <= 240,

            “/img/samples/stars_300.gif”,

      IF ( BANT_Score__c > 240 && BANT_Score__c <= 320,

            “/img/samples/stars_400.gif”,

      IF ( BANT_Score__c > 320,

            “/img/samples/stars_500.gif”,

“/img/samples/stars_000.gif”)))))), “RatingImage”)

In my formula, I used nested IF() functions to stipulate the ranges and assign a number of stars by picking the right image.

Next, I’d like to capture how far along I am in answering all my BANT Scores. To accomplish this, I’ll use the color swatch images. The cool thing about the IMAGE() function is it allows you to define both the height and width of the color swatch. We just normally leave those bits off because they’re optional. Instead, we’re going to leverage those parameters to create bars.  The full IMAGE() function works like this:

IMAGE(IMAGE_URL, ALTERNATE_TEXT, HEIGHT, WIDTH)

So, I could write a formula like this:

IMAGE(“/img/samples/color_green.gif”, “green”, 10, 50)

That would give me a bar five times as wide as it is tall. What really unlocks the power of this image swatch is when you realize you can put LOGIC in the height/width fields! Stick two swatches together and calculate their widths as a portion of a total amount, and you’ve got yourself a totally declarative progress bar.  

To do your math, create two Roll-up Summary fields. First, create Total_BANT_Scores__c, which counts the total number of BANT Score child records on the Opportunity, and second, create Completed_BANT_Scores__c, which counts the BANT Scores on the oppty that have an entry — any entry — in the Last Updated field. Here’s my final formula, with comments:

/* this starts the Image function & asks for a green swatch 10 pixels high */

IMAGE("/img/samples/color_green.gif","green", 10 , 

/* this calculates the number of pixels in width to give completed items */

      (Completed_BANT_Scores__c * 10)

/* this closes the image function and appends it to the next bit */

      )& 

/* this starts the Image function & asks for a red swatch 10 pixels high */

IMAGE("/img/samples/color_red.gif","red", 10 , 

/* this calculates the number of pixels in width to give incomplete items */

    ((Total_BANT_Scores__c - Completed_BANT_Scores__c)) * 10

/* this closes the image function and appends it to the next bit */

    ) & 

/* this returns the percentage complete in text */

" "& TEXT (ROUND((( Completed_BANT_Scores__c / Total_BANT_Scores__c ) * 100 

),0)) & "%"

I like to use comments in my formulas to make it easier to troubleshoot them or so the next person who inherits my work can read them more easily. Commenting is a piece of cake — just use /* at the start of the line and */ at the end and Salesforce will skip that bit.

The end result winds up being a pretty cool highlights bar! We can clearly see the score, how the score is shaping up, and how far along we are in our work. 

The final result.

BONUS POINTS: Version control and a cleanup tool

So, putting it all together, we have a pretty great BANT Model running in our org. Users can quickly and easily score opportunities, and admins can easily update the model. For bonus points, you may also want to track versions. Since each of those individual BANT scores only make sense in the context of the whole model, if an organization changes the score breakdown, they will likely need a way to refresh the whole set of BANT Scores on Opportunities in flight. 

To do this, you first need to track what version of your BANT model is currently in use. I simply went back to Custom Metadata and created a new CMDT called “BANT Version.”

How to track BANT versions.

This stores a simple text string that I used to identify the version. Ideally, an organization would have the whole model and its version name documented outside of Salesforce. When the model changes, most likely that change will also be hashed out in meetings or work sessions outside of Salesforce. If the admin comes back to make significant changes to the model, they can create a new BANT Version record, name it, and set the old one to a status of “Retired.”

Next, I went back to the flow creating the BANT Scores on Oppty create, and added a step to stamp the parent Opportunity with the version at record create. 

Add a step to stamp the parent Opportunity with the version at record create.

Now, it’s possible to create a report of all Opportunities based on what version of the BANT model they are currently using. This makes it easy to update them manually or, better, you could create a flow to “refresh” the BANT Scores based on a quick action button click or other user event. This means the choice of whether to update the BANT Model for Opportunities in flight is an easy one, and the work can be done with just a click. 

Business results:

It takes a bit of work, and there are a lot of moving parts, but in the end we have a totally custom BANT Sales Model running in Salesforce. We can easily update our model, and even track which Opportunities are on which model. Users can clearly see how their Opportunity stacks up AND how much work they have left to do, and Marketing can target their nurturing campaigns even more precisely than before. 

Do try this at home

Give CMDTs a try today! They’re a fantastic way to offload business logic and are an admin superpower! Check out the Trailhead badges below to get started and level up your Flow and formula field skills.

Resources

Want to see more good stuff? Subscribe to our channel!

SUBSCRIBE TODAY

Have an Idea for a Story?

We are all about the community and sharing ideas.
Do you have an interesting idea or useful tip that you want to share?

SHARE YOUR IDEA