Data Densification in CRM Analytics

“No Results Found” against a blank, white backdrop is a common response in CRMA when data has been filtered or faceted in a way that leaves no data to be displayed in a chart widget. Unlike the number widget and text widget which allow customization of this message, chart widgets do not offer this flexibility. There are instances where the end user may instead want to see 0s with the chart’s dimensional values intact, and the error text can be a bit jarring when surrounded by other beautiful graphics.

In the image below, we describe the “Tasks by User” for a fictional company, Relax Corp. In this example, the business would like a dashboard that describes Tasks in 3 different statuses with varying priorities and types. The current widgets are pre-filtered to create a “swim lane” type visualization. The polar gauge charts are a single query, with Status on the trellis shelf, while each of the bubble clusters is an individual matrix chart.

If we select a status using the list filter at the top of the dashboard, we are met with “No Results Found” in several of the charts, as well as a misalignment of the donut chart in relation to its respective matrix below.

Our end user at Relax Corp would like to see zeros instead of “No Results Found”, as well as maintain the spacing in the initial layout, i.e. we don’t want to see a single polar gauge when filtering to a single Status; we would like to see two gauges with zero and one with 18.

Currently, Salesforce does allow the user to specify a customized message for no results in a number widget, but not in a chart visualization. By making the custom text for no results “0”, the number widget now shows “0” instead of “No Results Found” as it did before.

Before we describe the solution, we would like to clearly define the end state by showing a visual of the correct dashboard. Below, we see zero for the number widgets, polar gauges, and matrix charts. In addition, we see all three status types, priorities, and task types even when no data is present.

We can even select multiple items in status, priority, and type and see the corresponding data relating to those subsections of data while still seeing all possible subsections that aren’t being surfaced in the current filter selections.

Before we dive into the solution, we need to introduce a couple of concepts: Dataset blending and connecting data sources. Read on to the next section if you are unfamiliar with these capabilities. If these concepts are familiar to you, you can skip to the solution!

Much like datasets can be blended in Tableau, CRM Analytics offers this same feature. Blending datasets involves two or more datasets being used in a single query: a primary dataset, and one or more additional dataset(s) that share at least one-dimensional value set. When you first open a query on a dataset, you notice the “+” sign in the upper left corner of the lens. Clicking this button and selecting an additional dataset will prompt you to select another dataset and establish the kind of join relationship you want.

Note: whichever dataset you initially opened the lens on serves as your “left” dataset.

Once two or more datasets are blended, the default behavior is that a filter on your primary dataset will not filter the values of your secondary dataset unless: 1) they both share that dimension value set, and 2) you connect the data sources on the dimensional fields they share using the dashboard UI. For this solution, we will not be creating relationships between our datasets, but to learn more about connecting data sources, see Salesforce’s documentation.

Dataset blending is a critical part of our solution, but our dashboard only uses one dataset so what dataset do we blend with? First, we need to make recipe changes in order to output a dataset we can use in our blend. This solution needs a second dataset that includes all possible dimensional values for each subsection of Status, Priority, and Task. To do this, we’ll create a new branch in the recipe with an Aggregate and Output node right before the original output node. This will ensure all transformations and filtering are taken into account.

The Aggregate node contains only the fields required for our visuals: Status, Priority, and Task. The output will be a new dataset that contains every possible combination of the dimensions we need. Now, run the new recipe and head back to Analytics Studio.

Now that we have our dataset for blending, we can begin. Open the query lens for the chart you want to begin with.

Note: The option to blend datasets will be unavailable if you have already placed dimensions or measures on the shelves or in the filter pane. You’ll have to remove everything except “Count of Rows” in order to set up the blend. After the blend is set up, you can again place your fields and calculations on their respective shelves. In the case of our blend, we will not be connecting our two data sources on their common dimensions. This will make sense below.

After you’ve cleared out your lens shelves, select the “+” in the upper left corner and locate the aggregate labels dataset you created in the recipe step. At this point, there are two ways to do this that give you the same result.

1. Open a new lens on your values- only dataset and Left Blend with your main dataset
2. Open a lens on your main dataset and Right Blend it with your values-only dataset

The important part is that all values from your labels dataset should be included, regardless of whether they are in your main dataset. For this solution, we’ll be using option 2.

Once the blend is set up, it should look like the image below:

This solution works because the blend adds a coalesce behind the scenes, which ensures that when a filter selection is made that excludes a dimensional value(s) from your main dataset, all dimensional values are still available from your labels- only dataset for the chart to pull from. You can see the SAQL behind a blend below.

When a certain Status is missing on a pre-filtered widget, instead of showing no results, the coalesce adds in the subsection for the missing category.

Because we opted not to connect our data sources on their common dimensions, our filter on Status is only filtering our main dataset. This allows all values from our labels dataset to be available for visualization, while offering the ability to evaluate the measure calculations to 0 in our main dataset.

This functionality isn’t always desired as it may drag query performance down, but it is a useful workaround when a client has a specific use case in mind that requires an alternative to the default “no results” behavior.

We hope this helps you in your next data quest. If you have problems in your own Salesforce organization or need help optimizing your use of CRMA, reach out to SpringML. Our experts can help!