I do enjoy a good challenge and recently the PreppinData and WorkoutWednesday teams joined forces in a challenge that was based on Salesforce data. This was the perfect setup to take Salesforces native analytics engine (CRMA fka. TCRM fka. Einstein Analytics) for a spin to see what is possible.
To be fair, I am not a CRMA guru. I use it for around 1.5 years now and feel confident that I understand the features and limitations of the tool and that I can solve most problems with a bit of googling. At the same time I am aware of my blind spots (mainly SAQL, and certain types of bindings). That at least puts me in a position to understand what it is that I am searching for, which goes a long way to solving problems.
With this out of the way, let’s look at the PreppinData challenge first:
The main task of the ETL part was to utilise the two provided tables to create a dataset that has one row per status change of every given opportunity. In CRMA Recipes it would look something like this:
It wasn’t overly complicated in this case, however while Tableau Prep provides a pivoting tool, in CRMA Recipes this needs to be done by splitting the data flow and union it again in order to move values from columns to rows. In this case it was very simple but you can imagine that it becomes much more complex when you have more than 2 values to pivot. The clean-ups just made sure that field names, data types, etc. were correct then the historic data was appended and an aggregation tool was used to make sure that duplicates were removed.
One other slightly annoying aspect is that date aggregations split the dates into date parts, which need to be stitched together and parsed afterwards if you want to keep the dates. It’s not hugely complex but something that you will need to keep in mind if you do it regularly. Apart from this, the problem is solved fairly quickly and efficiently.
This is where it gets interesting. Here is the challenge: https://www.workout-wednesday.com/2022w23tab/
And this is how the dashboard was supposed to look like:
Just from looking at it, everybody who spent some time in CRMA will be able to tell you that there are a bunch of aspects that are straight up not possible, to name a few:
- You can’t customise legends like this
- Dot plots cannot have a time axis
- Those grey reference bands are not possible
The fun was to figure out how you’d be able to come close to it, in this case without regards to maintainability or scalability (trust me, the resulting dashboard should never be productionised!).
As I mentioned above, these kind of dot-plots are possible in CRMA but not with a date axis, so the first thing I did was to transform the data into a numeric value. In this case I took the number of days from a few days before the first opportunity opened until the given date at which the stage changes.
The result looked very close to what was specified. I knew that I wouldn’t be able to add the grey background but I felt this was a minor detail.
After manually assigning the colours and making sure I could sort the whole chart by the close date, this was the result:
To remove the confusing numeric axis, I hid it in the main chart and created to timeline charts in which I set the value for each mark to 0 and positioned them above and below the main chart.
This is a technique many advanced Tableau users will be familiar with when it comes to customising axis and/or making multiple charts look like one. The problems in CRMA with this technique are that
- you cannot perfectly align the two charts. Depending on the granularity of the data a dot in January may appear where it says February
- There is a very thin line visible with different colours. If you don’t look for it, you might not see it and if you do, you might just ignore it but it’s there
- Time axis cannot be customised, so you end up with this weird sequence of “Mar 11 21 Apr 11 21…” which indicate when a certain month start and where the 11th and 21st day of the month is.
After aligning it properly and a few cosmetic changes the result was decent:
At this stage I used the top line chart to also display a legend and as I wasn’t able to manually sort the stages, I added a numeric pre-fix to force the sort I wanted
High level KPIs
This is not complicated at all. CRMA has a number widget that shows you exactly one number. My issue was that I had to customise the data to such a degree, that I had to figure out all the correct filters first to arrive at the correct figure. Once that was done I just had to duplicate the widget 4 times, change the filter to the next stage and adjust to text colour.
I could not figure out how to do a customised legend. I had this idea to make it a scatterplot, define the row and column for each value in the data and then just plot 6 dots and label them. The problem is that I don’t think you can force labels on a scatterplot. So what I would have ended up with would have been 6 coloured dots without labels. The existing legend works, but it would have been a nice touch to have it customised as well.
To enable a lot of these things, I actually had to adjust the recipe to add additional calculations and values to actually enable some of the things I did in the dashboard. (I also reorganised the nodes to make it easier to work with)
There are a bunch of added calculations to sort the legends, calculate the numeric values for the x-axis, to get the close date on each record in order to sort the chart properly and a few other small adjustments.
This was the WoW challenge:
And this is my result in CRMA:
Further (possible) improvements
There are a few things that could be done to get even closer to the solution but I already spent way more time on this than I anticipated:
- You can use bindings to switch the sort between close data and days open. That’s fairly straightforward to do, I just ran into a problem somewhere and didn’t get to troubleshooting it
- You could try to emulate the grey background by creating additional records that plot grey dots in the background, from one day before open date to one day after the last status change. This wouldn’t be perfect as you’d likely see the individual dots and I don’t know if they would overlap with the coloured dots. It would also blow up the amount of data, so it has a bunch of disadvantages but as a POC it might be fun to try it
- There might be a way to get rid of the coloured lines in the two line charts. It wasn’t a huge priority for me but I guess you could make the lines white and add the legend as just another chart next to it.
CRMA isn’t Tableau (duh!). CRMA imposes more limitations on the user, which makes it hard to create more custom visuals. That said, you’ll be able to create the standard charts your normal business user will be familiar with and – like any other tool – you can push the boundaries by thinking out of the box and using the tools you are given in a way that they weren’t originally intended for. In this particular case, you might also have to consider thinking “outside of the tool” and put a bit more effort in your data structure in order to support very specific requirements.