Multivariate Maps in CRM Analytics

One of the most overlooked features provided by CRM Analytics is the ability to create maps! On the surface, it looks like you can only create simple, single value analysis within CRM Analytics maps, but that’s not true. If you get creative, you can create multivariate and more advanced maps with just a little bit of background work. Today, we’ll be going through how to create a bivariate map in CRM Analytics. 

Understanding the Data

Start off by downloading this dataset which compares the population of each US State with the number of Chipotle Restaurants in each state. The analysis this will produce will show us which states are overrepresented by Chipotle Restaurants and which are underrepresented. If you look at the data source, you’ll notice that there are three columns, the state, the number of chipotle locations, and the population. In order to produce the multivariate analysis, we’ll need to bucket both of our numeric values into proportional buckets based on the population and number of locations.

In order to create these buckets, you can either use a data recipe, or do it directly in the dashboard. For this example, I’ll be showing you how to complete this from within the dashboard itself.

Bucketing your Data

After loading the dataset into CRM Analytics, you can open up the dataset in a new dashboard. I pulled the dataset into a new chart widget and converted it to a compare table. The compare table allows us to perform analysis on our dashboard without going into the SAQL code. Within the compare table, I created a column for both the total population as well as the number of chipotle locations. 

Now, we’ll want to create our buckets. I want to create proportional buckets based on population. This means that we will divide each state’s population by the population of the most populous state. In order to do this, we’ll need to create a windowing function that identifies the max population found in the Sum of Population column. Click on the plus sign under the columns section of the compare table and click “Add Column” and select “Foruma.” Then, by clicking the “f(x)” we’ll be able to add the windowing function. I selected “Sliding Window” with a function of “Max” and removed the start and end values. This pulls the population of California onto every row so that we can create our buckets. 

The next step is to divide each state’s population by the Maximum Population. I clicked the plus sign in the top right corner, and used a simple math calculation to divide column “A” (the Sum of Population) by our “MaxPop” Column. After you’ve completed this step, you get a good idea of how the distribution of the population looks across your data. 

This will then allow you to decide the buckets you intend to use. I decided to put my ranges as 0.0-0.1, 0.1-0.2, and 0.2-1.0. This roughly corresponds to the population ranges of 0.0-4.0m, 4.0m-8.0m, and 8.0m+. You can create your own buckets depending on your desired display. I like to use this method to get an idea of the distribution of data before I choose my buckets. To create the bucketing column, I used the case formula in a new column of the same compare table. I converted all of these values into a number between 1 and 3. This will allow us to create 9 unique buckets when compared to Chipotle locations. 

Now that we’ve bucketed our population values, we will do the same thing with our Chipotle locations column. That’ll be identifying the max value for all states with a windowing function, dividing the number of locations by the maximum number of locations, and then creating our proportional buckets. The buckets I chose for my Chipotle locations ended up being identical to that of the state populations, 0.0-0.1, 0.1-0.2, and 0.2-1.0. These values correspond to 0-40 locations, 41-90 locations, and 91+ locations. 

Now that we have both our population and chipotle bucket columns, we can combine these two values using a further case statement. We want to create nine unique buckets for the analysis, so I used the following case statement in a column I called “Population vs Chipotle:”

case 

when ChipBucket == 1 and PopBucket == 1 then 1

when ChipBucket == 1 and PopBucket == 3 then 3

when ChipBucket == 2 and PopBucket == 1 then 4

when ChipBucket == 1 and PopBucket == 2 then 2

when ChipBucket == 3 and PopBucket == 1 then 5

when ChipBucket == 2 and PopBucket == 2 then 6

when ChipBucket == 3 and PopBucket == 2 then 7

when ChipBucket == 2 and PopBucket == 3 then 8

else 9 end

Click close in the bottom right corner of your compare table, and change the visualization to a map. In the formatting, change the view to US States, and hide all the columns in your analysis except the Population vs Chipotle column. 

Displaying your Data

At this point, we can begin to play around with the display. I wanted my 9 values to be displayed into bucket colors, so I created my own custom legend. I mocked up one that shows the states that overrepresented and underrepresented using text widgets. I chose a dark red color for states with a high population and low number of restaurants, and a dark green for a state with a low population and a high number of restaurants. If a state had the same value for both population and number of restaurants, I made those a tan color. I could then use this text legend to color code my map to show both over and underrepresented states.

In the conditional formatting of the map, I selected the Population vs Chipotle column, and selected Bins. I created a bin for each bucket value, and populated it with the colors found in our legend. This will give us our final map.

One final step that will bring our map to the next level is to update the tooltip to provide a more detailed explanation of our map. Right now, our tooltip only shows the state name and bucket that we used to create our mapping fields. What would be more useful is to include things like the state population and number of locations in the tooltip. Fortunately, CRMA has made it easy to add those values since we already have them in the backend of our map (remember the compare table we created at the start?). 

Click on your map and scroll down to the tooltip section of the widget details. Click “Customize data point details.” Here, we can select the dimensions and measures we’d like to include in our tooltip. I removed all but the Sum of Population and Sum of Chipotle Locations.

Now, you’ve told the whole story with your map! 

Next steps

If you want to take your map to the next level, you can replace the hard-coded buckets we created in the compare table with input widget values, which would allow users to set the size of the buckets they want to use in their analysis. Simply select “Add Column” in your compare table and select Input to allow an existing input widget to power the buckets we created! Stay tuned for future CRMA and Tableau content on Red Analytics!