What-if Analysis with CRM Analytics

4.8
(16)

Testing scenarios has been the number one goal since day one of computing. One great example of this is Deep blue, the first computer to win a chess game vs the world champion while running all possible scenarios as fast as possible. Fast forward to today, and computers are still used to run scenarios of all sorts, in various industries. Whether I want to evaluate the impact of my pipeline on my annual revenue or understand how a factor will affect my company’s gas emissions. Having the ability to run a what-if analysis is a key aspect of analytics. So it is long overdue that we decided to bring it to CRM Analytics.

Introducing the input widget

So far, CRMA did not provide a way to easily grasp a value from the end dashboard consumer and reuse that value to compute other values. Pre-configured options using the toggle widget were as close as we could get to running scenarios. However, those scenarios were all planned ahead of time; meaning the author of the dashboard would choose a defined set of scenarios from which the end consumer would be able to choose, nothing else.

With the input widget, we’ve made it easy to capture any numerical value from your dashboard user. Just drag and drop it like any other widget, configure it with the settings of your choice and you’re almost ready to go.

Note: To learn more about each of the widget properties, head to our documentation.

For now, builders will need to use bindings to link the input selection to any query where they would like to use this input. Now we know that our motto is EASY. But we also recognized how valuable this new widget would be. Here is just a glimpse of some use cases across various industries.

Use cases

  • As a sales manager, I would like to change each of my sales rep’s pipeline values and evaluate how that impacts my quarterly revenue.
  • In rebate management, as a dashboard user, I would like to change the rebate % and see how that would impact my profit to test different scenarios.
  • In the sustainability field, I would like to change certain factors and see how that affects my gas emissions.
  • In the supply chain business, I would like to see how orders or margins would be impacted by a shortage of material.

As you can see, there are many use cases and I’m sure you are already thinking of ways to utilize this new feature.

You said bindings

Well yes, bindings. And yes, Input widget. But do not let this trouble you. I figured it’ll be better to explain this all with an example.

Let’s look at one of the previous use cases and see how we would implement it. Let’s focus on the rebate percentage and see how it would impact my profit. In this example, we will first look at the widget properties and then we will dive into the query configuration.

Input Widget Configuration

  1. First, let’s add an input widget to your dashboard. This widget will be used to apply a percentage to our profit value.
  2. Let’s start by configuring the widget properties (in the right property panel). First, let’s add a title and call it Rebate % value.
  3. In the input values section, you can choose between the slider or freeform input modes. Know that both allow capturing the user’s input, the main difference being a different look. In our example, we will use the slider mode.
  4. Next is the value type, for now only numerical values are supported so we won’t change this.
  5. For the display format, we will choose the percent option. Note that this is only a visual indicator for your users. Meaning we will need to divide our formula result by 100 to make it a percentage.
  6. For min and max, we will choose 1 and 100 which are classic settings for percentages.
  7. Lastly, the start value will be 1 and we will keep increments of 1 as well. This is what your widget and properties should look like:

Target Query Creation

  1. Let’s transition to the query and start by creating one as a compare table and add the fields you will use for your calculation. In our example, we are interested in the “profit” field.
  2. Add a formula column, this column will be used to perform the calculation later. For this example, I just reference column A in my formula so that both columns have the same value.
  3. This is what my query would look like:
  1. Hit done.
  2. Now drag and drop your query on the dashboard grid.

Let’s bind the Input widget and Target Query

  1. Select the table widget with your query and in the right property panel go to the “Advanced Editor”. This will allow you to visualize the JSON code powering the widget or query.
  2. Go to the query tab. In this case, we want to modify the query and add a reference to the input value in our formula column.
  3. Using the left panel create a binding with a syntax similar to this:
    1. Start by selecting the query and pick the input one (it is automatically created when you add an input widget)
    2. now for the source data, we will use a cell and use the first row index: 0. Finally we are interested in the input column of the query (not the min or max).
    3. In this case, we want the interaction type to be a selection.
    4. Last, under “more options”, we will need to change the data serialization function to be asObject rather than string since this is a numerical value. This should be your final result.
cell(input_1.selection, 0, \"input\").asObject()
  1. Now paste this binding into the “Formula group” of your query which should be populated by “A”
  2. Lastly, to get the profit percentage we will want to multiply the input value coming from the binding to our column A and divide it by 100 to get this formula below:
"formula": "A/100*{{cell(input_1.selection, 0, \"input\").asObject()}}"
  1. Hit “Done” and Voilà!

Once set up, switch to view mode to test your different scenarios using the input widget. You will instantly see how your selection is impacting your other queries. And because it works as a selection on the input widget, you can save your scenarios as Saved views and share them with your team.

Now you have connected your input query to a formula, you can use that field to perform any calculations you wish. To do that, just go to that formula field in the JSON and perform any computation you’d like.

Note: To learn more about bindings, I would encourage anyone to read All you need to know about bindings.

What’s next

You guessed it, we could not leave such a key feature with a technical barrier of entry, we had to make it easy. So in the upcoming release, we will allow users to add this input field as a column directly in your queries (similarly to formula fields), all through clicks.

We’re also thinking of supporting more value types, such as dates and text in the future and this is something we would love your feedback on. Add your improvement ideas in the comments or reach out to me in the trailblazer community.

How useful was this post?

Click on a star to rate useful the post is!

Written by


7 thoughts on “What-if Analysis with CRM Analytics”

  • 1
    Tim Short on August 30, 2022 Reply

    Whoot whoot! Awesome, I highly requested feature, thank you!

  • 2
    Tony Watt on September 5, 2022 Reply

    Thanks for explaining, Arthur. We’ve been looking for this functionality for some time – great news.

  • 3
    dominique beaudin on October 18, 2022 Reply

    Will this widget be available for all versions of CRMA? Great Blog!

  • 4
    Arthur Fabre on November 10, 2022 Reply

    @dominique, Yes this feature should be available for any CRMA license that allows you to create a dashboard.

  • 5
    Michael Giese on December 22, 2022 Reply

    Great news, been waiting for this type of functionality!

    @Arthur I’ve followed all the steps you outlined above and believe I’ve built everything correctly. However, the table in my dashboard isn’t automatically updating when using my input slider.

    Could I be missing a step where it’s not auto-updating my table results after changing my input widget slider value? It looks to be calculating…but using the low end of my widget (in this case, 1%).

  • 6
    Michael Giese on December 22, 2022 Reply

    Figured it out and it’s working great now! Woohoo!!!

  • 7
    Leslie on October 20, 2023 Reply

    Such a lifesaver! I built something like the what-if analysis with 4 input fields and everything is working fantastically thanks to this article. Thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.