With the Salesforce Incident Management app template you can load raw input data from Salesforce for the Incident Management process, extracted to either Snowflake or SQl Server using CData Sync. The raw input data is transformed through a series of transformations that take place via dbt (data build tool) and produce the required input data used in Process Mining process apps.
Salesforce calls incidents as Cases. the Incident Management process starts when the case is reported and it ends when the case gets solved.
The basic activities for the Incident Management process are opening new cases, assigning them to correct users, working on the issue, resolving and closing cases. Reassignments for the different users may occur during the process depending if the first assignment was correct, if the user assigned is actually the best option for resolving any specific case or if the case needs attention from multiple users during its resolution.
| Tag | Description | Motivation |
|---|---|---|
| Multiple assignments detected | Cases that have multiple user assignments. | This either represents a lack of skills of the first assigned user to resolve the Issue or inefficiencies in identifying the best qualified user to work on it. |
| More than 2 user role changes detected | Cases that have more than two changes to the user role. | Changes in the user role may represent fail attempts to assign the right user with the correct skill set and/or permissions to work on a case. This may also represent multiple teams assigned to work on it. |
| Priority change detected | Cases that have got more than one 'Set Priority...' activity. | The change in priority level is commonly used by support users to extend the time they have to solve an incident. |
| Ticket reopening detected | Cases that got multiple activities 'Change Status to Solved'. | The reopening of a "Solved" Incident might indicate that the solution provided for an Incident was not satisfactory. This could mean that the support user rushed to close it to comply with the SLA providing a less optimal service. |
| Ticket directly closed detected | Cases that have only 'Create Case' and 'Change Status to Closed' as activities. | Directly closing a case may signal that a case has been created unnecessarily or that the case has been worked on and resolved without having previously been registered in the system. |
Salesforce keeps track of time based on a set of milestones that each case has to go through. These milestones are not standard, the milestones are defined and customized in the Salesforce's system.
Multiple Due dates based on all the milestone categories set in Salesforce are created automatically.
Salesforce Service Cloud. Case Management from Salesforce Service Cloud must be configured. You need read access to the Case Management objects in Salesforce Service Cloud.
In order to connect via CData Sync, you must have appropriate read permissions on each of the objects which are being extracted.
The Salesforce Service Cloud app template works on the process starting from the opening of a case until its resolution and closure. For case history tracking, the minimal setup requires access to the following transactional objects:
It is also necessary to have access to the following master tables:
For Due dates the following objects are necessary:
Below is a description on how to use CData Sync to set up a source connection and load data into a Process Mining Salesforce Incident Management process app.
In general, you should follow the steps as described in Loading data using CData Sync (Snowflake) or Loading data using CData Sync (SQL Server) to set up data loading using CData Sync. Since specific settings are required when using Salesforce Service Cloud, pay attention to the steps described below.
Define the following settings in the Replicate Options section in the Advanced tab in the Job Settings panel.
Important: Make sure you edit the Pre-job Event.
CData Sync allows the use of environment variables in order to drive specific extraction logic in each query.
| Variable | Description | Comment |
|---|---|---|
| start_extraction_date | Defines first date for which data will be extracted. | Mandatory |
| end_extraction_date | Last date for which data will be extracted. |
In order to setup the environment variables:
| Step | Action |
|---|---|
| 1 | Access the job created in the previous step. |
| 2 | Click on the Events tab. |
| 3 | Add the following lines to the Pre-Job Event script. |
<!-- Modify environment variables here. -->
<!-- Variable start_extraction_date must be populated.-->
<api:set attr="out.env:start_extraction_date" value="2022-01-01" />
<!-- In case a specific end date is needed, replace the value string with the required date in yyyy-MM-dd format. -->
<!-- i.e api:set attr="out.env:end_extraction_date" value="2022-02-01" -->
<api:set attr="out.env:end_extraction_date" value="[null | now() | todate('yyyy-MM-dd')]" />
<api:push item="out" />
Important: Do not modify the api:info details that are shown by default.
In order to modify the environment variables, modify the values within the Events tab. By default, end_extraction_date will default to today's date. start_extraction_date must always be populated.
Once the job is correctly setup, click on Add Custom Query under the Tables tab and paste the following queries (each query needs to maintain the semicolon at the end). Make sure you save all changes.
REPLICATE [Case] SELECT [Id], [AccountId], [CaseNumber], [ContactId], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [HasCommentsUnreadByOwner], [IsClosed], [Origin], [OwnerId], [Priority], [Status], [SuppliedName], [Type] FROM [Case] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [Contact] SELECT [Id], [Name] FROM [Contact];
REPLICATE [RecordType] SELECT [Id], [Name] FROM [RecordType];
REPLICATE [User] SELECT [Id], [Department], [Name], [ProfileId], [UserType], [UserRoleId] FROM [User];
REPLICATE [UserRole] SELECT [Id], [Name] FROM [UserRole];
REPLICATE [Profile] SELECT [Id], [Name] FROM [Profile];
REPLICATE [CaseMilestone] SELECT [Id], [CaseId], FORMAT([CompletionDate], 'yyyy-MM-dd hh:mm:ss') as [CompletionDate], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [MilestoneTypeId], FORMAT([TargetDate], 'yyyy-MM-dd hh:mm:ss') as [TargetDate] FROM [CaseMilestone] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [Account] SELECT [Id], [AccountSource], [BillingCountry], [BillingState], [CreatedById], [Industry], [Name], [OwnerId], [Type] FROM [Account];
REPLICATE [MilestoneType] SELECT [Id], [Name] FROM [MilestoneType];
REPLICATE [CaseHistory] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [CaseId], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Field], [NewValue], [OldValue], [DataType] FROM [CaseHistory] WHERE ([Field] IN ('Status', 'Priority', 'Contact', 'Owner', 'ownerAssignment', 'RecordType')) AND ([DataType] IN ('Text', 'DynamicEnum', 'RecordType'))
Note regarding CData Salesforce Provider Version 21.0.8097.0
When a table has an _Incremental Check Column_ field predefined in CData Sync, the SQL query cannot use the same date field in both statements (WITH and WHERE) at the same time.
If version 21.0.8097.0 (or an older version) of CData is used, the date filtering should be done in the WHERE clause and should be removed from the WITH statement.
Alternative Scenarios Record Type is enabled in Salesforce Org
In case Record Type is used the Case query must be amended to add the RecordTypeId field in the query.
The following replicate shows Case with RecordTypeId.
REPLICATE [Case] SELECT [Id], [AccountId], [CaseNumber], [ContactId], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [HasCommentsUnreadByOwner], [IsClosed], [Origin], [OwnerId], [Priority], [RecordTypeId], [Status], [SuppliedName], [Type] FROM [Case] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
The following tables include the list of fields per input table, their description, data type to be used when formatting the input and the filter flag to identify those that are being used to filter data.
Below is an overview of the different field types and their default format settings.
| Field type | Description |
|---|---|
| boolean | true, false, 1, 0 |
| date | yyyy-mm-dd |
| datetime | yyyy-mm-dd hh:mm:ss[.ms], where [.ms] is optional. |
| double | Decimal separator: . (dot); thousand separator: none |
| integer | Thousand separator: none |
| text | N/A |
Table containing changes performed on each case. This is the historical table from which activities are created.
| Field | Stores | Type | Filtering |
|---|---|---|---|
| CaseId | Case's key related to the change in the log history. | text | |
| CreatedById | User's key that added the change to the log history. | text | |
| CreatedDate | Timestamp that the change was added to the log history. | datetime | time-based filter |
| DataType | Changed data's type. | text | in ('Text','DynamicEnum') |
| Field | The type of change added to the log history. | text | in ('Status','Priority','Contact','Owner','ownerAssignment') |
| Id | System's key for this table. | text | |
| NewValue | Value added post change. | text | |
| OldValue | Value previous to the change. | text |
This is a transactional table for cases.
| Field | Stores | Type | Filtering |
|---|---|---|---|
| AccountId | Account's key related to the case. | text | |
| CaseNumber | Interface's case number. | text | |
| ContactId | Customer's contact key. | text | |
| CreatedById | User's key for the user that created the case. | text | |
| CreatedDate | Date the case was created. | datetime | time-based filter |
| HasCommentsUnreadByOwner | Identifier for unread comments in the case. | boolean | |
| Id | System's key for this table. | text | |
| IsClosed | Identifier for closed cases. | boolean | |
| Origin | Channel by which the ticket was created. | text | |
| OwnerId | User's key for the owner of the case. | text | |
| Priority | Priority level of the case. | text | |
| RecordTypeId | Record type key that categorizes cases. | text | |
| Status | Last status fo the case. | text | |
| SuppliedName | Customer's name. | text | |
| Type | Case's type. | text |
This table stores data related to the accounts from which cases are linked.
| Field | Stores | Type | Filtering |
|---|---|---|---|
| AccountSource | The source of the account record. | text | |
| BillingCountry | Country portion of billing address. | text | |
| BillingState | State or province portion of billing address. | text | |
| CreatedById | User's key who created the account, including creation date and time. | text | |
| Id | System's key for this table. | text | |
| Industry | Primary business of account. | text | |
| Name | Account's name. | text | |
| OwnerId | Account owner's key. | text | |
| Type | Type of account. | text |
This object contains master data related to the case's contact.
| Field | Stores | Type | Filtering |
|---|---|---|---|
| Id | System's key for this table. | text | |
| Name | Contact's name. | text |
This object contains master data related to the case's record type.
| Field | Stores | Type | Filtering |
|---|---|---|---|
| Id | System's key for this table. | text | |
| Name | Record type's name. | text |
This object contains master data related to users.
| Field | Stores | Type | Filtering |
|---|---|---|---|
| Department | User's department. | text | |
| Id | System's key for this table. | text | |
| Name | User's name. | text | |
| ProfileId | ID of the user's profile. | text | |
| UserRoleId | ID of the user's role. | text | |
| UserType | Type of user. | text |
This object contains master data related to the user role.
| Field | Stores | Type | Filtering |
|---|---|---|---|
| Id | System's key for this table. | text | |
| Name | Name of the user role. | text |
This object contains master data related to the user profile.
| Field | Stores | Type | Filtering |
|---|---|---|---|
| Id | System's key for this table. | text | |
| Name | Name of the user profile. | text |
This object contains start dates and target dates for the main steps of the process. These milestones will be used for the due dates model.
| Field | Stores | Type | Filtering |
|---|---|---|---|
| CaseId | Case's key related to the change in the log history. | text | |
| CompletionDate | Date the Milestone was actually completed. | datetime | |
| CreatedDate | Date the milestone was created. | datetime | time-based filter |
| Id | System's key for this table. | text | |
| MilestoneTypeId | MilestoneType's key related to milestone categories. | text | |
| TargetDate | Expected date for completion of the milestone. | datetime |
This object contains names of the milestones.
| Field | Stores | Type | Filtering |
|---|---|---|---|
| Id | System's key for this table. | text | |
| Name | Category names fot milestones. | text |
Salesforce Service Cloud has only one entity and is created based on the "Case" table.
| Entity | Input data |
|---|---|
| Case | Case |
The activities are created from records in CaseHistory table.
The activities are based on the "Field" field in Case_history_input. Some activities are also based on "Old_value" and "New_value" fields.
The list of activities is the following:
| Activity | Description |
|---|---|
| Assign Owner | Assign user responsible for working on the case. |
| Auto-assign Owner | Auto-assign user responsible for working on the case. |
| Change Record Type to... | Contains a set fo activities related to Record Type of cases. |
| Change Status to... | Contains a set fo activities related to statuses of cases. |
| Change Contact | Change clients contact user data. |
| Create Case | Create a new case. |
| Remove Contact | Remove clients contact user data. |
| Set Contact | Set clients contact user data. |
| Set Priority to... | Identify priority level. |
In order to add data which will be defined by the end user, one additional file is loaded into the model which need to be prepared by the Analyst. This is the Automated_users_raw file, which contains the users that are flagged as automated. If this is not configured, the connector will still work properly but it will not have that extra information.
Salesforce does not contain data related to "Event_start", "Case_value" and "Team".
If any activity is missing, a new "Field" value needs to be identified and added in the CDATA replicate query, Case_history_input and the models included in the 3_events folder.