Salesforce Files and Content Downloads monitoring in CRM Analytics/Einstein Analytics/Tableau CRM
This CRMA dashboard helps to analyze how users are engaging with files and Salesforce CRM Content.
Problem Statement:
File tracking is available in the Event Monitoring Analytics App, however we will need to purchase an additional license for it. If the client wishes to track just files without needing to purchase an event monitoring App, this method will be useful.
Business KPI’s
We are going to solve below KPI’s in this CRMA Dashboard.
Solution Approach:
Since the ContentVersionHistory object is not a CRMA supported object, we are unable to create a dataset using it in Data Recipe. Therefore, we will use CRMA Dashboard’s Direct Data Queries and SOQL.
The file download history is stored in the ContentVersionHistory object .so we will start by using Salesforce Direct on the ContentVersionHistory Object. To do this, click on Create Query, then select Salesforce Direct and then ContentVersionHistory.
ContentVersionHistory have a look up relationship with ContentVersion and User object. In order to Access the File and User related details we have to traverse the relationship tree of the objects. so, lets switch to the “Query Mode.” let's build a SOQL query.
The below are the list fields required to build a SOQL query.
A total of five Salesforce Direct and SOQL queries will be created. First one is for “Top 10 Downloaded Users”, second is for the “Top 10 Downloaded Files,” a third is for the Details Table, and two more queries are for filters (UserName & User Role Name) and one more as Intermittent Query .
Since we are tracking files download, so we will add this filter on the Where clause “ field=contentVersionDownloaded “ across all the SOQL Query.
Note:
The ContentVersionHistory object field “Field” also contains the actions listed below; if you would like to track any of these actions, we can also put them in the Where clause.
- contentVersionCreated
- contentVersionUpdated
- contentVersionViewed
- contentVersionRated
- contentVersionCommented
- contentVersionDataReplaced
for more details about the ContentVersionHistory Object, pls refer below the document.
- Top 10 Most Downloaded Files
Measures: We will use the Count of Records field as our measure since each record in the ContentVersionHistory object represents a change or action.
Groups: ContentVersion.Title(File Name) field will be used as Grouping Dimension, as we are targeting the Files that have been downloaded the most.
Order By : We will use the Aggregated Measure (Count of records) as an Order by field as “DESC” since our goal is to show the most downloaded files records.
Limit: We will limit the query results to 10 because we only wanted to display the Top 10 Most downloaded Files records.
Filter: As explained above we will use the "field=contentVersionDownloaded “. We must include each Binding for each dashboard filter on a Where clause in order to enable dashboard filters (“User RoleName” and “User Name”) to interact with the Dashboard widget.
To add more filters to the WHERE clause we use logical expressions like AND/OR. In this case, we will use AND
SOQL accepts the Multiple values in where clause is like
WHERE field1 IN (‘value1’, ‘value2’, ‘value3’).
Note the parenthesis and the single quotes. When performing multi-select in SOQL, we will need to replicate this statement. that can be achieved by below commands.
- join: This joins all strings together.
- column: cell is for single selection, but when we do multi-selection, it is a column we are selecting.
- How do we join the multiple strings?: We use a single quote followed by a comma, space, single quote. And we need the query to understand that the single quote is a string, so we enclose the whole single quote, comma, space, single in double quotes and then escape the double quotes!
User Name filter binding:
CreatedBy.Name IN ('{{join(column(Users_Info_1.selection, [\"Name\"]), \"', '\").asString()}}')User Role Name filter binding:
CreatedBy.UserRole.Name IN ('{{join(column(UserRole_1.selection, [\"Name\"]), \"', '\").asString()}}')Final Query:
select COUNT(Id) Total_Downloads,ContentVersion.Title from ContentVersionHistory
where (field = 'contentVersionDownloaded' AND CreatedDate = THIS_YEAR AND CreatedBy.Name IN ('{{join(column(Users_Info_1.selection, [\"Name\"]), \"', '\").asString()}}') AND CreatedBy.UserRole.Name IN ('{{join(column(UserRole_1.selection, [\"Name\"]), \"', '\").asString()}}') )
group by ContentVersion.Title
ORDER BY COUNT(Id) DESC
LIMIT 10Following a successful query execution, go to the chart mode. alter the chart’s formatting as necessary. click Update and Bring it on the dashboard.
Note : Let’s disable faceting for all widgets in the dashbaord .since it isn’t functioning correctly for SOQL queries.
2. Top 10 Most Downloaded Users
Measures: We will use the Count of Records field as our measure since each record in the ContentVersionHistory object represents a change or action.
Groups: CreatedBy.Name(Downloaded User Name) field will be used as Grouping Dimension, as we are targeting the User who have downloaded the most.
Order By : We will use the Aggregated Measure (Count of records) as an Order by field as “DESC” since our goal is to show the most downloaded Users records.
Limit: We will limit the query results to 10 because we only wanted to display the Top 10 Users who downloaded the files most.
Filter: The same filters that are used in the query above will also be used in this one. Furthermore, we intended to include an additional binding on this query that would allow the “Top 10 Most Downloaded Files” chart to be selected, thereby faceting the results of the “Top 10 Most Downloaded Users” chart and “Detail Table”.
The charts “Top 10 Most Downloaded Users” and “Detail Table” should be faceted by passing the value of “Top 10 Most Downloaded Files” chart selected column ; otherwise, the downloaded count will be determined using all of the files (values passing through intermittent step ) . for this we will use the below nested bindings
ContentVersion.Title IN ('{{coalesce(join(column(Top_10_Downloaded_Fi_1.selection, [\"Title\"]), \"', '\"),join(column(Intermident__Query__1.result, [\"Title\"]), \"', '\")).asString()}}')Consideration :
To ensure that we are not exceeding the character limits passed by the Result Bindings. if the value is high, We must further filter the records by considering your use case. View the SOQL restrictions in more detail.
Final Query :
select COUNT(Id) Total_Downloads,CreatedBy.Name from ContentVersionHistory
where (field = 'contentVersionDownloaded' AND CreatedDate = THIS_YEAR AND CreatedBy.Name IN ('{{join(column(Users_Info_1.selection, [\"Name\"]), \"', '\").asString()}}') AND CreatedBy.UserRole.Name IN ('{{join(column(UserRole_1.selection, [\"Name\"]), \"', '\").asString()}}') AND ContentVersion.Title IN ('{{coalesce(join(column(Top_10_Downloaded_Fi_1.selection, [\"Title\"]), \"', '\"),join(column(Top_10_Downloaded_Fi_1.result, [\"Title\"]), \"', '\")).asString()}}') )
group by CreatedBy.Name
ORDER BY COUNT(Id) DESC
LIMIT 10Following a successful query execution, go to the chart mode. alter the chart’s formatting as necessary. click Update and Bring it on the dashboard.
3. Detail Tables
Dimensions : Bring all necessary fields for the table to display, as there will be no requirement for grouping them as it is a flat table.
Filters : The same filters that are used in the query above will also be used in this query, including “Top 10 Most Downloaded Files” Faceting bindings.
Final Query :
select ContentVersion.Title,ContentVersion.FileType,CreatedBy.Name,CreatedDate,CreatedBy.UserRole.Name from ContentVersionHistory
where (field = 'contentVersionDownloaded' AND CreatedDate = THIS_YEAR AND CreatedBy.Name IN ('{{join(column(Users_Info_1.selection, [\"Name\"]), \"', '\").asString()}}') AND CreatedBy.UserRole.Name IN ('{{join(column(UserRole_1.selection, [\"Name\"]), \"', '\").asString()}}') AND ContentVersion.Title IN ('{{coalesce(join(column(Top_10_Downloaded_Fi_1.selection, [\"Title\"]), \"', '\"),join(column(Top_10_Downloaded_Fi_1.result, [\"Title\"]), \"', '\")).asString()}}') )Following a successful query execution, go to the Table mode. alter the table formatting as necessary. click Update and Bring it on the dashboard.
4.UserRole
Since UserRole represents the wider spectrum level, this filter is being created as a first order filter.
Groups: CreatedBy.Name(Downloaded User Name) field will be used as Grouping Dimension, as we are targeting the User who have downloaded the most.
Note:
When we select Multiple Selection Required, the filter selects the first one on the list. As opposed to static datasets, Salesforce Direct, Pick initial values doesn’t work. So, we have to manually select the filters each time we want to interact with the dashboard.
Final Query:
SELECT COUNT(Id),CreatedBy.UserRole.Name FROM ContentVersionHistory
where (field = 'contentVersionDownloaded'AND CreatedDate = THIS_YEAR)
group by CreatedBy.UserRole.NameFollowing a successful query execution, go to the Table mode. . click Update.
we pull the list widget on to the dashboard and then use this query to populate these widgets. Because Salesforce Direct / SOQL don’t work with Global Filters yet.
Format the filter widgets:
- Update instantly = false
- Measure field = none
- Show widget actions = false
- Apply Global Filters = false
- Selection Type = Multiple Selection Required
- Faceting = None
5. User info
The filter should be formatted as above since this will also be a list selector.
This filter will be dependant on the “User RoleName” filter. so that Users belonging to that Role will be popup whenever role is selected.
Groups: The column CreatedBy.Name (Downloaded User Name) will be utilized as the Grouping Dimension because our goal is to obtain a list of User Names.
Filter : In order to make the query a dependent filter, we are feeding it a list of the Role Name selection values though binding.
User RoleName selection binding :
CreatedBy.UserRole.Name IN ('{{join(column(UserRole_1.selection, [\"Name\"]), \"', '\").asString()}}') Final Query:
SELECT COUNT(Id),CreatedBy.Name FROM ContentVersionHistory
where (field = 'contentVersionDownloaded' AND CreatedDate = THIS_YEAR AND CreatedBy.UserRole.Name IN ('{{join(column(UserRole_1.selection, [\"Name\"]), \"', '\").asString()}}'))
GROUP BY CreatedBy.NameFollowing a successful query execution, go to the Table mode. alter the table formatting as necessary. click Update and Bring it on the dashboard.
6. Intermittent Query:
In a dashboard Salesforce Direct /SOQL will not work faceting. so, we are creating this intermittent step to produce a list of Files Names.
Whenever “Top 10 Most Downloaded Files” Chart column is not selected then we are passing this query result to the “ Detail Tables and Top 10 Most Downloaded Users” widgets.
Final Query :
select COUNT(Id) Total_Downloads,ContentVersion.Title from ContentVersionHistory where (field = 'contentVersionDownloaded' AND CreatedDate = THIS_YEAR )
group by ContentVersion.TitleNotes: I have only taken into consideration faceting the “Top 10 Most Downloaded Files” chart or my use case.To facet the other chart selections, you can use the same method.
Dashboard Overview :
Acknowledgment: thanks to Sayantani Mitra for Join Bindings.
