Salesforce Incident Management

Introduction

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.

Incident Management process

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.

Tags

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.

Due Dates

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 configuration

Source system

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.

Environmental settings

In order to connect via CData Sync, you must have appropriate read permissions on each of the objects which are being extracted.

System specific settings

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:

Configuring CData Sync for Salesforce Service Cloud

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.

Setting up the source connection

Creating the extraction job

Define the following settings in the Replicate Options section in the Advanced tab in the Job Settings panel.

Setting up environment variables in the extraction job

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.

Table Replication

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_raw] SELECT [Id], [AccountId], [CaseNumber], [ContactId], [CreatedById], [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_raw] SELECT [Id], [Name] FROM [Contact];
REPLICATE [RecordType_raw] SELECT [Id], [Name] FROM [RecordType];
REPLICATE [User_raw] SELECT [Id], [Department], [Name], [ProfileId], [UserType], [UserRoleId] FROM [User];
REPLICATE [UserRole_raw] SELECT [Id], [Name] FROM [UserRole];
REPLICATE [Profile_raw] SELECT [Id], [Name] FROM [Profile];
REPLICATE [CaseMilestone_raw] SELECT [Id], [CaseId], [CompletionDate], [CreatedDate], [MilestoneTypeId], [TargetDate] FROM [CaseMilestone] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [Account_raw] SELECT [Id], [AccountSource], [BillingCountry], [BillingState], [CreatedById], [Industry], [Name], [OwnerId], [Type] FROM [Account];
REPLICATE [MilestoneType_raw] SELECT [Id], [Name] FROM [MilestoneType];
REPLICATE [CaseHistory_raw] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT * 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_raw] SELECT [Id], [AccountId], [CaseNumber], [ContactId], [CreatedById], [CreatedDate], [HasCommentsUnreadByOwner], [IsClosed], [Origin], [OwnerId], [Priority], [RecordTypeId], [Status], [SuppliedName], [Type] FROM [Case] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');

Input fields

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.

Input types

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

CaseHistory

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

Case

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

Account

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

Contact

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

RecordType

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

User

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

UserRole

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

Profile

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

CaseMilestone

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

MilestoneType

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

Design specifications

Entities

Salesforce Service Cloud has only one entity and is created based on the "Case" table.

Entity Input data
Case Case

Activities

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.

Customizing the transformations

Configuration

Automated Users

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.

Limitations

Salesforce does not contain data related to "Event_start", "Case_value" and "Team".

Troubleshooting

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.