Skip to main content

Celonis Product Documentation

Salesforce example

We will build an event log based on raw data from Salesforce. This event log will have data of the process "Case Management".

What do we need?

  1. To extract from Salesforce our Case and CaseHistory table:

    1. Sample file to download: Case.csv

    2. Sample file to download: CaseHistory.csv

  2. A way to manipulate data. We recommend JupyterLab - click here.

Use the steps below to build the event log:
Step 1: Upload the files (if you are using JupyterLab locally you can skip this step)

Click the button bellow and upload your files.

39682838.png
Step 2: Launch a Python3 notebook

Click the + button and then select Python 3.

39682837.png
Step 3: Paste the content of the script on the right side and click the Play button
39682836.png
Step 4: Download the generated Event Log
39682835.png
Step 5: Upload your event log in Celonis and start analyzing your process

Event Log Generator for Salesforce Case Management Process

import pandas as pd
import os
import datetime                                                                                                                     

#read the files
Case = pd.read_csv(os.getcwd()+"/"+"Case.csv")
CaseHistory = pd.read_csv(os.getcwd()+"/"+"CaseHistory.csv")

#create the structure of your eventlog
#add as many columns as possible
eventlog = pd.DataFrame(columns = ['CaseID','Activity','Timestamp','Priority','Comments','Type','Status','Reason'])

#let's build our event log now

for index, row in Case.iterrows():
    # activity: Case Created
    if not pd.isna(row['CreatedDate']) and not pd.isna(row['Id']) and row['IsDeleted'] == False :
        new_row = {
            'CaseID':row['Id'], 
            'Activity':'Case Created', 
            'Timestamp':row['CreatedDate'], 
            'Priority':row['Priority'],
            'Comments':row['Comments'],
            'Type':row['Type'],
            'Status':row['Status'],
            'Reason':row['Reason']
        }
        eventlog = eventlog.append(new_row, ignore_index=True)
    # activity: Case Closed
    if not pd.isna(row['ClosedDate']) and not pd.isna(row['Id']) and row['IsDeleted'] == False and row['IsClosed'] == True:
        new_row = {
            'CaseID':row['Id'], 
            'Activity':'Case Closed', 
            'Timestamp':row['ClosedDate'], 
            'Priority':row['Priority'],
            'Comments':row['Comments'],
            'Type':row['Type'],
            'Status':row['Status'],
            'Reason':row['Reason']
        }
        eventlog = eventlog.append(new_row, ignore_index=True)

# Activities: Changes 
#Rename Id column to CaseId in order to be able to merge the two tables. In addition rename all the columns that have the same name in the case and casehistory table
Case_new = Case.rename(columns={'Id': 'CaseId', 'CreatedDate': 'CreatedDateCase', 'IsDeleted': 'IsDeletedCase'})

#Merge the tables
CaseHistory = CaseHistory.merge(Case_new, on='CaseId')

# Now we can start creating the event log for changes
for index, row in CaseHistory.iterrows():
    if not pd.isna(row['CreatedDate']) and not pd.isna(row['Id']) and row['IsDeleted'] == False :
        new_row = {
            'CaseID':row['CaseId'],
            'Activity':'Set ' + row['Field'] + ' to ' + row['NewValue'],
            'Timestamp':row['CreatedDate'], 
            'Priority':row['Priority'],
            'Comments':row['Comments'],
            'Type':row['Type'],
            'Status':row['Status'],
            'Reason':row['Reason']
        }
        eventlog = eventlog.append(new_row, ignore_index=True)
        
eventlog = eventlog.sort_values(['CaseID', 'Timestamp'], ascending=[True, True]).reset_index(drop=True)
eventlog.to_csv(os.getcwd()+"/"+"event_log.csv", index=False)
print(eventlog)