Pages

Saturday, June 9, 2018

Einstein Analytics: Opportunity Dashboard with SAQL Union to show all User

Let's go straight to the business requirements, "show all sales rep with their total sales amount, if there is no opportunity owned by the sales rep, sales rep name must be shown with $0."

High-level solution:
1. Load User master data
2. Load Opportunity data
3. Use UNION to combine the dataset

User data


Opportunity data


Build User master Lens group by Id and Name
q = load "user1";
q = group q by ('Id', 'Name');
q = foreach q generate 'Id' as 'Id', 'Name' as 'Name', count() as 'count';
q = order q by ('Id' asc, 'Name' asc);
q = limit q 2000;

Let's modify necessary SAQL:
1. Rename all q data stream to dsu -- for easier identifier and uniqueness
2. Rename projected 'Id' to 'User_Id', and 'Name' to 'User_Name' -- I'll tell you the reason later
3. Remove 'count' as we do not need it -- User_Id is unique
4. Add 'sum_Amount' with 0 in foreach -- I'll tell you the reason later
5. Remove limit

Here is the result
dsu = load "user1";
dsu = group dsu by ('Id', 'Name');
dsu = foreach dsu generate 'Id' as 'User_Id', 'Name' as 'User_Name', 0 as 'sum_Amount';
dsu = order dsu by 'User_Id';


Build Opportunity Lens group by OwnerId
q = load "opportunity";
q = group q by 'OwnerId';
q = foreach q generate 'OwnerId' as 'OwnerId', sum('Amount') as 'sum_Amount';
q = order q by 'OwnerId' asc;
q = limit q 2000;

Let's modify necessary SAQL:
6. Rename all q data stream to dso -- for easier identifier and uniqueness
7. Rename projected 'OwnerId' to 'User_Id' -- I'll tell you the reason later
8. Add 'User_Name' with "-" in foreach -- I'll tell you the reason later
9. Remove limit

Here is the result
dso = load "opportunity";
dso = group dso by 'OwnerId';
dso = foreach dso generate 'OwnerId' as 'User_Id', "-" as 'User_Name', sum('Amount') as 'sum_Amount';
dso = order dso by 'User_Id';


Combine the dataset with UNION
final = union dsu,dso;
final = group final by ('User_Id');
final = foreach final generate first('User_Name') as 'User_Name', sum('sum_Amount') as 'sum_Amount';


The Complete SAQL
dsu = load "user1";
dsu = group dsu by ('Id', 'Name');
dsu = foreach dsu generate 'Id' as 'User_Id', 'Name' as 'User_Name', 0 as 'sum_Amount';
dsu = order dsu by 'User_Id';

dso = load "opportunity";
dso = group dso by 'OwnerId';
dso = foreach dso generate 'OwnerId' as 'User_Id', "-" as 'User_Name', sum('Amount') as 'sum_Amount';
dso = order dso by 'User_Id';

final = union dsu,dso;
final = group final by ('User_Id');
final = foreach final generate first('User_Name') as 'User_Name', sum('sum_Amount') as 'sum_Amount';


The Moment of Truth



Explanation
  • we rename Id and Name in step (2) to have the same column name with step (7)  and (8)
  • we add 'sum_Amount' in step (4) to have the same column name with dso data stream
  • for our use case, we get the dataset aligned with the same column by adding dummy columns before using UNION to both data stream
  • In the last row, we use aggreagate function first() to return the first user name, as our union  start with dsu which contain user name, while dso at the second/last will always contain "-" for user name, see step (8)
  • In the last row, we also sum the 'sum_Amount' again, practically this is sum the 'sum_Amount'  with 0, remember we add 0 as dummy value in step (4)

Make it simple
Since sum_Amount always 0 in dsu, and User_Name always "-" in dso, we can just simply not need to add them to the data stream, and we will still get the same result, let's remove the unnecessary statement.

dsu = load "user1";
dsu = group dsu by ('Id', 'Name');
dsu = foreach dsu generate 'Id' as 'User_Id', 'Name' as 'Name';

dso = load "opportunity";
dso = group dso by 'OwnerId';
dso = foreach dso generate 'OwnerId' as 'User_Id', sum('Amount') as 'sum_Amount';

final = union dsu,dso;
final = group final by ('User_Id');
final = foreach final generate first('Name') as 'Name', sum('sum_Amount') as 'sum_Amount';
final = order final by 'Name';




Reference:


12 comments:

  1. Hello, your posts are really helpful! Just have a quick question - can I combine 'opportunity created by' and 'opportunity owner' into one table and also calculate the number difference in Tableau CRM? Because an opportunity can be passed over people for many time so the current owners are not necessarily the same as the creators. I want a table to be similar to the below one, not sure if its possible and using the declarative method not SAQL? Many thanks!

    Column1:User's Name, Column2:Number of opportunities created between Jan to Mar, Column3: Number of opportunities own at present, Column4: different between 2&3

    ReplyDelete
    Replies
    1. sounds like you need to "read" opportunity field history record in the dataflow

      Delete
    2. Hi Johan, many thanks for your response. Yes that was what I've done here. I've got two seperate datasets here but just wanted to see if I can display them into one table (joint them) in the format I shared. My team just wanted to see who is good at creating new opportunities and also retain good ones especially for new team joiners. e.g. if someone has created 100 new oppots over the past quarter but only own 80 oppotys at present (closed+open) that would be red flagged. So they will need to display them into one table and group by Sales Rep's name. But the tricky thing is that one is the Createdby.name while the other is Owner.name.

      Column1: Sales Rep's Name, Column2:Number of opportunities created between Jan to Mar, Column3: Number of opportunities own at present, Column4: different between 2&3

      Many thanks!

      Delete
    3. I would say in this scenario to use Createdby.name as the key and compare this with the current owner, you can use compare table for this, including highlight, then also put the data below the chart

      Delete
    4. Thanks again Johan! But I dont think it possible? Seems I will have to group the name by both 'Createdby'and 'Ownedby'. Otherwise, from the example below, say Richard created 5 Opptys and 1 of these has been transferred to Tom. Meanwhile, Richard has received 2 opptys which were orginally created by Tim. So Richard owns 6 (5-1+2)opptys in total. Then when we put Richard in the column 1 below on CreatedBy, it shows 4 correctly in column 2 for sure but will only show 4 rather than 6 in the column 3 because the other 2 were not created by him. But I will need it to show as 7 instead. Any idea? Thank you

      Column1: Sales Rep's Name, Column2:Number of opportunities created between Jan to Mar, Column3: Number of opportunities own at present, Column4: different between 2&3

      Delete
    5. How many dataset you have now? I think this possible with Union as sample above

      Delete
    6. Thank you Johan! I have two datasets now. One is the oppty details with the current owner and the other with the created by. Then i realised that i couldn't join and group by Createdby or current owner as i will always miss the other side (when owner <> createdby). Actually, this is only an example I used to demonstrate my issue here as it was easier to explain. Our real use case is to pull out a table to show the total sales by reps's name and the number of first call they've done. We have different oppty stage to capture how many calls that sales reps have done with one lead. Again, an opportunity can be passed over different reps. Anyway, you meant we need a bit SAQL code on it right? Thanks for that, I will try and explore a bit on Union.

      Delete
    7. Is this what you want? I use Dataflow with simple augment, but do not have the detail https://ap5.salesforce.com/sfc/p/7F000006OEAR/a/7F000001fe0g/QZWn03ucsqrL0FMyqo6B7NHrD4AsOLwPr_9J8jFaxlw

      Delete
    8. Yes Johan! I can see in this example, you've created 706 records but only own at present. I made a simple example in Excel. This is very similar to what i need to achieve.

      https://um3.salesforce.com/sfc/p/24000000aVwj/a/4H000001JraZ/6gYXMTx2jwMGB0xQTVuwfF1RKa6lqQuJe8.xbYtFxNs in this example, CreatedBy represents who made the field change (stage). I just dont think I can join them by using the User ID? It's not the unique information here. Also, there will be thousands of lines with the same ID. Thank you

      Delete
    9. if you do not need detail in the dashboard by clicking a cell, you can simply use dataflow, no need to use SAQL union
      checkout this dataflow https://ap5.salesforce.com/sfc/p/7F000006OEAR/a/7F000001fe0v/VjFTrCPpVPe8m3q_TwfGNYRDSYXnvYSiYGCQeYWGW3c

      Delete
    10. Many thanks Johan for your continuous help on this case! much appreciated! Seems the computer expression is the core node here. It looks like I have to get the count in the dataflow first rather than in the lens. My case would be a little bit different as i will also need to augment the Opportunity Field History object. I will play around it based on your sample dataflow! It should be fine. Thank you very much!

      Just one silly question here: 1, what does '1' in SAQL represents? Is there any 2 or 3? Also, is there any documentations where I can learn to use the '1' here? Tried but didn't find much cotents online. Sorry I am new to the TCRM.

      Thanks a lot!

      Delete
    11. 1 in that case just for counting for each record, similar as Power of 1

      Delete

Page-level ad