Salesforce: File and Content report type

Run File and Content Report report type to analyze how users are engaging with files and Salesforce CRM Content, however, this report type will not show custom fields created in the Content. You can use Content Report report type to report all content, including the Library Name and Content Custom fields, however, Content Report report type will not tell you the content and file download history and versioning.

Let me take a screenshot of fields available from File and Content Report

If your report contains fields only in the red box, the report result will not tell you the download history and version for each file. So, if a file has multiple version, it will show only the latest version as # Versions and # Size (MB).

Now, let us add "File Version" from green box.

See that the 1st file have 4 version and show as different lines, but note that Size (MB) for each line is the file size of the latest version, not file size of each file.


Next, let us remove "File Version" and add "Download Date" from the blue box.

Each download will appear as a line with the download date time, downloaded by, and user type.


Last, let us add back "File Version" to the last report.

The primary lines here is still "download", file version added will tell you the version of the file downloaded by someone and when. This report will not show all file version.


Reference: File and Content Reports

Einstein Analytics: Add and Append in Recipe

When you create a new recipe in Einstein Analytics, there is 2 main data manipulation: Add Data and Append Data, some of us may confuse and mix up between them, including me.

Recipe in general
- Produce a new dataset (re-run the recipe with overwrite dataset created)
- Cannot overwrite source dataset

Add Data
Imagine that Add data is similar to VLOOKUP() in Microsoft Excel:
- Additional columns
- No change in the number of rows

you need matching key in each dataset, and fields to add

this recipe will produce a new dataset with additional columns from lookup result 

Append Data
This will add more rows in the new dataset, example: dataset A has 10 rows and dataset B has 8 rows, the result would be 18 rows in total.
If dataset A and B have different fields, the missing fields will be null in result dataset.

mapping fields between source dataset

the recipe will say how many rows to be added

the result after recipe run, appended dataset do not have "Kota"

Noted:

- Green icon means the recipe has been Pushed

- Yellow icon means the recipe is Saved, but has not been pushed

 Reference: Einstein Analytics: Add Data Recipe

Einstein Analytics: Using Append Transformation

Use Case: Every quarter, order data produce by an external system and need to be imported into Einstein Analytics with CSV file.

Until Winter 19, we cannot add data to the existing dataset, so the option:

1. Manually merge in Excel file
Download existing Einstein Analytics Order dataset into CSV file, manually combine with the latest quarter CSV file and existing Einstein Analytics. Then edit the dataset to Replace the data using combined CSV.


2. Using Append Transformation
Load the latest quarter CSV as a new dataset.
Create a dataflow to read existing dataset and append with the latest quarter CSV uploaded.
Register the combined data to the existing dataset.

notes:
append_Master node: "Source" is both dataset.
reg_Master node: set "Alias" with dataset API name for the master data, and "Name" with Name for master dataset.

You can check the dataset API name by edit the dataset.

yellow highlight is the dataset Name, which can be different with API name

green highlight is API name.

Make sure you just run the dataflow once, otherwise you will create duplication from Quarter data.

Einstein Analytics: Filter Dataset across Object

Use Case:
To produce a clean dataset in dataflow based on the following scenario:
There are 2 objects in SFDC which is not directly related, but both have Opportunity Id.
- Result OS = digest object OS with some filters, contain Opportunity Id.
- Result A = digest object A with some filters, also contain Opportunity Id.
- Result OS should only contain records where Opportunity Id available in Result A.

Solution:

• Start from Result OS
• Do augment with the Result A
• Create compute expression node -- case when ('OppId' is not null && 'OppId' == 'resultA.OppId' then "Yes" else "No" end 
• Finally, create filter node to filter "Yes"

Dataflow:

JSON (simplified)

 {  
  "getOS": {  
   "action": "edgemart",  
   "parameters": {  
    "alias": "dataOS"  
   }  
  },  
  "getA": {  
   "action": "edgemart",  
   "parameters": {  
    "alias": "dataA"  
   }  
  },  
  "augOS_A": {  
   "action": "augment",  
   "parameters": {  
    "operation": "LookupSingleValue",  
    "relationship": "A",  
    "left": "getOS",  
    "left_key": [  
     "OptyId"  
    ],  
    "right": "getA",  
    "right_key": [  
     "OptyId"  
    ],  
    "right_select": [  
     "AccId",  
     "Name"  
    ]  
   }  
  },  
  "Check_in_A": {  
   "action": "computeExpression",  
   "parameters": {  
    "mergeWithSource": true,  
    "source": "augOS_A",  
    "computedFields": [  
     {  
      "type": "Text",  
      "name": "Found",  
      "label": "Found",  
      "saqlExpression": "case when 'A.Name' is not null then \"Yes\" else \"No\" end"  
     }  
    ]  
   }  
  },  
  "regFilter": {  
   "action": "sfdcRegister",  
   "parameters": {  
    "alias": "Filter1",  
    "source": "Filter_Only_Yes",  
    "name": "Filter1"  
   }  
  },  
  "Filter_Only_Yes": {  
   "action": "filter",  
   "parameters": {  
    "filter": "Found:EQ:Yes",  
    "source": "Check_in_A"  
   }  
  }  
 }  

Salesforce: Opportunity Trends Report Type

We discussed a few objects related to Opportunity in detail in the previous blog, from:
- OpportunityFeed
- OpportunityFieldHistory
- OpportunityHistory
- Opportunity__hd

Also, a few report types related to Opportunity:
- Opportunity Field History
- Opportunity History
- Opportunities with Historical Trending

There is one more report type we have not discussed so far Opportunity Trends report type. The good thing about this report type is, it is enabled by default, however, you can't really customize a lot.

Opportunity Trends report takes a Snapshot "As of" the 1st of the month each passing month. These snapshots cannot look forward and the snapshot date and frequency can't be changed. If an opportunity created on 20 Nov 2018, the first snapshot for would be on 1 Dec 2018.

This report type will have information on:
- Historical Amount
- Historical Stage
- Historical Close Date
- Historical Probability
- As of Date

sample report

I do not see any object that stored this data, but let me know if you found it.


Reference:

• Opportunity Trend report differs from the output of Opportunity History report
• Opportunity Trends Report
• As of date on Opportunity Trend Report

Salesforce: Opportunity Tracking, Historical and Trending

Over the last few years, I shared in this blog about multiple objects and method to track and trend Opportunity data in Salesforce:

Trending with Reporting Snapshot
This is a method to capture the trend of Opportunity data by creating a snapshot report, and daily push the result into a custom object. After a while, we will have data to analyze the Opportunity trend based on data stored in the custom object.

Enable Feed Tracking versus Set History Tracking
The idea in this blog is to introduce OpportunityFeed (prefix 0D5 - FeedItem) and OpportunityFieldHistory (prefix 017 - Entity History) object.
Once Opportunity is enabled for Feed Tracking, when users make changes on a few fields in one transaction, it will always create only "one" record in OpportunityFeed. For a standard object, Salesforce will create a new object end with "Feed", and for a custom object, the object API name will end with "__Feed".
While using Set History Tracking, Saleforce will create a number of records in the OpportunityFieldHistory object, based on the number of fields changed, even it happened in one transaction.

Salesforce Opportunity History (Stage History)
This blog is explaining the difference between OpportunityFieldHistory (prefix 017 - Entity History) and OpportunityHistory (prefix 008 - OpportunityHistory) object.
Normally, when Set History Tracking is enabled to an object, Salesforce will create a new object with the suffix "History" for a standard object or "__History" for a custom object.
However, for Opportunity, instead of called as "OpportunityHistory" object, field tracking for Opportunity is called as OpportunityFieldHistory. The reason, simply because the API name of the OpportunityHistory object has been used for Stage History.

Opportunity Stage Aging
This blog is sharing on using "Opportunity History" report type to get the detail of the changes:
- Stage Name
- Amount
- Probability
- Close Date.

Using "Opportunity History" report type, we can get information:
- From Stage
- To Stage
- Stage Change (Y/N)
- Stage Duration
- Last Stage Change Date

Stage Duration in Opportunity History report type will count the number of days an opportunity is in a particular sales stage. While in Opportunity report type, Stage Duration is the number of days from the date when opportunity change to current Stage until today and the number of days keep counting even for the closed opportunity.


Summary:
So far we have discussed a few objects related to Opportunity tracking:

OpportunityHistory
Prefix: 008
Label: Opportunity History
Description: this object store Opportunity Stage detail and used by Opportunity History report type

OpportunityFieldHistory 
Prefix: 017
Label: Opportunity Field History
Description: this object store field value changes when History Tracking is enabled for Opportunity

OpportunityFeed
Prefix: 0D5
Label: Opportunity Feed
Description: similar to OpportunityFieldHistory, this object store feed tracking for Opportunity when Feed Tracking is enabled for Opportunity


Next, we are going to introduce another feature called Historical Trending
API Name: Opportunity__hd
Prefix: hxx
Label: Opportunities (Historical)
Description: this object store value of Opportunity fields enabled for historical trending, including the before and after values.

Once Historical Trending is enabled, following actions will be triggered:

Email
You will get an email from noreply@salesforce.com, this email will tell you that your historical trending data is ready.

New Object
Salesforce will create a new object, for a standard object, it will end with "__hd" e.g. Opportunity__hd, and for a custom object, it will end with "_hd" e.g. Object1__c_hd

Since this is considered custom objects, there will be no default prefix, but it will start with hxx, example h00 for the 1st object enabled for Historical Trending, the 2nd object would be h01, and etc.

Report Type

Salesforce will create a custom report type end with "with Historical Trending", e.g.:

- for Opportunity, it will be called "Opportunities with Historical Trending"

- for other objects, it will be called e.g. "Object1 with Historical Trending"

With this report type, you can compare data for up to five points in time (snapshots) within the previous three full months and all days so far in the current month.


Let us compare between the report and object query. I'll use Opportunity for Historical Trending. Special case for Opportunity: the Amount, Close Date, Forecast Category, Probability and Stage fields are always trended, but you can add more fields as required for trending.

The following report is created with Opportunities with Historical Trending report type and filter with Close Date within 2018.

* today date = 3 Dec 2018

For this sample, I will query with filter on 2 opportunities shown as above report:
SELECT ParentId, CreatedDate, ValidFromDate, ValidToDate, Amount__hpr, Amount__hst, CloseDate__hpr, CloseDate__hst, StageName__hpr, StageName__hst FROM Opportunity__hd WHERE ParentId IN ('0065000000Tmjwh','0065000000PWibH') ORDER BY ParentId, CreatedDate DESC

Note:

• For each field enable for trending, you will found _hpr and _hst fields
• _hpr = value at the start of the Date time range
• _hst = value at the end of the Date time range

There are a few things we can learn by query Opportunity__hd and the relation with the above report:

• ValidFromDate will determine the values in the trending slot
• If the value changed more than once in a day, only the last value of the day will be shown in the report, in this example: line 3 - $15,000.00 is never shown in the report because it is overwritten to $22,000.00 within the same day.
• Report filter is based on the current data
• The date return in the query is GMT, while the report will be based on the user time zone

Reference:

• Trending with Reporting Snapshot
• Enable Feed Tracking versus Set History Tracking
• Salesforce Opportunity History (Stage History)
• Opportunity Stage Aging
• Set Up Historical Trend Reporting
• Limitations on Historical Trend Reporting