Bucketing in SAQL in Dashboard and ELT in Salesforce Analytics Cloud

Binning or Bucketing or Categorisation is a method of taking a number of continuous values and placing them into ‘bins’. To do this we use the Case Statement in SAQL. We can actually do this in a number of places. We can position a case statement as part of the ETL from Salesforce and as part of a SAQL step in the dashboard JSON. A simple way to think about it, is simply taking a value from a column with a specific requirement and adding a categorisation to it in a new column. This allows you to do things such as group, query and graph the categorisations in Salesforce Analytics cloud. Sometimes this is a great idea, especially when thinking about your end users who may be doing data discovery or who are building dashboards and you want to keep them from doing SAQL.

In the examples below, a simple binning on a dimension is shown, then a calculation, followed by a demonstration on how this can be done in the ETL script when pulling data from Salesforce CRM

The first example we are using the OnTime Dataset. we want to categorise the flights based on where the destinations are:

  • ABE
  • ABI

We have created an extra column in the dataset called Coasts; this is where flights or rows have a destination of ABI or ABE. All other destinations will be categorised in the column Coasts as ‘Other’.

We now move to applying categorisation to specific thresholds. Here we can project a measure and assign bins to specific thresholds.

The same can be done when pulling information from Salesforce directly using the platform ETL. In this example we are ingesting both the Account and Opportunity Object. We are then adding a transform using the “computeExpression” to the account object ingestion. This is a simple case statement where we end up creating a new column where AnnualRevenue meets certain criteria. This is then augmented onto the Opportunity object and then registered into Salesforce Analytics Cloud.