5 CRM Analytics Tips and Tricks I wish I learned sooner!
1) No SAQL Knowledge? no problem
You may know Dataflows will be retired at some point, and recipes will become the main tool to build datasets; however, some templated apps such as the Sales Analytics App still leverage Dataflow for Dataset construction. For these use cases you might need to edit some dataflows to add a filter or create a compute expression or compute relative which requires some SAQL knowledge.
Now, you may find date filters in SAQL are a bit more complex, since they are deconstructed in the dataflow and split into date parts, so, if you are not quite sure how to write a date filter in SAQL, simply follow the next steps:
1) Open your dataset in a new new lens and add the filter you need for your dataflow, for example: Filter by Created Date between 08/01/2018 to 08/01/2022
Image 1. Open Dataset in new lens and add filter
2) Then change your lens to query mode and copy the code
Image 2. Lens in query mode
3) Now that you have the code copied, go back to your dataflow, add your filter node, and simply paste the code into the filter node in the dataflow!
Image 3. Filter in Dataflow
If you do need SAQL assistance feel free to check out these resources:
Salesforce blogger→ SAQL simply explained - Part 3
2) Replace the dataset behind your dashboard
Now, let’s say you want to replace the dataset you are using in a dashboard without having to open each and every one of the widgets; maybe you are using a Dataset from one of the templated apps but decide to add a couple of transformations to that dataset, so you create a new recipe using the original dataset as input, then add a transform node to add a couple of new fields and register this as a new dataset.
Now, you want those new transformations available on your dashboard... so how do you replace it? It’s actually super simple!
1) Use CTROL+E for PC or CMD+E for Macs in order to access the JSON edior
Image 4. JSON editor
2) Next make sure you replace the ID, Name and Label
Image 5. Search for id, label and name
3) Make sure you choose Replace All since there will probably be multiple references of the same dataset
Image 6. Make sure you choose the replace all button at the top right
For more resources checkout the following:
Replacing Datasets on a Dashboard --> https://salesforce.vidyard.com/watch/XfbcTkgdhEiUb6B1RLkYCa
3) Prevent using multiple pages with Components!
Components are an awesome alternative for leveraging pages to create interactions. Thinking of adding multiple pages in a dashboard? How about letting users toggle back and forth?
Components are basically a mini dashboard within a dashboard where instead of adding pages for dynamic interactions, you can add these as a component!
See my example below, where I have my goals by Opportunity owner, Role and Stage.. I am actually using different datasets behind each chart, but it appears as one page in my dashboard.. since I am using a component that has 3 separate pages in it.
Image 7. Dashboard with an embedded component
Image 8. Component View with 3 separate pages
If you want more information on components check out the blog below:
Make your dashboard flexible with component widgets→ Link to blog
4) Keep seeing a recipe error out for a field you cannot see in the UI? Try the JSON!
A lot of times, customers will see errors in a recipe, but they are unable to find it in a node within the recipe. This can be tricky, specially if you need to remove the field if it’s causing issues such as preventing a recipe to run or preventing a successful connection for data sync.
If you are unable to find the field via the UI, download the JSON, do a control+F and search for the field name in the json. Note: You mind to check for all references since most likely it will be referenced more than once!
Once you have removed all references for that field, re-upload your JSON to your recipe.
Image 9. Download and re-upload JSON with the 2 buttons at top left
5) That one Function that will allow you to Create flags, Re-Order Fields and make your life easy peasy..,
So we’ve seen a lot of use cases where we either need a way to flag records that meet specific criteria, or we need to display days of the week as Monday, Tuesday, Wednesday instead.. So, I will walk through 3 common use cases where a Case statement can make your life easier. In summary, we will use case statements to express if/then logic.
Note: There are hundreds of applications for Case statements and you can also combine Case statements with other functions to create the perfect formula
Use Case: Flagging Duplicate Opportunities
Let’s say you are creating a recipe with Opportunities, however you need to remove duplicates, below are the steps:
We will enable the toggle for Multi-row formula, since we need to look at multiple rows to identify duplicates
Next, we will use Current and Lag functions from the Window functions since Current function will return the value from the current record in the partition and lag function returns the value from the previous record in the partition; this way we compare current record with previous.
Additionally, we want to be able to flag those records as ‘Duplicates’ which is why I’ll include it in the logic below:
case
when current(Name)= lag(Name)
then 'Duplicate'
else
Name
end
Image 10. Multi row formula combined with case function, current and lag
Use Case: Re-ordering Stages
Another really common use case is reordering Opportunity Stages, so they show in a specific order once we open them in a new lens.
Image 11. Opportunity stages-> If you see this lens above, you’ll see the stages don’t real appear in order,
In order to see the stages in order, we need to create a case statement similar to the following:
case
when StageName = 'Qualification'
then '01. Qualification'
when StageName = 'Discovery'
then '02. Discovery'
when StageName = 'Proposal/Quote'
then '03. Proposal/Quote'
when StageName = 'Negotiation'
then '04. Negotiation'
when StageName = 'Closed/Won'
then '05. Closed/Won'
when StageName = 'Closed/Lost'
then '06. Closed/Lost'
end
Image 12. Case statement used to Re-order stages
The final lens will look something like this:
Image 13. Final lens
Resources: Blog: My most used computeExpression
Use Case: Assigning Labels to Days of Week
This use case is also quite common, sometimes users wish to group by Day of the week, however the dates are usually in the following format: DD/MM/YYY HH:MM:SS
Luckily, we have many options to change the format of these dates. Including using dayofweek function to first extract the day from the date and then calculating the Day of the week with a case statement, such as the example below:
Image 14. Combining functions DayofWeek with Case statement
Resources:
Date functions for formulas: Link to help article
