Skip to main content

Quick Tips: Sort Pipeline by Sales stages with Salesforce Tableau CRM (Einstein Analytics)

Quick Tips: Sort Pipeline by Sales stages with Salesforce CRM Analytics (aka Tableau CRM)

Problem Statement

While working on a recent change, we realized (a bit late) that the sales pipeline view shows sales stages in incorrect order. Ideally, you would want the pipeline view to reflect the stage in the same sequence as the sales process (for example Prospect, Propose, Negotiate, Close).

Sales Path

Sales Path

Sales Pipeline

Initial SAQL

q = load "opportunities";
q = group q by 'StageName';
q = foreach q generate 'StageName' as 'StageName',
    sum('Amount') as 'sum_Amount';
q = order q by 'StageName' asc;
q = limit q 2000;

Solution Approach

Approach 1: Augment a field within the Opportunity dataset (CRM Analytics) to generate a new column for the Sales stage sequence

This approach would be faster to deliver and has comparatively less change. However, as sales stages are changed/ updated it can result in the Tableau CRM dashboard showing an incorrect sales stage sequence. So, it can lead to dual maintenance (Sales cloud and CRM Analytics)

Approach 2: Prefix opportunity stage value with stage position (for example 1 - Prospecting)

This approach may also be a faster change. However, it uses non-friendly sequence numbers in the opportunity stage. Also, if the same stage values are used across multiple sales processes (or record types for other objects), the sequence may not stay correct.

Approach 3: Create a new formula field within the Opportunity object (Sales cloud) to generate a new column for the Sales stage sequence (PREFERRED)

This approach will take more time (depending on your change management processes) but will ensure that Sales teams own and update their sales stages as needed. In other words, no dual maintenance between Sales Cloud and CRM Analytics.

However, the given solution works for approaches 1 and 3

Solution Steps

1. Create a new field on Stage Position Opportunity to reflect stage sort value

2. Update Object connection settings and required workflows/ recipes to get your new column into the desired dataset

3. Modify Chart SAQL to add a max of Stage Position to the result set to get another dimension.

q = load "opportunities";
q = group q by 'StageName';
q = foreach q generate 'StageName' as 'StageName',
    sum('Amount') as 'sum_Amount',
    max('Stage_Position__c') as 'sum_Stage_Position__c';
q = order q by 'StageName' asc;
q = limit q 2000;

Sample result when you run your query

4. Now, sort final result set using Stage Position field

Final SAQL

q = load "opportunities";
q = group q by 'StageName';
q = foreach q generate 'StageName' as 'StageName',
    sum('Amount') as 'sum_Amount',
    max('Stage_Position__c') as 'sum_Stage_Position__c';
q = order q by 'sum_Stage_Position__c' asc;
q = limit q 2000;

Sample result when you run the query (notice results are sorted by stage position column)

Finally, view your sales pipeline chart sorted in the right order

Note

The given solution can work for any similar requirements, wherein sorting needs to be performed via a hidden column, that is, a column not used in chart display.



Comments

Popular posts from this blog

Quick Tips: Salesforce default Images

Well, I'm sure a lot of you still rely on using out of the box salesforce images for displaying quick icons within formula fields or even using them within your Visualforce pages. Lately, I realized that a lot of earlier resources are no longer accessible, so I tried to quickly extract all images from Salesforce CSS files and provide a quick reference here. Please note, I've referenced all images from SF servers directly, so if anything changes, the image should stop rendering here. As these images are completely controlled by Salesforce, and in case they change anything, it might lead to image not being accessible. Image path Image /img/samples/flag_green.gif /img/samples/flag_green.gif /img/samples/flag_red.gif /img/samples/color_red.gif /img/samples/color_yellow.gif /img/samples/color_green.gif /img/samples/light_green.gif /img/samples/light_yellow.gif /img/samples/light_red.gif /img/samples/stars_100.gif /img/samples/stars_200.gif /img/samples/stars_300.

Lightning: Generate PDF from Lightning components with in-memory data

I'm sure as everyone is diving into lightning components development, they are getting acquainted with the nuances of the Lightning components framework. As well as, its current limitations. Being a new framework, this is bound to happen. Although we have our users still using salesforce classic, we have started using lightning components framework our primary development platform and Visualforce is considered primarily for rendering lightning components within Classic Service console. Recently, while re-architecting a critical module, we encountered a problem wherein we needed to generate PDF from lightning components. Now, being Javascript intensive framework, it has limited room for such features (may be included in future roadmap). As of now, there is no native feature within the lightning framework to do so (at least I didn't find anything). Common Scenario - Create Visualforce page to retrieve data and generate PDF For scenarios where the data exist within Sa

Lightning: Generate PDF within Lightning Experience with Salesforce Data

Some time back I posted a solution to generate PDF from Lightning components using in-memory data. Post url: http://www.vermanshul.com/2017/07/lightning-generate-pdf-from-lightning.html It was developed for a specific scenario, wherein we need to generate PDF where: User interface is Salesforce classic Initiated via Lightning Component Data doesn't exist within Salesforce and is completely in-memory As complex and tricky this situation was, we did end up finding a stable and equally tricky solution. However, I realize that there are still lack of solutions (or maybe my search skills are downgrading) to generate and automatically download PDF document from Lightning Experience, without using any lightning components, wherein data exists within Salesforce. You can use the earlier solution in that case, but it will be an overkill. There are various solutions available to generate PDF from javascript. But, I still think the plain old method of converting HTML to PDF (via